1，用户分页的参数类
package hwt.Utils;
import java.util.Map;
public class PageEntity {
private Integer page; //目前是第几页
private Integer size; //每页大小
private Map params; //传入的参数
private String orderColumn;
private String orderTurn = "ASC";
public String getOrderColumn() {
return orderColumn;
}
public void setOrderColumn(String orderColumn) {
this.orderColumn = orderColumn;
}
public String getOrderTurn() {
return orderTurn;
}
public void setOrderTurn(String orderTurn) {
this.orderTurn = orderTurn;
}
public Integer getPage() {
return page;
}
public void setPage(Integer page) {
this.page = page;
}
public Integer getSize() {
return size;
}
public void setSize(Integer size) {
this.size = size;
}
public Map getParams() {
return params;
}
public void setParams(Map params) {
this.params = params;
}
}
2，分页结果类
package hwt.Utils;
import java.util.ArrayList;
import java.util.List;
/**
* 分页结果
* @author hwt
*
*/
public class PagingResult<T> {
//当前页
private int currentPage;
//总共记录条数
private int totalSize;
//结果集
private List<T> resultList = new ArrayList<T>();
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getTotalSize() {
return totalSize;
}
public void setTotalSize(int totalSize) {
this.totalSize = totalSize;
}
public List<T> getResultList() {
return resultList;
}
public void setResultList(List<T> resultList) {
this.resultList = resultList;
}
}
3，BaseDAO接口
package hwt.DAO;
import hwt.Utils.PageEntity;
import hwt.Utils.PagingResult;
import java.io.Serializable;
import java.util.List;
import java.util.Map;
/**
* baseDAO
* @author hwt
*
*/
public interface BaseDAO<T,PK extends Serializable> {
/**
* 新增实体
* @param entity
* @return 影响记录条数
*/
public abstract int insert(T entity);
/**
* 修改一个实体对象（UPDATE一条记录）
* @param entity 实体对象
* @return 修改的对象个数，正常情况=1
*/
public abstract int update(T entity);
/**
* 修改符合条件的记录
* <p>此方法特别适合于一次性把多条记录的某些字段值设置为新值（定值）的情况，比如修改符合条件的记录的状态字段</p>
* <p>此方法的另一个用途是把一条记录的个别字段的值修改为新值（定值），此时要把条件设置为该记录的主键</p>
* @param param 用于产生SQL的参数值，包括WHERE条件、目标字段和新值等
* @return 修改的记录个数，用于判断修改是否成功
*/
public abstract int updateParam(Map param);
/**
* 按主键删除记录
* @param primaryKey 主键对象
* @return 删除的对象个数，正常情况=1
*/
public abstract int delete(PK primaryKey);
/**
* 删除符合条件的记录
* <p><strong>此方法一定要慎用，如果条件设置不当，可能会删除有用的记录！</strong></p>
* @param param 用于产生SQL的参数值，包括WHERE条件（其他参数内容不起作用）
* @return
*/
public abstract int deleteParam(Map param);
/**
* 清空表，比delete具有更高的效率，而且是从数据库中物理删除（delete是逻辑删除，被删除的记录依然占有空间）
* <p><strong>此方法一定要慎用！</strong></p>
* @return
*/
public abstract int truncate();
/**
* 查询整表总记录数
* @return 整表总记录数
*/
public abstract int count();
/**
* 查询符合条件的记录数
* @param param 查询条件参数，包括WHERE条件（其他参数内容不起作用）。此参数设置为null，则相当于count()
* @return
*/
public abstract int count(Object param);
/**
* 按主键取记录
* @param primaryKey 主键值
* @return 记录实体对象，如果没有符合主键条件的记录，则返回null
*/
public abstract T get(PK primaryKey);
/**
* 取全部记录
* @return 全部记录实体对象的List
*/
public abstract List<T> select();
/**
* 按条件查询记录
* @param param 查询条件参数，包括WHERE条件、分页条件、排序条件
* @return 符合条件记录的实体对象的List
*/
public abstract List<T> selectParam(Map param);
/**
* 按条件查询记录，并处理成分页结果
* @param param 查询条件参数，包括WHERE条件、分页条件、排序条件
* @return PaginationResult对象，包括（符合条件的）总记录数、页实体对象List等
*/
public abstract PagingResult<T> selectPagination(PageEntity param);
/**
* 批量插入
* @param list
*/
public abstract int insertBatch(final List<T> list);
/**
* 批量修改
* @param list
*/
public abstract int updateBatch(final List<T> list);
/**
* 批量删除
* @param list
*/
public abstract int deleteBatch(final List<PK> list);
}
4，BaseDAO的实现类
package hwt.DAO;
import hwt.Utils.PageEntity;
import hwt.Utils.PagingResult;
import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.RowBounds;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.support.SqlSessionDaoSupport;
/**
* baseDAO的实现基类
*
* @author hwt
*
* @param <T>
* @param <PK>
*/
public class BaseDaoImpl<T, PK extends Serializable> extends
SqlSessionDaoSupport implements BaseDAO<T, PK> {
// mapper.xml中的namespace
private String namespace;
// sqlmap.xml定义文件中对应的sqlid
public static final String SQLID_INSERT = "insert";
public static final String SQLID_INSERT_BATCH = "insertBatch";
public static final String SQLID_UPDATE = "update";
public static final String SQLID_UPDATE_PARAM = "updateParam";
public static final String SQLID_UPDATE_BATCH = "updateBatch";
public static final String SQLID_DELETE = "delete";
public static final String SQLID_DELETE_PARAM = "deleteParam";
public static final String SQLID_DELETE_BATCH = "deleteBatch";
public static final String SQLID_TRUNCATE = "truncate";
public static final String SQLID_SELECT = "select";
public static final String SQLID_SELECT_PK = "selectPk";
public static final String SQLID_SELECT_PARAM = "selectParam";
public static final String SQLID_SELECT_FK = "selectFk";
public static final String SQLID_COUNT = "count";
public static final String SQLID_COUNT_PARAM = "countParam";
@Resource(name = "sqlSessionTemplate")
public void setSuperSqlSessionTemplate(SqlSessionTemplate sqlSessionTemplate) {
super.setSqlSessionTemplate(sqlSessionTemplate);
}
public String getNamespace() {
return namespace;
}
public void setNamespace(String namespace) {
this.namespace = namespace;
}
@Override
public int insert(T entity) {
int rows = 0;
try {
rows = getSqlSession().insert(namespace + "." + SQLID_INSERT,
entity);
} catch (Exception e) {
e.printStackTrace();
}
return rows;
}
@Override
public int update(T entity) {
int rows = 0;
try {
rows = getSqlSession().update(namespace + "." + SQLID_UPDATE,
entity);
} catch (Exception e) {
e.printStackTrace();
}
return rows;
}
@Override
public int updateParam(Map param) {
int rows = 0;
try {
rows = getSqlSession().update(namespace + "." + SQLID_UPDATE_PARAM,
param);
} catch (Exception e) {
e.printStackTrace();
}
return rows;
}
@Override
public int delete(PK primaryKey) {
int rows = 0;
try {
rows = getSqlSession().delete(namespace + "." + SQLID_DELETE,
primaryKey);
} catch (Exception e) {
e.printStackTrace();
}
return rows;
}
@Override
public int deleteParam(Map param) {
int rows = 0;
try {
rows = getSqlSession().delete(namespace + "." + SQLID_DELETE_PARAM,
param);
} catch (Exception e) {
e.printStackTrace();
}
return rows;
}
@Override
public int truncate() {
int rows = 0;
try {
rows = getSqlSession().delete(namespace + "." + SQLID_TRUNCATE);
} catch (Exception e) {
e.printStackTrace();
}
return rows;
}
@Override
public int count() {
int result = 0;
try {
result = getSqlSession().selectOne(namespace + "." + SQLID_COUNT);
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
@Override
public int count(Object param) {
int result = 0;
try {
result = getSqlSession().selectOne(namespace + "." + SQLID_COUNT_PARAM,param);
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
@Override
public T get(PK primaryKey) {
try {
return getSqlSession().selectOne(namespace + "." + SQLID_SELECT_PK,primaryKey);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
@Override
public List<T> select() {
try {
return getSqlSession().selectList(namespace + "." + SQLID_SELECT);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
@Override
public List<T> selectParam(Map param) {
try {
return getSqlSession().selectList(namespace + "." + SQLID_SELECT_PARAM,param);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
@Override
public PagingResult<T> selectPagination(PageEntity pageEntity) {
try {
int page = pageEntity.getPage() == null || "".equals(pageEntity.getPage()) ? 1 : pageEntity.getPage(); //默认为第一页
int size = pageEntity.getSize() == null || "".equals(pageEntity.getSize()) ? 15 : pageEntity.getSize();; //默认每页15个
RowBounds rowBounds = new RowBounds((page-1)*size, size);
Map param = pageEntity.getParams();
if (param != null) {
param.put("orderColumn", pageEntity.getOrderColumn());
param.put("orderTurn", pageEntity.getOrderTurn());
}else {
param = new HashMap();
param.put("orderColumn", pageEntity.getOrderColumn());
param.put("orderTurn", pageEntity.getOrderTurn());
}
List<T> resultList = getSqlSession().selectList(namespace + "." + SQLID_SELECT_PARAM,param,rowBounds);
int totalSize = count(pageEntity.getParams());
PagingResult<T> pagingResult = new PagingResult<T>();
pagingResult.setCurrentPage(page);
pagingResult.setTotalSize(totalSize);
pagingResult.setResultList(resultList);
return pagingResult;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
@Override
public int insertBatch(List<T> list) {
try {
return getSqlSession().insert(namespace + "." + SQLID_INSERT_BATCH,list);
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
@Override
public int updateBatch(List<T> list) {
int rows = 0;
try {
for (T t : list) {
rows = rows + getSqlSession().update(namespace + "." + SQLID_UPDATE, t);
}
} catch (Exception e) {
e.printStackTrace();
}
return rows;
}
@Override
public int deleteBatch(List<PK> list) {
try {
return getSqlSession().delete(namespace + "." + SQLID_DELETE_BATCH,list);
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
/**
* 日志打印
* @param sqlId
* @param param
*/
public void printLog(String sqlId,Object param){
Configuration configuration = getSqlSession().getConfiguration();
//sqlId为配置文件中的sqlid
MappedStatement mappedStatement = configuration.getMappedStatement(sqlId);
//param为传入到sql语句中的参数
BoundSql boundSql = mappedStatement.getBoundSql(param);
//得到sql语句
String sql = boundSql.getSql().trim();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
System.out.println("info-sql: "+sdf.format(new Date())+" "+sql);
}
}
5，Emp的映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="hwt.Mapper.EmpMapper">
<!-- 结果集 -->
<resultMap id="empResult" type="Emp">
<id property="empid" column="empid" />
<result property="empname" column="empname"/>
<!-- 多对一的关系，注意resultMap的名字为 引用的namespace+resultMap的Id -->
<association property="dep" column="depid" javaType="Dep" resultMap="hwt.Mapper.DepMapper.depResult"/>
</resultMap>
<!-- 新增emp实体类 -->
<insert id="insert" parameterType="Emp">
insert into emp(empid,empname,depid) values(#{empid,jdbcType=INTEGER},#{empname,jdbcType=VARCHAR},#{dep.depid,jdbcType=INTEGER})
</insert>
<!-- 更新对象 -->
<update id="update" parameterType="Emp">
update emp
<set>
<if test="empname != null">empname=#{empname,jdbcType=VARCHAR},</if>
<if test="dep != null">depid=#{dep.depid,jdbcType=INTEGER},</if>
</set>
<where>
<if test="empid != null">
empid = #{empid,jdbcType=INTEGER}
</if>
</where>
</update>
<!-- 更新对象（参数） -->
<update id="updateParam" parameterType="java.util.Map">
update emp
<set>
<if test="empname != null">empname=#{empname,jdbcType=VARCHAR},</if>
<if test="dep != null">depid=#{depid,jdbcType=INTEGER},</if>
</set>
<where>
<if test="empid != null">
empid = #{empid,jdbcType=INTEGER}
</if>
</where>
</update>
<!-- 批量更新 -->
<update id="updateBatch" parameterType="Emp">
<!-- collection可以是List对于list,数组对于array,Map对应ids -->
<foreach collection="list" item="emp" separator=";">
update emp
<set>
<if test="emp.empname != null">empname= #{emp.empname,jdbcType=VARCHAR},</if>
<if test="emp.dep != null">depid= #{emp.dep.depid,jdbcType=INTEGER},</if>
</set>
<where>
<if test="emp.empid != null">
empid = #{emp.empid,jdbcType=INTEGER}
</if>
</where>
</foreach>
</update>
<!-- 根据主键删除 -->
<delete id="deletePK" parameterType="int">
delete from emp where empid = #{empid}
</delete>
<!-- 根据参数删除 -->
<delete id="deleteParam" parameterType="java.util.Map">
delete from emp
<where>
<if test="empname != null">empname = #{empname}</if>
<if test="depid != null"> and depid = #{depid}</if>
<if test="empid != null"> and empid = #{empid}</if>
</where>
</delete>
<!-- 批量删除 -->
<delete id="deleteBatch">
delete from emp where empid in
<trim prefix="(" suffix=")" suffixOverrides=",">
<foreach collection="list" item="pk" separator=",">
#{pk}
</foreach>
</trim>
</delete>
<!-- 批量插入 -->
<insert id="insertBatch" parameterType="arraylist">
insert into emp(empid,empname,depid)
<!-- collection可以是List对于list,数组对于array,Map对应ids -->
<foreach collection="list" item="emp" index="index" separator="union all">
select #{emp.empid,jdbcType=NUMERIC},#{emp.empname,jdbcType=VARCHAR},#{emp.dep.depid,jdbcType=NUMERIC} from dual
</foreach>
</insert>
<!-- count -->
<select id="count" resultType="int">
select count(*) from emp e
</select>
<select id="countParam" parameterType="java.util.Map" resultType="int">
select count(*) from emp e
<where>
<if test="empname != null">empname = #{empname}</if>
<if test="depid != null"> and depid = #{depid}</if>
<if test="empid != null"> and empid = #{empid}</if>
</where>
</select>
<!-- 查询（参数） -->
<select id="selectParam" parameterType="java.util.Map" resultType="Emp">
select * from emp
<where>
<if test="empname != null">empname = #{empname}</if>
<if test="depid != null"> and depid = #{depid}</if>
<if test="empid != null"> and empid = #{empid}</if>
</where>
<if test="orderColumn != null">
order by ${orderColumn}
<if test="orderTurn != null">
${orderTurn}
</if>
</if>
</select>
</mapper>