一般情況下，都是在DAO類中使用JdbcTemplate，JdbcTemplate在XML配置文件中配置好，直接在DAO中注入即可。

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:p="http://www.springframework.org/schema/p"xmlns:context="http://www.springframework.org/schema/context"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd"><context:component-scan base-package="com.ivy"/><context:property-placeholder location="classpath:jdbc.properties"/><bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"destroy-method="close"p:driverClassName="${jdbc.driverClassName}"p:url="${jdbc.url}"p:username="${jdbc.username}"p:password="${jdbc.password}"/><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"p:dataSource-ref="dataSource"/> </beans>

在Spring配置文件中配置DAO一般分為4個(gè)步驟：

定義DataSource

定義JdbcTemplate

聲明一個(gè)抽象的bean，以便所有DAO復(fù)用配置JdbcTemplate屬性的配置。

配置具體的DAO

?

　　基本數(shù)據(jù)操作

• 更改數(shù)據(jù)

盡量使用可綁定參數(shù)的SQL，以便數(shù)據(jù)庫(kù)可以復(fù)用SQL的執(zhí)行計(jì)劃，提高數(shù)據(jù)庫(kù)的執(zhí)行效率。

• 返回?cái)?shù)據(jù)庫(kù)的表自增主鍵值

例如：

final String sqlString = "INSERT INTO t_forum(forum_name, forum_desc) VALUES (?, ?)";KeyHolder keyHolder = new GeneratedKeyHolder();jdbcTemplate.update(new PreparedStatementCreator() {@Overridepublic PreparedStatement createPreparedStatement(Connection conn)throws SQLException {PreparedStatement ps = conn.prepareStatement(sqlString);ps.setString(1, forum.getForumnName());ps.setString(2, forum.getForumnDesc());return ps;}}, keyHolder);forum.setForumnId(keyHolder.getKey().intValue());

• 批量更新

private final String BATCH_INSERT_SQL = "INSERT INTO t_forum(forum_name, forum_desc) VALUES (?, ?)";

public void addForums(final List<Forum> forums) {jdbcTemplate.batchUpdate(BATCH_INSERT_SQL, new BatchPreparedStatementSetter() {@Overridepublic void setValues(PreparedStatement ps, int index) throws SQLException {Forum forum = forums.get(index);ps.setString(1, forum.getForumName());ps.setString(2, forum.getForumDesc());}@Overridepublic int getBatchSize() {return forums.size();}});}

?

• 查詢數(shù)據(jù)

　　Spring提供了RowCallbackHandler回調(diào)接口，通過(guò)該接口可以定義如何從結(jié)果集中獲取數(shù)據(jù)。

單條結(jié)果集處理：

private final String SELECT_SQL = "SELECT forum_name, forum_desc FROM t_forum WHERE forum_id=?";

public Forum getForum(final int forumId) {final Forum forum = new Forum();jdbcTemplate.query(SELECT_SQL, new Object[]{forumId}, new RowCallbackHandler() {@Overridepublic void processRow(ResultSet rs) throws SQLException {forum.setForumId(forumId);forum.setForumDesc(rs.getString("forum_desc"));forum.setForumName(rs.getString("forum_name"));}});return forum;}

多條數(shù)據(jù)結(jié)果集的處理：

private final String SELECT_MULTI_SQL = "SELECT forum_id,forum_name, forum_desc FROM t_forum WHERE forum_id between ? and ?";

public List<Forum> getForums(final int fromId, final int toId) {final List<Forum> forums = new ArrayList<>();jdbcTemplate.query(SELECT_MULTI_SQL, new Object[]{fromId, toId}, new RowCallbackHandler() {@Overridepublic void processRow(ResultSet rs) throws SQLException {Forum forum = new Forum();forum.setForumId(rs.getInt("forum_id"));forum.setForumDesc(rs.getString("forum_desc"));forum.setForumName(rs.getString("forum_name"));forums.add(forum);}});return forums;}

使用RowMapper<T>處理結(jié)果集，RowMapper<T>更適合在多行結(jié)果集中使用：

private final String SELECT_MULTI_SQL = "SELECT forum_id,forum_name, forum_desc FROM t_forum WHERE forum_id between ? and ?";

public List<Forum> getForumsByRowMapper(final int fromId, final int toId) {return jdbcTemplate.query(SELECT_MULTI_SQL, new Object[]{fromId, toId}, new RowMapper<Forum>() {@Overridepublic Forum mapRow(ResultSet rs, int index) throws SQLException {Forum forum = new Forum();forum.setForumId(rs.getInt("forum_id"));forum.setForumDesc(rs.getString("forum_desc"));forum.setForumName(rs.getString("forum_name"));return forum;}});}

?

RowCallbackHandler vs RowMapper<T>

　　從功能上來(lái)說(shuō)，RowCallbackHandler和RowMapper<T>并沒(méi)有太大的區(qū)別，它們都是用于定義結(jié)果集行的讀取邏輯，將ResultSet中的數(shù)據(jù)映射到對(duì)象或List中。

　　當(dāng)處理大結(jié)果集時(shí)，如果使用RowMapper，結(jié)果集中的所有數(shù)據(jù)最終都會(huì)映射并匯總成一個(gè)List<T>對(duì)象，占用大量的JVM內(nèi)存，甚至可直接引發(fā)OOM，這時(shí)，應(yīng)該使用RowCallbackHandler接口，用processRow()方法內(nèi)部處理結(jié)果集數(shù)據(jù)。

?

• 查詢單值數(shù)據(jù)

int類型的單值查詢接口：int queryForInt(String sql)

private final String COUNT_SQL = "SELECT COUNT(*) FROM t_forum"; public int getForumNum() {return jdbcTemplate.queryForInt(COUNT_SQL);}

long類型的單值查詢接口：long queryForLong(String sql)

其他類型的單值查詢接口：<T> T queryForObject(String sql, Class<T> requiredType);

?

使用RowMapper獲取單值對(duì)象：

public class TopicDao {@Autowiredprivate JdbcTemplate jdbcTemplate;public double getReplyRate(int userId) {String sql = "SELECT topic_replies, topic_views FROM t_topic WHERE user_id=?";double rate = jdbcTemplate.queryForObject(sql, new Object[]{userId}, new RowMapper<Double>() {@Overridepublic Double mapRow(ResultSet rs, int index) throws SQLException {// TODO Auto-generated method stubint replies = rs.getInt("topic_replies");int views = rs.getInt("topic_views");if (views > 0) {return new Double((double)replies/views);} else {return new Double(0.0);}}});return rate;} }

?

調(diào)用存儲(chǔ)過(guò)程

創(chuàng)建一個(gè)存儲(chǔ)過(guò)程：

delimiter //CREATE PROCEDURE P_GET_TOPIC_NUM(IN in_user_id INT, OUT out_num INT) BEGINSELECT COUNT(*) INTO out_num FROM t_topic WHERE user_id=in_user_id; END // delimiter ; public int getUserTopicNum(final int userId) {String sql = "{call P_GET_TOPIC_NUM(?,?)}";Integer num = jdbcTemplate.execute(sql, new CallableStatementCallback<Integer>() {@Overridepublic Integer doInCallableStatement(CallableStatement cs)throws SQLException, DataAccessException {// TODO Auto-generated method stubcs.setInt(1, userId);cs.registerOutParameter(2, Types.INTEGER);cs.execute();return cs.getInt(2);}});return num;}

?

轉(zhuǎn)載于:https://www.cnblogs.com/IvySue/p/6513423.html

總結(jié)

以上是生活随笔為你收集整理的《Spring》（十六）---- JDBC的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。