时间戳

首页 > 技术文章 > MVC案例的实现

MVC案例的实现

yangHS 2019-07-04 16:45 原文

要求一：加入C3P0数据源

——C3P0

——数据库驱动的jar包

1.首先在src目录下创建c3p0的配置文件c3p0-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
    <default-config>
        <!--连接池在无空闲连接可用时一次性创建的新数据库连接数，default：3-->
        <property name="acquireIncrement">5</property>
        <!--连接池初始化时创建的连接数，default：3-->
        <property name="initialPoolSize">10</property>
        <!--连接池保持的最小连接数，default：3-->
        <property name="minPoolSize">10</property>
        <!--连接池中拥有的最大连接数，如果获得新连接时，
        连接总数超过这个值则不会再获取新连接，而是等待其他连接释放-->
        <property name="maxPoolSize">50</property>
        <!--连接池为数据源缓存的preparedstatement的总数-->
        <property name="maxStatements">20</property>
        <!--连接池为数据源单个connection缓存的preparedstatement数-->
        <property name="maxStatementsPerConnection">5</property>

    </default-config>
    <named-config name="mysql">
        <property name="user">root</property>
        <property name="password">password</property>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/yang</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">10</property>
        <property name="maxPoolSize">50</property>
    </named-config>

</c3p0-config>

　　

2.创建一个JdbcUtil类，创建一个c3p0连接池，并且创建连接，销毁连接。

public class JdbcUtils {
    /**
     * 释放Connection连接
     * @param connection
     */
    public static void releaseConnection(Connection connection){

            if (connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

    }

    private static DataSource dataSource = null;

    static{
        dataSource = new ComboPooledDataSource("mysql");
    }

    public static Connection getConnection() throws SQLException {
        
        return dataSource.getConnection();
    }
}

 

3.可以创建一个测试类来测试，第一个测试是否可以获取一个连接。

  @Test
    public void testGetConnection() throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        System.out.println(connection);
    }

　　

要求二：编写DAO、DBUtils工具栏和CustomerDAO接口

提供CustomerDAO接口的实现类：CustomerDAOImpl

 

1.实体类：

package com.mvcapp.entity;

import java.io.Serializable;

public class Customer implements Serializable {
    private Integer id;
    private String name;
    private String password;

    public Customer(Integer id, String name, String password) {
        this.id = id;
        this.name = name;
        this.password = password;
    }

    public Customer(String name, String password) {
        this.name = name;
        this.password = password;
    }

    public Customer() {
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

　　

2.DAO

package com.mvcapp.dao;

import com.mvcapp.db.JdbcUtils;
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 java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

/**
 * 封装了基本的CRUD的方法，以供子类继承使用
 * 当前DAO直接在方法中获取数据库连接
 * 整个DAO采取DBUtils解决方案
 * @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];
                }
            }
        }
    }
    
    /**
     * 获取一条记录
     * @param sql
     * @param args
     * @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 (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.releaseConnection(connection);
        }
        return null;
    }

    /**
     * 获取一组值
     * @param sql
     * @param args
     * @return
     */
    public List<T> getForList(String sql,Object ...args){
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            return queryRunner.query(connection,sql,new BeanListHandler<>(clazz));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.releaseConnection(connection);
        }
        return  null;
    }

    /**
     * 返回某一个字段的值，例如返回某一条记录的customerName，或返回数据表中国有多少条记录
     * @param sql
     * @param args
     * @param <E>
     * @return
     */
    public <E> E getForValue(String sql,Object ...args){
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            return (E) queryRunner.query(connection,sql,new ScalarHandler(),args);
        } catch (SQLException 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 (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.releaseConnection(connection);
        }
    }
}

　　

3.CustomerDAO接口

package com.mvcapp.dao;

import com.mvcapp.entity.Customer;

import java.util.List;

public interface CustomerDAO {
    public List<Customer> getAll();

    public void save(Customer customer);

    public Customer get(Integer id);

    public void delete(Integer id);

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

　　

4.CustomerDAOImpl实现类：

package com.mvcapp.daoimpl;

import com.mvcapp.dao.CustomerDAO;
import com.mvcapp.dao.DAO;
import com.mvcapp.entity.Customer;

import java.util.List;

public class CustomerDAOImpl extends DAO<Customer> implements CustomerDAO {
    @Override
    public List<Customer> getAll() {
        String sql = "SELECT * FROM CUSTOMER";
        return getForList(sql);
    }

    @Override
    public void save(Customer customer) {
        String sql = "insert into customer(name,password) values(?,?)";
        update(sql,customer.getName(),customer.getPassword());
    }

    @Override
    public Customer get(Integer id) {
        String sql = "select * from customer where id=?";
        return get(sql,id);
    }

    @Override
    public void delete(Integer id) {
        String sql ="delete from customer where id=?";
        update(sql,id);
    }

    @Override
    public long getCountWithName(String name) {
        String sql = "Select count(id) from customer where name=?";
        return getForValue(sql,name);
    }
}

　　

5.CustomerDAOImplTest测试类：

package com.mvcapp.test;

import com.mvcapp.dao.CustomerDAO;
import com.mvcapp.daoimpl.CustomerDAOImpl;
import com.mvcapp.entity.Customer;
import org.junit.jupiter.api.Test;

import java.util.List;

public class CustomerDAOImplTest {
    private CustomerDAO customerDAO = new CustomerDAOImpl();
    @Test
    public void testGet(){
       Customer customer =  customerDAO.get(1);
        System.out.println(customer);
    }

    @Test
    public void testSave(){
        Customer customer = new Customer("Wang","222");
        customerDAO.save(customer);
    }

    @Test
    public void testdelete(){
        int id = 4;
        customerDAO.delete(id);
    }

    @Test
    public void testlist(){
        List<Customer> list = customerDAO.getAll();
        System.out.println(list);
    }

    @Test
    public void testcount(){
        long i = customerDAO.getCountWithName("SEN");
        System.out.println(i);
    }
}

　

推荐阅读