JDBC自定义框架

用到的连接池:druid-1.0.9.jar
Druid是阿里巴巴开发的号称为监控而生的数据库连接池，Druid是目前最好的数据库连接池。在功能、性能、扩
展性方面，都超过其他数据库连接池，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况。
Druid下载地址:https://github.com/alibaba/druid
提前写好的JdbcUtils类

package com.example.jdbcUtils;

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;

/**
 * 该类的作用:
 * jdbc工具类
 */
public class JdbcUtils {
    private static  DataSource dataSource;
    static {
        try {
            //获取src目录下druid连接池的配置文件并创建数据源
            InputStream is = JdbcUtils.class.getResourceAsStream("/druid.properties");
            Properties properties = new Properties();
            properties.load(is);
            dataSource= DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //返回当前数据源
    public static DataSource getDataSource() {
        return dataSource;
    }

    public static Connection getConnection(){
        //获取数据库的连接
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
          throw new RuntimeException(e);
        }
    }
    public static void close(Connection connection,PreparedStatement preparedStatement){
        close(connection,preparedStatement,null);
    }
    public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        //释放资源
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement!=null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

分析：实现框架将分为两大类
1.增删改
2.查询
简单实现增删改功能:

private DataSource dataSource;
//返回当前数据源
public JdbcTemplet(DataSource dataSource) {
    this.dataSource=dataSource;
}
public int Update(String sql,Object...params) {
    Connection connection = null;
    PreparedStatement pstm=null;
    try {
        connection = dataSource.getConnection();
        pstm  = connection.prepareStatement(sql);
     	  //查询sql中的占位符个数
        ParameterMetaData parameterMetaData = pstm.getParameterMetaData();
        int parameterCount = parameterMetaData.getParameterCount();
        //判断传入的占位符个数与实际传递的参数个数是否为一致
        if (parameterCount != params.length) {
            throw new RuntimeException("您的SQL语句中的占位符个数和实际传递的参数数量不一致");
        }
         //给占位符设置具体的参数值
        for (int i = 0; i < parameterCount; i++) {
            pstm.setObject(i + 1, params[i]);
        }
        // 返回影响当前行数的int值
        return pstm.executeUpdate();

    } catch (SQLException e) {
       throw new RuntimeException(e);
    }finally {
        //释放资源
        JdbcUtils.close(connection,pstm);
    }
}

测试:

@Test
public void test(){
    DataSource dataSource = JdbcUtils.getDataSource();
    JdbcTemplet jdbcTemplet = new JdbcTemplet(dataSource);
    jdbcTemplet.Update("update product set price=10000 where pid=?", 1);
}

查询功能较复杂:
实现如下：

public Object query(String sql,ResultSetHandler handler, Object...params){
    Connection connection=null;
    PreparedStatement pstm=null;
    ResultSet resultSet=null;
    try {
        connection= dataSource.getConnection();
        pstm =connection.prepareStatement(sql);
        //查询sql中的占位符个数
        ParameterMetaData parameterMetaData = pstm.getParameterMetaData();
        int parameterCount = parameterMetaData.getParameterCount();
          //判断传入的占位符个数与实际传递的参数个数是否为一致
        if(parameterCount!=params.length){
            throw new RuntimeException("您的SQL语句中的占位符个数和实际传递的参数数量不一致");
        }
         //给占位符设置具体的参数值
        for (int i = 0; i <parameterCount; i++) {
            pstm.setObject(i+1,params[i]);
        }
        resultSet = pstm.executeQuery();
        System.out.println(resultSet);
         //采用策略设计模式来适应各种的不确定性（确定：结果都在ReusltSet中，不确定的是：封装到什么对象中，是一个还是多个）
         //使用结果处理器（ResultSetHandler）封装一下
        return handler.handle(resultSet);
    } catch (SQLException e) {
        throw new RuntimeException("出选了错误",e);
    }finally {
        JdbcUtils.close(connection,pstm,resultSet);
    }
}

ResultSetHandler接口

public interface ResultSetHandler {
    Object handle(ResultSet rs);
}

编写封装一个对象的处理器

**public class BeanHandler implements ResultSetHandler {
    private Class  clazz;
    //返回具体的封装类型
    public BeanHandler(Class clazz){
        this.clazz=clazz;
    }
    @Override
    public Object handle(ResultSet rs) {
        try {
            if (!rs.next()){
                return null;
            }
              Object bean = clazz.newInstance();//获取传入对象的一个实例
            //封装数据到bean中
            //前提：数据库字段名和JavaBean的字段名保持了一致
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();//列数
            for (int i = 0; i <count ; i++) {
                String columnName = rsmd.getColumnName(i + 1);//取得列名
                Object columnData = rs.getObject(i + 1);//取得值
                Field field = clazz.getDeclaredField(columnName); //获得传入类私有的属性 （Bean的字段名：private int id;...）
                field.setAccessible(true);//暴力破解
                field.set(bean,columnData);//将columnData值放入bean对象的当前私有属性中
                // 1
            }
            return bean;
        }catch (Exception e) {
         throw new RuntimeException("封装数据失败，请检查您的bean是否符合限制条件",e);            }
    }
}

javaBean

public class Product {
    private int pid;
    private String pname;
    private double price;

    @Override
    public String toString() {
        return "Product{" +
                "pid=" + pid +
                ", pname='" + pname + '\'' +
                ", price=" + price +
                '}';
    }

    public int getPid() {
        return pid;
    }

    public void setPid(int pid) {
        this.pid = pid;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public Product() {

    }
}

测试:

@Test
public void test4() {
    DataSource dataSource = JdbcUtils.getDataSource();
    JdbcTemplet jdbcTemplet = new JdbcTemplet(dataSource);
    Object query = jdbcTemplet.query("select * from product where pid=?", new BeanHandler(Product.class), 2);
    System.out.println(query);
}

封装多条记录的处理器
原理如上，将返回的bean对象增加到LIST集合中并返回.

public class BeanListHandler implements ResultSetHandler {
    private Class clazz;
public BeanListHandler(Class clazz) {
    this.clazz = clazz;
}

@Override
public Object handle(ResultSet rs) {
    try {
        List list = new ArrayList<>();
        while (rs.next()) {
            System.out.println();
            Object bean = clazz.newInstance();
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();
            for (int i = 0; i < count; i++) {
                String columnName = rsmd.getColumnName(i + 1);
                Object columnData = rs.getObject(i + 1);
                System.out.println(columnName + columnData);
                Field field = clazz.getDeclaredField(columnName);
                field.setAccessible(true);
                field.set(bean, columnData);
            }
            list.add(bean);
        }
        return list;
    } catch (Exception e) {
        throw new RuntimeException("封装数据失败，请检查您的bean是否符合限制条件",e);
    }
    }
  }

测试

@Test
public void test5() {
    DataSource dataSource = JdbcUtils.getDataSource();
    JdbcTemplet jdbcTemplet = new JdbcTemplet(dataSource);
    Object query = jdbcTemplet.query("select * from product", new BeanListHandler(Product.class));
    System.out.println(query);
}