package com.dao; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import com.entity.Message1; import com.entity.Message2; import com.entity.Message3; import com.util.DBUtil; public class MessageDao { //登录 public int login(String username, String password,String shenfen) { String sql=null; if(shenfen.equals("student")) { sql="select * from d2 where num='"+username+"'and password='"+password+"'"; }else if(shenfen.equals("teacher")) { sql="select * from d1 where num='"+username+"'and password='"+password+"'"; }else if(shenfen.equals("manage")) { sql="select * from d4 where username='"+username+"'and password='"+password+"'"; } Connection conn=DBUtil.getConn(); Statement st=null; ResultSet rs=null; int row=0; try { st=conn.createStatement(); rs=st.executeQuery(sql); if (rs.next()) { row=1; }else { row=0; } } catch (Exception e) { // TODO: handle exception } return row; } //添加 public boolean add1(Message1 message) { String sql = "insert into d1(num,tname,tsex,txueyuan,tzhicheng,password)"+"values('" + message.getNum() + "','" + message.getTname() + "','" + message.getTsex() + "','" + message.getTxueyuan() + "','" + message.getTzhicheng() + "','"+message.getPassword()+"')"; // 创建数据库链接 Connection conn = DBUtil.getConn(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); state.executeUpdate(sql); } catch (Exception e) { e.printStackTrace(); } finally { // 关闭连接 DBUtil.close(state, conn); } if (a > 0) { f = true; } return f; } public boolean add2(Message2 message) { String sql = "insert into d2(num,sname,ssex,sclass,smajor,password)"+"values('" + message.getNum() + "','" + message.getSname() + "','" + message.getSsex() + "','" + message.getSclass() + "','" + message.getSmajor() + "','"+message.getPassword()+"')"; // 创建数据库链接 Connection conn = DBUtil.getConn(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); state.executeUpdate(sql); } catch (Exception e) { e.printStackTrace(); } finally { // 关闭连接 DBUtil.close(state, conn); } if (a > 0) { f = true; } return f; } public boolean add3(Message3 message) { String sql = "insert into d3(cnum,cname,cpnum,cteacher)"+"values('" + message.getCnum() + "','" + message.getCname() + "','" + message.getCpnum() + "','" + message.getCteacher() + "')"; // 创建数据库链接 Connection conn = DBUtil.getConn(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); state.executeUpdate(sql); } catch (Exception e) { e.printStackTrace(); } finally { // 关闭连接 DBUtil.close(state, conn); } if (a > 0) { f = true; } return f; } public static boolean num1(String num) { boolean flag = false; String sql = "select * from d1 where num = '" + num + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { flag = true; } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return flag; } public static boolean num2(String num) { boolean flag = false; String sql = "select * from d2 where num = '" + num + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { flag = true; } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return flag; } public static boolean num3(String cnum) { boolean flag = false; String sql = "select * from d3 where cnum = '" + cnum + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { flag = true; } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return flag; } //查询 public Message1 search(String name) { String sql = "select * from d1 where name = '" + name + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; Message1 bean=null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { int id2=rs.getInt("id"); String name2 = rs.getString("name"); String sex2 = rs.getString("sex"); String nation2 = rs.getString("nation"); String date2 = rs.getString("date"); String age2=rs.getString("age"); String politics2=rs.getString("politics"); /*bean = new Message1(id2,name2,sex2,nation2,date2,age2,politics2);*/ } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return bean; } //删除 public boolean delete(String name){ String sql="delete from d1 where name='" + name + "'"; Connection conn = DBUtil.getConn(); Statement state = null; int a = 0; boolean f = false; try { state = conn.createStatement(); a = state.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(state, conn); } if (a > 0) { f = true; } return f; } //通过姓名查找 public static Message1 getMessageBynum1(String num) { String sql = "select * from d1 where num ='" + num + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; Message1 message = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { String num1 = rs.getString("num"); String tname = rs.getString("tname"); String tsex = rs.getString("tsex"); String txueyuan=rs.getString("txueyuan"); String tzhicheng=rs.getString("tzhicheng"); String password=rs.getString("password"); message = new Message1(num1,tname,tsex,txueyuan,tzhicheng,password); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return message; } public static Message2 getMessageBynum2(String num) { String sql = "select * from d2 where num ='" + num + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; Message2 message = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { String num1 = rs.getString("num"); String tname = rs.getString("sname"); String tsex = rs.getString("ssex"); String txueyuan=rs.getString("sclass"); String tzhicheng=rs.getString("smajor"); String password=rs.getString("password"); message = new Message2(num1,tname,tsex,txueyuan,tzhicheng,password); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return message; } public static String getMessageBynum3(String num) { String sql = "select * from d1 where num ='" + num + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; Message1 message = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { String num1 = rs.getString("num"); String tname = rs.getString("tname"); String tsex = rs.getString("tsex"); String txueyuan=rs.getString("txueyuan"); String tzhicheng=rs.getString("tzhicheng"); String password=rs.getString("password"); message = new Message1(num1,tname,tsex,txueyuan,tzhicheng,password); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return message.getTname(); } //修改 public static boolean update12(Message1 message,String name) { String sql = "update d1 set num='" + message.getNum() + "', tname='" + message.getTname()+"',tsex='"+message.getTsex()+"',txueyuan='"+message.getTxueyuan()+"', tzhicheng='" + message.getTzhicheng() + "', password='" + message.getPassword() + "'where num ='" + name + "'"; Connection conn = DBUtil.getConn(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); a = state.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(state, conn); } if (a > 0) { f = true; } return f; } public static boolean update22(Message2 message,String name) { String sql = "update d2 set num='" + message.getNum() + "', sname='" + message.getSname()+"',ssex='"+message.getSsex()+"',Sclass='"+message.getSclass()+"', Smajor='" + message.getSmajor() + "', password='" + message.getPassword() + "'where num ='" + name + "'"; Connection conn = DBUtil.getConn(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); a = state.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(state, conn); } if (a > 0) { f = true; } return f; } //显示全部 public List<Message3> list() { String sql = "select * from d3"; List<Message3> list = new ArrayList<>(); Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { Message3 bean = null; String cnum=rs.getString("cnum"); String cname = rs.getString("cname"); String cpnum = rs.getString("cpnum"); String cteacher = rs.getString("cteacher"); bean =new Message3(cnum,cname,cpnum,cteacher); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return list; } }
package com.entity; //tnum tname tsex txueyaun tzhicheng public class Message1 { private String num; private String tname; private String tsex; private String txueyuan; private String tzhicheng; private String password; public String getNum() { return num; } public void setNum(String num) { this.num = num; } public String getTname() { return tname; } public void setTname(String tname) { this.tname = tname; } public String getTsex() { return tsex; } public void setTsex(String tsex) { this.tsex = tsex; } public String getTxueyuan() { return txueyuan; } public void setTxueyuan(String txueyuan) { this.txueyuan = txueyuan; } public String getTzhicheng() { return tzhicheng; } public void setTzhicheng(String tzhicheng) { this.tzhicheng = tzhicheng; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public Message1(String num, String tname, String tsex, String txueyuan, String tzhicheng, String password) { super(); this.num = num; this.tname = tname; this.tsex = tsex; this.txueyuan = txueyuan; this.tzhicheng = tzhicheng; this.password = password; } public Message1() { super(); // TODO 自动生成的构造函数存根 } }
package com.entity; public class Message2 { //num sname ssex sclass smajor private String num; private String sname; private String ssex; private String sclass; private String smajor; private String password; public String getNum() { return num; } public void setNum(String num) { this.num = num; } public String getSname() { return sname; } public void setSname(String sname) { this.sname = sname; } public String getSsex() { return ssex; } public void setSsex(String ssex) { this.ssex = ssex; } public String getSclass() { return sclass; } public void setSclass(String sclass) { this.sclass = sclass; } public String getSmajor() { return smajor; } public void setSmajor(String smajor) { this.smajor = smajor; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public Message2(String num, String sname, String ssex, String sclass, String smajor, String password) { super(); this.num = num; this.sname = sname; this.ssex = ssex; this.sclass = sclass; this.smajor = smajor; this.password = password; } }
package com.entity; public class Message3 { public String cnum; public String cname; public String cpnum; public String cteacher; public String getCnum() { return cnum; } public void setCnum(String cnum) { this.cnum = cnum; } public String getCname() { return cname; } public void setCname(String cname) { this.cname = cname; } public String getCpnum() { return cpnum; } public void setCpnum(String cpnum) { this.cpnum = cpnum; } public String getCteacher() { return cteacher; } public void setCteacher(String cteacher) { this.cteacher = cteacher; } public Message3(String cnum, String cname, String cpnum, String cteacher) { super(); this.cnum = cnum; this.cname = cname; this.cpnum = cpnum; this.cteacher = cteacher; } }
package com.service; import java.util.List; import com.dao.MessageDao; import com.entity.Message1; import com.entity.Message2; import com.entity.Message3; //服务层 public class MessageService { MessageDao mDao = new MessageDao(); //登录 public int login(String username,String password,String shenfen) { return mDao.login(username,password,shenfen); } //添加 public boolean add1(Message1 message) { boolean f = false; if(!mDao.num1(message.getNum())) { mDao.add1(message); f = true; } return f; } public boolean add2(Message2 message) { boolean f = false; if(!mDao.num2(message.getNum())) { mDao.add2(message); f = true; } return f; } public boolean add3(Message3 message) { boolean f = false; if(!mDao.num3(message.getCnum())) { mDao.add3(message); f = true; } return f; } //查找 public Message1 search(String name) { return mDao.search(name); } //删除 public boolean delete(String name) { mDao.delete(name); return true; } public Message1 getMessageBynum(String num) { return mDao.getMessageBynum1(num); } //修改 public void update(Message1 message,String num) { mDao.update12(message,num); } //显示所有 public List<Message3> list() { return mDao.list(); } }
package com.servlet; import java.io.IOException; import java.util.ArrayList; import java.util.List; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.Cookie; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import com.dao.MessageDao; import com.entity.Message1; import com.entity.Message2; import com.entity.Message3; import com.service.MessageService; @WebServlet("/MessageServlet") public class MessageServlet extends HttpServlet { static String wen=null; static String mo=null; private static final long serialVersionUID = 1L; MessageService service = new MessageService(); /** * 方法选择 */ protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String method = req.getParameter("method"); if ("add1".equals(method)) { add1(req, resp); }else if ("login".contentEquals(method)) { login(req, resp); }else if ("add2".equals(method)) { add2(req, resp); }else if ("add3".equals(method)) { add3(req, resp); }else if ("update11".equals(method)) { update11(req, resp); }else if ("update12".equals(method)) { update12(req, resp); }else if ("update21".equals(method)) { update21(req, resp); }else if ("update22".equals(method)) { update22(req, resp); }else if ("list".equals(method)) { list(req, resp); } } //登录 private void login(HttpServletRequest req,HttpServletResponse resp) throws IOException, ServletException { req.setCharacterEncoding("utf-8"); String username=req.getParameter("username"); String password=req.getParameter("password"); String shenfen=req.getParameter("shenfen"); int message=service.login(username,password,shenfen); if(message == 0) { req.setAttribute("message", "帐号或密码错误!"); req.getRequestDispatcher("login.jsp").forward(req,resp); } else { Cookie cookie=new Cookie("username", username); cookie.setPath("/"); cookie.setMaxAge(60*60*24); resp.addCookie(cookie); req.setAttribute("message", "登陆成功"); req.getRequestDispatcher(shenfen+".jsp").forward(req,resp); } } //添加 private void add1(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException { req.setCharacterEncoding("utf-8"); //获取数据num tname tsex txueyaun tzhicheng String num = req.getParameter("num"); String tname = req.getParameter("tname"); String tsex = req.getParameter("tsex"); String txueyuan = req.getParameter("txueyuan"); String tzhicheng = req.getParameter("tzhicheng"); String password = req.getParameter("password"); Message1 message = new Message1(num,tname,tsex,txueyuan,tzhicheng,password); //添加后消息显示 if(service.add1(message)) { req.setAttribute("message", "添加成功"); req.getRequestDispatcher("manage.jsp").forward(req,resp); } else { req.setAttribute("message", "工号重复,请重新录入"); req.getRequestDispatcher("add1.jsp").forward(req,resp); } } private void add2(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException { req.setCharacterEncoding("utf-8"); //snum sname ssex sclass smajor String num = req.getParameter("num"); String sname = req.getParameter("sname"); String ssex = req.getParameter("ssex"); String sclass = req.getParameter("sclass"); String smajor = req.getParameter("smajor"); String password = req.getParameter("password"); Message2 message = new Message2(num,sname,ssex,sclass,smajor,password); //添加后消息显示 if(service.add2(message)) { req.setAttribute("message", "添加成功"); req.getRequestDispatcher("manage.jsp").forward(req,resp); } else { req.setAttribute("message", "学号重复,请重新录入"); req.getRequestDispatcher("add2.jsp").forward(req,resp); } } private void add3(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException { req.setCharacterEncoding("utf-8"); //snum sname ssex sclass smajor String cnum = req.getParameter("cnum"); String cname = req.getParameter("cname"); String cpnum = req.getParameter("cpnum"); String cteacher = req.getParameter("cteacher"); Message3 message = new Message3(cnum,cname,cpnum,cteacher); //添加后消息显示 if(service.add3(message)) { req.setAttribute("message", "添加成功"); req.getRequestDispatcher("teacher.jsp").forward(req,resp); } else { req.setAttribute("message", "课程编号重复,请重新录入"); req.getRequestDispatcher("add3.jsp").forward(req,resp); } } //查询 private void search(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String name = req.getParameter("name"); Message1 message = service.search(name); if(message == null) { req.setAttribute("message", "查无此用户!"); req.getRequestDispatcher("search.jsp").forward(req,resp); } else { req.setAttribute("message", message); req.getRequestDispatcher("search1.jsp").forward(req,resp); } } private void search1(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String name = req.getParameter("name"); Message1 message = service.search(name); if(message == null) { req.setAttribute("message", "查无此用户!"); req.getRequestDispatcher("search.jsp").forward(req,resp); } else { req.setAttribute("message", message); req.getRequestDispatcher("search2.jsp").forward(req,resp); } } //删除 private void delete(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); //String name=req.getParameter("name"); //if(MessageDao.name(name)) service.delete(mo); req.setAttribute("message", "删除成功!"); req.getRequestDispatcher("delete.jsp").forward(req,resp); } //通过姓名查找后删除 /* private void getMessageByname(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String name = req.getParameter("name"); Message1 message = service.getMessageByname(name); mo=name; if(message==null) { req.setAttribute("message", "未找到该用户"); req.getRequestDispatcher("delete.jsp").forward(req, resp); } else { req.setAttribute("message", message); req.getRequestDispatcher("delete1.jsp").forward(req,resp); } }*/ private void update11(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String num = req.getParameter("num"); Message1 message = MessageDao.getMessageBynum1(num); wen=num; if(message==null) { req.setAttribute("message","未找到该用户"); req.getRequestDispatcher("teacher.jsp").forward(req, resp); } else { req.setAttribute("message",message); req.getRequestDispatcher("update12.jsp").forward(req,resp); } } private void update21(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String num = req.getParameter("num"); Message2 message = MessageDao.getMessageBynum2(num); wen=num; if(message==null) { req.setAttribute("message","未找到该用户"); req.getRequestDispatcher("update21.jsp").forward(req, resp); } else { req.setAttribute("message",message); req.getRequestDispatcher("update22.jsp").forward(req,resp); } } //修改 private void update12(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String num3 = req.getParameter("num"); String tname3 = req.getParameter("tname"); String tsex3 = req.getParameter("tsex"); String txueyuan3 = req.getParameter("txueyuan"); String tzhicheng3 = req.getParameter("tzhicheng"); String password3 = req.getParameter("password"); Message1 message = new Message1(num3,tname3,tsex3,txueyuan3,tzhicheng3,password3); if(MessageDao.update12(message,wen)) { req.setAttribute("message","修改成功"); req.getRequestDispatcher("update11.jsp").forward(req,resp); } } private void update22(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String num3 = req.getParameter("num"); String sname3 = req.getParameter("sname"); String ssex3 = req.getParameter("ssex"); String sclass3 = req.getParameter("sclass"); String smajor3 = req.getParameter("smajor"); String password3 = req.getParameter("password"); Message2 message = new Message2(num3,sname3,ssex3,sclass3,smajor3,password3); if(MessageDao.update22(message,wen)) { req.setAttribute("message","修改成功"); req.getRequestDispatcher("update21.jsp").forward(req,resp); } } //显示全部 private void list(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); List<Message3> messages = service.list(); req.setAttribute("messages", messages); req.getRequestDispatcher("allcourse.jsp").forward(req,resp); } }
//DBUtil.java package com.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DBUtil { public static String db_url = "jdbc:mysql://localhost:3306/select_system?useSSL=false&serverTimezone=GMT"; public static String db_user = "root"; public static String db_pass = "0000"; public static Connection getConn () { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver");//加载驱动 conn = DriverManager.getConnection(db_url, db_user, db_pass); } catch (Exception e) { e.printStackTrace(); } return conn; } /** * 关闭连接 * @param state * @param conn */ public static void close (Statement state, Connection conn) { if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close (ResultSet rs, Statement state, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) throws SQLException { Connection conn = getConn(); PreparedStatement pstmt = null; ResultSet rs = null; String sql ="select * from d1"; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); if(rs.next()){ System.out.println("空"); }else{ System.out.println("不空"); } } }