JDBC小结

　　老师如是说：有一个规律永远不变，就是量变引起质变，什么时候你吃的盐比我吃的米多，那你就可以教我了……

1.JDBC是一个标准，是J2EE的一部分；讲tcp时就提到了分层思想，jdbc也是分层，即上层只需要和紧挨着的下层通讯，方便扩展。不管开奥拓车还是开奥迪车方向盘都在屁股前边，档都在右边。不过jdbc还需要写select语句等，而hibernate完全实现了数据库底层，而且屏蔽了细小差别；现在的EJB也实现了某些功能。

2.TestJDBC

　　若是连接字符串或者某些方法忘了（但是有些东西不能忘，比如System.out.println），就注明平时用多了Eclipse，若是查查还能知道……

 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          /*
10           * 一定要加上try catch finally，不要throws
11           * 因为若在遍历过程中出问题，那么下面的关闭就一直执行不了
12           * 服务器会越来越卡，最后死掉
13           */
14          try {
15              Class.forName("oracle.jdbc.driver.OracleDriver");
16              //new oracle.jdbc.driver.OracleDriver();
17              conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", 
18 
19 "scott", "tiger");
20              stmt = conn.createStatement();
21              //结果集就像游标，不过不像数据库，第一条没指向第一条，像iterator
22              rs = stmt.executeQuery("select * from dept");
23              while(rs.next()) {
24                  System.out.println(rs.getString("deptno"));
25                  //和实际字段类型无关，只要能转换为int就可以
26                  System.out.println(rs.getInt("deptno"));
27              }
28          } catch (ClassNotFoundException e) {
29              e.printStackTrace();
30              /*
31               * 实际上要用log4j把异常记录在日志里
32               */
33          } catch (SQLException e) {
34              e.printStackTrace();
35          } finally {
36              /*
37               * 要把rs，stmt，conn声明在大try外边
38               * 否则在这就不认识了
39               */
40              try {
41                  /*
42                   * 不加try catch会出错，因为close也会出错.
43                   * 如果rs没有初始化，这直接close那么一定会报错
44                   */
45                  if(rs != null) {
46                      rs.close();
47                      /*
48                       * 查过api后会发现close后会直接设置为null
49                       * 方便gc回收
50                       * 写下面的是为了展现良好的编程风格
51                       */
52                      rs = null;
53                  }
54                  if(stmt != null) {
55                      stmt.close();
56                      stmt = null;
57                  }
58                  if(conn != null) {
59                      conn.close();
60                      conn = null;
61                  }
62              } catch (IOException e) {
63                  e.printStackTrace();
64              }
65          }
66      }
67  
68  }
69 
70 View Code

View Code

3.JDBC操作DML语句

 1 import java.sql.Connection;
 2 import java.sql.DriverManager;
 3 import java.sql.ResultSet;
 4 import java.sql.SQLException;
 5 import java.sql.Statement;
 6 
 7 public class TestDML {
 8 
 9     /*
10      * 执行完DML语句后看是否成功
11      * 打开window--show view--other--data
12      */
13     public static void main(String[] args) {
14         Statement stmt = null;
15         Connection conn = null;
16         try {
17             Class.forName("com.mysql.jdbc.Driver");
18             conn = DriverManager.getConnection("jdbc:mysql:///news", "root", "root");
19             stmt = conn.createStatement();
20             String sql = "insert into dept2 values (98, 'GAME', 'BJ')";
21             stmt.executeUpdate(sql);
22         } catch (ClassNotFoundException e) {
23             e.printStackTrace();
24         } catch (SQLException e) {
25             e.printStackTrace();
26         } finally {
27             try {
28                 if(stmt != null) {
29                     stmt.close();
30                     stmt = null;
31                 }
32                 if(conn != null) {
33                     conn.close();
34                     conn = null;
35                 }
36             } catch (SQLException e) {
37                 e.printStackTrace();
38             }
39         }
40     }
41 
42 }

View Code

 1 import java.sql.Connection;
 2 import java.sql.DriverManager;
 3 import java.sql.ResultSet;
 4 import java.sql.SQLException;
 5 import java.sql.Statement;
 6 
 7 public class TestDML2 {
 8 
 9     /*
10      * 从命令行输入
11      * 右键 进入run configuration，在arguments输入
12      */
13     public static void main(String[] args) {
14         if(args.length != 3) {
15             System.out.println("Parameter Error! Please Input Again!");
16             System.exit(-1);
17         }
18         
19         int deptno = 0;
20         
21         try {
22             deptno = Integer.parseInt(args[0]);
23         } catch (NumberFormatException e) {
24             System.out.println("Parameter Error! Deptno should be Number Format!");
25             System.exit(-1);
26         }
27         /*
28          * 把这些变量名声明在一块
29          */
30         String dname = args[1];
31         String loc = args[2];
32         
33         Statement stmt = null;
34         Connection conn = null;
35         try {
36             Class.forName("oracle.jdbc.driver.OracleDriver");
37             //new oracle.jdbc.driver.OracleDriver();
38             conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");
39             stmt = conn.createStatement();
40             //注意values后边有个空格
41             String sql = "insert into dept2 values (" + deptno + ",'" + dname + "','" + loc + "')";
42             System.out.println(sql);
43             stmt.executeUpdate(sql);
44         } catch (ClassNotFoundException e) {
45             e.printStackTrace();
46         } catch (SQLException e) {
47             e.printStackTrace();
48         } finally {
49             try {
50                 if(stmt != null) {
51                     stmt.close();
52                     stmt = null;
53                 }
54                 if(conn != null) {
55                     conn.close();
56                     conn = null;
57                 }
58             } catch (SQLException e) {
59                 e.printStackTrace();
60             }
61         }
62     }
63 
64 }

View Code

4.JDBC处理PreparedStatement

 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:SXT", "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 }

View Code

5.JDBC处理存储过程

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

View Code

6.JDBC事务处理

　　比如转账时至少需要两条update语句，需要同时成功。

 1 import java.sql.*;
 2 public class TestTransaction {
 3     public static void main(String[] args) {
 4         
 5         Connection conn = null;
 6         Statement stmt = null;
 7         
 8         try {
 9             Class.forName("oracle.jdbc.driver.OracleDriver");
10             conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:SXT", "scott", "tiger");
11             
12             conn.setAutoCommit(false);
13             stmt = conn.createStatement();
14             stmt.addBatch("insert into dept2 values (51, '500', 'haha')");
15             stmt.addBatch("insert into dept2 values (52, '500', 'haha')");
16             stmt.addBatch("insert into dept2 values (53, '500', 'haha')");
17             stmt.executeBatch();
18             conn.commit();
19             //恢复现场，就像那时候做坦克时，变换完前景色后，在恢复回来
20             conn.setAutoCommit(true);
21         } catch (ClassNotFoundException e) {
22             e.printStackTrace();
23         } catch(SQLException e) {
24             
25             e.printStackTrace();
26             
27             try {
28                 if(conn != null)
29                 {
30                     conn.rollback();
31                     /*
32                      * 下面这句要加上因为可能没执行到18行而异常
33                      */
34                     conn.setAutoCommit(true);
35                 }
36             } catch (SQLException e1) {
37                 e1.printStackTrace();
38             }
39         }finally {
40             try {
41                 if(stmt != null)
42                     stmt.close();
43                 if(conn != null)
44                     conn.close();
45             } catch (SQLException e) {
46                 e.printStackTrace();
47             }
48         }
49     }
50 }

View Code

7.JDBC处理可滚动结果集　

 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                     //下面是静态变量
13                     //对滚动不敏感表示你可以随便滚动
14                     ResultSet.TYPE_SCROLL_INSENSITIVE,
15                     //表示并发访问时只能读取
16                     ResultSet.CONCUR_READ_ONLY);
17             ResultSet rs = stmt
18                     .executeQuery("select * from emp order by sal");
19             rs.next();
20             System.out.println(rs.getInt(1));
21             rs.last();
22             System.out.println(rs.getString(1));
23             System.out.println(rs.isLast());
24             System.out.println(rs.isAfterLast());
25             //当前记录是第几条记录；先定位到最后一条，再调用,返回的值可表示共多少行
26             System.out.println(rs.getRow());
27             rs.previous();
28             System.out.println(rs.getString(1));
29             rs.absolute(6);
30             System.out.println(rs.getString(1));
31             rs.close();
32             stmt.close();
33             conn.close();
34         } catch (SQLException e) {
35             e.printStackTrace();
36         }
37     }
38 }

View Code

8.JDBC批处理

 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:SXT", "scott", "tiger");
 8         
 9         PreparedStatement ps = conn.prepareStatement("insert into dept2 values (?, ?, ?)");
10         ps.setInt(1, 61);
11         ps.setString(2, "haha");
12         ps.setString(3, "bj");
13         ps.addBatch();
14         
15         ps.setInt(1, 62);
16         ps.setString(2, "haha");
17         ps.setString(3, "bj");
18         ps.addBatch();
19         
20         ps.setInt(1, 63);
21         ps.setString(2, "haha");
22         ps.setString(3, "bj");
23         ps.addBatch();
24         
25         ps.executeBatch();
26         ps.close();
27         
28         conn.close();
29 
30     }
31 
32 }

View Code

9.JDBC处理可更新的结果集

　　最传统的方式往往最有效，新方式可能支持的不好。Oracle不支持的，但是MySql支持的。

 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         //更新进入数据库
17         rs.updateRow();
18 
19         //插入新行
20         rs.moveToInsertRow();
21         rs.updateInt(1, 9999);
22         rs.updateString("ename","AAAA");
23         rs.updateInt("mgr", 7839);
24         rs.updateDouble("sal", 99.99);
25         //更新进入数据库
26         rs.insertRow();
27         //将光标移动到新建的行
28         rs.moveToCurrentRow();
29 
30         //删除行
31         rs.absolute(5);
32         rs.deleteRow();
33 
34         //取消更新
35         //rs.cancelRowUpdates();
36 
37       }catch(SQLException e){
38         e.printStackTrace();
39       }
40     }
41 }

View Code

10.DataSource和RowSet简介

　　随着J2EE的标准而出现，经常和企业级应用关联在一块。

题外话：

a.找不到安装程序时先不要搜索，去程序中找属性。
b.命令行下要想使用外部jar，则需要加进入classpath；普通的java工程使用外部jar文件需要右键--build path--add external archives；最好放在自己的某一工程下不要放在系统下，防止新旧文件或者和其他人的文件冲突，这也属于耦合性的降低。
c.java.sql.*中Connection是接口，算是一流的公司卖标准，年年收专利费；但是做出让人人都遵守的接口也不容易。

posted @ 2013-07-16 11:39 加拿大小哥哥阅读(1633) 评论(10) 编辑收藏举报

刷新页面返回顶部

JDBC小结

公告