JSP+Servlet培训班作业管理系统[9] -数据库操作层实现

本篇实现数据库相关的实体类(entity包)和操作类(operation包)，具体代码如下：

/*entity包下的实体类，与数据库中的表是对应关系*/
package entity;
public class Role {//对应system_role
    private int roleId;
    private String roleName;
    public int getRoleId() {
        return roleId;
    }
    public void setRoleId(int roleId) {
        this.roleId = roleId;
    }
    public String getRoleName() {
        return roleName;
    }
    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }
}
package entity;
public class User {//对应system_user
    private int userId;
    private String userName;
    private String userPassword;
    private Role userRole;  
    public int getUserId() {
        return userId;
    }
    public void setUserId(int userId) {
        this.userId = userId;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public String getUserPassword() {
        return userPassword;
    }
    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }
    public Role getUserRole() {
        return userRole;
    }
    public void setUserRole(Role userRole) {
        this.userRole = userRole;
    }
}
package entity;
public class Course {//对应system_course
    private int courseId;
    private String courseName;
    private User courseUser;
    public int getCourseId() {
        return courseId;
    }
    public void setCourseId(int courseId) {
        this.courseId = courseId;
    }
    public String getCourseName() {
        return courseName;
    }
    public void setCourseName(String courseName) {
        this.courseName = courseName;
    }
    public User getCourseUser() {
        return courseUser;
    }
    public void setCourseUser(User courseUser) {
        this.courseUser = courseUser;
    }
}
package entity;
public class Work {//对应system_work
    private int workId;
    private String workTitle;
    private String workTime;
    private Course workCourse;
    public int getWorkId() {
        return workId;
    }
    public void setWorkId(int workId) {
        this.workId = workId;
    }
    public String getWorkTitle() {
        return workTitle;
    }
    public void setWorkTitle(String workTitle) {
        this.workTitle = workTitle;
    }
    public String getWorkTime() {
        return workTime;
    }
    public void setWorkTime(String workTime) {
        this.workTime = workTime;
    }
    public Course getWorkCourse() {
        return workCourse;
    }
    public void setWorkCourse(Course workCourse) {
        this.workCourse = workCourse;
    }
}
package entity;
public class Job {//对应work_job
    private int jobId;  
    private String jobTime;
    private String jobContent;
    private int jobScore;
    private Work jobWork;
    private User jobUser;
    public int getJobId() {
        return jobId;
    }
    public void setJobId(int jobId) {
        this.jobId = jobId;
    }
    public String getJobTime() {
        return jobTime;
    }
    public void setJobTime(String jobTime) {
        this.jobTime = jobTime;
    }
    public String getJobContent() {
        return jobContent;
    }
    public void setJobContent(String jobContent) {
        this.jobContent = jobContent;
    }
    public int getJobScore() {
        return jobScore;
    }
    public void setJobScore(int jobScore) {
        this.jobScore = jobScore;
    }
    public Work getJobWork() {
        return jobWork;
    }
    public void setJobWork(Work jobWork) {
        this.jobWork = jobWork;
    }
    public User getJobUser() {
        return jobUser;
    }
    public void setJobUser(User jobUser) {
        this.jobUser = jobUser;
    }
}

然后来实现对这几张表基本的增、删、改、查操作类（对于外键的操作和关联，见仁见智，猫哥的意见是，够用就好！），操作类均放于包operation下，为了规范操作类的基本必备操作，建立一个接口ObjectOperation如下：

package operation;
import java.util.List;
public interface ObjectOperation {//操作接口，用于执行对象对应数据库表的增删改查操作
    public List selectAll();//选取表中所有数据
    public Object selectById(int id);//按id获取一条记录
    public int add(Object obj);//添加一条数据
    public int deleteById(String id);//按id删除一条记录
    public int update(Object obj);//按obj对象的信息修改一条记录(以obj的id标记需要修改的记录)
}

好的，为了实现数据库操作，将之前已经设立的数据库类拷贝进来，放于包mysql下，并将其中的异常相关类修改如下（跟mysql相关的操作具体见猫哥带你去战斗—Java Web开发—Java篇[12]—使用连接池的mysql操作类）（不要忘记将mysql-connector-java-5.1.39-bin.jar放于WEB-INF\lib下）：

package mysql;
import java.sql.*;
import java.util.Date;
import java.util.LinkedList;
import exception.MyException;
/**
 * MySQL数据库自定义连接池
 * @author 猫哥
 * @date 2017.2.6修改
 */
public class MySQLPool {
    private static LinkedList<Connection> pool = new LinkedList<Connection>(); 
    private static int maxCount=1;//最大连接数
    static{//初始化
        for(int i=0;i<maxCount;i++){
            MySQLHandler handler=new MySQLHandler();
            Connection connection = handler.buildConnection();
            pool.add(connection);
        }    
    }
    public static Connection getConnecton() throws MyException{
        if(pool.size()==0)//分配完了
        {
            throw new MyException(new Date(),"数据库连接池资源短缺，无连接可分配","数据库连接错误");
        }
        else{
            return pool.remove(0);//删除第一个对象并返回
        }
    }
    public static void release(Connection connection){//使用完的归还给池子
        pool.add(connection);
    }
}

package mysql;
import java.sql.*;//导入数据库相关类库
import java.util.Date;

import exception.MyException;
/**
 * MysqlHandler MySQL数据库管理类，使用数据库连接池
 * @author 猫哥
 * @date 2016.1.9
 * @modify 2016.2.6 MysqlHandler-MySQLHandler,use MyException
 */
public class MySQLHandler{   
    //依然是那熟悉的三个必备参数
    private Connection conn = null;
    private Statement stmt = null;
    private ResultSet rs = null;
    //建立数据库连接，此处仅用于提供原始连接，供放入池中使用
    public Connection buildConnection() {        
         String driver = "com.mysql.jdbc.Driver";
         String url = "jdbc:mysql://localhost:3306/homework?useUnicode=true&characterEncoding=utf-8";//数据库连接字符串
         String user = "root";
         String password = "Pass1234";
         try{   
            Class.forName(driver);//加载驱动程序
            conn=DriverManager.getConnection(url,user,password);
         }
         catch(Exception ex){
            //暂时不处理（未throw），加上日志模块后记在日志里
            new MyException(new Date(),ex.getMessage(),"数据库连接建立异常");
         }
        return conn;
    }
    //操作1，“增删改查”中，增、删、改都是执行sql语句，无需返回ResultSet结果集，所以设置为一个方法
    public int execute(String sql) throws MyException{
        try {
            if(conn==null)//未分配
                conn=MySQLPool.getConnecton();//用时从池中取，很爽快
            stmt=conn.createStatement();
            int affectedCount = stmt.executeUpdate(sql);//此处真正执行stmt定义的操作
            return affectedCount;//这个是收到影响的行数
        }
        catch (Exception ex) {
            throw new MyException(new Date(),ex.getMessage(),"数据库连接错误");
        }
    }
    //操作2，如果是查询，需返回结果集
    public ResultSet query(String sql)throws Exception{
        try{
            if(conn==null)//未分配
                conn=MySQLPool.getConnecton();//用时从池中取，很爽快
            stmt=conn.createStatement();
            rs = stmt.executeQuery(sql);//执行pstmt中定义的查询
            return rs;//将结果集返回 
        }
         catch (Exception ex) {
            throw new MyException(new Date(),ex.getMessage(),"数据库连接错误");
         }
    }
    //操作3，释放资源
    public void sayGoodbye(){
        if(rs!=null){//关闭结果集，这个不关闭也浪费
            try {
                rs.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        if(stmt!=null){//关闭Statement，不要浪费
            try {
                stmt.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        //此处注意，conn在池中管理，不用关闭，用完回归连接池继续使用
        MySQLPool.release(conn);
    }

}

最后就是具体的操作类了，暂时只有必要的（接口定义的），也没测试，之后编码有问题的话可能略加修改：

package operation;
import java.sql.ResultSet;
import exception.MyException;
import java.util.*;
import entity.*;
import mysql.*;
public class RoleOperation implements ObjectOperation{
    @Override
    public Object selectById(int id) {
        MySQLHandler hand=new MySQLHandler();
        ResultSet rs=null;
        Role one=new Role();//如果查询内容为空，则通过one.getRoleId()==0来判断即可
        try {
            //此处不要写select *，因为数据库将*转换为该表所有列名肯定需要浪费时间
            rs=hand.query("select role_id,role_name from system_role r where r.role_id='"+id+"'");
            while(rs.next()){
                one.setRoleId(rs.getInt("role_id"));
                one.setRoleName(rs.getString("role_name"));
            }
            hand.sayGoodbye();
            return one;
        } catch (Exception ex) {
            //对于数据库操作层面的异常，此时不予以向外抛出，记录在日志中分析即可
            //在设计的时候就要明确，什么类型异常要外抛，什么异常不抛出只记录
            new MyException(new Date(),ex.getMessage(),"RoleOperation.selectById异常");
            return null;//注意null和new Role()并不同！
        }
    }
    @Override
    public List selectAll() {//注意返回值null和list.size()==0的区别
        MySQLHandler hand=new MySQLHandler();
        ResultSet rs=null;
        ArrayList<Role> list=new ArrayList<Role>();//返回值
        try {
            rs=hand.query("select role_id,role_name from system_role r");
            while(rs.next()){
                Role one=new Role();//返回值中的一个
                one.setRoleId(rs.getInt("role_id"));
                one.setRoleName(rs.getString("role_name"));
                list.add(one);//添加到列表
            }
            hand.sayGoodbye();//释放资源
            return list;
        } catch (Exception ex) {
            new MyException(new Date(),ex.getMessage(),"RoleOperation.selectAll异常");
            return null;
        }
    }
    @Override
    public int add(Object obj) {
        Role one=(Role)obj;
        MySQLHandler hand=new MySQLHandler();
        try {
            int re=hand.execute("insert into system_role(role_name) values('"+one.getRoleName()+"')");
            hand.sayGoodbye();
            return re;
        } catch (Exception ex) {
            new MyException(new Date(),ex.getMessage(),"RoleOperation.add异常");
            return 0;
        }
    }
    @Override
    public int deleteById(String id) {
        MySQLHandler hand=new MySQLHandler();
        try {
            int re=hand.execute("delete from system_role where role_id='"+id+"'");
            hand.sayGoodbye();
            return re;
        } catch (Exception ex) {
            new MyException(new Date(),ex.getMessage(),"RoleOperation.deleteById异常");
            return 0;
        }
    }
    @Override
    public int update(Object obj) {
        Role one=(Role)obj;
        MySQLHandler hand=new MySQLHandler();
        try {
            int re=hand.execute("update system_role set role_name='"+one.getRoleName()
                    +"' where role_id='"+one.getRoleId()+"'");
            hand.sayGoodbye();
            return re;
        } catch (Exception ex) {
            new MyException(new Date(),ex.getMessage(),"RoleOperation.update异常");
            return 0;
        }
    }
}

package operation;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import mysql.MySQLHandler;
import entity.*;
import exception.MyException;
public class UserOperation implements ObjectOperation{
    @Override
    public Object selectById(int id) {
        MySQLHandler hand=new MySQLHandler();
        ResultSet rs=null;
        User one=new User();//如果查询内容为空，则通过one.getUserId()==0来判断即可
        try {
            //此处不要写select *，因为数据库将*转换为该表所有列名肯定需要浪费时间
            rs=hand.query("select user_id,user_name,user_password,role_id,role_name from "
                    +"system_user u,system_role r where u.user_id='"+id+"' and u.user_role=r.role_id");
            while(rs.next()){
                one.setUserId(rs.getInt("User_id"));
                one.setUserName(rs.getString("User_name"));
                one.setUserPassword(rs.getString("user_password"));
                Role role=new Role();
                role.setRoleId(rs.getInt("role_id"));
                role.setRoleName(rs.getString("role_name"));
                one.setUserRole(role);
            }
            hand.sayGoodbye();
            return one;
        } catch (Exception ex) {
            //对于数据库操作层面的异常，此时不予以向外抛出，记录在日志中分析即可
            //在设计的时候就要明确，什么类型异常要外抛，什么异常不抛出只记录
            new MyException(new Date(),ex.getMessage(),"UserOperation.selectById异常");
            return null;//注意null和new User()并不同！
        }
    }
    @Override
    public List selectAll() {//注意返回值null和list.size()==0的区别
        MySQLHandler hand=new MySQLHandler();
        ResultSet rs=null;
        ArrayList<User> list=new ArrayList<User>();//返回值
        try {
            rs=hand.query("select user_id,user_name,user_password,role_id,role_name from "
                    +"system_user u,system_role r where u.user_role=r.role_id");
            while(rs.next()){
                User one=new User();//返回值中的一个
                one.setUserId(rs.getInt("User_id"));
                one.setUserName(rs.getString("User_name"));
                one.setUserPassword(rs.getString("user_password"));
                Role role=new Role();
                role.setRoleId(rs.getInt("role_id"));
                role.setRoleName(rs.getString("role_name"));
                one.setUserRole(role);
                list.add(one);//添加到列表
            }
            hand.sayGoodbye();//释放资源
            return list;
        } catch (Exception ex) {
            new MyException(new Date(),ex.getMessage(),"UserOperation.selectAll异常");
            return null;
        }
    }
    /*需要注意添加用户时，我们只用到了关联表的id*/
    @Override
    public int add(Object obj) {
        User one=(User)obj;
        MySQLHandler hand=new MySQLHandler();
        try {
            int re=hand.execute("insert into system_User(User_name,user_password,user_role)"
                    +" values('"+one.getUserName()+"','"+one.getUserPassword()+"','"+one.getUserRole().getRoleId()+"')");
            hand.sayGoodbye();
            return re;
        } catch (Exception ex) {
            new MyException(new Date(),ex.getMessage(),"UserOperation.add异常");
            return 0;
        }
    }
    /*这个方法我是从RoleOperation中拷贝过来的，然后使用User替换了Role，此时定睛一看，竟无需改变*/
    @Override
    public int deleteById(String id) {
        MySQLHandler hand=new MySQLHandler();
        try {
            int re=hand.execute("delete from system_User where User_id='"+id+"'");
            hand.sayGoodbye();
            return re;
        } catch (Exception ex) {
            new MyException(new Date(),ex.getMessage(),"UserOperation.deleteById异常");
            return 0;
        }
    }
    /*此处需要注意修改user_role的逻辑，如果设计的是修改用户信息时同步修改角色，可以就如下写代码
     而如果修改用户信息不修改角色，修改角色的功能是单独的菜单，那么可单独增加updateUserRole方法
     猫哥建议直接在update里都写好，如果有区分的功能菜单，直接在在command命令层写不同的代码即可*/
    @Override
    public int update(Object obj) {
        User one=(User)obj;
        MySQLHandler hand=new MySQLHandler();
        try {
            int re=hand.execute("update system_User set User_name='"+one.getUserName()
                    +"',user_password='"+one.getUserPassword()+"',user_role='"+one.getUserRole().getRoleId()
                    +"' where User_id='"+one.getUserId()+"'");
            hand.sayGoodbye();
            return re;
        } catch (Exception ex) {
            new MyException(new Date(),ex.getMessage(),"UserOperation.update异常");
            return 0;
        }
    }
}

因其他几个operation类的结构和写法，完全可以由RoleOperation和UserOperation代表，故此处不再一一粘贴代码，如有需要，可留言获取。