JDBC复习

 

一，JDBC的编程步骤：   --学自尚学堂

1.Load the Driver，向DriverManager注册
①.Class.forName(“该Driver的名字”)或者是Class.forName(“该Driver的名字”).newInstance()或者直接new这个Driver
②.实例化时自动向DriverManager注册，不需要再调用DriverManager.registerDriver方法

2.Connect to the DataBase
①.DriverManager.getConnec()

3.Excute the SQL
①.Connection.CreateStatem()
②.Statement.executeQuery()
③.Statement.executeUpdate()

4.Retrieve the result data      Retrieve （检索储存的信息）
①.循环取得结果 while(rs.next())

5.Show the result data
①.将数据库中的各种类型转换为java中的类型，用get方法

6.Close   （后打开的先关）
①.close the ResultSet
②.close the Statement
③.close the Connection

二，Oracle的连接字符串

    Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@主机IP:1521:所要连接的数据库的SID","scott","tiger");

一个连接的Format

 

 

 

 

public static void main(String[] args) {
Connection conn = null;
try {
//向DriverManager注册
Class.forName("oracle.jdbc.driver.OracleDriver");
    //从DriverManager获得连接
    conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.210:1521:orcl","scott","tiger");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
//最后关闭连接
if (conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

三，执行查询SQL时的Format

 

 

 

 

public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.210:1521:orcl","scott","tiger");
//创建Statement，然后通过Statement的executeQuery(sql)方法执行查询语句，并把结果返回到ResultSet结果集中
    stmt = conn.createStatement();
String sql = "select * from dept";
rs = stmt.executeQuery(sql);
    while (rs.next()){
System.out.println(rs.getInt("deptno"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//最后关闭所有资源，后打开的先关，关闭后赋值为null是为了让垃圾收集器尽快释放内存
try {
if (rs != null){
rs.close();
rs = null;
}
if (stmt != null){
stmt.close();
stmt = null;
}
if (conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

四，执行insert，update等对表进行改动的SQL时的Format

public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.210:1521:orcl","scott","tiger");
//建立prepareStatement，通过prepareStatement的executeUpdate方法执行SQL语句？是占位符，这样写SQL就更方便，避免了单引号的问题
    pstmt = conn.prepareStatement("insert into dept2 values (?,?,?)");
pstmt.setInt(1, 50);
pstmt.setString(2, "GAME");
pstmt.setString(3, "BEIJING");
pstmt.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null){
rs.close();
rs = null;
}
if (stmt != null){
stmt.close();
stmt = null;
}
if (conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

五，调用PLSQL时的Format

① 先建立一个Stored Procedure

create or replace procedure p
(v_a in number, v_b number, v_return out number, v_temp in out number)
is
begin
if (v_a > v_b) then
v_return := v_a;
else
v_return := v_b;
end if;
v_temp := v_temp + 1;
end;

② java中的写法

public static void main(String[] args) {
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.210:1521:orcl", "scott", "tiger");
//建立一个CallableStatement ，然后通过CallableStatement 的execute方法来执行PLSQL
    CallableStatement cstmt = conn.prepareCall("{call p(?,?,?,?)}");
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.registerOutParameter(4, Types.INTEGER);
cstmt.setInt(1, 13);
cstmt.setInt(2, 45);
cstmt.setInt(4, 9);
cstmt.execute();
System.out.println(cstmt.getInt(3));
System.out.println(cstmt.getInt(4));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}

六，在JDBC中处理Batch
①

public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.210:1521:orcl", "scott", "tiger");
stmt = conn.createStatement();
//用Statement的addBatch方法把各个SQL语句添加进去，然后在调用executeBatch语句执行这些SQL语句
    stmt.addBatch("insert into dept2 values(51,'lit1','tokyo')");
stmt.addBatch("insert into dept2 values(52,'lit2','tokyo')");
stmt.addBatch("insert into dept2 values(53,'lit3','tokyo')");
stmt.executeBatch();
   } catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null){
conn.close();
conn = null;
}
} catch (SQLException e){
e.printStackTrace();
}
}
}

②

public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.210:1521:orcl", "scott", "tiger");
pstmt = conn.prepareStatement("insert into dept2 values(?,?,?)");
//调用PreparedStatement 的addBatch加入各种SQL语句，然后调用executeBatch方法执行这些SQL语句
pstmt.setInt(1, 61);
pstmt.setString(2, "lit1");
pstmt.setString(3, "okayama");
pstmt.addBatch();
pstmt.setInt(1, 62);
pstmt.setString(2, "lit2");
pstmt.setString(3, "okayama");
pstmt.addBatch();
pstmt.setInt(1, 63);
pstmt.setString(2, "lit3");
pstmt.setString(3, "okayama");
pstmt.addBatch();
pstmt.executeBatch();
   } catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
conn = null;
}
if (pstmt != null) {
pstmt.close();
pstmt = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

七，JDBC中的事物处理，transaction，（就是关于commit）

public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.210:1521:orcl", "scott", "tiger");
stmt = conn.createStatement();
   //首先用Connection中的setAutoCommit方法将自动commit设为false，因为JDBC默认是自动commit，执行完所要执行的语句块后再用Connection的commit方法提交，完成后setAutoCommit(true)，恢复JDBC的默认设置
conn.setAutoCommit(false);
    stmt.addBatch("insert into dept2 values(41,'lit1','tokyo')");
stmt.addBatch("insert into dept2 values(42,'lit2','tokyo')");
stmt.addBatch("insert into dept2 values(43,'lit3','tokyo')");
stmt.executeBatch();
conn.commit();
conn.setAutoCommit(true);
   } catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
//捕捉到任何的SQLException立刻rollback，然后恢复JDBC的默认设置
   try {
if(conn != null) {
conn.rollback();
conn.setAutoCommit(true);
}
} catch (SQLException e1) {
e1.printStackTrace();
}
   } finally {
try {
if (conn != null){
conn.close();
conn = null;
}
} catch (SQLException e){
e.printStackTrace();
}
}
}

转自 http://hi.baidu.com/ahuzl/blog/item/c3b900d10a02a987a0ec9cce.html