Spring JDBCtemplateの使用説明
4286 ワード
主にSpring JDBTtemplateの使い方を紹介します
SpringのプロファイルでApplicationContextでデータソースを構成する
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://xxx.xxx.com:3306/metadb?useUnicode=true&characterEncoding=utf-8" />
<property name="username" value="dbausr" />
<property name="password" value="dbausr" />
</bean>
<bean id = "TransactionManager" class = "org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name = "dataSource" ref="dataSource"/>
</bean>
<bean id = "jdbcTemplate" class = "org.springframework.jdbc.core.JdbcTemplate">
<property name = "dataSource" ref="dataSource"/>
</bean>
Javaコードでの使用
@Component
public class TestDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public void test() {
String sql = "select * from base_config limit 1" ;
SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql);
while(sqlRowSet.next()) {
System.out.println(sqlRowSet.getString(1) + "," + sqlRowSet.getString(2));
}
}
}
インタフェースドキュメント
http://docs.spring.io/spring/docs/4.1.0.RELEASE/javadoc-api/
一般的な方法の例
.queryメソッドクエリー生成オブジェクトリスト
public SqlcheckerResult getCheckerResult(String messageID, String sql_id) {
StringBuilder sqlBuilder = new StringBuilder("select * from `table` where 1 = 1 ");
if(!StringUtils.isEmpty(messageID)) {
sqlBuilder.append(" and message_id = '").append(messageID.replace("'", "''")).append("'");
}
if(!StringUtils.isEmpty(sql_id)) {
sqlBuilder.append(" and sql_id = '").append(sql_id.replace("'", "''")).append("'");
}
sqlBuilder.append(" order by id desc limit 10");
List<SqlcheckerResult> sqlcheckerResults = jdbcTemplate.query(sqlBuilder.toString(), new RowMapper<SqlcheckerResult>() {
@Override
public SqlcheckerResult mapRow(ResultSet resultSet, int rowNum) throws SQLException {
SqlcheckerResult sqlCheckResult = new SqlcheckerResult();
sqlCheckResult.setSqlString(resultSet.getString("sql_text"));
List<RuleCheckResult> ruleResults = new ArrayList<RuleCheckResult>();
JSONArray ruleCheckMaps = JSONObject.parseArray(resultSet.getString("rule_advice"));
for(int idx = 0; idx < ruleCheckMaps.size(); idx++) {
JSONObject checkMap = (JSONObject) ruleCheckMaps.get(idx);
RuleCheckResult checkResult = new RuleCheckResult(
checkMap.getString("rule"), checkMap.getBoolean("success"),
checkMap.getString("detail"), checkMap.getString("advice")
);
ruleResults.add(checkResult);
}
sqlCheckResult.setRuleCheckResults(ruleResults);
return sqlCheckResult;
}
});
if(null != sqlcheckerResults && sqlcheckerResults.size() > 0) {
return sqlcheckerResults.get(0);
}
return null;
}