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.