Struts2+Ibatis+Spring+级联查询

最近闲来没事，做了个struts2+ibatis+spring入门例子 数据库用的是oracle 10。表是scott.emp和scott.dept两张表 级联查询（详细了解  ibatis in action）本例子采用的是后者 

 

dao封装

 

 

package com.huasoft.common.dao;

import java.io.Serializable;
import java.util.List;
import java.util.Map;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

/**
 * 类 编 号： 类 名 称：IBaseDao.java 内容摘要:DAO层封装接口，包含常用的CURD和分页操作 完成日期：2014-3-26
 * 编码作者：马彪
 */
public abstract interface  BaseDao<T> {
	/*分页查询*/

	QueryResult<T> pagedQuery(Map<String, Object> searchParams, Integer pageNumber,Integer pageSize, final String statementId);

}

 

 

    

package com.huasoft.common.dao;

import java.io.Serializable;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.util.StringUtil;
import org.springframework.orm.ibatis.SqlMapClientCallback;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;

import com.ibatis.sqlmap.client.SqlMapExecutor;


/**
 * 类 编 号： 
 * 类 名 称：BaseDaoSupport.java 
 * 内容摘要:DAO层封装使用了泛型，包含常用的CURD和分页操作
 * 完成日期：2014-03-26 编码作者：马彪
 */
@SuppressWarnings("unchecked")
public class BaseDaoImpl<T> extends SqlMapClientDaoSupport implements BaseDao<T> {

	
	private static Logger log=Logger.getLogger(BaseDaoImpl.class);
	
	

	/*
	 * 分页查询
	 * */
	public QueryResult<T> pagedQuery(Map<String, Object> searchParams, Integer pageNumber,Integer pageSize, final String statementId)
	{
		//if(pageNumber==null)pageNumber=1;
	
		Integer total=(Integer)getSqlMapClientTemplate().queryForObject(statementId, searchParams);

		Pageable page=buildPageRequest(pageNumber,pageSize);
		searchParams.put("offset",(pageNumber-1)*pageSize);
		searchParams.put("limit",page.getOffset());
		List list=getSqlMapClientTemplate().queryForList(statementId, searchParams);
		Page pagination=new PageImpl(list,page,total);
		QueryResult queryResult=new QueryResult<T>();
		queryResult.setContent(pagination.getContent());
		queryResult.setTotal(total);
		queryResult.setTotalpages(pagination.getTotalPages());
		
		return queryResult;
	}
	
	
	 
	private PageRequest buildPageRequest(Integer pageNumber, Integer pageSize)
	{
		return new PageRequest(pageNumber, pageSize);
	}
	

	
	
	
	
	
	
}

    

 

 

   分页查询辅助类

 

   

package com.huasoft.common.dao;

import java.util.List;

public class QueryResult<T>
{
	private List<T> content;
	private Integer total;

	private Integer totalpages;

	
	public List<T> getContent()
	{
		return content;
	}
	public void setContent(List<T> content) {
		this.content = content;
	}
	public Integer getTotal() {
		return total;
	}
	public void setTotal(Integer total) {
		this.total = total;
	}
	
	public Integer getTotalpages() {
		return totalpages;
	}
	public void setTotalpages(Integer totalpages) {
		this.totalpages = totalpages;
	}
}

    

 

    

    javabean

 

   

package com.huasoft.common.domain;


import java.util.List;
import com.huasoft.common.domain.Emp
public class Dept 
{
	private String deptno;
	private String dname;
	private String loc;
	private List<Emp> emp;
	
        /*setter 和getter 省略*/

	
}

   

 

   

package com.huasoft.common.domain;

import java.math.BigDecimal;
import java.util.Date;

public class Emp
{
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
   
    private Date hiredate;
    private BigDecimal sal;
    private BigDecimal comm;
    private Integer deptno;
   
   
	
	
}

 

    javabean映射文件

 

 

   

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="EmpData">
  
  
  <typeAlias alias="emp" type="com.huasoft.common.domain.Emp"/>
  <typeAlias alias="dept"type="com.huasoft.common.domain.Dept"/>
  
 
  <resultMap id="empResult" class="emp" >
    <result column="EMPNO" property="empno" jdbcType="INTEGER" />
    <result column="ENAME" property="ename" jdbcType="VARCHAR" />
    <result column="JOB" property="job" jdbcType="VARCHAR" />
    <result column="MGR" property="mgr" jdbcType="INTEGER" />
    <result column="HIREDATE" property="hiredate" jdbcType="TIMESTAMP" />
    <result column="SAL" property="sal" javaType="java.math.BigDecimal" jdbcType="DECIMAL" />
    <result column="COMM" property="comm" jdbcType="DECIMAL" />
    <result column="deptno2" property="deptno" jdbcType="VARCHAR" />
 
  </resultMap>
  
  
  <resultMap id="deptResult" class="dept">
    <result column="deptno1" property="deptno" jdbcType="VARCHAR"/>
    <result column="DNAME" property="dname" jdbcType="VARCHAR"/>
    <result column="LOC" property="loc" jdbcType="VARCHAR"/>
    <result property="problemdata" resultMap="EmpData.empResult" />
  </resultMap>
  
  
  <select id="EmpData.query" parameterClass="java.util.HashMap"  
		resultMap="deptResult">
			select * from 
       (select tab.*, rownum row_num from (select tab1.deptno as deptno1,
                    tab.empno,
                    tab.ename,
                    tab.job,
                    tab.mgr,
                    tab.hiredate,
                    tab.sal,
                    tab.comm,
                    tab.deptno as deptno2,
                    tab1.dname,
                    tab1.loc
                    from scott.emp tab, scott.dept tab1
                    where tab1.deptno = tab.deptno) tab where rownum <![CDATA[
                    <=
                    ]]> #limit#
        )tab1 where row_num>#offset#
  </select>
	
	
	<select id="ProblemData.count"
		parameterClass="java.util.HashMap"  
		resultClass="java.lang.Integer">
		select count(*) from 
       (select tab.*, rownum row_num from (select tab1.deptno as deptno1,
                    tab.empno,
                    tab.ename,
                    tab.job,
                    tab.mgr,
                    tab.hiredate,
                    tab.sal,
                    tab.comm,
                    tab.deptno as deptno2,
                    tab1.dname,
                    tab1.loc
                    from scott.emp tab, scott.dept tab1
                    where tab1.deptno = tab.deptno) tab 
        )tab1 
		
	</select>
</sqlMap>

 

    sqlmap-config.xml

 

 

    

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
    <settings enhancementEnabled="true" maxTransactions="20" maxRequests="32" maxSessions="10"/>
      <!-- common  -->
      
      <sqlMap resource="com/huasoft/common/domain/EmpSqlMap.xml" />
</sqlMapConfig>

 

 

 

   struts2和spring的配置文件，在网上都可以搜的到，这里不再写了。