Spring JDBC packages

Spring对JDBC的支持由以下几个包通力实现：

org.springframework.jdbc.core This contains the foundations of JDBC classes in Spring. It includes the core JDBC class, JdbcTemplate, which simplies programming database operations with JDBC.
org.springframework.jdbc.datasource This contains helper classes and DataSource implementations that you can use to run JDBC code outside a JEE container.
org.springframework.jdbc.object This contains classes that help convert the data returned from the database into Java objects.
org.springframework.jdbc.support
org.springframework.jdbc.config This contains classes that support JDBC configuration within Spring’s ApplicationContext.

DataSource

Spring可以帮你管理database connection，你只需要实现javax.sql.DataSource就可以了。换句话说，DataSource负责提供并管理Connections.

DriverManagerDataSource

最简单的DataSource实现，不支持connection pooling.

XML配置示例

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>

    <context:property-placeholder location="jdbc.properties" />

Java Configuration示例

@Configuration
@PropertySource("jdbc2.properties")
public class DbConfig {
	@Value("${driverClassName}")
	private String driverClassName;

	@Value("${url}")
	private String url;

	@Value("${username}")
	private String username;

	@Value("${password}")
	private String password;

	@Bean
	@Lazy
	public DataSource dataSource() {
		try {
			DriverManagerDataSource dataSource = new DriverManagerDataSource();
			dataSource.setDriverClassName(driverClassName);
			dataSource.setUrl(url);
			dataSource.setUsername(username);
			dataSource.setPassword(password);

			return dataSource;
		} catch (Exception ex) {
			return null;
		}
	}
}

dbcp2.BasicDataSource

    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>

    <context:property-placeholder location="jdbc.properties" />

HikariDataSource

    <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">
        <property name="jdbcUrl" value="${jdbcUrl}" />
        <property name="username" value="${username}" />
        <property name="password" value="${password}" />
        <property name="dataSourceProperties">
            <props>
                <prop key="cachePrepStmts">${dataSource.cachePrepStmts}</prop>
                <prop key="prepStmtCacheSize">${dataSource.prepStmtCacheSize}</prop>
                <prop key="prepStmtCacheSqlLimit">${dataSource.prepStmtCacheSqlLimit}</prop>
                <prop key="useServerPrepStmts">${dataSource.useServerPrepStmts}</prop>
                <prop key="useLocalSessionState">${dataSource.useLocalSessionState}</prop>
                <prop key="rewriteBatchedStatements">${dataSource.rewriteBatchedStatements}</prop>
                <prop key="cacheResultSetMetadata">${dataSource.cacheResultSetMetadata}</prop>
                <prop key="cacheServerConfiguration">${dataSource.cacheServerConfiguration}</prop>
                <prop key="elideSetAutoCommits">${dataSource.elideSetAutoCommits}</prop>
                <prop key="maintainTimeStats">${dataSource.maintainTimeStats}</prop>
            </props>
        </property>
    </bean>

    <context:property-placeholder location="jdbc-hikari.properties" />

Spring embedded database

从Spring 3.0开始，Spring开始支持embedded database.

    <jdbc:embedded-database id="dataSource" type="H2">
        <jdbc:script location="classpath:db/schema.sql" />
        <jdbc:script location="classpath:db/test-data.sql" />
    </jdbc:embedded-database>

JdbcTemplate

JdbcTemplate是Spring JDBC的核心，JdbcTemplate provides a means by which developers can perform SQL operations against a relational database without all the ceremony and boilerplate typically required when working with JDBC.

NamedParameterJdbcTemplate

相比JdbcTemplate以placeholder的方式指定入参，NamedParameterJdbcTemplate支持以参数名的形式指定入参。

RowMapper

ResultSetExtractor

MappingSqlQuery

MappingSqlQuery提供了一种更object-oriented的方式来做sql查询。

代码示例

public class SelectAllSingers extends MappingSqlQuery<Singer> {
	private static final String SQL_SELECT_ALL_SINGERS = "select id, first_name, last_name, birth_date from singer";

	public SelectAllSingers(DataSource dataSource) {
		super(dataSource, SQL_SELECT_ALL_SINGERS);
	}

	@Override
	protected Singer mapRow(ResultSet rs, int rowNum) throws SQLException {
		Singer singer = new Singer();
		singer.setId(rs.getLong("id"));
		singer.setFirstName(rs.getString("first_name"));
		singer.setLastName(rs.getString("last_name"));
		singer.setBirthDate(rs.getDate("birth_date"));

		return singer;
	}
}

SqlUpdate

SqlUpdate提供了一种更object-oriented的方式来做sql更新。

代码示例

public class UpdateSinger extends SqlUpdate {
	private static final String SQL_UPDATE_SINGER = "update singer set first_name = :first_name, " +
		"last_name = :last_name, birth_date = :birth_date where id = :id";

	public UpdateSinger(DataSource dataSource) {
		super(dataSource, SQL_UPDATE_SINGER);
		super.declareParameter(new SqlParameter("first_name", Types.VARCHAR));
		super.declareParameter(new SqlParameter("last_name", Types.VARCHAR));
		super.declareParameter(new SqlParameter("birth_date", Types.DATE));
		super.declareParameter(new SqlParameter("id", Types.INTEGER));
	}
}

KeyHolder

	public void insert(Singer singer) {
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("first_name", singer.getFirstName());
		paramMap.put("last_name", singer.getLastName());
		paramMap.put("birth_date", singer.getBirthDate());

		KeyHolder keyHolder = new GeneratedKeyHolder();
		insertSinger.updateByNamedParam(paramMap, keyHolder);
		singer.setId(keyHolder.getKey().longValue());
	}

BatchSqlUpdate

暂略

SqlFunction

暂略