2020.12.16
一、今日学习内容
今天练习了代码，增删改查
//Dao package Dao; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import DBUtil.DBUtil; import entity.Login; import entity.User; public class LoginDao { public boolean loginadd(Login login) { String sql = "insert into login(IDuser,password,flag) values('" + login.getIDuser() + "','" + login.getPassword() + "','" + login.getFlag() + "')"; //创建数据库链接 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 useradd(User user) { String sql = "insert into user(username,IDuser,password,userclass,usersex) values('" + user.getUsername()+"','"+user.getIDuser() + "','" + user.getPassword() + "','" + user.getUserclass() +"','"+user.getUsersex()+ "')"; //创建数据库链接 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 loginjudge(String IDuser,String password) { String sql = "select * from user "; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; boolean f=false; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { String iduser = rs.getString("IDuser"); String pasw = rs.getString("password"); if(iduser.equals(IDuser)&&pasw.equals(password)) { f=true; } } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return f; } public List<User> chaxun(String username,String IDuser){ String sql = "select * from user where "; if (username != "") { sql += "username like '%" +username+ "%'"; } else if (IDuser != "") { sql += "IDuser like '%" +IDuser+ "%'"; } List<User> list = new ArrayList<>(); Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; User bean = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { String username1 = rs.getString("username"); String IDuser1 = rs.getString("IDuser"); String password1=rs.getString("password"); String userclass1 = rs.getString("userclass"); String usersex1=rs.getString("usersex"); bean =new User(username1,IDuser1,password1,userclass1,usersex1); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return list; } public List<User> liulan() { String sql = "select * from user"; List<User> list = new ArrayList<>(); Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); User bean = null; while (rs.next()) { String username1 = rs.getString("username"); String IDuser1 = rs.getString("IDuser"); String password1=rs.getString("password"); String userclass1 = rs.getString("userclass"); String usersex1=rs.getString("usersex"); bean =new User(username1,IDuser1,password1,userclass1,usersex1); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return list; } public boolean delete2(String username) { String sql="delete from user where username='" + username + "'"; 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 User getByname(String username) { String sql = "select * from user where username ='" + username + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; User user1 = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { int id=rs.getInt("id"); String IDuser1 = rs.getString("IDuser"); String password1=rs.getString("password"); String userclass1 = rs.getString("userclass"); String usersex1=rs.getString("usersex"); user1 = new User(id,username,IDuser1,password1,userclass1,usersex1); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return user1; } public boolean xiugai(User user) { String sql = "update user set username='" + user.getUsername() + "', IDuser='" + user.getIDuser() + "', password='" + user.getPassword()+ "', userclass='" + user.getUserclass() + "', usersex='" + user.getUsersex()+ "' where id='" + user.getId() + "'"; 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; } }
//DBUtil package DBUtil; 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/test?serverTimezone=UTC"; public static String db_user = "root"; public static String db_pass = "08301016"; public static Connection getConn() { Connection conn = null; try { Class.forName("com.mysql.cj.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(); } } } }
//entity.Login package entity; public class Login { private int id; private String IDuser; private String password; private String flag; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getIDuser() { return IDuser; } public void setIDuser(String iDuser) { IDuser = iDuser; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getFlag() { return flag; } public void setFlag(String flag) { this.flag = flag; } public Login(int id,String iDuser,String password,String flag) { this.id = id; IDuser = iDuser; this.password = password; this.flag = flag; } public Login(String iDuser,String password,String flag) { IDuser = iDuser; this.password = password; this.flag = flag; } }
//entity.User package entity; public class User { private int id; private String username; private String IDuser; private String userclass; private String password; private String usersex; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getIDuser() { return IDuser; } public void setIDuser(String iDuser) { IDuser = iDuser; } public String getUserclass() { return userclass; } public void setUserclass(String userclass) { this.userclass = userclass; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getUsersex() { return usersex; } public void setUsersex(String usersex) { this.usersex = usersex; } public User(int id,String username,String iDuser,String password,String userclass,String usersex) { this.id = id; this.username = username; IDuser = iDuser; this.userclass = userclass; this.password = password; this.usersex = usersex; } public User(String username,String iDuser,String password,String userclass,String usersex) { this.username = username; IDuser = iDuser; this.userclass = userclass; this.password = password; this.usersex = usersex; } }
//service
package Service; import Dao.LoginDao; import entity.Login; import entity.User; import java.util.List; public class LoginService { LoginDao lDao=new LoginDao(); public void loginadd(Login login) { lDao.loginadd(login); } public void useradd(User user) { lDao.useradd(user); } public boolean loginjudge(String IDuser,String password) { return lDao.loginjudge(IDuser, password); } public List<User> chaxun(String username,String IDuser) { return lDao.chaxun(username,IDuser); } public List<User> liulan() { return lDao.liulan(); } public void delete2(String username) { lDao.delete2(username); } public User getByname(String username) { return lDao.getByname(username); } public void xiugai(User user) { lDao.xiugai(user); } }
//servlet package Servlet; import java.util.Date; import java.text.SimpleDateFormat; import java.io.IOException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import entity.Login; import entity.User; import Service.LoginService; @WebServlet("/LoginServlet") public class LoginServlet extends HttpServlet{ private static final long serialVersionUID = 1L; LoginService service=new LoginService(); protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String method = req.getParameter("method"); if ("loginadd".equals(method)) { loginadd(req, resp); } else if("useradd".equals(method)) { useradd(req,resp); }else if("chaxun".equals(method)) { chaxun(req,resp); }else if("liulan".equals(method)) { liulan(req,resp); }else if("delete2".equals(method)) { delete2(req,resp); }else if("getByname".equals(method)) { getByname(req,resp); }else if("getByname1".equals(method)) { getByname1(req,resp); }else if("xiugai".equals(method)) { xiugai(req,resp); } } private void loginadd(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException { req.setCharacterEncoding("utf-8"); //获取数据 String IDuser=req.getParameter("IDuser"); String password=req.getParameter("password"); String flag = req.getParameter("flag"); Login login=new Login(IDuser,password,flag); service.loginadd(login); if(flag.equals("管理员")&&password.equals("123456")) { req.setAttribute("message", "登录成功"); req.getRequestDispatcher("manager.jsp").forward(req,resp); }else if(flag.equals("管理员")&&!password.equals("123456")) { req.setAttribute("message", "密码错误，登录失败"); req.getRequestDispatcher("login.jsp").forward(req,resp); }else if(flag.equals("读者")) { if(service.loginjudge(IDuser, password)) { req.setAttribute("message", "登录成功"); req.getRequestDispatcher("login.jsp").forward(req,resp); }else { req.setAttribute("message", "用户名或密码错误，登录失败"); req.getRequestDispatcher("login.jsp").forward(req,resp); } } } private void useradd(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException { req.setCharacterEncoding("utf-8"); //获取数据 String username=req.getParameter("username"); String IDuser=req.getParameter("IDuser"); String password=req.getParameter("password"); String userclass=req.getParameter("userclass"); String usersex=req.getParameter("usersex"); User user=new User(username,IDuser,password,userclass,usersex); service.useradd(user); req.setAttribute("message", "录入成功"); req.getRequestDispatcher("mareaadd.jsp").forward(req,resp); } private void chaxun(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String username=req.getParameter("username"); String IDuser = req.getParameter("IDuser"); List<User> users = service.chaxun(username,IDuser); if(null==users||users.size()==0) { req.setAttribute("message", "没有该读者"); req.getRequestDispatcher("chaxun.jsp").forward(req,resp); } else { req.setAttribute("users",users); req.getRequestDispatcher("list.jsp").forward(req,resp); } } private void liulan(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); List<User> users = service.liulan(); req.setAttribute("users",users); req.getRequestDispatcher("liulan.jsp").forward(req, resp); } private void getByname(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String username = req.getParameter("username"); User user1 = service.getByname(username); if(user1 == null) { req.setAttribute("message", "查无此信息！"); req.getRequestDispatcher("delete2.jsp").forward(req,resp); } else { req.setAttribute("user1", user1); req.getRequestDispatcher("delete1.jsp").forward(req,resp); } } private void delete2(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String username=req.getParameter("username"); service.delete2(username); req.setAttribute("message", "删除成功！"); req.getRequestDispatcher("LoginServlet?method=liulan").forward(req,resp); } private void getByname1(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String username = req.getParameter("username"); //System.out.println("***"+username); User user2 = service.getByname(username); if(user2 == null) { req.setAttribute("message", "查无此信息！"); req.getRequestDispatcher("xiugai.jsp").forward(req,resp); } else { req.setAttribute("user2", user2); req.getRequestDispatcher("xiugai1.jsp").forward(req,resp); } } private void xiugai(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); int id = Integer.parseInt(req.getParameter("id")); String username = req.getParameter("username"); String IDuser= req.getParameter("IDuser"); String password = req.getParameter("password"); String userclass = req.getParameter("userclass"); String usersex = req.getParameter("usersex"); User user=new User(id,username,IDuser,password,userclass,usersex); service.xiugai(user); req.setAttribute("message", "修改成功"); req.getRequestDispatcher("LoginServlet?method=liulan").forward(req,resp); } }
二、遇到的问题
用的还是不是很熟练
三、明日计划
明天继续
