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

三叶草

一、JSP和Servlet的简单介绍

1、Servlet和JSP简介：

Java开发Web应用程序时用到的技术主要有两种，即Servlet和JSP，Servlet是在服务器端执行的Java程序，一个被称为Servlet容器的程序（其实就是服务器）负责执行Java程序，而JSP(Java Server Page)则是一个页面，由JSP容器负责执行.

2、Servlet和JSP的区别：

Servlet以Java程序为主，输出HTML代码时需要使用out.println函数，也就是说Java中内嵌HTML；而JSP则以HTML页面为主，需要写Java代码时则在页面中直接插入Java代码，即HTML中内嵌Java.

3、MVC模型

MVC模型就是将数据、逻辑处理、用户界面分离的一种方法

1）、M(Model, 模型)：用于数据处理、逻辑处理

2）、V(View，视图)：用于显示用户界面

3）、C(Controller，控制器)：根据客户端的请求控制逻辑走向和画面

而在Java中，MVC这三个部分则分别对应于 JavaBeans、JSP和Servlet

1）、M = JavaBeans：用于传递数据，拥有与数据相关的逻辑处理

2）、V = JSP：从Model接收数据并生成HTML

3）、C = Servlet：接收HTTP请求并控制Model和View

4、jdbc连接，可参考文章：点击打开链接
二、代码演示，实现了book的添加删除和修改功能

1、环境的配置

myeclipse+tomcat+MySQL

2、Book中bean类

package example.bean.book;  
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; 
  } 
 
}

3、Servlet类

1)、FindServlet.java

package example.servlet.book;  
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; 
 
import example.bean.book.Book; 
 
/** 
 * Servlet implementation class FindServlet 
 */ 
public class FindServlet extends HttpServlet { 
  private static final long serialVersionUID = 1L; 
 
  /** 
   * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse 
   *   response) 
   */ 
  protected void doGet(HttpServletRequest request, 
    HttpServletResponse response) throws ServletException, IOException { 
  try { 
    // 加载数据库驱动，注册到驱动管理器 
    Class.forName("com.mysql.jdbc.Driver"); 
    // 数据库连接字符串 
    String url = "jdbc:mysql://localhost:3306/db_book?useUnicode=true&characterEncoding=utf-8"; 
    // 数据库用户名 
    String username = "root"; 
    // 数据库密码 
    String password = ""; 
    // 创建Connection连接 
    Connection conn = DriverManager.getConnection(url, username, 
      password); 
    // 添加图书信息的SQL语句 
    String sql = "select * from tb_books"; 
    // 获取Statement 
    Statement statement = conn.createStatement(); 
 
    ResultSet resultSet = statement.executeQuery(sql); 
 
    List<Book> list = new ArrayList<Book>(); 
    while (resultSet.next()) { 
 
    Book book = new Book(); 
    book.setId(resultSet.getInt("id")); 
    book.setName(resultSet.getString("name")); 
    book.setPrice(resultSet.getDouble("price")); 
    book.setBookCount(resultSet.getInt("bookCount")); 
    book.setAuthor(resultSet.getString("author")); 
    list.add(book); 
 
    } 
    request.setAttribute("list", list); 
    resultSet.close(); 
    statement.close(); 
    conn.close(); 
 
  } catch (Exception e) { 
    e.printStackTrace(); 
  } 
 
  request.getRequestDispatcher("book_list.jsp") 
    .forward(request, response); 
 
  } 
 
  /** 
   * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse 
   *   response) 
   */ 
  protected void doPost(HttpServletRequest request, 
    HttpServletResponse response) throws ServletException, IOException { 
  // TODO Auto-generated method stub 
  doGet(request, response); 
  } 
 
}

2)、UpdateServlet.java类

package example.servlet.book; 
 
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; 
 
/** 
 * Servlet implementation class UpdateServlet 
 */ 
public class UpdateServlet extends HttpServlet { 
  private static final long serialVersionUID = 1L; 
 
  /** 
   * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse 
   *   response) 
   */ 
  protected void doGet(HttpServletRequest request, 
    HttpServletResponse response) throws ServletException, IOException { 
  int id = Integer.valueOf(request.getParameter("id")); 
  int bookCount = Integer.valueOf(request.getParameter("bookCount")); 
  try { 
    // 加载数据库驱动，注册到驱动管理器 
    Class.forName("com.mysql.jdbc.Driver"); 
    // 数据库连接字符串 
    String url = "jdbc:mysql://localhost:3306/db_book"; 
    // 数据库用户名 
    String username = "root"; 
    // 数据库密码 
    String password = ""; 
    // 创建Connection连接 
    Connection conn = DriverManager.getConnection(url, username, 
      password); 
    // 更新SQL语句 
    String sql = "update tb_books set bookcount=? where id=?"; 
    // 获取PreparedStatement 
    PreparedStatement ps = conn.prepareStatement(sql); 
    // 对SQL语句中的第一个参数赋值 
    ps.setInt(1, bookCount); 
    // 对SQL语句中的第二个参数赋值 
    ps.setInt(2, id); 
    // 执行更新操作 
    ps.executeUpdate(); 
    // 关闭PreparedStatement 
    ps.close(); 
    // 关闭Connection 
    conn.close(); 
  } catch (Exception e) { 
    e.printStackTrace(); 
  } 
  // 重定向到FindServlet 
  response.sendRedirect("FindServlet"); 
 
  } 
 
  /** 
   * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse 
   *   response) 
   */ 
  protected void doPost(HttpServletRequest request, 
    HttpServletResponse response) throws ServletException, IOException { 
  // TODO Auto-generated method stub 
  doGet(request, response); 
  } 
 
}

3）、DeleteServlet类

package example.servlet.book; 
import java.io.IOException; 
import java.sql.Connection; 
import javax.servlet.ServletException; 
import javax.servlet.http.HttpServlet; 
import javax.servlet.http.HttpServletRequest; 
import javax.servlet.http.HttpServletResponse; 
import example.dao.book.BookJdbcDao; 
import example.dao.book.ConnectionFactory; 
 
/** 
 * Servlet implementation class DeleteServlet 
 */ 
public class DeleteServlet extends HttpServlet { 
  private static final long serialVersionUID = 1L; 
 
  /** 
   * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse 
   *   response) 
   */ 
  protected void doGet(HttpServletRequest request, 
    HttpServletResponse response) throws ServletException, IOException { 
  int id = Integer.valueOf(request.getParameter("id")); 
  try { 
//   // 加载数据库驱动，注册到驱动管理器 
//   Class.forName("com.mysql.jdbc.Driver"); 
//   // 数据库连接字符串 
//   String url = "jdbc:mysql://localhost:3306/db_book"; 
//   // 数据库用户名 
//   String username = "root"; 
//   // 数据库密码 
//   String password = ""; 
//   // 创建Connection连接 
//   Connection conn = DriverManager.getConnection(url, username, 
//     password); 
//   // 删除图书信息的SQL语句 
//   String sql = "delete from tb_books where id=?"; 
//   // 获取PreparedStatement 
//   PreparedStatement ps = conn.prepareStatement(sql); 
//   // 对SQL语句中的第一个占位符赋值 
//   ps.setInt(1, id); 
//   // 执行更新操作 
//   ps.executeUpdate(); 
//   // 关闭PreparedStatement 
//   ps.close(); 
//   // 关闭Connection 
//   conn.close(); 
    BookJdbcDao bookDao=new BookJdbcDao(); 
    Connection conn=ConnectionFactory.getInstance().getConnection(); 
    bookDao.delete(conn,id); 
     
  } catch (Exception e) { 
    e.printStackTrace(); 
  } 
  // 重定向到FindServlet 
  response.sendRedirect("FindServlet"); 
  } 
 
  /** 
   * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse 
   *   response) 
   */ 
  protected void doPost(HttpServletRequest request, 
    HttpServletResponse response) throws ServletException, IOException { 
  doGet(request, response); 
  } 
 
}

4、servlet访问url映射配置：
由于客户端是通过URL地址访问web服务器中的资源，所以Servlet程序若想被外界访问，必须把servlet程序映射到一个URL地址上，这个工作在web.xml文件中使用<servlet>元素和<servlet-mapping>元素完成，<servlet>元素用于注册Servlet，它包含有两个主要的子元素：<servlet-name>和<servlet-class>，分别用于设置Servlet的注册名称和Servlet的完整类名。
一个<servlet-mapping>元素用于映射一个已注册的Servlet的一个对外访问路径，它包含有两个子元素：<servlet-name>和<url-pattern>，分别用于指定Servlet的注册名称和Servlet的对外访问路径.另外：同一个Servlet可以被映射到多个URL上，即多个<servlet-mapping>元素的<servlet-name>子元素的设置值可以是同一个Servlet的注册名.

eg:上例中的web.xml

<?xml version="1.0" encoding="UTF-8"?> 
<web-app xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns="https://java.sun.com/xml/ns/javaee" xmlns:web="https://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="https://java.sun.com/xml/ns/javaee https://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5"> 
 <display-name>JdbcConnection</display-name> 
 <welcome-file-list> 
  <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> 
  <description></description> 
  <display-name>FindServlet</display-name> 
  <servlet-name>FindServlet</servlet-name> 
  <servlet-class>example.servlet.book.FindServlet</servlet-class> 
 </servlet> 
 <servlet-mapping> 
  <servlet-name>FindServlet</servlet-name> 
  <url-pattern>/FindServlet</url-pattern> 
 </servlet-mapping> 
 <servlet> 
  <description></description> 
  <display-name>UpdateServlet</display-name> 
  <servlet-name>UpdateServlet</servlet-name> 
  <servlet-class>example.servlet.book.UpdateServlet</servlet-class> 
 </servlet> 
 <servlet-mapping> 
  <servlet-name>UpdateServlet</servlet-name> 
  <url-pattern>/UpdateServlet</url-pattern> 
 </servlet-mapping> 
 <servlet> 
  <description></description> 
  <display-name>DeleteServlet</display-name> 
  <servlet-name>DeleteServlet</servlet-name> 
  <servlet-class>example.servlet.book.DeleteServlet</servlet-class> 
 </servlet> 
 <servlet-mapping> 
  <servlet-name>DeleteServlet</servlet-name> 
  <url-pattern>/DeleteServlet</url-pattern> 
 </servlet-mapping> 
</web-app>

5、jsp页面

1）、index.jsp web页面主界面

<%@page import="java.sql.SQLException"%> 
<%@page import="java.sql.DriverManager"%> 
<%@page import="java.sql.Connection"%> 
<%@ page language="java" contentType="text/html; charset=utf-8" 
  pageEncoding="utf-8"%> 
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "https://www.w3.org/TR/html4/loose.dtd"> 
<html> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 
<title>添加图书信息</title> 
 
<script type="text/javascript"> 
  function check(form) { 
  with (form) { 
    if (name.value == "") { 
    alert("图书名称不能为空"); 
    return false; 
    } 
 
    if (price.value == "") { 
    alert("图书价格不能为空"); 
    return false; 
    } 
 
    if (author.value == "") { 
    alert("作者不能为空"); 
    return false; 
    } 
 
  } 
  } 
</script> 
 
</head> 
 
 
<body> 
 
<form action="addbook.jsp" method="post" > 
  <table align="center" width="450"> 
  <tr> 
    <td align="center" colspan="2"> 
    <h2>添加图书信息</h2> 
    <hr> 
    </td> 
  </tr> 
 
  <tr> 
    <td align="right">图书名称：</td> 
    <td><input type="text" name="name"></td> 
  </tr> 
 
  <tr> 
    <td align="right">价 格：</td> 
    <td><input type="text" name="price"></td> 
  </tr> 
 
  <tr> 
    <td align="right">数 量：</td> 
    <td><input type="text" name="bookCount" /></td> 
  </tr> 
 
  <tr> 
    <td align="right">作 者：</td> 
    <td><input type="text" name="author" /></td> 
  </tr> 
  <tr> 
    <td align="center" colspan="2"><input type="submit" value="添加"> 
    </td> 
  </tr> 
  </table> 
</form> 
 
<h2 align="center"> 
  <a href="FindServlet">查询图书信息</a> 
</h2> 
 
</body> 
</html>

2)、addbook.jsp 添加书籍成功或者失败后的显示页面

<%@ page language="java" contentType="text/html; charset=utf-8" 
  pageEncoding="utf-8"%> 
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "https://www.w3.org/TR/html4/loose.dtd"> 
 
<%@page import="java.sql.Connection"%> 
<%@page import="java.sql.DriverManager"%> 
<%@page import="java.sql.PreparedStatement"%> 
<html> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 
<title>添加结果</title> 
</head> 
<body> 
  <% 
  request.setCharacterEncoding("utf-8"); 
  %> 
  <jsp:useBean id="book" class="example.bean.book.Book"></jsp:useBean> 
  <jsp:setProperty property="*" name="book" /> 
  <% 
  try { 
    // 加载数据库驱动，注册到驱动管理器 
    Class.forName("com.mysql.jdbc.Driver"); 
    // 数据库连接字符串 
    String url = "jdbc:mysql://localhost:3306/db_book?useUnicode=true&characterEncoding=utf-8"; 
    // 数据库用户名 
    String username = "root"; 
    // 数据库密码 
    String password = ""; 
    // 创建Connection连接 
    Connection conn = DriverManager.getConnection(url, username, 
      password); 
    // 添加图书信息的SQL语句 
    String sql = "insert into tb_books(name,price,bookCount,author) values(?,?,?,?)"; 
    // 获取PreparedStatement 
    PreparedStatement ps = conn.prepareStatement(sql); 
    // 对SQL语句中的第1个参数赋值 
    ps.setString(1, book.getName()); 
    // 对SQL语句中的第2个参数赋值 
    ps.setDouble(2, book.getPrice()); 
    // 对SQL语句中的第3个参数赋值 
    ps.setInt(3, book.getBookCount()); 
    // 对SQL语句中的第4个参数赋值 
    ps.setString(4, book.getAuthor()); 
    // 执行更新操作，返回所影响的行数 
    int row = ps.executeUpdate(); 
    // 判断是否更新成功 
    if (row > 0) { 
    // 更新成输出信息 
    out.print("成功添加了 " + row + "条数据！"); 
    } 
    // 关闭PreparedStatement，释放资源 
    ps.close(); 
    // 关闭Connection，释放资源 
    conn.close(); 
  } catch (Exception e) { 
    out.print("图书信息添加失败！"); 
    e.printStackTrace(); 
  } 
  %> 
  <br> 
  <a href="index.jsp">返回</a> 
</body> 
</html>

3)、book_list.jsp 查找所有书籍信息的显示页面，以表格方式显示

<!--<%@page import="sun.awt.SunHints.Value"%>--> 
<%@ page language="java" contentType="text/html; charset=utf-8" 
  pageEncoding="utf-8"%> 
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "https://www.w3.org/TR/html4/loose.dtd"> 
 
<%@page import="java.util.List"%> 
<%@page import="example.bean.book.Book"%> 
<html> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 
<title>所有图书信息</title> 
<style type="text/css"> 
td { 
  font-size: 12px; 
} 
 
h2 { 
  margin: 0px 
} 
</style> 
<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 align="center" width="450" border="1" height="180" 
  bordercolor="white" bgcolor="black" cellpadding="1" cellspacing="1"> 
  <tr bgcolor="white"> 
    <td align="center" colspan="7"> 
    <h2>所有图书信息</h2> 
    </td> 
  </tr> 
  <tr align="center" bgcolor="#e1ffc1"> 
    <td><b>ID</b></td> 
    <td><b>图书名称</b></td> 
    <td><b>价格</b></td> 
    <td><b>数量</b></td> 
    <td><b>作者</b></td> 
    <td><b>修改</b></td> 
    <td><b>删除</b></td> 
  </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" bgcolor="white"> 
    <td><%=book.getId()%></td> 
    <td><%=book.getName()%></td> 
    <td><%=book.getPrice()%></td> 
    <td><%=book.getBookCount()%></td> 
    <td><%=book.getAuthor()%></td> 
    <td > 
    <form style="align:center; background-color: gray" action="UpdateServlet" method="post" 
      > 
       <input type="hidden" name="id" value="<%=book.getId()%>"> <input 
      type="text" name="bookCount" size="3"> 
       <input type="submit" value="修改数量"> 
    </form> 
    </td> 
    <td> 
    <a href="DeleteServlet?id=<%=book.getId()%>">删除</a> 
    </td> 
 
 
  </tr> 
  <% 
    } 
    } 
  %> 
  </table> 
  <h2 align="center"> 
  <a href="index.jsp">返回添加图书信息页面</a> 
  </h2> 
 
</body> 
</html>

6、jdbc简单的封装

1）、ConnectionFactory.java工厂类

package example.dao.book; 
 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.SQLException; 
 
public class ConnectionFactory { 
   
  private String driverClassName = "com.mysql.jdbc.Driver"; 
  private String url = "jdbc:mysql://localhost:3306/db_book?useUnicode=true&characterEncoding=utf-8"; 
  private String userName = "root"; 
  private String password = ""; 
   
  private static ConnectionFactory connectionFactory=null; 
 
  private ConnectionFactory() { 
   
  try { 
    Class.forName(driverClassName); 
  } catch (ClassNotFoundException e) { 
    e.printStackTrace(); 
  } 
  } 
   
  public Connection getConnection() throws SQLException 
  { 
  return DriverManager.getConnection(url, userName, password); 
   
  } 
   
  public static ConnectionFactory getInstance() 
  {   
  if (null==connectionFactory) { 
    connectionFactory=new ConnectionFactory(); 
  } 
  return connectionFactory; 
   
  } 
}

2）、BookJdbcDao.java数据库操作封装类

package example.dao.book; 
 
import java.sql.Connection; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
 
import example.bean.book.Book; 
 
public class BookJdbcDao { 
   
  private PreparedStatement ptmt = null; 
  private ResultSet rs = null; 
 
  public BookJdbcDao() { 
  } 
   
   
  public void findAll(Connection conn) throws SQLException 
  { 
  //to do 
   
  } 
   
  public void delete(Connection conn, int id) throws SQLException 
  { 
  String sql = "delete from tb_books where id=?"; 
  try{ 
    ptmt = conn.prepareStatement(sql); 
    // 对SQL语句中的第一个占位符赋值 
    ptmt.setInt(1, id); 
    // 执行更新操作 
    ptmt.executeUpdate(); 
     
  }finally{ 
    if (null!=ptmt) { 
    ptmt.close(); 
    } 
     
    if (null!=conn) { 
    conn.close(); 
    } 
     
  } 
   
  } 
   
  public void update(Connection conn, int id ,int bookcount) throws SQLException 
  { 
  //to do 
   
  } 
   
 
}

源代码下载链接
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持CodeAE代码之家。

[JSP] jsp+servlet+jdbc实现对数据库的增删改查

相关帖子

Java多线程超级详解(只看这篇就够了)

简单介绍ASP.NET Core实现文件上传和下载

Java NIO全面详解(看这篇就够了)

如何正确的中断线程？你的姿势是否正确

SPI：Java的高可扩展利器

Java面试为何阿里强制要求不在foreach里执行删除操作

图解Java排序算法之希尔排序

图解Java排序算法之快速排序的三数取中法

图解Java排序算法之堆排序

用Java8 stream处理数据

三叶草 LV3