使用c3p0和QueryRunner实现基本的mvc

前言

在这些年的开发过程中，基本上都是直接使用ssh或者ssm框架，直接调用去实现业务功能。
最近在开源中国上看到一遍文章初学 Java Web 开发，请远离各种框架，从 Servlet 开发， 然后我开始反思， 好像离开了这些框架，忘了怎么去搭建一个基本后端链接，真的原生的web的知识了解的不是很深，从现在开始着手,说干就干，

知识点

• c3p0 链接池
• oracle
• commons-dbutils-1.3.jar
• 反射

c3p0-config.xml 连接池配置

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

<c3p0-config>

  <named-config name="mvcapp"> 
   	
   	<property name="user">uccbiz</property>
   	<property name="password">uccbiz</property>
   	<property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
   	<property name="jdbcUrl">jdbc:oracle:thin:@xxxx</property>
   
    <property name="acquireIncrement">5</property>
    <property name="initialPoolSize">10</property>
    <property name="minPoolSize">10</property>
    <property name="maxPoolSize">50</property>

    <property name="maxStatements">20</property> 
    <property name="maxStatementsPerConnection">5</property>
   </named-config>
    
</c3p0-config>
复制代码

JdbcUtils.java ，获取和释放链接

public class JdbcUtils {

	private static DataSource dataSource;

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

	/**
	 * 创建数据库链接
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnnection() throws SQLException {
		return dataSource.getConnection();
	}

	/**
	 * 释放链接
	 * 
	 * @param connection
	 */
	public static void releaseConnection(Connection connection) {
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

复制代码

Dao.java 与数据库交互 ，基本的数据操作

/**
 * 封装了基本的 CRUD 的方法, 以供子类继承使用 当前 DAO 直接在方法中获取数据库连接. 整个 DAO 采取 DBUtils 解决方案.
 * 
 * @param <T>:
 *            当前 DAO 处理的实体类的类型是什么
 * @Description
 * @author KoshiroPeng
 */
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[] actualTypeArguments = parameterizedType.getActualTypeArguments();
			if (actualTypeArguments != null && actualTypeArguments.length > 0) {
				if (actualTypeArguments[0] instanceof Class) {
					clazz = (Class<T>) actualTypeArguments[0];
				}
			}
		}
	}

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

	/**
	 * 返回T所对应的list
	 * 
	 * @param sql
	 * @param objects
	 * @return
	 */
	public List<T> getForList(String sql, Object... objects) {
		Connection conn = null;
		try {
			conn = JdbcUtils.getConnnection();
			return queryRunner.query(conn, sql, new BeanListHandler<>(clazz), objects);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.releaseConnection(conn);
		}
		return null;
	}

	/**
	 * 返回对应的T的一个实例对象
	 * 
	 * @param sql
	 * @param objects
	 * @return
	 */
	public T get(String sql, Object... objects) {
		Connection connnection = null;
		try {
			connnection = JdbcUtils.getConnnection();
			return queryRunner.query(connnection, sql, new BeanHandler<>(clazz), objects);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.releaseConnection(connnection);
		}
		return null;
	}

	/**
	 * 该方法封装了 INSERT、DELETE、UPDATE 操作
	 * 
	 * @param sql
	 * @param objects
	 */
	public void update(String sql, Object... objects) {
		Connection connnection = null;
		try {
			connnection = JdbcUtils.getConnnection();
			queryRunner.update(connnection, sql, objects);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.releaseConnection(connnection);
		}
	}

}

复制代码

Customer pojo实体类

package com.netinfo.mvc.entity;

public class Customer {

	private Integer id;

	private String name;

	private String address;

	private String phone;

	public Customer() {

	}

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

	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 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 + "]";
	}

}

复制代码

CustomerDao.java 接口，定义操作数据库的方法

package com.netinfo.mvc.dao;

import java.util.List;

import com.netinfo.mvc.entity.Customer;

public interface CustomerDao {
	
	public List<Customer> getForListWithCriteriaCustomer(CriteriaCustomer criteriaCustomer);
	
	public List<Customer> getAll();
	
	public Customer getCustomer(Integer id);
	
	public int getCustomerCount(String name);
	
	public void insert(Customer customer);
	
	public void delete(Integer id);
	
	public void update(Customer customer);
	

}

复制代码

CustomerDaoImpl.java 实现

package com.netinfo.mvc.dao.imp;

import java.util.List;

import com.netinfo.mvc.dao.CriteriaCustomer;
import com.netinfo.mvc.dao.CustomerDao;
import com.netinfo.mvc.dao.DAO;
import com.netinfo.mvc.entity.Customer;

public class CustomerDaoImpl extends DAO<Customer> implements CustomerDao {

	@Override
	public List<Customer> getForListWithCriteriaCustomer(CriteriaCustomer criteriaCustomer) {
		String sql = "select * from customers where name like ? and address like ? and phone like ?";
		return getForList(sql, criteriaCustomer.getName(), criteriaCustomer.getAddress(), criteriaCustomer.getPhone());
	}

	@Override
	public List<Customer> getAll() {
		String sql = "select * from customers";
		return getForList(sql);
	}

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

	@Override
	public int getCustomerCount(String name) {
		String sql = "select count(*) from customers WHERE name = ?";
		return getForValue(sql, name);
	}

	@Override
	public void insert(Customer customer) {
		String sql = "INSERT INTO customers(name, address, phone) VALUES(?,?,?)";
		update(sql, customer);
	}

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

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

}

复制代码

CustomerDAOJdbcImplTest.java 测试

package com.netinfo.mvc.test;

import java.util.List;

import org.junit.Test;

import com.netinfo.mvc.dao.CriteriaCustomer;
import com.netinfo.mvc.dao.CustomerDao;
import com.netinfo.mvc.dao.imp.CustomerDaoImpl;
import com.netinfo.mvc.entity.Customer;

/**
 * 接口测试类
 * 
 * @Description
 * @author KoshiroPeng
 */
public class CustomerDAOJdbcImplTest {

	private CustomerDao customerDAO = new CustomerDaoImpl();

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

	@Test
	public void testGetCustomer() {
		Customer customer = customerDAO.getCustomer(1);
		System.out.println(customer);
	}

	@Test
	public void testGetCustomerCount() {
		int count = customerDAO.getCustomerCount("");
		System.out.println(count);
	}

	@Test
	public void testInsert() {
		Customer customer = new Customer();
		customer.setAddress("beijing");
		customer.setId(12);
		customer.setName("小李子");
		customerDAO.insert(customer);
	}

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

	@Test
	public void testUpdateCustomer() {
		Customer customer = new Customer("xiaomi", "北京", "15800000000");
		customerDAO.update(customer);
	}

}

复制代码