mybatis xml开发实战

骑猪看日落 2023-02-18 03:14 1阅读 0赞

# 1、使用sql标签和include标签 #

<?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.asiainfo.bi.icmp.common.dao.mapper.SampleDataMapper">
    
      <sql id="columns">
        id,class,task_id,excute_id,data,data_time
      </sql>
    
      <select id="findOne" resultMap="sampleDataResMap">
        select <include refid="columns" />
        from sample_data where task_id=#{taskId} and data->'$.linkurl' = #{url}
      </select>
    
    </mapper>

# 2、参数类型为对象（当有多个参数时，名字用@Param注解标识，且不要写parameterType） #

int insert(@Param("sampleData") SampleData sampleData);

<insert id="insert" parameterType="com.asiainfo.bi.icmp.common.dao.pojo.SampleData">
          insert into sample_data(id,task_id,excute_id,data,data_time) values(#{sampleData.id},
          #{sampleData.taskId},#{sampleData.excuteId},#{sampleData.data},#{sampleData.dataTime})
      </insert>

# 3、参数类型为list #

int insertMany(List<SampleData> sampleDatas);

<insert id="insertMany" parameterType="list">
        insert into sample_data(id,task_id,excute_id,data,data_time) values
        <foreach collection="list" item="item" separator=",">
          (#{item.id},#{item.taskId},#{item.excuteId},
           #{item.data},#{item.dataTime})
        </foreach>
      </insert>

# 4、返回单个对象 #

一般的select,会返回多个对象，这时候返回值要用list类型,但是resultMap或resultType是单个对象的类型

如果能够确定select只会返回一个对象，则返回值类型用该对象的类型

如果不能确定select只返回一个对象，但又想只返回一个对象,则可以用limit1

Urls findOne(@Param("taskId")Integer taskId, @Param("url") String url);

<select id="findOne" resultMap="urlsResMap">
        select <include refid="columns" />
        from urls where task_id=#{taskId} and url = #{url} limit 1
      </select>

# 5、in字句查询 #

<select id="listKeywordByTenderId" parameterType="list" resultType="map">
            SELECT a.tender_id,a.lable_id,b.lablename,a.attr1 FROM tender_lable_relation a LEFT JOIN lable b
            ON b.lableId = a.lable_id WHERE a.tender_id in
            
            <foreach collection="list" open="(" close=")" index="index" item="item" separator=",">
                #{item}
            </foreach>
            group by a.tender_id,a.lable_id
        </select>

List<Map<String,Object>> listKeywordByTenderId(List<Integer> tenderIdList);

# 6、**CDATA** #

**<!\[CDATA\[   \]\]>   以<!\[CDATA\[开始，以\]\]>结束**

**CDATA区不会被解析，用法：****<!\[CDATA\[  <=  \]\]>**