Notes for JDBC operations on MySQL

Today, I developed a java interface program to insert values from the mysql database to an Oracle database. I think it is quite simple. mysql's query SQL is written, so there is no difficulty. The code for JDBC operations on the database in java has not changed much. However, some operation details were not noticed, and it took me one afternoon to complete the operation.

My practice is like this. First, I wrote the SQL statement for querying mysql, and everything went smoothly on the mysql database client. Note that SQL statements contain Chinese characters. One sentence is tempTable. type = dinary 'interest '. Five records are queried. mysql's Chinese Query requires a binary conversion symbol built in mysql. After debugging, I copied the SQL statement to the java code, but the result of JDBC execution was 0. My first instinct was that the mysql database connection was wrong, but soon I found that the database connection was correct,There is no difference between the SQL statement in java code and the SQL statement executed by the mysql client. However, the execution results in the client and java code are inconsistent.

Finally, the problem is located in the Chinese language of the SQL statement. The Chinese encoding in java is inconsistent with that in the SQL statement executed by the mysql database.

I checked the SQL statement for creating the table in mysql that I want to operate on:
Create table 'temptable '(
'Contractid' varchar (10) not null default '123 ',
'Version' int (2) not null default '1 ',
'Type' varchar (20) not null,
'Memo' text,
'Count' int (3) default '0 ',
Primary key ('contractid', 'version ')
)ENGINE = MyISAM default charset = latin1;
I checked a lot of articles online,The final solution is to convert the Chinese in the SQL statement of java code to binary.
String lx = "interest ";
Lx = new String (lx. getBytes ("GBK"), "latin1 ");
String SQL _info = "select..." +
"Where tempTable. type = '" + lx + "'" +
"......";
-- Then the JDBC operation
After the transformation is completed, execute the java code again. The result is consistent with the SQL Execution result of the database client.




There is also a problem that occurs when I create an interface. Also depressed for a long time.
I wrote a completely correct SQL statement, but the execution result is not what I want. SQL has a max (atable. version) operation.

Because the version field of the other database is built into the varchar type when creating the atable table,In actual business, the maximum value of this version is 22, and I use the max function of SQL to query the result.Is 9, which leads to inconsistent query results.Later, the version field of the atable table in the other database is changed to the integer type to be correctly executed.