JSP复习笔记——第10章 连接数据库 之 JDBC相关

批处理：

Java代码
1.import java.sql.*;  
2.public class TestBatch {  
3. 
4. 
5.    public static void main(String[] args) throws Exception {  
6.        Class.forName("oracle.jdbc.driver.OracleDriver");  
7.        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521: sky", "scott", "tiger");  
8.        /* 
9.        Statement stmt = conn.createStatement(); 
10.        stmt.addBatch("insert into dept2 values (51, '500', 'haha')"); 
11.        stmt.addBatch("insert into dept2 values (52, '500', 'haha')"); 
12.        stmt.addBatch("insert into dept2 values (53, '500', 'haha')"); 
13.        stmt.executeBatch(); 
14.        stmt.close(); 
15.        */ 
16.          
17.        PreparedStatement ps = conn.prepareStatement("insert into dept2 values (?, ?, ?)");  
18.        ps.setInt(1, 61);  
19.        ps.setString(2, "haha");  
20.        ps.setString(3, "bj");  
21.        ps.addBatch();  
22.          
23.        ps.setInt(1, 62);  
24.        ps.setString(2, "haha");  
25.        ps.setString(3, "bj");  
26.        ps.addBatch();  
27.          
28.        ps.setInt(1, 63);  
29.        ps.setString(2, "haha");  
30.        ps.setString(3, "bj");  
31.        ps.addBatch();  
32.          
33.        ps.executeBatch();  
34.        ps.close();  
35.          
36.        conn.close();  
37. 
38.    }  
39. 
40.} 
import java.sql.*;
public class TestBatch {


public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521: sky", "scott", "tiger");
/*
Statement stmt = conn.createStatement();
stmt.addBatch("insert into dept2 values (51, '500', 'haha')");
stmt.addBatch("insert into dept2 values (52, '500', 'haha')");
stmt.addBatch("insert into dept2 values (53, '500', 'haha')");
stmt.executeBatch();
stmt.close();
*/

PreparedStatement ps = conn.prepareStatement("insert into dept2 values (?, ?, ?)");
ps.setInt(1, 61);
ps.setString(2, "haha");
ps.setString(3, "bj");
ps.addBatch();

ps.setInt(1, 62);
ps.setString(2, "haha");
ps.setString(3, "bj");
ps.addBatch();

ps.setInt(1, 63);
ps.setString(2, "haha");
ps.setString(3, "bj");
ps.addBatch();

ps.executeBatch();
ps.close();

conn.close();

}

}


Java代码
1.import java.sql.*;  
2. 
3.public class TestJDBC {  
4. 
5.    public static void main(String[] args) {  
6.        ResultSet rs = null;  
7.        Statement stmt = null;  
8.        Connection conn = null;  
9.        try {  
10.            Class.forName("oracle.jdbc.driver.OracleDriver");  
11.            //new oracle.jdbc.driver.OracleDriver();  
12.            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:sky", "scott", "tiger");  
13.            stmt = conn.createStatement();  
14.            rs = stmt.executeQuery("select * from dept");  
15.            while(rs.next()) {  
16.                System.out.println(rs.getString("deptno"));  
17.                System.out.println(rs.getInt("deptno"));  
18.            }  
19.        } catch (ClassNotFoundException e) {  
20.            e.printStackTrace();  
21.        } catch (SQLException e) {  
22.            e.printStackTrace();  
23.        } finally {  
24.            try {  
25.                if(rs != null) {  
26.                    rs.close();  
27.                    rs = null;  
28.                }  
29.                if(stmt != null) {  
30.                    stmt.close();  
31.                    stmt = null;  
32.                }  
33.                if(conn != null) {  
34.                    conn.close();  
35.                    conn = null;  
36.                }  
37.            } catch (SQLException e) {  
38.                e.printStackTrace();  
39.            }  
40.        }  
41.    }  
42. 
43.} 
import java.sql.*;

public class TestJDBC {

public static void main(String[] args) {
ResultSet rs = null;
Statement stmt = null;
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:sky", "scott", "tiger");
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from dept");
while(rs.next()) {
System.out.println(rs.getString("deptno"));
System.out.println(rs.getInt("deptno"));
}
} 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();
}
}
}

}


PreparedStatement:

Java代码
1.import java.sql.*;  
2. 
3. 
4.public class TestPrepStmt {  
5. 
6.    public static void main(String[] args) {  
7.        if(args.length != 3) {  
8.            System.out.println("Parameter Error! Please Input Again!");  
9.            System.exit(-1);  
10.        }  
11.          
12.        int deptno = 0;  
13.          
14.        try {  
15.            deptno = Integer.parseInt(args[0]);  
16.        } catch (NumberFormatException e) {  
17.            System.out.println("Parameter Error! Deptno should be Number Format!");  
18.            System.exit(-1);  
19.        }  
20.          
21.        String dname = args[1];  
22.        String loc = args[2];  
23.          
24.        PreparedStatement pstmt = null;  
25.        Connection conn = null;  
26.        try {  
27.            Class.forName("oracle.jdbc.driver.OracleDriver");  
28.            //new oracle.jdbc.driver.OracleDriver();  
29.            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:sky", "scott", "tiger");  
30.            pstmt = conn.prepareStatement("insert into dept2 values (?, ?, ?)");  
31.            pstmt.setInt(1, deptno);  
32.            pstmt.setString(2, dname);  
33.            pstmt.setString(3, loc);  
34.            pstmt.executeUpdate();  
35.        } catch (ClassNotFoundException e) {  
36.            e.printStackTrace();  
37.        } catch (SQLException e) {  
38.            e.printStackTrace();  
39.        } finally {  
40.            try {  
41.                if(pstmt != null) {  
42.                    pstmt.close();  
43.                    pstmt = null;  
44.                }  
45.                if(conn != null) {  
46.                    conn.close();  
47.                    conn = null;  
48.                }  
49.            } catch (SQLException e) {  
50.                e.printStackTrace();  
51.            }  
52.        }  
53.    }  
54. 
55.} 
import java.sql.*;


public class TestPrepStmt {

public static void main(String[] args) {
if(args.length != 3) {
System.out.println("Parameter Error! Please Input Again!");
System.exit(-1);
}

int deptno = 0;

try {
deptno = Integer.parseInt(args[0]);
} catch (NumberFormatException e) {
System.out.println("Parameter Error! Deptno should be Number Format!");
System.exit(-1);
}

String dname = args[1];
String loc = args[2];

PreparedStatement pstmt = null;
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:sky", "scott", "tiger");
pstmt = conn.prepareStatement("insert into dept2 values (?, ?, ?)");
pstmt.setInt(1, deptno);
pstmt.setString(2, dname);
pstmt.setString(3, loc);
pstmt.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(pstmt != null) {
pstmt.close();
pstmt = null;
}
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

}


jdbc处理存储过程：

Java代码
1.import java.sql.*;  
2.public class TestProc {  
3. 
4.    /** 
5.     * @param args 
6.     */ 
7.    public static void main(String[] args) throws Exception {  
8.          
9.        Class.forName("oracle.jdbc.driver.OracleDriver");  
10.        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");  
11.        CallableStatement cstmt = conn.prepareCall("{call p(?, ?, ?, ?)}");  
12.        cstmt.registerOutParameter(3, Types.INTEGER);  
13.        cstmt.registerOutParameter(4, Types.INTEGER);  
14.        cstmt.setInt(1, 3);  
15.        cstmt.setInt(2, 4);  
16.        cstmt.setInt(4, 5);  
17.        cstmt.execute();  
18.        System.out.println(cstmt.getInt(3));  
19.        System.out.println(cstmt.getInt(4));  
20.        cstmt.close();  
21.        conn.close();  
22.    }  
23. 
24.} 
import java.sql.*;
public class TestProc {

/**
* @param args
*/
public static void main(String[] args) throws Exception {

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");
CallableStatement cstmt = conn.prepareCall("{call p(?, ?, ?, ?)}");
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.registerOutParameter(4, Types.INTEGER);
cstmt.setInt(1, 3);
cstmt.setInt(2, 4);
cstmt.setInt(4, 5);
cstmt.execute();
System.out.println(cstmt.getInt(3));
System.out.println(cstmt.getInt(4));
cstmt.close();
conn.close();
}

}


JDBC处理可滚动的结果集

Java代码
1.import java.sql.*;  
2. 
3.public class TestScroll {  
4.    public static void main(String args[]) {  
5. 
6.        try {  
7.            new oracle.jdbc.driver.OracleDriver();  
8.            String url = "jdbc:oracle:thin:@192.168.0.1:1521:SXT";  
9.            Connection conn = DriverManager  
10.                    .getConnection(url, "scott", "tiger");  
11.            Statement stmt = conn.createStatement(  
12.                    ResultSet.TYPE_SCROLL_INSENSITIVE,  
13.                    ResultSet.CONCUR_READ_ONLY);  
14.            ResultSet rs = stmt  
15.                    .executeQuery("select * from emp order by sal");  
16.            rs.next();  
17.            System.out.println(rs.getInt(1));  
18.            rs.last();  
19.            System.out.println(rs.getString(1));  
20.            System.out.println(rs.isLast());  
21.            System.out.println(rs.isAfterLast());  
22.            System.out.println(rs.getRow());  
23.            rs.previous();  
24.            System.out.println(rs.getString(1));  
25.            rs.absolute(6);  
26.            System.out.println(rs.getString(1));  
27.            rs.close();  
28.            stmt.close();  
29.            conn.close();  
30.        } catch (SQLException e) {  
31.            e.printStackTrace();  
32.        }  
33.    }  
34.} 
import java.sql.*;

public class TestScroll {
public static void main(String args[]) {

try {
new oracle.jdbc.driver.OracleDriver();
String url = "jdbc:oracle:thin:@192.168.0.1:1521:SXT";
Connection conn = DriverManager
.getConnection(url, "scott", "tiger");
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt
.executeQuery("select * from emp order by sal");
rs.next();
System.out.println(rs.getInt(1));
rs.last();
System.out.println(rs.getString(1));
System.out.println(rs.isLast());
System.out.println(rs.isAfterLast());
System.out.println(rs.getRow());
rs.previous();
System.out.println(rs.getString(1));
rs.absolute(6);
System.out.println(rs.getString(1));
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}


JDBC处理可更新的结果集

Java代码
1.import java.sql.*;  
2.public class TestUpdataRs {  
3.    public static void main(String args[]){  
4.      
5.    try{  
6.        new oracle.jdbc.driver.OracleDriver();  
7.        String url="jdbc:oracle:thin:@192.168.0.1:1521:SXT";  
8.        Connection conn=DriverManager.getConnection(url,"scott","tiger");  
9.        Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);  
10.          
11.        ResultSet rs=stmt.executeQuery("select * from emp2");  
12.          
13.        rs.next();  
14.        //更新一行数据  
15.        rs.updateString("ename","AAAA");  
16.        rs.updateRow();  
17. 
18.        //插入新行  
19.        rs.moveToInsertRow();  
20.        rs.updateInt(1, 9999);  
21.        rs.updateString("ename","AAAA");  
22.        rs.updateInt("mgr", 7839);  
23.        rs.updateDouble("sal", 99.99);  
24.        rs.insertRow();  
25.        //将光标移动到新建的行  
26.        rs.moveToCurrentRow();  
27. 
28.        //删除行  
29.        rs.absolute(5);  
30.        rs.deleteRow();  
31. 
32.        //取消更新  
33.        //rs.cancelRowUpdates();  
34. 
35.      }catch(SQLException e){  
36.        e.printStackTrace();  
37.      }  
38.    }  
39.} 
import java.sql.*;
public class TestUpdataRs {
    public static void main(String args[]){

try{
    new oracle.jdbc.driver.OracleDriver();
    String url="jdbc:oracle:thin:@192.168.0.1:1521:SXT";
    Connection conn=DriverManager.getConnection(url,"scott","tiger");
    Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
   
    ResultSet rs=stmt.executeQuery("select * from emp2");
   
    rs.next();
    //更新一行数据
    rs.updateString("ename","AAAA");
    rs.updateRow();

    //插入新行
    rs.moveToInsertRow();
    rs.updateInt(1, 9999);
    rs.updateString("ename","AAAA");
    rs.updateInt("mgr", 7839);
    rs.updateDouble("sal", 99.99);
    rs.insertRow();
    //将光标移动到新建的行
    rs.moveToCurrentRow();

    //删除行
    rs.absolute(5);
    rs.deleteRow();

    //取消更新
    //rs.cancelRowUpdates();

  }catch(SQLException e){
    e.printStackTrace();
  }
    }
}


JDBC处理Transaction

Java代码
1.import java.sql.*;  
2.public class TestTransaction {  
3. 
4. 
5.    public static void main(String[] args) {  
6.          
7.        Connection conn = null;  
8.        Statement stmt = null;  
9.          
10.        try {  
11.            Class.forName("oracle.jdbc.driver.OracleDriver");  
12.            conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:SXT", "scott", "tiger");  
13.              
14.            conn.setAutoCommit(false);  
15.            stmt = conn.createStatement();  
16.            stmt.addBatch("insert into dept2 values (51, '500', 'haha')");  
17.            stmt.addBatch("insert into dept2 values (52, '500', 'haha')");  
18.            stmt.addBatch("insert into dept2 values (53, '500', 'haha')");  
19.            stmt.executeBatch();  
20.            conn.commit();  
21.            conn.setAutoCommit(true);  
22.        } catch (ClassNotFoundException e) {  
23.            e.printStackTrace();  
24.        } catch(SQLException e) {  
25.              
26.            e.printStackTrace();  
27.              
28.            try {  
29.                if(conn != null)  
30.                {  
31.                    conn.rollback();  
32.                    conn.setAutoCommit(true);  
33.                }  
34.            } catch (SQLException e1) {  
35.                e1.printStackTrace();  
36.            }  
37.        }finally {  
38.            try {  
39.                if(stmt != null)  
40.                    stmt.close();  
41.                if(conn != null)  
42.                    conn.close();  
43.            } catch (SQLException e) {  
44.                e.printStackTrace();  
45.            }  
46.        }  
47.          
48. 
49.    }  
50. 
51.}