第三次实训课

于 2020-07-08 15:36:03 发布
阅读量199 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/denglijuan_/article/details/107207245
版权

第三次实训课

一、创建数据访问接口
先创建dao包
创建数据访问接口CollegeDao
在这里插入图片描述
创建学生数据访问接口StatusDao
在这里插入图片描述
创建用户数据访问接口StudentDao
在这里插入图片描述
创建用户数据访问接口UserDao
在这里插入图片描述
二、创建数据访问接口实现类
在net.dlj.student.dao包里创建impl子包。
创建学校数据访问接口实现类CollegeDaoImpl

package net.dlj.student.dao.impl;
import net.dlj.student.bean.College;
import net.dlj.student.dao.CollegeDao;
import net.dlj.student.dbutil.ConnectionManager;

import java.sql.*;

/**

  • 功能：学校数据访问接口实现类
  • 作者：邓力涓
  • 日期：2020年07月04日
    */

public class CollegeDaoImpl implements CollegeDao {

@Override
public College findById(int id) {
    College college = null;

    Connection conn = ConnectionManager.getConnection();

    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("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 e) {
        e.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 e) {
        e.printStackTrace();
    } finally {
        ConnectionManager.closeConnection(conn);
    }

    return count;
}

}
在根包net.dlj.student里创建test子包，在里面创建测试类TestCollegeDaoImpl
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
创建状态数据访问接口实现类StatusDaoImpl
在这里插入图片描述
在这里插入图片描述
在net.dlj.student.test包里创建测试类TestStatusDaoImpl
在这里插入图片描述
在这里插入图片描述
学生数据访问接口实现类StudentDaoImpl

package net.dlj.student.dao.impl;

import net.dlj.student.bean.Student;
import net.dlj.student.dao.StudentDao;
import net.dlj.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();
        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());
            count = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }

        return count;
    }

    @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);
            count = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }
        return count;
    }

    @Override
    public int deleteByClass(String clazz) {
        int count = 0;

        Connection conn = ConnectionManager.getConnection();
        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) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }

        return count;
    }

    @Override
    public int deleteByDepartment(String department) {
        int count = 0;

        Connection conn = ConnectionManager.getConnection();
        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) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }

        return count;
    }

    @Override
    public int update(Student student) {
        int count = 0;

        Connection conn = ConnectionManager.getConnection();
        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());

            count = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }

        return count;
    }

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

            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 e) {
            e.printStackTrace();
        } finally {

            ConnectionManager.closeConnection(conn);
        }

        return student;
    }


    @Override
    public List<Student> findByName(String name) {
        // 声明学生列表
        List<Student> students = new ArrayList<Student>();


        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.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;
    }

    @Override
    public List<Student> findByClass(String clazz) {

        List<Student> students = new ArrayList<Student>();


        Connection conn = ConnectionManager.getConnection();
        // 2. 定义SQL字符串
        String strSQL = "select * from t_student where class 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.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;
    }

    @Override
    public List<Student> findByDepartment(String department) {
        List<Student> students = new ArrayList<Student>();

        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.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;
    }

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

        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.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 e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }

        return students;
    }
    @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) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }

        return rows;
    }

    @Override
    public Vector findRowsByClass() {
        Vector rows = new Vector();

        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select class as '班级', count(*) as '人数'"
                + " from t_student group by class order by class 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) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }

        return rows;
    }

    @Override
    public Vector findRowsByDepartment() {
        Vector rows = new Vector();

        Connection conn = ConnectionManager.getConnection();
        String strSQL = "select department 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) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }

        return rows;
    }
}

在net.dlj.student.test包里创建测试类
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
创建用户数据访问接口实现类UserDaoImpl
package net.dlj.student.dao.impl;

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

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

public class UserDaoImpl implements UserDao {

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

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

@Override
public int deleteById(String 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, Integer.parseInt(id));
        count = pstmt.executeUpdate();
        pstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        ConnectionManager.closeConnection(conn);
    }

    return count;
}

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

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

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

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

}
在net.dlj.student.test包里创建测试类TestUserDaoImpl

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

denglijuan_
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值