Java + Mysql UTF8 Problem

as the title said, I have a problem between java and mysql

The mysql DB, tables, and columns are utf8_unicode_ci. I have an application that took some input from an xml, then compose the query...

public String [] saveField(String xmltag, String lang){     
  NodeList nodo = this.doc.getElementsByTagName(xmltag);
  String [] pos = new String[nodo.getLength()];     
  for (int i = 0 ; i < nodo.getLength() ; i++ ) {
     Node child = nodo.item(i);
     pos[i] =  "INSERT INTO table (id, lang, value) VALUES (" +
        child.getAttributes().getNamedItem("id").getNodeValue().toString() + " , " +
        lang + " , " + 
        "'" + child.getFirstChild().getTextContent() + "'" +
        ");";       
    }   
   return pos;
}

this method return an array of String that contains one or more SQL insert Query... then

Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql:///dbname", "user", "pass");
.....
Statement s; s =
this.con.createStatement ();
s.execute(query);

both with s.execyte and s.executeUpdate the special characters are stored as ?

so special char are not stored correctly: מסירות קצרות is stored as ?????????

Hi! is stored as Hi!

Any advice?

Thanks

Answers


Solved, I forgot to add the encoding when initializing Connection:

before was:

con = DriverManager.getConnection("jdbc:mysql:///dbname", "user", "pass");

now (working):

con = DriverManager.getConnection("jdbc:mysql:///dbname?useUnicode=true&characterEncoding=utf-8", "user", "pass");


AUGH!

Okay, so, this isn't directly the thing you asked for, but this:

 pos[i] =  "INSERT INTO table (id, lang, value) VALUES (" +
    child.getAttributes().getNamedItem("id").getNodeValue().toString() + " , " +
    lang + " , " + 
    "'" + child.getFirstChild().getTextContent() + "'" +
    ");";       

Set off all my internal "DON'T DO THIS" alarms.

Do you have absolute and complete control over the incoming text? Are you sure someone won't have an apostrophe in the incoming text, even by accident?

Instead of creating SQL text, please refactor your code so that you end up calling:

PreparedStatement pstmt =
    con.prepareStatement("INSERT INTO table (id, lang, value) VALUES (?,?,?)");
// then, in a loop:
pstmt.setString(0, child.getAttributes().getNamedItem("id").getNodeValue().toString());
pstmt.setString(1, lang);
pstmt.setString(2, child.getFirstChild().getTextContent());
pstmt.execute();

That is, let the DB escape the text. Please, unless someday you want to have a conversation like this one. As an advantageous side effect, this approach may solve your problem, assuming that the string values are still correct when you read them from the XML. (As someone else mentioned, it's very possible that things are getting messed up when you read from the XML)


Need Your Help

What is ST in PostGIS?

gis postgis spatial terminology

Almost all the functions in PostGIS start with ST. e.g. ST_Distance_Sphere, ST_GeomFromText, ST_Intersection, etc.

How can I make all images of different height and width the same via CSS?

html css image css3 resize

I am trying to create an image wall consisting of product photos. Unfortunately, all of them are of different height and width. How can I use css to make all images look the same size? preferably...