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

Java  /  管理员 发布于 7年前   209

一、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="https:/article/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="https:/article/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="https:/article/DeleteServlet?id=<%=book.getId()%>">删除</a>       </td>       </tr>     <%       }       }     %>   </table>   <h2 align="center">     <a href="https:/article/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        }     } 

源代码下载链接

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