第三次Java实训

一、创建数据访问接口

在bao中建立四个表的数据访问接口

1.学校数据访问接口
创建学校数据访问接口CollegeDao

2.状态数据访问接口
创建 状态数据访问接口StatusDao

3.学生数据访问接口
创建学生数据访问接口StudentDao

4.用户数据访问接口
创建用户数据访问接口UserDao

二、创建数据访问接口实现类

在dao包中创建子包impl

1.创建学校数据访问接口实现类
创建学校数据访问接口实现类CollegeDaoImpl
代码展示

package net.lbj.student.dao.impl;

import net.lbj.student.bean.College;
import net.lbj.student.dao.CollegeDao;
import net.lbj.student.dbutil.ConnectionManager;

import java.sql.*;

/**
 * 学校数据访问接口实现类
 * 2020/07/11
 */
public class CollegeDaoImpl implements CollegeDao {
    /**
     * 按id查找学校
     *
     * @param id
     * @return 学校对象
     */
    @Override
    public College findById(int id) {
        //声明学校对象
        College college = null;

        //1.获取数据库连接
        Connection conn = ConnectionManager.getConnection();
        //2.定义SQL字符串
        String strSQL = "select * from t_college where id = ?";
        try {
            //3.创建预备语句对象
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            //4.设置占位符的值
            pstmt.setInt(1,id);
            //5.执行SQL查询，返回结果集
            ResultSet rs = pstmt.executeQuery();
            //6.判断结果集是否有对象
            //指针移到下一步用next
            if (rs.next()){
                //7.创建学校实体对象
                college = new College();
                //8.利用当前纪录各个字段值去设置学校对象的属性
                college.setId(rs.getInt("id"));
                college.setName(rs.getString("name"));
                college.setPresident(rs.getString("president"));
                college.setStartTime(rs.getTimestamp("start_time"));
                college.setTelephone(rs.getString("telephone"));
                college.setEmail(rs.getString("email"));
                college.setAddress(rs.getString("address"));
                college.setProfile(rs.getString("profile"));
            }
            //9.关闭结果集
            rs.close();
            //10.关闭预备语句对象
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }

        //返回学校对象
        return college;
    }

    /**
     * 更新学校信息
     *
     * @param college
     * @return 更新记录数
     */
    @Override
    public int update(College college) {
        //声明更新记录数
        int count = 0;

        //1.获取数据库连接
        Connection conn = ConnectionManager.getConnection();
        //2.定义SQL字符串，明确操作意图
        String strSQL = "update t_college set name = ?, president = ?, start_time = ?,"
                + " telephone = ?, email = ?, address = ?, profile = ? where id = ?";
        try {
            //3.创建预备语句对象
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            //4.设置占位符的值
            pstmt.setString(1,college.getName());
            pstmt.setString(2,college.getPresident());
            pstmt.setTimestamp(3,new Timestamp(college.getStartTime().getTime()));
            pstmt.setString(4,college.getTelephone());
            pstmt.setString(5,college.getEmail());
            pstmt.setString(6,college.getAddress());
            pstmt.setString(7,college.getProfile());
            pstmt.setInt(8,college.getId());
            //5.执行SQL更新，返回更新记录数
            count = pstmt.executeUpdate();
            //6.关闭预备语句对象
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7.关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }
        //返回更新记录数
        return count;
    }
}

单元测试：对CollegeDaoImpl进行单元测试
在test文件里面创建测试类TestCollegeDaoImpl
代码如下：

package net.lbj.student.test;
//标红按alt+回车

import net.lbj.student.bean.College;
import net.lbj.student.dao.CollegeDao;
import net.lbj.student.dao.impl.CollegeDaoImpl;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

/**
 * 测试学校数据访问接口实现类
 * 2020/07/11
 */
public class TestCollegeDaoImpl {
    //创建学校数据访问接口对象
    CollegeDao dao = new CollegeDaoImpl();

    @Before
    public void beforeTest() {
        System.out.println("呵呵，单元测试开始咯~");
    }

    @After
    public void afterTest() {
        System.out.println("呵呵，单元测试结束咯~");
    }

    @Test
    public void testFindById() {
        //调用学校数据访问对象的查询方法，获取学校对象
        College college = dao.findById(1);
        //判断是否查询成功
        if (college != null) {
            //输出学校信息
            System.out.println("校名： " + college.getName());
            System.out.println("校长： " + college.getPresident());
            System.out.println("地址： " + college.getAddress());
            System.out.println("邮箱： " + college.getEmail());
            System.out.println("电话： " + college.getTelephone());
        } else {
            System.out.println("没有查询到学校记录！");
        }
    }

    @Test
    public void testUpdate() {
        //调用学校数据访问对象的查询方法，获取学校对象
        College college = dao.findById(1);
        //输出原校长
        System.out.println("原校长： " + college.getPresident());
        //修改学校信息，改校长
        college.setPresident("可爱多");
        //调用学校数据访问对象的更新方法
        int count = dao.update(college);
        //判断是否更新成功
        if (count > 0) {
            System.out.println("恭喜，学校记录更新成功！");
            System.out.println("新校长：" + dao.findById(1).getPresident());
        } else {
            System.out.println("遗憾，学校记录更新失败！");
        }
    }
}

在此代码中包含两个单元测试方法testFindById()，testUpdate()，添加测试注解符@test，如需使用@test注解方法，需要使用JUnit4，讲@test添加进入JUnit4的类路径
2.创建状态数据访问接口实现类
创建状态数据访问接口实现类StatusDaoImpl
代码展示

package net.lbj.student.dao.impl;

import net.lbj.student.bean.Status;
import net.lbj.student.dao.StatusDao;
import net.lbj.student.dbutil.ConnectionManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 状态数据访问接口实现类
 */
public class StatusDaoImpl implements StatusDao {
    @Override
    public Status findById(int id) {
        //声明状态对象
        Status status = null;

        //1.获取数据库连接对象
        Connection conn = ConnectionManager.getConnection();
        //2.定义SQL字符串
        String strSQL = "SELECT * FROM t_status WHERE id = ?";
        try {
            //3.创建预备语句对象
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            //4.设置占位符的值
            pstmt.setInt(1,id);
            //5.执行SQL查询，返回结果集
            ResultSet rs = pstmt.executeQuery();
            //6.判断结果集是否有记录
            if (rs.next()) {
                //7.创建学校实体对象
                status = new Status();
                //8.利用当前纪录各个字段值去设置学校对象的属性
                status.setId(rs.getInt("id"));
                status.setCollege(rs.getString("college"));
                status.setVersion(rs.getString("version"));
                status.setAuthor(rs.getString("author"));
                status.setTelephone((rs.getString("telephone")));
                status.setAddress(rs.getString("address"));
                status.setEmail(rs.getString("email"));
            }
            //9.关闭预备语句对象
            pstmt.close();
            //10.关闭结果集对象
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }

        //返回状态对象
        return status;
    }

    @Override
    public int update(Status status) {
        //定义更新记录数
        int count = 0;

        //1.获得数据库连接
        Connection conn = ConnectionManager.getConnection();
        //2.定义SQL字符串
        String strSQL = "update t_status set college = ?, version = ?, author = ?," +
                "telephone = ?, address = ?, email = ? where id = ?";
        try {
            //3.创建预备语句对象
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            //4.设置占位符的值
            pstmt.setString(1,status.getCollege());
            pstmt.setString(2,status.getVersion());
            pstmt.setString(3,status.getAuthor());
            pstmt.setString(4,status.getTelephone());
            pstmt.setString(5,status.getAddress());
            pstmt.setString(6,status.getEmail());
            pstmt.setInt(7,status.getId());
            //5.执行更新操作，更新记录
            count = pstmt.executeUpdate();
            //6.关闭预备语句对象
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }

        //返回更新记录数
        return count;
    }
}

单元测试：对StatusDaoImpl进行单元测试
代码如下：

package net.lbj.student.test;

import net.lbj.student.bean.Status;
import net.lbj.student.dao.StatusDao;
import net.lbj.student.dao.impl.StatusDaoImpl;
import org.junit.Test;

/**
 * 测试状态数据访问接口实现类
 */
public class TestStatusDaoImpl {
    //声明状态数据访问对象
    StatusDao dao = new StatusDaoImpl();

    @Test
    public void testFindById() {
        //调用状态数据访问对象的查询方法
        Status status = dao.findById(1);
        //输出状态信息
        System.out.println("作者：" + status.getAuthor());
        System.out.println("学校：" + status.getCollege());
        System.out.println("版本：" + status.getVersion());
        System.out.println("地址：" + status.getAddress());
        System.out.println("电话：" + status.getTelephone());
        System.out.println("邮箱：" + status.getEmail());
    }

    @Test
    public void testUpdate() {
        //调用状态数据访问对象的查询方法
        Status status = dao.findById(1);
        //修改状态对象的属性
        status.setAuthor("无心剑");
        status.setTelephone("13845456780");
        status.setEmail("wixinjian@163.com");
        //调用状态数据访问对象的更新方法
        int count = dao.update(status);
        //判断状态更新是否成功
        if (count > 0){
            System.out.println("状态记录更新成功！");
            System.out.println(dao.findById(1));
        } else {
            System.out.println("状态记录更新失败！");
        }
    }
}

在此代码中包含两个单元测试方法testFindById()，testUpdate()。
3.创建学生数据访问接口实现类
创建学生数据访问接口实现类StudentDaoImpl
代码展示

package net.lbj.student.dao.impl;

import net.lbj.student.bean.Student;
import net.lbj.student.dao.StudentDao;
import net.lbj.student.dbutil.ConnectionManager;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

/**
 * 学生数据访问接口实现类
 */

public class StudentDaoImpl implements StudentDao {
    /**
     * 插入学生纪录
     * @param student
     * @return 插入记录数
     */
    @Override
    public int insert(Student student) {
        //定义插入记录数
        int count = 0;

        //1.获取数据库连接
        Connection conn = ConnectionManager.getConnection();
        //2.定义SQL字符串
        String strSQL = "insert into t_student (id, name, sex, age, department, class, telephone)"
                + " values (?, ?, ?, ?, ?, ?, ?)";
        try {
            //3.创建预备语句对象
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            //4.设置占位符的值
            pstmt.setString(1,student.getId());
            pstmt.setString(2,student.getName());
            pstmt.setString(3,student.getSex());
            pstmt.setInt(4,student.getAge());
            pstmt.setString(5,student.getDepartment());
            pstmt.setString(6,student.getClazz());
            pstmt.setString(7,student.getTelephone());
            //5.执行SQL，返回插入记录数
            count = pstmt.executeUpdate();
            //6.关闭预备语句对象
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }

        //返回插入记录数
        return count;
    }

    /**
     * 按学号删除学生记录
     *
     * @param id
     * @return 删除记录数
     */
    @Override
    public int deleteById(String id) {
        //定义删除记录数
        int count = 0;

        //1.获取数据库连接
        Connection conn = ConnectionManager.getConnection();
        //2.定义SQL字符串
        String strSQL = "delete from t_student where id = ?";
        try {
            //3.创建预备语句对象
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            //4.设置占位符的值
            pstmt.setString(1, id);
            //5.执行SQL，返回删除记录数
            count = pstmt.executeUpdate();
            //6.关闭预备语句对象
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }

        //返回删除记录数
        return count;
    }

    /**
     * 按班级删除学生记录
     *
     * @param clazz
     * @return
     */
    @Override
    public int deleteByClass(String clazz) {
        //定义删除记录数
        int count = 0;

        //1.获取数据库连接
        Connection conn = ConnectionManager.getConnection();
        //2.定义SQL字符串
        String strSQL = "delete from t_student where class = ?";
        try {
            //3.创建预备语句对象
            PreparedStatement pstmt = conn.prepareStatement((strSQL));
            //4.设置占位符的值
            pstmt.setString(1,clazz);
            //5.执行SQL，返回删除记录数
            count = pstmt.executeUpdate();
            //6.关闭预备语句对象
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }

        //返回删除记录数
        return count;
    }

    /**
     * 按系部删除学生记录
     *
     * @param department
     * @return 删除记录数
     */
    @Override
    public int deleteByDepartment(String department) {
        //
        int count = 0;

        //1.
        Connection conn = ConnectionManager.getConnection();
        //2.
        String strSQL = "delete from t_student where department = ?";
        try {
            //3.
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            //4.
            pstmt.setString(1,department);
            //5.
            count = pstmt.executeUpdate();
            //6.
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //
            ConnectionManager.closeConnection(conn);
        }

        //
        return count;
    }

    /**
     * 更新学生记录
     *
     * @param student
     * @return
     */
    @Override
    public int update(Student student) {
        //
        int count = 0;

        //1.
        Connection conn = ConnectionManager.getConnection();
        //2.
        String strSQL = "update t_student set name = ?, sex = ?, age = ?,"
                + " department = ?, class = ?, telephone = ? where id = ?";
        try {
            //3.
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            //4.
            pstmt.setString(1,student.getName());
            pstmt.setString(2,student.getSex());
            pstmt.setInt(3,student.getAge());
            pstmt.setString(4,student.getDepartment());
            pstmt.setString(5,student.getClazz());
            pstmt.setString(6,student.getTelephone());
            pstmt.setString(7,student.getId());
            //5.
            count = pstmt.executeUpdate();
            //6.
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //
            ConnectionManager.closeConnection(conn);
        }

        //
        return count;
    }

    /**
     * 按学号查询学生记录
     *
     * @param id
     * @return 学生实体
     */
    @Override
    public Student findById(String id) {
        //
        Student student = null;

        //1.
        Connection conn = ConnectionManager.getConnection();
        //2.
        String strSQL = "select * from t_student where id = ?";
        try {
            //3.
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            //4.
            pstmt.setString(1,id);
            //5.
            ResultSet rs = pstmt.executeQuery();
            //6.
            if (rs.next()) {
                //7.
                student = new Student();
                //8.
                student.setId(rs.getString("id"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setAge(rs.getInt("age"));
                student.setDepartment(rs.getString("department"));
                student.setClazz(rs.getString("class"));
                student.setTelephone(rs.getString("telephone"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //
            ConnectionManager.closeConnection(conn);
        }

        //
        return student;
    }

    /**
     * 按姓名查询学生记录
     *
     * @param name
     * @return 学生列表
     */
    @Override
    public List<Student> findByName(String name) {
        //
        List<Student> students = new ArrayList<Student>();

        //1.
        Connection conn = ConnectionManager.getConnection();
        //2.
        String strSQL = "select * from t_student where name like ?";
        try {
            //3.
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            //4.
            pstmt.setString(1,name + "%");
            //5.
            ResultSet rs = pstmt.executeQuery();
            //6.
            while (rs.next()) {
                //
                Student student = new Student();
                //
                student.setId(rs.getString("id"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setAge(rs.getInt("age"));
                student.setDepartment(rs.getString("department"));
                student.setClazz(rs.getString("class"));
                student.setTelephone(rs.getString("telephone"));
                //
                students.add(student);
            }
            //7.
            rs.close();
            //8.
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //
            ConnectionManager.closeConnection(conn);
        }

        //
        return students;
    }

    /**
     * 按班级查询学生记录
     *
     * @param clazz
     * @return 学生列表
     */
    @Override
    public List<Student> findByClass(String clazz) {
        //
        List<Student> students = new ArrayList<Student>();

        //1.
        Connection conn = ConnectionManager.getConnection();
        //2.
        String strSQL = "select * from t_student where class like ?";
        try {
            //3.
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            //4.
            pstmt.setString(1,clazz + "%");
            //5.
            ResultSet rs = pstmt.executeQuery();
            //6.
            while (rs.next()) {
                Student student = new Student();
                //
                student.setId(rs.getString("id"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setAge(rs.getInt("age"));
                student.setDepartment(rs.getString("department"));
                student.setClazz(rs.getString("class"));
                student.setTelephone(rs.getString("telephone"));
                //
                students.add(student);
            }
            //
            rs.close();
            //
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //
            ConnectionManager.closeConnection(conn);
        }

        //
        return students;
    }

    /**
     * 按系部查询学生记录
     * @param department
     * @return 学生列表
     */
    @Override
    public List<Student> findByDepartment(String department) {
        //
        List<Student> students = new ArrayList<Student>();

        //1.
        Connection conn = ConnectionManager.getConnection();
        //2.
        String strSQL = "select * from t_student where department like ?";
        try {
            //3.
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            //4.
            pstmt.setString(1,department + "%");
            //5.
            ResultSet rs = pstmt.executeQuery();
            //6.
            while (rs.next()) {
                //
                Student student = new Student();
                //
                student.setId(rs.getString("id"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setAge(rs.getInt("age"));
                student.setDepartment(rs.getString("department"));
                student.setClazz(rs.getString("class"));
                student.setTelephone(rs.getString("telephone"));
                //
                students.add(student);
            }
            //7.
            rs.close();
            //8.
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //
            ConnectionManager.closeConnection(conn);
        }

        //
        return students;
    }

    /**
     * 查询全部学生记录
     *
     * @return 学生列表
     */

    @Override
    public List<Student> findAll() {
        //
        List<Student> students = new ArrayList<Student>();

        //1.
        Connection conn = ConnectionManager.getConnection();
        //2.
        String strSQL = "select * from t_student";
        try {
            //3.
            Statement stmt = conn.createStatement();
            //4.
            ResultSet rs = stmt.executeQuery(strSQL);
            //5.
            while (rs.next()) {
                //
                Student student = new Student();
                //
                student.setId(rs.getString("id"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setAge(rs.getInt("age"));
                student.setDepartment(rs.getString("department"));
                student.setClazz(rs.getString("class"));
                student.setTelephone(rs.getString("telephone"));
                //
                students.add(student);
            }
            //6.
            rs.close();
            //7.
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //
            ConnectionManager.closeConnection(conn);
        }
        //
        return students;
    }

    /**
     * 按性别统计学生人数
     *
     * @return 统计结果向量
     */
    @Override
    public Vector findRowsBySex() {
        //
        Vector rows = new Vector();

        //1.
        Connection conn = ConnectionManager.getConnection();
        //2.
        String strSQL = "select sex as '性别', count(*) as '人数'"
                + " from t_student group by sex order by sex desc";
        try {
            //3.
            Statement stmt = conn.createStatement();
            //4.
            ResultSet rs = stmt.executeQuery(strSQL);
            //5.
            while (rs.next()) {
                //
                Vector<String> currentRow = new Vector();
                //
                currentRow.addElement(rs.getString("性别"));
                currentRow.addElement(rs.getInt("人数") + "");
                //
                rows.addElement(currentRow);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //
            ConnectionManager.closeConnection(conn);
        }

        //
        return rows;
    }




    /**
     * 按班级统计学生人数
     *
     * @return 统计结果向量
     */
    @Override
    public Vector findRowsByClass() {
        //定义行集向量
        Vector rows = new Vector();

        //1.获取数据库连接对象
        Connection conn = ConnectionManager.getConnection();
        //2.定义SQL字符串
        String strSQL = "select class as '班级', count(*) as '人数'"
                + " from t_student group by class order by class desc";
        try {
            //3. 创建语句对象
            Statement stmt = conn.createStatement();
            //4.执行SQL，返回结果集
            ResultSet rs = stmt.executeQuery(strSQL);
            //5.遍历结果集
            while (rs.next()) {
                //定义当前行向量
                Vector<String> currentRow = new Vector();
                //利用当前记录字段值设置当前行向量的元素值
                currentRow.addElement(rs.getString("班级"));
                currentRow.addElement(rs.getInt("人数") + "");
                //将当前行向量添加到行集向量
                rows.addElement(currentRow);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }

        //返回行集向量
        return rows;
    }


    /**
     * 按系部统计学生人数
     *
     * @return 统计结果向量
     */
    @Override
    public Vector findRowsByDepartment() {
        //定义行集向量
        Vector rows = new Vector();

        //1.获取数据库连接对象
        Connection conn = ConnectionManager.getConnection();
        //2.定义SQL字符串
        String strSQL = "select department as '系部', count(*) as '人数'"
                + " from t_student group by department order by department desc";
        try {
            //3.创建语句对象
            Statement stmt = conn.createStatement();
            //4.执行SQL。返回结果集
            ResultSet rs = stmt.executeQuery(strSQL);
            //5.遍历结果集
            while (rs.next()) {
                //定义当前行向量
                Vector<String> currentRow = new Vector();
                //利用当前纪录字段值设置当前行向量的元素值
                currentRow.addElement(rs.getString("系部"));
                currentRow.addElement(rs.getInt("人数") + "");
                //将当前行向量添加到行集向量
                rows.addElement(currentRow);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }

        //返回行集向量
        return rows;
    }
}

单元测试：对StudentDaoImpl进行单元测试

package net.lbj.student.test;
//测试StudentDaoImpl

import net.lbj.student.bean.Student;
import net.lbj.student.dao.StudentDao;
import net.lbj.student.dao.impl.StudentDaoImpl;
import org.junit.Test;

import java.util.Iterator;
import java.util.List;
import java.util.Vector;

public class TestStudentDaoImpl {
    //定义学生数据访问对象
    StudentDao dao = new StudentDaoImpl();

    @Test
    public void testInsert(){
        //创建学生对象
        Student student = new Student();
        //设置学生对象属性
        student.setId("19242036");
        student.setName("元可欣");
        student.setSex("女");
        student.setAge(20);
        student.setDepartment("艺术传媒学院");
        student.setClazz("2019数媒3班");
        student.setTelephone("15890653456");
        //调用学生数据访问对象的插入方法
        int count = dao.insert(student);
        //判断学生纪录是否插入成功
        if (count > 0) {
            System.out.println("恭喜，学生记录插入成功！");
            System.out.println(dao.findById(student.getId()));
        } else {
            System.out.println("遗憾，学生记录插入失败！");
        }
    }

    @Test
    public void testDeleteById() {
        String id = "19242099";
        //调用学生数据访问对象的按id删除方法
        int count = dao.deleteById(id);
        //判断学生记录是否删除成功
        if (count > 0) {
            System.out.println("恭喜，学生记录删除成功！");
        } else {
            System.out.println("遗憾，学生记录删除失败！");
        }
    }

    @Test
    public void testDeleteByClass() {
        String clazz = "2019小教2班";
        //调用学生数据访问对象的按班级删除方法
        int count = dao.deleteByClass(clazz);
        if (count > 0) {
            System.out.println("恭喜，[" + clazz + "]学生记录删除成功！");
        } else {
            System.out.println("遗憾，]" + clazz + "]学生记录删除失败！");
        }
    }

    @Test
    public void testFindByName() {
        //查找所有姓“李”的学生记录（还可以查单个人名字）
        String name = "李";
        //调用学生数据访问对象的按姓名查找方法
        List<Student> students = dao.findByName(name);
        //判断列表里是否元素
        if (students.size() > 0) {
            //通过增强for循环遍历学生列表
            for (Student student : students) {
                System.out.println(student);
            }
        } else {
            System.err.println("温馨提示：查无此人！");
        }
    }

    @Test
    public void testFindAll() {
        //调用学生数据访问对象的查找全部方法
        List<Student> students = dao.findAll();
        //通过增强for循环遍历学生列表
        for (Student student : students) {
            System.out.println(student);
        }
    }

    @Test
    public void testFindRowsBySex() {
        //调用学生数据访问对象的按性别统计人数方法
        Vector rows = dao.findRowsBySex();
        //获取向量的迭代器
        Iterator iterator = rows.iterator();
        //遍历迭代器
        while (iterator.hasNext()) {
            System.out.println(iterator.next());
        }
    }

    //---------------------
    @Test
    public void testDeleteByDepartment() {
        String department = "国际学院";
        //调用学生数据访问对象的按系部删除方法
        int count = dao.deleteByDepartment(department);
        //判断学生记录是否删除成功
        if (count > 0) {
            System.out.println("恭喜，学生记录删除成功！");
        } else {
            System.out.println("遗憾，学生记录删除失败！");
        }
    }

    @Test
    public void testUpdate() {
        //更新学生记录
        String id = "19242099";
        Student students = dao.findById(id);
        students.setName("余雨欣");
        students.setAge(20);
        students.setSex("女");
        students.setTelephone("15145456780");
        int count = dao.update(students);
        if (count > 0){
            System.out.println("学生记录更新成功！");
            System.out.println(dao.findById(id));
        } else {
            System.out.println("学生记录更新失败！");
        }
    }

    @Test
    public void testFindById() {
        String id= "19204091";
        //调用学生数据访问对象的按id查找方法
        Student student = dao.findById(id);
       //输出学生信息
        System.out.println("学号：" + student.getId());
        System.out.println("姓名：" + student.getName());
        System.out.println("性别：" + student.getSex());
        System.out.println("年龄：" + student.getAge());
        System.out.println("系部：" + student.getDepartment());
        System.out.println("班级：" + student.getClazz());
        System.out.println("电话：" + student.getTelephone());
    }

    @Test
    public void testFindByClass() {
        String clazz = "2019计应3班";
        //调用学生数据访问对象的按班级查找方法
        List<Student> students = dao.findByClass(clazz);
        //判断列表里是否元素
        if (students.size() > 0) {
            //通过增强for循环遍历学生列表
            for (Student student : students) {
                System.out.println(student);
            }
        } else {
            System.err.println("温馨提示：没有查到该班级！");
        }
    }
    @Test
    public void testFindByDepartment() {
        String department = "人文学院";
        //调用学生数据访问对象的按系部查找方法
        List<Student> students = dao.findByDepartment(department);
        //判断列表里是否元素
        if (students.size() > 0) {
            //通过增强for循环遍历学生列表
            for (Student student : students) {
                System.out.println(student);
            }
        } else {
            System.err.println("温馨提示：未查到该学院！");
        }
    }

    @Test
    public void testFindRowsByClass() {
        //调用学生数据访问对象的按班级统计人数方法
        Vector rows = dao.findRowsByClass();
        //获取向量的迭代器
        Iterator iterator = rows.iterator();
        //遍历迭代器
        while (iterator.hasNext()) {
            System.out.println(iterator.next());
        }
    }

    @Test
    public void testFindRowsByDepartment() {
        //调用学生数据访问对象的按系部统计人数方法
        Vector rows = dao.findRowsByDepartment();
        //获取向量的迭代器
        Iterator iterator = rows.iterator();
        //遍历迭代器
        while (iterator.hasNext()) {
            System.out.println(iterator.next());
        }
    }
}

在此代码中包含单元测试方法testInsert(),testDeleteById(),testDeleteByClass(),testFindByName(),testFindAll(),testFindRowsBySex(),
testDeleteByDepartment()……
4.创建用户数据访问接口实现类
创建用户数据访问接口实现类UserDaoImpl
代码展示：

package net.lbj.student.dao.impl;

import net.lbj.student.bean.User;
import net.lbj.student.dao.UserDao;
import net.lbj.student.dbutil.ConnectionManager;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户数据访问接口实现类
 */
public class UserDaoImpl implements UserDao {
    /**
     * 插入用户纪录
     * @param user
     * @return 插入记录数
     */
    @Override
    public int insert(User user) {
        int count = 0;

        //1.获取数据库连接
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "insert into t_user (username, password, telephone, register_time)" +
                "values (?, ?, ?, ?)";
        if (!isUsernameExisted(user.getUsername())) {
            try {
                PreparedStatement pstmt = conn.prepareStatement(strSQL);
                pstmt.setString(1, user.getUsername());
                pstmt.setString(2,user.getPassword());
                pstmt.setString(3,user.getTelephone());
                pstmt.setTimestamp(4, new Timestamp(user.getRegisterTime().getTime()));
                count = pstmt.executeUpdate();
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                ConnectionManager.closeConnection(conn);
            }
        }

        return count;
    }

    /**
     * 按id删除用户纪录
     *
     * @param id
     * @return 删除记录数
     */
    @Override
    public int deleteById(int id) {
        int count = 0;
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "delete from t_user where id = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            pstmt.setInt(1,id);
            count = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }

        return count;
    }

    /**
     * 更新用户纪录
     *
     * @param user
     * @return 更新记录数
     */
    @Override
    public int update(User user) {
        int count = 0;
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "update t_user set username = ?, password = ?, telephone = ?," +
                "register_time = ? where id = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getTelephone());
            pstmt.setTimestamp(4, new Timestamp(user.getRegisterTime().getTime()));
            pstmt.setInt(5, user.getId());
            count = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }

        return count;
    }

    /**
     * 按id查询用户
     *
     * @param id
     * @return 用户实体
     */
    @Override
    public User findById(int id) {
        User user = null;
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select * from t_user where id = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            pstmt.setInt(1,id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setTelephone(rs.getString("telephone"));
                user.setRegisterTime(rs.getTimestamp("register_time"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return user;
    }

    /**
     * 查询所有用户
     *
     * @return 用户列表
     */
    @Override
    public List<User> findAll() {
        List<User> users = new ArrayList<User>();
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select * from t_user";
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(strSQL);
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setTelephone(rs.getString("telephone"));
                user.setRegisterTime(rs.getTimestamp("register_time"));
                users.add(user);
            }
            rs.close();
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return users;
    }

    /**
     * 用户登录
     *
     * @param username
     * @param password
     * @return 登录用户实体
     */

    @Override
    public User login(String username, String password) {
        User user = null;
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select * from t_user where username = ? and password = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setTelephone(rs.getString("telephone"));
                user.setRegisterTime(rs.getTimestamp("register_time"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return user;
    }

    @Override
    public boolean isUsernameExisted(String username) {
        boolean existed = false;
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select * from t_user where username = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                existed = true;
            }
            pstmt.close();
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return existed;
    }
}

单元测试：对UserDaoImpl进行单元测试

package net.lbj.student.test;

import net.lbj.student.bean.User;
import net.lbj.student.dao.UserDao;
import net.lbj.student.dao.impl.UserDaoImpl;
import org.junit.Test;

import java.util.Date;
import java.util.List;

public class TestUserDaoImpl {
    //定义用户数据访问对象
    UserDao dao = new UserDaoImpl();

    @Test
    public void testFindById() {
        //调用用户数据访问对象的查找方法
        User user = dao.findById(1);
        //输入用户信息
        System.out.println("用户名：" + user.getUsername());
        System.out.println("密码：" + user.getPassword());
        System.out.println("电话：" + user.getTelephone());
        System.out.println("注册时间：" + user.getRegisterTime());
    }

    @Test
    public void testLogin() {
        String username, password;
        username = "李红";
        password = "444444";
        //调用用户数据访问对象的登录方法
        User user = dao.login(username, password);
        //判断用户登录是否成功
        if (user != null) {
            System.out.println("恭喜，用户名或密码正确，登陆成功！");
        } else {
            System.out.println("遗憾，用户名或密码错误，登陆失败！");
        }
    }

    @Test
    public void testIsUsernameExisted() {
        //定义用户名
        String username = "王霞";
        //调用用户数据访问对象的用户名存在与否方法
        boolean result = dao.isUsernameExisted(username);
        //判断用户名是否存在
        if (result) {
            System.out.println("温馨提示：[" + username + "]已存在，不可用此名注册！");
        } else {
            System.out.println("温馨提示：[" + username + "]不存在，可用此名注册！");
        }
    }

    @Test
    public void testInsert() {
        //定义用户对象
        User user = new User();
        //设置用户属性
        user.setUsername("吴欣");
        user.setPassword("101010");
        user.setTelephone("15123234590");
        user.setRegisterTime(new Date());
        //调用用户数据访问对象的插入方法
        int count = dao.insert(user);
        //判断用户记录是否插入成功
        if (count > 0) {
            System.out.println("恭喜，用户纪录插入成功！");
            System.out.println(dao.findById(dao.findAll().size()));
        } else {
            System.out.println("遗憾，用户纪录插入失败！");
        }
    }

    @Test
    public void testDeletedById() {
        int id = 1;
        //调用学生数据访问对象的id删除方法
        int count = dao.deleteById(id);
        //判断学生记录是否删除成功
        if (count > 0) {
            System.out.println("恭喜，学生记录删除成功！");
        } else {
            System.out.println("遗憾，学生记录删除失败！");
        }
    }

    @Test
    public void testUpdate() {
        //更新学生记录
        int id = 2;
        User user = dao.findById(id);
        user.setUsername("魏潇元");
        user.setPassword("112233");
        user.setTelephone("12134567654");
        int count = dao.update(user);
        if (count > 0) {
            System.out.println("学生记录更新成功！");
            System.out.println(dao.findById(id));
        } else {
            System.out.println("学生记录更新失败！");
        }
    }

    @Test
    public void testFindAll() {
        //调用学生数据访问对象的查找全部方法
        List<User> users = dao.findAll();
        //通过增强for循环遍历学生列表
        for (User user : users) {
            System.out.println(user);
        }
    }
}

在此代码中包含单元测试方法testFindById()，testLogin()，testIsUsernameExisted()，testInsert()，
testDeletedById()，testUpdate()，testFindAll()。