Geek Answers Handbook

Cannot save UTF-8 content in MySQL using Java PreparedStatement

For some strange reason, I cannot add UTF-8 data to my MySQL database. When I enter a non-Latin character, it is saved as. Everything else is kept in order. So, for example, “this example® ™” is stored in order, but “和 英 辞典” is stored as “????”.

Link to connection in order:

private DataSource getDB() throws PropertyVetoException {
    ComboPooledDataSource db = new ComboPooledDataSource();
    db.setDriverClass("com.mysql.jdbc.Driver");
    db.setJdbcUrl("jdbc:mysql://domain.com:3306/db?useUnicode=true&characterEncoding=UTF-8");
    db.setUser("...");
    db.setPassword("...");
    return db;
}

I use PreparedStatement, as you would expect, I even tried typing "set names utf8" as someone suggested.

    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = db.getConnection();

        stmt = conn.prepareStatement("set names utf8");
        stmt.execute();
        stmt = conn.prepareStatement("set character set utf8");
        stmt.execute();

                    ... set title...
        stmt = conn.prepareStatement("INSERT INTO Table (title) VALUES (?)");
        stmt.setString(1,title);

        stmt.execute();
    } catch (final SQLException e) {
    ...

The table itself seems beautiful.

Default Character Set: utf8
Default Collation: utf8_general_ci
...
Field title:
Type text
Character Set: utf8
Collation: utf8_unicode_ci

I tested it by typing in Unicode ("和 英 辞典" specifically) using a graphical editor, and then selecting from a table - and it was returned just fine. So this seems like a problem with JDBC.

What am I missing?

+5
6

mysql 2 , UTF-8.

:

CREATE DATABASE 'db' CHARACTER SET 'utf8';

UTF-8 ( )

CREATE TABLE  `Table1` (
    [...]
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci

, utf8, JVM utf8, :

java -Dfile.encoding=UTF-8 [...]


"**JAVA_TOOLS_OPTIONS**" to -Dfile.encoding="UTF-8"

, :

System.setProperty("file.encoding" , "UTF-8");

( , JVM )

, .

+3

JDBC :

JDBC: MySQL://: 3306/_ characterEncoding = utf8

+5

stmt.setNString(...) stmt.setString(...).
.

+3

mysql show variables like 'character%'; .

Since you get a one-to-one multi-byte character to question mark relationship, most likely the connection will perform character set conversion and replace Chinese characters with the replacement character for single-byte characters.

+1
source

Also check locale -a on ubuntu by default Ubuntu works with en_us locale and does not have another locale set. should indicate characterEncoding = utf8 when connecting via JDBC.

0
source

add url at the end of your DB connection - (nothing else is needed) ex.

spring.datasource.url = jdbc:mysql://localhost:3306/dbname?characterEncoding=utf8
0
source

More articles:


All Articles