4天贯通JDBC技术七、实现DAO及其实现类CustomerDAO的代码

【DAO.java】

//DAO:database access object

 

class ReflectionUtils{

//获取clazz对象对应的运行时类的父类的泛型

public static Class getSuperGeneric(Class clazz){

Type type = clazz.getGenericSuperclass();

ParameterizedType p = (ParameterizedType)type;

Type[] ts = p.getActualTypeArguments();

return (Class)ts[0];

}

}

 

public class DAO<T> {

private Class<T> clazz = null;

//this.getClass()在这个问题中,就是CustomerDAO

public DAO(){

clazz = ReflectionUtils.getSuperGeneric(this.getClass());

}

//获取数据库的标准的特定含义的值

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(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(rs, ps, null);

}

return null;

}

//返回多个对象,以集合的形式返回

public List<T> getForList(Connection conn,String sql,Object ...args){

PreparedStatement ps = null;

ResultSet rs = null;

List<T> list = new ArrayList<>();

try{

//1.预编译sql语句,获取PreparedStatement对象

ps = conn.prepareStatement(sql);

//2.填充占位符

for(int i = 0;i < args.length;i++){

ps.setObject(i + 1, args[i]);

}

//3.返回一个结果集

rs = ps.executeQuery();

ResultSetMetaData rsmd = rs.getMetaData();

int columnCount = rsmd.getColumnCount();

while(rs.next()){

T t = clazz.newInstance();

//给t对象的相应属性赋值

for(int i = 0;i < columnCount;i++){

Object columnVal = rs.getObject(i + 1);

String columnLabel = rsmd.getColumnLabel(i + 1);

PropertyUtils.setProperty(t, columnLabel, columnVal);

}

list.add(t);

}

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(rs, ps, null);

}

//System.out.println(clazz);

return list;

}

//返回一个对象

public T get(Connection conn,String sql,Object ...args){

PreparedStatement ps = null;

ResultSet rs = null;

try{

//1.预编译sql语句,获取PreparedStatement对象

ps = conn.prepareStatement(sql);

//2.填充占位符

for(int i = 0;i < args.length;i++){

ps.setObject(i + 1, args[i]);

}

//3.返回一个结果集

rs = ps.executeQuery();

ResultSetMetaData rsmd = rs.getMetaData();

int columnCount = rsmd.getColumnCount();

if(rs.next()){

T t = clazz.newInstance();

//给t对象的相应属性赋值

for(int i = 0;i < columnCount;i++){

Object columnVal = rs.getObject(i + 1);

String columnLabel = rsmd.getColumnLabel(i + 1);

PropertyUtils.setProperty(t, columnLabel, columnVal);

}

return t;

}

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(rs, ps, null);

}

//System.out.println(clazz);

return null;

}

//通用的增删改的操作

public void 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]);

}

ps.executeUpdate();

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, ps, null);

}

}

}

【CustomerDAO.java】

//CustomerDAO类是用来操作Customer类的

public class CustomerDAO extends DAO<Customer>{

// @Test

// public void testGeneric(){

// Class clazz = CustomerDAO.class;

// Type type = clazz.getGenericSuperclass();

// ParameterizedType p = (ParameterizedType)type;

// Type[] ts = p.getActualTypeArguments();

// System.out.println(ts[0]);

// }

/**

 * 获取对应的表中的记录的个数

 */

public long getCount(Connection conn){

String sql = "select count(*) from customers";

return (long)getValue(conn, sql);

}

/**

 * 返回customers表中的所有数据

 * @param conn

 * @return

 */

public List<Customer> getAll(Connection conn){

String sql = "select id,name,email,birth from customers";

return getForList(conn, sql);

}

/**

 * 根据指定的id返回相应的对象

 * @param conn

 * @param customerId

 */

public Customer getInstance(Connection conn,int customerId){

String sql = "select id,name,email,birth from customers where id = ?";

return get(conn, sql, customerId);

}

/**

 * 删除指定customerId的数据表中的记录

 * @param conn

 * @param customerId

 */

public void delete(Connection conn,int customerId){

String sql = "delete from customers where id = ?";

update(conn, sql, customerId);

}

/**

 * 向数据表中修改指定id的信息为Customer对象的信息

 * @param conn

 * @param cust

 */

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());

}

/**

 * 向数据表中插入一条数据

 * @param conn  数据库的连接

 * @param cust 要插入的Customer对象

 */

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());

}

}

【TestCustomerDAO.java】

public class TestCustomerDAO {

CustomerDAO customerDAO = new CustomerDAO();

@Test

public void testGetCount(){

Connection conn = null;

try{

conn = JDBCUtils.getConnection();

long count = customerDAO.getCount(conn);

System.out.println(count);

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, null, conn);

}

}

@Test

public void testGetAll(){

Connection conn = null;

try{

conn = JDBCUtils.getConnection();

List<Customer> list = customerDAO.getAll(conn);

//System.out.println(list);

Iterator<Customer> iterator = list.iterator();

while(iterator.hasNext()){

System.out.println(iterator.next());

}

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, null, conn);

}

}

@Test

public void testQuery(){

Connection conn = null;

try{

conn = JDBCUtils.getConnection();

Customer cust = customerDAO.getInstance(conn, 13);

System.out.println(cust);

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, null, conn);

}

}

@Test

public void testDelete(){

Connection conn = null;

try{

conn = JDBCUtils.getConnection();

customerDAO.delete(conn, 10);

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, null, conn);

}

}

@Test

public void testUpdate(){

Connection conn = null;

try{

conn = JDBCUtils.getConnection();

Customer cust = new Customer(10, "张卫健", "zwj@gmail.com",new Date(new java.util.Date().getTime()));

customerDAO.update(conn, cust);

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, null, conn);

}

}

@Test

public void testInsert(){

Connection conn = null;

try{

conn = JDBCUtils.getConnection();

Customer cust = new Customer(10, "张卫健", "zwj@gmail.com",new Date(new java.util.Date().getTime()));

customerDAO.insert(conn, cust);

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, null, conn);

}

}

}