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

﹏ヽ暗。殇╰゛Y 2022-05-11 12:10 193阅读 0赞

一、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连接，可参考文章：[点击打开链接][Link 1]

二、代码演示，实现了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 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 
       
       } 
       
       
       }

[源代码下载链接][Link 2]

[Link 1]: http://blog.csdn.net/j086924/article/details/51546876
[Link 2]: http://download.csdn.net/detail/j086924/9557220