mybatis 几个小技巧

最新推荐文章于 2022-02-10 14:53:11 发布

aizi9860

最新推荐文章于 2022-02-10 14:53:11 发布

阅读量173

点赞数

文章标签： java 数据库

原文链接：http://www.cnblogs.com/zhd-axx/p/6938025.html

版权

1.多个参数传入 dao接口

public interface TrainingUserExtendMapper extends BaseExtMapper {

    public int updateTrainingUserByMobileNum(@Param("offTrainId")Long offTrainId, @Param("mobileNum")Long mobileNum, @Param("signTime")Date signTime);

    public Integer selectByMobileNum(@Param("offTrainId")Long offTrainId, @Param("mobileNum")Long mobileNum);
}

mapper文件

<update id="updateTrainingUserByMobileNum" >
    update TRAINING_USER
    set  IS_SIGN = 1,SIGN_TIME = #{signTime,jdbcType=TIMESTAMP}
    where OFF_TRAIN_ID = #{offTrainId,jdbcType=BIGINT} AND MOBILE_NUM = #{mobileNum,jdbcType=BIGINT}
  </update>
  
  <select id="selectByMobileNum" resultType="Integer"  >
    SELECT 
    count(1)
    FROM TRAINING_USER 
    WHERE OFF_TRAIN_ID = #{offTrainId,jdbcType=BIGINT} AND MOBILE_NUM = #{mobileNum,jdbcType=BIGINT}
  </select>

2 数据库里的字段是 datetime类型

当传入string 类型的时间时，mapp文件可用str_to_date（）方法

params.put("updateDateStart", updateDateStart + " 00:00:00");
//dao 接口
public interface OfflineTrainingExtendMapper {

    
    public List<OfflineTraining> getListTraining(Map<String,Object> params);
}

mapp文件

<select id="getListTraining" resultMap="BaseResultMap">
          SELECT * FROM OFFLINE_TRAINING  WHERE 1=1 
        <include refid="selectTrainingCondition"/>
        <if test="page != null and rows != null">
            LIMIT #{startIndex},#{rows}
        </if>
      </select>
      
      <sql id="selectTrainingCondition">
          <if test="trainingName != null">
              AND NAME LIKE  CONCAT('%', #{trainingName},'%')
          </if>
          <if test="updateDateStart != null">
              AND UPDATE_TIME &gt;= str_to_date(#{updateDateStart}, '%Y-%m-%d %H:%i:%s')
          </if>
          <if test="updateDateEnd != null">
              AND UPDATE_TIME &lt;= str_to_date(#{updateDateEnd}, '%Y-%m-%d %H:%i:%s')
          </if>
          <if test="statuses != null">
              AND STATUS IN (#{statuses})
          </if>
      </sql>

3 当定义的实体类需要额外字段时，可以通过继承的方式可以在mapper里给字段赋值，特殊字段在实体类get方法里赋值

实体类

package cn.com.demos.entity;

import cn.com.demos.entity.base.EntityBase;
import java.io.Serializable;
import java.util.Date;

public class TrainingUser extends EntityBase implements Serializable {
    private Integer type;

    private Long userId;

    private String userName;

    private Long mobileNum;

    private Integer isSign;

    private Date signTime;

    private Float score;

    private Long offTrainId;

    private Integer credit;

    private Date createTime;

    private static final long serialVersionUID = 1L;

    public Integer getType() {
        return type;
    }

    public void setType(Integer type) {
        this.type = type;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName == null ? null : userName.trim();
    }

    public Long getMobileNum() {
        return mobileNum;
    }

    public void setMobileNum(Long mobileNum) {
        this.mobileNum = mobileNum;
    }

    public Integer getIsSign() {
        return isSign;
    }

    public void setIsSign(Integer isSign) {
        this.isSign = isSign;
    }

    public Date getSignTime() {
        return signTime;
    }

    public void setSignTime(Date signTime) {
        this.signTime = signTime;
    }

    public Float getScore() {
        return score;
    }

    public void setScore(Float score) {
        this.score = score;
    }

    public Long getOffTrainId() {
        return offTrainId;
    }

    public void setOffTrainId(Long offTrainId) {
        this.offTrainId = offTrainId;
    }

    public Integer getCredit() {
        return credit;
    }

    public void setCredit(Integer credit) {
        this.credit = credit;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
}

实体类子类

public class TrainingUserExtend extends TrainingUser implements Serializable{

    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    
    private String offlineTrainingName;
    
    private String isSignText;

    public String getOfflineTrainingName() {
        return offlineTrainingName;
    }

    public void setOfflineTrainingName(String offlineTrainingName) {
        this.offlineTrainingName = offlineTrainingName;
    }

    public String getIsSignText() {
        return super.getIsSign()==Constant.ONE?Constant.ISSIGN_TRUE:Constant.ISSIGN_NO;
    }

    public void setIsSignText(String isSignText) {
        this.isSignText = isSignText;
    }
    

}

mapper文件

<?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="cn.com.demos.mapper.extend.TrainingUserExtendMapper">
  <resultMap id="BaseResultMap" type="cn.com.demos.entity.TrainingUser">
    <id column="ID" jdbcType="BIGINT" property="id" />
    <result column="TYPE" jdbcType="INTEGER" property="type" />
    <result column="USER_ID" jdbcType="BIGINT" property="userId" />
    <result column="USER_NAME" jdbcType="VARCHAR" property="userName" />
    <result column="MOBILE_NUM" jdbcType="BIGINT" property="mobileNum" />
    <result column="IS_SIGN" jdbcType="INTEGER" property="isSign" />
    <result column="SIGN_TIME" jdbcType="TIMESTAMP" property="signTime" />
    <result column="SCORE" jdbcType="REAL" property="score" />
    <result column="OFF_TRAIN_ID" jdbcType="BIGINT" property="offTrainId" />
    <result column="CREDIT" jdbcType="INTEGER" property="credit" />
    <result column="CREATE_TIME" jdbcType="TIMESTAMP" property="createTime" />
  </resultMap>
 <resultMap id="TrainingUserExtendResult" type="cn.com.demos.entity.extend.TrainingUserExtend"
        extends="BaseResultMap">        
        <result column="NAME" property="offlineTrainingName" jdbcType="VARCHAR" />
    </resultMap>
  <sql id="Base_Column_List">
    ID, TYPE, USER_ID, USER_NAME, MOBILE_NUM, IS_SIGN, SIGN_TIME, SCORE, OFF_TRAIN_ID,
    CREDIT, CREATE_TIME
  </sql>

<select id="selectByParameter" resultMap="TrainingUserExtendResult" parameterType="map" >
    SELECT
    o.NAME,
    t.ID,t.USER_ID,t.USER_NAME,t.MOBILE_NUM,t.SCORE,t.CREDIT,t.CREATE_TIME
    FROM TRAINING_USER t, OFFLINE_TRAINING o WHERE t.OFF_TRAIN_ID = o.ID
    <if test="offlineTrainingId != null" >
    AND o.ID = #{offlineTrainingId}  
    </if>
    <if test="type != null" >
    AND t.TYPE = #{type}
    </if>
    <if test="userName != null" >
    AND t.USER_NAME LIKE CONCAT('%',#{userName},'%')
    </if>
    ORDER BY CREATE_TIME DESC
    <if test="starNum != null and pageSize != null" >
    LIMIT #{starNum},#{pageSize}
    </if>
</select>

<select id="selectTotalByParameter" resultType="int" parameterType="map" >
    SELECT
    COUNT(t.ID)
    FROM TRAINING_USER t, OFFLINE_TRAINING o WHERE t.OFF_TRAIN_ID = o.ID
    <if test="offlineTrainingId != null" >
    AND t.OFF_TRAIN_ID = #{offlineTrainingId}  
    </if>
    <if test="type != null" >
    AND t.TYPE = #{type}
    </if>
    <if test="isSign != null " >
    AND t.IS_SIGN = #{isSign}
    </if>
    <if test="userName != null" >
    AND t.USER_NAME LIKE CONCAT('%',#{userName},'%')
    </if>
</select>
    
    <select id="selectTrainingUserListByParameter" resultMap="TrainingUserExtendResult" parameterType="map" >
    SELECT
    o.NAME,t.ID,t.USER_ID,t.USER_NAME,t.MOBILE_NUM,t.IS_SIGN,t.SIGN_TIME
    FROM TRAINING_USER t, OFFLINE_TRAINING o WHERE t.OFF_TRAIN_ID = o.ID
    <if test="offlineTrainingId != null " >
    AND t.OFF_TRAIN_ID = #{offlineTrainingId}  
    </if>
    <if test="type != null " >
    AND t.TYPE = #{type}
    </if>
    <if test="isSign != null " >
    AND t.IS_SIGN = #{isSign}
    </if>
    <if test="userName != null " >
    AND t.USER_NAME LIKE CONCAT('%',#{userName},'%')
    </if>
    ORDER BY t.SIGN_TIME DESC
    <if test="starNum != null and pageSize != null" >
    LIMIT #{starNum},#{pageSize}
    </if>
</select>

<update id="updateTrainingUserByMobileNum" >
    update TRAINING_USER
    set  IS_SIGN = 1,SIGN_TIME = #{signTime,jdbcType=TIMESTAMP}
    where OFF_TRAIN_ID = #{offTrainId,jdbcType=BIGINT} AND MOBILE_NUM = #{mobileNum,jdbcType=BIGINT}
  </update>
 
  <select id="selectByMobileNum" resultType="Integer"  >
    SELECT
    count(1)
    FROM TRAINING_USER
    WHERE OFF_TRAIN_ID = #{offTrainId,jdbcType=BIGINT} AND MOBILE_NUM = #{mobileNum,jdbcType=BIGINT}
  </select>
</mapper>

函数：FROM_UNIXTIME
作用：将MYSQL中以INT(11)存储的时间以"YYYY-MM-DD"格式来显示。
语法：FROM_UNIXTIME(unix_timestamp,format)

返回表示 Unix 时间标记的一个字符串，根据format字符串格式化。format可以包含与DATE_FORMAT()函数列出的条目同样的修饰符。

根据format字符串格式化date值。
下列修饰符可以被用在format字符串中：

%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。

例子：

SELECT FROM_UNIXTIME(1234567890, '%Y-%m-%d %H:%i:%S')

1. UNIX时间戳转换为日期用函数： FROM_UNIXTIME() 

一般形式：select FROM_UNIXTIME(1156219870); 

2. 日期转换为UNIX时间戳用函数： UNIX_TIMESTAMP() 

一般形式：Select UNIX_TIMESTAMP('2006-11-04 12:23:00′);

if("year".equals(searchType) || "halfyear".equals(searchType)){
            map.put("searchType", "%Y-%m");
        }
        if("month".equals(searchType)){
            map.put("searchType", "%Y-%m-%d");
        }
        if("day".equals(searchType)){
            map.put("searchType", "%Y-%m-%d %H");
        }
        
        map.put("startTime", startTime); //2017-3-4
        map.put("endTime", endTime);
        map.put("orgIdList", orgIdList);
        map.put("rootOrgId", rootOrgId);

mapper

<select id="selectLoginCount" resultMap="CountResultMap" parameterType="map" >
    select 
   COUNT(1) as COUNT,from_UNIXTIME(LOGIN_TIME/1000,#{searchType}) as TIME 
     FROM USER_LOGIN_EXIT where 1=1 
    <if test="orgIdList != null and orgIdList != '' and orgIdList.size()>0">
         and ORG_ID in
         <foreach item="orgId" index="index" collection="orgIdList"
             open="(" separator="," close=")">
             #{orgId}
         </foreach>
     </if>
    <if test="rootOrgId != null" >
     and  ROOT_ORG_ID = #{rootOrgId}
    </if>
    <if test="startTime != null" >//数据库里 LOGIN_TIME 是int（11）， 除以1000 只得到日期，不得到时分秒  searchType就是传进来的%Y-%m-%d
    and from_UNIXTIME(LOGIN_TIME/1000,#{searchType}) &gt;= #{startTime} //startTime 就是日期  比如 2017-5-6
    </if>
    <if test="endTime != null" >
    and from_UNIXTIME(LOGIN_TIME/1000,#{searchType}) &lt;= #{endTime} 
    </if>
    group by time;
  </select>

4 一个sql语句，可以根据表里某个字段查询某个记录的排名是多少

<select id="selectOrderNoByUserId"  resultType="Integer">
    SELECT
            temp.pm
        FROM
            (
                SELECT
                    @rowNum :=@rowNum + 1 pm,
                    u.*
                FROM
                    (SELECT @rowNum := 0) a,
                    `PERSONAL_POINTS` u
                WHERE ROOT_ORG_ID = #{rootOrgId,jdbcType=BIGINT}
                ORDER BY
                    POINTS DESC,USER_ID ASC
            ) temp
        WHERE
            temp.USER_ID = #{userId,jdbcType=BIGINT}
  </select>

转载于:https://www.cnblogs.com/zhd-axx/p/6938025.html