JDBC: Inserting unicode UTF-8 characters into MySQL
Just a quick heads up if you're looking to insert for example Chinese characters into a MySQL database.
Case:
You're using JDBC to insert strings with unicode characters from your Java application and are seeing ??? or empty strings instead of 是 or 了in your database.
How to fix it:
First:
If your Java application is reading the data from for example a text file. Make sure you have specified the right characters encoding in your call to the input stream. It should look something like this:
BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF-8" ) );Second:
Make sure you tell JDBC which encoiding to use. This is done as part of the query string when connecting to the DB.
Notice this part: ?useUnicode=yes&characterEncoding=UTF-8. This is where the magic happens.
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname?useUnicode=yes&characterEncoding=UTF-8","username", "password");Third:
Ensure your table uses UTF-8. I usually go with "Encoding: UTF-8 Unicode(utf8)" and "Collation: utf8_bin"
Cheers,
May the code be with you!
Written by Sebastian
Related protips
4 Responses
try {
Statement stmt;
ResultSet rs;
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/arabic?useUnicode=yes&characterEncoding=UTF-8";
Connection con = (Connection) DriverManager.getConnection(url, "root", "root");
// con.setCharacterEncoding("utf-8");
stmt = (Statement) con.createStatement();
stmt.executeQuery("SET NAMES 'UTF8'");
stmt.executeQuery("SET CHARACTER SET 'UTF8'");
String greekname = "κωνσταντίνα";
stmt.executeUpdate("INSERT INTO categories(category_id,category_name) VALUES ('" + 17 + "','" + greekname + "')");
con.close();
} catch (Exception e) {
System.out.println("problem during the connection with the database!"+e);
}
Awesome it works for me & solve my proublem.
Thanks
Sachin
Our application reads dburl from xml file where the above jdbc change can't be inserted. What to do in such scenario?
Will you can use "setNString " as show in Example
String Insr="Insert into PTTable "+"(PaID,Paname,PaAge,PaGender,PaDoctor,PaDate,TestID) VALues"+"(?,?,?,?,?,?,?)";
PreparedStatement stmt=(PreparedStatement)con.prepareCall(Insr);
stmt.setInt(1, PaID);
stmt.setNString(2,Paname);
stmt.setInt(3, PaAge);
stmt.setNString(4, PaGender);
stmt.setNString(5,PaDoctor);
stmt.setDate(6, Padate);
stmt.setInt(7, TestID);
stmt.executeUpdate();
