J2EE进阶之JDBC简单应用之客户管理系统十七

阳光穿透心脏的1/2处 2024-02-18 14:19 56阅读 0赞

## **JDBC应用之客户管理系统** ##

### 需求 对用户表进行查询修改删除操作 ###

![45KGFRV.png][]

### 步骤 严格MVC架构编写 ###

*  0项目结构图

![6If1eZJ.png][]

*  1：domain封装数据和创建数据库  
    Customer.java bean文件
    
        1 package jdbcdemo2.com.wsj.domain;
          2 
          3 import java.io.Serializable;
          4 import java.util.Date;
          5 
          6 /**
          7  * use wsj;
          8     create table customers(
          9     id varchar(100) primary key,
         10     name varchar(100) not null,
         11     gender varchar(10) not null,
         12     birthday date not null,
         13     phonenum varchar(100) not null,
         14     email varchar(100) not null,
         15     hobby varchar(100) not null,
         16     type varchar(100) not null,
         17     description varchar(255) not null
         18 );
         19  * @author Angus
         20  *
         21  */
         22 public class Customer implements Serializable{
         23     private String id;
         24     private String name;
         25     private String gender;//male female
         26     private Date birthday;
         27     private String phonenum;
         28     private String email;
         29     private String hobby;//吃饭,睡觉,学java
         30     private String type;//VIP 普通客户
         31     private String description;
         32     public String getId() {
         33         return id;
         34     }
         35     public void setId(String id) {
         36         this.id = id;
         37     }
         38     public String getName() {
         39         return name;
         40     }
         41     public void setName(String name) {
         42         this.name = name;
         43     }
         44     public String getGender() {
         45         return gender;
         46     }
         47     public void setGender(String gender) {
         48         this.gender = gender;
         49     }
         50     public Date getBirthday() {
         51         return birthday;
         52     }
         53     public void setBirthday(Date birthday) {
         54         this.birthday = birthday;
         55     }
         56     public String getPhonenum() {
         57         return phonenum;
         58     }
         59     public void setPhonenum(String phonenum) {
         60         this.phonenum = phonenum;
         61     }
         62     public String getEmail() {
         63         return email;
         64     }
         65     public void setEmail(String email) {
         66         this.email = email;
         67     }
         68     public String getHobby() {
         69         return hobby;
         70     }
         71     public void setHobby(String hobby) {
         72         this.hobby = hobby;
         73     }
         74     public String getType() {
         75         return type;
         76     }
         77     public void setType(String type) {
         78         this.type = type;
         79     }
         80     public String getDescription() {
         81         return description;
         82     }
         83     public void setDescription(String description) {
         84         this.description = description;
         85     }
         86     @Override
         87     public String toString() {
         88         return "Customer [id=" + id + ", name=" + name + ", gender=" + gender
         89                 + ", birthday=" + birthday + ", phonenum=" + phonenum
         90                 + ", email=" + email + ", hobby=" + hobby + ", type=" + type
         91                 + ", description=" + description + "]";
         92     }
         93 }
 *  2：编写业务service接口和实现

BusinessService接口

1 package jdbcdemo2.com.wsj.service;
      2 
      3 import java.util.List;
      4 
      5 import jdbcdemo2.com.wsj.domain.Customer;
      6 import jdbcdemo2.com.wsj.exception.IdIsNullException;
      7 
      8 public interface BusinessService {
      9     /**
     10      * 查询所有客户信息
     11      * @return
     12      */
     13     List<Customer> findAllCustomers();
     14     /**
     15      * 添加客户
     16      * @param customer
     17      */
     18     void addCustomer(Customer customer);
     19     /**
     20      * 根据id删除客户信息
     21      * @param customerId
     22      */
     23     void deleteCustomer(String customerId);
     24     /**
     25      * 更新客户信息
     26      * @param customer
     27      * @throws IdIsNullException 如果customer的id为null，抛出此异常
     28      */
     29     void updateCustomer(Customer customer) throws IdIsNullException;
     30     /**
     31      * 基本上都要写
     32      * 根据主键查询客户
     33      * @param customerId
     34      * @return 没有返回null
     35      */
     36     Customer findCustomerById(String customerId);
     37 }

BusinessServiceImpl实现类

package jdbcdemo2.com.wsj.service.impl;
    
    import java.util.List;
    import java.util.UUID;
    
    import jdbcdemo2.com.wsj.dao.CustomerDao;
    import jdbcdemo2.com.wsj.dao.impl.CustomerDaoMySQLImpl;
    import jdbcdemo2.com.wsj.domain.Customer;
    import jdbcdemo2.com.wsj.exception.IdIsNullException;
    import jdbcdemo2.com.wsj.service.BusinessService;
    
    public class BusinessServiceImpl implements BusinessService {
        private CustomerDao dao = new CustomerDaoMySQLImpl();
        @Override
        public List<Customer> findAllCustomers() {
            // TODO Auto-generated method stub
            return dao.findAll();
        }
    
        @Override
        public void addCustomer(Customer customer) {
            if(customer==null)
                throw new IllegalArgumentException("参数不全");
            customer.setId(UUID.randomUUID().toString());
            dao.save(customer);
        }
    
        @Override
        public void deleteCustomer(String customerId) {
            if(customerId==null||"".equals(customerId.trim()))
                throw new IllegalArgumentException("参数不全");
            dao.delete(customerId);
    
        }
    
        @Override
        public void updateCustomer(Customer customer) throws IdIsNullException {
            if(customer==null)
                throw new IllegalArgumentException("参数不全");
            if(customer.getId()==null)
                throw new IdIsNullException("Do you want save your data?if yes please use addCustomer method");
            dao.update(customer);
    
        }
    
        @Override
        public Customer findCustomerById(String customerId) {
            return dao.find(customerId);
        }
    
    }

*  3：编写dao接口和实现

CustomerDao接口 一般在创建业务需求时候自动生成

1 package jdbcdemo2.com.wsj.dao;
      2 
      3 import java.util.List;
      4 
      5 import jdbcdemo2.com.wsj.domain.Customer;
      6 
      7 public interface CustomerDao {
      8 
      9     List<Customer> findAll();
     10 
     11     void save(Customer customer);
     12 
     13     void delete(String customerId);
     14 
     15     void update(Customer customer);
     16 
     17     Customer find(String customerId);
     18 
     19 }

CustomerDaoMySQLImpl实现类

1 package jdbcdemo2.com.wsj.dao.impl;
      2 
      3 import java.sql.Connection;
      4 import java.sql.PreparedStatement;
      5 import java.sql.ResultSet;
      6 import java.sql.SQLException;
      7 import java.util.ArrayList;
      8 import java.util.List;
      9 
     10 import jdbcdemo2.com.wsj.dao.CustomerDao;
     11 import jdbcdemo2.com.wsj.domain.Customer;
     12 import jdbcdemo2.com.wsj.utils.JDBCUtils;
     13 
     14 public class CustomerDaoMySQLImpl implements CustomerDao{
     15 
     16     private Connection conn;
     17     private PreparedStatement stmt;
     18     private ResultSet rs;
     19 
     20     @Override
     21     public List<Customer> findAll() {
     22         try {
     23             conn = JDBCUtils.getConnection();
     24             stmt = conn.prepareStatement("select * from customers");
     25             rs = stmt.executeQuery();
     26             
     27             List<Customer> cs = new ArrayList<Customer>();
     28             
     29             while(rs.next()){
     30                 Customer c = new Customer();
     31                 c.setId(rs.getString("id"));
     32                 c.setName(rs.getString("name"));
     33                 c.setGender(rs.getString("gender"));
     34                 c.setBirthday(rs.getDate("birthday"));
     35                 c.setPhonenum(rs.getString("phonenum"));
     36                 c.setEmail(rs.getString("email"));
     37                 c.setHobby(rs.getString("hobby"));
     38                 c.setType(rs.getString("type"));
     39                 c.setDescription(rs.getString("description"));
     40                 cs.add(c);
     41             }
     42             return cs;
     43         } catch (SQLException e) {
     44             throw new RuntimeException(e);
     45         }finally{
     46             JDBCUtils.release(rs, stmt, conn);
     47         }
     48     }
     49 
     50     @Override
     51     public void save(Customer customer) {
     52         try{
     53             conn = JDBCUtils.getConnection();
     54             stmt = conn.prepareStatement("insert into customers (id,name,gender,birthday,phonenum,email,hobby,type,description) values (?,?,?,?,?,?,?,?,?) ");
     55             stmt.setString(1, customer.getId());
     56             stmt.setString(2, customer.getName());
     57             stmt.setString(3, customer.getGender());
     58             stmt.setDate(4,new java.sql.Date( customer.getBirthday().getTime()));
     59             stmt.setString(5, customer.getPhonenum());
     60             stmt.setString(6, customer.getEmail());
     61             stmt.setString(7, customer.getHobby());
     62             stmt.setString(8, customer.getType());
     63             stmt.setString(9, customer.getDescription());
     64             stmt.executeUpdate();
     65         }catch(Exception e){
     66             throw new RuntimeException(e);
     67         }finally{
     68             JDBCUtils.release(rs, stmt, conn);
     69         }
     70     }
     71 
     72     @Override
     73     public void delete(String customerId) {
     74         try{
     75             conn = JDBCUtils.getConnection();
     76             stmt = conn.prepareStatement("delete from customers where id=?");
     77             stmt.setString(1, customerId);
     78             stmt.executeUpdate();
     79         }catch(Exception e){
     80             throw new RuntimeException(e);
     81         }finally{
     82             JDBCUtils.release(rs, stmt, conn);
     83         }
     84     }
     85 
     86     @Override
     87     public void update(Customer customer) {
     88         try{
     89             conn = JDBCUtils.getConnection();
     90             stmt = conn.prepareStatement("update customers  set name=?,gender=?,birthday=?,phonenum=?,email=?,hobby=?,type=?,description=? where id=?");
     91             
     92             stmt.setString(1, customer.getName());
     93             stmt.setString(2, customer.getGender());
     94             stmt.setDate(3,new java.sql.Date( customer.getBirthday().getTime()));
     95             stmt.setString(4, customer.getPhonenum());
     96             stmt.setString(5, customer.getEmail());
     97             stmt.setString(6, customer.getHobby());
     98             stmt.setString(7, customer.getType());
     99             stmt.setString(8, customer.getDescription());
    100             stmt.setString(9, customer.getId());
    101             stmt.executeUpdate();
    102             
    103         }catch(Exception e){
    104             throw new RuntimeException(e);
    105         }finally{
    106             JDBCUtils.release(rs, stmt, conn);
    107         }
    108         
    109     }
    110 
    111     @Override
    112     public Customer find(String customerId) {
    113         try{
    114             conn = JDBCUtils.getConnection();
    115             stmt = conn.prepareStatement("select * from customers where id=?");
    116             stmt.setString(1, customerId);
    117             rs = stmt.executeQuery();
    118             if(rs.next()){
    119                 Customer c = new Customer();
    120                 c.setId(rs.getString("id"));
    121                 c.setName(rs.getString("name"));
    122                 c.setGender(rs.getString("gender"));
    123                 c.setBirthday(rs.getDate("birthday"));
    124                 c.setPhonenum(rs.getString("phonenum"));
    125                 c.setEmail(rs.getString("email"));
    126                 c.setHobby(rs.getString("hobby"));
    127                 c.setType(rs.getString("type"));
    128                 c.setDescription(rs.getString("description"));
    129                 return c;
    130             }
    131             return null;
    132         }catch(Exception e){
    133             throw new RuntimeException(e);
    134         }finally{
    135             JDBCUtils.release(rs, stmt, conn);
    136         }
    137     }
    138     
    139 }

这样业务逻辑基本写完了

*  4：写单元测试，测试dao 其中用到的工具类和属性文件和[JDBC基础文章][JDBC]中的一样
    
        1 package jdbcdemo2.com.wsj.test;
          2 
          3 import static org.junit.Assert.*;
          4 
          5 import java.util.Date;
          6 import java.util.List;
          7 
          8 import jdbcdemo2.com.wsj.domain.Customer;
          9 import jdbcdemo2.com.wsj.exception.IdIsNullException;
         10 import jdbcdemo2.com.wsj.service.BusinessService;
         11 import jdbcdemo2.com.wsj.service.impl.BusinessServiceImpl;
         12 
         13 import org.junit.Test;
         14 
         15 public class BusinessTest {
         16     private BusinessService s = new BusinessServiceImpl();
         17     
         18 
         19     @Test
         20     public void testAddCustomer() {
         21         Customer c = new Customer();
         22         c.setName("ybb");
         23         c.setGender("male");
         24         c.setBirthday(new Date());
         25         c.setPhonenum("119");
         26         c.setEmail("ybb@qq.com");
         27         c.setHobby("吃饭,睡觉,学java");
         28         c.setType("VIP");
         29         c.setDescription("纯爷们");
         30         s.addCustomer(c);
         31     }
         32     @Test
         33     public void testFindCustomerById() {
         34         Customer c = s.findCustomerById("8371b359-ec57-43dc-a6da-b4394cd5e669");
         35         assertNotNull(c);
         36         c = s.findCustomerById("2");
         37         assertNull(c);
         38     }
         39     @Test(expected=jdbcdemo2.com.wsj.exception.IdIsNullException.class)
         40     public void testUpdateCustomer() throws IdIsNullException{
         41         Customer c = new Customer();
         42         c.setDescription("纯娘们");
         43         s.updateCustomer(c);
         44     }
         45     
         46     
         47     @Test(expected=jdbcdemo2.com.wsj.exception.IdIsNullException.class)
         48     public void testFindAllCustomers() throws IdIsNullException {
         49         List<Customer> cs = s.findAllCustomers();
         50         assertEquals("纯娘们", cs.get(0).getDescription());
         51     }
         52     
         53     
         54     @Test
         55     public void testDeleteCustomer() {
         56         s.deleteCustomer("8371b359-ec57-43dc-a6da-b4394cd5e669");
         57     }
         58 
         59     
         60 
         61     
         62 
         63 }
 *  5；编写Servlet控制器
    
        1 package jdbcdemo2.com.wsj.controller;
          2 
          3 import java.io.IOException;
          4 import java.util.List;
          5 
          6 import javax.servlet.ServletException;
          7 import javax.servlet.http.HttpServlet;
          8 import javax.servlet.http.HttpServletRequest;
          9 import javax.servlet.http.HttpServletResponse;
         10 
         11 import jdbcdemo2.com.wsj.domain.Customer;
         12 import jdbcdemo2.com.wsj.service.BusinessService;
         13 import jdbcdemo2.com.wsj.service.impl.BusinessServiceImpl;
         14 
         15 /**
         16  * Servlet implementation class ControlServlet
         17  */
         18 public class ControlServlet extends HttpServlet {
         19     private static final long serialVersionUID = 1L;
         20        
         21     private BusinessService s = new BusinessServiceImpl();
         22     @Override
         23     protected void doGet(HttpServletRequest request,
         24             HttpServletResponse response) throws ServletException, IOException {
         25         String encoding = "UTF-8";
         26         request.setCharacterEncoding(encoding);
         27         response.setContentType("text/html;charset=" + encoding);
         28         String op = request.getParameter("op");
         29         System.out.println("有没有反应啊");
         30         if ("showAllCustomers".equals(op)) {
         31             showAllCustomers(request, response);
         32         }
         33     }
         34 
         35     // 查询所有客户信息
         36     private void showAllCustomers(HttpServletRequest request,
         37             HttpServletResponse response) throws ServletException, IOException {
         38         List<Customer> cs = s.findAllCustomers();
         39         // 封装数据到域对象中
         40         request.setAttribute("cs", cs);
         41         request.getRequestDispatcher("/listCustomers.jsp").forward(request,
         42                 response);
         43     }
         44 
         45     @Override
         46     protected void doPost(HttpServletRequest req, HttpServletResponse resp)
         47             throws ServletException, IOException {
         48         doGet(req, resp);
         49     }
         50 
         51 }
 *  6：编写web代码

通过index.HTML转发到Servlet设置另一个页面显示数据。这次只做了一个查询功能。。。

index.html 代码

listCustomer.html

1 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
      2 <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
      3 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
      4 <html>
      5   <head>
      6     <title>显示所有客户信息</title>
      7     
      8     <meta http-equiv="pragma" content="no-cache">
      9     <meta http-equiv="cache-control" content="no-cache">
     10     <meta http-equiv="expires" content="0">
     11     
     14     <style type="text/css">
     15         .odd{
     16             background-color: #c3f3c3;
     17         }
     18         .even{
     19             background-color: #f3c3f3;
     20         }
     21     </style>
     22   </head>
     23   
     24   <body>
     25     <table width="88%">
     26         <tr>
     27             <td>
     28                 <a href="">添加</a>
     29                 <a href="">删除</a>
     30             </td>
     31         </tr>
     32         <tr>
     33             <td>
     34                 <table border="1" width="100%">
     35                     <tr>
     36                         <th>选择</th>
     37                         <th>姓名</th>
     38                         <th>性别</th>
     39                         <th>生日</th>
     40                         <th>手机</th>
     41                         <th>邮件</th>
     42                         <th>爱好</th>
     43                         <th>类型</th>
     44                         <th>备注</th>
     45                         <th>操作</th>
     46                     </tr>
     47                     <c:forEach items="${cs}" var="c" varStatus="vs">
     48                         <tr class="${vs.index%2==0?'odd':'even' }">
     49                             <td>
     50                                 <input type="checkbox" name="ids" value="${c.id}">
     51                             </td>
     52                             <td>${c.name}</td>
     53                             <td>${c.gender=='male'?'男':'女' }</td>
     54                             <td>${c.birthday}</td>
     55                             <td>${c.phonenum}</td>
     56                             <td>${c.email}</td>
     57                             <td>${c.hobby}</td>
     58                             <td>${c.type}</td>
     59                             <td>${c.description}</td>
     60                             <td>
     61                                 [<a href="">修改</a>]
     62                                 [<a href="">删除</a>]
     63                             </td>
     64                         </tr>
     65                     </c:forEach>
     66                 </table>
     67             </td>
     68         </tr>
     69     </table>
     70   </body>
     71 </html>

效果如下:

![PBtfbW7.png][]

[45KGFRV.png]: http://i.imgur.com/45KGFRV.png
[6If1eZJ.png]: http://i.imgur.com/6If1eZJ.png
[JDBC]: http://blog.csdn.net/onceing/article/details/77185582
[PBtfbW7.png]: http://i.imgur.com/PBtfbW7.png