JDBC DAO示例
- BaseDAO抽象类
/*
*dao()
*封装了针对数据表的通用的操作
*
*
*@author 刘鑫
*@create by 2021/5/9 21:59
*/
public abstract class BaseDAO {
//通用的增删改操作 ----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>T getInstance(Connection conn,Class<T> clazz,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 <T> List<T> getForList(Connection conn, Class<T> clazz, 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;
}
}
- Customer类:对应数据库中的一张表
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
public Customer() {
}
public Customer(int id, String name, String email, Date birth) {
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", birth=" + birth +
'}';
}
}
3.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);
}
4.CustomerDAOImpl类
public class CustomerDAOImpl extends BaseDAO 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 = getInstance(conn, Customer.class, 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, Customer.class, sql);
return list;
}
@Override
public Long getCount(Connection conn) {
String sql="select count(*) from customers";
return getValue(conn, sql);
}
@Override
public Date getMaxBirth(Connection conn) {
String sql="select max(birth) from customers";
return getValue(conn,sql);
}
}