Java实训报告5

E66666666666666

已于 2023-06-18 11:45:11 修改

阅读量154

点赞数 1

分类专栏： java基础讲课笔记 java 文章标签： java 开发语言

于 2023-06-14 11:51:07 首次发布

本文链接：https://blog.csdn.net/E66666666666666/article/details/131204704

版权

java基础讲课笔记同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

java

5 篇文章 0 订阅

订阅专栏

文章目录

八、项目开发实现步骤
- （七）创建数据访问接口实现类

八、项目开发实现步骤

（七）创建数据访问接口实现类

在net.liujinxin.student.dao包里创建impl子包（impl：implementation）

1、创建学校数据访问接口实现类

在net.liujinxin.student.dao.impl包里创建CollegeDaoImpl类
实现CollegeDao接口
选择要实现的抽象方法
方法的空实现

（1）编写按标识符查询学校记录方法

package net.liujinxin.student.dao.impl;                                             
                                                                                    
import net.liujinxin.student.bean.College;                                          
import net.liujinxin.student.dao.CollegeDao;                                        
import net.liujinxin.student.dbutil.ConnectionManager;                              
                                                                                    
import javax.imageio.plugins.jpeg.JPEGImageReadParam;                               
import java.sql.Connection;                                                         
import java.sql.PreparedStatement;                                                  
import java.sql.ResultSet;                                                          
import java.sql.SQLException;                                                       
                                                                                    
                                                                                    
public class CollegeDaoImpl implements CollegeDao {                                 
                                                                                    
    @Override                                                                       
    public College findById(int id) {                                               
        // 定义一个学校对象                                                                 
        College college = null;                                                     
                                                                                    
        // 获取数据库连接                                                                  
        Connection conn = ConnectionManager.getConnection();                        
        // 定义SQL字符串                                                                 
        String strSQL = "select * from t_college where id = ?";                     
        // 创建预备语句对象                                                                 
        try {                                                                       
            PreparedStatement pstmt = conn.prepareStatement(strSQL);                
            // 设置占位符的值                                                              
            pstmt.setInt(1, id);                                                    
            // 执行查询操作，返回结果集                                                         
            ResultSet rs = pstmt.executeQuery();                                    
            // 判断结果集是否为空                                                            
            if (rs.next()) {                                                        
                // 创建学校对象                                                           
                college = new College();                                            
                // 利用当前记录的字段值设置学校对象属性值                                              
                college.setId(rs.getInt("id"));                                     
                college.setName(rs.getString("name"));                              
                college.setPresident(rs.getString("president"));                    
                college.setStartTime(rs.getTimestamp("star_time"));                 
                college.setEmail(rs.getString("email"));                            
                college.setAddress(rs.getString("address"));                        
                college.setProfile(rs.getString("profile"));                        
            }                                                                       
            // 关闭结果集                                                                
            rs.close();                                                             
            // 关闭预备语句对象                                                             
            pstmt.close();                                                          
        } catch (SQLException e) {                                                  
            System.err.println(e.getMessage());                                     
        } finally {                                                                 
            ConnectionManager.closeConnection(conn); // 关闭数据库连接                     
        }                                                                           
                                                                                    
        // 返回学校对象                                                                   
        return college;                                                             
    }

（2）编写更新学校记录方法

package net.liujinxin.student.dao.impl;

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

import javax.imageio.plugins.jpeg.JPEGImageReadParam;
import java.sql.*;

 @Override // 更新学校记录
    public int update(College college) {
        // 定义更新记录数
        int count = 0;

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

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

1_、测试学校数据访问接口实现类

在test目录里创建net.liujinxin.student.dao.impl包，在包里创建TestCollegeDaoImpl

（1）编写测试按标识符查询学校记录方法

package net.liujinx.student.dao.impl;

import net.liujinxin.student.bean.College;
import net.liujinxin.student.dao.CollegeDao;
import net.liujinxin.student.dao.impl.CollegeDaoImpl;
import org.junit.Test;

/**
 * 功能：测试学校数据访问接口实现类
 * 作者：刘金鑫
 * 日期：2023年06月14日
 */
public class TestCollegeImpl {
    @Test // 测试测试按标识符查询学校记录
    public void testFindById() {
        // 定义标识符变量
        int id = 1;
        // 创建学校数据访问接口对象
        CollegeDao collegeDao = new CollegeDaoImpl();
        // 调用按标识符查询学校记录方法
        College college = collegeDao.findById(id);
        // 判断查询是否成功
        if(college != null){
            System.out.println("标识符：" + college.getId());
            System.out.println("学校名称：" + college.getName());
            System.out.println("校长：" + college.getPresident());
            System.out.println("建校时间：" + college.getStartTime());
            System.out.println("电子邮箱：" + college.getEmail());
            System.out.println("通讯地址：" + college.getAddress());
            System.out.println("学校概况：" + college.getProfile());
        }else {
            System.out.println("标识符为[" + id + "]的学校记录不存在~");
        }
    }
}

测试结果

（2）编写测试更新学校记录方法

 @Test
    public void testUpdate(){
        CollegeDao collegeDao = new CollegeDaoImpl();
        // 获取标识符为1的学校记录
        College college = collegeDao.findById(1);
        // 输出更新前的学校信息
        System.out.println("更新前：" + college);
        // 更新学校对象属性
        college.setName("泸职院");
        college.setPresident("萌萌哒");
        college.setProfile("泸职院是省双高建设单位");
        // 调用更新学校记录方法
        int count = collegeDao.update(college);
        // 判断是否成功
        if (count > 0 ){
            System.out.println("恭喜，学校记录更新成功");
            System.out.println("更新后：" + collegeDao.findById(1));
        }else {
            System.out.println("遗憾，学校记录更新失败~");
        }
    }

运行该方法，查看结果

2、创建状态数据访问接口实现类

在net.liujinxin.student.dao.impl包里创建StatusDaoImpl类
实现StatusDao接口
选择要实现的抽象方法
方法的空实现

（1）编写按标识符查询学校记录方法

@Override
    public Status findById(int id) {
        // 定义状态对象
        Status status = null;
        //获取数据库连接
        Connection conn = ConnectionManager.getConnection();
        // 定义SQL字符串
        String strSQL = "select * from t_status where id = ?";
        // 创建预备对象
        try {
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            pstmt.setInt(1, id);
            // 执行查询操作，返回结果集
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()){
                // 实例化状态
                status = new Status();
                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"));
            }
            // 关闭预备对象
            pstmt.close();
            // 关闭对象结果集
                rs.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            // 关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }
        // 返回对象
        return status;
    }

（2）编写更新状态记录方法

    @Override
    public int update(Status status) {
        // 定义更新记录
        int count = 0;
        // 获取数据库连接
        Connection conn = ConnectionManager.getConnection();
        // 定义SQL字符
        String strSQL = "update t_status set college = ?, version = ?, author = ?, telephone = ?, address = ?, email = ? where id = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            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());
            // 执行更新操作，更新记录
            count = pstmt.executeUpdate();
            // 关闭预备语句对象
            pstmt.close();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            // 关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }
        return count;
    }

2_、测试状态数据访问接口实现类

在test目录里创建net.liujinxin.student.dao.impl包，在包里创建TestStatusDaoImpl

（1）编写测试按标识符查询状态记录方法

@Test
    public void testFindById(){
        int id = 1;
        StatusDao statusDao = new StatusDaoImpl(); // 申明数据访问对象
        // 调用状态数据访问对象的查询方法
        Status status = statusDao.findById(id);
        if(status != null) {
            // 输出状态信息
            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());
        }else{
            System.out.println("标识符为[" + id + "]的状态记录不存在~");
        }
    }

测试结果

（2）编写测试更新状态记录方法

@Test
    public void testUpdate(){
        StatusDao statusDao = new StatusDaoImpl();
        // 获取标识为1的状态记录
        Status status = statusDao.findById(1);
        // 输出更新前的状态记录
        System.out.println("更新前：" + status);
        // 更新状态对象属性
        status.setVersion("v1.0");
        status.setAddress("泸职院");
        status.setTelephone("123414142");
        status.setEmail("12431341234@qq.com");
        // 调用更新状态记录方法
        int count = statusDao.update(status);
        // 判断是否成功
        if (count > 0){
            System.out.println("恭喜，更新成功！");
            System.out.println("更新后：" + statusDao.findById(1));
        }else {
            System.out.println("抱歉，更新失败~");
        }
    }

测试结果

3、创建学生数据访问接口实现类

在net.liujinxin.student.dao.impl包里创建StudentDaoImpl类
实现StudentDao接口
选择要实现的抽象方法
方法的空实现

（1）编写按标识符插入学生记录方法

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

（2）编写删除学生记录方法

   @Override
    public int deleteById(String id) {
        int count = 0;
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "delete from t_student where id = ?";
        try {
            // 创建预备对象
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            // 设置占位符
            pstmt.setString(1, id);
            // 执行SQL，返回删除记录数
            count = pstmt.executeUpdate();
            // 关闭预备语句
            pstmt.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            // 关闭数据库
            ConnectionManager.closeConnection(conn);
        }
        return count;
    }

（3）编写按班级删除学生记录方法

    @Override
    public int deleteByClass(String clazz) {
        int count = 0;
        Connection conn =ConnectionManager.getConnection();
        // 定义SQL字符串
        String strSQL = "delete from t_student where class = ?";
        try {
            // 编写预备语句
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            pstmt.setString(1,clazz);
            count = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return count;

（4）编写按系部删除学生记录方法

    @Override
    public int deleteByDepartment(String department) {
        int count = 0;
        Connection conn =ConnectionManager.getConnection();
        // 定义SQL字符串
        String strSQL = "delete from t_student where department = ?";
        try {
            // 编写预备语句
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            pstmt.setString(1,department);
            count = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return count;
    }

（5）编写更新学生记录的方法

    @Override
    public int update(Student student) {
        int count = 0;
        Connection conn =ConnectionManager.getConnection();
        String strSQL = "update t_student set name = ?, sex = ?, age = ?, department = ?, clazz = ?, telephone = ? where id = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            pstmt.setString(1, student.getName());
            pstmt.setString(2, student.getSex());
            pstmt.setString(3, student.getAge());
            pstmt.setString(4, student.getDepartment());
            pstmt.setString(5, student.getClazz());
            pstmt.setString(6, student.getTelephone());
            pstmt.setString(7, student.getId());
            count = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
        }
        return count;
    }

（6）编写按学号查询学生记录方法

    @Override
    public Student findById(String id) {
        Student student = null;
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select * from t_student where id = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            pstmt.setString(1, id);
            // 执行SQL，返回结果集
            ResultSet rs = pstmt.executeQuery();
            // 判断结果集是否有记录
            if(rs.next()){
                student = new Student();
                student.setId(rs.getString("id"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setAge(rs.getString("age"));
                student.setDepartment(rs.getString("department"));
                student.setClazz(rs.getString("clazz"));
                student.setTelephone(rs.getString("telephone"));
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return student;
    }

（7）编写按姓名查询学生记录方法

    @Override
    public List<Student> findByName(String name) {
        List<Student> students = new ArrayList<>();
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select * from t_student where name like ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            pstmt.setString(1, name + "%");
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Student student = new Student();
                student = new Student();
                student.setId(rs.getString("id"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setAge(rs.getString("age"));
                student.setDepartment(rs.getString("department"));
                student.setClazz(rs.getString("clazz"));
                student.setTelephone(rs.getString("telephone"));
                // 将实体添加到学生列表
                students.add(student);
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return students;
    }

（8）编写按班级查询学生记录方法

    @Override
    public List<Student> findByClass(String clazz) {
        List<Student> students = new ArrayList<>();
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select * from t_student where clazz like ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            pstmt.setString(1, clazz + "%");
            ResultSet rs = pstmt.executeQuery();
            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.getString("age"));
                student.setDepartment(rs.getString("department"));
                student.setClazz(rs.getString("clazz"));
                student.setTelephone(rs.getString("telephone"));
                // 将实体添加到学生列表
                students.add(student);
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return students;
    }

（9）编写按系部查询学生记录方法

    @Override
    public List<Student> findByDepartment(String department) {
        List<Student> students = new ArrayList<>();
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select * from t_student where department like ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            pstmt.setString(1, department + "%");
            ResultSet rs = pstmt.executeQuery();
            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.getString("age"));
                student.setDepartment(rs.getString("department"));
                student.setClazz(rs.getString("clazz"));
                student.setTelephone(rs.getString("telephone"));
                // 将实体添加到学生列表
                students.add(student);
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return students;
    }

（10）编写查看全部学生记录

   @Override
    public List<Student> findAll() {
        List<Student> students = new ArrayList<>();
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select * from t_student";
        try {
            // 创建语对象
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(strSQL);
            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.getString("age"));
                student.setDepartment(rs.getString("department"));
                student.setClazz(rs.getString("clazz"));
                student.setTelephone(rs.getString("telephone"));
                // 将实体添加到学生列表
                students.add(student);
                rs.close();
                stmt.close();
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return students;
    }

（11）编写按性别统计学生人数方法

   @Override
    public Vector findRowsBySex() {
        // 定义行集变量
        Vector rows = new Vector();
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select sex as '性别', count(*) as '人数' from t_student group by sex order by sex desc";
        // 创建语句对象
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(strSQL);
            while (rs.next()){
                Vector<String> currentRow = new Vector<>();
                currentRow.addElement(rs.getString("性别"));
                currentRow.addElement(rs.getInt("人数") + "");
                // 想当前行向量添加到集向量
                rows.addElement(currentRow);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return rows;
    }

（12）编写按班级统计学生人数方法

    @Override
    public Vector findRowsByClass() {
        // 定义行集变量
        Vector rows = new Vector();
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select sex as '性别', count(*) as '人数' from t_student group by clazz order by clazz desc";
        // 创建语句对象
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(strSQL);
            while (rs.next()){
                Vector<String> currentRow = new Vector<>();
                currentRow.addElement(rs.getString("班级"));
                currentRow.addElement(rs.getInt("人数") + "");
                // 想当前行向量添加到集向量
                rows.addElement(currentRow);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return rows;
    }

（13）编写按系部统计学生人数方法

@Override
    public Vector findRowsByDepartment() {
        // 定义行集变量
        Vector rows = new Vector();
        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select sex as '性别', count(*) as '人数' from t_student group by department order by department desc";
        // 创建语句对象
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(strSQL);
            while (rs.next()){
                Vector<String> currentRow = new Vector<>();
                currentRow.addElement(rs.getString("系部"));
                currentRow.addElement(rs.getInt("人数") + "");
                // 想当前行向量添加到集向量
                rows.addElement(currentRow);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return rows;
    }

3_、测试学生数据访问接口实现类

在net.liujinxin.student.dao.Impl包里创建TestStudentsDaoImpl
在这里插入图片描述

（1）编写测试插入学生记录方法

package net.liujinx.student.dao.impl;

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

public class TestStudentsDaoImpl {
    StudentDao dao = new StudentDaoImpl();
    @Test
    public void testInsert(){
        Student student = new Student();
        student.setId("12345");
        student.setName("王俊朋");
        student.setSex("男");
        student.setAge("19");
        student.setDepartment("人工智能与大数据学院");
        student.setClazz("2022软件三班");
        student.setTelephone("19115803001");
        int count = dao.insert(student);
        if (count > 0){
            System.out.println("恭喜学生记录录入成功！");
            System.out.println(dao.findById(student.getId()));
        }else {
            System.out.println("遗憾，学生记录插入失败！");
        }
    }
}

测试结果
数据库表中添加效果

（2）编写按学号删除学生记录方法

@Test
    public void testDeleteById(){
        String id = "12345";
        int count = dao.deleteById(id);
        if(count > 0){
            System.out.println("恭喜，学生记录删除成功！");
        }else {
            System.out.println("遗憾，学生记录删除失败！");
        }
    }

测试结果
数据库表中效果

（3）编写测试按班级删除学生记录方法

@Test
    public void testDeleteByClass(){
        String clazz = "2022计应1班";
        int count = dao.deleteByClass(clazz);
        if(count > 0){
            System.out.println("恭喜，学生记录删除成功！");
        }else {
            System.out.println("遗憾，学生记录删除失败！");
        }
    }

测试结果
数据库表中效果

可见原“2022计应1班黄运已被删除”

（4）编写按名字查询学生记录方法

    @Test
    public void testFindByName(){
        String name = "name";
        List<Student> students = dao.findByName(name);
        if (students.size() > 0){
            // 通过增强for循环遍历学生列表
            for (Student student : students){
                System.out.println(student);
            }
        }else {
            System.out.println("温馨提示：查无此人");
        }
    }

输入表中没有的姓名测试结果
输入表中姓名测试结果

（5）编写测试按照系部查找学生信息方法

@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);
            }
        }
    }

测试结果

（6）编写测试查找学生全部信息方法

@Test
    public void testFindByAll(){
        List<Student> students = dao.findAll();
        for (Student student : students){
            System.out.println(student);
        }
    }

测试结果

（4）编写按性别统计人数的方法


    @Test
    public void testFinfRowsBySex(){
        Vector rows = dao.findRowsBySex();
        // 获取向量迭代器
        Iterator iterator = rows.iterator();
        while(iterator.hasNext()){
            System.out.println(iterator.next());
        }
    }

测试结果

（8）编写测试更新学生信息方法

@Test
   @Test
    public void testUpdate(){
        StudentDao studentDao = new StudentDaoImpl();
        Student student = studentDao.findById("19204196");
        System.out.println("更新前：" + student);
        student.setName("吴双龙");
        student.setDepartment("地球");
        student.setAge(19);
        student.setTelephone("11111000551");
        student.setDepartment("大数据学院");
        int count = studentDao.update(student);
        if (count > 0) {
            System.out.println("更新成功！！！");
            System.out.println("更新后：" + studentDao.findById("19204196"));
        } else {
            System.out.println("What a pity!更新失败！！！");
        }
    }

测试结果

（9）编写根据系部删除学生信息方法

@Test
    public void testDeleteByDepartment() {
        String department = "电子工程学院";

        int count = dao.deleteByDepartment(department);
        if (count > 0) {
            System.out.println("恭喜，" + department + "的学生记录删除成功！！！");
        } else {
            System.out.println("What a pity!学生记录删除失败！！！");
        }
    }

测试结果

（10）编写根据班级查找学生记录方法

@Test
    public void testFindByClass() {
        String clazz = "2022机电3班";

        List<Student> students = dao.findByClass(clazz);
        if (students.size() > 0) {
            for (Student student : students) {
                System.out.println(student);
            }
        } else {
            System.out.println("查询失败！！！");
        }
    }

测试结果

（11）编写测试方法testFindByClass()

@Test
    public void testFindByClass() {
        String clazz = "2022小教2班";

        List<Student> students = dao.findByClass(clazz);
        if (students.size() > 0) {
            for (Student student : students) {
                System.out.println(student);
            }
        } else {
            System.out.println("查询失败！！！");
        }
    }

测试结果

（12）编写测试方法testFindRowsByClass()

@Test
    public void testFindRowsByClass() {
        Vector rows = dao.findRowsByClass();
        Iterator iterator = rows.iterator();
        while (iterator.hasNext()) {
            System.out.println(iterator.next());
        }
    }

测试结果

（13）编写测试方法testFindRowsByDepartment()

@Test
    public void testFindRowsByDepartment() {

        Vector rows = dao.findRowsByDepartment();
        Iterator iterator = rows.iterator();
        while (iterator.hasNext()) {
            System.out.println(iterator.next());
        }
    }

测试结果

4、编写用户数据访问接口

在这里插入图片描述

空方法实现

（1）编写插入用户记录方法

@Override
    public int insert(User user) {
        int count = 0;

        Connection conn = ConnectionManager.getConnection();

        String strSQL = "INSERT INTO t_user (username, password, telephone, register_time)"
                + " value (?, ?, ?, ?)";
        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) {
                System.err.println(e.getMessage());
            } finally {
                ConnectionManager.closeConnection(conn);
            }
        }
        return count;
    }

（2）编写按照Id删除用户记录方法

@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) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return count;
    }

（3）编写更新用户信息方法

@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) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return count;
    }

（4）编写通过Id寻找用户信息方法

@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"));
            }
            pstmt.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return user;
    }

（5）编写查找全部用户信息方法

@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) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return users;
    }

（6）编写登陆方法

@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"));
            }
            pstmt.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return user;
    }

（7）编写已存在用户查找方法

@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) {
            System.err.println(e.getMessage());
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return existed;
    }

4_、测试用户数据访问数据接口实现类

（1）编写测试插入用户记录方法

@Test
    public void testInsert() {
        User user = new User();
        user.setUsername("GG爆");
        user.setPassword("123654");
        user.setTelephone("963874521");
        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("插入失败！！！");
        }
    }

测试结果

（2）编写测试按照Id删除用户记录方法

@Test
    public void testDeleteById() {
        int id = 3;
        int count = dao.deleteById(id);
        if (count != 0) {
            System.out.println("删除用户记录成功！！！");
        } else {
            System.out.println("删除记录失败！！！");
        }
    }

测试结果

（3）编写测试更新用户信息方法

 @Test
    public void testUpdate() {
        UserDao userdao = new UserDaoImpl();
        User user = userdao.findById(1);
        System.out.println("更新前：" + user);
        user.setTelephone("112312342");
        user.setUsername("超人强");
        int count = userdao.update(user);
        if (count > 0) {
            System.out.println("更新成功！！！");
            System.out.println("更新后：" + userdao.findById(1));
        } else {
            System.out.println("更新失败！！！");
        }
    }

测试结果

（4）编写测试通过Id寻找用户信息方法

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

测试结果

（5）编写测试查找全部用户信息方法

@Test
    public void testFindAll() {

        List<User> users = dao.findAll();
        for (User user : users) {
            System.out.println(user);
        }
    }

测试结果

（6）编写测试登陆方法

@Test
    public void testLogin() {
        String username, password;
        username = "超人强";
        password = "admin";
        User user = dao.login(username, password);
        if (user != null) {
            System.out.println("用户名与密码正确，登录成功！！！");
        } else {
            System.out.println("登录失败！用户名或密码输入错误！！！");
        }
    }

测试结果

（7）编写测试已存在用户查找方法

  @Test
    public void testIsUsernameExisted() {
        String username = "张三丰";
        boolean result = dao.isUsernameExisted(username);
        if (result) {
            System.out.println("温馨提示：用户名[" + username + "]已存在，不可用此名注册！！！");
        } else {
            System.out.println("温馨提示：用户名[" + username + "]不存在，可用此名注册");
        }
    }