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 
     
  } 
   
 
} 

源代码下载链接

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