OOP-ResearchMake It Simpler by Object Oriented Programming

MySQL, JDBC PreparedStatement, Shift_JIS, EUC-JP, BIG5, GB2312, Java

With MySQL JDBC driver, we can store Java String with 2 bytes characters, such as Japanese Shift_JIS, Chinese BIG5 or GB2312. JDBC PreparedStatement can be executed to query by Japanese language.

This documentation describes how to store 2 bytes String into MySQL. MySQL is the world fastest SQL database. While MySQL can be compiled for one specific character set (charset), such as Japanese ShiftJIS, it is desirable to store String with multiple charsets. For example, SQL database for e-commerce should accept Chinese GB2312 as well as Japanese EUC-JP. With MySQL JDBC driver, we can store Java String with 2 bytes characters, such as Japanese Shift_JIS, Chinese BIG5 or GB2312. PreparedStatement can be executed to query by Japanese language or Chinese, even if MySQL compiled just for ASCII. By OOP-Research.


Java and MySQL

MySQL is the world fastest SQL database, which is available for most platform. Through JDBC driver, our Java programs can insert and query the data on MySQL. MM Mysql Driver is the complete JDBC2.0 implementation, by which we can use PreparedStatement. Once the instance of PreparedStatement is retrieved, we can execute it again and again along with the different parameters. For example:


    private static final String SQL_DRV=
         "org.gjt.mm.mysql.Driver";
    private static final String SQL_URL=
         "jdbc:mysql://localhost/foo";
    private static final String INSERT=
         "INSERT INTO my_tbl (col_1,col_2) VALUES(?,?)";
.....
    Class.forName(SQL_DRV);
    Connection con=
         DriverManager.getConnection(SQL_URL,usr,pss);
    PreparedStatement ps=con.prepareStatement(INSERT);
    String col_1_str=null;
    String col_2_str=null;
    for(int i=0; col_1s.length; i++){
        // col_1s and col_2s are the arrays of Strings
        col_1_str=col_1s[i];
	col_2_str=col_2s[i];
	ps.setString(1,col_1_str);
	ps.setString(2,col_2_str);
	ps.executeUpdate();
    }
    ps.close();
    con.close();
.....


The 2 arrays of Strings are inserted into my_tbl by this simple code.

Go Site Map

Java is Internationalization ready

Within our Java code, once the instance of String is created, we need not pay any attention whether it consists of all the ASCII characters or not. For example:


   private static final String RESOURCE="MyResource";
   try{
      ResourceBundle res=ResourceBundle.getBundle(RESOURCE);
      button_title=res.getString("button");
      ....
      }
   catch(Exception ex){
      ....
   }
   JButton mybutton=new JButton(button_title);

In the Japanese language environment, JVM tries to find MyResource_ja.properties in its CLASSPATH and parse its contents if found. In case that MyResource_ja.properties does not exist in the CLASSPATH, MyResource.properties will be parsed as the default property file instead. Anyway, the instance of ResourceBundle is created based on the property file, and it provides us with the instance of String appropriate for the runtime environment.
Thus, the instance of String with NON-ASCII characters can easily be created. And such an instance of String can be treated as if it is all the ASCII String.
To create the property file for NON-ASCII characters, we should use native2ascii tool which is supplied with JDK. For details about these features, please read the JDK documentation or Java Tutorial.
As for the Strings on Swing GUI, you can write them in the XML file. This can easily be done by OOP XMLPanelEdit.
Go Site Map

And MySQL can be!

But what happens to store the NON-ASCII Strings into SQL database? When MySQL is compiled for ASCII charset, all the NON-ASCII Strings inserted into table was corruped. Query on such a culumn fails. Although MySQL can be compiled for the specific charset, it cannot be capable for the Strings other than that charset. How happy we are if MySQL can be Internationalization ready as Java. If it is, we can take the full advantage of Java and MySQL can serve as SQL database behind the web application for almost all the language in the world!
And MySQL can be! Any NON-ASCII Strings can be converted into all the ASCII Strings using UTF8 byte array. It looks like this:


    private static final String SQL_DRV=
        "org.gjt.mm.mysql.Driver";
    private static final String SQL_URL=
        "jdbc:mysql://localhost/foo";
    private static final String INSERT=
        "INSERT INTO my_tbl (col_1,col_2) VALUES(?,?)";
.....
    Class.forName(SQL_DRV);
    Connection con=
        DriverManager.getConnection(SQL_URL,usr,pss);
    PreparedStatement ps=con.prepareStatement(INSERT);
    String col_1_str=null;
    String col_2_str=null;
    byte[] col_1_b=null;
    byte[] col_2_b=null;
    for(int i=0; col_1s.length; i++){
        // col_1s and col_2s are the arrays of Strings
	// Now, some of them can be NON-ASCII String
        col_1_str=col_1s[i];
	col_2_str=col_2s[i];
	col_1_b=col_1_str.getBytes("UTF8");
	col_2_b=col_2_str.getBytes("UTF8");
	col_1_str=new String(col_1_b,"8859_1");
	col_2_str=new String(col_2_b,"8859_1");
	ps.setString(1,col_1_str);
	ps.setString(2,col_2_str);
	ps.executeUpdate();
    }
    ps.close();
    con.close();
.....


In the col_1 and col_2, the Strings which consists of only the ASCII characters are stored. So, some Strings with Japanese ShiftJIS and another with Chinese BIG5 can be stored in the same column of the single table, by MySQL.
Query on this kind of column can be done by the same way:

    private static final String SELECT=
        "SELECT * FROM my_tbl where col_1=?";
....
    PreparedStatement ps=con.prepareStatement(SELECT);
    // query_str may be NON-ASCII String
    byte[] query_b=query_str.getBytes("UTF8");
    query_str=new String(query_b,"8859_1");
    ps.setString(1,query_str);
    ResultSet rs=ps.executeQuery();
    while(rs.next()){
	col_1_str=rs.getString(1);
	col_2_str=rs.getString(2);
	col_1_b=col_1_str.getBytes("8859_1");
	col_2_b=col_2_str.getBytes("8859_1");
	col_1_str=new String(col_1_b,"UTF8");
	col_2_str=new String(col_2_b,"UTF8");
    }
    rs.close();


Note that resulting String must be reverted through the procedure in reverse order.

Go Site Map

Caution!
All the APIs for Servlet/JSP introduced by this web site are now included in Bento framework:
  • Simpler than JSTL or Apache Struts
  • MVC framework by HTML
  • Input validation from CGI FORM
  • Easy user authentication
  • Easy localization (L10n)
To download the APIs and source code examples, please visit the web site of Bento framework.


JBuilder 2007


General Information

For Java Development

Java and all Java-based trademarks and logos are trademarks or registered of Sun Microsystems, Inc. in the United States and other countries.


ALL CONTENTS COPYRIGHT 1997-2007, OOP-Research Corporation. All rights reserved.