Java Insert and Read Utf-8 in Mysql Example
In most cases trouble happens because not everything is coded with UTF-8 or we are not doing the transcoding properly. We must tell MySQL how to store, send or receive the Unicode Transformation Format - 8 bit (UTF-8). The easiest way to avoid any trouble is by setting everything to UTF-8.
1 - Script to Create a Database and a Table UTF-8
- You can store data in this format, to do so you must use the right collation for UTF-8. Have in mind that collations may affect performance.
CREATE DATABASE `test_db` /*!40100 COLLATE 'utf8_bin' */;
USE `test_db`;
CREATE TABLE `test_table` (
`text` VARCHAR(50) NULL
)
COLLATE='utf8_bin'
ENGINE=InnoDB;
- If you are using an already created database you must make sure it supports UTF-8 or make some changes at least in one column table to make it compatible with UTF-8.
Change database:
ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_bin;
Change table:
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8;
Change column:
ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin;
2 - Code Snipet
- You need to download a MySQL JDBC driver and set it in your classpath to run this example
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class MySQLUtf8 { public static void main(String[] args) throws Exception { Connection myCon = null; try { String userName = "myuser", password = "mypass";// change accordingly String url = "jdbc:mysql://localhost:3306/test_db?useUnicode=true&characterEncoding=utf-8";// change accordingly Class.forName("com.mysql.jdbc.Driver").newInstance(); myCon = DriverManager.getConnection(url, userName, password); PreparedStatement preparedStmt; try { String sql = "INSERT INTO test_table (mytext) values (?)"; preparedStmt = myCon.prepareStatement(sql); preparedStmt.setString(1, "beautiful /ˈbjuːtɪfʊl/ 美麗 美しい جميل"); preparedStmt.executeUpdate(); preparedStmt = myCon.prepareStatement("SELECT mytext from test_table "); ResultSet rs = preparedStmt.executeQuery(); while (rs.next()) { System.out.println("-->" +rs.getString("mytext") + "<--"); } preparedStmt.close(); } catch (SQLException e) { e.printStackTrace(); } } catch (Exception e) { System.err.println(e.getMessage()); } finally { if (myCon != null) { try { myCon.close(); } catch (Exception e) { } } } } }
3 - Make sure your Eclipse is set to UTF-8 your Code and Console.
- From your package explorer in eclipse left click on your class file > properties > Resource > Text Encoding. (Set to UTF-8 and click Apply)
- Your Eclipse Console must be UTF-8. Go to Run > Run Configurations > Commons and set Encoding to UTF-8 (Click Apply)
4 Run the Example
- Remember to change your user, password and url accordingly.
Note 1 - With Tomcat Connection Pool
Add connectionProperties="useUnicode=yes;characterEncoding=utf8;" to your context.xml e.g.
<Resource name="jdbc/mysql" auth="Container" type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/test_db" connectionProperties="useUnicode=yes;characterEncoding=utf8;" username="yourUser" password="YourPassword" maxActive="10" maxIdle="0" maxWait="45" maxAge="45" removeAbandoned="true" removeAbandonedTimeout="45" />
Additional Notes
- Mind both JDBC driver and MySQL versions.