DAO及其实现类升级后的操作

升级后的封装了针对数据表的通用操作（CURD）

package DAOshengji;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import util.JDBCUtils;

/*
 * DAO: data(base)  access  object
 * 封装了针对数据表的通用操作（CURD）
 */
public abstract class BaseDAO<T> {
    private Class<T> clazz=null;
//  public BaseDAO() {
//      
//  }
    
    {
        //获取当前BaseDAO的子类继承父类中的泛型
         Type genericSuperclass = this.getClass().getGenericSuperclass();
         ParameterizedType paramType = (ParameterizedType) genericSuperclass;
         Type[] typeArguments = paramType.getActualTypeArguments();//获取了父类的泛型参数
         clazz=(Class<T>) typeArguments[0];//泛型的第一个参数
    }
    //通用的增删改操作---version 2.0版本（考虑事务）
    public  int update(Connection conn,String sql,Object ...args) {//sql中占位符的个数与可变形参的长度相同
        PreparedStatement ps=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.执行sql
            return ps.executeUpdate();
        }catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            //4.资源的关闭
            JDBCUtils.closeResource(null, ps);
        }
        return 0;
    }
    
    //通用的查询操作,返回数据表中一条记录(version 2.0 考虑上事务)
    public  T getInstance(Connection conn,String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // 预编译sql语句
            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();
            // 通过ResultSetMetaData获取结果集中的列数
            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);

                    // 给t对象指定的某个属性赋值为value:通过反射
                    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;
    }

    
     //针对不同表的通用的查询操作，返回多条数据(version 2.0 考虑事务)
        public  List<T> getForList(Connection conn, String sql, Object...args){
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                // 预编译sql语句
                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();
                // 通过ResultSetMetaData获取结果集中的列数
                int columnCount = rsmd.getColumnCount();
                //创建集合对象
                ArrayList<T> list = new ArrayList<T>();
                while (rs.next()) {
                    T t = clazz.newInstance();
                    
                    // 处理结果集一行数据的每一列:给t对象指定的属性赋值
                    for (int i = 0; i < columnCount; i++) {
                        // 获取列值
                        Object columnvalue = rs.getObject(i + 1);
                        // 获取每个列的列名
                        String columnName = rsmd.getColumnLabel(i + 1);

                        // 给t对象指定的某个属性赋值为value:通过反射
                        Field field = clazz.getDeclaredField(columnName);
                        field.setAccessible(true);
                        field.set(t, columnvalue);

                    }
                    list.add(t);
                }
                return list;
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                JDBCUtils.closeResource(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 (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }finally {
                    JDBCUtils.closeResource(null, ps, rs);                  
                }
                return null;
                
        }
        
    
}

次接口用于规范针对于表的常用操作

package DAOshengji;

import java.sql.Connection;
import java.sql.Date;
import java.util.List;

import bean.Thesis;



/*+
 * 次接口用于规范针对于thesis表的常用操作
 */
public interface thesisDAO {
    //将the对象添加到数据库中
    public abstract void insert(Connection conn,Thesis the);
    
    //针对指定的id，删除表中的一条记录
    public abstract void deleteById(Connection conn,int id);
    
    //针对内存中的the对象，去修改数据表中的指定记录
    public abstract void update(Connection conn,Thesis the);

    //针对指定的id查询得到对应的Thesis对象
    public abstract Thesis getThesisById(Connection conn,int id);
    
    //查询表中的所有记录构成的集合
    public abstract List<Thesis> getALL(Connection conn);
    
    //返回数据表中的数据的条目数
    public abstract Long getCount(Connection conn);
    
    //返回数据表中最大的生日
    public abstract Date getMaxBirth(Connection conn);
}

升级后的方法重写

package DAOshengji;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.Date;
import java.util.List;

import bean.Thesis;

public class ThesisDAOImpl extends BaseDAO<Thesis> implements thesisDAO{

    @Override
    public void insert(Connection conn, Thesis the) {
        String sql = "insert into thesis(t_name,t_author,t_create_time,t_magazine,t_number)values(?,?,?,?,?)";//？表示占位符
        update(conn, sql,the.getT_name(),the.getT_author(),the.getT_create_time(),the.getT_magazine(),the.getT_number());
    }

    @Override
    public void deleteById(Connection conn, int id) {
        String sql = "delete from thesis where id = ? ";
        update(conn, sql, id);
    }

    @Override
    public void update(Connection conn, Thesis the) {
        String sql = "update thesis set t_name = ?,t_author=?,t_create_time = ?,t_magazine = ?,t_number = ? where id = ?";
        update(conn, sql,the.getT_name(),the.getT_author(),the.getT_create_time(),the.getT_magazine(),the.getT_number(),the.getId());
    }

    @Override
    public Thesis getThesisById(Connection conn, int id) {
        String sql = "select id,t_name,t_author,t_magazine,t_number from thesis where id = ?";
        Thesis thesis = getInstance(conn,sql, id);
        return thesis;
    }

    @Override
    public List<Thesis> getALL(Connection conn) {
        String sql = "select id,t_name,t_author,t_magazine,t_number from thesis";
        List<Thesis> list = getForList(conn, sql);
        return list;
    }

    @Override
    public Long getCount(Connection conn) {
        String sql = "select count(*) from Thesis";
        return getValue(conn, sql);
        
    }

    @Override
    public Date getMaxBirth(Connection conn) {
        String sql ="select max(t_create_time) from thesis";
        return getValue(conn, sql);
    }

}

测试方法

package DAOshengji.juint;

import java.sql.Connection;
import java.sql.Date;
import java.util.List;

import org.junit.Test;

import DAOshengji.ThesisDAOImpl;
import bean.Thesis;
import util.JDBCUtils;

public class ThesisDAOImplTest {
    private ThesisDAOImpl dao = new ThesisDAOImpl();
    @Test
    public void testInsert() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Thesis the = new Thesis(0,"杨过", "666", new Date(145454155L), "神雕侠侣", 01);
            dao.insert(conn, the);
            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, 42);
            System.out.println("删除成功");
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn, null);            
        }
    }

    @Test
    public void testUpdateConnectionThesis() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Thesis the = new Thesis(43,"小龙女","666",new Date(1548484848484L),"神雕侠侣",2);
            dao.update(conn, the);
            System.out.println("修改成功");
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn, null);            
        }
    }

    @Test
    public void testGetThesisById() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Thesis the = dao.getThesisById(conn, 2);
            System.out.println(the);
            System.out.println("查询成功");
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn, null);            
        }
    }

    @Test
    public void testGetALL() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            List<Thesis> all = dao.getALL(conn);
            all.forEach(System.out::println);
            System.out.println("查询成功");
        } 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);            
        }
    }

}