java连接数据库进行增删改查 javaweb连接数据库增删改查

转载

编程梦想编织者 2023-10-24 06:50:06

文章标签 java连接数据库进行增删改查 JavaWeb 增删改查基础项目 sql 文章分类 Java 后端开发

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

一、准备工作

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>   ");
		}
		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>

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。