Can't persist emojis with mysql and hibernate

Question

I have actually found this problem multiple times on Stackoverflow, but the solutions would not help me.

I have a chat module in my android app and want to persist the messages in my server db, which works fine until special characters like emojis appear.

ERROR: Incorrect string value: '\xF0\x9F\x98\x81' for column 'message' at row 1
...
...
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x81' for column 'message' at row 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
... 23 more

My environment is:

-Mysql 5.6
-Tomcat 8.0.8
-Hibernate 4.3.5
-JDK 1.8.0_05

This is the used table with the column in question, 'message':

These are my properties in the persistence.xml (version 2.1):

<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/gamedb?useUnicode=true&amp;characterEncoding=UTF-8" />
<property name="javax.persistence.jdbc.user" value="*********" />
<property name="javax.persistence.jdbc.password" value="**************" />

<property name="hibernate.hbm2ddl.auto" value="update" />
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />
<property name="hibernate.connection.useUnicode" value="true" />
<property name="hibernate.connection.characterEncoding" value="utf8" />

Now I tried the following solutions without effect:

-Change datatype of 'message' from varchar to longtext
-Change collation of 'message' to utf8mb4
-Change collation of table to utf8mb4
-Append url with "?useUnicode=true&characterEncoding=UTF-8"
-Set character-set-server of mysql to utf8mb4

I think the emoji is correctly transmitted to the server, before it persists the message it broadcasts it back to the app and it gets correctly displayed.

Answer 1

I once had the same problem. I don't know a pretty solution but this had worked for me.

After I created the Session object I changed the connection collation by hand:

s.doReturningWork(new ReturningWork<Object>() {
    @Override
    public Object execute(Connection conn) throws SQLException
    {
        try(Statement stmt = conn.createStatement()) {
            stmt.executeQuery("SET NAMES utf8mb4");
        }

        return null;
    }
});

Answer 2

if you are using hibernate with c3p0, you can use c3p0 config connectionCustomizerClassName whitch you can set to a class doing work with connections c3p0 got.

example:

hibernate.cfg.xml

<property name="hibernate.c3p0.connectionCustomizerClassName">com.hzmoyan.newlyappserver.db.C3p0UseUtf8mb4</property>

C3p0UseUtf8mb4 class

public class C3p0UseUtf8mb4 extends  AbstractConnectionCustomizer{
     @Override
    public void onAcquire(Connection c, String parentDataSourceIdentityToken)
        throws Exception {
        super.onAcquire(c, parentDataSourceIdentityToken);
        try(Statement stmt = c.createStatement()) {
            stmt.executeQuery("SET NAMES utf8mb4");
        }
    }
}