Meeting Management System 会议管理系统

此项目涉及：【未涉及框架】

前端：HTML + CSS + JS后端：JSP + Servlet + JDBC数据库：Oracle开发工具：Eclipse + 数据库第三方软件：PL / SQL Developerjar包分层 VOUtil DAOServiceServletFilterListener

各层名字只是随意取的

Name别名用途VOBean，Domain，Entity实体类Util/工具/功能层DAO/数据操作层Service/业务逻辑层Servlet/控制层Filter/过滤器Listener/监听器

其他

除了必备的专业知识，你还应该会

Alt键+/ 的快捷方式（真的还有很多人不知道……）代码查错Java 3.0 与以往版本相比之下的优势

前期准备

数据库建表：

部门表meetingdept；员工表meetingemp；会议室表meetingroom

涉及知识点回忆

JDBC

数据库连接方法JDBC API Connection接口Statement接口ResultSet接口预处理语句 PreparedStatement对象连接池与数据源

代码区

一：VO： 1. Dept类

public class Dept { //声明属性 private int deptid; private String deptname; //set get public int getDeptid() { return deptid; } public void setDeptid(int deptid) { this.deptid = deptid; } public String getDeptname() { return deptname; } public void setDeptname(String deptname) { this.deptname = deptname; } //全参构造器 public Dept(int deptid, String deptname) { super(); this.deptid = deptid; this.deptname = deptname; } //无参构造器 public Dept() { super(); } //提供tostring @Override public String toString() { return "Dept [deptid=" + deptid + ", deptname=" + deptname + "]"; } }

2.Emp类

public class Emp { //声明属性 private int empId; private String empName; private String username; private String password; private String phone; private String email; private int deptid; private String status; //提供set get 构造器 toString public int getEmpId() { return empId; } public void setEmpId(int empId) { this.empId = empId; } public String getEmpName() { return empName; } public void setEmpName(String empName) { this.empName = empName; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public int getDeptid() { return deptid; } public void setDeptid(int deptid) { this.deptid = deptid; } public String getStatus() { return status; } public void setStatus(String status) { this.status = status; } public Emp(int empId, String empName, String username, String password, String phone, String email, int deptid, String status) { super(); this.empId = empId; this.empName = empName; this.username = username; this.password = password; this.phone = phone; this.email = email; this.deptid = deptid; this.status = status; } public Emp() { super(); } @Override public String toString() { return "Emp [empId=" + empId + ", empName=" + empName + ", username=" + username + ", password=" + password + ", phone=" + phone + ", email=" + email + ", deptid=" + deptid + ", status=" + status + "]"; } }

3.MeetingRoom类

public class MeetingRoom { private int roomId; private String roomNum; private String roomName; private int capacity; public int getRoomId() { return roomId; } public void setRoomId(int roomId) { this.roomId = roomId; } public String getRoomNum() { return roomNum; } public void setRoomNum(String roomNum) { this.roomNum = roomNum; } public String getRoomName() { return roomName; } public void setRoomName(String roomName) { this.roomName = roomName; } public int getCapacity() { return capacity; } public void setCapacity(int capacity) { this.capacity = capacity; } public String getStatus() { return status; } public void setStatus(String status) { this.status = status; } private String status; }

二：Util：DbUtil→打开和关闭数据库

import java.sql.Connection; import java.sql.DriverManager; public class DbUtil { static Connection conn = null; public static void main(String[] args) { getConn(); } //连接数据库的方法 public static Connection getConn(){ //加载数据库驱动 try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger"); //第一个参数字符串内容固定 } catch (Exception e) { e.printStackTrace(); //驱动命令 } return conn; } }

三：DAO 1.1 接口DeptDao

import java.util.List; import com.oameeting.vo.Dept; public interface DeptDao { public List<Dept> selectAll(); //返回用户信息集合 //添加部门 ：不需要参数String id， as： 序列帮忙管理id public void insertDept(String name); public void deleteDept(String id); }

1.2 实现接口DeptDaoImpl

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.oameeting.util.DbUtil; import com.oameeting.vo.Dept; public class DeptDaoImpl implements DeptDao{ Connection conn = null; PreparedStatement ptmt = null; ResultSet rs = null; List<Dept> list = null; //集合对象 Dept dept = null; @Override public List<Dept> selectAll() { list = new ArrayList<Dept>(); //查询部门全部 conn = DbUtil.getConn(); String sql = "select * from meetingdept"; try { ptmt = conn.prepareStatement(sql); //返回值复制给预编译对象 rs = ptmt.executeQuery(); while(rs.next()){ dept = new Dept(); dept.setDeptid(rs.getInt(1)); dept.setDeptname(rs.getString(2)); list.add(dept); } } catch (SQLException e) { e.printStackTrace(); } return list; } //测试:手动代码添加部门 成功！！！ public static void main(String[] args) { DeptDaoImpl dao = new DeptDaoImpl(); dao.insertDept("boss部门"); List<Dept> list = dao.selectAll(); for (Dept dept : list) { System.out.println(dept); } } //增加一个部门的具体实现（JDBC） @Override public void insertDept( String name) { conn = DbUtil.getConn(); String sql = "insert into meetingdept values(dept_seq.nextval,?)"; try { ptmt = conn.prepareStatement(sql);//预编译 ptmt.setString(1, name);//给问号赋值 ptmt.executeUpdate();//执行sql语句 } catch (SQLException e) { e.printStackTrace(); } } @Override public void deleteDept(String id) { conn = DbUtil.getConn(); String sql = "delete from meetingdept where deptid = ?"; try { ptmt = conn.prepareStatement(sql); ptmt.setString(1, id); ptmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } }

2.1 EmpDao

import java.util.List; import com.oameeting.vo.Emp; public interface EmpDao { public Emp selectByName(String name);//通过name查找用户是否存在 public void insertEmp(Emp emp); //增加一个数据 public List<Emp> selectByStatus();//查找所有等待审批的员工 public void updateStatus(String status,String id) ;//注册审批：修改状态值 public List<Emp> selectByNUS(String name,String username,String status);//要返回集合 public List<Emp> selectByNUSOnePage(String name,String username,String status,int start,int end);//分页查询 }

2.2 EmpDaoImpl

getConnection得到连接对象Connection 创建Connection接口的Statement对象 调用Statement对象，来向数据库发送SQL语句，实现对数据库的操作 如果Statement对象用于查询，就调用它的executeQuery()返回的ResultSet是不开滚，不可更新的 预编译PreparedStatement，继承于Statement，用Connection的下列方法创建PreparedStatement对象 通过setXxx()对占位符进行赋值

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.oameeting.servlet.insertDeptServlet; import com.oameeting.util.DbUtil; import com.oameeting.vo.Emp; public class EmpDaoImpl implements EmpDao{ Connection conn = null; PreparedStatement ptmt = null; ResultSet rs = null; Emp emp = null; //登录 通过名字name去找emp是否存在 @Override public Emp selectByName(String name) { conn = DbUtil.getConn(); String sql = "select empid,empname,username,password,phone,email,deptid,status " + " from meetingemp where username = ?"; try { ptmt = conn.prepareStatement(sql); //给问号赋值 ptmt.setString(1, name); //执行sql语句 rs = ptmt.executeQuery(); if(rs.next()){ emp = new Emp();//实例化emp对象 //将rs的内容全部赋值给emp对象，用emp对象去交互 //rs.getInt(1) 获取empid的值 //rs.getString(2) emp.setEmpId(rs.getInt(1)); emp.setEmpName(rs.getString(2)); emp.setUsername(rs.getString(3)); emp.setPassword(rs.getString(4)); emp.setPhone(rs.getString(5)); emp.setEmail(rs.getString(6)); emp.setDeptid(rs.getInt(7)); emp.setStatus(rs.getString(8)); } } catch (SQLException e) { e.printStackTrace(); }finally{ try { rs.close(); ptmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return emp; } //测试！！！！成功 public static void main(String[] args) { EmpDaoImpl dao = new EmpDaoImpl(); //搜索员工测试：查询状态值=2的员工 //List< Emp> list = dao.selectByNUS(null, null,"2"); List< Emp> list = dao.selectByNUSOnePage(null, null,null,1,3); for(Emp emp : list){ System.out.println(emp); } // Emp emp = new Emp(); //手动添加用户 emp.setEmpName("jerry"); dao.insertEmp(emp); System.out.println(dao.selectByName("tom")); //System.out.println(dao.selectByName("")); } //新增 @Override public void insertEmp(Emp emp) { // TODO Auto-generated method stub // conn = DbUtil.getConn(); String sql = "insert into meetingemp values(emp_seq.nextval,?,?,?,?,?,?,?)"; //对应数据库中meetingemp表的字段数 try { ptmt = conn.prepareStatement(sql); //给问号赋值， ptmt.setString(1, emp.getEmpName()); //第二个字段 ptmt.setString(2, emp.getUsername()); ptmt.setString(3, emp.getPassword()); ptmt.setString(4, emp.getPhone()); ptmt.setString(5, emp.getEmail()); ptmt.setInt(6,emp.getDeptid() ); ptmt.setString(7, "2"); //注册后要先审核，LoginServlet设置了2表示正在审核 ptmt.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } @Override public List<Emp> selectByStatus() { // TODO Auto-generated method stub List<Emp> list = new ArrayList<Emp>(); //复制 public Emp selectByName(String name) 代码，再进行修改 conn = DbUtil.getConn(); String sql = "select empid,empname,username,password,phone,email,deptid,status " + " from meetingemp where status = ?"; try { ptmt = conn.prepareStatement(sql); //给问号赋值 ptmt.setString(1,"2"); //状态status=2的为正在审核状态 //执行sql语句 rs = ptmt.executeQuery(); while(rs.next()){ emp = new Emp();//实例化emp对象 //将rs的内容全部赋值给emp对象，用emp对象去交互 //rs.getInt(1) 获取empid的值 emp.setEmpId(rs.getInt(1)); emp.setEmpName(rs.getString(2)); emp.setUsername(rs.getString(3)); emp.setPassword(rs.getString(4)); emp.setPhone(rs.getString(5)); emp.setEmail(rs.getString(6)); emp.setDeptid(rs.getInt(7)); emp.setStatus(rs.getString(8)); list.add(emp); } } catch (SQLException e) { e.printStackTrace(); }finally{ try { rs.close(); ptmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } @Override public void updateStatus(String status,String id) { // TODO Auto-generated method stub conn = DbUtil.getConn(); String sql = "update meetingemp set status = ? where empid = ?"; try { ptmt = conn.prepareStatement(sql); //给问号赋值 ptmt.setString(1,status); ptmt.setString(2,id); ptmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } //通过名字，账户名，状态来查询,用户可只输入一个查询条件 @Override public List<Emp> selectByNUS(String name, String username, String status) { // TODO Auto-generated method stub List<Emp> list =new ArrayList<Emp>(); conn = DbUtil.getConn(); //处理动态sql语句 String namesql = null; String usernamesql = null; String statussql =null; if(name == null || " ".equals(name)){ namesql = "";//如果外界传入的是空值，就用空串""代替 }else{ namesql = "and empname = ' "+name+ " ' "; } if(username == null || " ".equals(username)){ usernamesql = ""; }else{ usernamesql = " and username = ' "+username+" ' "; } if(status == null || " ".equals(status)){ statussql = ""; }else{ statussql = " and status = ' "+status+ " ' "; } //多个查询条件;where 1=1 为了sql语句条件拼接 String sql = "select * from meetingemp where 1 = 1" + namesql + usernamesql +statussql; try {//执行sql语句 ptmt = conn.prepareStatement(sql); rs = ptmt.executeQuery(); while(rs.next()){ emp = new Emp(); //将rs的内容全部赋值给emp对象，用emp对象去交互 emp.setEmpId(rs.getInt(1)); emp.setEmpName(rs.getString(2)); emp.setUsername(rs.getString(3)); emp.setPassword(rs.getString(4)); emp.setPhone(rs.getString(5)); emp.setEmail(rs.getString(6)); emp.setDeptid(rs.getInt(7)); emp.setStatus(rs.getString(8)); list.add(emp); } } catch (SQLException e) { e.printStackTrace(); }finally{ try {//关闭数据库的链接 rs.close(); ptmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } @Override public List<Emp> selectByNUSOnePage(String name, String username, String status, int start, int end) {//通过名字，账户名，状态来查询,用户可只输入一个查询条件 // TODO Auto-generated method stub List<Emp> list =new ArrayList<Emp>(); conn = DbUtil.getConn(); //**处理动态sql语句 String namesql = null; String usernamesql = null; String statussql =null; if(name == null || " ".equals(name)){ namesql = "";//如果外界传入的是空值，就用空串""代替 }else{ namesql = "and empname = ' "+name+ " ' "; } if(username == null || " ".equals(username)){ usernamesql = ""; }else{ usernamesql = " and username = ' "+username+" ' "; } if(status == null || " ".equals(status)){ statussql = ""; }else{ statussql = " and status = ' "+status+ " ' "; } //**[从里往外看，嵌套] String sql = "select * from ( select e.* , rownum ru " + "from ( select * from (select * from meetingemp" + " where 1 = 1 " + namesql + usernamesql +statussql +")) e ) " + "where ru between " +start + " and "+ end ; try { //执行sql语句 ptmt = conn.prepareStatement(sql); rs = ptmt.executeQuery(); while(rs.next()){ emp = new Emp(); //将rs的内容全部赋值给emp对象，用emp对象去交互 emp.setEmpId(rs.getInt(1)); emp.setEmpName(rs.getString(2)); emp.setUsername(rs.getString(3)); emp.setPassword(rs.getString(4)); emp.setPhone(rs.getString(5)); emp.setEmail(rs.getString(6)); emp.setDeptid(rs.getInt(7)); emp.setStatus(rs.getString(8)); list.add(emp); } } catch (SQLException e) { e.printStackTrace(); }finally{ try { //关闭数据库的链接 rs.close(); ptmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } }

3.1 MeetingRoomDao

import java.util.List; import com.neusoft.oameeting.vo.MeetingRoom; public interface MeetingRoomDao { public void insertMeetingRoom(MeetingRoom room);//添加会议 public List<MeetingRoom> selectAll(); public void updateMeetingRoom(MeetingRoom room); }

3.2

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.oameeting.util.DbUtil; import com.oameeting.vo.Dept; import com.oameeting.vo.MeetingRoom; public class MeetingRoomDaoImpl implements MeetingRoomDao{ Connection conn = null; PreparedStatement ptmt = null; ResultSet rs = null; MeetingRoom room= null; List<MeetingRoom> list =null; @Override public void insertMeetingRoom(MeetingRoom room) { conn = DbUtil.getConn(); String sql = "insert into meetingroom values(meetingroom_seq.nextval,?,?,?,?)"; //对应数据库中meetingemp表的字段数 try { ptmt = conn.prepareStatement(sql); ptmt.setString(1, room.getRoomNum()); //第二个字段 ptmt.setString(2, room.getRoomName()); ptmt.setInt(3, room.getCapacity()); ptmt.setString(4, room.getStatus()); ptmt.executeUpdate(); //as insert } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } @Override public List<MeetingRoom> selectAll() { list = new ArrayList<MeetingRoom>(); //查询会议室全部 conn = DbUtil.getConn(); String sql = "select * from meetingroom"; try { ptmt = conn.prepareStatement(sql); rs = ptmt.executeQuery(); while(rs.next()){ room = new MeetingRoom(); room.setRoomId(rs.getInt(1)); room. setRoomNum(rs.getString(2)); room. setRoomName(rs.getString(3)); room. setCapacity(rs.getInt(4)); room. setStatus(rs.getString(5)); list.add(room); } } catch (SQLException e) { e.printStackTrace(); } return list; } //测试:手动代码添加部门 failed！！！ public static void main(String[] args) { MeetingRoomDaoImpl dao = new MeetingRoomDaoImpl(); MeetingRoom rooms = new MeetingRoom(); rooms.setRoomId(5); rooms.setRoomName("第11会议室"); dao.updateMeetingRoom(rooms); } @Override public void updateMeetingRoom(MeetingRoom room) { // TODO Auto-generated method stub conn = DbUtil.getConn(); String sql = "update meetingroom set roomnum=?,roomname=?,capacity=?,status=? where roomid= ?"; //对应数据库中meetingemp表的字段数 System.out.println("++++++++++++++++++++++");//console test try { ptmt = conn.prepareStatement(sql); ptmt.setString(1, room.getRoomNum()); ptmt.setString(2, room.getRoomName()); ptmt.setInt(3, room.getCapacity()); ptmt.setString(4, room.getStatus()); ptmt.setInt(5,room.getRoomId()); ptmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } }