JDBC(二)增,删,改,查操作

第四步:写个DAO,里面包括增删改查的操作

package cn.itcast.webapp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.itcast.webapp.db.DBManager;
import cn.itcast.webapp.domain.Customer;

public class CustomerDAO {
 
 public static void  updateCustomer(Customer cust){
  //1. 获取数据库连接
  Connection conn = DBManager.getConnection();
  //2. 准备 sql 文
  String sql = "UPDATE customers SET name = ?, address = ?, phone = ? WHERE id = ?";
 
  PreparedStatement ps = null;
 
  try {
   //3. 获取 PreparedStatement 对象
   ps = conn.prepareStatement(sql);
   ps.setString(1, cust.getName());
   ps.setString(2, cust.getAddress());
   ps.setString(3, cust.getPhone());
   ps.setInt(4, cust.getId());
  
   //4. 进行更改操作
   ps.executeUpdate();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   throw new RuntimeException("修改用户信息异常");
  }finally{
   //5. 关闭数据库资源
   DBManager.releaseDBSource(null, ps, conn);
  }
 }
 
 public static Customer getCustomerById(int id){
  Customer cust = null;
 
  Connection conn = DBManager.getConnection();
  PreparedStatement ps = null;
  ResultSet rs = null;
 
  String sql = "SELECT id, name, address, phone FROM customers WHERE id = ?";
  try {
   ps = conn.prepareStatement(sql);
   ps.setInt(1, id);
  
   rs = ps.executeQuery();
   if(rs.next()){
    String name = rs.getString(2);
    String address = rs.getString("address");
    String phone = rs.getString("phone");
   
    cust = new Customer();
    cust.setName(name);
    cust.setId(id);
    cust.setAddress(address);
    cust.setPhone(phone);
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   throw new RuntimeException("根据 id: " + id + " 查询用户信息异常.");
  }
 
  return cust;
 }
 
 public static void deleteCustomerById(int id){
  //1. 获取连接
  Connection conn = null;
  PreparedStatement ps = null;
 
  conn = DBManager.getConnection();
 
  //2. 准备 sql
  String sql = "DELETE FROM customers WHERE id = ?";
 
  try {
   //3. 获取 PreparedStatement
   ps = conn.prepareStatement(sql);
   ps.setInt(1, id);
  
   //4. 删除操作
   ps.executeUpdate();
  } catch (SQLException e) {
   e.printStackTrace();
  }finally{
   //5. 关闭数据库资源
   DBManager.releaseDBSource(null, ps, conn);
  }
 }
 
 //根据 customer 对象查找符合条件的 customer 记录, 将它们放到 List 中, 并返回
 public static List searchCustomers(Customer customer){
  List customers = new ArrayList();
 
  //1. 不考虑条件, 全部查出来, 并返回
  //a. 获取数据库连接
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
 
  conn = DBManager.getConnection();
 
  //b. 准备 sql 语句
  String sql = "SELECT id, name, address, phone FROM customers WHERE name LIKE ? AND address LIKE

? AND phone LIKE ?";
 
  try {
   //c. 获取执行 sql 语句的 PreparedStatement 对象
   ps = conn.prepareStatement(sql);
   ps.setString(1, "%" + (customer.getName() == null ? "" : customer.getName())  + "%");
   ps.setString(2, "%" + (customer.getAddress() == null ? "" : customer.getAddress()) + "%");
   ps.setString(3, "%" + (customer.getPhone() == null ? "" : customer.getPhone()) + "%");
  
   //d. 执行查询
   rs = ps.executeQuery();
  
   //e. 对 ResultSet 进行遍历
  
   Customer cust = null;
   while(rs.next()){
    int id = 0;
    String name = null;
    String address = null;
    String phone = null;
   
    id = rs.getInt(1);
    name = rs.getString(2);
    address = rs.getString(3);
    phone = rs.getString(4);
   
    cust = new Customer();
   
    cust.setId(id);
    cust.setAddress(address);
    cust.setName(name);
    cust.setPhone(phone);
   
    customers.add(cust);
   }
  } catch (SQLException e) {
   e.printStackTrace();
   throw new RuntimeException("查询用户异常");
  }finally{
   //f. 关闭数据库资源
   DBManager.releaseDBSource(rs, ps, conn);
  }
 
 
  return customers;
 }
 
 public static void saveCustomer(Customer customer){
  Connection conn = null;
  PreparedStatement ps = null;
 
  String sql = "INSERT INTO customers(name, address, phone) VALUES(?, ?, ?)";
  String name = customer.getName();
  String address = customer.getAddress();
  String phone = customer.getPhone();
 
  conn = DBManager.getConnection();
 
  try {
   ps = conn.prepareStatement(sql);
   ps.setString(1, name);
   ps.setString(2, address);
   ps.setString(3, phone);
  
   ps.executeUpdate();
  } catch (SQLException e) {
   e.printStackTrace();
   if(e.getMessage().equals("Duplicate entry '" + customer.getName() + "' for key 2"))
    throw new RuntimeException("该用户名已经存在, 请重新选择!");
   throw new RuntimeException("插入新客户信息异常");
  }finally{
   DBManager.releaseDBSource(null, ps, conn);
  }
  }
}

下面写的是视图界面程序:

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>My JSP 'addCustomer.jsp' starting page</title>
    <script type="text/javascript" src="js/check.js"></script>
  </head>
 
  <body>
 <c:if test="${requestScope.errorInfo != null}">
  <font color="red">${requestScope.errorInfo}</font>
  <br><br>
 </c:if>  
    <form action="addCustomerServlet" method="post">
     <table>
      <tr>
       <td>Name: </td>
       <td><input type="text" name="name" value="${param.name }" /></td>
      </tr>
      <tr>
       <td>Address: </td>
       <td><input type="text" name="address" value="${param.address }"  /></td>
      </tr>
      <tr>
       <td>Phone: </td>
       <td><input type="text" name="phone" value="${param.phone }" /></td>
      </tr>
      <tr>
       <td colspan="2">
        <input type="submit" value="Submit" id="submitButton" />
       </td>
      </tr>
     </table>
    </form>
  </body>
</html>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>My JSP 'searchCustomers.jsp' starting page</title>
  </head>
 
  <body>
   <c:if test="${requestScope.errorInfo != null}">
    <font color="red">${requestScope.errorInfo}</font>
    <br><br>
   </c:if>
   <form action="searchCustomerServlet" method="post">
     <table>
      <tr>
       <td>Name: </td>
       <td><input type="text" name="name" /></td>
      </tr>
      <tr>
       <td>Address: </td>
       <td><input type="text" name="address" /></td>
      </tr>
      <tr>
       <td>Phone: </td>
       <td><input type="text" name="phone" /></td>
      </tr>
      <tr>
       <td colspan="2">
        <input type="submit" value="Search" />&nbsp;&nbsp;
        <a href="addCustomer.jsp">Create New Customer</a>
       </td>
      </tr>
     </table>
    </form>
   
    <c:if test="${requestScope.customers != null}">
     <br>
     <hr>
     Search Results: <br>
     <c:if test="${!empty requestScope.customers}">
      <table border="1" cellpadding="8" cellspacing="0">
       <tr>
        <td>Name</td>
        <td>Address</td>
        <td>Phone</td>
        <td>Update</td>
        <td>Delete</td>
       </tr>
       <c:forEach items="${requestScope.customers}" var="customer">
        <tr>
         <td>${customer.name }</td>
         <td>${customer.address }</td>
         <td>${customer.phone }</td>
         <td><a href="customerUIServlet?id=${customer.id }&name=${param.name }

&address=${param.address }&phone=${param.phone }">Update</a></td>                           
         <td><a href="deleteCustomerServlet?id=${customer.id }&name=${param.name }

&address=${param.address }&phone=${param.phone }">Delete</a></td>
        </tr>
       </c:forEach>
      </table>
     </c:if>
     <c:if test="${empty requestScope.customers}">
      对不起, 没有满足条件的客户信息.
     </c:if>
    </c:if>
  </body>
</html>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>My JSP 'updateCustomer.jsp' starting page</title>
  </head>
 
  <body>
    <form action="updateCustomerServlet" method="post">
     <input type="hidden" name="updateid" value="${requestScope.customer.id }" />
     <input type="hidden" name="name" value="${param.name }" />
     <input type="hidden" name="address" value="${param.address }" />
     <input type="hidden" name="phone" value="${param.phone }" />
    
     <table>
      <tr>
       <td>Name: </td>
       <td><input type="text" name="updatename" value="${requestScope.customer.name }" /></td>
      </tr>
      <tr>
       <td>Address: </td>
       <td><input type="text" name="updateaddress" value="${requestScope.customer.address }" 

/></td>
      </tr>
      <tr>
       <td>Phone: </td>
       <td><input type="text" name="updatephone" value="${requestScope.customer.phone }" /></td>
      </tr>
      <tr>
       <td colspan="2">
        <input type="submit" value="Submit" id="submitButton" />
       </td>
      </tr>
     </table>
    </form>   
  </body>
</html>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>My JSP 'success.jsp' starting page</title>
  </head>
 
  <body>
          添加成功!<br>
    <a href="searchCustomers.jsp">return...</a>
  </body>
</html>

现在呢,就需要在配置文件中,将两个不相关联的东西连接起来: web.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5"
 xmlns="http://java.sun.com/xml/ns/javaee"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
 http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
  <servlet>
    <description>This is the description of my J2EE component</description>
    <display-name>This is the display name of my J2EE component</display-name>
    <servlet-name>AddCustomerServlet</servlet-name>
    <servlet-class>cn.itcast.webapp.servlet.AddCustomerServlet</servlet-class>
  </servlet>
  <servlet>
    <description>This is the description of my J2EE component</description>
    <display-name>This is the display name of my J2EE component</display-name>
    <servlet-name>SearchCustomersServlet</servlet-name>
    <servlet-class>cn.itcast.webapp.servlet.SearchCustomersServlet</servlet-class>
  </servlet>
  <servlet>
    <description>This is the description of my J2EE component</description>
    <display-name>This is the display name of my J2EE component</display-name>
    <servlet-name>DeleteCustomerServlet</servlet-name>
    <servlet-class>cn.itcast.webapp.servlet.DeleteCustomerServlet</servlet-class>
  </servlet>
  <servlet>
    <description>This is the description of my J2EE component</description>
    <display-name>This is the display name of my J2EE component</display-name>
    <servlet-name>CustomerUIServlet</servlet-name>
    <servlet-class>cn.itcast.webapp.servlet.CustomerUIServlet</servlet-class>
  </servlet>
  <servlet>
    <description>This is the description of my J2EE component</description>
    <display-name>This is the display name of my J2EE component</display-name>
    <servlet-name>UpdateCustomerServlet</servlet-name>
    <servlet-class>cn.itcast.webapp.servlet.UpdateCustomerServlet</servlet-class>
  </servlet>

 

 

  <servlet-mapping>
    <servlet-name>AddCustomerServlet</servlet-name>
    <url-pattern>/addCustomerServlet</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>SearchCustomersServlet</servlet-name>
    <url-pattern>/searchCustomerServlet</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>DeleteCustomerServlet</servlet-name>
    <url-pattern>/deleteCustomerServlet</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>CustomerUIServlet</servlet-name>
    <url-pattern>/customerUIServlet</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>UpdateCustomerServlet</servlet-name>
    <url-pattern>/updateCustomerServlet</url-pattern>
  </servlet-mapping>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
</web-app>

 

这个字一个小的应用程序就写完了.通过这个小例子 ,我们可以很清晰的了解的MVC 模式的开发流程,当然这个还

算是很简单的程序,今后在做大的应用开发时,MVC 模式的优势将会很明显的显示出来!记着,servlet的编写是一

个很重要的一个过程的!当然,一个WEB程序所有的一切都是密不可分的!但是servlet写的好坏会直接影响到程序

的性能的!我至少现在这么认为!