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

2019-11-06 07:46:28

字体：大中小

来源：转载

供稿：网友

本篇实现数据库相关的实体类(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代表，故此处不再一一粘贴代码，如有需要，可留言获取。

上一篇：JSP中文乱码问题的解决方案

下一篇：JSP+Servlet培训班作业管理系统[7] -Mysql数据库设计