Spring SqlQuery 使用

/** 
 * Project Name:webblog 
 * File Name:ForumOODao.java 
 * Package Name:edu.bjfu.webblog.daoimpl 
 * Date:2013年9月11日 下午4:39:22 
 * Copyright (c) 2013, zhangzhaoyu0524@163.com All Rights Reserved. 
 * 
*/  

package edu.bjfu.webblog.daoimpl;  

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlFunction;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.object.StoredProcedure;

import edu.bjfu.webblog.domain.Forum;

/** 
 * ClassName:ForumOODao <br/> 
 * Function: 以OO 的方式访问数据库. <br/> 
 * Reason:   SqlQuery 是一个可重用的、线程安全的类，它封装了一个SQL 查询. <br/>
 * 			 其子类 MappingSqlQuery 作为一个更加易用的实现类能够将结果集中的行为
 * 			映射为Java 对象 SqlQuery 还有另外两个扩展类分别是 MappingSqlQueryWithParameters
 * 			和 UpdatableSqlQuery 
 * Date:     2013年9月11日 下午4:39:22 <br/> 
 * @author   zhangzhaoyu 
 * @version   
 * @since    JDK 1.6 
 * @see       
 */
public class ForumOODao {

	@Autowired
	private DataSource dataSource;

	private ForumQuery forumQuery;
	private ForumInsert forumInsert;
	private GetTopicNum getTopicNum;
	private SqlFunction<Integer> forumNumCount;

	//初始化对象
	@PostConstruct
	public void init() {
		this.forumQuery = new ForumQuery(this.dataSource);
		this.forumInsert = new ForumInsert(this.dataSource);
		this.getTopicNum = new GetTopicNum(this.dataSource);

		this.forumNumCount = new SqlFunction<Integer>(dataSource, 
				" SELECT count(*) FROM t_forum ");
		this.forumNumCount.compile();
	}

	public Forum getFourm(int forumId) {
		return this.forumQuery.findObject(forumId);
	}

	public void addForum(Forum forum) {
		this.forumInsert.insert(forum);
	}

	public int getTopicNum(int userId) {
		return this.getTopicNum.getTopicNum(userId);
	}

	public int getForumNum() {
		return this.forumNumCount.run();
	}
	private class ForumQuery extends MappingSqlQuery<Forum>{

		public ForumQuery(DataSource ds) {
			super(ds, "SELECT forum_id, forum_name, forum_desc FROM t_form WHERE forum_id = ? ");
			declareParameter(new SqlParameter(Types.INTEGER));
			compile();
		}

		@Override
		protected Forum mapRow(ResultSet rs, int rowNum) throws SQLException {

			Forum forum = new Forum();
			forum.setForumId(rs.getInt("forum_id"));
			forum.setForumName(rs.getString("forum_name"));
			forum.setForumDesc(rs.getString("forum_desc"));

			return forum;
		}

	}

	private class ForumInsert extends SqlUpdate {

		public ForumInsert(DataSource ds) {
			super(ds, "INSERT INTO t_forum(forum_name, forum_desc) VALUES(:forumName, :forumDesc)");
			declareParameter(new SqlParameter("forumDesc", Types.VARCHAR));
			declareParameter(new SqlParameter("forumName", Types.VARCHAR));
			compile();
		}

		public void insert(Forum forum) {
			Map<String, String> params = new HashMap<String, String>();
			params.put("forumName", forum.getForumName());
			params.put("forumDesc", forum.getForumDesc());
			super.updateByNamedParam(params);
		}
	}

	private class GetTopicNum extends StoredProcedure {

		private static final String SQL = " P_GET_TOPIC_NUM ";

		public GetTopicNum(DataSource ds) {
			setDataSource(ds);
			setSql(SQL);

			declareParameter(new SqlParameter("userId", Types.INTEGER));
			declareParameter(new SqlParameter("outNum", Types.INTEGER));
			compile();
		}

		public int getTopicNum(int userId) {
			Map<String, Integer> map = new HashMap<String, Integer>();
			map.put("userId", userId);
			Map<String, Object> outMap = execute(map);
			return (Integer)outMap.get("outNum");
		}
	}


}