Mybatis中的mapper.xml文件学习和使用

Love The Way You Lie 2021-09-21 03:10 291阅读 0赞

# Mybatis中的mapper.xml文件学习和使用 #

本文是基于Windows 10系统环境，进行Mybatis中的mapper.xml的学习和使用

*  **Windows 10**
 *  **Mybatis**

--------------------

## 一、mapper.xml书写规范 ##

### (1) select语句 ###

<select id="getUserByMap" parameterType="java.util.Map" resultType="java.util.Map">
            select * from user
            <where>
                1=1
                <if test="parameter != null and parameter.username != null and parameter.username != ''">
                    and username like concat('%',#{parameter.username},'%')
                </if>
                <if test="parameter != null and parameter.age != null">
                    and age = #{parameter.age}
                </if>
                <if test="filters != null and filters.authority != null">
                    and authority in
                    <foreach item="item" index="index" collection="filters.authority" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="filters != null and filters.nickname != null">
                    and nickname in
                    <foreach item="item" index="index" collection="filters.nickname" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
                <choose>
                <when test="type != null and mobile != null and type=='mobile'" >
                    and mobile = #{mobile}
                </when>
                <otherwise>
                    and account = #{account}
                </otherwise>
            </choose>
            </where>
            <if test="sorter != null and sorter.field != null and sorter.order != null">
                order by ${sorter.field} ${sorter.order}
            </if>
            <if test="pagination != null and pagination.pageSize != null and pagination.offset != null">
                limit #{pagination.offset},#{pagination.pageSize}
            </if>
        </select>

## 二、注意事项 ##

### (1) order by后面的参数不生效 ###

在mybatis中使用下面的语句，不会生效

select XXXX from table order by #{column} #{order}

mybatis会将上面的语句翻译为

select XXXX from table order by "authority" "desc"

将\#\{ \}改为$\{ \}，语句生效

select XXXX from table order by ${column} ${order}

mybatis会将上面的语句翻译为

select XXXX from table order by authority desc

## 三、Mybatis中javaType和jdbcType对应关系 ##

JDBC Type           Java Type  
    CHAR                String  
    VARCHAR             String  
    LONGVARCHAR         String  
    NUMERIC             java.math.BigDecimal  
    DECIMAL             java.math.BigDecimal  
    BIT             	boolean  
    BOOLEAN             boolean  
    TINYINT             byte  
    SMALLINT            short  
    INTEGER             int  
    BIGINT              long  
    REAL                float  
    FLOAT               double  
    DOUBLE              double  
    BINARY              byte[]  
    VARBINARY           byte[]  
    LONGVARBINARY       byte[]  
    DATE                java.sql.Date  
    TIME                java.sql.Time  
    TIMESTAMP           java.sql.Timestamp  
    CLOB                Clob  
    BLOB                Blob  
    ARRAY               Array  
    DISTINCT            mapping of underlying type  
    STRUCT              Struct  
    REF                 Ref  
    DATALINK            java.net.URL[color=red][/color]  
    
    <resultMap type="java.util.Map" id="resultjcm">  
      <result property="FLD_NUMBER" column="FLD_NUMBER" javaType="double" jdbcType="NUMERIC"/>  
      <result property="FLD_VARCHAR" column="FLD_VARCHAR" javaType="string" jdbcType="VARCHAR"/>  
      <result property="FLD_DATE" column="FLD_DATE" javaType="java.sql.Date" jdbcType="DATE"/>  
      <result property="FLD_INTEGER" column="FLD_INTEGER" javaType="int" jdbcType="INTEGER"/>  
      <result property="FLD_DOUBLE" column="FLD_DOUBLE" javaType="double" jdbcType="DOUBLE"/>  
      <result property="FLD_LONG" column="FLD_LONG" javaType="long" jdbcType="INTEGER"/>  
      <result property="FLD_CHAR" column="FLD_CHAR" javaType="string" jdbcType="CHAR"/>  
      <result property="FLD_BLOB" column="FLD_BLOB" javaType="[B" jdbcType="BLOB" />  
      <result property="FLD_CLOB" column="FLD_CLOB" javaType="string" jdbcType="CLOB"/>  
      <result property="FLD_FLOAT" column="FLD_FLOAT" javaType="float" jdbcType="FLOAT"/>  
      <result property="FLD_TIMESTAMP" column="FLD_TIMESTAMP" javaType="java.sql.Timestamp" jdbcType="TIMESTAMP"/>  
     </resultMap>

--------------------