Special INSERT encoding with Java + mySQL from CSV

Question

I have a little problem, I am creating a database from CSV files using a Java application connected to mySQL database.

CSV is encoded by ISO-8859-1. It is read using a buffered file reader and parsed by String methods. Then the rows are entered into mySQL through the JDBC driver.

Problem: accents (this is a French application) are lost during the transfer. In the mySQL database, they are in an unrecognized format that is not UTF-8 nor Latin-1 ...

My hypothesis is that strings are encoded weirdly and retain this encoding upon reinstallation. How can I apply encoding for an INSERT statement in Java?

Answer 1

You need to make sure that you read the CSV using InputStreamReaderthe proper encoding (which is the very file itself, which in this particular case, therefore ISO-8859-1).

BufferedReader reader = new BufferedReader(new InputStreamReader(input, "ISO-8859-1"));

You also need to make sure that the JDBC connection string contains a parameter characterEncodingwith the appropriate encoding (which is the one with which the table was created that you have not yet defined in the MySQL database). If it looks like Unicode encoding, you also need to add a parameter useUnicode=true.

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

The next question is likely to be: How do I determine which encoding my database table uses ?. You can do this using the command SHOW. It will contain encoding information.

SHOW CREATE DATABASE dbname; -- shows CREATE DATABASE statement.
SHOW CREATE TABLE dbname.tblname; -- shows CREATE TABLE statement.

, , MySQL CSV Java/JDBC? LOAD DATA INFILE. CSV , MySQL .