JDBC DAO实例优化版
- BaseDAO抽象类
public abstract class BaseDAO<T>{
private Class<T> clazz=null;
{
/*
* 获取当前BaseDAO的子类继承的父类中的泛型
* */
//获取当前类的直接父类
Type genericSuperclass = this.getClass().getGenericSuperclass();
//将当前类的直接父类强转成参数化类型,如Collection <String>
ParameterizedType paramType= (ParameterizedType) genericSuperclass;
//获取泛型参数
Type[] actualTypeArguments = paramType.getActualTypeArguments();
clazz= (Class<T>) actualTypeArguments[0];
}
//通用的增删改操作 ----version 2.0(考虑上事务)
public int update(Connection conn, String sql, Object...args){
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for(int i=0;i< args.length;i++){
ps.setObject(i+1,args[i]);
}
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null,ps);
}
return 0;
}
//通用的查询操作,用于返回数据表中的一条记录(version 2.0:考虑上事务)
public T getInstance(Connection conn,String sql,Object...args){
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps = conn.prepareStatement(sql);
//填充占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//查询
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
if(rs.next()){
Constructor<T> declaredConstructor = clazz.getDeclaredConstructor();
T t = declaredConstructor.newInstance();
for (int i=0;i<columnCount;i++){
//得到结果集中的列名
String columnName = rsmd.getColumnLabel(i + 1);
//得到结果集中的值
Object columnValue = rs.getObject(i + 1);
//通过反射得到Customer对应的字段
Field field= clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null,ps,rs);
}
return null;
}
//通用的查询操作,返回多条记录的集合
public List<T> getForList(Connection conn, String sql, Object...args){
List<T> list=new ArrayList<>();
PreparedStatement ps=null;
ResultSet rs=null;
try {
//2.预编译sql语句
ps = conn.prepareStatement(sql);
//3.填充占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//4.执行查询
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (rs.next()){
Constructor<T> declaredConstructor = clazz.getDeclaredConstructor();
T t = declaredConstructor.newInstance();
for (int i=0;i<columnCount;i++){
//获取当前列当前行的列名和值
String columnLabel = rsmd.getColumnLabel(i + 1);
Object columnValue = rs.getObject(i + 1);
Field declaredField = clazz.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(t,columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null,ps,rs);
}
return null;
}
//用于查询特殊值的通用的方法
public <E>E getValue(Connection conn,String sql,Object...args) {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for (int i=0;i< args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if (rs.next()){
return (E)rs.getObject(1);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.close(null,ps,rs);
}
return null;
}
}
2.CustomerDAO接口
public interface CustomerDAO {
/*
* 将cust对象添加到数据库中
* */
void insert(Connection conn, Customer cust);
/*
* 针对指定的id,删除表中的一条记录
* */
void deleteById(Connection conn,int id);
/*
*针对于内存中的cust对象,去修改数据表中指定的记录
* */
void update(Connection conn,Customer cust);
/*
* 针对指定的id查询得到对应的Customer对象
* */
Customer getCustomerById(Connection conn,int id);
/*
* 查询表中的所有记录构成的集合
* */
List<Customer> getAll(Connection conn);
/*
* 返回数据表中的数据的条目数
* */
Long getCount(Connection conn);
Date getMaxBirth(Connection conn);
}
3.CustomerDAOImpl类
public class CustomerDAOImpl extends BaseDAO<Customer> implements CustomerDAO {
@Override
public void insert(Connection conn, Customer cust) {
String sql="insert into customers(name,email,birth) values(?,?,?)";
update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth());
}
@Override
public void deleteById(Connection conn, int id) {
String sql="delete from customers where id = ?";
update(conn,sql,id);
}
@Override
public void update(Connection conn, Customer cust) {
String sql="update customers set name=?,email=?,birth=? where id=?";
update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
}
@Override
public Customer getCustomerById(Connection conn, int id) {
String sql="select id,name,email,birth from customers where id=?";
Customer customer = (Customer) getInstance(conn,sql, id);
return customer;
}
@Override
public List<Customer> getAll(Connection conn) {
String sql="select id,name,email,birth from customers";
List<Customer> list = getForList(conn, sql);
return list;
}
@Override
public Long getCount(Connection conn) {
String sql="select count(*) from customers";
return (Long) getValue(conn, sql);
}
@Override
public Date getMaxBirth(Connection conn) {
String sql="select max(birth) from customers";
return (Date) getValue(conn,sql);
}
}