-
DAO:Data Access Object访问数据信息的类和接口,包括了对数据的CRUD(Create、Retrival、Update、Delete),而不包含任何业务相关的信息。有时也称作:BaseDAO
-
作用:为了实现功能的模块化,更有利于代码的维护和升级。
-
层次结构
BeanDAO
public abstract class BeanDAO {
// 通用增删改操做-------version2.0(考虑事务)
public int updata(Connection conn, String sql, Object... args) {// sql语句中占位符个数与可变形参个数必须相同
PreparedStatement ps = null;
try {
// 1.加载并注册驱动获取数据库的连接
// conn = JdbcUtils.getConnection();
// 2.预编译SQL语句返回PrepareStatement的实例
ps = conn.prepareStatement(sql);
// 3.填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
// 4.执行
return ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 5.关闭资源
JdbcUtils.closeResource(null, ps);
}
return 0;
}
// 通用的查询一条记录的操做---(考虑了事务的)
public <T> T getInstance(Connection conn, Class<T> clazz, String sql, Object... args) {
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 获取连接
conn = JdbcUtils.getConnection();
// 获取PreparedStatement
ps = conn.prepareStatement(sql);
// 填充占位符
for (int i = 0; i < args.length; i++) {// 填充占位符从1开始
ps.setObject(i + 1, args[i]);
}
// 执行SQL语句
rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
// 类似于迭代器指针
if (rs.next()) {
// Order order = new Order();
T t = clazz.newInstance();
// 遍历一条记录里的各个字段
for (int i = 0; i < columnCount; i++) {
// 遍历每一个字段的名字为反射提供属性名和
// String columnName = metaData.getColumnName(i+1);
String columnLabel = metaData.getColumnLabel(i + 1);
Object columnValue = rs.getObject(i + 1);
// 为order指定的columnName赋值columnValue,通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 关闭资源
JdbcUtils.closeResource(null, ps, rs);
}
return null;
}
// 通用的查询多条记录集合的操做---(考虑事务的)
public <T> List<T> getListInstance(Connection conn, Class<T> clazz, String sql, Object... args) {
PreparedStatement ps = null;
ResultSet rs = null;
ArrayList<T> alist = null;
try {
conn = JdbcUtils.getConnection();
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();
alist = new ArrayList<T>();
while (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
// 获取列别名
String columnLabel = rsmd.getColumnLabel(i + 1);
// 获取列值
Object columnValue = rs.getObject(i + 1);
// 为列名为columnLabel的赋值,通过反射
Field field = clazz.getDeclaredField(columnLabel);
// 防止field为私有权限
field.setAccessible(true);
field.set(t, columnValue);
}
alist.add(t);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JdbcUtils.closeResource(null, ps, rs);
}
return alist;
}
@Test
public void test() throws Exception {
Connection conn = JdbcUtils.getConnection();
String sql = "select count(*) from customers";
Object count = getValue(conn, sql);
System.out.println(count);
}
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()) {
Object object = rs.getObject(1);
System.out.println(object.getClass());
return (E) rs.getObject(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JdbcUtils.closeResource(null, ps, rs);
}
return null;
}
}
CustomersDao
//此接口用于规范针对于Customer表的操做
public interface CustomersDao {
/**
*
* @Description:将cust对象添加到数据库中
* @author:about1134
* @param conn
* @param cust
*/
void insert(Connection conn,Customer cust);
/**
*
* @Description:针对指定的id,删除表中的一条记录
* @author:about1134
* @param conn
* @param id
*/
void deleteById(Connection conn,int id);
/**
*
* @Description:针对内存中的cust对象,去修改数据表中的指定记录
* @author:about1134
* @param conn
* @param cust
*/
void update(Connection conn,Customer cust);
/**
*
* @Description:针对指定的id查询得到对应得到Customer对象
* @author:about1134
* @param conn
* @param id
* @return
*/
Customer getCustomerById(Connection conn,int id);
/**
*
* @Description:查询表中的所用记录构成的集合
* @author:about1134
* @param conn
* @return
*/
List<Customer> getAll(Connection conn);
/**
*
* @Description:返回数据表中的数据的条目数
* @author:about1134
* @param conn
* @return
*/
Long getCount(Connection conn);
/**
*
* @Description:返回数据表中最大得到生日
* @author:about1134
* @param conn
* @return
*/
Date getMaxBirth(Connection conn);
}
CustomerDaoImpl实现类
public class CustomerDaoImpl extends BeanDAO implements CustomersDao {
@Override
public void insert(Connection conn, Customer cust) {
String sql = "insert into Customers(name,email,birth)values(?,?,?)";
updata(conn, sql, cust.getName(),cust.getEmail(),cust.getBirth());
}
@Override
public void deleteById(Connection conn, int id) {
String sql = "delete from customers where id = ?";
updata(conn, sql , id);
}
@Override
public void update(Connection conn, Customer cust) {
String sql = "update customers set name = ?,email = ?,birth = ? where id = ?";
// TODO Auto-generated method stub
updata(conn, sql , cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
}
@Override
public Customer getCustomerById(Connection conn, int id) {
String sql = "select id,name,birth,email from customers where id = ?";
Customer instance = getInstance(conn, Customer.class, sql , id);
return instance;
}
@Override
public List<Customer> getAll(Connection conn) {
String sql = "select id,name,email,birth from customers";
List<Customer> list = getListInstance(conn, Customer.class, sql);
return list;
}
@Override
public Long getCount(Connection conn) {
String sql = "select count(*) from customers";
Long count = getValue(conn, sql);
return count;
}
@Override
public Date getMaxBirth(Connection conn) {
String sql = "select max(birth) from customers";
return getValue(conn, sql);
}
}
测试类
public class Customers {
private CustomerDaoImpl dao = new CustomerDaoImpl();
@Test
public void testInsert() {
Connection conn = null;
try {
conn = JdbcUtils.getConnection();
Customer cust = new Customer(1,"新垣结衣","xin@qq.com",new Date(12312312L));
dao.insert(conn, cust);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResource(conn, null);
}
}
@Test
public void testDeleteById() {
Connection conn = null;
try {
conn = JDBCUtil.getConnection1();
dao.deleteById(conn, 33);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResource(conn, null);
}
}
@Test
public void testUpdate() {
Connection conn = null;
try {
conn = JdbcUtils.getConnection();
Customer cust = new Customer(32,"有村架纯","you@qq.com",new Date(21312312312L));
dao.update(conn, cust);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResource(conn, null);
}
}
@Test
public void testGetCustomerById() {
Connection conn = null;
try {
conn = JDBCUtil.getConnection3();
Customer cuss = dao.getCustomerById(conn, 34);
System.out.println(cuss);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResource(conn, null);
}
}
@Test
public void testGetAll() {
Connection conn = null;
try {
conn = JdbcUtils.getConnection();
List<Customer> all = dao.getAll(conn);
Iterator<Customer> iterator = all.iterator();
while(iterator.hasNext()) {
System.out.println(iterator.next());
}
} catch (Exception e) {
// TODO Auto-generated catch block
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) {
// TODO Auto-generated catch block
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) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResource(conn, null);
}
}
}