设计自己的JDBCUtils工具类

先看一下原始的JDBC

/**
 *@author WDreamIn
   */
public class TestJDBC {
    /**JDBC 连接
     * @param args
     * @throws ClassNotFoundException  
     * @throws SQLException 
     */

    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        System.out.println("places input your account and password:");
        Scanner input = new Scanner(System.in);

        String carId = input.nextLine();
        String pwd = input.nextLine();

        //1.加载驱动
        Class.forName("oracle.jdbc.OracleDriver");

        //2.通过DriverManager获得连接对象,提供三个实参(URL,UserName,Password)
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","hr","hr");
//      System.out.println(conn);

        String sql = "select * from t_account where id = ? and password = ? ";

        //3.获得发送SQL的工具
        //Statement stmt = conn.createStatement();

        PreparedStatement ps = conn.prepareStatement(sql);

        //3.5 绑定动态参数
        ps.setString(1 , carId);
        ps.setString(2, pwd);

        //4.发送SQL语句到数据库
//      int result = stmt.executeUpdate("insert into t_class values(30,'Java班')");
        //ResultSet rs = stmt.executeQuery("select * from t_account where id = ? and password = ? ");
        ResultSet rs = ps.executeQuery();

        //5.处理结果
        if(rs.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }

        //
//      while(rs.next()){//是否存在下一行数据
            //rs代表第一行
//          String id = rs.getString(1);
//          String password = rs.getString(2);
//          String name = rs.getString("username");
//          double balance = rs.getDouble("balance");
//          String phone = rs.getString("phone");
//          
//          System.out.println(id + "\t" +password+"\t"+name+"\t"+balance+"\t"+phone);

//      }

        //6.释放资源 ,后开先关
        rs.close();
        ps.close();
        conn.close();

    }
}

以上总结了JDBC开发的6个步骤,下面对此封装将满足重用和跨平台.

public class JDBCUtils{
    private static final Properties prop = new Properties();

    static{
        try {
            //1.读取配置文件
            InputStream is = new FileInputStream("bin\\jdbc.properties");
            //2.将文件中的key-value保存在Map中
            prop.load(is);

            String driverName = prop.getProperty("driverName");
            Class.forName(driverName);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /*
     *获得连接对象
     */
    public static Connection getConnection(){
        try {
            String  url = prop.getProperty("url");
            String username = prop.getProperty("username");
            String password = prop.getProperty("password");
            return DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /*
     * 释放资源
     */
    public static void release(Connection conn, PreparedStatement ps, ResultSet rs){
        try{
            if(rs != null){
                rs.close();
            }
            if(ps!=null){
                ps.close();
            }
            if(conn!=null){
                conn.close();
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
}

添加事务控制
借助线程工具类(可以在整个线程所持有的Map中,存储一个键(Threadlocal)值(conn)).
将参数对象(Connection conn)添加到当前线程中 :

private static final ThreadLocal<Connection> tl = new ThreadLocal<Connection>(); //工具类

修改getConnection方法：
Connection conn = tl.get(); //获取线程中保存的Connection对象
if(conn == null){ //当线程中没有保存过Connection对象时
conn = DriverManager.getConnection(url, userName, password); //获取连接对象
tl.set(conn); //并保存至线程中
}
return conn;
在release中关闭conn时，同时tl.remove(); //移除线程中的连接对象，只有业务提交或回滚之后才关闭、移除连接。

public class JDBCUtils {

    private static final ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

    private static final Properties prop = new Properties();

    static{
        try {
            //1.读取配置文件
            InputStream is = new FileInputStream("bin\\jdbc.properties");
            //2.将文件中的key-value保存在map中
            prop.load(is);
            String driverName = prop.getProperty("driverName");
            Class.forName(driverName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获得连接对象
     */
    public static Connection getConnection(){

        Connection conn = tl.get();

        try {
            String url = prop.getProperty("url");
            String username = prop.getProperty("username");
            String password = prop.getProperty("password");
            if(conn == null){
                //创建新连接
                conn = DriverManager.getConnection(url,username,password);
                //绑定在当前线程中
                tl.set(conn);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 释放资源
     */
    public static void release(Connection conn , PreparedStatement ps , ResultSet rs){
        try {
            if(rs!=null){
                rs.close();
            }

            if(ps!=null){
                ps.close();
            }

            if(conn!=null){
                conn.close();
                tl.remove();//将绑定好的连接对象移除
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

/**
 * 事物管理的封装
 * 
 * @author WDreamIn
 * 
 */
public class TransactionManager {

    // 开启事物
    public static void begin() {
        Connection conn = JDBCUtils.getConnection();
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 事物提交
    public static void commit() {
        Connection conn = JDBCUtils.getConnection();
        try {
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(conn, null, null);
        }
    }

    // 回滚事物
    public static void rollback() {
        Connection conn = JDBCUtils.getConnection();
        try {
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(conn, null, null);
        }
    }

}