I:提供一个操作数据表的基本的DAO类(Version 1.0)
public abstract class BaseDAO {
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]);
}
int i = ps.executeUpdate();
return i;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null, ps);
}
return 0;
}
public <T> T Query(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()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columnValue = rs.getObject(i + 1);
String ColumnLabel = rsmd.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(ColumnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null, ps, rs);
}
return null;
}
public <T> List<T> QueryAll(Connection conn, Class<T> clazz, String sql, Object... args) {
PreparedStatement ps = null;
ResultSet rs = null;
List<T> list;
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();
list = new ArrayList<T>();
while (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columnValue = rs.getObject(i + 1);
String ColumnLabel = rsmd.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(ColumnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null, ps, rs);
}
return null;
}
@SuppressWarnings("unchecked")
public <E> E getValue(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]);
}
ResultSet rs = ps.executeQuery();
if (rs.next()) {
Object object = rs.getObject(1);
return (E) object;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null, ps);
}
return null;
}
}
II:创建具体的操作指定数据表的接口
- 将 Customer 对象添加到数据库中
- 根据指定的 ID 删除数据表中的一条记录
- 根据内存中的 Customer 对象,修改数据表中指定的记录
- 根据指定 ID 查询得到对应的 Customer 对象
- 查询数据表中所有记录构成的集合
- 返回数据表中记录的条目数
- 返回数据表中最大的生日
public interface CustomerDAO {
void insert(Connection conn, Customer cust);
void deleteById(Connection conn, int id);
void update(Connection conn, Customer cust);
Customer getCustomerById(Connection conn, int id);
List<Customer> getAll(Connection conn);
Long getCount(Connection conn);
Date getMaxBirth(Connection conn);
}
III:创建具体的操作指定数据表的类
- 去继承 DAO 类,获取操作数据库通用方法的结构
- 去实现具体的操作指定数据表的接口,在内部编写相应的逻辑方法
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 = Query(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 = QueryAll(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);
}
}
IV:对实现类中所实现的方法进行单元测试
- 实例化此实现类的对象,声明为 private 表时仅供单元测试类所使用
public class CustomerDAOImplTest {
private CustomerDAOImpl dao = new CustomerDAOImpl();
@Test
public void testInsert() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = sdf.parse("2020-3-8");
Customer cust = new Customer("尚硅谷", "atguigu@gmail.com", new java.sql.Date(date.getTime()));
dao.insert(conn, cust);
System.out.println("添加成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testDeleteById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
dao.deleteById(conn, 25);
System.out.println("删除成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testUpdate() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Customer cust = new Customer(21, "康师傅", "shkstart@gmail.com", new java.sql.Date(3235879865235L));
dao.update(conn, cust);
System.out.println("更新成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testGetCustomerById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Customer cust = dao.getCustomerById(conn, 21);
System.out.println(cust);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testGetAll() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
List<Customer> list = dao.getAll(conn);
Iterator<Customer> iterator = list.iterator();
while (iterator.hasNext()) {
Customer customer = iterator.next();
System.out.println(customer);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testGetCount() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Long count = dao.getCount(conn);
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testGetMaxBirth() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Date maxBirth = dao.getMaxBirth(conn);
System.out.println(maxBirth);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
}