JDBC 常用操作

// 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 exist
		boolean exist = t.existTable(conn, "student");		
		// create table
		if (!exist){
			t.createTable(conn);			
		}
		// drop table
		t.dropTable(conn, "student");		
		// +------------------------------------------+
		// +--              procedure               --+
		// +------------------------------------------+
		// create procedure
		t.createProcedure(conn);
		// call procedure
		t.callProcedure(conn);
		// drop procedure
		t.dropProcedure(conn);
		// +------------------------------------------+
		// +--              trigger                 --+
		// +------------------------------------------+
		// create trigger
		t.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){}
		}
		// print
		System.out.println("Table " + tableName + " exist: " + exist);
		// return
		return 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);
		// print
		System.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){}
		}
		// print
		System.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 {
			// insert
			pstmt = 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();
			// update
			pstmt = conn.prepareStatement(update);
			pstmt.setInt(1, -1);
			pstmt.setString(2, "1");
			pstmt.executeUpdate();
			// select
			stmt = 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.