BaseDAO类
/**
*
* @Description 封装了通用的操作(考虑事务)
* @author lxystart Email:1323915490@qq.com
* @version
* @date 2021年3月28日下午2:17:58
*
*/
public class BaseDAO {
// 通用的增删改操作,考虑事务(1获取连接从外部获取2.连接不关闭),返回受影响的行数
public int update(Connection conn, String sql, Object... args) {
// 2.预编译,返回PreparedStatement的实例,填充占位符
PreparedStatement ps = null;
try {
// 1。与数据库建立连接
InputStream is = ClassLoader.getSystemResourceAsStream("jdbc.properties");
Properties p = new Properties();
p.load(is);
String user = p.getProperty("user");
String password = p.getProperty("password");
String url = p.getProperty("url");
String driverClass = p.getProperty("driverClass");
// 注册驱动
Class.forName(driverClass);
conn = DriverManager.getConnection(url, user, password);
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
// 3.执行
return ps.executeUpdate();
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} finally {
// 资源的关闭,没有关闭连接
CRUD.closeRerouse(ps, null);
}
return 0;
}
// 通用的查询操作,返回数据库的一条记录,并封装成对象(考虑事务)
public <T> T getInstance(Connection conn, Class<T> clazz, String sql, Object... args) {
// 获取连接
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = CRUD.getConnection();
// 预编译sql语句,返回PrepareStatement的一个实例
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
// 执行并返回结果集,结果集获取字段值
rs = ps.executeQuery();
// 元数据获取数据的数据(字段值的修饰数据)
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
// 因为返回多个个对象,所以都判断
if (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object coulumnValue = rs.getObject(i + 1);
String columnLable = metaData.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLable);
field.setAccessible(true);
field.set(t, coulumnValue);
}
return t;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 资源的关闭
CRUD.closeRerouseAll(ps, null, rs);
}
return null;
}
//通用的查询操作,返回任意表中的多个记录(并封装成集合),考虑事务
public <T> List<T> getInstances(Connection conn,Class<T> clazz,String sql,Object...args) {
// 从外部获取连接
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = CRUD.getConnection();
// 预编译sql语句,返回PrepareStatement的一个实例
ps = conn.prepareStatement(sql);
for(int i = 0; i <args.length;i++) {
ps.setObject(i+1, args[i]);
}
// 执行并返回结果集,结果集获取字段值
rs = ps.executeQuery();
// 元数据获取数据的数据(字段值的修饰数据)
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
// 因为返回多个个对象,所以都判断
ArrayList<T> list = new ArrayList<T>();
while (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object coulumnValue = rs.getObject(i + 1);
String columnLable = metaData.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLable);
field.setAccessible(true);
field.set(t, coulumnValue);
}list.add(t);
}
return list;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 资源的关闭
CRUD.closeRerouseAll(ps, null, rs);
}return null;
}//用于查询特殊值,只是返回一个值得函数(分组函数)
public <E> E getOne(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 e) {
// TODO Auto-generated catch block
e.printStackTrace();
}CRUD.closeRerouseAll(ps, null, rs);
return null;
}
}
具体的针对操作Customer表的接口
//此接口用于规范针对customers表的通用操作,现在考虑事务了,所以方法都要带Connection conn,带连接
public interface CustomerDAO {
/**
*
* @Description 将cust对象添加到数据库中
* @author lxystart
* @date 2021年3月28日下午3:08:52
* @param conn
* @param cust
*/
void insert(Connection conn,Customer cust);
/**
*
* @Description 针对表中的ID,删除数据库中的一条数据
* @author lxystart
* @date 2021年3月28日下午3:11:28
* @param conn
* @param id
*/
void deleteByID(Connection conn,int folwid);
/**
*
* @Description 针对内存中的customer对象,修改表中的数据
* @author lxystart
* @date 2021年3月28日下午3:13:02
* @param conn
* @param cust
*/
void update(Connection conn,Customer cust);
/**
*
* @Description 针对指定的ID,查询得到相应的Customer对象
* @author lxystart
* @date 2021年3月28日下午3:14:49
* @param conn
* @param cust
* @return
*/
Customer getCustomerByID(Connection conn,int flowid);
/**
*
* @Description 返回表中所有的记录
* @author lxystart
* @date 2021年3月28日下午3:17:49
* @param conn
* @return
*/
List<Customer> getAllCustomers(Connection conn);
/**
*
* @Description 返回表中所有的行数
* @author lxystart
* @date 2021年3月28日下午3:20:02
* @param conn
* @return
*/
Long getCounts(Connection conn);
/**
*
* @Description 返回表中日期最大的
* @author lxystart
* @date 2021年3月28日下午3:22:39
* @param conn
* @return
*/
Date getMaxDate(Connection conn);
}
具体的实现类
//具体的针对examStudent表的操作的类
public class CustomerDAOImpl extends BaseDAO implements CustomerDAO {
@Override//考虑事务,连接就先不要关闭
public void insert(Connection conn, Customer cust) {
String sql = "insert into examstudent(type,idcard,examcard) values(?,?,?)";
update(conn, sql, cust.getType(), cust.getIdCard(), cust.getExamCard());
}
@Override
public void deleteByID(Connection conn, int flowid) {
String sql = "delete from examstudent where flowid = ?";
update(conn, sql, flowid);
}
@Override
public void update(Connection conn, Customer cust) {
String sql = "update examstudent set type =?,idcard = ?,examcard =? where flowid = ?";
update(conn, sql, cust.getType(),cust.getIdCard(),cust.getExamCard(),cust.getFlowId());
}
@Override
public Customer getCustomerByID(Connection conn, int flowid) {
String sql ="select flowid flowId,type,idcard idCard,examcard examCard,location from examstudent where flowid = ?";
Customer instance = getInstance(conn, Customer.class, sql, flowid);
return instance;
}
@Override
public List<Customer> getAllCustomers(Connection conn) {
String sql ="select flowid flowId,type,idcard idCard,examcard examCard,location from examstudent";
List<Customer> instances = getInstances(conn, Customer.class, sql);
return instances;
}
@Override
public Long getCounts(Connection conn) {
String sql = "select count(*) from examstudent";
return getOne(conn, sql);
}
@Override
public Date getMaxDate(Connection conn) {
String sql = "select max(birthday) from examstudent";
return getOne(conn, sql);
}
}
自己测试
public class CustomerDAOImplTest {
CustomerDAOImpl cai = new CustomerDAOImpl();
@Test//注意insert into 语句写错了
public void test1() {
Connection conn = null;
try {
conn = CRUD.getConnection();
cai.insert(conn, new Customer(123, 1, "123", "456", "123", "1", 1));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
CRUD.closeRerouse(null, conn);
}
}
@Test//修改没有id这个属性
public void test2() {
Connection conn = null;
try {
conn = CRUD.getConnection();
cai.deleteByID(conn,5);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
CRUD.closeRerouse(null, conn);
}
}@Test//注意update的语法 update 表名 set 字段名 = ?where。。。
public void test3() {
Connection conn = null;
try {
conn = CRUD.getConnection();
cai.update(conn, new Customer(1, 1, "123", "456", "123", "1", 1));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
CRUD.closeRerouse(null, conn);
}
}@Test//要注意既然要返回类型,那么别名就要起号
public void test4() {
Connection conn = null;
try {
conn = CRUD.getConnection();
Customer customerByID = cai.getCustomerByID(conn, 1);
System.out.println(customerByID);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
CRUD.closeRerouse(null, conn);
}
}@Test//注意点就是:既然有返回结果集,那么一定要起别名
public void test5() {
Connection conn = null;
try {
conn = CRUD.getConnection();
List<Customer> list = cai.getAllCustomers(conn);
list.forEach(System.out::println);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
CRUD.closeRerouse(null, conn);
}
}
@Test
public void test6() {
Connection conn = null;
try {
conn = CRUD.getConnection();
Long counts = cai.getCounts(conn);
System.out.println(counts);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
CRUD.closeRerouse(null, conn);
}
}
@Test
public void test7() {
Connection conn = null;
try {
conn = CRUD.getConnection();
Date maxDate = cai.getMaxDate(conn);
System.out.println(maxDate);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
CRUD.closeRerouse(null, conn);
}
}
}