//language:java //author:tfy //time:2009.10.14 //fun:connect to db2 use jdbc driver (type 3) import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Db2Connect { static String urlPrefix = "jdbc:db2:"; static String url; static Connection con; static Statement stmt; static ResultSet rs; public boolean connectToDb2(String serverIp, int serverPort, String DBName, String user, String password) { url = urlPrefix + "//" + serverIp + ":"+Integer.toString(serverPort) + "/" + DBName; System.out.println ("**** Enter class EzJava"); try { // Load the driver: // Class.forName("COM.ibm.db2.jdbc.app.DB2Driver"); Class.forName("COM.ibm.db2.jdbc.net.DB2Driver"); // Class.forName("com.ibm.db2.jcc.DB2Driver"); System.out.println(url); // Create the connection using the IBM Data Server Driver for JDBC and SQLJ con = DriverManager.getConnection (url, user, password); // Commit changes manually // con.setAutoCommit(false); System.out.println("**** Created a JDBC connection to the data source"); // Create the Statement //reference: http://blog.csdn.net/yuchen2008/archive/2007/01/24/1492427.aspx //stmt = con.createStatement(); stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); System.out.println("**** Created JDBC Statement object"); } catch (ClassNotFoundException e) { System.err.println("Could not load JDBC driver"); System.out.println("Exception: " + e); e.printStackTrace(); return false; } catch(SQLException ex) { System.err.println("SQLException information"); while(ex!=null) { System.err.println ("Error msg: " + ex.getMessage()); System.err.println ("SQLSTATE: " + ex.getSQLState()); System.err.println ("Error code: " + ex.getErrorCode()); ex.printStackTrace(); ex = ex.getNextException(); // For drivers that support chained exceptions } } return true; } public ResultSet queryDb2(String sql) { try { // Execute a query and generate a ResultSet instance rs = stmt.executeQuery(sql); System.out.println("**** Created JDBC ResultSet object"); } catch(SQLException ex) { System.err.println("SQLException information"); while(ex!=null) { System.err.println ("Error msg: " + ex.getMessage()); System.err.println ("SQLSTATE: " + ex.getSQLState()); System.err.println ("Error code: " + ex.getErrorCode()); ex.printStackTrace(); ex = ex.getNextException(); // For drivers that support chained exceptions } } return rs; } public boolean closeDb2() { try { // Close the ResultSet rs.close(); System.out.println("**** Closed JDBC ResultSet"); // Close the Statement stmt.close(); System.out.println("**** Closed JDBC Statement"); // Connection must be on a unit-of-work boundary to allow close con.commit(); System.out.println ( "**** Transaction committed" ); // Close the connection con.close(); System.out.println("**** Disconnected from data source"); System.out.println("**** JDBC Exit from class EzJava - no errors"); } catch(SQLException ex) { System.err.println("SQLException information"); while(ex!=null) { System.err.println ("Error msg: " + ex.getMessage()); System.err.println ("SQLSTATE: " + ex.getSQLState()); System.err.println ("Error code: " + ex.getErrorCode()); ex.printStackTrace(); ex = ex.getNextException(); // For drivers that support chained exceptions return false; } } return true; } } //time:2009.10.14 //author:tfy //fun:connect to db2, get and process resultset, import java.sql.*; import java.util.Vector; import java.lang.*; public class DataProcess { static String serverIp = "127.0.0.1";//192.168.1.107"; static int serverPort = 6789; //cmd : db2jstrt 6789 static String DBName = "STUDENT"; static String user = "db2admin"; static String password = "1234"; static ResultSet rs; static Db2Connect db2con = new Db2Connect(); static String rowfield[]; static String colfield = "AREAL"; static String zbfield = "NUM"; public static void main(String argv[]) { rowfield = new String[2]; rowfield[1] = "AGE"; rowfield[0] = "GRADE"; db2con.connectToDb2(serverIp, serverPort, DBName,user, password); int argvNum = argv.length; String sql = "select AGE, GRADE, AREAL, NUM from DB2ADMIN.STUDENTINFO"; String ele; getDBInfo(sql, rowfield, colfield, zbfield); try { while (rs.next()) { ele = rs.getString(1); System.out.println("element : " + ele); } System.out.println("**** Fetched all rows from JDBC ResultSet"); } catch(SQLException ex) { System.err.println("SQLException information"); while(ex!=null) { System.err.println ("Error msg: " + ex.getMessage()); System.err.println ("SQLSTATE: " + ex.getSQLState()); System.err.println ("Error code: " + ex.getErrorCode()); ex.printStackTrace(); ex = ex.getNextException(); // For drivers that support chained exceptions } } db2con.closeDb2(); } public static void getDBInfo(String sql, String rowfield[], String colfield, String zbfield) { rs = db2con.queryDb2(sql); Vector<String> dbInfo; String st; dbInfo= new Vector<String>();//row[]; col[]; zb[] int n = rowfield.length; for (int _i=0; _i<n; _i++) { st = rowfield[_i]; dbInfo = getAttrbutionInfo(st); } dbInfo = getAttrbutionInfo(colfield); dbInfo = getAttrbutionInfo(zbfield); } private static Vector<String> getAttrbutionInfo(String attribution) { String empNo; ; Vector<String> vec = new Vector<String>(); vec.add(attribution); //System.out.println(attribution); try { //rs.getArray(attribution); int columIndex = rs.findColumn(attribution); System.out.println("can not find colum: " + Integer.toString(columIndex)); //if (columIndex == -1) { // System.out.println("can not find colum: " + attribution); //} if ( rs.getConcurrency() == 1 ) { System.out.println("the ResultSet can update!"); } rs.first(); //db can access while (rs.next()) { empNo = rs.getString(columIndex); System.out.println(empNo); if (vec.indexOf(empNo) == -1) vec.add(empNo); } } catch(SQLException ex) { System.err.println("SQLException information"); while(ex!=null) { System.err.println ("Error msg: " + ex.getMessage()); System.err.println ("SQLSTATE: " + ex.getSQLState()); System.err.println ("Error code: " + ex.getErrorCode()); ex.printStackTrace(); ex = ex.getNextException(); // For drivers that support chained exceptions } } for (int _i=0; _i<vec.size(); _i++) { System.out.println(vec.get(_i)); } return vec; } }