首先，我们需要明确一下技术栈的概念：
- JSP (Java Server Pages)：基于Java语言的服务器端网页开发技术；
- Servlet：是Java Web应用程序的核心，位于服务端，负责处理浏览器发送过来的HTTP请求并返回响应结果；
- JDBC (Java Database Connectivity)：Java数据库连接，Java语言操作关系型数据库的标准接口。
下面是JSP+servlet+JDBC实现对数据库增删改查的完整攻略。
第一步：搭建开发环境
开发环境需要：
- JDK 1.8 或以上版本；
- Tomcat 8 或以上版本；
- MySQL 5.7 或以上版本；
- Eclipse 或 IntelliJ IDEA。
第二步：建立数据库
在MySQL数据库中，新建一个数据库，例如sampledb，然后创建一个用户，并且为该用户授权访问database中的表。
第三步：建表
在sampledb中建立一个表，例如userinfo，userinfo表有三个字段，分别为id、name、age，其中id为主键。
CREATE TABLE `userinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
第四步：实现业务逻辑
编写Java类，通过JDBC连接数据库，实现对数据库的增删改查操作，具体流程如下：
1. 配置数据库连接
在项目中创建一个database.properties文件，配置如下：
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/sampledb
username=root
password=root
2. 编写工具类
创建一个JdbcUtils工具类，在该类中实现数据库的连接与关闭操作。
public class JdbcUtils {
private static String driver;
private static String url;
private static String username;
private static String password;
private Connection conn;
static{
try {
Properties props = new Properties();
InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("database.properties");
props.load(is);
driver = props.getProperty("driver");
url = props.getProperty("url");
username = props.getProperty("username");
password = props.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
public Connection getConnection() throws SQLException {
if(conn==null)
return DriverManager.getConnection(url, username, password);
else
return conn;
}
public void closeConnection() throws SQLException{
if(conn!=null){
conn.close();
conn = null;
}
}
}
3. 编写实体类
创建实体类UserInfo，用于封装从数据库中取出的数据。
public class UserInfo {
private int id;
private String name;
private int age;
// 构造函数
public UserInfo() {
}
public UserInfo(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
// Getter 和 Setter
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
4. 编写DAO层
创建UserInfoDao类，封装对数据库的增删改查操作。
public class UserInfoDao {
private Connection getConnection() throws SQLException {
return new JdbcUtils().getConnection();
}
public boolean addUserInfo(UserInfo userInfo) {
Connection conn = null;
String sql = "INSERT INTO userinfo(name,age) VALUES (?,?)";
PreparedStatement ps = null;
try {
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, userInfo.getName());
ps.setInt(2, userInfo.getAge());
ps.executeUpdate();
return true;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
public boolean removeUserInfo(int id) {
Connection conn = null;
String sql = "DELETE FROM userinfo WHERE id = ?";
PreparedStatement ps = null;
try {
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
ps.executeUpdate();
return true;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
public boolean updateUserInfo(UserInfo userInfo) {
Connection conn = null;
String sql = "UPDATE userinfo SET name = ?, age = ? WHERE id = ?";
PreparedStatement ps = null;
try {
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, userInfo.getName());
ps.setInt(2, userInfo.getAge());
ps.setInt(3, userInfo.getId());
ps.executeUpdate();
return true;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
public List<UserInfo> findAllUserInfo() {
Connection conn = null;
String sql = "SELECT * FROM userinfo";
PreparedStatement ps = null;
ResultSet rs = null;
List<UserInfo> userInfoList = new ArrayList<>();
try {
conn = getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
UserInfo userInfo = new UserInfo();
userInfo.setId(rs.getInt("id"));
userInfo.setName(rs.getString("name"));
userInfo.setAge(rs.getInt("age"));
userInfoList.add(userInfo);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
rs.close();
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return userInfoList;
}
}
5. 编写Servlet类
编写WebServlet类，用于处理客户端请求，调用DAO层实现增删改查。
@WebServlet(name = "UserInfoServlet", urlPatterns = {"/UserInfoServlet"})
public class UserInfoServlet extends HttpServlet {
private UserInfoDao userInfoDao;
public void init() {
userInfoDao = new UserInfoDao();
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String methodName = request.getParameter("methodName");
switch (methodName) {
case "addUserInfo":
addUserInfo(request, response);
break;
case "removeUserInfo":
removeUserInfo(request, response);
break;
case "updateUserInfo":
updateUserInfo(request, response);
break;
case "findAllUserInfo":
findAllUserInfo(request, response);
break;
}
}
protected void addUserInfo(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String name = request.getParameter("name");
int age = Integer.parseInt(request.getParameter("age"));
UserInfo userInfo = new UserInfo();
userInfo.setName(name);
userInfo.setAge(age);
if (userInfoDao.addUserInfo(userInfo)) {
response.sendRedirect("/success.jsp");
} else {
response.sendRedirect("/error.jsp");
}
}
protected void removeUserInfo(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int id = Integer.parseInt(request.getParameter("id"));
if (userInfoDao.removeUserInfo(id)) {
response.sendRedirect("/success.jsp");
} else {
response.sendRedirect("/error.jsp");
}
}
protected void updateUserInfo(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int id = Integer.parseInt(request.getParameter("id"));
String name = request.getParameter("name");
int age = Integer.parseInt(request.getParameter("age"));
UserInfo userInfo = new UserInfo();
userInfo.setId(id);
userInfo.setName(name);
userInfo.setAge(age);
if (userInfoDao.updateUserInfo(userInfo)) {
response.sendRedirect("/success.jsp");
} else {
response.sendRedirect("/error.jsp");
}
}
protected void findAllUserInfo(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
List<UserInfo> userInfoList = userInfoDao.findAllUserInfo();
request.setAttribute("userInfoList", userInfoList);
request.getRequestDispatcher("/userinfo.jsp").forward(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
public void destroy() {
userInfoDao = null;
}
}
6. 编写JSP页面
使用JSP页面实现UserInfoServlet的视图。
write.jsp：
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Add User</title>
</head>
<body>
<form action="UserInfoServlet" method="post">
<input type="text" placeholder="Name" name="name"/><br/>
<input type="text" placeholder="Age" name="age"/><br/>
<input type="hidden" name="methodName" value="addUserInfo"/>
<input type="submit" value="Add User"/>
</form>
</body>
</html>
userinfo.jsp:
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>User Info</title>
</head>
<body>
<table border="1">
<tr>
<td>ID</td>
<td>Name</td>
<td>Age</td>
<td>Actions</td>
</tr>
<%
List<UserInfo> userInfoList = (List<UserInfo>)request.getAttribute("userInfoList");
for (UserInfo userInfo : userInfoList) {
%>
<tr>
<td><%=userInfo.getId()%></td>
<td><%=userInfo.getName()%></td>
<td><%=userInfo.getAge()%></td>
<td>
<form action="UserInfoServlet" method="post">
<input type="hidden" name="id" value="<%=userInfo.getId()%>"/>
<input type="hidden" name="name" value="<%=userInfo.getName()%>"/>
<input type="hidden" name="age" value="<%=userInfo.getAge()%>"/>
<input type="hidden" name="methodName" value="updateUserInfo"/>
<input type="submit" value="Edit"/>
</form>
<form action="UserInfoServlet" method="post">
<input type="hidden" name="id" value="<%=userInfo.getId()%>"/>
<input type="hidden" name="methodName" value="removeUserInfo"/>
<input type="submit" value="Remove"/>
</form>
</td>
</tr>
<% } %>
</table>
<br/><br/>
<form action="write.jsp">
<input type="submit" value="Add User"/>
</form>
</body>
</html>
第五步：测试运行
将项目程序打包并放置在Tomcat服务器中运行，通过浏览器访问JSP页面，验证新增、修改、删除和查询等操作是否能够正常进行。
示例
下面给出以下两个示例：
示例1：新增用户
客户端访问write.jsp页面，输入用户信息，点击“Add User”按钮。UserInfoServlet通过POST请求获取数据，并通过DAO层调用JDBC向数据库插入新数据。
示例2：查询用户信息
客户端访问userinfo.jsp页面，UserInfoServlet通过DAO层调用JDBC从数据库中取出所有用户信息，返回JSP页面供用户查看。
本站文章如无特殊说明，均为本站原创，如若转载，请注明出处：jsp+servlet+jdbc实现对数据库的增删改查 - Python技术站
微信扫一扫 
支付宝扫一扫 