java - JSP encoding while inserting non-English text in MySQL database

Question

I am using Ajax call to insert Indian characters in MySQL database. I am facing an UTF-8 encoding problem in between flow of my application.

When I am inserting the non-English characters directly by JDBC (not using an Ajax call), then it's showing "????" in the database.

When I include

response.setCharacterEncoding("UTF-8");
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");

in my JSP file, then I receive the following in my database (question marks instead of non-English characters):

????????

When I do not include above lines it shows me junk character like this in database:

àa?à??àa?àaaà??àa·à??àa

Whereas the actual value is

????

So the actual problem lies in or after sending insert request to MySQL command in JSP through JDBC jdbc connector.

I have included following tags in all my JSP files to ensure character encoding:

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>

and

<meta http-equiv="Content-Type" content="text/html; charset=utf-8;charset=UTF-8">

I checked MySQL tables are Unicode enabled and I am able to enter correctly non English text through terminal.

How is this problem caused and how can I solve it?

Now, i am able to write using a insert statement only....but when i mix some queries and insert statement then... my application return me following error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' following are my database variables:

| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8_general_ci            |
| collation_database       | utf8_general_ci            |
| collation_server         | utf8_general_ci            |
| completion_type          | 0                          |
| concurrent_insert        | 1                          |
| connect_timeout          | 10                         |

Answer 1

When I am inserting the non-English characters directly by JDBC (not using an Ajax call), then it's showing "????" in the database.

This will only happen when the both sides are perfectly aware of the character encoding differences in each side. Any character which is not covered by the character encoding used on the other side will be replaced by a question mark ?. Otherwise you would have seen Mojibake.

In this particular case, those sides are the Java side and the database side, with the JDBC driver as mediator. To fix this, you need to tell the JDBC driver what encoding those characters are in. You can do that by setting the useUnicode=true&characterEncoding=UTF-8 parameters in the JDBC connection URL.

jdbc:mysql://localhost:3306/dbname?useUnicode=true&characterEncoding=UTF-8

Then, depending on how you're sending the parameters from the client to server, you possibly also need to fix the request encoding. Given the fact that you're seeing Mojibake when you removes request.setCharacterEncoding("UTF-8"), you're using POST. So that part is fine.

For the case that, if you were using GET to send the parameters, you would need to configure URI encoding in the server side. It's unclear what server you're using, but in case of for example Tomcat, it's a matter of editing the <Connector> entry in /conf/server.xml as follows:

<Connector ... URIEncoding="UTF-8">

See also:

• Unicode - How to get the characters right?