学习博客：BaseDAO的使用

先做一个BaseDAO做一个基本实现：

package cn.zsp.dao;

import cn.zsp.uil.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public abstract class BaseDAO {

    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.closeResource(null,ps,rs);
        }

        return null;
    }

    /***********************考虑事务的查询操作************************/
    //考虑事务的查询操作 version 2.0 返回1个对象
    public <T> T getInstance(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();
            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);
                    //反射存数据
                    //要数据的应该是一个类，我们只是取到这个类，然后给使用这个类变量的对象赋值
                    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;
    }

    /***********************考虑事务的查询操作************************/
    //考虑事务的查询操作 version 2.0 返回多个对象
    public <T> List<T> getForList(Connection conn, Class<T> clazz,
                                  String sql,
                                  Object ...args)   {

        PreparedStatement ps =null;
        ResultSet rs=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();
            ArrayList<T> arrayList = 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);
                    //反射存数据
                    //要数据的应该是一个类，我们只是取到这个类，然后给使用这个类变量的对象赋值
                    Field field =clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);

                }
                arrayList.add(t);
            }
            return arrayList;

        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            JDBCUtils.closeResource(null,ps,rs);
        }
        //获得预编译的SQL语句的对象
        return  null;
    }






    /***********************考虑事务的增删改操作************************/
    //考虑事务的增删改操作 version 2.0
    public int update(Connection conn,String sql,Object...args)     {
        //这里用Object是因为我不知道到底是什么数据类型会传过来

        PreparedStatement ps=null;
        try {
            conn= JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i <args.length ; i++) {
                ps.setObject(i+1,args[i]);
            }

            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        JDBCUtils.closeResource(null,ps);
        return 0;
    }
}

然后做一个实现类的DAO接口：

package cn.zsp.dao;

import cn.zsp.bean.Students;

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

//此接口用于规范针对于students表的常用操作
public interface StudentsDao {
    /**
     * 添加学生
     * @param conn
     * @param students
     */
    void insert(Connection conn, Students students);
    /**
     * 根据id删除学生
     * @param conn
     * @param uid
     */
    void deleteById(Connection conn, int uid);
    /**
     * 根据传过来的类对表进行更新
     * @param conn
     * @param students
     */
    void update(Connection conn, Students students);

    /**
     * 根据id查询学生
     * @param conn
     * @param id
     * @return
     */
    Students getStudentsById(Connection conn, int id);

    /**
     * 传个连接得到表里面的数据
     * @return
     */
    List<Students> getAll(Connection conn);
    Long getCount(Connection conn);

}

最后实现接口继承DAO接口：

package cn.zsp.dao;

import cn.zsp.bean.Students;

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

public class StudentsDAOImpl extends BaseDAO implements  StudentsDao {
    @Override
    public void insert(Connection conn, Students students) {
    String sql ="insert into students(uid,uname,uhobby)values(?,?,?)";
    update(conn,sql,students.getUid(),students.getUname(),students.getUhobby());
    }

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

    @Override
    public void update(Connection conn, Students students) {
        String sql ="update students set uname=?,uhobby=? where " +
                "uid=?";
        update(conn,sql,students.getUname(),students.getUhobby(),
                students.getUid());
    }

    @Override
    public Students getStudentsById(Connection conn, int uid) {
        String sql ="select uid,uname,uhobby from students where uid =?";
        Students student = getInstance(conn, Students.class, sql, uid);
        return student;

    }

    @Override
    public List<Students> getAll(Connection conn) {
        String sql="select * from students";
        List<Students> list = getForList(conn, Students.class, sql);
        return list;
    }

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

}

底下是测试案例：

package cn.zsp.dao.junit;

import cn.zsp.bean.Students;
import cn.zsp.dao.StudentsDAOImpl;
import cn.zsp.uil.JDBCUtils;
import org.junit.Test;

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

import static org.junit.Assert.*;

public class StudentsDAOImplTest {
    private StudentsDAOImpl dao =new StudentsDAOImpl();
    @Test
    public void insert()   {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        dao.insert(conn,new Students(5,"连宜辉","睡觉"));
        JDBCUtils.closeResource(conn,null);
    }

    @Test
    public void deleteById()  {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        dao.deleteById(conn,5);
        JDBCUtils.closeResource(conn,null);
    }

    @Test
    public void update() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        dao.update(conn,new Students(1,"朱少鹏","java"));
        JDBCUtils.closeResource(conn,null);
    }

    @Test
    public void getStudentsById() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Students student = dao.getStudentsById(conn, 1);
        System.out.println(student);
        JDBCUtils.closeResource(conn,null);
    }

    @Test
    public void getAll() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Students> all = dao.getAll(conn);
        for (Students students : all) {
            System.out.println(students);
        }
        JDBCUtils.closeResource(conn,null);
    }

    @Test
    public void getCount() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Long count = dao.getCount(conn);
        System.out.println(count);
        JDBCUtils.closeResource(conn,null);
    }
}