数据库类型和java类型的映射

 

https://blog.csdn.net/qq_39019865/article/details/80800649

mybatis中的映射实例搜集

xml

<?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="com.hainei.mapper.ipam.IpamPerformanceMapper">
    <resultMap id="BaseResultMap" type="com.hainei.pojo.model.ipam.IpamPerformance">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="content" property="content" jdbcType="VARCHAR"/>
        <result column="department" property="department" jdbcType="VARCHAR"/>
        <result column="personnel" property="personnel" jdbcType="VARCHAR"/>
        <result column="amount" property="amount" jdbcType="DECIMAL"/>
        <result column="period" property="period" jdbcType="TIMESTAMP"/>
        <result column="remark" property="remark" jdbcType="VARCHAR"/>
        <result column="type" property="type" jdbcType="TINYINT"/>
        <result column="status" property="status" jdbcType="TINYINT"/>
        <result column="is_deleted" property="isDeleted" jdbcType="TINYINT"/>
        <result column="gmt_created_by" property="gmtCreatedBy" jdbcType="VARCHAR"/>
        <result column="gmt_created_on" property="gmtCreatedOn" jdbcType="TIMESTAMP"/>
        <result column="gmt_updated_by" property="gmtUpdatedBy" jdbcType="VARCHAR"/>
        <result column="gmt_updated_on" property="gmtUpdatedOn" jdbcType="TIMESTAMP"/>
        <result column="revision" property="revision" jdbcType="INTEGER"/>
    </resultMap>
    <resultMap id="ViewMap" type="com.hainei.pojo.vo.ipam.IpamPerformanceVO">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="content" property="content" jdbcType="VARCHAR"/>
        <result column="department" property="department" jdbcType="VARCHAR"/>
        <result column="personnel" property="personnel" jdbcType="VARCHAR"/>
        <result column="amount" property="amount" jdbcType="DECIMAL"/>
        <result column="period" property="period" jdbcType="TIMESTAMP"/>
        <result column="remark" property="remark" jdbcType="VARCHAR"/>
        <result column="type" property="type" jdbcType="TINYINT"/>
        <result column="status" property="status" jdbcType="TINYINT"/>
    </resultMap>
    <select id="listByKeys" resultMap="ViewMap">
        SELECT p.id,content,department,amount,period,p.remark,p.type,p.`status`,personnel,b.`name`
        personnelName,o.`name` departmentName
        FROM ipam_performance p LEFT JOIN base_user b on p.personnel = b.id
        LEFT JOIN base_organization o on p.department = o.id
        WHERE p.is_deleted = 0 and p.type = #{type}
        <if test="keyWord != null and keyWord != ''">
            <if test="type == 0 or type == 1 or type == 2">
                and CONCAT(p.content,p.amount,IFNULL(b.`name`,''),IFNULL(o.`name`,'')) like
                concat('%',concat(#{keyWord},'%'))
            </if>
            <if test="type == 3 ">
                and CONCAT(p.content,p.amount) like concat('%',concat(#{keyWord},'%'))
            </if>
            <if test="type == 4 or type ==5 or type ==6 or type ==7 or type ==8 or type ==9">
                and CONCAT(p.amount) like concat('%',concat(#{keyWord},'%'))
            </if>
        </if>
        ORDER BY p.gmt_created_on DESC
    </select>
    <select id="settleInfo" resultType="com.hainei.pojo.vo.ipam.IpamPerformanceRecordVO">
        SELECT SUM(IF(type=0 ,amount,0)) bouns,SUM(IF(type=1 ,amount,0)) punish,
        SUM(IF(type=2 ,amount,0)) commission,SUM(IF(type=3 ,amount,0)) marketExpenses,SUM(IF(type=4 ,amount,0)) salesDivisionAllocation,
        SUM(IF(type=5 ,amount,0)) salesOtherAllocation,SUM(IF(type=6 ,amount,0)) marketCost,SUM(IF(type=7 ,amount,0)) projectCost,
        SUM(IF(type=8 ,amount,0)) technicalDepartmentCosts,SUM(IF(type=9,amount,0)) otherfee
        FROM ipam_performance
        WHERE is_deleted = 0 and YEAR(period) = #{years}
    </select>
    <update id="settle">
        UPDATE ipam_performance
        SET `status` = 1
        WHERE YEAR(period) = #{years}
    </update>
    <update id="updateBySelective">
        update ipam_performance
        <set>
            content = #{content,jdbcType=VARCHAR},
            department = #{department,jdbcType=VARCHAR},
            personnel = #{personnel,jdbcType=VARCHAR},
            amount = #{amount,jdbcType=DECIMAL},
            period = #{period,jdbcType=TIMESTAMP},
            remark = #{remark,jdbcType=VARCHAR},
            type = #{type,jdbcType=TINYINT},
            gmt_updated_by = #{gmtUpdatedBy,jdbcType=VARCHAR},
            gmt_updated_on = #{gmtUpdatedOn,jdbcType=TIMESTAMP},
        </set>
        where id = #{id,jdbcType=VARCHAR}
    </update>

    <select id="getAmount" resultType="bigdecimal">
        SELECT COALESCE(SUM(amount),0)
        FROM ipam_performance
        WHERE YEAR(period) = #{years} AND personnel = #{userId} AND type = #{type} and is_deleted = 0
    </select>

    <select id="getDepartAmount" resultType="bigdecimal">
        SELECT COALESCE(SUM(p.amount),0)
        FROM ipam_performance p LEFT JOIN  base_organization o on p.department = o.id
        WHERE YEAR(period) = #{years}  AND p.type = #{type} and p.content ="部门" and p.is_deleted = 0 and o.`name` = "技术发展部"
    </select>
    <select id="listByYears" resultType="com.hainei.pojo.vo.ipam.IpamPerformancePageInfoVO">
        SELECT p.id,content,amount,period,p.remark
        FROM ipam_performance p LEFT JOIN base_user b on p.personnel = b.id
        LEFT JOIN base_organization o on p.department = o.id
        WHERE p.is_deleted = 0 and YEAR(period) = #{years} and p.type = #{type}
        <if test="type == 0 or type == 1 or type == 2">
            <if test="userId != null">
                and personnel = #{userId}
            </if>
            <if test="userId == null">
                and content ="部门"
            </if>
        </if>
        <if test="status == 1">
            and status = #{status}
        </if>
        ORDER BY p.period DESC
    </select>

</mapper>

model

package com.hainei.pojo.model.ipam;

import java.math.BigDecimal;
import java.util.Date;
import javax.persistence.*;

@Table(name = "ipam_performance")
public class IpamPerformance {
    /**
     * 主键
     */
    @Id
    private String id;

    /**
     * 发放、奖惩对象，项目名称，花费内容
     */
    private String content;

    /**
     * 部门
     */
    private String department;

    /**
     * 人员
     */
    private String personnel;

    /**
     * 金额、增加金额
     */
    private BigDecimal amount;

    /**
     * 时间、支出时间
     */
    private Date period;

    /**
     * 备注
     */
    private String remark;

    /**
     * 0资金发放1奖惩2项目提成3市场费用支出4销售部门分推5销售其他分推6市场费用7项目费用8技术部门费用
     */
    private Byte type;

    /**
     * 0已汇算1未汇算
     */
    private Byte status;

    /**
     * 是否逻辑删除
     */
    @Column(name = "is_deleted")
    private Byte isDeleted;

    /**
     * 创建人
     */
    @Column(name = "gmt_created_by")
    private String gmtCreatedBy;

    /**
     * 创建时间
     */
    @Column(name = "gmt_created_on")
    private Date gmtCreatedOn;

    /**
     * 更新人
     */
    @Column(name = "gmt_updated_by")
    private String gmtUpdatedBy;

    /**
     * 更新时间
     */
    @Column(name = "gmt_updated_on")
    private Date gmtUpdatedOn;

    /**
     * 乐观锁
     */
    private Integer revision;

    /**
     * 获取主键
     *
     * @return id - 主键
     */
    public String getId() {
        return id;
    }

    /**
     * 设置主键
     *
     * @param id 主键
     */
    public void setId(String id) {
        this.id = id;
    }

    /**
     * 获取发放、奖惩对象，项目名称，花费内容
     *
     * @return content - 发放、奖惩对象，项目名称，花费内容
     */
    public String getContent() {
        return content;
    }

    /**
     * 设置发放、奖惩对象，项目名称，花费内容
     *
     * @param content 发放、奖惩对象，项目名称，花费内容
     */
    public void setContent(String content) {
        this.content = content;
    }

    /**
     * 获取部门
     *
     * @return department - 部门
     */
    public String getDepartment() {
        return department;
    }

    /**
     * 设置部门
     *
     * @param department 部门
     */
    public void setDepartment(String department) {
        this.department = department;
    }

    /**
     * 获取人员
     *
     * @return personnel - 人员
     */
    public String getPersonnel() {
        return personnel;
    }

    /**
     * 设置人员
     *
     * @param personnel 人员
     */
    public void setPersonnel(String personnel) {
        this.personnel = personnel;
    }

    /**
     * 获取金额、增加金额
     *
     * @return amount - 金额、增加金额
     */
    public BigDecimal getAmount() {
        return amount;
    }

    /**
     * 设置金额、增加金额
     *
     * @param amount 金额、增加金额
     */
    public void setAmount(BigDecimal amount) {
        this.amount = amount;
    }

    /**
     * 获取时间、支出时间
     *
     * @return period - 时间、支出时间
     */
    public Date getPeriod() {
        return period;
    }

    /**
     * 设置时间、支出时间
     *
     * @param period 时间、支出时间
     */
    public void setPeriod(Date period) {
        this.period = period;
    }

    /**
     * 获取备注
     *
     * @return remark - 备注
     */
    public String getRemark() {
        return remark;
    }

    /**
     * 设置备注
     *
     * @param remark 备注
     */
    public void setRemark(String remark) {
        this.remark = remark;
    }

    /**
     * 获取0资金发放1奖惩2项目提成3市场费用支出4销售部门分推5销售其他分推6市场费用7项目费用8技术部门费用
     *
     * @return type - 0资金发放1奖惩2项目提成3市场费用支出4销售部门分推5销售其他分推6市场费用7项目费用8技术部门费用
     */
    public Byte getType() {
        return type;
    }

    /**
     * 设置0资金发放1奖惩2项目提成3市场费用支出4销售部门分推5销售其他分推6市场费用7项目费用8技术部门费用
     *
     * @param type 0资金发放1奖惩2项目提成3市场费用支出4销售部门分推5销售其他分推6市场费用7项目费用8技术部门费用
     */
    public void setType(Byte type) {
        this.type = type;
    }

    /**
     * 获取0已汇算1未汇算
     *
     * @return status - 0已汇算1未汇算
     */
    public Byte getStatus() {
        return status;
    }

    /**
     * 设置0已汇算1未汇算
     *
     * @param status 0已汇算1未汇算
     */
    public void setStatus(Byte status) {
        this.status = status;
    }

    /**
     * 获取是否逻辑删除
     *
     * @return is_deleted - 是否逻辑删除
     */
    public Byte getIsDeleted() {
        return isDeleted;
    }

    /**
     * 设置是否逻辑删除
     *
     * @param isDeleted 是否逻辑删除
     */
    public void setIsDeleted(Byte isDeleted) {
        this.isDeleted = isDeleted;
    }

    /**
     * 获取创建人
     *
     * @return gmt_created_by - 创建人
     */
    public String getGmtCreatedBy() {
        return gmtCreatedBy;
    }

    /**
     * 设置创建人
     *
     * @param gmtCreatedBy 创建人
     */
    public void setGmtCreatedBy(String gmtCreatedBy) {
        this.gmtCreatedBy = gmtCreatedBy;
    }

    /**
     * 获取创建时间
     *
     * @return gmt_created_on - 创建时间
     */
    public Date getGmtCreatedOn() {
        return gmtCreatedOn;
    }

    /**
     * 设置创建时间
     *
     * @param gmtCreatedOn 创建时间
     */
    public void setGmtCreatedOn(Date gmtCreatedOn) {
        this.gmtCreatedOn = gmtCreatedOn;
    }

    /**
     * 获取更新人
     *
     * @return gmt_updated_by - 更新人
     */
    public String getGmtUpdatedBy() {
        return gmtUpdatedBy;
    }

    /**
     * 设置更新人
     *
     * @param gmtUpdatedBy 更新人
     */
    public void setGmtUpdatedBy(String gmtUpdatedBy) {
        this.gmtUpdatedBy = gmtUpdatedBy;
    }

    /**
     * 获取更新时间
     *
     * @return gmt_updated_on - 更新时间
     */
    public Date getGmtUpdatedOn() {
        return gmtUpdatedOn;
    }

    /**
     * 设置更新时间
     *
     * @param gmtUpdatedOn 更新时间
     */
    public void setGmtUpdatedOn(Date gmtUpdatedOn) {
        this.gmtUpdatedOn = gmtUpdatedOn;
    }

    /**
     * 获取乐观锁
     *
     * @return revision - 乐观锁
     */
    public Integer getRevision() {
        return revision;
    }

    /**
     * 设置乐观锁
     *
     * @param revision 乐观锁
     */
    public void setRevision(Integer revision) {
        this.revision = revision;
    }
}