SpringMvc + Hibernate + Mysql saves emoticon characters (nicknames) to the database and reports an error ?, Springmvchib.pdf

SpringMvc + Hibernate + Mysql saves emoticon characters (nicknames) to the database and reports an error ?, Springmvchib.pdf

Background:

A small or medium H5 game

 

Description:

The game obtains the user nickname through authorized login and saves the user information to the Mysql database. When some user nicknames contain expressions (special characters), an error occurred while saving the game to the database!

 

Core Error:

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\xA4\xB4\xF0\x9F...' for column 'nick_name' at row 7at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3536)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3468)at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1957)at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107)at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2086)at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2371)at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2289)at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2274)at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2595)... 77 more

　　

Problem Analysis:

After reading the online materials, I learned that "The Special emoticon character set is 4 bytes, while the utf8 character set only supports 1-3 bytes. Changing the database-related character set to utf8mb4 can solve the problem, the premise is that Mysql 5.5 or above is required; otherwise, this character set is not supported"

 

Repair practices:

Step 1: Modify user table> nickname field character set to utf8mb4... the file cannot be saved after testing and the same error is reported.

Step 2: Modify the user table character set to utf8mb4 on the basis of the above... the user table cannot be saved after testing and the same error is reported.

Step 3: Modify the database character set to utf8mb4 on the basis of the above... after testing, the database still cannot be saved and the same error is reported.

Step 4: Modify the Mysql configuration file my. ini, which includes: 1. default-character-set = utf8mb4 2. character-set-server = utf8mb4 in short, utf8 is changed to utf8mb4, and Mysql service is restarted... it is tested that using Mybatis in this state can smoothly store emoticon characters into the database, but it still cannot be saved using Hibernate, and the same error is reported.

Step 5: modify the code BaseDaoImpl. java at the persistent layer of the project, and add a piece of code before inserting and modifying the record... after testing, the emoticon is successfully saved to Mysql.

 

Related code:

"GetSession (). connection (). prepareStatement (" set names utf8mb4 "cmd.exe cute ();"

　　

    public T save(T entity) {        Assert.notNull(entity);        try {            getSession().connection().prepareStatement("set names utf8mb4").execute();        } catch (Exception e) {            e.printStackTrace();        }        getSession().save(entity);        return entity;    }    public Object update(Object entity) {        Assert.notNull(entity);        try {            getSession().connection().prepareStatement("set names utf8mb4").execute();        } catch (Exception e) {            e.printStackTrace();        }        getSession().update(entity);        //getSession().flush();        return entity;    }    public Object saveOrUpdate(Object entity) {        Assert.notNull(entity);        try {            getSession().connection().prepareStatement("set names utf8mb4").execute();        } catch (Exception e) {            e.printStackTrace();        }        getSession().saveOrUpdate(entity);        return entity;    }