JDBC工具类的两种写法

​​​​小配置文件：jdbc.properties

driverClass=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:xe
username=hr
password=hr

1.工具类只提供Connection对象静态方法和释放资源静态方法

工具类：JDBCUtil 

package com.zp.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class JDBCUtil {
    private static DataSource dataSource ;
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<> ();

    static {
        InputStream is = JDBCUtil.class.getResourceAsStream ("/jdbc.properties");
        Properties prop = new Properties ();
        try {
            prop.load (is);
            dataSource = DruidDataSourceFactory.createDataSource (prop);
            is.close ();
        } catch (Exception e) {
            e.printStackTrace ();
        }
    }

    public static Connection getConnection(){
        Connection conn = threadLocal.get ();
        if(conn == null){
            try {
                conn = dataSource.getConnection ();
                threadLocal.set (conn);
            } catch (SQLException e) {
                e.printStackTrace ();
            }
        }
        return conn;
    }

    public static void close(Connection conn, PreparedStatement ps, ResultSet rs){
        try {
            if(rs != null){
                rs.close ();
            }
            if(ps != null){
                ps.close ();
            }
            if(conn != null){
                conn.close ();   //把conn对象返回给连接池中  没有关闭连接
                threadLocal.remove ();  //移除当前线程绑定的连接对象，怕其它人使用该线程，但该线程绑定的conn对象已经返回给连接池了   java.sql.SQLException: connection holder is null
            }
        }catch (Exception e){
            e.printStackTrace ();
        }
    }

    public static void main(String[] args) throws SQLException {
        Connection conn = getConnection ();
        System.out.println (conn);
//        conn.close (); //这里关  // conn： oracle.jdbc.driver.T4CConnection@573fd745
                           //conn2：oracle.jdbc.driver.T4CConnection@573fd745     获取的是同一个（只有多线程的时候才可能获取两个不同的）

        Connection conn2 = getConnection ();
        System.out.println (conn2);
        conn.close ();  //conn：oracle.jdbc.driver.T4CConnection@573fd745
                        //conn2：oracle.jdbc.driver.T4CConnection@15327b79
        conn2.close ();



    }
}

2.工具类提供Connection对象静态方法和释放资源静态方法并且封装增删改查

       2.1通过接口回调封装查询

  RowMapper接口

package com.zp.util;

import java.sql.ResultSet;
import java.sql.SQLException;

public interface RowMapper<T> {
    public T row(ResultSet rs) throws SQLException;
}

谁调用查询谁实现这接口，并传递实现类

dao接口实现类：

package com.zp.dao.impl;

import com.zp.dao.ProductDAO;
import com.zp.entity.Product;
import com.zp.util.RowMapper;
import com.zp.util.JDBCUtil;
import com.zp.util.JDBCUtil2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class ProductDAOImpl2 implements ProductDAO {
    @Override
    public void insert(Product product) {
        JDBCUtil2.executeUpdate ("insert into t_product values(seq_product.nextval,?,?,?)",product.getPname (),product.getPrice (),product.getDescr ());
    }

    @Override
    public void delete(int pid) {
        JDBCUtil2.executeUpdate ("delete from t_product where pid = ?",pid);
    }

    @Override
    public void update(Product product) {
        JDBCUtil2.executeUpdate ("update t_product set pname = ? , price = ? , descr = ? where pid = ?",product.getPname (),product.getPrice (),product.getDescr (),product.getPid ());
    }

    @Override
    public List<Product> selectAll() {
        List<Product> list = JDBCUtil2.selectAll ("select * from t_product", new RowMapper<Product> () {

            @Override
            public Product row(ResultSet rs) throws SQLException {
                Product product = new Product (rs.getInt ("pid"), rs.getString ("pname"), rs.getDouble ("price"), rs.getString ("descr"));
                return product;
            }
        });
        return list;
    }

    @Override
    public Product selectById(int pid) {
        Product product = JDBCUtil2.selectOne ("select * from t_product where pid = ?", new RowMapper<Product> () {
            @Override
            public Product row(ResultSet rs) throws SQLException {
                Product product = new Product (rs.getInt ("pid"), rs.getString ("pname"), rs.getDouble ("price"), rs.getString ("descr"));
                return product;
            }
        }, pid);
        return product;
    }
}

工具类：JDBCUtil2.class

package com.zp.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 封装增删改查方法
 * 使用接口回调封装查询
 */
public class JDBCUtil2 {
    private static DataSource dataSource;
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<> ();

    static {
        InputStream is = JDBCUtil2.class.getResourceAsStream ("/jdbc.properties");
        Properties prop = new Properties ();
        try {
            prop.load (is);
            dataSource = DruidDataSourceFactory.createDataSource (prop);
            is.close ();
        } catch (Exception e) {
            e.printStackTrace ();
        }
    }

    public static Connection getConnection(){
        Connection conn = threadLocal.get ();
        if (conn == null){
            try {
                conn = dataSource.getConnection ();
                threadLocal.set (conn);
            } catch (SQLException e) {
                e.printStackTrace ();
            }
        }
        return conn;
    }

    //封装增删改操作
    public static void executeUpdate(String sql,Object... objects){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection ();
            ps = conn.prepareStatement (sql);
            if (objects!=null && objects.length>0){
                for (int i = 0; i < objects.length; i++) {
                    ps.setObject (i+1,objects[i]);
                }
            }
            ps.executeUpdate ();
        }catch (Exception e){
            e.printStackTrace ();
            throw new RuntimeException (e);
        }finally {
            close (null,ps,null);
        }
    }

    //封装查询  使用接口回调
    public static <T> List<T> selectAll(String sql, RowMapper<T> rowMapper,Object... objects){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<T> list = new ArrayList<> ();
        try {
            conn = getConnection ();
            ps = conn.prepareStatement (sql);
            if(objects!=null && objects.length>0){
                for (int i = 0; i < objects.length; i++) {
                    ps.setObject (i+1,objects[i]);
                }
            }
            rs = ps.executeQuery ();
            while (rs.next ()){
                //list.add (new Product (rs.getInt ("pid"),rs.getString ("pname"),rs.getDouble ("price"),rs.getString ("descr")));
                // 不具有通用性，要想具有通用性，就让谁调用谁提供里面的这行封装代码，创建一个接口（RowMapper）,
                // 这个接口有一个row方法，谁调用谁实现，实现体包含实体类的封装，返回封装好的对象
                list.add (rowMapper.row (rs));
            }
        }catch (Exception e){
            e.printStackTrace ();
            throw new RuntimeException (e);
        }finally {
            close (null,ps,rs);
        }
        return list;
    }

    //封装查询一个  使用接口回调
    public static <T> T selectOne(String sql, RowMapper<T> rowMapper,Object... objects){
        List<T> list = selectAll (sql, rowMapper, objects);
        return list!=null?list.get (0):null;
    }

    public static void close(Connection conn, PreparedStatement ps, ResultSet rs){
        try {
            if (rs != null){
                rs.close ();
            }
            if(ps != null){
                ps.close ();
            }
            if(conn != null){
                conn.close ();
                threadLocal.remove ();
            }
        }catch (Exception e){
            e.printStackTrace ();
        }
    }
}

      2.1通过反射实现查询

   查询方法传递实体类的类对象

 @Override
    public List<Product> selectAll() {
        List<Product> list = JDBCUtil3.selectAll ("select * from t_product", Product.class);
        return list;
    }

查询封装方法

//封装查询  使用反射
    public static <T> List<T> selectAll(String sql, Class<T> clazz, Object... objects) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<T> list = new ArrayList<> ();
        try {
            conn = getConnection ();
            ps = conn.prepareStatement (sql);
            if (objects != null && objects.length > 0) {
                for (int i = 0; i < objects.length; i++) {
                    ps.setObject (i + 1, objects[i]);
                }
            }
            rs = ps.executeQuery ();

            ResultSetMetaData metaData = rs.getMetaData ();   //getMetaData方法作用：获取查询结果集的的元数据（包含了所有的列）
            int count = metaData.getColumnCount ();   //getColumnCount方法作用：获取查询结果列的总个数
//            System.out.println (count);

            T t = null;

            while (rs.next ()) {
                t = clazz.newInstance ();  //反射创建对象   一个对象代表一行数据

                for (int i = 1; i <= count; i++) {
                    String columnName = metaData.getColumnName (i);   //获取列名
                    Object value = rs.getObject (columnName);    //根据列名获取对应列的值


                    if (value == null){
                        continue;
                    }
//                    System.out.println (columnName);  //获取列名
//                    System.out.println (value);
                    columnName = "set" + columnName.substring (0, 1).toUpperCase () + columnName.substring (1).toLowerCase ();
                    //            "set"   +  "P"  +  "name"     setPname
//                    System.out.println (columnName);


                    //将每一列的值赋值给t对象中的属性  --->   要求：表中的列名和实体类属性名必须一致
                    Method[] methods = clazz.getMethods ();   //获取当前传递过来实体类中所有的方法
                    for (Method method : methods) {
                        /*
                            java.lang.IllegalArgumentException: argument type mismatch
                            出现该异常原因：rs.getObject (columnName)获取oracle中的number类型时Object对应着
                            java中的java.math.BigDecimal类型（运行时的Object就是java.math.BigDecimal类型）
                            此类型不能自动装换为int或者double
                        */
                        if (method.getName ().equals (columnName)) {
                            if (value instanceof BigDecimal) {
                                Class type = method.getParameterTypes ()[0];
                                if (type.getName ().equals ("int")){
                                    value = ((BigDecimal)value).intValue ();
                                }
                                if (type.getName ().equals ("double")){
                                    value = ((BigDecimal)value).doubleValue ();
                                }
                            }
                            method.invoke (t, value);  //java.lang.IllegalArgumentException  数据库number类型的值为空时执行该方法会报该异常
                            break;
                        }
                    }
                }


                list.add (t);
            }
        } catch (Exception e) {
            e.printStackTrace ();
            throw new RuntimeException (e);
        } finally {
            close (null, ps, rs);
        }
        return list;
    }