Unicode with PHP and Java in MySQL

You may experience problems accessing your unicode data from Java on your database if you have used PHP to insert those data in there. The reason is that PHP saves the data using Latin1 encoding by default. Sometimes you cannot change the encoding back to real unicode so here is the solution to resolve this issue.  This problem arises when you need to run a query and select a row based on a unicode column such as usernames which contain unicode characters.

Using this method, you can insert  and access data in both PHP and Java without encoding issues. Here is the solution to overcome this issue:
1- Use utf8 encoding in the connection string
String connectionString = "jdbc:mysql://127.0.0.1/databasename?".
                    "useUnicode=true&characterEncoding=utf8&".
                    "connectionCollation=utf8_general_ci";
2- Create a function to set the encoding to utf8 and run it before a select statement:

public void setUTF8()

{

  PreparedStatement pstmt;

  try 
  {

    pstmt = con.prepareStatement("set names utf8" );    

    pstmt.executeUpdate();

    pstmt.close();

  }

  catch (SQLException e) 

  {

    e.printStackTrace();

  }

}

3- Create a function to set the encoding to latin1 and run it before every update or insert statement:

public void setLatin1()

{

  PreparedStatement pstmt;

  try 
  {

    pstmt = con.prepareStatement("set names latin1" );    

    pstmt.executeUpdate();

    pstmt.close();

  }

  catch (SQLException e) 

  {

    e.printStackTrace();

  }

}

This provides perfect compatibility between PHP and Java for accessing and adding data in your database.

4- Convert the unicode column to utf8 from latin1 in your MySQL using the Convert and Cast functions:

select CONVERT(CAST(username as binary) USING utf8) from users;

Here is an example of accessing the data:
String username = "مجید";
String sqlQuery = "select * from users where".
                  " CONVERT(CAST(username as binary) USING utf8) = ? ";
setUTF8();
try{
Connection con = myDB.getConnection();
PreparedStatement query = con.prepareStatement(sqlQuery);
query.setString(1 , username);
rs = query.executeQuery();
if(rs.next())
{
// Login successful
}
rs.close();
query.close();
}
catch (SQLException ex)
{
ex.printStackTrace();
}


// Insert unicode data in latin1 encoding in Java

setLatin1();

sqlQuery = "insert into users (username) values (?)";


try{
Connection con = myDB.getConnection();
PreparedStatement query = con.prepareStatement(sqlQuery);
query.setString(1 , username);
query.executeUpdate();
query.close();
}
catch (SQLException ex)
{
ex.printStackTrace();
}

About majid

Software engineer, Web developer and IT graduate. Profile: View My Profile
This entry was posted in Scripts. Bookmark the permalink.