DB2 JDBC 应用实例

/* * Created on 2006-4-26 * * TODO To change the template for this generated file go to Window - * Preferences - Java - Code Style - Code Templates */ package JDBC; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.PrintStream; import java.io.UnsupportedEncodingException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.Blob; import java.sql.Clob; import java.sql.Time; import java.sql.Date; import java.sql.ResultSetMetaData; import java.math.BigDecimal; import java.sql.Timestamp; import java.sql.Types; import java.util.Properties; public class DB { public String alias = "STLEC1"; public String server = "1.0.0.1"; public int portNumber = 446; // 0 indicates legacy type 2 connection, // < 0 use universal type 2 connection public String userId = "SYSADM"; public static String CRCN = "\r\n"; public String password = "gr8tcode"; protected Connection con = null; protected PrintStream log = System.out; /** * -1 -- disabled; 0 -- error or exception; 1 -- debug; 2 -- info; */ protected int logLevel = 2; public Connection getConnection() { return con; } public boolean isConnected() { return con != null; } public DB() { } /** * * <B>Purpose: convert a string representing a bytes in HEX format into a * bytes </B> * * @param hex * HEX represention of a bytes * @return the bytes */ public static final byte[] HexString2BinBytes(String hex) { if (hex == null || hex.length() < 1) return null; int bufsize = hex.length() / 2; byte[] rlt = new byte[bufsize]; int pos = 0; while (pos < hex.length() - 1) { String char_hex_value = hex.substring(pos, pos + 2); rlt[pos / 2] = (byte) Integer.parseInt(char_hex_value, 16); pos += 2; } return rlt; } /** * <B>Purpose: </B> * * @param bytes * the binary format of a string * @param enc * the codepage used to encode the bytes into a string * @return a string encoded by encoding scheme enc from binary bytes */ public static final String bytes2String(byte[] bytes, String enc) { String rlt = null; if (bytes == null || enc == null || enc.length() < 1) return null; ByteArrayOutputStream b = new ByteArrayOutputStream(bytes.length); try { b.write(bytes); rlt = b.toString(enc); } catch (IOException e) { e.printStackTrace(); return null; } return rlt; } public static String CharacterConversion(String strHEX, String INCCSID, String OUCCSID) { String result = ""; //System.out.print("INCCSID:" + INCCSID + "\tOUCCSID:" + OUCCSID); byte[] bytes = HexString2BinBytes(strHEX); result = bytes2String(bytes, INCCSID); try { bytes = result.getBytes(OUCCSID); result = new String(bytes, System.getProperty("file.encoding")); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } //System.out.println("\t TO STRING:" + result + "\t FROM HEX:" + // strHEX); return result; } public static String CharacterConversion(byte[] bytesHEX, String INCCSID, String OUCCSID) { String result = ""; //System.out.print("INCCSID:" + INCCSID + "\tOUCCSID:" + OUCCSID); byte[] bytes = bytesHEX; result = bytes2String(bytes, INCCSID); try { bytes = result.getBytes(OUCCSID); result = new String(bytes, System.getProperty("file.encoding")); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } //System.out.println("\t TO STRING:" + result + "\t FROM HEX:" + // strHEX); return result; } public void init(String argv[]) { if (argv.length > 5 || (argv.length == 1 && (argv[0].equals("?") || argv[0].equals("-?") || argv[0].equals("/?") || argv[0].equalsIgnoreCase("-h") || argv[0].equalsIgnoreCase("/h") || argv[0].equalsIgnoreCase("-help") || argv[0] .equalsIgnoreCase("/help")))) { info("Usage: prog_name [dbAlias] [userId passwd] (use legacy JDBC type 2 driver)\n" + " prog_name -u2 [dbAlias] [userId passwd] (use universal JDBC type 2 driver)\n" + " prog_name [dbAlias] server portNum userId passwd (use universal JDBC type 4 driver)"); } switch (argv.length) { case 0: // Type 2, use all defaults alias = "sample"; userId = ""; password = ""; break; case 1: // Type 2, dbAlias specified or Type 2 Universal if (argv[0].equalsIgnoreCase("-u2")) { alias = "sample"; portNumber = -1; } else { alias = argv[0]; } userId = ""; password = ""; break; case 2: // Type 2, userId & passwd specified if (argv[0].equalsIgnoreCase("-u2")) { alias = argv[1]; userId = ""; password = ""; portNumber = -1; } else { alias = "sample"; userId = argv[0]; password = argv[1]; } break; case 3: // Type 2, dbAlias, userId & passwd specified or Type 2 // Universal if (argv[0].equalsIgnoreCase("-u2")) { alias = "sample"; userId = argv[1]; password = argv[2]; portNumber = -1; } else { alias = argv[0]; userId = argv[1]; password = argv[2]; } break; case 4: // Type 4, use default dbAlias or Type 2 Universal if (argv[0].equalsIgnoreCase("-u2")) { alias = argv[1]; userId = argv[2]; password = argv[3]; portNumber = -1; } else { alias = "sample"; server = argv[0]; portNumber = Integer.valueOf(argv[1]).intValue(); userId = argv[2]; password = argv[3]; } break; case 5: // Type 4, everything specified if (!argv[0].equalsIgnoreCase("-u2")) { alias = argv[0]; server = argv[1]; portNumber = Integer.valueOf(argv[2]).intValue(); userId = argv[3]; password = argv[4]; } break; } } public DB(String argv[]) { init(argv); } // DB Constructor public Connection connect() { String url = null; // In Partitioned Database environment, set this to the node number // to which you wish to connect (leave as "0" in non-Partitioned // Database environment) String nodeNumber = "0"; Properties props = new Properties(); try { if (portNumber < 0) { url = "jdbc:db2:" + alias; debug(" Connect to '" + alias + "' database using JDBC Universal type 2 driver."); Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); } else if (portNumber == 0) { url = "jdbc:db2:" + alias; debug(" Connect to '" + alias + "' database using JDBC type 2 driver."); Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance(); } else { url = "jdbc:db2://" + server + ":" + portNumber + "/" + alias; debug(" Connect to '" + alias + "' database using JDBC type 4 driver."); Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); } if (null != userId) { props.setProperty("user", userId); props.setProperty("password", password); } props.setProperty("CONNECTNODE", nodeNumber); //DriverManager.setLogStream(System.out); con = DriverManager.getConnection(url, props); } catch (Exception e) { e.printStackTrace(); } // enable transactions //con.setAutoCommit(false); if (con==null) debug("Connect FAIL."); else debug("Connect OK."); return con; } // connect public void disconnect() { if (con == null) return; try { // makes all changes made since the previous commit/rollback // permanent // and releases any database locks currrently held by the // Connection. con.commit(); // immediately disconnects from database and releases JDBC resources con.close();