Can't SpringBoot+Mysql save emoj expression?

Especially remember a long time ago, if you want to save emoj expressions in mysql, you need to transcode emoj expressions and save them. Every time you read them, you need to decode and restore them. Every time you do this sb operation, you really feel the heart jam. Is there any way to save them directly?

mysql itself can support emoj expressions by selecting a coding set (such as utfbmb4). However, today, there is a rather ghost problem. Emoj expressions can be written directly in the table, but when the emoj is inserted through the spring boot code, an exception is thrown:

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x9D\xE6\xB1...' for column 'nick' at row 1
	at com.mysql.jdbc.SQLError.createSQLException( ~[mysql-connector-java-5.1.30.jar:na]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket( ~[mysql-connector-java-5.1.30.jar:na]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket( ~[mysql-connector-java-5.1.30.jar:na]
	at com.mysql.jdbc.MysqlIO.sendCommand( ~[mysql-connector-java-5.1.30.jar:na]
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect( ~[mysql-connector-java-5.1.30.jar:na]
	at com.mysql.jdbc.ConnectionImpl.execSQL( ~[mysql-connector-java-5.1.30.jar:na]
	at com.mysql.jdbc.PreparedStatement.executeInternal( ~[mysql-connector-java-5.1.30.jar:na]

Next, I will demonstrate the correct use posture and the wrong case s that cause the above problems, so as to avoid repeated mining

<!-- more -->

I. Emoj's journey of expression support

Next, our goal is to read or write emoj expressions directly to mysql

1. Table character set

First, for mysql tables, you need to specify the character set as utfbmb4

CREATE TABLE `Subscribe` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(140) NOT NULL DEFAULT '',
  `nick` varchar(30) NOT NULL DEFAULT 'Nickname?',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 Subscription not activated, 1 subscription activated, 2 unsubscribed',
  `created` int(13) NOT NULL DEFAULT '0' COMMENT 'Creation time',
  `updated` int(13) NOT NULL DEFAULT '0' COMMENT 'Update time'
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)

The character set of the above table is set to utf8mb4 directly. If a table already exists, but the character set is not utf8mb4, in this case, we can also set the coding of a column separately as follows

ALTER TABLE `Subscribe` CHANGE `nick` `nick` VARCHAR(30)  CHARACTER SET utf8mb4 NOT NULL  DEFAULT '';

With the above settings, we can add emoj directly to this table

2. SpringBoot support

Next, let's go to the main topic, spring boot project, how to support the insertion of emoj; first, let's look at the project dependency

    <relativePath/> <!-- lookup parent from repository -->



        <name>Spring Snapshots</name>
        <name>Spring Milestones</name>
        <name>Spring Releases</name>

We are using version 2.2.1.RELEASE, please make sure to introduce the dependency of spring boot starter JDBC and MySQL connector Java

Then configure the db related properties,

## DataSource

Then we can have a pleasant test

public class Application {

    public Application(JdbcTemplate jdbcTemplate) {
        log.warn("application start!!!");

        // Insert emoj expression
        jdbcTemplate.update("insert into Subscribe (`email`, `nick`) values (?, ?)",
                UUID.randomUUID().toString() + "", "🐺wolf");

        List<Map<String, Object>> r = jdbcTemplate.queryForList("select * from Subscribe order by id desc limit 2");"r: {}", r);

    public static void main(String[] args) {;

The measured results are as follows

This has been inserted successfully, so the problem is, what's the exception at the beginning of this article

3. Scene recurrence

The problem at the beginning of the article is mainly caused by the version problem of MySQL connector Java. Let's repeat it. First, specify the version as 5.1.30 (because this version is used internally, so the mining pit...)


Secondly, you need to specify driver class name in the environment configuration

spring.datasource.driver-class-name= com.mysql.jdbc.Driver

Be careful

It should be noted here that in the higher version of MySQL connector Java, it has been changed to com.mysql.cj.jdbc.Driver. If the above Driver is still configured, there will be a prompt when executing

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

Finally, execute the previous test code again, and the exception will come

4. summary

It can be said that there are many scenarios for storing emoj expressions in mysql. After all, in the 21st century, applications that do not support emoj have no future. Through the previous case, I introduced how to correctly make springboot applications support emoj expressions, and also gave a pit caused by version problems

emoj support steps

  • The first is source support. You need to modify the character set of mysql table or some columns to utf8mb4
  • Pay attention to the MySQL connector java version introduced. Be sure to select a newer version,
    • For example, springboot2.2.1.RELEASE is 8.0.18 by default
    • 5.1.30 in our demonstration does not support emoj insertion
  • Driver class. In the new version, com.mysql.cj.jdbc.Driver has been used to replace the previous com.mysql.jdbc.Driver

II. other

0. project

1. A grey Blog

The best letter is not as good as the above. It's just a one-of-a-kind remark. Due to the limited personal ability, there are inevitably omissions and mistakes. If you find a bug or have better suggestions, you are welcome to criticize and correct. Thank you very much

Here is a grey personal blog, recording all the blogs in study and work. Welcome to visit

Keywords: Programming MySQL Java Spring JDBC

Added by dvd420 on Fri, 13 Dec 2019 06:49:31 +0200