Spring之在开发中使用SpringJDBC

在实际开发中使用SpringJDBC有两种方式：

1. 在Dao中添加属性JdbcTemplate并用Spring注入；

    JdbcTemplate类被设计成为线程安全的，所以可以在IOC 容器中声明它的单个实例，并将这个实例注入到所有的 DAO 实例中。JdbcTemplate也利用了Java 1.5 的特定(自动装箱，泛型，可变长度等)来简化开发。

2. 使Dao继承JdbcDaoSupport；

    Spring JDBC 框架还提供了一个 JdbcDaoSupport 类来简化 DAO 实现，该类声明了 jdbcTemplate 属性，它可以从 IOC 容器中注入，或者自动从数据源中创建。

Dao设计示例：

1. 添加jdbcTemplate属性，用注解配置

1.1 添加Dao

package xyz.huning.dao.impl.jdbctemplate.annotation;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import xyz.huning.dao.impl.Employee;
import xyz.huning.dao.impl.IEmployeeDao;

@Repository
public class EmployeeDao implements IEmployeeDao{

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public Employee getEmployeeById(int id) {
		String sql = "select id,name,level from t_employee where id = ?";
		RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
		return jdbcTemplate.queryForObject(sql, rowMapper, id);
	}

}

1.2 添加配置

<?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/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">
	
	
	<context:component-scan base-package="xyz.huning.dao.impl.jdbctemplate.annotation"></context:component-scan>
	
	<!-- 导入资源文件 -->
	<context:property-placeholder location="classpath:db.properties"/>
	
	<!-- 配置 C3P0 数据源 -->
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="user" value="${jdbc.user}"></property>
		<property name="password" value="${jdbc.password}"></property>
		<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
		<property name="driverClass" value="${jdbc.driverClass}"></property>

		<property name="initialPoolSize" value="${c3p0.initialPoolSize}"></property>
		<property name="minPoolSize" value="${c3p0.minPoolSize}"></property>
		<property name="maxPoolSize" value="${c3p0.maxPoolSize}"></property>
		<property name="maxIdleTime" value="${c3p0.maxIdleTime}"></property>
		<property name="idleConnectionTestPeriod" value="${c3p0.idleConnectionTestPeriod}"></property>
	</bean>
	
	<!--配置Spring的JdbcTemplate-->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
</beans>

1.3 添加测试类 

package xyz.huning.dao.impl.jdbctemplate.annotation;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import xyz.huning.dao.impl.Employee;

public class Main {

	public static void main(String[] args) {
		
		ApplicationContext ctx = new ClassPathXmlApplicationContext("jdbctemplate-annotation.xml");

		EmployeeDao employeeDao = ctx.getBean("employeeDao", EmployeeDao.class);
		Employee employee = employeeDao.getEmployeeById(1);
		System.out.println(employee);
		
		((ClassPathXmlApplicationContext)ctx).close();
	}
}

 2. 添加jdbcTemplate属性，用XML配置

2.1 添加Dao

package xyz.huning.dao.impl.jdbctemplate.xml;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import xyz.huning.dao.impl.Employee;
import xyz.huning.dao.impl.IEmployeeDao;

public class EmployeeDao implements IEmployeeDao{

	private JdbcTemplate jdbcTemplate;
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public Employee getEmployeeById(int id) {
		String sql = "select id,name,level from t_employee where id = ?";
		RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
		return jdbcTemplate.queryForObject(sql, rowMapper, id);
	}

}

2.2 添加配置

<?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/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">
	
	<!-- 导入资源文件 -->
	<context:property-placeholder location="classpath:db.properties"/>
	
	<!-- 配置 C3P0 数据源 -->
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="user" value="${jdbc.user}"></property>
		<property name="password" value="${jdbc.password}"></property>
		<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
		<property name="driverClass" value="${jdbc.driverClass}"></property>

		<property name="initialPoolSize" value="${c3p0.initialPoolSize}"></property>
		<property name="minPoolSize" value="${c3p0.minPoolSize}"></property>
		<property name="maxPoolSize" value="${c3p0.maxPoolSize}"></property>
		<property name="maxIdleTime" value="${c3p0.maxIdleTime}"></property>
		<property name="idleConnectionTestPeriod" value="${c3p0.idleConnectionTestPeriod}"></property>
	</bean>
	
	<!--配置Spring的JdbcTemplate-->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
    <bean id="employeeDao" class="xyz.huning.dao.impl.jdbctemplate.xml.EmployeeDao">
    	<property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>
	
</beans>

2.3 添加测试类 

package xyz.huning.dao.impl.jdbctemplate.xml;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import xyz.huning.dao.impl.Employee;

public class Main {

	public static void main(String[] args) {
		
		ApplicationContext ctx = new ClassPathXmlApplicationContext("jdbctemplate-xml.xml");

		EmployeeDao employeeDao = ctx.getBean("employeeDao", EmployeeDao.class);
		Employee employee = employeeDao.getEmployeeById(1);
		System.out.println(employee);
		
		((ClassPathXmlApplicationContext)ctx).close();
	}
}

3. 继承JdbcDaoSupport ，用注解配置

    备注： 可以选择向Dao中注入JdbcTemplate或者向Dao中注入dataSource

3.1 添加Dao

package xyz.huning.dao.impl.jdbcdaosuppport.annotation;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import xyz.huning.dao.impl.Employee;
import xyz.huning.dao.impl.IEmployeeDao;

@Repository
public class EmployeeDao extends JdbcDaoSupport implements IEmployeeDao{
	
	@Autowired
	public void injectDataSource(DataSource dataSource)
	{
		setDataSource(dataSource);
	}
	
//	@Autowired
//	public void injectJdbcTemplate(JdbcTemplate jdbcTemplate)
//	{
//		setJdbcTemplate(jdbcTemplate);
//	}
	
	@Override
	public Employee getEmployeeById(int id) {
		String sql = "select id,name,level from t_employee where id = ?";
		RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
		return getJdbcTemplate().queryForObject(sql, rowMapper, id);
	}

}

3.2 添加配置

<?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/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">
	
	
	<context:component-scan base-package="xyz.huning.dao.impl.jdbcdaosuppport.annotation"></context:component-scan>
	
	<!-- 导入资源文件 -->
	<context:property-placeholder location="classpath:db.properties"/>
	
	<!-- 配置 C3P0 数据源 -->
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="user" value="${jdbc.user}"></property>
		<property name="password" value="${jdbc.password}"></property>
		<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
		<property name="driverClass" value="${jdbc.driverClass}"></property>

		<property name="initialPoolSize" value="${c3p0.initialPoolSize}"></property>
		<property name="minPoolSize" value="${c3p0.minPoolSize}"></property>
		<property name="maxPoolSize" value="${c3p0.maxPoolSize}"></property>
		<property name="maxIdleTime" value="${c3p0.maxIdleTime}"></property>
		<property name="idleConnectionTestPeriod" value="${c3p0.idleConnectionTestPeriod}"></property>
	</bean>
	
	<!--配置Spring的JdbcTemplate-->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
</beans>

3.3 添加测试类

package xyz.huning.dao.impl.jdbcdaosuppport.annotation;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import xyz.huning.dao.impl.Employee;

public class Main {

	public static void main(String[] args) {
		
		ApplicationContext ctx = new ClassPathXmlApplicationContext("jdbcdaosuppport-annotation.xml");

		EmployeeDao employeeDao = ctx.getBean("employeeDao", EmployeeDao.class);
		Employee employee = employeeDao.getEmployeeById(1);
		System.out.println(employee);
		
		((ClassPathXmlApplicationContext)ctx).close();
	}
}

4. 继承JdbcDaoSupport ，用XML配置

    备注： 可以选择向Dao中注入JdbcTemplate或者向Dao中注入dataSource

4.1 添加Dao

package xyz.huning.dao.impl.jdbcdaosupport.xml;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import xyz.huning.dao.impl.Employee;
import xyz.huning.dao.impl.IEmployeeDao;

public class EmployeeDao extends JdbcDaoSupport implements IEmployeeDao{
	
	@Override
	public Employee getEmployeeById(int id) {
		String sql = "select id,name,level from t_employee where id = ?";
		RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
		return getJdbcTemplate().queryForObject(sql, rowMapper, id);
	}

}

4.2 添加配置

<?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/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">
	
	<!-- 导入资源文件 -->
	<context:property-placeholder location="classpath:db.properties"/>
	
	<!-- 配置 C3P0 数据源 -->
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="user" value="${jdbc.user}"></property>
		<property name="password" value="${jdbc.password}"></property>
		<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
		<property name="driverClass" value="${jdbc.driverClass}"></property>

		<property name="initialPoolSize" value="${c3p0.initialPoolSize}"></property>
		<property name="minPoolSize" value="${c3p0.minPoolSize}"></property>
		<property name="maxPoolSize" value="${c3p0.maxPoolSize}"></property>
		<property name="maxIdleTime" value="${c3p0.maxIdleTime}"></property>
		<property name="idleConnectionTestPeriod" value="${c3p0.idleConnectionTestPeriod}"></property>
	</bean>
	
	<!--向Dao中注入JdbcTemplate-->
	<!--
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<bean id="employeeDao" class="xyz.huning.dao.impl.jdbcdaosupport.xml.EmployeeDao">
		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>
    -->
    
    <!--向Dao中注入dataSource-->
    <bean id="employeeDao" class="xyz.huning.dao.impl.jdbcdaosupport.xml.EmployeeDao">
		<property name="dataSource" ref="dataSource"></property>
    </bean>
	
</beans>

4.3 添加测试类

package xyz.huning.dao.impl.jdbcdaosupport.xml;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import xyz.huning.dao.impl.Employee;

public class Main {

	public static void main(String[] args) {
		
		ApplicationContext ctx = new ClassPathXmlApplicationContext("jdbcdaosuppport-xml.xml");

		EmployeeDao employeeDao = ctx.getBean("employeeDao", EmployeeDao.class);
		Employee employee = employeeDao.getEmployeeById(1);
		System.out.println(employee);
		
		((ClassPathXmlApplicationContext)ctx).close();
	}
}