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="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" 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>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" "http://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" onsubmit="check(this)"> 

  <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" "http://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" "http://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" 

          onsubmit="return check(this);"> 

           <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 

     

  } 

   

 

} 





源代码下载链接

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持中文源码网。