Mybatis use bill

Time:2022-8-18

foreword

Mybatis is truly an outlier among persistence frameworks I've seen because it's sql-centric rather than object and table based mapping. I will talk about some important skills of Mybatis in this article, and compare it with Hibernate, the previous article in this article.

Mybatis configuration

Add the following configuration to the ApplicationContext:

XML<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="mapperLocations" value="classpath*:mapper/*.xml" />
        <property name="configLocation" value="classpath:mybatis-config.xml"/>
    </bean>
    <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg index="0" ref="sqlSessionFactory" />
    </bean>
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="sbeat.dao" />
</bean>

Then do further configuration in mybatis-config.xml.

XML<configuration>

    <typeAliases>
        <package name="sbeat.model" />
    </typeAliases>

</configuration>    

Mybatis Pagination

Mybatis does not support paging, so I use the PageHelper plugin, first add the configuration to your Mybatis configuration file

XML<plugins>
        <!-- com.github.pagehelper为PageHelper类所在包名 -->
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <property name="dialect" value="mysql" />

            <!-- 该参数默认为false -->
            <!-- 设置为true时,会将RowBounds第一个参数offset当成pageNum页码使用 -->
            <!-- 和startPage中的pageNum效果一样 -->
            <!-- <property name="offsetAsPageNum" value="false" /> -->

            <!-- 该参数默认为false -->
            <!-- 设置为true时,使用RowBounds分页会进行count查询 -->
            <!-- <property name="rowBoundsWithCount" value="false" /> -->

            <!-- 设置为true时,如果pageSize=0或者RowBounds.limit = 0就会查询出全部的结果 -->
            <!-- (相当于没有执行分页查询,但是返回结果仍然是Page类型) -->
            <property name="pageSizeZero" value="true" />

            <!-- 3.3.0版本可用 - 分页参数合理化,默认false禁用 -->
            <!-- 启用合理化时,如果pageNum<1会查询第一页,如果pageNum>pages会查询最后一页 -->
            <!-- 禁用合理化时,如果pageNum<1或pageNum>pages会返回空数据 -->
            <property name="reasonable" value="false" />

            <!-- 3.5.0版本可用 - 为了支持startPage(Object params)方法 -->
            <!-- 增加了一个`params`参数来配置参数映射,用于从Map或ServletRequest中取值 -->
            <!-- 可以配置pageNum,pageSize,count,pageSizeZero,reasonable,不配置映射的用默认值 -->
            <!-- 不理解该含义的前提下,不要随便复制该配置 -->
            <!-- <property name="params" value="pageNum=start;pageSize=limit;" /> -->
        </plugin>
</plugins>

Then add the following sentence before the query that requires pagination:

javaPageHelper.startPage(pageNum,PAGE_SIZE);

sql tags in Mybatis

Since Mybatis generates SQL statements through text replacement assembly, it is not difficult to find that its insertion and update are also static, and the insertion of null in the object is also null. The default value of your database doesn't work and you get null, so how to fix this?

XML<sql id="userColumn">
        <trim suffixOverrides=",">
            <if test="id!=null">id,</if>
            <if test="phone!=null">phone,</if>
            <if test="email!=null">email,</if>
            <if test="photo!=null">photo,</if>
        </trim>
</sql>

<sql id="userValue">
        <trim suffixOverrides=",">
            <if test="id!=null">#{id},</if>
            <if test="phone!=null">#{phone},</if>
            <if test="email!=null">#{email},</if>
            <if test="photo!=null">#{photo},</if>
        </trim>
</sql>

<insert id="insert" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        insert into user (<include refid="userColumn" />)
        values (<include refid="userValue"></include>)
</insert>

We solved this problem by using include and sql tags.

Mybatis typeHandler

Sometimes we often want to store Collection or other objects directly in the database in the form of Json strings instead of opening a table. Although the general opinion is that this approach is not approved, this demand actually exists. How can the conversion of strings and objects be done in DAO without giving the upper layer an explicit conversion?

You can use a custom Typehandler, an example is given below

java@MappedJdbcTypes(JdbcType.VARCHAR)
public class JSONHandler implements TypeHandler<Object> {

    /**
      * Delimiter for json data and class name
      * */
     private static final char SPLIT = '/'; 


     /**
      * json convert to object
      * */
     private Object jsonToObject(String json) throws RuntimeException{
        if (json == null) {
            return null;
        }
        int index = json.lastIndexOf(SPLIT);
        if (index < 0) {
            return null;
        }
        String key = json.substring(index + 1, json.length());
        json = json.substring(0, index);
        Class<?> cls = null;
        try {
            cls = Class.forName(key);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(&quot;The specified class could not be found when serializing to json&quot;, e);
        }
        Object ob = JSON.parseObject(json, cls);
        return ob;
    }

    public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        if(parameter == null){
             ps.setString(i, null);
             return;
        }
            String json = JSON.toJSONString(parameter);
            json  = json + SPLIT + parameter.getClass().getName();
            ps.setString(i, json);

    }

    public Object getResult(ResultSet rs, String columnName) throws SQLException {
        String  json = rs.getString(columnName);
        return  jsonToObject(json);
    }

    public Object getResult(ResultSet rs, int columnIndex) throws SQLException {
        String json=rs.getString(columnIndex);
        return jsonToObject(json);
    }

    public Object getResult(CallableStatement cs, int columnIndex) throws SQLException {
        String  json = cs.getString(columnIndex);
        return  jsonToObject(json);
    }


}

First set the JDBCType to be processed, which is obviously a variable-length character, then implement the given interface, and finally add such a sentence to the Mybatis configuration file.

XML<typeHandlers>
        <typeHandler  handler="sbeat.util.helper.JSONHandler" javaType="java.util.List"/>
        <typeHandler  handler="sbeat.util.helper.JSONHandler" javaType="java.util.Map"/>
    </typeHandlers>

Both Map and List are handled by this handler, but I actually found that this does not seem to be useful. What is useful is to specify it explicitly in the Mapper.xml file, as shown below.

XML<insert>
insert (tags) values (#{tags,typeHandler=sbeat.util.helper.JSONHandler})
</insert>
<resultMap type="Employee" id="employeeResult">
    <result property="tags" column="tags" javaType="java.util.List"  typeHandler="sbeat.util.helper.JSONHandler"/>
</resultMap>

Mybatis foreign key query

Foreign key queries need to use the association tag of resultMap, as shown below

XML    <resultMap type="Message" id="msgResultMap">
        <id property="id" column="id" />
        <result property="created" column="created" />
        <result property="title" column="title" />
        <result property="content" column="content" />
        <result property="has_read" column="has_read" />
        <result property="msgType" column="msgType" />
        <result property="receiverId" column="receiverId" />        
        <association property="sender" javaType="User">
            <id property="id" column="userId"/>
            <result property="name" column="name"/>
            <result property="phone" column="phone"/>
            <result property="passwd" column="passwd"/>
            <result property="photo" column="photo"/>
            <result property="email" column="email"/>
            <result property="userType" column="userType"/>
        </association>
    </resultMap>

Mybatis multi-parameter

Use @Params annotation in interface definition and do not define paramType in XML like below

javapublic List<Feedback> findByTradeId(@Param("tradeId") Long tradeId,@Param("ownerType") UserType ownerType);
XML    <select id="findByTradeId" resultType="Feedback">
        select * from feedback where tradeId=#{tradeId} 
        <if test="ownerType!=null">AND ownerType=#{ownerType}</if>
        ORDER BY created DESC
    </select>

Mybatis comma problem

Inattentive commas are often the most common mistakes in using Mybatis, which can be avoided as much as possible by using where and set first-level trim tags, as follows:

XML<trim suffixOverrides=",">
    <if test="id!=null">id,</if>
    <if test="phone!=null">phone,</if>
</trim>
<set>
    <if test="phone!=null">phone=#{phone},</if>
    <if test="name!=null">name=#{name},</if>
</set>
<where>
    <if test="id!=null">id=#{id}</if>
    <choose>
        <when test="logic_delete!=null">AND logic_delete=#{logic_delete}</when>
        <otherwise>
            AND logic_delete=false
        </otherwise>
    </choose>
    <if test="name!=null"> AND name=#{name}</if>
</where>