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

最新推荐文章于 2024-05-22 11:04:05 发布

小高

最新推荐文章于 2024-05-22 11:04:05 发布

阅读量4.4w

点赞数 59

分类专栏：入门开发设计网页数据库输入输出流设计开发文章标签： JavaWeb 增删改查基础项目

本文链接：https://blog.csdn.net/weixin_43289466/article/details/83509255

版权

入门同时被 3 个专栏收录

12 篇文章 2 订阅

订阅专栏

开发设计

9 篇文章 0 订阅

订阅专栏

设计开发

5 篇文章 0 订阅

订阅专栏

简单的页面对数据库的增删改查——新手入门推举

一、准备工作

eclipse(无版本要求)或myeclipse都行，本人用的是eclipse
Apache 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<User> list=new ArrayList<User>();   //最终返回整个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<list.size()/pageSize;j++)
		{
			sb.append("<a href='show?pageIndex="+j+"'>"+(j+1)+"</a>&nbsp&nbsp&nbsp");
		}
		for(int i=pageIndex*pageSize;i<(pageIndex+1)*pageSize;i++){
			User u=(User)list.get(i);
		sb.append("<html><head></head><body><table>");
			sb.append("<tr><td>用户编号"+u.getId()+"</td>");
			sb.append(String.format("<td>用户名%s</td>", u.getUserName()));
			sb.append("<td>邮箱"+u.getEmail()+"</td>");
			sb.append("<td><a href='add.html'>增加</a></td>");
			sb.append("<td><a href='delete.html'>删除</a></td>");
			sb.append("<td><a href='modifyData?userName="+u.getUserName()+"'>修改</a></td>");
			sb.append("</tr>");
			}
		sb.append("</table></body></html>");
		pw.write(sb.toString());
		pw.flush();
		pw.close();
	}

}

8、增加类

package cn.UI;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import Model.User;

public class AddUser 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 {
		// TODO Auto-generated method stub
		String userName=request.getParameter("userName");
		String passWord=request.getParameter("passWord");
		String Email=request.getParameter("Email");
		User u=new User();
		u.setUserName(userName);
		u.setPassword(passWord);
		u.setEmail(Email);
		new BLL.UserBLL().addUser(u);
		response.sendRedirect("show");
	}
}

9、删除类

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;

public class DeleteServlet 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 {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("gbk");
		response.setCharacterEncoding("gbk");
		PrintWriter pw=response.getWriter();
		new BLL.UserBLL().deleteUser(Integer.parseInt(request.getParameter("id")));
		response.sendRedirect("show");
		pw.flush();
		pw.close();
	}

}

10、修改类一，获取当前用户数据值，便于操作者修改

package cn.UI;

import java.io.BufferedReader;
import java.io.FileReader;
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 ModifyData 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 {
		response.setCharacterEncoding("gbk");
		String userName=request.getParameter("userName");  //拿到要查询的id编号的数据
		User u=new BLL.UserBLL().QueryUserByUserName(userName);
		//展示u的数据，并显示出来
		//读模板，然后替换特殊字符部分
		BufferedReader br=new BufferedReader(new FileReader("E:\\Eclipse工作空间\\第二部\\ServiceJava\\WebContent\\modifyData.html"));
		String s=br.readLine();
		String context="";
		while(s!=null)
		{
			context=context+s;
			s=br.readLine();//读出所有的文本			
		}
		context=context.replace("@Id", u.getId()+"");   //替换字符串
		context=context.replace("@userName", u.getUserName()).replace("@Email", u.getEmail());
		PrintWriter pw=response.getWriter();
		pw.println(context);
		pw.flush();
		pw.close();
		
		
	}

}

11、修改类二

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 UpdateData 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 {
		// TODO Auto-generated method stub
		User u=new User();
		request.setCharacterEncoding("gbk");
		response.setCharacterEncoding("gbk");
		int  id=Integer.valueOf(request.getParameter("id"));
		String userName=request.getParameter("userName");
		String Email=request.getParameter("Email");
		u.setId(id);
		u.setUserName(userName);
		u.setEmail(Email);
		new BLL.UserBLL().modifyUser(u);
		response.sendRedirect("show");
	}
}

PS：我这里加了一个MD5，加密了用户密码（可用可不用）

package Util;

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;

public class MD5Helper {
	private static String bytesToHex(byte[] bytes) {
        StringBuffer md5str = new StringBuffer();
        //把数组每一字节换成16进制连成md5字符串
        int digital;
        for (int i = 0; i < bytes.length; i++) {
             digital = bytes[i];
 
            if(digital < 0) {
                digital += 256;
            }
            if(digital < 16){
                md5str.append("0");
            }
            md5str.append(Integer.toHexString(digital));
        }
        return md5str.toString().toLowerCase();
    }
    public static String GetMD5Code(String strObj) {
        String resultString = null;
        try {
            resultString = new String(strObj);
            MessageDigest md = MessageDigest.getInstance("MD5");
            // md.digest() 该函数返回值为存放哈希值结果的byte数组
            byte b[]=md.digest(strObj.getBytes());
            resultString =bytesToHex(b) ;
        } catch (NoSuchAlgorithmException ex) {
            ex.printStackTrace();
        }
        return resultString;
    }

}

五、Web的配置

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
  <display-name>ServiceJava</display-name>
  <welcome-file-list>
  <welcome-file>login.html</welcome-file>
    <welcome-file>login.htm</welcome-file>
    <welcome-file>login.jsp</welcome-file>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
   <servlet>
  	<servlet-name>ShowData</servlet-name>
  	<servlet-class>cn.UI.ShowData</servlet-class>  
  </servlet>
  <servlet-mapping>
  	<servlet-name>ShowData</servlet-name>
  	<url-pattern>/show</url-pattern>
  </servlet-mapping>
  <servlet>
  	<servlet-name>AddUser</servlet-name>
    <servlet-class>cn.UI.AddUser</servlet-class>
  </servlet>
  <servlet-mapping>
  	<servlet-name>AddUser</servlet-name>
    <url-pattern>/addUser</url-pattern>
  </servlet-mapping>
  <servlet>
  	<servlet-name>Login</servlet-name>
    <servlet-class>cn.UI.Login</servlet-class>
  </servlet>
  <servlet-mapping>
  	<servlet-name>Login</servlet-name>
    <url-pattern>/login</url-pattern>
  </servlet-mapping>
   <servlet>
  	<servlet-name>DeleteServelet</servlet-name>
    <servlet-class>cn.UI.DeleteServlet</servlet-class>
  </servlet>
  <servlet-mapping>
  	<servlet-name>DeleteServelet</servlet-name>
    <url-pattern>/delete1</url-pattern>
  </servlet-mapping>
  <servlet>
  <servlet-name>modifyData</servlet-name>
  <servlet-class>cn.UI.ModifyData</servlet-class>
  </servlet>
  <servlet-mapping>
  	<servlet-name>modifyData</servlet-name>
  	<url-pattern>/modifyData</url-pattern>
  </servlet-mapping>
    <servlet>
  <servlet-name>UpdateData</servlet-name>
  <servlet-class>cn.UI.UpdateData</servlet-class>
  </servlet>
  <servlet-mapping>
  	<servlet-name>UpdateData</servlet-name>
  	<url-pattern>/upDate</url-pattern>
  </servlet-mapping>
</web-app>

六、HTML页面代码部分

1、增

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="addUser" method="post">
<table>
	<tr><td>用户名</td><td><input type="text" name="userName"></td></tr>
    <tr><td>密码</td><td><input type="password" name="passWord"></td></tr>
    <tr><td>Email</td><td><input type="text" name="Email"></td></tr>
	<tr><td colspan="2"><input type="submit" value="提交添加咯"></td></tr>
</table>
</form>
</body>
</html>

2、删

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
	function del(){
		var userId=document.getElementById("userId").value;
		var result=confirm("会不会删除啊？");
		if(result==true){
			window.location.href='delete1?id='+userId;
		}
		//alert(userId);		
	}


</script>
</head>
<body>
<form action="delete" method="post">
	请输入你要删除的id<input type="text" name="Id" id="userId">
	<input type="button" value="删除" onclick="del();">
	
</form>
</body>
</html>

3、改

<!DOCTYPE html>
<html>
<head>
<meta charset="gbk">
<title>Insert title here</title>
</head>
<body>
<form action="upDate" method="post">
<table>
	<tr><td>用户编号</td><td><input type="text" name="id" value="@Id"></td></tr>
	<tr><td>用户名</td><td><input type="text" name="userName" value="@userName"></td></tr>
	<tr><td>用户邮箱</td><td><input type="text" name="Email" value="@Email"></td></tr>
	<tr><td colspan="2"><input type="submit"  value="修改"></td></tr>
</table>
</form>

</body>
</html>

4、登录

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="login" method="post">
<table>
	<tr><td>用户名</td><td><input type="text" name="userName" ></td></tr>
	<tr><td>用户邮箱</td><td><input type="password" name="passWord" ></td></tr>
	<tr><td colspan="2"><input type="submit"  value="登录"></td></tr>
</table>
</form>
</body>
</html>

注意：
编码格式需要统一，否则会出现乱码情况，查询修改等功能也可能报错

小高

关注

59
点赞
踩
741

收藏

觉得还不错? 一键收藏
16
评论
JavaWeb连接数据库增删改查（含登录验证、分页）

简单的页面对数据库的增删改查——新手入门推举一、准备工作eclipse(无版本要求)或myeclipse都行，本人用的是eclipseApache Tomcat，我用的是7.0版本数据库 MySQL需要掌握到的知识点：项目的基本框架搭建（经典三层框架）二、项目的基本框架搭建三层框架分别为：表现层、业务逻辑层、数据访问层。表现层：包含JSP和Servlet等与web相关的内容...
复制链接

扫一扫