MVC案例

MVC案例分析：

                - 没有业务层，直接Servlet调用Dao，所以也没有业务操作。所有在DAO直接获取Connection对象                   

                -采用MVCDs设计模式

                -使用到的技术：

                                     mvc设计模式：JSP    Servlet  POJO

                                     数据库使用Mysql

                                      连接数据库使用C3P0数据库连接池

                                      JDBC工具使用DBUtils

                                      页面上的提示操作使用jQuery

技术难点：多个请求如何使用一个Servlet

                  模糊查询

                  在创建或者修改的情况，验证用户名是否已经被使用，并给出提示

基本架构

 1.创建数据表

Create table customers(
    id int primary key auto_increment,
    name varchar(30) not null unique,
    address varchar(30),
    phone varchar(30)
);

为 name 字段添加唯一约束：
alter table customers add constraint name_uk unique(name);

 

2.加入 C3P0 数据源
                      C3p0
                   数据库驱动的 jar 包

c3p0-config.xml

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

    <named-config name="helloc3p0">
        <!--提供获取连接的四个基本信息 -->
        <!--连接本地主机的话： jbdc:mysql://localhost:3306/test 可写成jbdc:mysql:///test -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/aff</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <!-- 对数据库连接池管理的基本信息 -->
        <!-- 当数据库连接池中的连接数不够时，c3p0一次向数据库服务器申请的连接数 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化时的连接数 -->
        <property name="initialPoolSize">10</property>
        <!-- 维护的最少连接数 -->
        <property name="minPoolSize">10</property>
        <!-- 维护的最多的连接数 -->
        <property name="maxPoolSize">100</property>
        <!-- 最多维护的Satement的个数 -->
        <property name="maxStatements">50</property>
        <!-- 每个连接最多使用Statement的个数 -->
        <property name="maxStatementsPerConnection">2</property>

    </named-config>
</c3p0-config>
    

 

3.编写 DAO、JdbcUtils工具类 和 CustomerDAO 接口

DAO

package com.aff.mvcapp.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.aff.mvcapp.db.JDBCUtilsC3P0;

/**
 * 封装了基本的CRUD方法，以供子类继承使用. 当前 DAO 直接在方法中获取数据库连接
 * 
 * @param <T>：当前DAO处理的实体类的类型是什么
 */
public class DAO<T> {
    private QueryRunner queryRunner = new QueryRunner();

    private Class<T> clazz;

    public DAO() {
        Type superclass = getClass().getGenericSuperclass();
        if (superclass instanceof ParameterizedType) {
            ParameterizedType parameterizedType = (ParameterizedType) superclass;
            Type[] typeArgs = parameterizedType.getActualTypeArguments();// 多个参数
            if (typeArgs != null && typeArgs.length > 0) {// 参数不为空，不少于一个
                if (typeArgs[0] instanceof Class) {
                    clazz = (Class<T>) typeArgs[0];
                }
            }
        }
    }

    // 返回某一个字段的值
    // 例如返回某一条记录的customerName，或返回数据表中有多少条记录等
    public <E> E getForValue(String sql, Object args) {
        Connection conn = null;
        try {
            conn = JDBCUtilsC3P0.getConnection();
            return (E) queryRunner.query(conn, sql, new ScalarHandler(), args);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsC3P0.closeResource(conn, null, null);
        }
        return null;
    }

    // 返回T 所对应的List
    public List<T> getForList(String sql, Object... args) {
        Connection conn = null;
        try {
            conn = JDBCUtilsC3P0.getConnection();
            return queryRunner.query(conn, sql, new BeanListHandler<>(clazz), args);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsC3P0.closeResource(conn, null, null);
        }
        return null;
    }

    // 返回对应 T 的一个实例类的对象
    public T get(String sql, Object... args) {
        Connection conn = null;
        try {
            conn = JDBCUtilsC3P0.getConnection();
            return queryRunner.query(conn, sql, new BeanHandler<>(clazz), args);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsC3P0.closeResource(conn, null, null);
        }
        return null;

    }

    // 封装了insert delete update操作
    public void update(String sql, Object... args) {
        Connection conn = null;
        try {
            conn = JDBCUtilsC3P0.getConnection();
            queryRunner.update(conn, sql, args);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsC3P0.closeResource(conn, null, null);
        }

    }

}

 

JdbcUtils工具类

JDBCUtilsC3P0.java

package com.aff.mvcapp.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import com.mchange.v2.c3p0.ComboPooledDataSource;

//使用C3P0数据库连接池
public class JDBCUtilsC3P0 {
    // 把池子拿到外边，连接池一个就够，需要的连接从池子中拿
    private static ComboPooledDataSource cbpds = new ComboPooledDataSource("helloc3p0");

    public static Connection getConnection() throws SQLException {
        Connection conn = cbpds.getConnection();
        return conn;
    }
    public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs) {
        // 7.资源的关闭
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
}

 

CustomerDAO

package com.aff.mvcapp.dao;
import java.util.List;
import com.aff.mvcapp.domian.Customer;
public interface CustomerDAO {

    public List<Customer> gerAll();

    public void save(Customer customer);

    public Customer get(Integer id);

    public void delete(Integer id);

    /**
     * 返回和 name 相等的记录数
     * 
     * @param name
     * @return
     */
    public long getCountWithName(String name);

    public void update(Customer customer);
}

 

4.提供 CustomerDAO 接口的实现类：CustomerDAOImpl

CustomerDAOImpl

package com.aff.mvcapp.dao.impl;
import java.util.List;
import com.aff.mvcapp.dao.CustomerDAO;
import com.aff.mvcapp.dao.DAO;
import com.aff.mvcapp.domian.Customer;

public class CustomerDAOImpl extends DAO<Customer> implements CustomerDAO {

    @Override
    public List<Customer> gerAll() {
        String sql = "select  id, name, address, phone from  customers";
        return getForList(sql);
    }

    @Override
    public void save(Customer customer) {
        String sql = "insert into customers(name,address,phone)values(?,?,?)";
        update(sql, customer.getName(), customer.getAddress(), customer.getPhone());
    }

    @Override
    public Customer get(Integer id) {
        String sql = "select id,name,address,phone from customers where id =?";
        return get(sql, id);
    }

    @Override
    public void delete(Integer 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);
    }

    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()); 
}

 

5.测试CustomerDAOImpl

TestCustomerDAOImpl

package com.aff.mvcapp.test;
import java.util.List;
import org.junit.Test;
import com.aff.mvcapp.dao.CustomerDAO;
import com.aff.mvcapp.dao.impl.CustomerDAOImpl;
import com.aff.mvcapp.domian.Customer;

public class TestCustomerDAOImpl {
    private CustomerDAO customerDAO = new CustomerDAOImpl();

    @Test
    public void testGerAll() {
        List<Customer> list = customerDAO.gerAll();
        list.forEach(System.out::println);

    }

    @Test
    public void testSave() {
        Customer customer = new Customer();
        customer.setAddress("苏州");
        customer.setName("芳芳");
        customer.setPhone("1451326318489");
        customerDAO.save(customer);
    }

    @Test
    public void testGetInteger() {
        Customer cust = customerDAO.get(1);
        System.out.println(cust);
    }

    @Test
    public void testDelete() {
        customerDAO.delete(1);
    }

    @Test
    public void testGetCountWithName() {
        long count = customerDAO.getCountWithName("芳芳");
        System.out.println(count);
    }
}

目录结构