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數據庫設計