引言
正确使用JdbcTemplate执行数据库操作
1、Bean声明
@Configuration classDatabaseConfiguration { @Bean publicDataSource dataSource() { DataSource dataSource; ... returndataSource; } @Bean publicJdbcTemplate jdbcTemplate() { return newJdbcTemplate(dataSource()); } }
2、封装Dao类型
/*** Created by Ant on 2015/1/1. */ public abstract classAntSoftDaoBase { @Resource(name = "jdbcTemplate") privateJdbcTemplate jdbcTemplate; privateString tableName; protectedAntSoftDaoBase(String tableName) { this.tableName =tableName; } protectedJdbcTemplate getJdbcTemplate() { returnjdbcTemplate; } public voidclearAll() { getJdbcTemplate().update("DELETE FROM " +tableName); } public intcount() { return getJdbcTemplate().queryForObject( "SELECT count(*) FROM " + tableName, Integer.class); } }
/*** Created by Ant on 2015/1/1. */@Repository public class AppDao extendsAntSoftDaoBase{ 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"; publicAppDao() { super(TABLE_NAME); } public int create(finalAppInfo appInfo) { ... } public List<AppInfo> list(int pageNo, intpageSize) { ... } public AppInfo get(intappId) { ... } public voidupdate(AppInfo appInfo) { ... } }
该Dao类型提供了对AppInfo数据的增删查改接口,对这些接口的具体实现,后面再进行详细介绍
3、使用Tomcat-jdbc数据库连接池
<dependency> <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-jdbc</artifactId> <version>7.0.42</version> </dependency>
创建连接池DataSource的逻辑封装在如下方法中,DatabaseConfiguration.dataSource方法内部可以直接调用此方法获取具备连接池功能的DataSource
privateDataSource getTomcatPoolingDataSource(String databaseUrl, String userName, String password) { org.apache.tomcat.jdbc.pool.DataSource dataSource = neworg.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分钟 returndataSource; }
关于各数值的配置请根据实际情况调整
配置重连逻辑,以在连接失效是进行自动重连。默认情况下mysql数据库将关闭掉超过8小时的连接,开发的第一个java后端项目,加入数据库连接池后的几天早晨,web平台前几次数据库操作总是失败,配置重连逻辑即可解决
使用HSQL进行数据库操作单元测试
1、内嵌数据库HSQLDB
a)配置HSQL DataSource
<dependency> <groupId>org.hsqldb</groupId> <artifactId>hsqldb</artifactId> <version>2.3.0</version> </dependency>
@Bean publicDataSource antsoftDataSource() { DataSource dataSource; if(antsoftDatabaseIsEmbedded) { dataSource =getEmbeddedHsqlDataSource(); } else{ dataSource =getTomcatPoolingDataSource(antsoftDatabaseUrl, antsoftDatabaseUsername, antsoftDatabasePassword); } returndataSource; }
其中antsoftDatabaseIsEmbedded等对象字段值的定义如下
@Value("${antsoft.database.isEmbedded}") private booleanantsoftDatabaseIsEmbedded; @Value("${antsoft.database.url}") privateString antsoftDatabaseUrl; @Value("${antsoft.database.username}") privateString antsoftDatabaseUsername; @Value("${antsoft.database.password}") private String antsoftDatabasePassword;
通过@Value指定配置项key名称,运行时通过key查找配置值替换相应字段
配置文件为resources/application.properties
antsoft.database.isEmbedded=falseantsoft.database.url=jdbc:mysql://127.0.0.1:3306/antsoft_app antsoft.database.username=root antsoft.database.password=ant
单元测试配置文件为resources/application-test.properties
antsoft.database.isEmbedded=true
表示单测使用内嵌数据库
b)HSQL数据库初始化脚本
privateDataSource getEmbeddedHsqlDataSource() { log.debug("create embeddedDatabase HSQL"); return new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.HSQL).addScript("classpath:db/hsql_init.sql").build(); }
通过addScript指定初始化数据库SQL脚本resources/db/hsql_init.sql,内容如下
SET DATABASESQL SYNTAX MYS TRUE; CREATE TABLEapp ( id int GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL, name varchar(64) NOT NULL, user_id varchar(64) NOT NULL, title varchar(64) NOT NULL, description varchar(1024) NOT NULL, ctime datetime NOT NULL, status int NOT NULL, PRIMARY KEY(id), UNIQUE(name) ); CREATE TABLEapp_unique_name ( id int GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL, unique_name varchar(64) NOT NULL UNIQUE, PRIMARY KEY(id) ); ...
HSQL语法与MySql语法存在差异,使用是需注意,我在开发过程中注意到的不同点列举如下
-不支持tinyint等数据类型,int后不允许附带表示数据长度的括号,如不支持int(11)
- 不支持index索引,但支持unique index
- 不支持AUTO_INCREMENT语法
c)验证你的HSQL脚本
可采用如下方式验证hsql语句正确性
在本地maven仓库中找到hsqldb(正确引入过hsqldb),博主本机目录C:Usersant.m2 epositoryorghsqldbhsqldb2.3.2
执行hsqldb-2.3.2.jar (java -jar hsqldb-2.3.2.jar)
默认窗体一个提示框,点击ok。在右侧输入SQL语句,执行工具栏中中Execuete SQL
如下截图,显示SQL执行成功
上图SQL语句如下
CREATE TABLEapp_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) );
2、编写单元测试覆盖Dao数据库操作
@Retention(RetentionPolicy.RUNTIME) @Target(ElementType.TYPE) @SpringApplicationConfiguration(classes = Application.class) @WebAppConfiguration @IntegrationTest("server.port=0") @ActiveProfiles("test") public @interfaceAntSoftIntegrationTest { }
定义测试类型,添加如下注解
@AntSoftIntegrationTest @RunWith(SpringJUnit4ClassRunner.class)
我对自己代码的期望是,尽可能100%的Dao方法都被单元测试覆盖。
以下代码演示对AppService(其接口实现转发调用AppDao相应接口)进行的基本单元测试,其中测试了create、update及get三种操作
@AntSoftIntegrationTest @RunWith(SpringJUnit4ClassRunner.class) public classAppServiceTests { @Autowired privateAppService appService; @Autowired privateTestService testService; @Before public voidclearApp() { testService.clearApp(); } @Test public voidtestApp() { 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"; intappId; { //创建应用 AppInfo appInfo = newAppInfo(); 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 = newAppInfo(); 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(intappId, 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()); } }
开发经验分享
1、失效的事务
/*** {@linkEnableAutoConfiguration Auto-configuration} for * {@linkDataSourceTransactionManager}. * * @authorDave Syer */@Configuration @ConditionalOnClass({ JdbcTemplate.class, PlatformTransactionManager.class}) public class DataSourceTransactionManagerAutoConfiguration implementsOrdered { @Override public intgetOrder() { returnInteger.MAX_VALUE; } @Autowired(required = false) privateDataSource dataSource; @Bean @ConditionalOnMissingBean(name = "transactionManager") @ConditionalOnBean(DataSource.class) publicPlatformTransactionManager transactionManager() { return new DataSourceTransactionManager(this.dataSource); } @ConditionalOnMissingBean(AbstractTransactionManagementConfiguration.class) @Configuration @EnableTransactionManagement protected static classTransactionManagementConfiguration { } }
由此可见,若未将DataSource声明为Bean,将不会创建transactionManager,@Transactional注解将毫无作用
@Bean publicPlatformTransactionManager transactionManager() {return newDataSourceTransactionManager(myDataSource()); }
默认会使用方法名作为bean的命名,因此此处覆盖了默认的transactionManager Bean对象
2、获取新增数据的自增id
@Repository
public class MessageDao extendsAntSoftDaoBase {
private static final String TABLE_NAME = "app_message";
private static final String COLUMN_NAMES = "app_id, message, ctime, status";
protectedMessageDao() {
super(TABLE_NAME);
}
private static final String SQL_INSERT_DATA =
"INSERT INTO " + TABLE_NAME + " (" + COLUMN_NAMES + ") "
+ "VALUES (?, ?, ?, ?)";
public int create(finalMessageInfo messageInfo) {
KeyHolder keyHolder = newGeneratedKeyHolder();
getJdbcTemplate().update(newPreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection) throwsSQLException {
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(newDate().getTime()));
ps.setInt(++i, 0); // 状态默认为0
returnps;
}
}, keyHolder
);
returnkeyHolder.getKey().intValue();
}
...
}
3、SQL IN 语句
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()); }
4、查询数据一般方法,及注意事项
AppDao类型中提供get方法,以根据一个appId获取该APP数据,代码如下
private static final String SQL_SELECT_DATA = "SELECT id, " + COLUMN_NAMES + " FROM " + TABLE_NAME + " WHERE id = ?"; public AppInfo get(intappId) { List<AppInfo> appInfoList = getJdbcTemplate().query(SQL_SELECT_DATA, new Object[] {appId}, newAppRowMapper()); return appInfoList.size() > 0 ? appInfoList.get(0) : null; }
注意点:由于主键id会唯一标识一个数据项,有些人会使用queryForObject获取数据项,若未找到目标数据时,该方法并非返回null,而是抛异常EmptyResultDataAccessException。应使用query方法,并检测返回值数据量
AppRowMapper用于解析每行数据并转成Model类型,其代码如下
private static class AppRowMapper implements RowMapper<AppInfo>{ @Override public AppInfo mapRow(ResultSet rs, int i) throwsSQLException { AppInfo appInfo = newAppInfo(); 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"))); returnappInfo; } }