使用c3p0连接数据库实现增删改查

2019-11-26 08:42:09

字体：大中小

来源：转载

供稿：网友

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

一、准备

JAR包：

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

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

然后还少不了dbutils 使用的是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连接数据库进行单表增删改查功能完结。附上结构图