DBUtils框架
DBUtils框架的作用
DBUtils框架能够将将JDBC的方法封装,大大简化了Dao层的操作,且提供出了处理结果集的接口,可以依据项目做拓展。让整个JDBCUitls也变得更加简单。
1.框架处理结果集的接口:
ResultSetHandler(结果集处理的接口)、ColumnRrocessor(列接受器接口)
public interface ResultSetHandler<T> {
/**
* 返回的结果集
* @param resultSet
* @return
*/
T handler(ResultSet resultSet) throws SQLException, IllegalAccessException, InstantiationException, InvocationTargetException;
}
public interface ColumnProcessor<T> {
/**
* 处理列的数据
* @param resultSet
* @return
*/
T[] handler (ResultSet resultSet) throws SQLException;
}
2.框架的异常处理:
DBUtilException
public class DBUtilException extends RuntimeException {
private int code;
private String errorMessage;
public DBUtilException(int code,String errorMessage){
super(errorMessage); //错误信息传递给父异常
this.code=code;
this.errorMessage=errorMessage;
}
}
3. 存放错误码的枚举类:
ResponseCode
public enum ResponseCode {
SELECT_MANY_NUMBER(555,"查询出多个结果,与预期要求不符"),
SELECT_RESULT_NONE(444,"结果集为空,与预期不相符合"),
SELECT_RESULT_ERROR(333,"处理集使用错误"),
SELECT_PARAMETER_DIFFERENT(222,"参数个数不同,无法参与查询"),
SELECT_PARAMETER_EMPTY(111,"含有空参数,无法参与查询");
private int code;
private String msg;
private ResponseCode(int code ,String msg){
this.code = code;
this.msg = msg;
}
public int getCode() {
return code;
}
public String getMsg() {
return msg;
}
}
4. 框架的核心执行类:
QueryRunner:通过处理集处理SQL语句,并返回相应的返回值
public class QueryRunner {
//设置数据源
private DataSource dataSource;
//线程局部变量存储连接对象
private static ThreadLocal<Connection> local =new ThreadLocal<>();
//有参构造方法,初始化数据源
public QueryRunner(DataSource dataSource){
this.dataSource=dataSource;
}
/**
* 获取连接对象
* @return 连接对象
*/
public Connection getConnection() throws SQLException {
Connection connection=local.get();
//查看当前local是否存有连接对象
if(connection!=null){
return connection;
}else{
//从数据源中获取一个新的connection
connection = dataSource.getConnection();
//装入local
local.set(connection);
return connection;
}
}
/**
* 对表的更新操作(插入、修改、删除)
* @param sql sql语句
* @param args 参数
* @return 影响行数
*/
public int update(String sql,Object... args) throws SQLException {
//获取连接对象
Connection connection = getConnection();
//获取提交SQL语句的对象
PreparedStatement statement = getPreparedStatement(sql, connection, args);
//提交sql给数据库 获取返回值:受影响的行数
int rows = statement.executeUpdate();
close(statement,null);
return rows;
}
/**
* 增加处理事物的能力
* @param connection 事务的连接对
* @param sql sql语句
* @param args 参数
* @return 影响的行数
* @throws SQLException
*/
public int update(Connection connection,String sql,Object... args) throws SQLException {
//获取提交SQL语句的对象
PreparedStatement statement = getPreparedStatement(sql, connection, args);
//提交sql给数据库 获取返回值:受影响的行数
int rows = statement.executeUpdate();
close(statement,null);
return rows;
}
/**
* 查询数据,根据结果集的处理方式返回
* @param sql
* @param resultSetHandler 处理结果集对象
* @param args
* @param <T>
* @return
* @throws SQLException
*/
public <T> T query(String sql, ResultSetHandler<T> resultSetHandler, Object... args) throws SQLException, IllegalAccessException, InvocationTargetException, InstantiationException {
//参数判断
if (StringUtils.isEmpty(sql)||resultSetHandler==null){
throw new DBUtilException(ResponseCode.SELECT_PARAMETER_EMPTY.getCode(),ResponseCode.SELECT_PARAMETER_EMPTY.getMsg());
}
PreparedStatement preparedStatement = getPreparedStatement(sql, getConnection(), args);
//获取返回结果集
ResultSet resultSet = preparedStatement.executeQuery();
T t = resultSetHandler.handler(resultSet);
close(preparedStatement,resultSet);
return t;
}
/**
* 处理SQL语句的方法
* @param sql
* @param connection
* @param args
* @return
* @throws SQLException
*/
private PreparedStatement getPreparedStatement(String sql, Connection connection, Object[] args) throws SQLException {
PreparedStatement statement = connection.prepareStatement(sql);
//获取sql参数的元数据
ParameterMetaData parameterMetaData = statement.getParameterMetaData();
//获取参数个数(SQL语句中占位符的个数)
int parameterCount = parameterMetaData.getParameterCount();
//判断占位符和去args参数个数是否相等
if (parameterCount!= args.length){
throw new DBUtilException(ResponseCode.SELECT_PARAMETER_DIFFERENT.getCode(), ResponseCode.SELECT_PARAMETER_DIFFERENT.getMsg());
}
//循环设置SQL中的占位符
for (int i = 0; i < parameterCount; i++) {
statement.setObject(i+1, args[i]);
}
return statement;
}
public void close(PreparedStatement statement,ResultSet resultSet) throws SQLException {
Connection connection = local.get();
if (resultSet!=null){
resultSet.close();
}
if (statement!=null){
statement.close();
}
if(null!=connection){
connection.close();
local.remove();
}
}
}
5. 基础的结果集处理器和列处理器
BeanHandler:处理查询到的单行数据
/**
* 返回单行值,自动封装为对象
* @param <T>
*/
public class BeanHandler<T> implements ResultSetHandler<T> {
//设置封装对象
private Class laCass;
public BeanHandler(Class laCass){
this.laCass=laCass;
}
@Override
public T handler(ResultSet resultSet) throws SQLException, IllegalAccessException, InstantiationException, InvocationTargetException {
//获取结果集的元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//通过元数据获取列的数量
int columnCount = metaData.getColumnCount();
//开始封装对象
//利用反射实例化对象
T t = (T) laCass.newInstance();
//判断结果集指针下是否有数据
if (!resultSet.next()){
return null;
}
//获取查询行的数据
for (int i = 0; i < columnCount; i++) {
//获取列名
String columnName = metaData.getColumnName(i + 1);
//获取值
Object object = resultSet.getObject(i + 1);
//使用BeanUtils工具类封装
BeanUtils.copyProperty(t,columnName,object);
}
//再次判断是否有多行数据
if (resultSet.next()){
throw new DBUtilException(ResponseCode.SELECT_MANY_NUMBER.getCode(),ResponseCode.SELECT_MANY_NUMBER.getMsg());
}
return t;
}
}
BeanListHandler:处理查询到的多行数据
public class BeanListHandler<T> implements ResultSetHandler<List<T>> {
//设置封装对象
private Class laCass;
public BeanListHandler(Class laCass){
this.laCass=laCass;
}
@Override
public List<T> handler(ResultSet resultSet) throws SQLException, IllegalAccessException, InstantiationException, InvocationTargetException {
//获取结果集的元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//获取列的数量
int columnCount = metaData.getColumnCount();
//创建集合
List<T> ts = new ArrayList<T>();
while (resultSet.next()){
//创建封装对象
T t = (T) laCass.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取列名
String columnName = metaData.getColumnName(i + 1);
//获取值
Object object = resultSet.getObject(i + 1);
//使用BeanUtils工具类封装
BeanUtils.copyProperty(t,columnName,object);
}
ts.add(t);
}
return ts;
}
}
BeanArrayHandler:处理查询到的单列数据
注:这里使用到了处理单列的列处理器
public class BeanArrayHandler<T> implements ResultSetHandler<T[]> {
private ColumnProcessor columnProcessor;
public BeanArrayHandler(ColumnProcessor columnProcessor){
this.columnProcessor = columnProcessor;
}
@Override
public T[] handler(ResultSet resultSet) throws SQLException {
if (!resultSet.next()){
throw new DBUtilException(ResponseCode.SELECT_RESULT_NONE.getCode(), ResponseCode.SELECT_RESULT_NONE.getMsg());
}
return (T[]) columnProcessor.handler(resultSet);
}
}
ColumnProcessor:单列处理器
public class BasicColumnProcessor<T> implements ColumnProcessor<T> {
private Class aClass;
public BasicColumnProcessor(Class aClass){
this.aClass = aClass;
}
@Override
public T[] handler(ResultSet resultSet) throws SQLException {
//游标还原
resultSet.beforeFirst();
//获取结果集中的元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//获取返回的列数
int columnCount = metaData.getColumnCount();
//判断当前列是否是1
if (columnCount!=1){
throw new DBUtilException(ResponseCode.SELECT_RESULT_ERROR.getCode(), ResponseCode.SELECT_RESULT_ERROR.getMsg());
}
//创建集合存放数据
ArrayList<T> ts = new ArrayList<>();
while (resultSet.next()){
//获取行的单个数据
T t = (T) resultSet.getObject(1);
ts.add(t);
}
return getArray(ts);
}
public T[] getArray(List<T> tList){
T[] t = (T[]) Array.newInstance(aClass,tList.size());
for (int i = 0; i <tList.size() ; i++) {
t[i]=tList.get(i);
}
return t;
}
}
在JDBCUtils中使用DBUtils工具类
public class JDBCUtils {
//定义线程池
private static DruidDataSource dataSource;
//静态代码块初始化
static {
dataSource = new DruidDataSource();
dataSource.setUrl("jdbc:mysql:///studentsystem?useUnicode=true&characterEncoding=UTF-8");
dataSource.setUsername("root");
dataSource.setPassword("1234");
}
/**
* 返回的QueryRunner对象
* @return
*/
public static QueryRunner queryRunner(){
return new QueryRunner(dataSource);
}
}
Dao层实现类使用示例:
public class UserDAOImpl implements IUserDAO {
/**
* 插入数据
* @param user
* @throws SQLException
*/
@Override
public void insert(User user) throws SQLException {
JDBCUtils.queryRunner().update("insert into user(userName,password) values(?,?)",user.getUserName(),user.getPassword());
}
/**
* 修改数据
* @param user
* @throws SQLException
*/
@Override
public void update(User user) throws SQLException {
JDBCUtils.queryRunner().update("update user set userName=?,password=? where id=?",user.getUserName(),user.getPassword(),user.getId());
}
/**
* 通过id查询
* @param id
* @return
* @throws InvocationTargetException
* @throws SQLException
* @throws InstantiationException
* @throws IllegalAccessException
*/
@Override
public User selectUserById(int id) throws InvocationTargetException, SQLException, InstantiationException, IllegalAccessException {
return JDBCUtils.queryRunner().query("select * from user where id=?",new BeanHandler<User>(User.class),id);
}
/**
* 查询所有
* @return
* @throws InvocationTargetException
* @throws SQLException
* @throws InstantiationException
* @throws IllegalAccessException
*/
@Override
public List<User> select() throws InvocationTargetException, SQLException, InstantiationException, IllegalAccessException {
return JDBCUtils.queryRunner().query("select * from user",new BeanListHandler<User>(User.class));
}
/**
* 删除数据
* @param id
* @throws SQLException
*/
@Override
public void delete(int id) throws SQLException {
JDBCUtils.queryRunner().update("delete from user where id=?",id);
}
}