Making MySQL utf-8 compatible

To solve the problem with smileys/ emoticons, the utf8mb4 is required since utf8 uses a maximum of three bytes per character and contains only BMP(Basic Multilingual Plane) characters. As of MySQL 5.5.3, the utf8mb4 character set uses a maximum of four bytes per character supports supplemental characters.

In the case of windows, you can do it by using workbench. But as most of the developers use Ubuntu, so we will proceed with a detailed solution for Ubuntu only. Following are the steps to make the database Smileys compatible:

1. Changes in MySQL config file

Open “/etc/mysql/mysql.cnf“ using any editor in case of ubuntu and change default-character-set, default-character-set, character-set-server and collation-server as follows

{client}

default-character-set = utf8mb4

{mysql}

default-character-set = utf8mb4

{mysql}

character-set-client-handshake = FALSE

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

and then restart the MySQL service.

*please change all the brackets to square brackets “[ ]” before restarting.

Now as you have successfully configured the file, let’s move to the next step.

2. Creating database and table in MySQL with utf8mb4 support

Run following codes:

CREATE DATABASE project_db
CHARACTER SET utf8
COLLATE utf8_general_ci;
CREATE TABLE STUDENT (name mediumtext COLLATE utf8mb4_unicode_ci)
ENGINE=InnoDB AUTO_INCREMENT=6
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

Making API utf-8 compatible in Spring

To modify the API we need to add some properties in the dispatcher-servlet.xml file. Following 3 properties are must for making the API utf-8 compatible so as to solve the problem with smileys in Spring.

1. Add the following hibernate properties in session factory bean definition

class=”org.springframework.orm.hibernate4.LocalSessionFactoryBean”>

org.hibernate.dialect.MySQL5Dialect

true

utf8mb4

utf8

2. Follow the following instructions

Set useUnicode=true and characterEncoding=UTF-8 and add characterEncoding property in connectionProperties. The property that you need to add is as follows:

        UTF-8

        true

3. And the last step is

Add request mapping produces = “text/plain;charset=UTF-8“

E.g :  @RequestMapping(value=”/sumit/api”,produces=”text/plain;charset=UTF-8″)

  @ResponseBody public void create(@RequestParam String input)

{

ResponseEntity(“output”, HttpStatus.OK);

}

Now the problem with smileys would have been successfully solved. If you are still facing any problem, do discuss with us in comments.
We would be very happy to help you.