JDBC数据库中表更新

 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 
 8 public class JdbcUpdate {
 9 
10     /**
11      * @param args
12      */
13     public static void main(String[] args) {
14         try {
15             //加载驱动
16             Class.forName("oracle.jdbc.driver.OracleDriver");
17             //获取连接
18             Connection con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.16.111:1521:orcl","scott","tiger");
19             //获得句柄
20             Statement sta = con.createStatement();
21             
22             //SQL查询命令
23             String sqlFind = "select * from empbak";
24             //从emp中复制一个新表为empbak
25             String sqlCreTable = "create table empbak as select * from emp";
26             //NVL (expr1, expr2)：expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一
27             String sqlUpdate = "update  empbak set sal = sal+200,comm = nvl(comm+100,100) where sal > (select avg(sal) from empbak )";
28             //复制表
29             int temp = sta.executeUpdate(sqlCreTable);
30             
31             //初始查询
32             //发送SQL语句，如果发送的是select,使用executeQuery；如果发送的是INSERT,DELETE,UPDATE，使用executeUpdate
33             ResultSet rs1 = sta.executeQuery(sqlFind);
34             while(rs1.next()){
35                 System.out.print(rs1.getInt("empno")+"	");
36                 System.out.print(rs1.getString("ename")+"	");
37                 System.out.print(rs1.getString("job")+"	");
38                 System.out.print(rs1.getInt("mgr")+"	");
39                 System.out.print(rs1.getString("hiredate")+"	");
40                 System.out.print(rs1.getFloat("sal")+"	");
41                 System.out.print(rs1.getFloat("comm")+"	");
42                 System.out.println();
43             }
44             System.out.println("********************更新以后*********************");
45             int temp1 = sta.executeUpdate(sqlUpdate);
46             ResultSet rs = sta.executeQuery(sqlFind);
47             while(rs.next()){
48                 System.out.print(rs.getInt("empno")+"	");
49                 System.out.print(rs.getString("ename")+"	");
50                 System.out.print(rs.getString("job")+"	");
51                 System.out.print(rs.getInt("mgr")+"	");
52                 System.out.print(rs.getString("hiredate")+"	");
53                 System.out.print(rs.getFloat("sal")+"	");
54                 System.out.print(rs.getFloat("comm")+"	");
55                 System.out.println();
56             }
57             rs.close();
58             sta.close();
59             con.close();
60         } catch (ClassNotFoundException e) {
61             // TODO 自动生成的 catch 块
62             e.printStackTrace();
63         } catch (SQLException e) {
64             // TODO 自动生成的 catch 块
65             e.printStackTrace();
66         }
67     }
68 
69 }