Java link MySQL database and garbled



jdk:j2sdk1.5.0
mysql:mysql-5.0.41-win32_178
jdbc driver : Mysql-connector-java-3.2.0-alp Ha-bin.jar

One, Java connection to the database
Java and database connection book there are four ways to write, I understand in fact from its essentially two, a set of Jdbc-odbc Bridge, through the ODBC data source and database connected. The other is a pure Java-based connection that is driven by the Java class Library API. In MySQL, for example, these two methods require MYSQL-ODBC drive and MYSQL-JDBC-ODBC driver respectively. I use the second method, of course, it is recommended to use a pure Java-type connection, need MYSQL-JDBC-ODBC driver, can be downloaded from the Internet.
You first need to place the driver in the Lib directory of the JDK's installation directory and add the jar package to the environment variable classpath, with the same settings as Dt.jar and Tools.jar in the classpath of the JDK.
The connection process is in three steps: Load the driver, establish a connection using the connection in the java.sql package, generate a statement object from the Connection object, and then statement the object to pass the SQL statement by calling its method.
Here's a concrete example to test our connection:

Import java.sql.*;
public class Connecttomysqltest
{
public static void Main (string[] args)
{
Try
{
Load Driver
Class.forName ("Com.mysql.jdbc.Driver");
Create a URL for the specified database
String url= "Jdbc:mysql://localhost:3306/student"; Student is the name of the database created
String username= "root";Root is the default user name for MySQL
String password= "123";123 is the MySQL password you set
Create a connection
Connection conn=drivermanager.getconnection (Url,username,password);
Create a statement object
Statement stmt=conn.createstatement ();
Statement object submits SQL statement to database
Stmt.execute ("drop table if exists Stu");Delete if Table Stu is present
Stmt.execute ("CREATE TABLE stu (ID int NOT NULL primary key,name varchar () not null,sex varchar (4))"); Create a table Stu
Inserting records
Stmt.execute ("INSERT into Stu values (1, ' aaa ', ' Boy ')");
Stmt.execute ("INSERT into Stu values (2, ' BBB ', ' Girl ')");
Stmt.execute ("INSERT into Stu values (3, ' CCC ', ' Girl ')");
Executes a query database operation and returns the result set of the query
ResultSet rs=stmt.executequery ("Select * from Stu");
To position the result set table pointer before the first record
Rs.beforefirst ();
Print Query Results
SYSTEM.OUT.PRINTLN ("The data of the Stu table is as follows:");
System.out.println ("---------------------");
System.out.println ("School number \ t name \ t gender");
System.out.println ("---------------------");
int num;
String name;
String sex;
while (Rs.next ())
{
Num=rs.getint ("id");
Name=rs.getstring ("name");
Sex=rs.getstring ("Sex");
System.out.println (num+ "\ t" +name+ "\ T" +sex);
}
Stmt.close ();
Conn.close ();
}
catch (Exception e)
{
E.printstacktrace ();
}
}
}

before testing , you need to start MySQL and type in the MySQL command window: Create DATABASE  Student Enter to build the student database, and then compile run connecttomysqltest at the command prompt, The following results can occur:

Second, MySQL Chinese garbled problem
First , let's start with a test:
after the above code runs successfully, open the MySQL command Window , in which input: use student; (Enter Select database)     
SELECT * from Stu; (Enter the following query results)

and STMT.E the code above. Xecute ("INSERT into Stu values (1, ' aaa ', ' Boy ')"), in the ' AAA ' instead of ' Zhang San ', then save the recompile run, run the result as:

We'll open the MySQL command window again, because we've just chosen the student database,  We can enter directly: SELECT * from Stu; (enter query) The result is as follows:

It can be found that after changing to Zhang San, the running result is displayed normally, but in MySQL it shows two question marks.

The default character encoding for MySQL is latin1, which corresponds to Java iso-8859-1. So to correctly display our Chinese characters, only need to be the MySQL character encoding method has latin1 changed to Chinese in the line, changed to "GBK" or "gb2312", modified methods have two kinds:
One is to reconfigure MySQL, one of its configuration wizards is to let you choose the character encoding, you can choose the Custom option, and then customize it as "GBK" or "gb2312";
The other is to directly modify the MySQL installation directory in the "My.ini" file, in the MySQL installation directory to find the file, open with Notepad, in which found the following two sentences:
[My SQL]
Default-character-set=latin1

[My sqld]
......
......
Default-character-set=latin1
Respectively, the latin1 is changed to "GBK", note to use lowercase, I heard the capitalization is not recognized.

There are several other methods on the Web, and here is a way to specify the character set and Unicode encoding in the connection string of the connection data, and he writes the URL of the database:
String url= "Jdbc:mysql://localhost:3306/student? user=root&password=123&useunicode=true&characterencoding=gb2312 ";
The purpose of this writing is to tell the driver to transcode the database in this way to avoid garbled, this method can only solve the problem of garbled data from Java out of MySQL, but if you insert Chinese data from Java to MySQL, still display garbled in MySQL , because you did not modify the character encoding of MySQL, it is the default latin1, so the Chinese still can not display normally, in other words, if the MySQL encoding method is latin1, you java into which the Chinese, in MySQL is always garbled, It can only correctly display the Chinese in MySQL inserted directly into it, from the outside to insert Chinese it is not normal display.

Another way is to enable Java to remove data from MySQL from "latin1" to "GBK", from Java to the data inserted into MySQL from "GBK" to "latin1" to take out the data as an example, after such conversion:

String Name=new string (rst.getstring ("name"). GetBytes ("Iso-8859-1"), "GBK");

Note here: GBK is uppercase in Java and lowercase is an error. Latin1 in Java corresponds to iso-8859-1.

You can also write two conversion functions, which are converted by invoking a function, as follows:
Import Java.io.UnsupportedEncodingException;
Solve Chinese problems
public class Encodingutil {

Remove from Database
public static string LATIN1TOGBK (String str)
{
try {
String temp_p = str;
byte[] temp_t = temp_p.getbytes ("iso-8859-1");
String temp = new String (temp_t, "GBK");
return temp;
}catch (UNSUPPORTEDENCODINGEXCEPtion ex) {
System.out.println (ex);
Return "";
}

}

//in Chinese data with
public static string GBKToLatin1 (String str)
{
if (str==null)
{
    str= "";
}
else{
    try{
      str=new String (str.getbytes ("GBK"), "Iso-8859-1");
    }
    catch (Exception ex) {
      Ex.printstacktrace ();
    }
}
return str;
}
}


          But this is fine for less database operations, if you want to access data from the database frequently, Then each sentence of the access statement you call the method to transform, it is obviously not feasible.

            write here, I say a little more, my own understanding for the latter two methods, Should be in the early driver is not perfect when the problem of the solution, it is possible that the driver of the two encoding conversion is not very good implementation, resulting in the access garbled problem, I have seen on the internet someone by modifying the MYSQL-JDBC driver to solve the problem, Because the MYSQL-JDBC drive is open source, he compiles the class file by modifying the program, and then runs without any problems, very bull people.
            my own understanding: Now the driver has been perfected, so when accessing the database, there will be no garbled problem, Garbled problem is only the database using the encoding method is not Chinese, then when inserting Chinese data, it does not display correctly, we just need to modify the database encoding way OK.

also note : Different versions of MySQL will have different results, this is very strange, some people on the web said that MySQL database is binary storage, support any data, Of course including Chinese, you enter it under the MySQL command line:
insert into stu values (4, ' John Doe ', ' Boy ');
Select * from Stu;
John Doe is displayed correctly, and if you insert the record in Java and then view it in MySQL , you will see it. , he means to prove that the reason for garbled appearance is in the Java and MySQL connection section, that is, the driver does not translate correctly. I've tested this in Mysql-5.0.51a-win32, but you're testing in the mysql-5.0.41-win32_178 we're using now, and if MySQL is encoded as latin1, when you insert a Chinese record in the MySQL command, An error occurred: error:1366 (HY000) Incorrect string value: .....
that is, in the mysql-5.0.41-win32_178, if the code is latin1, it does not allow us to insert the Chinese data directly, But I do not know why in the Mysql-5.0.51a-win32 can, this is a question, some people can give answers, in this first thanks.

Java link MySQL database and garbled