I learned JDBC some time ago and connected to mysql to get data. According to the teacher's sample data, I had to save some information such as names, and they were all in English. I didn't want to use English at the time, so I saved my roommate's name. Hehe, as a result, something went wrong.
Connect to database statement:
static final String DB_URL = "jdbc:mysql://localhost/filemanagement";
Query statement:
private static final String theUserQuery = "SELECT name, password, role FROM userinfo WHERE name = ?";
I'm using my name to query, NullPointerException, it's obvious that no corresponding data was found with my name, and it exists in the database. Why is this?
The answer from Baidu is Chinese garbled code. The solution is to modify the connection database statement to:
static final String DB_URL = "jdbc:mysql://localhost/filemanagement?useUnicode=true&characterEncoding=GBK";
Try again!
It's OK! But why is this? What are those two parameters? Why did the problem be solved after adding it?
These two parameters are explained as follows:
The default values for both parameters are false. In other words, when we specify the character set used for connection when connecting mysql, everything is normal. But I still don’t know much about the mechanism, so I continue to check it.
It turns out that there is a character set conversion process when Mysql connection performs query and other operations:
1. When MySQL Server receives the request, converts the request data from character_set_client to character_set_connection;
2. Before performing internal operations, convert the requested data from character_set_connection to the internal operation character set. The determination method is as follows:
• Use the CHARACTER SET setting value for each data field;
• If the above value does not exist, use the DEFAULT CHARACTER SET setting value (MySQL extension, non-SQL standard) of the corresponding data table;
• If the above value does not exist, the DEFAULT CHARACTER SET setting value of the corresponding database is used;
• If the above value does not exist, use character_set_server to set the value.
3. Convert the operation result from the internal operation character set to character_set_results.
What do these character sets represent?
character_set_server: default internal operation character set
character_set_client: The character set used by client source data
character_set_connection: Connection layer character set
character_set_results: query result character set
character_set_database: The default character set of currently selected database
character_set_system: system metadata (field name, etc.) character set
I also found some common questions. Although they are different from mine, they are of great reference value.
• Before inserting utf8-encoded data into a data table with the default character set is utf8, the connection character set is utf8.
When inserting, according to the default settings of the MySQL server, character_set_client, character_set_connection and character_set_results are latin1;
The data of the insertion operation will go through the character set conversion process of latin1=>latin1=>utf8. During this process, each inserted Chinese character will be saved from the original 3 bytes to 6 bytes;
The result during query will go through the character set conversion process of utf8=>utf8, and the saved 6 bytes are returned intact, resulting in garbled code...
• Set the connection character set to utf8 before inserting utf8-encoded data into a data table with the default character set is latin1.
When inserting, character_set_client, character_set_connection and character_set_results are all utf8;
The insertion data will be converted through the character set of utf8=>utf8=>latin1. If the original data contains Unicode characters other than /u0000~/u00ff range, it will be converted to the "?" (0x3F) symbol because it cannot be represented in the character set of latin1. In the future, no matter what the connection character set is set, its content cannot be restored.
(This part is excerpted from Brother Bird's blog, and the link is attached later)
The tables in my database are all set with utf8 encoding, but when I first connected, I did not set the connection character set, so the default is latin1. After the conversion from utf8=>latin1, garbled code is generated. The GBK encoding I used for the second time, and I didn't use utf8 encoding. Why is it OK? Actually, it is the same thing. Chinese is not in latin1 encoding, but in GBK and utf8, so there will be no problems.
The above is the solution to the garbled exception of JDBC connection to mysql. If you still have any questions, you can discuss it in the comment area below.