老师如是说：有一个规律永远不变，就是量变引起质变，什么时候你吃的盐比我吃的米多，那你就可以教我了……
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
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 }
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 }
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 }
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 }
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 }
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 }
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 }
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 }
10.DataSource和RowSet简介
随着J2EE的标准而出现，经常和企业级应用关联在一块。
题外话：
a.找不到安装程序时先不要搜索，去程序中找属性。
b.命令行下要想使用外部jar，则需要加进入classpath；普通的java工程使用外部jar文件需要右键--build path--add external archives；最好放在自己的某一工程下不要放在系统下，防止新旧文件或者和其他人的文件冲突，这也属于耦合性的降低。
c.java.sql.*中Connection是接口，算是一流的公司卖标准，年年收专利费；但是做出让人人都遵守的接口也不容易。
