JSP + Servlet + JDBC实现对数据库的增删改查_servlet+jdbc+jsp对数据库增删改查

作者：weixin_40725706 | 2024-05-15 23:06:38

踩

servlet+jdbc+jsp对数据库增删改查

关于Servlet在web中的配置就不详细介绍了，这里就是介绍Servlet结合JDBC以及JSP操作数据库，关于环境的搭建前面几篇博客都详细的介绍了

一：通过JDBC项数据库添加数据

创建图书信息tb_books表，结构如图所示

创建名称为Book 的类


package com.book.web3;
public class Book {
	private int id;
	private String name;
	private double price;
	private int bookCount;
	private String author;
	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 double getPrice(){
		return price;
	}
	public void setPrice(double price){
		this.price = price;
	}
	public int getBookCount(){
		return bookCount;
	}
	public void setBookCount(int bookCount){
		this.bookCount = bookCount;
	}
	public String getAuthor(){
		return author;
	}
	public void setAuthor(String author){
		this.author = author;
	}
}

创建jdbc.jsp页面，用于添加数据的表单页面，该表单提交到jdbcresult.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>
<body>
<form action="jdbcresult.jsp" method="post">
<table align="center" border="1" width="50%" cellpadding="6">  
	<tr>
		<th colspan="2" align="center" >添加图书信息</th><%--th加粗， cellpadding和android padding含义一样--%>
	</tr>
	<tr>
		<td align="center" >图书编号：</td>   
		<td align="left" ><input type="text" name="id"></td>
	</tr>
	<tr>
		<td align="center" >图书名称：</td> 
		<td align="left" ><input type="text" name="name"></td>
	</tr>
	<tr>
		<td align="center" >价        格：</td>
		<td align="left" ><input type="text" name="price"></td>
	</tr>
	<tr>
		<td align="center" >数        量：</td>
		<td align="left" ><input type="text" name="bookCount"></td>
	</tr>
	<tr>
		<td align="center" >作        者：</td>
		<td align="left" ><input type="text" name="author"></td>
	</tr>
	<tr>
		<th colspan="2" align="center" ><input type="submit" name="submit" value="添加"></th>
	</tr>
</table>
</form>
</body>
</html>

创建jdbcresult.jsp页面，该页面通过JDBC提交的图书信息添加到数据库


<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%@page import="java.sql.Connection" %>
<%@page import="java.sql.DriverManager" %>
    
    <% request.setCharacterEncoding("UTF-8"); %>  
<jsp:useBean id="book" class="com.book.web3.Book"></jsp:useBean>
<jsp:setProperty property="*" name="book"/>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<body>
<%
try{
	Class.forName("com.mysql.jdbc.Driver");
	String  url = "jdbc:mysql://localhost:3306/student";
	String userName = "roots"; // 用户名
	String userPwd = "123456"; // 密码
	Connection conn = DriverManager.getConnection(url, userName, userPwd);
	String sql = "insert into tb_books(id,name,price,bookCount, author)values(?,?,?,?,?)";
	
	PreparedStatement ps = conn.prepareStatement(sql);
	ps.setInt(1, book.getId());
	ps.setString(2, book.getName());
	ps.setDouble(3, book.getPrice());
	ps.setInt(4, book.getBookCount());
	ps.setString(5, book.getAuthor());
	
	int row = ps.executeUpdate();
	if(row > 0){
		out.print("成功添加了 " + row + "条数据！ ");
	}
	
	ps.close();
	conn.close();
}catch(Exception e){
	out.print("图书信息添加失败  " + e.toString());
}
%>
</body>
</html>

在jdbcresult.jsp页面中，首先通过<jsp:useBean>实例化JavaBean的对象Book，并通过<jsp:setProperty>对Book对象中的属性赋值，<jsp:setProperty>标签中property属性的值可以设置为' * '，作用是将与表单中同名的属性值赋值给JavaBean对象中的同名属性，使用这种方式就不必对JavaBean中的属性一一进行赋值，减少代码量

结果展示：

二：查询数据

使用JDBC查询数据操作后，需要通过ResultSet对象来装载查询结果集。

创建名称为 JdbcFindServlet的Servlet对象，用于查询图书信息


package com.book.web3;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
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;
public class JdbcFindServlet extends HttpServlet  {
    private static final long serialVersionUID = 1L;  
  
    /** 
     * 构造函数 
     */  
    public JdbcFindServlet()  
    {  
        super();  
    }  
      
    /** 
     * 初始化 
     */  
    public void init() throws ServletException  
    {}  
      
    /** 
     * doGet()方法 
     */  
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException  
    {  
        doPost(request, response);  
    }  
      
    /** 
     * doPost()方法 
     */  
    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException  {  
    	try {
        	Class.forName("com.mysql.jdbc.Driver");
        	String  url = "jdbc:mysql://localhost:3306/student";
        	String userName = "roots"; // 用户名
        	String userPwd = "123456"; // 密码
        	Connection conn = DriverManager.getConnection(url, userName, userPwd);
        	Statement stmt = conn.createStatement();
        	String sql = "select * from tb_books";
        	ResultSet rs = stmt.executeQuery(sql);
        	
        	List<Book> list = new ArrayList<Book>();
        	while (rs.next()) {
        		Book book = new Book();
        		book.setId(rs.getInt("id"));
        		book.setName(rs.getString("name"));
        		book.setPrice(rs.getDouble("price"));
        		book.setBookCount(rs.getInt("bookCount"));
        		book.setAuthor(rs.getString("author"));
        		list.add(book);
			}
        	request.setAttribute("list", list);
        	rs.close();
        	stmt.close();
        	conn.close();	
		} catch (Exception e) {
			// TODO: handle exception
		}
    	request.getRequestDispatcher("jdbcfindservlet.jsp").forward(request, response);
    }        
    /** 
     * 销毁 
     */  
    public void destroy()  
    {  
        super.destroy();  
    }  
}

JdbcFindServlet对象在web.xml中的配置如下


<servlet>
        <servlet-name>JdbcFindServlet</servlet-name>
        <servlet-class>com.book.web3.JdbcFindServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>JdbcFindServlet</servlet-name>
        <url-pattern>/findServlet.do</url-pattern>  <!-- 根据不同的url来调用不同的servlet来进行处理。 --> 
    </servlet-mapping>

创建jdbcfindservlet.jsp页面，用于显示查询信息，下面用的脚本处理的相关点击事件


<%@page import="java.util.ArrayList"%>
<%@page import="java.util.List"%>
<%@page import="com.book.web3.Book"%>
 
<%@ 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>		
<script type="text/javascript">  
    function check(form) {  
        with (form) {  
            if (bookCount.value == "") {  
                alert("请输入更新数量！");  
                return false;  
            }  
            if (isNaN(bookCount.value)) {  
                alert("格式错误！");  
                return false;  
            }  
            return true;  
        }  
    }  
</script> 	
</head>
<body>
 
 
<table id="table1" align="center" border="1" width="50%" cellpadding="6">  
	<tr>
		<th colspan="6" align="center" >查询到的图书信息</th><%--th加粗， cellpadding和android padding含义一样--%>
	</tr>
	<tr>
		<th align="center" >ID</th> 
		<th align="center" >图书名称</th> 
		<th align="center" >价格</th>
		<th align="center" >数量</th>
		<th align="center" >作者</th>
		<th align="center" >修改数量</th>
	</tr>
	<%
	//获取图书信息集合
	List<Book> list = (List<Book>)request.getAttribute("list");
	if(list == null || list.size() < 1){
		out.print("没有数据 ");
	}else{
		for(Book book:list){				
	%>	
		<tr align="center">
			<td> <%= book.getId() %></td>
			<td> <%= book.getName() %></td>
			<td> <%= book.getPrice() %></td>
			<td> <%= book.getBookCount() %></td>
			<td> <%= book.getAuthor() %></td>
			<td>
			<form action="updateServlet.do" method="post" οnsubmit="return check(this);">  
                         <input type="hidden" name="id" value="<%=book.getId()%>"> 
                         <input type="text" name="bookCount" size="3">  
                         <input type="submit" value="修改数量">  
                        </form>  				
			</td>
		</tr>
		
	<%	
		}
	}
	%>
</table>
 
</body>
</html>

创建程序入口界面，index.jsp页面

<a href="findServlet.do">查看所有图书</a>  //和web.xml配置下<url-pattern>一致

运行结果：

三：修改数据

在上面的查询界面中已经预留了修改的页面，修改的表单中含有两个属性id与bookCount，因此指定id作为修改条件

创建修改图书信息名称为JdbcUpdateServlet请求的Servlet对象


package com.book.web3;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class JdbcUpdateServlet extends HttpServlet  {
    private static final long serialVersionUID = 1L;  
  
    /** 
     * 构造函数 
     */  
    public JdbcUpdateServlet()  
    {  
        super();  
    }  
      
    /** 
     * 初始化 
     */  
    public void init() throws ServletException  
    {}  
      
    /** 
     * doGet()方法 
     */  
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException  
    {  
        doPost(request, response);  
    }  
      
    /** 
     * doPost()方法 
     */  
    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException  {  
    	try {
    		int id = Integer.valueOf(request.getParameter("id"));
    		int bookCount = Integer.valueOf(request.getParameter("bookCount"));
    		
        	Class.forName("com.mysql.jdbc.Driver");
        	String  url = "jdbc:mysql://localhost:3306/student";
        	String userName = "roots"; // 用户名
        	String userPwd = "123456"; // 密码
        	Connection conn = DriverManager.getConnection(url, userName, userPwd);
        	String sql = "update tb_books set bookCount=? where id=?";
        	PreparedStatement ps = conn.prepareStatement(sql);
        	ps.setInt(1, bookCount);
        	ps.setInt(2, id);
        	ps.executeUpdate();
        	
        	ps.close();
        	conn.close();
        	
		} catch (Exception e) {
			// TODO: handle exception
		}
    }  
      
    /** 
     * 销毁 
     */  
    public void destroy()  
    {  
        super.destroy();  
    }  
}

web.xml配置


<servlet>
        <servlet-name>JdbcUpdateServlet</servlet-name>
        <servlet-class>com.book.web3.JdbcUpdateServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>JdbcUpdateServlet</servlet-name>
        <url-pattern>/updateServlet.do</url-pattern>  <!--根据不同的url来调用不同的servlet来进行处理。 --> 
    </servlet-mapping>

操作结果：

修改前

修改后

后面的删除就不介绍了，差不多一样的

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/weixin_40725706/article/detail/575405