idea通过c3p0和QueryRunner对mysql进行增删改查2

学习spring之前，视频中先给我们一个任务就是用c3p0连接数据库来完成增删改查
一、准备

JAR包：

既然是连接数据库第一个最重要的就是数据库的驱动包
1.mysql-connection-java-5.1.44-bin.jar

接着就是
2.C3P0-0.9.2.1.jar以及
3.mchange-commons-java-0.2.3.4.jar

然后还少不了dbutils 使用的是
4.commons-dbutils-1.7.jar

一共是4个JAR包

二、配置
配置数据库连接:
创建c3p0-config.xml的配置文件，里面包含连接数据库的信息

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<default-config>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/mybatis</property>
		<property name="user">root</property>
		<property name="password">123</property>
		<property name="acquireIncrement">5</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">5</property>
		<property name="maxPoolSize">20</property>
	</default-config>
</c3p0-config>

创建C3P0Util.java 使用ComboPooledDataSource的对象获取数据库连接

package util;
 
import java.sql.Connection;
import java.sql.SQLException;
 
import javax.sql.DataSource;
 
import com.mchange.v2.c3p0.ComboPooledDataSource;
 
public class C3P0Util {
 
	private static ComboPooledDataSource ds=new ComboPooledDataSource();
	
	//获取数据源
	public static DataSource getDataSource(){
		return ds;
	}
	//获取一个连接
	public static Connection getConnection() throws SQLException{
		return ds.getConnection();
	}
}

由于没有使用Mybatis的逆向工程，
这里需要手动创建Customer.java 用于获取数据库表的所有列—实体类

package domain;
 
import java.io.Serializable;
 
public class Customer implements Serializable{
 
		private int cust_id;
		private String cust_name;
		private String cust_source;
		private String cust_industry;
		private String cust_level;
		private String cust_address;
		private String cust_phone;
		public int getCust_id() {
			return cust_id;
		}
		public void setCust_id(int cust_id) {
			this.cust_id = cust_id;
		}
		public String getCust_name() {
			return cust_name;
		}
		public void setCust_name(String cust_name) {
			this.cust_name = cust_name;
		}
		public String getCust_source() {
			return cust_source;
		}
		public void setCust_source(String cust_source) {
			this.cust_source = cust_source;
		}
		public String getCust_industry() {
			return cust_industry;
		}
		public void setCust_industry(String cust_industry) {
			this.cust_industry = cust_industry;
		}
		public String getCust_level() {
			return cust_level;
		}
		public void setCust_level(String cust_level) {
			this.cust_level = cust_level;
		}
		public String getCust_address() {
			return cust_address;
		}
		public void setCust_address(String cust_address) {
			this.cust_address = cust_address;
		}
		public String getCust_phone() {
			return cust_phone;
		}
		public void setCust_phone(String cust_phone) {
			this.cust_phone = cust_phone;
		}
		@Override
		public String toString() {
			return "Customer [cust_id=" + cust_id + ", cust_name=" + cust_name
					+ ", cust_source=" + cust_source + ", cust_industry="
					+ cust_industry + ", cust_level=" + cust_level
					+ ", cust_address=" + cust_address + ", cust_phone="
					+ cust_phone + "]";
		}		
}

三、接口
上面的配置文件和数据库表信息文件都已经写好了，
现在需要写两个接口提供所有的增删改查的方法
创建ICustomerDao.java 持久层接口 ，也就是最底层和数据库连接的接口类

package dao;
 
import java.util.List;
 
import domain.Customer;
 
public interface ICustomerDao {
 
	List<Customer> findAllCustomer();
 
	void saveCustomer(Customer customer);
 
	void updateCustomer(Customer customer);
 
	void deleteCustomer(int custId);
 
	Customer findCustomerById(int custId);
 
}

创建ICustomerService.java 业务层接口，
用于持久层和客户端连接的接口类，和Dao的方法一样

package service;
 
import java.util.List;
 
import domain.Customer;
 
public interface ICustomerService {
		
	//查询所有客户
	List<Customer> findAllCustomer();
	//保存客户信息
	void saveCustomer(Customer customer);
	//更改客户信息
	void updateCustomer(Customer customer);
	//根据Id删除对象
	void deleteCustomer(int cust_id);
	//根据ID查询用户，返回用户信息
	Customer findCustomerById(int cust_id);
}

四、实现类
创建CustomerDao.java 实现Dao接口，
利用C3P0里的QueryRunner类获取到数据库连接信息连接数据库

并将SQL语句传给数据库然后得到SQL的返回值。

package dao.impl;
 
import java.sql.SQLException;
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 dao.ICustomerDao;
import domain.Customer;
import util.C3P0Util;
 
//客户的持久层实现类
public class CustomerDao implements ICustomerDao {
 
	private QueryRunner runner=new QueryRunner(C3P0Util.getDataSource());
	@Override
	public List<Customer> findAllCustomer() {
		try {
			return runner.query("select * from cst_customer", new BeanListHandler<Customer>(Customer.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
 
	@Override
	public void saveCustomer(Customer customer) {
		try {
			runner.update("insert into cst_customer(cust_name,cust_source,cust_industry,cust_level,cust_address,cust_phone)values(?,?,?,?,?,?)",
					customer.getCust_name(),customer.getCust_source(),customer.getCust_industry(),
					customer.getCust_level(),customer.getCust_address(),customer.getCust_phone());
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
 
	}
 
	@Override
	public void updateCustomer(Customer customer) {
		try {
			runner.update("update cst_customer set cust_name=?,cust_source=?,cust_industry=?,cust_level=?,cust_address=?,cust_phone=? where cust_id=?",
					customer.getCust_name(),customer.getCust_source(),customer.getCust_industry(),
					customer.getCust_level(),customer.getCust_address(),customer.getCust_phone(),customer.getCust_id());
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
 
	@Override
	public void deleteCustomer(int custId) {
		try {
			runner.update("delete from cst_customer where cust_id=?",custId);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
 
	@Override
	public Customer findCustomerById(int custId) {
		try {
			return runner.query("select * from cst_customer where cust_id=?", new BeanHandler<Customer>(Customer.class),custId);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
 
}

创建CustomerService.java 实现业务层，
将需要查询的数据传给Dao层，并得到Dao层的返回值。

package service;
 
import java.util.List;
 
import dao.ICustomerDao;
import dao.impl.CustomerDao;
import domain.Customer;
 
//客户的业务层实现类
public class CustomerServiceImpl implements ICustomerService {
 
	private ICustomerDao customerDao=new CustomerDao();
	@Override
	public List<Customer> findAllCustomer() {
		// TODO Auto-generated method stub
		return customerDao.findAllCustomer();
	}
 
	@Override
	public void saveCustomer(Customer customer) {
		customerDao.saveCustomer(customer);
 
	}
 
	@Override
	public void updateCustomer(Customer customer) {
		customerDao.updateCustomer(customer);
 
	}
 
	@Override
	public void deleteCustomer(int custId) {
		customerDao.deleteCustomer(custId);
	}
 
	@Override
	public Customer findCustomerById(int custId) {
		// TODO Auto-generated method stub
		return customerDao.findCustomerById(custId);
	}
 
}

五、测试类

创建CustomerServiceTest.java
获取CustomerServiceImpl所有方法的测试方法

该类中在方法里创建CustomerServiceImpl对象
获取原始方法，
将参数传入Customer中，
通过CustomerService获取到返回值并打印。

package test;
 
import static org.junit.Assert.fail;
 
import java.util.List;
 
import org.junit.Test;
 
import domain.Customer;
import service.CustomerServiceImpl;
import service.ICustomerService;
 
public class CustomerServicerTest {
 
	@Test
	public void testFindAllCustomer() {
		ICustomerService cs=new CustomerServiceImpl();
		List<Customer> list=cs.findAllCustomer();
		for(Customer c: list){
			System.out.println(c);
		}
	}
 
	@Test
	public void testSaveCustomer() {
		ICustomerService cs=new CustomerServiceImpl();
		Customer c=new Customer();
		c.setCust_name("滴滴");
		c.setCust_source("dache");
		
		cs.saveCustomer(c);
	}
 
	@Test
	public void testUpdateCustomer() {
		fail("Not yet implemented");
	}
 
	@Test
	public void testDeleteCustomer() {
		fail("Not yet implemented");
	}
 
	@Test
	public void testFindCustomerById() {
		ICustomerService cs=new CustomerServiceImpl();
		Customer c=cs.findCustomerById(2);
		System.out.println(c);
	}
 
}

至此C3P0连接数据库进行单表增删改查功能完结。附上结构图