tableWrite2.java

//20131219
import java.sql.*;

public class tableWrite2 {
static String classname = "com.mysql.jdbc.Driver";
static String jdbcURL = "jdbc:mysql://localhost/";
static String UID = "root";
static String PWD = "root";
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 = "";
static String sqlString = "";
static String db = "";
static String action = "";

public static String go() {
sqlString = null;
sqlString = "";
String iSQL = "";
String uSQL = "";
String dSQL = "";

if (action.equals("Delete") || (action.indexOf("Delete")>=0) ){
dSQL = "delete from "+table ;
dSQL = dSQL+ " where " + key + " = " + value + " ;";
sqlString= dSQL;
} else {
if (key.trim().equals("") || key.trim().equals("insert")) {
String mfields = fields.replace("~", ",");
String mvalues = values.replace("~", ",");
sqlString = "insert IGnore into " + table + " ( " + mfields
+ ") value (" + mvalues + " ) ;";
iSQL = sqlString;
} 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+db, UID, PWD);
if (!iSQL.trim().equals("")) {
InsertNew(iSQL);
}
if (!uSQL.trim().equals("")) {
UpdateNew(uSQL);
}
if (!dSQL.trim().equals("")) {
UpdateNew(dSQL);
}
// conn.commit(); // it cannot use it will hand 20131119 20131114
conn.close();
} catch (Exception sqle) {
System.out.println(sqle);
sqlString = sqlString + "\n" + sqle;
System.exit(1);
}
return "\n" +action+" " +sqlString + "*****20131114***********\n";
}

public static void main(String argv[]) {
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 uSQL = "update " + argv[0]
+ " set nameinchinese='????' where employeenumber=63";
String dSQL = "delete from " + argv[0] + " where ID=5";

try {
Class.forName(classname).newInstance();
conn = DriverManager.getConnection(jdbcURL+db, UID, PWD);
System.out.println("Display current content");
ShowResults(aQuery);
System.out.println("\nInserting a new record .....");
ShowResults(aQuery);
System.out.println("\nUpdateing a record .....");
UpdateNew(uSQL);
ShowResults(aQuery);
System.out.println("\nDeleting a record .....");
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();
aStatement.executeUpdate(new String(uSQL.getBytes("ISO-8859-1"),
"UTF-8"));
} catch (Exception e) {
sqlString = sqlString + e;
}
}

private static void InsertNew(String iSQL) {
try {
Statement aStatement = conn.createStatement();
aStatement.executeUpdate(new String(iSQL.getBytes("ISO-8859-1"),
"UTF-8"));
} catch (Exception e) {
sqlString = sqlString + e;
}
}

private static void ShowResults(String aQuery) {
try {
Statement aStatement = conn.createStatement();
ResultSet rs = aStatement.executeQuery(aQuery);
ResultSetMetaData rsmeta = rs.getMetaData();
int cols = rsmeta.getColumnCount();
for (int i = 1; i <= cols; i++) {
if (i > 1)
System.out.print("\t");
System.out.print(rsmeta.getColumnLabel(i));
}
System.out.print("\n");
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("\n");
}
rs.close();
aStatement.close();
} catch (Exception e) {
System.out.println("Exception Occurs.");
}
}
}