Bulk INSERT and garbled resolution in JDBC

Character Set-garbled problem

When using JDBC to access the MySQL database, if JDBC uses a character set that is inconsistent with the character set used by MySQL, it can cause garbled characters. The workaround was to specify the same character set as the database when using JDBC. We can specify the character set used by JDBC by adding "? characterencoding=xxx" to the Db_url string.

At the same time, we need to know the character set used in MySQL, which can be obtained by the following two commands.

1) Show variables like '%character% '; Gets the character set used by the database or the entire server.

As you can see, the entire database server and the current database used by the author are all GBC encoded.

2) Show create table user; Get the character set used by the user table

It shows that the character set used by the current user table is also GBK. In fact, we can also set character sets for each column.

So which of these levels of character sets, exactly? In fact, they have a priority:

column> table> Database > Server

BULK INSERT Issues

There are times when we need to insert a large number of data into the database, and it is very slow to use plain SQL statements to insert a database into the databases. At this point we can use statement's BULK insert data interface to implement functionality.

Example code:

import java.sql.Connection; import Java.sql.DriverManager; import Java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; class User { Private String userid; Private String name; Private String password; Public User (string userid, string name, string password) { this. Setuserid (userid); this. SetName (name); this. SetPassword (password); } Public String GetUserid () { return userid; } Public void Setuserid (String userid) { this. userid = userid; } Public String GetName () { return name; } Public void SetName (String name) { this. Name = name; } Public String GetPassword () { return password; } Public void SetPassword (String password) { this. Password = password; } } Public class Hello { Static Final String jdbc_driver = "Com.mysql.jdbc.Driver"; Static String Db_url = "JDBC:MYSQL://LOCALHOST:3306/MLDN"; Static Final String USERNAME = "root"; Static Final String PASSWORD = "admin"; Public Static void Main (String a[]) { Connection Conn = null; Statement stmt = null; ResultSet rs = null; list<user> Listuser = Hello. GetUser (); Try { Load Driver Class. forname (jdbc_driver); Get links db_url + = "? CHARACTERENCODING=GBK"; conn = DriverManager. getconnection (db_url, USERNAME, PASSWORD); Execute SQL statement stmt = Conn.createstatement (); for (User item : Listuser) { Stmt.addbatch ("INSERT into User" (Userid,name,password) VALUES (' "+item.getuserid () +" ', ' "+item.getname () +" ', ' "+ Item.getpassword () + "');"); } Stmt.executebatch (); Stmt.clearbatch (); } Exception handling Catch (classnotfoundexception e) { TODO auto-generated Catch block E.printstacktrace (); } Catch (SQLException e) { TODO auto-generated Catch block E.printstacktrace (); } finally { Resource cleanup Try { Conn.close (); Stmt.close (); rs. Close (); } Catch (Exception e) { Ignore exceptions when closing. } } System. out. println ("///done~~"); } Public Static List<user> getUser() { list<user> userlist = new arraylist<user> (); Userlist.add (new User ("Cyx", "Eason Chan", "cyx999")); Userlist.add (new User ("Zjl", "ZHOU Jielun", "zjl123")); Userlist.add (Thenew User ("CJK", "Cang Jing," "cjk520")); Userlist.add (new User ("ly", "LIU YAN", "ly1988")); return userlist; } }

Bulk INSERT and garbled resolution in JDBC