JavaWeb连接数据库增删改查（含登录验证、分页）

2024-02-02 20:59| 来源: 网络整理| 查看: 265

简单的页面对数据库的增删改查——新手入门推举一、准备工作 eclipse(无版本要求)或myeclipse都行，本人用的是eclipseApache Tomcat，我用的是7.0版本数据库 MySQL需要掌握到的知识点：项目的基本框架搭建（经典三层框架）在这里插入图片描述

二、项目的基本框架搭建

三层框架分别为：表现层、业务逻辑层、数据访问层。

表现层：包含JSP和Servlet等与web相关的内容，负责与浏览器的响应和请求；业务层：只关心业务逻辑；数据层：封装了对数据库的访问细节，数据操作类；

三层关系：表现层依赖业务层，而业务层依赖数据层

三、实现数据库的连接

需要在MySQL中创建所需数据库，并将表建好。

数据库名为test项目信息表名为User，表结构如下，其中id为自增长主键，增量为1。在这里插入图片描述

四、代码实现

1、在Util包下新建一个JDBCUtil 类用来处理对数据进行连接

package Util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; public class JDBCUtil { private static String DBDriver="org.gjt.mm.mysql.Driver"; private static String url="jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8"; private static String user="root"; private static String password="root"; static Connection con=null; static Statement sta=null; static PreparedStatement psta=null; //获得数据库的链接 public static Connection getConnection() { try { Class.forName(DBDriver); try { con = DriverManager.getConnection(url, user, password); return con; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (ClassNotFoundException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } return null; } public static Statement createStatement() { try { sta=getConnection().createStatement(); return sta; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } //创造数据预处理对象 public static PreparedStatement createPreparedStatement(String sql) { try { psta = getConnection().prepareStatement(sql); return psta; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return psta; } //关闭所有打开的资源 public static void closeOperation() { if(psta==null) { try { psta.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(sta==null) { try { sta.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(con==null) { try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }

2、在Model包下创建User实体类（实体类中的对象对应数据库表中的字段数据）

package Model; //类的设计 public class User { private int id; private String userName; private String password; private String Email; 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 getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getEmail() { return Email; } public void setEmail(String email) { Email = email; } }

3、在Dao包下创建UserDao实现对数据库的增删改查方法

package Dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import Model.User; import Util.MysqlHelper; public class UserDao { public boolean Login(String userName,String password){ String sql="select userName,password from User "; ResultSet rs=MysqlHelper.executeQuery(sql); try { while(rs.next()){ //用户输入的信息和数据库中的信息做比较，判断输入是否正确； String name = rs.getString("userName"); String pwd = rs.getString("password"); if(name.equals(userName) && pwd.equals(Util.MD5Helper.GetMD5Code(password))){ return true; } } rs.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return false; } public void addUser(User u) { String sql="insert into User (userName,password,Email) values (?,?,?)"; Util.MysqlHelper.executeUpdate(sql, u.getUserName(),Util.MD5Helper.GetMD5Code(u.getPassword()),u.getEmail()); } public void deleteUser(int i){ String sql = "delete from user where id=?"; Util.MysqlHelper.executeUpdate(sql,i); } public void modifyUser(User u){ String sql = "update User set id=?,userName=?,Email=? where id=? "; Util.MysqlHelper.executeUpdate(sql,u.getId(),u.getUserName(),u.getEmail(),u.getId()); } public void deleteUserByUserName(String userName) { } public void updateUser(Object...objects) { } //根据用户名查询到一个User对象，然后返回User对象 public User QueryUserByUserName(String userName) { User u=new User(); String sql="select * from User where userName=?"; ResultSet rs=MysqlHelper.executeQuery(sql, userName); try { if(rs.next()) { u.setId(Integer.parseInt(rs.getObject("id").toString())); u.setUserName(rs.getString("userName")); u.setEmail(rs.getString("Email")); } } catch (NumberFormatException | SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return u; } //查询所有用户信息 public List QueryUsers() { List list=new ArrayList(); //最终返回整个list集合 String sql="select * from user"; ResultSet rs=MysqlHelper.executeQuery(sql); try { while(rs.next()) { //保存取出来的每一条记录 User u=new User(); u.setId(rs.getInt("id")); u.setUserName(rs.getString("userName")); u.setEmail(rs.getString("Email")); list.add(u); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } }

4、在Util包下创建MysqlHelper接收传递过来的参数

package Util; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class MysqlHelper { //执行增、删、改用可变参数，来接收传递过来的参数，参数的个数可以不确定，提高使用率 public static int executeUpdate(String sql,Object...objects) { int len=0; PreparedStatement psta=JDBCUtil.createPreparedStatement(sql); try { for (int i = 0; i < objects.length; i++) { psta.setObject(i+1, objects[i]); } len=psta.executeUpdate(); return len; //逐个对objects数组中的元素来进行设置 ,数组中一个元素对应一个问号 } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return len; } //执行查询 public static ResultSet executeQuery(String sql,Object...objects) { ResultSet rs=null; PreparedStatement psta=JDBCUtil.createPreparedStatement(sql); try{ for (int i = 0; i < objects.length; i++) { psta.setObject(i+1, objects[i]); } rs=psta.executeQuery(); return rs; }catch(SQLException e) { e.printStackTrace(); }finally{ //JDBCUtil.closeOperation(); } return rs; } }

5、在BLL包下创建UserBLL传递参数调用增、删、改、查方法

package BLL; import java.util.List; import Dao.UserDao; import Model.User; public class UserBLL { public void addUser(User u) { UserDao userdao = new UserDao(); userdao.addUser(u); } public boolean Login(String userName,String password){ return new UserDao().Login(userName,password); } public void deleteUser(int i) { UserDao userdao = new UserDao(); userdao.deleteUser(i); } public void modifyUser(User u) { UserDao userdao = new UserDao(); userdao.modifyUser(u); } // 根据用户名查询到一个User对象，然后返回User对象 public User QueryUserByUserName(String userName) { return new UserDao().QueryUserByUserName(userName); } public List QueryUsers() { return new UserDao().QueryUsers(); } }

6、在cn.UI包下创建Login用于登录验证

package cn.UI; import java.io.IOException; import java.io.PrintWriter; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import Model.User; public class Login extends HttpServlet{ @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // TODO Auto-generated method stub this.doPost(req, resp); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("gbk"); response.setCharacterEncoding("gbk"); String userName=request.getParameter("userName"); String passWord=request.getParameter("passWord"); if(new BLL.UserBLL().Login(userName, passWord)){ response.sendRedirect("show"); } PrintWriter pw=response.getWriter(); pw.println("账户或密码错误！"); pw.flush(); pw.close(); } }

7、创建分页查询类

package cn.UI; import java.io.IOException; import java.io.PrintWriter; import java.util.ArrayList; import java.util.List; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import Model.User; public class ShowData extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { this.doPost(req, resp); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { List list=new BLL.UserBLL().QueryUsers(); String pageIndex1=request.getParameter("pageIndex"); if(pageIndex1==null){ pageIndex1="0"; } int pageIndex=Integer.parseInt(pageIndex1); //拿到传递过来的请求的第几条记录 response.setCharacterEncoding("gbk"); PrintWriter pw=response.getWriter(); StringBuilder sb=new StringBuilder(); //字符串的缓存区 //int pageIndex=0; //这是第几页 int pageSize=3; //就是每一页要显示的数据总记录数 for(int j=0;j

【本文地址】

公司简介

联系我们