spring学习整合C3p0小案例_private queryrunner runner;

目录

准备代码

 C3p0 xml配置文件

获取连接池的工具类 

使用junit测试

整合spring部分代码更改：

自己写的类代码块的依赖

注意不能忽略调用包类的依赖（这部分需要看包的源代码）

---

准备代码

业务层机持久层的分离并且整合C3p0,

导入jar包：

bean类

cn.pro.domain.CustormerBean

package cn.pro.domain;
 
import java.util.Date;
 
public class CustormerBean {
	private int custormer_id;
	private String custormer_name;
	private String custormer_add;
	private Date custormer_birth;
	private int custormer_tel;
	private String custormer_sex;
	public int getCustormer_id() {
		return custormer_id;
	}
	@Override
	public String toString() {
		return "CustormerBean [custormer_id=" + custormer_id
				+ ", custormer_name=" + custormer_name + ", custormer_add="
				+ custormer_add + ", custormer_birth=" + custormer_birth
				+ ", custormer_tel=" + custormer_tel + ", custormer_sex="
				+ custormer_sex + "]";
	}
	public void setCustormer_id(int custormer_id) {
		this.custormer_id = custormer_id;
	}
	public String getCustormer_name() {
		return custormer_name;
	}
	public void setCustormer_name(String custormer_name) {
		this.custormer_name = custormer_name;
	}
	public String getCustormer_add() {
		return custormer_add;
	}
	public void setCustormer_add(String custormer_add) {
		this.custormer_add = custormer_add;
	}
	public Date getCustormer_birth() {
		return custormer_birth;
	}
	public void setCustormer_birth(Date custormer_birth) {
		this.custormer_birth = custormer_birth;
	}
	public int getCustormer_tel() {
		return custormer_tel;
	}
	public void setCustormer_tel(int custormer_tel) {
		this.custormer_tel = custormer_tel;
	}
	public String getCustormer_sex() {
		return custormer_sex;
	}
	public void setCustormer_sex(String custormer_sex) {
		this.custormer_sex = custormer_sex;
	}
 
 }

cn.pro.service.ICustormerService

package cn.pro.service;
 
import java.util.List;
 
import cn.pro.domain.CustormerBean;
 
public interface ICustormerService {
	void  addcustormer(CustormerBean custormer);
	void deletecustormer(int custormer_id);
	public CustormerBean getcustormer(String custormer_name);
	void updatecustormer(CustormerBean custormer);
	public List<CustormerBean> getall();
}

cn.pro.service.impl.ICustormerServiceImpl

package cn.pro.service.impl;
 
import java.util.List;
 
import cn.pro.dao.CustormerDao;
import cn.pro.dao.CustormerDaoImpl;
import cn.pro.domain.CustormerBean;
import cn.pro.service.ICustormerService;
 
public class ICustormerServiceImpl implements ICustormerService {
	private CustormerDao custormerdao = new  CustormerDaoImpl();
	@Override
	public void addcustormer(CustormerBean custormer) {
		// TODO Auto-generated method stub
		custormerdao.addcustormer(custormer);
	}
 
	@Override
	public void deletecustormer(int custormer_id) {
		// TODO Auto-generated method stub
		custormerdao.deletecustormer(custormer_id);
	}
 
	@Override
	public CustormerBean getcustormer(String custormer_name) {
		// TODO Auto-generated method stub
		return custormerdao.getcustormer(custormer_name);
	}
 
	@Override
	public void updatecustormer(CustormerBean custormer) {
		// TODO Auto-generated method stub
		custormerdao.updatecustormer(custormer);
	}
 
	@Override
	public List<CustormerBean> getall() {
		// TODO Auto-generated method stub
		return custormerdao.getall();
	}
 
}

业务层调用持久层：

cn.pro.dao.CustormerDao

package cn.pro.dao;
 
import java.util.List;
 
import cn.pro.domain.CustormerBean;
 
 
//客户的持久层接口
public interface CustormerDao {
 
	
 
	List<CustormerBean> getall();
 
	void addcustormer(CustormerBean custormer);
 
	void deletecustormer(int custormer_id);
 
	CustormerBean getcustormer(String custormer_name);
 
	void updatecustormer(CustormerBean custormer);
 
}

cn.pro.dao.CustormerDaoImpl

package cn.pro.dao;
 
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 com.mchange.v2.c3p0.ComboPooledDataSource;
 
import cn.pro.domain.CustormerBean;
import cn.pro.utils.C3p0Util;
//客户的持久层实现类
public class CustormerDaoImpl implements CustormerDao {
	ComboPooledDataSource ds = C3p0Util.getds();
	private QueryRunner runner=new QueryRunner(ds);
	
	@Override
	public List<CustormerBean> getall() {
		// TODO Auto-generated method stub
			 try {
				return runner.query("select * from custormer", new BeanListHandler<CustormerBean>(CustormerBean.class));
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new RuntimeException(e);
			}
 
	}
 
	@Override
	public void addcustormer(CustormerBean custormer) {
		// TODO Auto-generated method stub
		try {
			runner.update("insert into custormer values('custormer_id'=?,'custormer_name'=?,'custormer_add'=?,'custormer_tel'=?,'custormer_sex'=?,'custormer_birth'=?)",
					custormer.getCustormer_id(),
					custormer.getCustormer_name(),
					custormer.getCustormer_add(),
					custormer.getCustormer_tel(),
					custormer.getCustormer_sex(),
					custormer.getCustormer_birth());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
 
	@Override
	public void deletecustormer(int custormer_id) {
		// TODO Auto-generated method stub
		try {
			runner.update("delete custormer where custormer_id=?",custormer_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
 
	@Override
	public CustormerBean getcustormer(String custormer_name) {
		 try {
				return runner.query("select * from custormer where custormer_name=?", new BeanHandler<CustormerBean>(CustormerBean.class),custormer_name);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new RuntimeException(e);
			}
	}
 
	@Override
	public void updatecustormer(CustormerBean custormer) {
		// TODO Auto-generated method stub
		try {
			runner.update("update custormer set custormer_name=?,custormer_add=?,custormer_tel=?,custormer_sex=?,custormer_birth=? whrer custormer_id=?",
							custormer.getCustormer_name(),
							custormer.getCustormer_add(),
							custormer.getCustormer_tel(),
							custormer.getCustormer_sex(),
							custormer.getCustormer_birth(),
							custormer.getCustormer_id());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
 
}

 C3p0 xml配置文件

/src/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/springtest</property>
    <property name="user">root</property>
    <property name="password">123456</property>
    
	<property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">10</property>
  </default-config>
</c3p0-config>

C3p0 properties配置文件。

/springtest226/src/c3p0.properties

driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/springtest
user=root
password=123456

获取连接池的工具类 

cn.pro.utils.C3p0Util

package cn.pro.utils;
 
import java.beans.PropertyVetoException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;
import org.omg.CORBA.PUBLIC_MEMBER;
 
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
 
public class C3p0Util {
	static ComboPooledDataSource ds =new ComboPooledDataSource();//自动加载配置文件类容
	
	
	//获取properties文件中配置
/*	static{
		ResourceBundle rb = ResourceBundle.getBundle("c3p0");
		try {
			ds.setDriverClass(rb.getString("driverClass"));
			ds.setJdbcUrl(rb.getString("jdbcUrl"));
			ds.setUser(rb.getString("user"));
			ds.setPassword(rb.getString("password"));
			
		} catch (PropertyVetoException e) {
			// TODO Auto-generated catch block
			throw new ExceptionInInitializerError("初始化失败"+e);
		}
		}*/
		
		public static ComboPooledDataSource getds() {
			return ds;
		}
		public static Connection getConection(){
			try {
				return (Connection) ds.getConnection();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException();
			}
		}
		
		public static void close(ResultSet rs,Statement state,Connection conn){
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(state != null){
				try {
					state.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
 
		
}
}

使用junit测试

package cn.pro.junit;
 
import static org.junit.Assert.*;
 
import java.util.List;
import java.util.ResourceBundle;
 
import org.junit.Test;
 
import cn.pro.domain.CustormerBean;
import cn.pro.service.ICustormerService;
import cn.pro.service.impl.ICustormerServiceImpl;
 
public class ICustormerServiceImplTest {
 
	@Test
	public void test() {
		ICustormerService custormer = new ICustormerServiceImpl();
		List<CustormerBean> list = custormer.getall();
		System.out.println(list);
	}
	@Test
	public void test2(){
		ResourceBundle rb = ResourceBundle.getBundle("c3p0");
		System.out.println(rb.getString("driverClass"));
	}
 
}

把数据库中类容排列成数组打印出来。

整合spring部分代码更改：

自己写的类代码块的依赖

1.明确依赖关系。

2.使用set方法注入依赖

注意不能忽略调用包类的依赖（这部分需要看包的源代码）

1.有构造函数 

例如：

<bean id="runner" class="org.apache.commons.dbutils.QueryRunner">
    <constructor-arg name="ds" ref="datasource"></constructor-arg>
    </bean>

2.有set方法：

例如：

    <!-- 配置C3p0连接池  ComboPooledDataSource有set方法，通过set逐日依赖   -->
    <bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
    <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/springtest"></property>
    <property name="user" value="root"></property>
    <property name="password" value="123456"></property>
    </bean>

 

ICustormerServiceImpl依赖CustormerDao接口来调用持久层，生成set方法；

cn.pro.service.impl.ICustormerServiceImpl

package cn.pro.service.impl;
 
import java.util.List;
 
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
 
import cn.pro.dao.CustormerDao;
import cn.pro.dao.CustormerDaoImpl;
import cn.pro.domain.CustormerBean;
import cn.pro.service.ICustormerService;
 
public class ICustormerServiceImpl implements ICustormerService {
	//ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
	private CustormerDao custormerdao;
	public void setCustormerdao(CustormerDao custormerdao) {
		this.custormerdao = custormerdao;
	}
 
	//private CustormerDao custormerdao = new  CustormerDaoImpl();
	@Override
	public void addcustormer(CustormerBean custormer) {
		// TODO Auto-generated method stub
		custormerdao.addcustormer(custormer);
	}
 
	@Override
	public void deletecustormer(int custormer_id) {
		// TODO Auto-generated method stub
		custormerdao.deletecustormer(custormer_id);
	}
 
	@Override
	public CustormerBean getcustormer(String custormer_name) {
		// TODO Auto-generated method stub
		return custormerdao.getcustormer(custormer_name);
	}
 
	@Override
	public void updatecustormer(CustormerBean custormer) {
		// TODO Auto-generated method stub
		custormerdao.updatecustormer(custormer);
	}
 
	@Override
	public List<CustormerBean> getall() {
		// TODO Auto-generated method stub
		return custormerdao.getall();
	}
 
}

CustormerDaoImpl  依赖runner生成set方法：

cn.pro.dao.CustormerDaoImpl

package cn.pro.dao;
 
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 com.mchange.v2.c3p0.ComboPooledDataSource;
 
import cn.pro.domain.CustormerBean;
import cn.pro.utils.C3p0Util;
//客户的持久层实现类
public class CustormerDaoImpl implements CustormerDao {
	private QueryRunner runner;
 
	public void setRunner(QueryRunner runner) {
		this.runner = runner;
	}
 
	
	//ComboPooledDataSource ds = C3p0Util.getds();
	//private QueryRunner runner=new QueryRunner(ds);
	
	@Override
	public List<CustormerBean> getall() {
		// TODO Auto-generated method stub
			 try {
				return runner.query("select * from custormer", new BeanListHandler<CustormerBean>(CustormerBean.class));
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new RuntimeException(e);
			}
 
	}
 
	@Override
	public void addcustormer(CustormerBean custormer) {
		// TODO Auto-generated method stub
		try {
			runner.update("insert into custormer values('custormer_id'=?,'custormer_name'=?,'custormer_add'=?,'custormer_tel'=?,'custormer_sex'=?,'custormer_birth'=?)",
					custormer.getCustormer_id(),
					custormer.getCustormer_name(),
					custormer.getCustormer_add(),
					custormer.getCustormer_tel(),
					custormer.getCustormer_sex(),
					custormer.getCustormer_birth());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
 
	@Override
	public void deletecustormer(int custormer_id) {
		// TODO Auto-generated method stub
		try {
			runner.update("delete custormer where custormer_id=?",custormer_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
 
	@Override
	public CustormerBean getcustormer(String custormer_name) {
		 try {
				return runner.query("select * from custormer where custormer_name=?", new BeanHandler<CustormerBean>(CustormerBean.class),custormer_name);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new RuntimeException(e);
			}
	}
 
	@Override
	public void updatecustormer(CustormerBean custormer) {
		// TODO Auto-generated method stub
		try {
			runner.update("update custormer set custormer_name=?,custormer_add=?,custormer_tel=?,custormer_sex=?,custormer_birth=? whrer custormer_id=?",
							custormer.getCustormer_name(),
							custormer.getCustormer_add(),
							custormer.getCustormer_tel(),
							custormer.getCustormer_sex(),
							custormer.getCustormer_birth(),
							custormer.getCustormer_id());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
 
}

依赖关系：cn.pro.service.impl.ICustormerServiceImpl   ------>cn.pro.dao.CustormerDaoImpl------->org.apache.commons.dbutils.QueryRunner------>com.mchange.v2.c3p0.ComboPooledDataSource

bean.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
 
    <!-- bean definitions here -->
	<bean id="custormerservice" class="cn.pro.service.impl.ICustormerServiceImpl">
	<property name="custormerdao" ref="custormerdao"></property>
	</bean>
	<bean id="custormerdao" class="cn.pro.dao.CustormerDaoImpl">
		<property name="runner" ref="runner"></property>
	</bean>
	
	
	<!-- 配置runner runner有构造函数可通过arg配置 -->
	<bean id="runner" class="org.apache.commons.dbutils.QueryRunner">
	<constructor-arg name="ds" ref="datasource"></constructor-arg>
	</bean>
	
	
	<!-- 配置C3p0连接池  ComboPooledDataSource有set方法，通过set逐日依赖   -->
	<bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
	<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
	<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/springtest"></property>
	<property name="user" value="root"></property>
	<property name="password" value="123456"></property>
	</bean>
	
	
	
	
	
	
	
	
	
	
</beans>

junit测试

@Test
    public void test3(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        ICustormerService custormer =  (ICustormerService) ac.getBean("custormerservice");
        List<CustormerBean> list = custormer.getall();
        System.out.println(list);

这样就解决了程序的耦合。