java-mysql-servlet-call-java class-utf8

//ISO-8859-1 is Latin1 (test in tomcat6 mysql )

mysql-utf8

// create table te22t (id numeric(5), name char(50) , aa smallint,bb smallint,cc smallint);
// root@it-dev:/hr/WEB-INF/classes# java NewJDBC te22t

import java.sql.*;

public class tableWrite2 {
static String classname = "com.mysql.jdbc.Driver";
static String jdbcURL = "jdbc:mysql://localhost/test";
static String UID = "UserName";
static String PWD = "Password";
static Connection conn = null;
static String key="";
static String values="";
static String value="";
static String result="";
static String table = "";
static String fields = "" ;
static String _r = "";
static String format="";


public static String go(){
String sqlString = "";
String iSQL = "";
String uSQL = "";
String dSQL = "";
if (key.trim().equals("") || key.trim().equals("insert") ){
String mfields=fields.replace("~",",");
String mvalues=values.replace("~",",");
sqlString="insert IGnore into "+table+" ( "+mfields+") value ("+mvalues +" ) ;";
} else {
sqlString="update "+ table+ " set ";
String updateFields[]=fields.split("~");
String updateValues[]=values.split("~");
String delimiter="";
for (int rr = 0; rr< updateFields.length;rr++){
sqlString=sqlString+delimiter+
updateFields[rr]+" = "+updateValues[rr];
delimiter=",";
}
sqlString=sqlString+ " where " +key +" = "+value+" ;" ;
uSQL=sqlString;
}
try {
Class.forName(classname).newInstance();
conn = DriverManager.getConnection(jdbcURL,UID,PWD);
if (! iSQL.trim().equals("")){
InsertNew(iSQL);
}
if (! uSQL.trim().equals("")){
UpdateNew(uSQL);
}
conn.close();
} catch (Exception sqle) {
System.out.println(sqle);
System.exit(1);
}
return "\n"+sqlString;
}


  public static void main( String argv[] ) {
    // initialize query string
    if(argv.length != 1) {
      System.out.println("Usage: java NewJDBC ");
      System.out.println("   ex. java NewJDBC Product");
      System.exit(2);
    }
    String aQuery = "select * from " + argv[0];
    //String iSQL = "insert into " + argv[0] + " values(5,'繁體鍵盤',14.5,2)";
//iSQL="insert into te22t  values(5,'a',14,5,2);";
// nameinchinese,employeenumber
//String uSQL = "update " + argv[0] + " set nameinchinese='abcde' where employeenumber=63";
    String uSQL = "update " + argv[0] + " set nameinchinese='無線鍵盤' where employeenumber=63";
//String uSQL = "update " + argv[0] + " set Name='無線鍵盤' where ID=5";
    String dSQL = "delete from " + argv[0] + " where ID=5";

    try {
      // load the JDBC-ODBC bridge driver
      Class.forName(classname).newInstance();

      // connect to Database
      conn = DriverManager.getConnection(jdbcURL,UID,PWD);

      // Display current content
      System.out.println("Display current content");
      ShowResults(aQuery);

      // Insert a new record
      System.out.println("\nInserting a new record .....");
      //InsertNew(iSQL);
      ShowResults(aQuery);

      // Update record
      System.out.println("\nUpdateing a record .....");
      UpdateNew(uSQL);
      ShowResults(aQuery);

      // Delete record
      System.out.println("\nDeleting a record .....");
      //20131101 DeleteNew(dSQL);
      ShowResults(aQuery);

      conn.close();
    } catch (Exception sqle) {
      System.out.println(sqle);
      System.exit(1);
    }
  }

  private static void DeleteNew(String dSQL) {
    try {
      Statement aStatement = conn.createStatement();
      aStatement.executeUpdate(dSQL);
    } catch (Exception e) {
      System.out.println("Delete Error: " + e);
      System.exit(1);
    }
  }

  private static void UpdateNew(String uSQL) {
    try {
      Statement aStatement = conn.createStatement();

      // ISO-8859-1 其實就是 latin1 的編碼
      // 以下的目的是要把 UTF-8 的字串轉成 byte array 然後再轉成符合
      // 目前的 latin1 的編碼
      // aStatement.executeUpdate(new String(uSQL.getBytes("UTF-8"), "ISO-8859-1"));
  aStatement.executeUpdate(new String(uSQL.getBytes("Source"), "Target"));
  aStatement.executeUpdate(new String(uSQL.getBytes("Webpage"), "MySql"));
 aStatement.executeUpdate(new String(uSQL.getBytes("ISO-8859-1"), "UTF-8"));
    } catch (Exception e) {
      System.out.println("Update Error: " + e);
      System.exit(1);
    }
  }

  private static void InsertNew(String iSQL) {
    try {
      Statement aStatement = conn.createStatement();

      // 與 UpdateNew 一樣
      //aStatement.executeUpdate(new String(iSQL.getBytes("UTF-8"), "ISO-8859-1"));
 aStatement.executeUpdate(new String(iSQL.getBytes("ISO-8859-1"), "UTF-8"));
    } catch (Exception e) {
      System.out.println("Insert Error: " + e);
      System.exit(1);
    }
  }


  private static void ShowResults(String aQuery) {
    try {
      // Construct a SQL statement and submit it
      Statement aStatement = conn.createStatement();
      ResultSet rs = aStatement.executeQuery(aQuery);

      // Get info about the query results
      ResultSetMetaData rsmeta = rs.getMetaData();
      int cols = rsmeta.getColumnCount();

      // Display column headers
      for(int i=1; i<=cols; i++) {
        if(i > 1) System.out.print("\t");
        System.out.print(rsmeta.getColumnLabel(i));
      }
      System.out.print("\n");
      // Display query results.
      while(rs.next()) {
        for(int i=1; i<=cols; i++) {
          if (i > 1) System.out.print("\t");
          //System.out.print(new String(rs.getString(i).getBytes("ISO-8859-1"),"UTF-8"));
 System.out.print(new String(rs.getString(i).getBytes("ISO-8859-1"),"UTF-8"));
        }
        System.out.print("\n");
      }

      // Clean up
      rs.close();
      aStatement.close();
    }
    // a better exception handling can be used here.
    catch (Exception e) {
      System.out.println("Exception Occurs.");
    }
  }
}