本篇文章小编给大家分享一下使用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 { ListfindAllCustomer(); 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 { //查询所有客户 ListfindAllCustomer(); //保存客户信息 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 ListfindAllCustomer() { 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 ListfindAllCustomer() { // 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(); Listlist=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连接数据库进行单表增删改查功能完结。附上结构图