使用c3p0对mysql进行删除_使用c3p0连接数据库实现增删改查

学习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的配置文件，里面包含连接数据库的信息

com.mysql.jdbc.Driver

jdbc:mysql://localhost:3306/mybatis

root

123

5

10

5

20

创建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 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 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 findAllCustomer() {

try {

return runner.query("select * from cst_customer", new BeanListHandler(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.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 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 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连接数据库进行单表增删改查功能完结。附上结构图

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。