JDBC 常用操作

dyccsxg

// java code for db2 test
import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;public class Main {public static void main(String[] args) throws Exception {Main t = new Main();Connection conn = t.getConnection();// +------------------------------------------+// +--              table                   --+// +------------------------------------------+// check table existboolean exist = t.existTable(conn, "student");// create tableif (!exist){t.createTable(conn);}// drop tablet.dropTable(conn, "student");// +------------------------------------------+// +--              procedure               --+// +------------------------------------------+// create proceduret.createProcedure(conn);// call proceduret.callProcedure(conn);// drop proceduret.dropProcedure(conn);// +------------------------------------------+// +--              trigger                 --+// +------------------------------------------+// create triggert.createTable(conn);t.createTrigger(conn);t.executeCURD(conn);t.dropTrigger(conn);t.dropTable(conn,"student");}/** * 获取数据库连接 * @return */public Connection getConnection()throws Exception{String driver = "com.ibm.db2.jcc.DB2Driver";String url = "jdbc:db2://10.162.11.134:50000/sample";String user = "db2admin";String pass = "db2admin";// Class.forName(driver);return DriverManager.getConnection(url, user, pass);}/** * 判断表是否存在 * @param conn * @param tableName * @return */public boolean existTable(Connection conn,String tableName){boolean exist = false;String sql = "select * from " + tableName + " where 1=0";Statement stmt = null;try {stmt = conn.createStatement();stmt.executeQuery(sql);exist = true;} catch(SQLException e){exist = false;} finally {try { if(stmt != null)stmt.close(); } catch (Exception e){}}// printSystem.out.println("Table " + tableName + " exist: " + exist);// returnreturn exist;}/** * 创建表 * @param conn * @throws SQLException  */public void createTable(Connection conn) throws SQLException{String sql = "create table student("       + "  id   varchar(20) not null,"       + "  name varchar(32) not null,"       + "  age  integer"        + ")";Statement stmt = conn.createStatement();stmt.execute(sql);// printSystem.out.println("Create table : student");}/** * 删除表 * @param conn * @param tableName */public void dropTable(Connection conn,String tableName){String sql = "drop table " + tableName;Statement stmt = null;try {stmt = conn.createStatement();stmt.execute(sql);} catch (Exception e) {e.printStackTrace();} finally {try { if (stmt != null)stmt.close(); } catch (Exception e){}}// printSystem.out.println("Drop table: " + tableName);}/** * 创建存储过程 * @param conn */public void createProcedure(Connection conn){String sql = "create procedure tmp_proc (out curdtime varchar(19)) "           + "specific tmp_proc "           + "begin "           + "declare cur cursor for "           + "    select to_char(current timestamp,'yyyy-MM-dd HH24:mi:ss') as dtime from sysibm.sysdummy1;"           + "open cur; "           + "fetch cur into curdtime; "           + "close cur;"           + "end";Statement stmt = null;try {stmt = conn.createStatement();stmt.execute(sql);} catch (Exception e){e.printStackTrace();} finally {try { if (stmt != null)stmt.close();} catch(Exception e){}}}/** * 调用存储过程 * @param conn */public void callProcedure(Connection conn){String sql = "call tmp_proc(?)";CallableStatement cstmt = null;try {cstmt = conn.prepareCall(sql);cstmt.registerOutParameter(1, Types.VARCHAR);cstmt.execute();String result = cstmt.getString(1);System.out.println("procedure result: " + result);} catch (Exception e){e.printStackTrace();} finally {try { if (cstmt != null)cstmt.close();} catch (Exception e){}}}/** * 删除存储过程 * @param conn */public void dropProcedure(Connection conn){String sql = "drop specific procedure tmp_proc";Statement stmt = null;try {stmt = conn.createStatement();stmt.execute(sql);} catch (Exception e){e.printStackTrace();} finally {try { if (stmt != null)stmt.close();} catch (Exception e){}}}/** * 创建触发器 * @param conn */public void createTrigger(Connection conn){String sql = "create trigger trg_upd_stu "       + "before update on student "       + "referencing new as n "       + "for each row "       + "begin "       + "if (n.age is null or n.age < 0) then "       + "  set n.age = 0; "       + "end if; "       + "end";Statement stmt = null;try {stmt = conn.createStatement();stmt.execute(sql);} catch (Exception e){e.printStackTrace();} finally {try { if (stmt != null)stmt.close();} catch (Exception e){}}}/** * 删除触发器 * @param conn */public void dropTrigger(Connection conn){String sql = "drop trigger trg_upd_stu";Statement stmt = null;try {stmt = conn.createStatement();stmt.execute(sql);} catch (Exception e){e.printStackTrace();} finally {try { if (stmt != null)stmt.close();} catch (Exception e){}}}/** * 执行增改查删 * @param conn */public void executeCURD(Connection conn){String insert = "insert into student values(?,?,?)";String update = "update student set age = ? where id = ?";String select = "select * from student";String delete = "delete from student";// PreparedStatement pstmt = null;Statement stmt = null;try {// insertpstmt = conn.prepareStatement(insert);pstmt.setString(1, "1");pstmt.setString(2, "zhangsan");pstmt.setInt(3, 22);pstmt.addBatch();pstmt.setString(1, "2");pstmt.setString(2, "lisi");pstmt.setInt(3, 23);pstmt.addBatch();pstmt.executeBatch();// updatepstmt = conn.prepareStatement(update);pstmt.setInt(1, -1);pstmt.setString(2, "1");pstmt.executeUpdate();// selectstmt = conn.createStatement();ResultSet rs = stmt.executeQuery(select);while (rs.next()){String name = rs.getString("NAME");int age = rs.getInt("AGE");System.out.println("name: " + name + " age: " + age);}// delete stmt.executeUpdate(delete);} catch (Exception e){e.printStackTrace();} finally {try { if (stmt != null)stmt.close();} catch (Exception e){}try { if (pstmt != null)pstmt.close();} catch (Exception e){}}}} 
END.