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>