JDBC Settings

When installing MySQL, I encountered that students did not set the database according to the rules. As a result, garbled characters appeared in the operation of the database after JDBC connection. At that time, I hastily added some connection Settings for students, but the students did not understand them well, so HERE I give a brief introduction.

The problem of garbled code is nothing more than the coding set is not uniform cause!

  • It could be that the code is coded differently than the database
    • Be sure to use UTF-8 for everything
jdbc:mysql://localhost:3306/ database name? useUnicode=true&characterEncoding=UTF-8
Copy the code

Description: Specifies the encoding/decoding format of characters

Scene description

The mysql database uses GBK encoding, while the project database uses UTF-8 encoding. UseUnicode =true&characterEncoding=UTF-8; useUnicode=true&characterEncoding=UTF-8;

  1. When storing data

    When storing project data, the database will first use UTF-8 format to decode the data into bytecode, and then the decoded bytecode is stored in the database using GBK encoding.

  2. When you get the data

    When fetching data from the database, the database first decodes the data in the database into bytecode in GBK format, then encodes the decoded bytecode in UTF-8 format, and finally returns the data to the client.

Note: When configuring database UTL in an XML configuration file, use the escape character of &, which is &

JDBC Attribute Description

jdbc:mysql://[host][,failoverhost...] '[[: port] / [database]? PropertyName1] [= propertyValue1] [& propertyName2] [= propertyValue2]
Copy the code
The parameter name The default value Minimum version Requirements Parameters to describe
user All versions Database user name (used to connect to the database)
password All versions User password (used to connect to the database)
useUnicode false 1.1 g Whether to use the Unicode character set
characterEncoding false 1.1 g Specify character encoding
autoReconnect false 1.1 If the database connection is interrupted, is the database automatically connected again?
autoReconnectForPools false 3.1.3 Whether to use the reconnection policy for the database connection pool
failOverReadOnly true 3.0.12 After automatic reconnection is successful, is the connection set to read-only?
maxReconnects 3 1.1 The number of connections to retry when autoReconnect is set to true
initialTimeout 2 1.1 With autoReconnect set to true,

The interval between reconnections, in seconds
connectTimeout 0 3.0.1 Timeout for establishing a socket connection with the database server, in milliseconds. 0 indicates that it never times out and applies to JDK 1.4 and later
socketTimeout 0 3.0.1 Socket operation (read/write) times out, unit: millisecond. 0 indicates that it never times out

The MySQL connection URL can be set to:

jdbc:mysql://localhost:3306/database? useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE&failOverReadOnly=false
Copy the code

autoReconnect=true : If you have upgraded the mysql-connector, the characterEncoding= UTF8 will be automatically recognized as UTF8MB4 (compatible with the original UTF8), and the autoReconnect configuration is highly recommended. Utf8mb4 character set cannot be used because the latest DB configuration is not read due to cache.

When processing multiple SQL statements in MyBatis, we need to add allowMultiQueries parameter to the URL of MySQL connection and set it to true because the MySQL driver does not enable batch SQL execution

jdbc:mysql:/ / 127.0.0.1:3306 / database? useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
Copy the code

When using a database connection pool, it is best to set the following two parameters

autoReconnect=true&failOverReadOnly=false

MySQL SQL statements alias is not effective problem: useOldAliasMetadataBehavior

To improve readability, specify an alias for the same name and for the same name that needs to be checked multiple times.

jdbc:mysql://localhost/openemm? UseUnicode = yes&characterEncoding = utf-8 & useOldAliasMetadataBehavior = true: + JDBC: mysql: / / localhost/openemm? + useUnicode=yes&characterEncoding=UTF-8 + useUnicode=yes&characterEncoding=UTF-8 And set utf-8 + useOldAliasMetadataBehavior = true this represents the database alias are allowedCopy the code

No data can be queried in the database after the connection

Clearly there are data in the database that meet the conditions, but it is unable to query? It was probably a stupid mistake.

Try not to use the nchar () format in the database, because the fixed number of bits, when the data is not enough bits will use null characters to occupy the space. For example: name nchar (10) --> Schweisin uses String output, you see Schweisin □□□□□□Copy the code

The nchar () format is also a waste of space and is not recommended if not necessary.

The appendix