jsp+javaBean实现Mysql数据库的增删改查
准备工作
JDBC驱动的安装
（仅限参考）网址如下：
https://blog.youkuaiyun.com/weixin_42366630/article/details/89966826
数据库数据的准备
使用Navicat进行数据库的创建，注意字符集编码设为utf-8
主界面
增加界面
直接删除
修改界面
查找界面
所需jsp和javaBean的包
javaBean
jsp
showall.jsp不包含在内
javaBean
JdbcUtil.java
package Teacher_JavaBean;
import java.sql.*;
public class JdbcUtil {
public static Connection getConnection() {
Connection conn = null; // 声明数据库连接对象
PreparedStatement pstmt = null; // 声明数据库操作对象
String driverName = "com.mysql.jdbc.Driver"; // 驱动程序名
String userName = "root"; // 数据库用户名
String userPwd = ""; // 指定用户密码
// String dbName = "test"; // 指定数据库名字
// String url1 = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=urf" + dbName;
// String url2 = "?user=" + userName + "&password=" + userPwd;
// String url3 = "&useUnicode=true&characterEncoding=UTF-8";
//String url = "jdbc:mysql://localhost:3306/test?user=root&password="; // 形成带数据库读写编码的数据库连接字
String url = "jdbc:mysql://localhost:3306/test?userUnicode=true&characterEnconding=utf-8&autoReconnect=true";
try {
Class.forName(driverName);
conn = DriverManager.getConnection(url,userName,userPwd);
} catch (Exception e) {
e.printStackTrace();
} finally {
return conn;
}
}
public static void free(ResultSet rs, PreparedStatement ps, Connection conn) {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
User.java
package Teacher_JavaBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//import com.mysql.cj.xdevapi.Statement;
public class User {
private long id;
private String userId;
private String name;
private String password;
private String sex;
public User() {
}
public User(String userId, String username, String sex) {
this.userId = userId;
this.name = username;
this.sex = sex;
this.password = "";
}
public User(long id,String userId, String username, String sex) {
this.id = id;
this.userId = userId;
this.name = username;
this.sex = sex;
this.password = "";
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public long getId() {
return id;
}
public User findUserById(long id) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
User user = null;
try {
conn = JdbcUtil.getConnection();
String sql = "select * from user where id=? ";
ps = conn.prepareStatement(sql);
ps.setLong(1, id);
rs = ps.executeQuery();
if (rs.next()) {
user = new User(rs.getString(1),rs.getString(2),rs.getString(3));
// user.setUserId();
// user.setName(rs.getString(2));
// user.setSex(rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.free(rs, ps, conn);
}
return user;
}
}
UserDao.java
package Teacher_JavaBean;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserDao {
// 向数据库中添加用户记录的方法add()
public void add(User user) throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtil.getConnection();
String sql = "insert into user(id,username,sex,userid) values (?,?,?,?) ";
ps = conn.prepareStatement(sql);
ps.setLong(1, user.getId());
ps.setString(2, user.getName());
ps.setString(3, user.getSex());
ps.setString(4, user.getUserId());
ps.executeUpdate();
}
catch (Exception e) {
e.printStackTrace();
}
finally {
JdbcUtil.free(null, ps, conn);
}
}
// 修改数据库用户记录的方法update()
public void update(User user) throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtil.getConnection();
String sql = "update user set username=?,sex=? where userid=? ";
ps = conn.prepareStatement(sql);
ps.setString(1, user.getName());
ps.setString(2, user.getSex());
ps.setString(3, user.getUserId());
ps.executeUpdate();
} finally {
JdbcUtil.free(null, ps, conn);
}
}
// 删除数据库用户记录的方法delete()
public void delete(String UserId) throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtil.getConnection();
String sql = "delete from user where userid=?";
ps = conn.prepareStatement(sql);
ps.setString(1, UserId);
ps.executeUpdate();
} finally {
JdbcUtil.free(null, ps, conn);
}
}
// 根据id查询用户的方法findUserById()
public User findUserById(String UserId) throws Exception {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
User user = null;
try {
conn = JdbcUtil.getConnection();
String sql = "select * from user where userid=? ";
ps = conn.prepareStatement(sql);
ps.setString(1, UserId);
rs = ps.executeQuery();
if (rs.next()) {
user = new User(rs.getString(1),rs.getString(2),rs.getString(3));
// user.setUserId(rs.getString(1));
// user.setName(rs.getString(2));
// user.setSex(rs.getString(3));
}
} finally {
JdbcUtil.free(rs, ps, conn);
}
return user;
}
// 查询全部用户的方法QueryAll()
public List<User> QueryAll() throws Exception {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<User> userList = new ArrayList<User>();
try {
conn = JdbcUtil.getConnection();
String sql = "select * from user ";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
User user = new User(rs.getLong("id"), rs.getString("userid"),rs.getString("username"),rs.getString("sex"));
// user.setUserId(rs.getString(1));
// user.setName(rs.getString(2));
// user.setSex(rs.getString(3));
userList.add(user);
}
} finally {
JdbcUtil.free(rs, ps, conn);
}
return userList;
}
}
jsp
主界面
show.jsp
<%@page import="Teacher_JavaBean.User"%>
<%@page import="Teacher_JavaBean.JdbcUtil"%>
<%@page import="Teacher_JavaBean.UserDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import=" java.util.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>显示所有用户的信息</title>
</head>
<body>
<a href="select.jsp" >查询</a></br>
<a href="add.jsp" >增加</a></br>
<%
//request.setCharacterEncoding("UTF-8");
UserDao dao = new UserDao();
List<User> users = dao.QueryAll(); //查询获取目前数据库中的全部记录信息
%>
<table border="1 solid red">
<tr>
<th>编号</th>
<th>姓名</th>
<th>性别</th>
<th>操作1</th>
<th>操作2</th>
</tr>
<%
for (int i = 0; i < users.size(); ++i) {
User u = users.get(i);
//String abc = "编号:" + u.getUserId() + "_姓名:" + u.getName() + "_性别:" + u.getSex();
String userId = u.getUserId();
String userName = u.getName();
String sex = u.getSex();
Long id = u.getId();
%>
<tr>
<td><%=userId%></td>
<td><%=userName%></td>
<td><%=sex%></td>
<td><a href="update.jsp?id=<%=id%>&userid=<%=userId%>&username=<%=userName%>&sex=<%=sex%>">修改</a></td>
<td><a href="delete.jsp?userid=<%=userId%>">删除</a></td>
</tr>
<%
}
%>
</table>
</body>
</html>
增加
add.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>增加</title>
</head>
<body>
<form action="add1.jsp">
<input type="text" name="id" placeholder="id"/>
<input type="text" name="userName" placeholder="userName"/>
<input type="text" name="sex" placeholder="sex" />
<input type="text" name="userId" placeholder="userId" />
<input type="submit" vaule="提交">
</form>
</body>
</html>
add1.jsp
<%@page import="Teacher_JavaBean.UserDao"%>
<%@page import="Teacher_JavaBean.User"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>增加显示</title>
</head>
<body>
<% long id = Long.parseLong(request.getParameter("id"));
String userId = request.getParameter("userId");
String userName = request.getParameter("userName");
String sex = request.getParameter("sex");
%>
<% User user = new User(id,userId,userName,sex);
UserDao dao = new UserDao();
dao.add(user);
//out.print(user.getUserId());
//out.print(user.getName());
//out.print(user.getSex());
//out.print(userName);
%>
<jsp:forward page="show.jsp"/>
</body>
</html>
删除
delete.jsp
<%@page import="Teacher_JavaBean.UserDao"%>
<%@page import="Teacher_JavaBean.User"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>删除</title>
</head>
<body>
<%
String userId = request.getParameter("userid");
UserDao dao = new UserDao();
dao.delete(userId);
%>
<jsp:forward page="show.jsp"/>
</body>
</html>
修改
update.jsp
<%@page import="Teacher_JavaBean.UserDao"%>
<%@page import="Teacher_JavaBean.User"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>修改</title>
</head>
<body>
<%
//request.setCharacterEncoding("UTF-8");
long id = Long.parseLong(request.getParameter("id"));
String userId = request.getParameter("userid");
String userName = request.getParameter("username");
String sex = request.getParameter("sex");
%>
<form action="update1.jsp">
<input type="text" name="userid" value="<%=userId%>" />
<input type="text" name="username" value="<%=userName%>" />
<input type="text" name="Sex" value="<%=sex%>" />
<input type="hidden" name="Id" value="<%=id%>"/>
<input type="submit" vaule="提交修改内容">
</form>
</body>
</html>
update1.jsp
<%@page import="Teacher_JavaBean.UserDao"%>
<%@page import="Teacher_JavaBean.User"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta charset=UTF-8>
<title>修改显示</title>
</head>
<body>
<form action ="show.jsp" method="post">
<%
//request.setCharacterEncoding("UTF-8");
long id = Long.parseLong(request.getParameter("Id"));
String userId = request.getParameter("userid");
String userName = request.getParameter("username");
//userName = new String(userName.getBytes("ISO-8859-1"),"UTF-8"); ISO-8859-1
String sex = request.getParameter("Sex");
%>
<table border="1 solid red">
<tr>
<th>编号</th>
<th>姓名</th>
<th>性别</th>
<th>用户编号</th>
</tr>
<tr>
<td><%=id %></td>
<td><%=userName%></td>
<td><%=sex%></td>
<td><%=userId%></td>
</tr>
</table>
<%
//response.setCharacterEncoding("UTF-8");
User user = new User(id,userId,userName,sex);
UserDao dao = new UserDao();
dao.update(user);
out.print(user.getName());
%>
<input type="submit" value="返回主界面">
</form>
</body>
</html>
单条记录查询
select.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>查询</title>
</head>
<body>
<form action="select1.jsp">
<input type="text" name="userId" />
<input type="submit" vaule="提交">
</form>
</body>
</html>
select1.jsp
<%@page import="Teacher_JavaBean.UserDao"%>
<%@page import="Teacher_JavaBean.User"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import=" java.util.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>查询显示</title>
</head>
<body>
<%
String UserId = request.getParameter("userId");
UserDao dao = new UserDao();
User user = dao.findUserById(UserId);
%>
<table border="1 solid red">
<tr>
<th>编号</th>
<th>姓名</th>
<th>性别</th>
<th>用户编号</th>
</tr>
<tr>
<td><%=UserId%></td>
<td><%=user.getName()%></td>
<td><%=user.getSex()%></td>
<td><%=user.getUserId()%></td>
</tr>
</table>
</body>
</html>
完工！ ！
扫一扫
3728
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
