Spring JdbcTemplate 使用总结与经验分享

@Configuration
class DatabaseConfiguration {
	@Bean
	public DataSource dataSource() {
		DataSource dataSource;
		...
		return dataSource;
	}
 
	@Bean
	public JdbcTemplate jdbcTemplate() {
		return new JdbcTemplate(dataSource());
	}
}

/**
 * Created by Ant on 2015/1/1.
 */
public abstract class AntSoftDaoBase {
	@Resource(name = "jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
 
	private String tableName;
 
	protected AntSoftDaoBase(String tableName) {
		this.tableName = tableName;
	}
 
	protected JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
 
	public void clearAll() {
		getJdbcTemplate().update("DELETE FROM " + tableName);
	}
 
	public int count() {
		return getJdbcTemplate().queryForObject( "SELECT count(*) FROM " + tableName, Integer.class);
	}
} 通过@Resource注入jdbcTemplate对象，由于我仅定义了一个类型为jdbcTemplate的bean，可以这里可以省略掉name参数，及@Resource即可，或者使用@Autowired

/**
 * Created by Ant on 2015/1/1.
 */
@Repository
public class AppDao extends AntSoftDaoBase{
	private Logger logger = LoggerFactory.getLogger(getClass());

	private static final String TABLE_NAME = "app";

	private static final String COLUMN_NAMES = "name, user_id, title, description, ctime, status";

	public AppDao() {
		super(TABLE_NAME);
	}

	public int create(final AppInfo appInfo) {
	   ...
	}

	public List<AppInfo> list(int pageNo, int pageSize) {
		...
	}

	public AppInfo get(int appId) {
	   ...
	}

	public void update(AppInfo appInfo) {
		...
	}
}

<dependency>
	<groupId>org.apache.tomcat</groupId>
	<artifactId>tomcat-jdbc</artifactId>
	<version>7.0.42</version>
</dependency>

private DataSource getTomcatPoolingDataSource(String databaseUrl, String userName, String password) {
	org.apache.tomcat.jdbc.pool.DataSource dataSource = new org.apache.tomcat.jdbc.pool.DataSource();
	dataSource.setDriverClassName("com.mysql.jdbc.Driver");
	dataSource.setUrl(databaseUrl);
	dataSource.setUsername(userName);
	dataSource.setPassword(password);

	dataSource.setInitialSize(5); // 连接池启动时创建的初始化连接数量（默认值为0）
	dataSource.setMaxActive(20); // 连接池中可同时连接的最大的连接数
	dataSource.setMaxIdle(12); // 连接池中最大的空闲的连接数，超过的空闲连接将被释放，如果设置为负数表示不限
	dataSource.setMinIdle(0); // 连接池中最小的空闲的连接数，低于这个数量会被创建新的连接
	dataSource.setMaxWait(60000); // 最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间，超过该时间限制会抛出异常，如果设置-1表示无限等待
	dataSource.setRemoveAbandonedTimeout(180); // 超过时间限制，回收没有用(废弃)的连接
	dataSource.setRemoveAbandoned(true); // 超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收
	dataSource.setTestOnBorrow(true);
	dataSource.setTestOnReturn(true);
	dataSource.setTestWhileIdle(true);
	dataSource.setValidationQuery("SELECT 1");
	dataSource.setTimeBetweenEvictionRunsMillis(1000 * 60 * 30); // 检查无效连接的时间间隔 设为30分钟
	return dataSource;
}

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
@SpringApplicationConfiguration(classes = Application.class)
@WebAppConfiguration
@IntegrationTest("server.port=0")
@ActiveProfiles("test")
public @interface AntSoftIntegrationTest {
}

@AntSoftIntegrationTest
@RunWith(SpringJUnit4ClassRunner.class)

@AntSoftIntegrationTest
@RunWith(SpringJUnit4ClassRunner.class)
public class AppServiceTests {
	@Autowired
	private AppService appService;

	@Autowired
	private TestService testService;

	@Before
	public void clearApp() {
		testService.clearApp();
	}

	@Test
	public void testApp() {
		final String name = "xxx";
		final String userId = "Ant";
		final String title = "Hello World";
		final String description = "Description for Hello World";

		final String updatedName = "xxx";
		final String updatedUserId = "Ant";
		final String updatedTitle = "Hello World";
		final String updatedDescription = "Description for Hello World";

		int appId;
		{
			// 创建应用
			AppInfo appInfo = new AppInfo();
			appInfo.setName(name);
			appInfo.setUserId(userId);
			appInfo.setTitle(title);
			appInfo.setDescription(description);
			appId = appService.createApp(appInfo);
		}

		CheckAppInfo(appId, name, userId, title, description, AppStatus.NORMAL);

		{
			// 更新应用
			AppInfo appInfo = new AppInfo();
			appInfo.setId(appId);
			appInfo.setName(updatedName);
			appInfo.setUserId(updatedUserId);
			appInfo.setTitle(updatedTitle);
			appInfo.setDescription(updatedDescription);
			appService.updateApp(appInfo);
		}

		CheckAppInfo(appId, updatedName, updatedUserId, updatedTitle, updatedDescription, AppStatus.NORMAL);
	}

	// 获取应用，并验证数据
	private void CheckAppInfo(int appId, String name, String userId, String title, String description,
							  AppStatus appStatus) {
		AppInfo appInfo = appService.getApp(appId);
		assertEquals(appId, appInfo.getId());
		assertEquals(name, appInfo.getName());
		assertEquals(userId, appInfo.getUserId());
		assertEquals(title, appInfo.getTitle());
		assertEquals(description, appInfo.getDescription());
		assertEquals(appStatus, appInfo.getStatus());
	}
}

spring-boot/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/jdbc/DataSourceTransactionManagerAutoConfiguration.java
/**
 * {@link EnableAutoConfiguration Auto-configuration} for
 * {@link DataSourceTransactionManager}.
 *
 * @author Dave Syer
 */
@Configuration
@ConditionalOnClass({ JdbcTemplate.class, PlatformTransactionManager.class })
public class DataSourceTransactionManagerAutoConfiguration implements Ordered {

	@Override
	public int getOrder() {
		return Integer.MAX_VALUE;
	}

	@Autowired(required = false)
	private DataSource dataSource;

	@Bean
	@ConditionalOnMissingBean(name = "transactionManager")
	@ConditionalOnBean(DataSource.class)
	public PlatformTransactionManager transactionManager() {
		return new DataSourceTransactionManager(this.dataSource);
	}

	@ConditionalOnMissingBean(AbstractTransactionManagementConfiguration.class)
	@Configuration
	@EnableTransactionManagement
	protected static class TransactionManagementConfiguration {

	}

}

@Bean
public PlatformTransactionManager transactionManager() {
	return new DataSourceTransactionManager(myDataSource());
}

@Repository
public class MessageDao extends AntSoftDaoBase {
	private static final String TABLE_NAME = "app_message";

	private static final String COLUMN_NAMES = "app_id, message, ctime, status";

	protected MessageDao() {
		super(TABLE_NAME);
	}

	private static final String SQL_INSERT_DATA =
			"INSERT INTO " + TABLE_NAME + " (" + COLUMN_NAMES + ") "
					+ "VALUES (?, ?, ?, ?)";
	
	public int create(final MessageInfo messageInfo) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		getJdbcTemplate().update(new PreparedStatementCreator() {
									 public PreparedStatement createPreparedStatement(Connection connection) throws
											 SQLException {
										 PreparedStatement ps =
												 connection.prepareStatement(SQL_INSERT_DATA, Statement.RETURN_GENERATED_KEYS);
										 int i = 0;
										 ps.setInt(++i, messageInfo.getAppId());
										 ps.setString(++i, messageInfo.getMessage());
										 ps.setTimestamp(++i, new Timestamp(new Date().getTime()));
										 ps.setInt(++i, 0); // 状态默认为0
										 return ps;
									 }
								 }, keyHolder
		);
		return keyHolder.getKey().intValue();
	}
	...
}

private static final String SQL_UPDATE_STATUS =
		"UPDATE " + TABLE_NAME + " SET "
				+ "status = ? "
				+ "WHERE id IN (%s)";

public void updateStatus(List<Integer> ids, Status status) {
	if (ids == null || ids.size() == 0) {
		throw new IllegalArgumentException("ids is empty");
	}
	String idsText = StringUtils.join(ids, ", ");
	String sql = String.format(SQL_UPDATE_STATUS , idsText);
	getJdbcTemplate().update(sql, status.toValue());
} 

private static final String SQL_SELECT_DATA =
		"SELECT id, " + COLUMN_NAMES + " FROM " + TABLE_NAME + " WHERE id = ?";

public AppInfo get(int appId) {
	List<AppInfo> appInfoList = getJdbcTemplate().query(SQL_SELECT_DATA, new Object[] {appId}, new AppRowMapper());
	return appInfoList.size() > 0 ? appInfoList.get(0) : null;
}

private static class AppRowMapper implements RowMapper<AppInfo> {
	@Override
	public AppInfo mapRow(ResultSet rs, int i) throws SQLException {
		AppInfo appInfo = new AppInfo();
		appInfo.setId(rs.getInt("id"));
		appInfo.setName(rs.getString("name"));
		appInfo.setUserId(rs.getString("user_id"));
		appInfo.setTitle(rs.getString("title"));
		appInfo.setDescription(rs.getString("description"));
		appInfo.setCtime(rs.getTimestamp("ctime"));
		appInfo.setStatus(AppStatus.fromValue(rs.getInt("status")));
		return appInfo;
	}
}