欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

java第三次实训

程序员文章站 2024-01-25 08:54:40
创建学校数据访问接口实现类创建访问接口类1.在CollegeDaolmpl中写代码1.1在test中创建测试类testCollegeDaoImpl1.1.2编写testUpdate方法2.在StausDaoImpl中插入一下代码2.1 测试TestStatusDaoImpl方法3. 在StudentDaoImpl中插入以下代码3.1 编写测试类TestStudentDaoImpl3.1.1. 先测试testInsert3.1.2.测试testDeleteById3.1.3.测试testDeleteByCla...


创建下面几个类
java第三次实训
19计应三班 贾杨庆

创建访问接口类

1.在CollegeDaolmpl中写代码

package net.jyq.student.dao.impl;

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

import java.sql.*;

public class CollegeDaolmpl  implements CollegeDao {
    @Override
    public College findById(int id) {
        //定义学校对象
        College college=null;

        //获取数据库连接
        Connection conn= ConnectionManager.getConnection();
        //定义sql字符串
        String srtSQL="select * from t_college where id=?";
        try {
            PreparedStatement pstmt=conn.prepareStatement(srtSQL);
            pstmt.setInt(1,id);
            ResultSet rs=pstmt.executeQuery();
            college =new College();
            college.setId(rs.getInt("id"));
            college.setName(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"));
            pstmt.close();
            rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }

        return college;
    }

    @Override
    public int update(College college) {
        int count=0;

        Connection conn=ConnectionManager.getConnection();
        String strSQL= "update t_college set name = ?, president = ?, start_time = ?,"
                + " telephone = ?, 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.getTelephone());
            pstmt.setString(5,college.getEmail());
            pstmt.setString(6,college.getAddress());
            pstmt.setString(7,college.getProfile());
            pstmt.setInt(8,college.getId());

            count=pstmt.executeUpdate();

            pstmt.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return count;
    }
}

在这里插入图片描述

1.1在test中创建测试类testCollegeDaoImpl

编写testFindById方法

package net.jyq.student.test;

import net.jyq.student.bean.College;
import net.jyq.student.bean.User;
import net.jyq.student.dao.CollegeDao;
import net.jyq.student.dao.impl.CollegeDaolmpl;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.Date;


public class TestCollegeDaoImpl {
    //创建学校数据访问对象
    CollegeDao dao=new CollegeDaolmpl();
    @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("没有信息");
        }
    }

运行结果
java第三次实训

1.1.2编写testUpdate方法

    @Test
    public void testUpdate(){

        //调用调用学校数据访问对象的查询方法
        College college=dao.findById(1);
        college.setPresident("王成新");
        //调用学校数据访问为对象的更新方法
        int count=dao.update(college);
        //判断是否更新成功
        if(count>0){
            System.out.println("学校记录更新成功");
            System.out.println("新校长:"+dao.findById(1).getPresident());
        }else{
            System.out.println("学校记录更新成功");
        }
    }

运行结果
java第三次实训

2.在StausDaoImpl中插入一下代码

package net.jyq.student.dao.impl;
import net.jyq.student.bean.Status;
import net.jyq.student.dao.StatusDao;
import net.jyq.student.dbutil.ConnectionManager;

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


public class StausDaoImpl 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()) {
                // 实例化状态
                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("emali"));
            }
            // 7. 关闭预备语句对象
            pstmt.close();
            // 8. 关闭结果集对象
            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;
    }
}

2.1 测试TestStatusDaoImpl方法

package net.jyq.student.test;

import net.jyq.student.bean.Status;
import net.jyq.student.dao.StatusDao;
import net.jyq.student.dao.impl.StausDaoImpl;
import org.junit.Test;

public class TestStatusDaoImpl {
    //声明状态数据访问对象
    StatusDao dao =new StausDaoImpl();
    @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.getEmail());
    }   
}

运行结果
java第三次实训

在StausDaolmpl中emali书写错误导致出错
java第三次实训
出错的问题
java第三次实训
运行结果
java第三次实训

3. 在StudentDaoImpl中插入以下代码

package net.jyq.student.dao.impl;

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

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

public class StudentDaoImpl implements StudentDao {
    @Override
    public int insert(Student student) {
        //定义插入记录数
        int count=0;
        //获取数据库连接
        Connection  conn= ConnectionManager.getConnection();
        //定义sql字符串
        String strSQL="insert into t_student (id, name, sex, age, department, class, telephone)"
                + " values (?, ?, ?, ?, ?, ?, ?)";
        try {
            PreparedStatement pstmt=conn.prepareStatement(strSQL);
            //设置占位符的值
            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());
            //执行SQL返回插入的记录数
            count=pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
           ConnectionManager.closeConnection(conn);
        }
        return count;
    }

    /**
     * 按学号删除学生记录
     * @param
     * @return
     */
    @Override
    public int deleteById(String id) {

        int count=0;
        //获取数据库;链接
        Connection conn=ConnectionManager.getConnection();
        //定义sql字符串
        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 throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }
         //返回删除记录数
        return count;
    }

    /**
     * 按班级删除记录数
     * @param clazz
     * @return
     */

    @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);
            //执行SQL。返回删除语句
            count=pstmt.executeUpdate();
            //关闭数据库
            pstmt.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }
        return count;
    }

    /**
     * 按系部删除学生记录
     * @param department
     * @return
     */
    @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);
            //执行sql返回删除记录数
            count=pstmt.executeUpdate();
            //关闭预备语句对象
            pstmt.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }
          //返回删除记录
        return count;
    }

    /**
     * 更新学生记录
     * @param student
     * @return
     */

    @Override
    public int update(Student student) {
        int count=0;
        //创建数据库链接
        Connection conn=ConnectionManager.getConnection();
        //定义sql语句
        String strSQL="update t_student set name = ?, sex = ?, age = ?,"
                + " department = ?, class = ?, telephone = ? where id = ?";
        try {
            //创建预备对象
            PreparedStatement pstmt=conn.prepareStatement(strSQL);
            //设置占位符的值
            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());
            //执行SQL语句,返回更新数
            count=pstmt.executeUpdate();
            //关闭预备对象
            pstmt.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭数据库链接
            ConnectionManager.closeConnection(conn);
        }
        //返回更新记录数
        return count;
    }

    /**
     * 按学号查询学生记录
     * @param id
     * @return
     */
    @Override
    public Student findById(String id) {
        //声明学生对象
        Student student=null;
        //获取数据链接
        Connection conn=ConnectionManager.getConnection();
        //定义sql语句
        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.getInt("age"));
                student.setDepartment(rs.getString("department"));
                student.setClazz(rs.getString("class"));
                student.setTelephone(rs.getString("telephone"));

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }

        return student;
    }

    /**
     * 按姓名查询学生记录
     * @param name
     * @return
     */
    @Override
    public List<Student> findByName(String name) {
        //声明学生列表
        List<Student> students=new ArrayList<Student>();
        //获取数据库链接
        Connection conn= ConnectionManager.getConnection();
        //定义SQL 语句
        String strSQL="select * from t_student where name like ?";
        try {
            //创建预备语句
            PreparedStatement pstmt=conn.prepareStatement(strSQL);
            //设置占位符的值
            pstmt.setString(1,name+"%");
            //执行SQL语句返回结果值
            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.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 throwables) {
            throwables.printStackTrace();
        } finally {
            // 关闭数据库连接
           ConnectionManager.closeConnection(conn);
        }
        //返回学生列表
        return students;
    }

    /**
     * 按照班级查询学生记录
     * @param clazz
     * @return
     */

    @Override
    public List<Student> findByClass(String clazz) {
        //声明学生列表
        List <Student> students=new ArrayList<Student>();
        //获取数据库链接
        Connection conn=ConnectionManager.getConnection();
        //定义SQL语句
        String strSQL="select * from t_student where class like ?";
        try {
            //创建预备语句对象
            PreparedStatement pstmt=conn.prepareStatement(strSQL);
            //设置占位符的值
            pstmt.setString(1,clazz+"%");
            //执行sql返回结果值
            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.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 throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭数据库链接
            ConnectionManager.closeConnection(conn);
        }
        //返回学生列表
        return students;
    }

    /**
     * 按系部查询学生记录
     * @param department
     * @return
     */
    @Override
    public List<Student> findByDepartment(String department) {
        List<Student>students=new ArrayList<Student>();
        //获取数据库链接
        Connection conn=ConnectionManager.getConnection();
        //定义SQL 语句
        String strSQL="select * from t_student where department like ?";
        try {
            //创建预备语句对象
            PreparedStatement pstmt=conn.prepareStatement(strSQL);
            //设置占位符的值
            pstmt.setString(1,department+"%");
            //执行SQL语句返回结果值
            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.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 throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return students;
    }

    /**
     * 查询全部学生记录
     * @return
     */
    @Override
    public List<Student> findByAll() {
        List<Student>students=new ArrayList<Student>();
        //获取数据库链接
        Connection conn= ConnectionManager.getConnection();
        //定义SQL 语句
        String strSQL="select * from t_student";
        try {
            //创建语句对象
            Statement stmt=conn.createStatement();
            //执行SQL返回结果值
            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.getInt("age"));
                student.setDepartment(rs.getString("department"));
                student.setClazz(rs.getString("class"));
                student.setTelephone(rs.getString("telephone"));
                students.add(student);

            }
            rs.close();
            stmt.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return students;
    }

    @Override
    public Vector findRowsBySex() {
        //定义行集向量
        Vector rows=new Vector();
        //获取数据库链接
        Connection conn=ConnectionManager.getConnection();
        //定义sql语句
        String strSQL="select sex as '性别', count(*) as '人数'"
                + " from t_student group by sex order by sex desc";
        try {
            //创建语句对象
            Statement stmt=conn.createStatement();
            //执行sql返回结果集
            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 throwables) {
            throwables.printStackTrace();
        } finally {
            //将当前行向量添加到行集向量
            ConnectionManager.closeConnection(conn);
        }
        return rows;
    }

    /**
     *按班级
     * @return
     */
    @Override
    public Vector findRowsByClass() {
        //定义行集向量
        Vector rows=new Vector();
        //获取数据库链接
        Connection conn=ConnectionManager.getConnection();
        //定义sql语句
        String strSQL="select class as '班级', count(*) as '人数'"
                + " from t_student group by class order by class desc";
        try {
            //创建语句对象
            Statement stmt=conn.createStatement();
            //执行sql返回结果集
            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 throwables) {
            throwables.printStackTrace();
        } finally {
            //将当前行向量添加到行集向量
            ConnectionManager.closeConnection(conn);
        }
        return rows;
    }


    @Override
    public Vector findRowsByDepartment() {
        //定义行集向量
        Vector rows=new Vector();
        //获取数据库链接
        Connection conn=ConnectionManager.getConnection();
        //定义sql语句
        String strSQL="select department as '系部', count(*) as '人数'"
                + " from t_student group by department order by department desc";
        try {
            //创建语句对象
            Statement stmt=conn.createStatement();
            //执行sql返回结果集
            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 throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭数据库链接
            ConnectionManager.closeConnection(conn);
        }
        return rows;
    }

    }

3.1 编写测试类TestStudentDaoImpl

3.1.1. 先测试testInsert

import net.jyq.student.dao.StudentDao;
import net.jyq.student.dao.impl.StudentDaoImpl;
import org.junit.Test;

import java.util.Collections;
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("10060309");
        student.setName("张晓松");
        student.setSex("女");
        student.setAge(20);
        student.setDepartment("商学院");
        student.setClazz("19级市场营销2班");
        student.setTelephone("12345678910");
        int count=dao.insert(student);
        if(count>0){
            System.out.println("恭喜,学生记录插入!");
            System.out.println(dao.findById(student.getId()));
        }else{
            System.out.println("遗憾,学生记录插入失败");
        }
    }

运行结果
java第三次实训

3.1.2.测试testDeleteById

@Test
    public void testDeleteById(){
        //按学号来删除
        String  id="10050303";
        int count=dao.deleteById(id);
        if(count>0){
            System.out.println("恭喜,删除成功");
        }else{
            System.out.println("遗憾,删除失败");
        }
    }

运行结果
java第三次实训

3.1.3.测试testDeleteByClass

  @Test
    public void testDeleteByClass(){
        String clazz="19英教1班";
        //调用学生数据访问对象的按班级删除方法
        int count=dao.deleteByClass(clazz);
        if(count>0){
            System.out.println("恭喜,删除成功");
        }else{
            System.out.println("遗憾,删除失败");
        }
    }

运行结果在之前数据库被删除过所以删除失败
java第三次实训

3.1.4.测试testFindByNam

 @Test
    public void testFindByName(){
        String name="张三";
        //调用学生数据访问对象的按姓名查找方法
        List<Student> students=dao.findByName(name);
        if(students.size()>0){
            for(Student student:students){
                System.out.println(student);
            }
        }else{
            System.out.println("没有此人");
        }
    }

运行结果
java第三次实训

3.1.5.测试 testFindAll

@Test
    public void testFindAll(){
        //调用学生数据访问对象的全部查找方法
        List<Student> students=dao.findByAll();
        for(Student student:students){
            System.out.println(student);
        }

    }

运行结果
java第三次实训

3.1.6.测试testFindRowsBySe

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

运行结果
java第三次实训

3.1.7.测试testDeleteByDepartment

 @Test
    public void testDeleteByDepartment(){
        //按部门来删除
        String department="信息工程学院";
        int count=dao.deleteByDepartment(department);
        if(count>0){
            System.out.println("恭喜,删除成功");
        }else{
            System.out.println("遗憾,删除失败");
        }

    }

运行结果
java第三次实训

3.1.8.测试testUpdate

@Test
    public void testUpdate(){
        Student student =new Student();
        student.setId("10060301");
        student.setName("黄忠文");
        int count=dao.update(student);
        if(count>0){
            System.out.println("恭喜,更新成功");
        }else{
            System.out.println("遗憾,更新失败");
        }

    }

运行结果
java第三次实训

3.1.9.测试testFindById

  @Test
    public void testFindById(){
        String id="10080302";
        List<Student> students = Collections.singletonList(dao.findById(id));
        if(students.size()>0){
            for(Student student:students){
                System.out.println(student);
            }
        }else{
            System.out.println("没有此人");
        }
    }

运行结果
java第三次实训

3.1.10测试testFindByClass

 @Test
    public void testFindByClass(){
        String clazz="19级英教1班";
        List<Student> students = Collections.singletonList(dao.findById(clazz));
        if(students.size()>0){
            for(Student student:students){
                System.out.println(student);
            }
        }else{
            System.out.println("没有此人");
        }
    }

运行结果
java第三次实训

3.1.11.测试testFindByDepartment

 @Test
    public void testFindByDepartment(){
        String department="信息工程学院";
        List<Student> students = Collections.singletonList(dao.findById(department));
        if(students.size()>0){
            for(Student student:students){
                System.out.println(student);
            }
        }else{
            System.out.println("没有此人");
        }

    }

运行结果
java第三次实训

3.1.12.测试testFindRowsByClass

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

运行结果
java第三次实训

3.1.13.测试testFindRowsByDepartment

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

运行结果
java第三次实训

4. 在UserDaolmpl 中插入以下代码

package net.jyq.student.dao.impl;

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

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

public class UserDaolmpl implements UserDao {
    @Override
    public int insert(User user) {
        // 定义插入记录数
        int count = 0;

        // 1. 获得数据库连接
        Connection conn = ConnectionManager.getConnection();
        // 2. 定义SQL字符串
        String strSQL = "insert into t_user (username, password, telephone, register_time)"
                + " values (?, ?, ?, ?)";

        // 不允许用户表里插入两条用户名相同的记录
        if (!isUsernameExisted(user.getUsername())) {
            try {
                // 3. 创建预备语句对象
                PreparedStatement pstmt = conn.prepareStatement(strSQL);
                // 4. 设置占位符的值
                pstmt.setString(1, user.getUsername());
                pstmt.setString(2, user.getPassword());
                pstmt.setString(3, user.getTelephone());
                pstmt.setTimestamp(4, new Timestamp(user.getRegisterTime().getTime()));
                // 5. 执行SQL,返回插入记录数
                count = pstmt.executeUpdate();
                // 6. 关闭预备语句对象
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                // 关闭数据库连接
                ConnectionManager.closeConnection(conn);
            }
        }

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

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

        // 1. 获取数据库连接
        Connection conn = ConnectionManager.getConnection();
        // 2. 定义SQL字符串
        String strSQL = "delete from t_user 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 user
     * @return 更新记录数
     */
    @Override
    public int update(User user) {
        // 定义更新记录数
        int count = 0;

        // 1. 获得数据库连接
        Connection conn = ConnectionManager.getConnection();
        // 2. 定义SQL字符串
        String strSQL = "update t_user set username = ?, password = ?, telephone = ?,"
                + " register_time = ? where id = ?";
        try {
            // 3. 创建预备语句对象
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            // 4. 设置占位符的值
            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());
            // 5. 执行SQL,返回更新记录数
            count = pstmt.executeUpdate();
            // 6. 关闭预备语句对象
            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;

        // 1. 获取数据库连接对象
        Connection conn = ConnectionManager.getConnection();
        // 2. 定义SQL字符串
        String strSQL = "select * from t_user where id = ?";
        try {
            // 3. 创建预备语句对象
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            // 4. 设置占位符的值
            pstmt.setInt(1, id);
            // 5. 执行SQL,返回结果集
            ResultSet rs = pstmt.executeQuery();
            // 6. 判断结果集是否有记录
            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>();

        // 1. 获取数据库连接对象
        Connection conn = ConnectionManager.getConnection();
        // 2. 定义SQL字符串
        String strSQL = "select * from t_user";
        try {
            // 3. 创建语句对象
            Statement stmt = conn.createStatement();
            // 4. 执行SQL,返回结果集
            ResultSet rs = stmt.executeQuery(strSQL);
            // 5. 遍历结果集
            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);
            }
            // 6. 关闭结果集
            rs.close();
            // 7. 关闭语句对象
            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;

        // 1. 获取数据库连接
        Connection conn = ConnectionManager.getConnection();
        // 2. 定义SQL字符串
        String strSQL = "select * from t_user where username = ? and password = ?";
        try {
            // 3. 创建预备语句对象
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            // 4. 设置占位符的值
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            // 5. 执行SQL,返回结果集
            ResultSet rs = pstmt.executeQuery();
            // 6. 判断结果集是否有记录
            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;

        // 1. 获取数据库连接
        Connection conn = ConnectionManager.getConnection();
        // 2. 定义SQL字符串
        String strSQL = "select * from t_user where username = ?";
        try {
            // 3. 创建预备语句对象
            PreparedStatement pstmt = conn.prepareStatement(strSQL);
            // 4. 设置占位符的值
            pstmt.setString(1, username);
            // 5. 执行SQL,返回结果集
            ResultSet rs = pstmt.executeQuery();
            // 6. 判断结果集是否有记录
            if (rs.next()) {
                existed = true;
            }
            // 7. 关闭预备语句对象
            pstmt.close();
            // 8. 关闭结果集对象
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接
            ConnectionManager.closeConnection(conn);
        }

        // 返回存在与否变量
        return existed;
    }
}

4.1 测试UserDaolmpl

4.1.1.测试TestUserDaoImp

package net.jyq.student.test;

import net.jyq.student.bean.Student;
import net.jyq.student.bean.User;
import net.jyq.student.dao.UserDao;
import net.jyq.student.dao.impl.UserDaolmpl;
import org.junit.Test;

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

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

运行结果
java第三次实训

4.1.2.测试testLogin

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

运行结果
java第三次实训

4.1.2.测试testInsert

@Test
    public  void testInsert(){
        User user=new User();
        user.setUsername("鸿俊");
        user.setPassword("123456");
        user.setTelephone("123456878910");
        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("遗憾,用户记录插入失败");
        }
    }

运行结果
java第三次实训

4.1.3.测试testDeleteById

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

    }

运行结果
java第三次实训

4.1.4.测试testUpdat

 @Test
    public void testUpdate(){
        User user = new User();
        user.setId(5);
        user.setUsername("王丽");
        user.setPassword("123456");
        user.setTelephone("12343425678");
        user.setRegisterTime(new Date());
        int count = dao.update(user);
        if (count > 0){
            System.out.println("恭喜,更新学生记录成功");
            System.out.println(user);
        }else{
            System.out.println("遗憾,更新数据失败");
        }
    }

运行结果
java第三次实训

4.1.5.测试 testFindAll

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

    }

运行结果
java第三次实训

本文地址:https://blog.csdn.net/Nflyingj/article/details/107162348