只是小人物

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
JDBC(oracle)

一.jdbc访问数据库的步骤:
1. 注册驱动
   Class.forName("oracle.jdbc.driver.OracleDriver");
2. 根据url/dbUser/dbPwd构造连接(Connection)
   Connection conn = DriverManager.getConnection(url, dbUser, dbPwd);
3. 根据Connection对象构造语句(Statement)对象
   由语句对象传递sql语句到数据库,并接收结果.
   Statement stmt = conn.createStatement();
  
   if 查询: 返回ResultSet
      ResultSet rs = stmt.executeQuery(sql);
   if DML(insert,update,delete): 返回影响的记录数
      int n = stmt.executeUpdate(sql);
4. 关闭资源   
   rs.close();
   stmt.close();
   conn.close();

需要用到的接口
java.sql.*
Connection
Statement 和PreparedStatement
ResultSet
DriverManager

例子：

  
 1 package day1;
 2 import java.sql.*;
 3 
 4 public class JDBCDemo {
 5  //驱动
 6  private static final String DRIVER
 7   ="oracle.jdbc.driver.OracleDriver";
 8  //连接字符串
 9  private static String URL 
10   = "jdbc:oracle:thin:@192.168.2.205:1521:hqr";
11  //数据库的帐号和密码
12  private static final String DBUSER = "openlab";
13  private static final String DBPWD = "open123";
14 
15  /**
16   * 使用JDBC API 连接数据库
17   */
18  public static void main(String[] args) {
19   //findAllUers();
20   System.out.println(login("1002","1234"));
21  }
22  
23  private static void findAllUers() {
24   try {
25    //1.注册驱动
26    Class.forName(DRIVER);
27    //2.构造连接
28    Connection conn 
29    = DriverManager.getConnection(URL, DBUSER, DBPWD);
30    //3.构造语句对象,执行sql语句,并接受结果
31    String sql = "select * from users_hqr";
32    Statement stmt = conn.createStatement();
33    ResultSet rs = stmt.executeQuery(sql);
34    //遍历结果集
35    while(rs.next()){
36     //操作一行数据
37     System.out.println(rs.getString("name"));
38    }
39    
40    //4.关闭资源
41   } catch (Exception e) {
42    e.printStackTrace();
43   }
44  }
45 }

 

封装成ConntceionUtils.java类
需要用到的ConnectionUtils.java类

 1 package day1;
 2 import java.io.*;
 3 import java.sql.*;
 4 import java.util.*;
 5 /**
 6  * 工具类,负责读入属性文件中的参数
 7  * 注册驱动,获得连接,关闭资源
 8  */
 9 public class ConnectionUtils {
10  private static String driver;
11  private static String url;
12  private static String dbUser;
13  private static String dbPwd;
14  public static void getParam(String filename){
15   Properties props = new Properties();
16   try {
17    //把file中的键值对数据装载入集合中
18    props.load(new FileInputStream(new File(filename)));
19    driver = props.getProperty("driver");
20    url = props.getProperty("url");
21    dbUser = props.getProperty("dbUser");
22    dbPwd = props.getProperty("dbPwd");
23   } catch (FileNotFoundException e) {
24    e.printStackTrace();
25   } catch (IOException e) {
26    e.printStackTrace();
27   }
28  }
29  /**
30   * 根据四个全局变量参数构造连接对象并返回
31   * @return Connection对象 
32   */
33  public static Connection getConnection(){
34   Connection conn = null;
35   try {
36    getParam("src/db_oracle.properties");
37    Class.forName(driver);
38    conn = DriverManager.getConnection(url, dbUser, dbPwd);
39   } catch (Exception e) {
40    e.printStackTrace();
41   }
42   return conn;
43   
44  }
45  public static void close(ResultSet rs){
46   if(rs != null){
47    try {
48     rs.close();
49    } catch (SQLException e) {
50     e.printStackTrace();
51    }
52   }
53  }
54  public static void close(Statement stmt){
55   if(stmt != null){
56    try {
57     stmt.close();
58    } catch (SQLException e) {
59     e.printStackTrace();
60    }
61   }
62  }
63  public static void close(Connection conn){
64   if(conn != null){
65    try {
66     conn.close();
67    } catch (SQLException e) {
68     e.printStackTrace();
69    }
70   }
71  }
72  
73 }

 


二.JDBC 的 DML操作(增,改,删)

例子:

  1 package day1;
  2 import java.sql.*;
  3 public class JDBCDemo_DML {
  4  public static void main(String[] args) {
  5   System.out.println(changePwd(1002,5678)?"update success":"update faild");
  6   System.out.println(deleteUser(1001)?"success":"faild");
  7   addUser(1015,"dsads",1234,"19212512457","24@2.com");
  8  }
  9  public static boolean changePwd(int id,int pwd){
 10   String sql = "update users_hqr set pwd = " + pwd +
 11      " where id = " + id;
 12   boolean flag  = false;
 13   Connection conn = ConnectionUtils.getConnection();
 14   Statement stmt = null;
 15   try {
 16    stmt = conn.createStatement();
 17    int n = stmt.executeUpdate(sql);
 18    if(n == 1){
 19     flag = true;
 20    }
 21   } catch (SQLException e) {
 22    e.printStackTrace();
 23   } finally{
 24    ConnectionUtils.close(stmt);
 25    ConnectionUtils.close(conn);
 26   }
 27   return flag;
 28  }
 29  
 30  /**
 31   * 删除考生
 32   * @param id 指定的id
 33   * @return if成功返回true,否则返回false;
 34   */
 35  public static boolean deleteUser(int id){
 36   String sql = "delete users_hqr where id = " + id;
 37   boolean flag = false;
 38   Connection conn = ConnectionUtils.getConnection();
 39   Statement stmt = null;
 40   try {
 41    stmt = conn.createStatement();
 42    int n = stmt.executeUpdate(sql);
 43    if (n == 1) {
 44     flag = true;
 45    }
 46   } catch (SQLException e) {
 47    e.printStackTrace();
 48   } finally {
 49    ConnectionUtils.close(stmt);
 50    ConnectionUtils.close(conn);
 51   }
 52   return flag;
 53  }
 54  /**
 55  *添加用户方法1
 56  */
 57  public static boolean addUser_1(int id,int pwd,String name,
 58    String phone,String email){
 59   String sql = "insert into users_hqr " +
 60     "values ("+id+",'"+name+"',"+pwd+",'"+phone+"','"+email+"')" ;
 61   boolean flag  = false;
 62   Connection conn = ConnectionUtils.getConnection();
 63   Statement stmt = null;
 64   try {
 65    stmt = conn.createStatement();
 66    int n = stmt.executeUpdate(sql);
 67    if(n == 1){
 68     flag = true;
 69    }
 70   } catch (SQLException e) {
 71    e.printStackTrace();
 72   } finally{
 73    ConnectionUtils.close(stmt);
 74    ConnectionUtils.close(conn);
 75   }
 76   return flag;
 77  }
 78  /**
 79  *添加用户方法2
 80  */
 81  public static boolean addUser(int id,String name,int pwd,
 82    String phone,String email){
 83   String sql = "insert into users_hqr values(?,?,?,?,?)";
 84   boolean flag  = false;
 85   Connection conn = ConnectionUtils.getConnection();
 86   PreparedStatement stmt = null;
 87   try {
 88    stmt = conn.prepareStatement(sql);
 89    //给sql语句中的占位符赋值
 90    stmt.setInt(1, id); 
 91    stmt.setString(2, name);
 92    stmt.setInt(3, pwd);
 93    stmt.setString(4, phone);
 94    stmt.setString(5, email);
 95    stmt.executeUpdate();
 96   } catch (Exception e) {
 97    e.printStackTrace();
 98   } finally{
 99    ConnectionUtils.close(stmt);
100    ConnectionUtils.close(conn);
101   }
102   return flag;
103  }
104 }

 

三.JDBC中的事务(需要导入ConnectionUtils类)
sqlplus: 显式终止事务(提交 或 回滚)
  set autocommit on|off :切换是否自动提交
 默认关闭自动提交(off)
JDBC:自动提交,autocommit on
  conn.setAutoCommit(false);
 dml1: -500;
 dml2:+500;
 if(没有问题) conn.commit();
 else conn.rollback

例子:
1.帐户表,帐户A,帐户B
2.程序TransactionDemo
conn.setAutoCommit(false);
int n1 = stmt.executeUpdate(sql1);
int n2 = stmt.executeUpdate(sql2);

TransactionDemo.java:

 1 package day2;
 2 import java.sql.*;
 3 
 4 import day1.ConnectionUtils;
 5 /**
 6  * 测试JDBC中的事务
 7  * @author soft01
 8  *
 9  */
10 public class TransactionDemo {
11  public static void main(String[] args) {
12   transferMoney(500);
13  }
14  /**
15   * 转帐，从帐户A转到帐户B
16   */
17  public static void transferMoney(double money){
18   String sql1 = "update account_hqr set money = money - " + money
19    + " where id = 'A' ";
20   String sql2 = "update account_hqr set money = money + " + money
21    + " where id = 'B' ";
22   Connection conn = ConnectionUtils.getConnection();
23   Statement stmt = null;
24   try{
25    conn.setAutoCommit(false);//关闭自动提交
26    stmt = conn.createStatement();
27    int n1 = stmt.executeUpdate(sql1);
28    int n2 = stmt.executeUpdate(sql2);
29    if(n1==1 && n2==1){
30     conn.commit();
31    }else{
32     conn.rollback();
33    }
34   }catch (Exception e){
35    try {
36     conn.rollback();
37    } catch (SQLException e1) {
38     // TODO Auto-generated catch block
39     e1.printStackTrace();
40    }
41    e.printStackTrace();
42   } finally{
43    ConnectionUtils.close(stmt);
44    ConnectionUtils.close(conn);
45   }
46   
47  }
48 
49 }

 


四.JDBC中的批处理
批处理:批量处理SQL语句
 缓存:用来存放批量的SQL语句.

比如:JDBC中批量执行insert语句
1.create table mytemp_hqr(id number primary key);

2.BatchDemo.java
conn.setAutoCommit(false);
for(int i = 0; i < 10000; i +=){
 String sql =" insert into mytemp values(?)";
 stmt.addBatch(sql);
 if(i % 1000 ==0){
  stmt.executeBatch(); 
  stmt.clearBatch();
 }
}
stmt.executeBatch();
conn.commit();
批处理时必须关掉JDBC的自动提交功能

源代码:

 1 package day2;
 2 import java.sql.*;
 3 
 4 import day1.ConnectionUtils;
 5 /**
 6  * 测试JDBC批量处理操作
 7  * @author soft01
 8  *
 9  */
10 public class BatchDemo {
11  public static void main(String[] args) {
12   addTemp();
13  }
14  /**
15   * 批量增加数据到mytemp_hqr表中
16   */
17  public static void addTemp() {
18   String sql = "insert into mytemp_hqr values(?)";
19   Connection conn = ConnectionUtils.getConnection();
20   PreparedStatement stmt = null;
21   try { 
22    //关闭自动提交
23    conn.setAutoCommit(false);
24    //获得于编译语句对象
25    stmt = conn.prepareStatement(sql);
26    //循环100次,把sql加入到Batch中
27    for(int i=1;i<=100;i++){
28      
29     stmt.setInt(1, i);
30     stmt.addBatch();
31     if(i % 10 ==0){
32      stmt.executeBatch();
33      stmt.clearBatch();
34     }
35    }
36    stmt.executeBatch();
37    conn.commit();
38   } catch (SQLException e) {
39    // TODO Auto-generated catch block
40    e.printStackTrace();
41   }
42  }
43 }

 

posted on 2012-05-15 23:52  只是小人物  阅读(2015)  评论(0)  编辑  收藏  举报