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.}