<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>

  <named-config name="mvcapp">

    <property name="user">root</property>

    <property name="password">root</property>

    <property name="driverClass">com.mysql.jdbc.Driver</property>

    <property name="jdbcUrl">jdbc:mysql:///test</property>

    <property name="acquireIncrement"></property>

    <property name="initialPoolSize"></property>

    <property name="minPoolSize"></property>

    <property name="maxPoolSize"></property>

    <!-- intergalactoApp adopts a different approach to configuring statement caching -->

    <property name="maxStatements"></property>

    <property name="maxStatementsPerConnection"></property>

  </named-config>

</c3p0-config>

package com.cuit.mvc.db;

import java.sql.Connection;

import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**

 * JDBC操作工具

 * @author 白宁超 http://www.cnblogs.com/baiboy/

 */

public class JdbcUtils {

    /**

     * 释放Connection链接

     * @param connection

     */

    public static void releaseConnection(Connection connection){

        try{

            if(connection!=null) connection.close();

        }catch(Exception e){

            e.printStackTrace();

        }

    }

    private static DataSource dataSource = null;

    static{

        dataSource=new ComboPooledDataSource("mvcapp");

    }

    /**

     * 返回数据源的一个Connection对象

     * @return

     * @throws SQLException

     */

    public static Connection getConnection() throws SQLException{

        return dataSource.getConnection();

    }

}

package com.cuit.mvc.model;

public class Customer {

    private int  id;

    private String name;

    private String address;

    private String phone;

    public int getId() {

        return id;

    }

    public Customer() {

    }

    public Customer(String name, String address, String phone) {

        this.name = name;

        this.address = address;

        this.phone = phone;

    }

    public void setId(int id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public String getAddress() {

        return address;

    }

    public void setAddress(String address) {

        this.address = address;

    }

    public String getPhone() {

        return phone;

    }

    public void setPhone(String phone) {

        this.phone = phone;

    }

    @Override

    public String toString(){

        return "Customer [id="+id+",name="+name+",address"+address+

                ",phone="+phone+"]";

    }

}

package com.cuit.mvc.dao;

import java.lang.reflect.ParameterizedType;

import java.lang.reflect.Type;

import java.sql.Connection;

import java.util.List;

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.handlers.BeanHandler;

import org.apache.commons.dbutils.handlers.BeanListHandler;

import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.cuit.mvc.db.JdbcUtils;

/**

 * 封装了基本的CRUD的方法，以供子类继承使用

 * 当前DAO直接在方法中获取数据库连接

 * @param <T> ：当前DAO处理实体的类型是什么

 * @author 白宁超 http://www.cnblogs.com/baiboy/

 *

 */

public class DAO<T> {

    //此步骤前需要/lib加入commons-dbutils-xx.jar

    private QueryRunner  queryRunner=new QueryRunner();

    private Class<T> clazz;

    public DAO(){

        //Type通过Ctrl+Shift+O进行反射Type选择

        Type superClass=getClass().getGenericSuperclass();

        if(superClass instanceof ParameterizedType){

            ParameterizedType parameterizedType=(ParameterizedType)superClass;

            Type[] typeArgs=parameterizedType.getActualTypeArguments();

            if(typeArgs!=null && typeArgs.length>){

                if(typeArgs[] instanceof Class)    clazz=(Class<T>)typeArgs[];

            }

        }

    }

    /**

     * 返回某一个字段的值，或者返回数据表中有多少条记录等。

     * @param sql：SQL语句

     * @param args：填充SQL语句的占位符

     * @return

     */

    public <E> E getForValue(String sql,Object ... args) {

        Connection connection=null;

        try{

            connection=JdbcUtils.getConnection();

            return (E) queryRunner.query(connection,sql,new ScalarHandler<T>(),args);

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            JdbcUtils.releaseConnection(connection);

        }

        return null;

    }

    /**

     * 返回T所对应的List

     * @param sql：SQL语句

     * @param args：填充SQL语句的占位符

     * @return

     */

    public List<T> getForList(String sql,Object ... args){

        Connection connection=null;

        try{

            connection=JdbcUtils.getConnection();

            return queryRunner.query(connection,sql,new BeanListHandler<>(clazz),args);

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            JdbcUtils.releaseConnection(connection);

        }

        return null;

    }

    /**

     * 返回对应T的一个实体类对象

     * @param sql：SQL语句

     * @param args：填充SQL语句的占位符

     * @return

     */

    public T get(String sql,Object ... args){

        Connection connection=null;

        try{

            connection=JdbcUtils.getConnection();

            return queryRunner.query(connection,sql,new BeanHandler<>(clazz),args);

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            JdbcUtils.releaseConnection(connection);

        }

        return null;

    }

    /**

     * 该方法封装了INSERT、DELETE、UPDATE操作

     * @param sql：SQL语句

     * @param args：填充SQL语句的占位符

     */

    public void update(String sql,Object ... args){

        Connection connection=null;

        try{

            connection=JdbcUtils.getConnection();

            queryRunner.update(connection,sql,args);

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            JdbcUtils.releaseConnection(connection);

        }

    }

}

package com.cuit.mvc.dao;

import java.util.List;

import com.cuit.mvc.model.CriteriaCustomer;

import com.cuit.mvc.model.Customer;

public interface CustomerDAO {

    public List<Customer> getAll();//获取Customer列表信息

    public void save(Customer customer);//对Customer的添加,通过CTRL+T转到定义

    public void update(Customer customer);//对Customer的更新,通过CTRL+T转到定义

    public Customer get(int id);//获取Customer实体

    public void delete(int id);//根据id进行删除

    public long getCountWithName(String name);//返回name相等的记录数

    //cc封装了查询条件，返回查询条件的list

    public List<Customer> getForListWithCriteriaCustomer(CriteriaCustomer cc);

}

package com.cuit.mvc.dao.impl;

import java.util.List;

import com.cuit.mvc.dao.CustomerDAO;

import com.cuit.mvc.dao.DAO;

import com.cuit.mvc.model.CriteriaCustomer;

import com.cuit.mvc.model.Customer;

public class CustomerDAOJdbcImpl extends DAO<Customer> implements CustomerDAO{

    public List<Customer> getForListWithCriteriaCustomer(CriteriaCustomer cc) {

        String sql="select * from customers where name like ? and address like ? "

                + "and phone like ?";

        //修改了CriteriaCustomer的getter方法:使其返回字符串中有%%

        //若返回值为null返回%%，若不返回null则返回："%"+字段本身的值+"%"

        //如上效果如：cc.getName()==null？%%:%+name+%

        System.out.println(sql);

        return getForList(sql,cc.getName(),cc.getAddress(),cc.getPhone());

    }

    @Override

    public List<Customer> getAll() {

        String sql="select * from customers";

        return getForList(sql);

    }

    @Override

    public void save(Customer customer) {

        String sql="insert customers(name,address,phone) values(?,?,?)";

        update(sql, customer.getName(),customer.getAddress(),customer.getPhone());

    }

    @Override

    public Customer get(int id) {

        String sql="select * from customers where id=?";

        return get(sql,id);

    }

    @Override

    public void delete(int id) {

        String sql="delete  from customers where id=?";

        update(sql, id);

    }

    @Override

    public long getCountWithName(String name) {

        String sql="select count(id) from customers where name=?";

        return getForValue(sql, name);

    }

    @Override

    public void update(Customer customer) {

        String sql="update customers set name=?,address=?,phone=? where id=?";

        update(sql,customer.getName(),customer.getAddress(),customer.getPhone(),customer.getId());

    }

}

package com.cuit.mvc.servlet;

import java.io.IOException;

import java.io.PrintWriter;

import java.lang.reflect.Method;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.cuit.mvc.dao.CustomerDAO;

import com.cuit.mvc.dao.factory.CustomerDAOFactory;

import com.cuit.mvc.dao.impl.CustomerDAOJdbcImpl;

import com.cuit.mvc.dao.impl.CustomerDAOXMLImpl;

import com.cuit.mvc.model.CriteriaCustomer;

import com.cuit.mvc.model.Customer;

public class CustomerServlet extends HttpServlet {

    //private CustomerDAO customerDAO=new CustomerDAOJdbcImpl();

    //private CustomerDAO customerDAO=new CustomerDAOXMLImpl();

    private CustomerDAO customerDAO=CustomerDAOFactory.getInstance().getCustomerDAO();

    public void doGet(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

        doPost(request, response);

    }

    /*public void doPost(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

        String method=request.getParameter("method");

        switch (method) {

        case "add":  add(request,response); break;

        case "query": query(request,response); break;

        case "delete": delete(request,response);break;

        default: break;

        }

    }*/

    @Override

    protected void doPost(HttpServletRequest req, HttpServletResponse resp)

            throws ServletException, IOException {

        //1 获取servlet路径 诸如：/add.do

        String servletPath=req.getServletPath().substring();

        //去除/和.do得到类似于add这样字符串

        String methodName=servletPath.substring(,servletPath.length()-);

        //System.out.println(methodName);

        try {

            //利用反射获取获取methodName对应的方法

            Method method = getClass().getDeclaredMethod(methodName, HttpServletRequest.class,HttpServletResponse.class);

            //利用反射获取方法

            method.invoke(this, req,resp);

        } catch (Exception e) {

            //出错时候响应出来

            resp.sendRedirect("error.jsp");

        }

    }

    private void edit(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{

        String forwordPath="/error.jsp";

        //1 获取请求参数id

        String idstr=request.getParameter("id");

        //2 调用CustomeDAO的customerDAO.get(id)获取和id对应的Customer对象customer

        try{

            Customer customer=customerDAO.get(Integer.parseInt(idstr));

            if(customer!=null){

                forwordPath="/updatecustomer.jsp";

                //3 将customer放在request中

                request.setAttribute("customer", customer);

            }

        }catch(Exception e){}

        //4 响应updatecustomer.jsp页面：转发

        request.getRequestDispatcher(forwordPath).forward(request, response);

    }

    private void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{

        //1 获取请求参数:id,name,address,phone,oldname

        String id=request.getParameter("id");

        String name=request.getParameter("name");

        String oldname=request.getParameter("oldname");

        String address=request.getParameter("address");

        String phone=request.getParameter("phone");

        //2  检验name是否被占用

        //2.1  比较name和oldname是否相同，若相同name可用，oldname.equals(name)不如equalsIgnoreCase，数据库默认大小写一致的，而equals忽略大小写

        if(!oldname.equalsIgnoreCase(name)){

            //不相同，调用CustomerDAO的getCountWithName(String name)获取name在数据库中是否存在

            long count=customerDAO.getCountWithName(name);

            //大于0， 响应updatecustomer.jsp页面：通过转发响应newcustomer.jsp

            if(count>){

                // 通过request.getAttribute("message")显示信息，在页面上request.getAttribute("message")的方式显示

                // 表单据回显。address,phone显示提交的新值， name显示oldname，而不是新值

                request.setAttribute("message", "用户名["+name+"]已经被占用，请重新填写！");

                // 方法结束

                request.getRequestDispatcher("/updatecustomer.jsp").forward(request, response);

                return;

            }

        }

        //3 若验证通过，把表单参数封装为一个Customer对象customer

        Customer customer=new Customer(name,address,phone);

        customer.setId(Integer.parseInt(id));

        //4 调用CustomerDAO的update(Customer customer)执行更新操作

        customerDAO.update(customer);

        //5 重定向到query.do

        response.sendRedirect("query.do");

    }

    //模糊查询

    private void query(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{

        String name=request.getParameter("name");

        String address=request.getParameter("address");

        String phone=request.getParameter("phone");

        CriteriaCustomer cc=new CriteriaCustomer(name,address,phone);

        //1 调用CustomerDAO的getALl方法得到Customer集合

        //List<Customer> sustomers=customerDAO.getAll();获取所有信息列表

        List<Customer> customers=customerDAO.getForListWithCriteriaCustomer(cc);

        //2 把customer的集合放入request

        request.setAttribute("customers", customers);

        //3 转发页面index.jsp（不能使用重定向）

        request.getRequestDispatcher("/index.jsp").forward(request,response);

    }

    private void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{

        String idstr=request.getParameter("id").trim();

        int id=;

        try{

            id=Integer.parseInt(idstr);

            customerDAO.delete(id);

        }catch(Exception e){}

        response.sendRedirect("query.do");

    }

    //此方法名称跟页面add添加的action中add.do匹配

    private void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{

        //1 获取表单参数：name，address，phone

        String name=request.getParameter("name");

        String address=request.getParameter("address");

        String phone=request.getParameter("phone");

        //2  检验name是否被占用

        //2.1  调用CustomerDAO的getCountWithName(String name)获取name在数据库中是否存在

        long count=customerDAO.getCountWithName(name);

        if(count>){

        //2.2 若返回值大于0，则相应newcustomer.jsp页面：①在此页面显示一个错误信息②此表单值可以回显

        //     通过request.getAttribute("message")显示信息

        //     通过value="<%=request.getParameter("name")==null?"":request.getParameter("name")%>"回显

            request.setAttribute("message", "用户名["+name+"]已经被占用，请重新填写！");

            request.getRequestDispatcher("/newcustomer.jsp").forward(request, response);

            return;

        }

        //3 若验证通过，把表单参数封装为一个Customer对象customer

        Customer customer=new Customer(name,address,phone);

        //4 调用CustomerDAO的save(Customer customer)执行保存操作

        customerDAO.save(customer);

        //5 重定向到success.jsp页面

        response.sendRedirect("success.jsp");

    }

}

package com.cuit.mvc.test;

import static org.junit.Assert.*;

import java.util.List;

import org.junit.Test;

import com.cuit.mvc.dao.CustomerDAO;

import com.cuit.mvc.dao.impl.CustomerDAOJdbcImpl;

import com.cuit.mvc.model.CriteriaCustomer;

import com.cuit.mvc.model.Customer;

public class CustomerDAOJdbcImplTest {

    private  CustomerDAO customerDAO=new CustomerDAOJdbcImpl();

    @Test

    public void getForListWithCriteriaCustomer(){

        CriteriaCustomer cc=new CriteriaCustomer("Tom", null, null);

        List<Customer> customers=customerDAO.getForListWithCriteriaCustomer(cc);

        System.out.println(customers);

    }

    @Test

    public void testGetAll() {

        List<Customer> customers=customerDAO.getAll();

        System.out.println(customers);

    }

    @Test

    public void testSaveCustomer() {

        Customer customer=new Customer("Baijing","Shanghai","134-2345-9086");

        customerDAO.save(customer);

    }

    @Test

    public void testGetInt() {

        Customer cust=customerDAO.get();

        System.out.println(cust);

    }

    @Test

    public void testDelete() {

        customerDAO.delete();

    }

    @Test

    public void testGetCountWithName() {

        long count=customerDAO.getCountWithName("Tom");

        System.out.println(count);

    }

}

<%@page import="com.cuit.mvc.model.Customer"%>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

  <head>

    <base href="<%=basePath%>">

    <title>My JSP 'index.jsp' starting page</title>

  <script type="text/javascript" src="scripts/jquery.min.js"></script>

  <script type="text/javascript">

     $(function(){

         $('.delete').click(function(){

            var content=$(this).parent().parent().find("td:eq(1)").text();

            var flag=confirm("确定要删除此"+content+"信息？");

            return flag;

         });

     });

  </script>

  </head>

  <body>

      <form action="query.do">

         <table>

             <tr>

               <td>CustomerName:</td>

               <td><input type="text" name="name"/></td>

             </tr>

              <tr>

               <td>CustomerAddress:</td>

               <td><input type="text" name="address"/></td>

             </tr>

              <tr>

               <td>CustomerPhone:</td>

               <td><input type="text" name="phone"/></td>

             </tr>

              <tr>

               <td><input type="submit" value="Query"/></td>

               <td><a href="newcustomer.jsp">Create New Customer</a></td>

             </tr>

         </table>

      </form>

      <br/><br/>

      <%

          List<Customer> customers=(List<Customer>)request.getAttribute("customers");

          if(customers!=null && customers.size()>){

       %>

       <hr>

       <br/><br/>

       <table border="" cellpadding="" cellspacing="">

            <tr>

               <th>ID</th>

               <th>CustomerName</th>

               <th>CustomerAddress</th>

               <th>CustomerPhone</th>

               <th>Update/Delete</th>

            </tr>

            <%

              for(Customer customer:customers){

            %>

            <tr>

               <td class="id"><%=customer.getId() %></td>

               <td><%=customer.getName() %></td>

               <td><%=customer.getAddress() %></td>

               <td><%=customer.getPhone() %></td>

               <td>

                   <a  href="edit.do?id=<%=customer.getId() %>">Update</a>

                   <a href="delete.do?id=<%=customer.getId() %>" class="delete">Delete</a>

               </td>

               </tr>

               <%

                }

                %>

       </table>

       <%

          }

        %>

  </body>

</html>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

  <head>

    <base href="<%=basePath%>">

    <title>My JSP 'error.jsp' starting page</title>

    <meta http-equiv="pragma" content="no-cache">

    <meta http-equiv="cache-control" content="no-cache">

    <meta http-equiv="expires" content="">

    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">

    <meta http-equiv="description" content="This is my page">

    <!--

    <link rel="stylesheet" type="text/css" href="styles.css">

    -->

  </head>

  <body>

    <h4>对不起没有您请求的页面</h4>

    <img style=" height:200px; width: 200; margin: 0 auto;" src="imgs/error.jpg"></img><br/><br>

    <p style="font-size: 25px; color: red;">对不起访问失败！</p>

  </body>

</html>

<%@ 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>Insert title here</title>

</head>

<body>

    <%

        Object mes=request.getAttribute("message");

        if(mes!=null){

           out.print("<br>");

           out.print(mes);

           out.print("<br>");

           out.print("<br>");

        }

     %>

    <h1>添加一条新的customer信息</h1>

     <!--此处add.do依赖于CustomerServlet中的add方法名  -->

     <form action="add.do">

         <table>

             <tr>

               <td>CustomerName:</td>

               <td><input type="text" name="name"

                    value="<%=request.getParameter("name")==null?"":request.getParameter("name")%>"/></td>

             </tr>

              <tr>

               <td>CustomerAddress:</td>

               <td><input type="text" name="address"

                   value="<%=request.getParameter("address")==null?"":request.getParameter("address")%>"/></td>

             </tr>

              <tr>

               <td>CustomerPhone:</td>

               <td><input type="text" name="phone"

                    value="<%=request.getParameter("phone")==null?"":request.getParameter("phone")%>"/></td>

             </tr>

              <tr>

               <td colspan=""><input type="submit" value="Submit"/></td>

             </tr>

         </table>

      </form>

</body>

</html>

<%@ 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>Insert title here</title>

</head>

<body>

    <h2>成功添加，保存成功！</h2><br/><br/>

    <h2><a href="index.jsp">Back Index</a></h2>

</body>

</html>

<%@page import="com.cuit.mvc.model.Customer"%>

<%@ 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>Insert title here</title>

</head>

<body>

   <%

        Object mes=request.getAttribute("message");

        if(mes!=null){

           out.print("<br>");

           out.print(mes);

           out.print("<br>");

           out.print("<br>");

        }

        String id=null;

        String name=null;

        String oldname=null;

        String address=null;

        String phone=null;

        Customer customer=(Customer)request.getAttribute("customer");

        if(customer!=null){

           id=customer.getId()+"";

           address=customer.getAddress();

           name=customer.getName();

           oldname=customer.getName();

           phone=customer.getPhone();

        }else{

           id=request.getParameter("id");

           name=request.getParameter("oldname");

           oldname=request.getParameter("oldname");

           address=request.getParameter("address");

           phone=request.getParameter("phone");

        }

     %>

    <h1>更新一条新的customer信息</h1>

     <!--此处add.do依赖于CustomerServlet中的add方法名  -->

     <form action="update.do">

         <input type="hidden" name="id" value="<%=customer.getId()%>"/>

         <input type="hidden" name="oldname" value="<%=oldname%>"/>

         <table>

             <tr>

               <td>CustomerName:</td>

               <td><input type="text" name="name" value="<%=name%>"/></td>

             </tr>

              <tr>

               <td>CustomerAddress:</td>

               <td><input type="text" name="address"  value="<%=address %>"/></td>

             </tr>

              <tr>

               <td>CustomerPhone:</td>

               <td><input type="text" name="phone" value="<%=phone%>"/></td>

             </tr>

              <tr>

               <td colspan=""><input type="submit" value="Submit"/></td>

             </tr>

         </table>

      </form>

</body>

</html>