QueryRunner 与 Spring 整合

QreryRunner类(org.apache.commons.dbutils.QueryRunner) 是Dbutils的核心类之一，它显著的简化了SQL查询，并与ResultSetHandler协同工作将使编码量大为减少。这里不展开将他，有兴趣的可以到官网看https://commons.apache.org/proper/commons-dbutils/

<!-- spring-dataSource.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"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">
    
    
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
    	<property name="driverClassName" value="com.mysql.jdbc.Driver" />
    	<property name="url" value="jdbc:mysql://127.0.0.1:3306/test" />
    	<property name="username" value="admin" />
    	<property name="password" value="admin" />
    </bean>
    
    <!-- 这里需要注入dataSourceProxy，若注入dataSource是无法使用spring的事务功能 -->
    <bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner">
    	<constructor-arg name="ds" ref="dataSourceProxy" />
    </bean>
    
    <bean id="dataSourceProxy" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
    	<constructor-arg ref="dataSource" />
    </bean>
    
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSourceProxy" />
	</bean>
	
	<tx:annotation-driven transaction-manager="transactionManager" />
	
</beans>

@Service
public class TestService {
	
	@Autowired
	private QueryRunner queryRunner; 	//注入queryRunner
	
	@Transactional
	public void dbcpTest() {
		try {
			queryRunner.update("insert into yzm(yzm_value) values(now())");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}

同一个项目工程中，同时使用 QueryRunner 与 JdbcTemplate 关联不同的数据源

<!-- spring-dbcp.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"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">
    
    <!-- dbcp数据源 -->
    <bean id="dbcpDataSource" class="org.apache.commons.dbcp2.BasicDataSource">
    	<property name="driverClassName" value="com.mysql.jdbc.Driver" />
    	<property name="url" value="jdbc:mysql://127.0.0.1:3306/jdbc" />
    	<property name="username" value="admin" />
    	<property name="password" value="admin" />
    </bean>
    
    <bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner">
    	<constructor-arg name="ds" ref="dataSourceProxy" />
    </bean>
    
    <bean id="dataSourceProxy" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
    	<constructor-arg ref="dbcpDataSource" />
    </bean>
    
	<bean id="dbcpDataSourceTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSourceProxy" />
	</bean>
	
	<tx:annotation-driven transaction-manager="dbcpDataSourceTransactionManager" />
	
</beans>

<!-- spring-c3p0.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"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">
    
    <!-- c3p0数据源.xml -->
    <bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    	<property name="driverClass" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
    	<property name="jdbcUrl" value="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=c3p0" />
    	<property name="user" value="admin" />
    	<property name="password" value="admin" />
    </bean>
    
    <!-- 由于jdbcTemplate是spring的项目，所以可以直接注入dataSource -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    	<property name="dataSource" ref="c3p0DataSource" />
    </bean>
    
	<bean id="c3p0DataSourceTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="c3p0DataSource" />
	</bean>
	
	<tx:annotation-driven transaction-manager="c3p0DataSourceTransactionManager" />
	
</beans>

@Service
public class TestService {
	
	@Autowired
	private QueryRunner queryRunner;
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	//这里需要指定使用哪一个事务管理器，若事务管理器的名字为transactionManager时，可以不显示指定
	@Transactional("dbcpDataSourceTransactionManager")
	public void dbcpTest() {
		try {
			queryRunner.update("insert into yzm(yzm_value) values(now())");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Transactional("c3p0DataSourceTransactionManager")
	public void c3p0Test(){
		jdbcTemplate.update("insert into salary_import(m1) values(GETDATE())");
	}
	
}

QueryRunner 用法示例 

		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-dbcp2</artifactId>
			<version>2.4.0</version>  <!-- 2.5.0 以上版本需要jdk1.8 -->
		</dependency>
		<dependency>
			<groupId>commons-dbutils</groupId>
			<artifactId>commons-dbutils</artifactId>
			<version>1.7</version>
		</dependency>

@Service
public class TestService {

	@Autowired
	private QueryRunner queryRunner; 	
	
	//insert返回自增主键
	public long insert() throws SQLException {
		String sql = "INSERT INTO product(p_number) VALUE(?)";
		return queryRunner.insert(sql, new ScalarHandler<Long>(), System.currentTimeMillis() + "");
	}
	
	//执行insert、update、delete
	public int update() throws SQLException {
		String sql = "UPDATE `product` SET `p_number` = ? WHERE `p_id` = ?";
		return queryRunner.update(sql, "001", 1);
	}
	
	//批量执行
	public int[] batch() throws SQLException {
		String sql = "INSERT INTO product(p_number) VALUE(?)";
		Object[][] params = new Object[10][1];     //第一个参数为批量执行的次数，第2个参数为占位符的个数
		for (int i = 0; i < params.length; i++) {
			params[i][0] = new Random().nextInt(100);
		}
		return queryRunner.batch(sql, params);
	}
	
	public Product getProduct() throws SQLException {
		String sql = "SELECT * FROM product WHERE p_id = ?";
		return queryRunner.query(sql, new BeanHandler<>(Product.class), 1);
	}
	
	public List<Product> getProductForList() throws SQLException{
		String sql = "SELECT * FROM product";
		return queryRunner.query(sql, new BeanListHandler<>(Product.class));
	}
	
	public String getForValue() throws SQLException {
		String sql = "SELECT p_number FROM product WHERE p_id = ?";
		return queryRunner.query(sql, new ScalarHandler<String>(1));      //返回单个值，列从1开始
	}
	
	public Map<String, Object> getMap() throws SQLException {
		String sql = "SELECT * FROM product WHERE p_id = ?";
		return queryRunner.query(sql, new MapHandler(), 1);
	}
	
	public List<Map<String, Object>> getMapList() throws SQLException {
		String sql = "SELECT * FROM product";
		return queryRunner.query(sql, new MapListHandler());
	}

}