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(SQLError.java:1084) ~[mysql-connector-java-5.1.30.jar:na] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232) ~[mysql-connector-java-5.1.30.jar:na] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164) ~[mysql-connector-java-5.1.30.jar:na] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615) ~[mysql-connector-java-5.1.30.jar:na] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776) ~[mysql-connector-java-5.1.30.jar:na] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838) ~[mysql-connector-java-5.1.30.jar:na] at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082) ~[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`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
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
<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.2.1.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> </dependencies> <build> <pluginManagement> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </pluginManagement> </build> <repositories> <repository> <id>spring-snapshots</id> <name>Spring Snapshots</name> <url>https://repo.spring.io/libs-snapshot-local</url> <snapshots> <enabled>true</enabled> </snapshots> </repository> <repository> <id>spring-milestones</id> <name>Spring Milestones</name> <url>https://repo.spring.io/libs-milestone-local</url> <snapshots> <enabled>false</enabled> </snapshots> </repository> <repository> <id>spring-releases</id> <name>Spring Releases</name> <url>https://repo.spring.io/libs-release-local</url> <snapshots> <enabled>false</enabled> </snapshots> </repository> </repositories>
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, application.properties
## DataSource spring.datasource.url=jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false spring.datasource.username=root spring.datasource.password=
Then we can have a pleasant test
@Slf4j @SpringBootApplication 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() + "@t.com", "🐺wolf"); List<Map<String, Object>> r = jdbcTemplate.queryForList("select * from Subscribe order by id desc limit 2"); log.info("r: {}", r); } public static void main(String[] args) { SpringApplication.run(Application.class); } }
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...)
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.30</version> </dependency>
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
- A grey Blog personal Blog https://blog.hhui.top
- A grey blog spring special blog http://spring.hhui.top
