[原创]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);
    }
}

/**
 * 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;
    }

CREATE TABLE app_message (
  id bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
  app_id int NOT NULL,
  message varchar(1024) NOT NULL,
  ctime datetime NOT NULL,
  status int NOT NULL,
  PRIMARY KEY (id)
);

@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());
    }
}

/**
 * {@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());
    }

    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());
    }