mybatis有什么用，mybatis文件中SQL總結

頭部

<?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>標簽

<mapper namespace="com.xgh.sportsite.dao.read.IActivityDaoR"> 

</mapper>

實體類與表的映射<resultMap>

<resultMap id="activity" type="com.xgh.sportsite.entity.Activity">

通過resultMap中的id的值確定映射關系，一個文件中是否可以有多個resultMap

<id property="id" column="id" javaType="Long"/>

<result property="unitId" column="unit_id" javaType="long" jdbcType="BIGINT"/>

表中的id屬性是固定的

result有四個屬性

四種語句

select語句

<select id="getListPage" parameterType="hashmap" resultType="hashmap">

id與接口中的方法名相同，

parameterType的取值有hashmap(此處hashmap因該是框架自己定義的或在某個地方定義過，用hashmap代替HashMap)、基本數據類型（java.lang.Long）、自定義類。一般插入和刪除操作使用Long和自定義類型，查詢操作使用hashmap類型。

返回結果使用resultType或resultMap。resultMap的取值一般是hashmap，可用于返回多表關聯查詢的結果；resultType用于返回實體類。

<sql>標簽可以將需要查詢的字段列出來，盡量避免使用select *

<sql id="Base_Column_List">id,   member_id, kind,  context,   remart,    status,    create_date,   update_date,   abopt, data_1,    data_2,    data_3,    data_4
</sql>

和

<include refid="Base_Column_List" />

結合使用

?

查詢中的一些條件判斷

IFNULL(m.nick_name,'')AS nickName

在select語句中，如果為空就返回”“

status是關鍵字，列名如果為status需加上撇號``

<if>標簽 test條件

<if test="kind==1">AND t.member_id = #{memberId}
</if>

order by 默認升序，desc表示降序

limit

limit ${(page-1)*pageSize},${pageSize};

可以在${}表達式中進行計算

<trim>標簽

<trim prefix="WHERE" prefixOverrides="AND |OR ">

?

連接查詢 left/right join??????? on?????

LEFT JOIN tb_order o ON cvt.order_id = o.id

?

模糊查詢 ：like后面的條件為什么要這么寫

<if test="venueName!=null and venueName!='' ">AND cv.venue_name like '%${venueName}%'
</if>

?

UNION（select ...）

UNION（）

?

一個mapper文件有兩個resultMap，注意type的值也可以是java.util.Map

<resultMap type="com.xgh.sportsite.entity.FileData" id="fileDataMap"><result property="id" column="id" javaType="long" jdbcType="BIGINT" /><result property="nid" column="nid" javaType="long" jdbcType="BIGINT" /><result property="instId" column="inst_id" javaType="long" jdbcType="BIGINT" /><result property="instNid" column="inst_nid" javaType="long" jdbcType="BIGINT" /><result property="instCode" column="inst_code" javaType="string" jdbcType="VARCHAR" /><result property="unitId" column="unit_id" javaType="long" jdbcType="BIGINT" /><result property="unitNid" column="unit_nid" javaType="long" jdbcType="BIGINT" /><result property="unitCode" column="unit_code" javaType="string" jdbcType="VARCHAR" /><result property="dataCode" column="data_code" javaType="string" jdbcType="VARCHAR" /><result property="dataType" column="data_type" javaType="int" jdbcType="INTEGER" /><result property="dataId" column="data_id" javaType="long" jdbcType="BIGINT" /><result property="dataVersion" column="data_version" javaType="int" jdbcType="INTEGER" /><result property="path" column="path" javaType="string" jdbcType="VARCHAR" /><result property="relativePath" column="relative_path" javaType="string" jdbcType="VARCHAR" /><result property="fileName" column="file_name" javaType="string" jdbcType="VARCHAR" /><result property="oldName" column="old_name" javaType="string" jdbcType="VARCHAR" /><result property="fileSize" column="file_size" javaType="long" jdbcType="BIGINT" /><result property="fileSuffix" column="file_suffix" javaType="string" jdbcType="VARCHAR" /><result property="status" column="status" javaType="int" jdbcType="INTEGER" /><result property="createDate" column="create_date" javaType="date" jdbcType="TIMESTAMP" /><result property="ord" column="ord" javaType="int" jdbcType="INTEGER" /><result property="type" column="type" javaType="int" jdbcType="INTEGER" /></resultMap><resultMap type="java.util.Map" id="fileDataMap2"><result property="ID" column="id" javaType="long" jdbcType="BIGINT" /><result property="NID" column="nid" javaType="long" jdbcType="BIGINT" /><result property="INST_ID" column="inst_id" javaType="long" jdbcType="BIGINT" /><result property="INST_NID" column="inst_nid" javaType="long" jdbcType="BIGINT" /><result property="INST_CODE" column="inst_code" javaType="string" jdbcType="VARCHAR" /><result property="UNIT_ID" column="unit_id" javaType="long" jdbcType="BIGINT" /><result property="UNIT_NID" column="unit_nid" javaType="long" jdbcType="BIGINT" /><result property="UNIT_CODE" column="unit_code" javaType="string" jdbcType="VARCHAR" /><result property="DATA_CODE" column="data_code" javaType="string" jdbcType="VARCHAR" /><result property="DATA_TYPE" column="data_type" javaType="int" jdbcType="INTEGER" /><result property="DATA_ID" column="data_id" javaType="long" jdbcType="BIGINT" /><result property="DATA_VERSION" column="data_version" javaType="int" jdbcType="INTEGER" /><result property="PATH" column="path" javaType="string" jdbcType="VARCHAR" /><result property="RELATIVE_PATH" column="relative_path" javaType="string" jdbcType="VARCHAR" /><result property="FILE_NAME" column="file_name" javaType="string" jdbcType="VARCHAR" /><result property="OLD_NAME" column="old_name" javaType="string" jdbcType="VARCHAR" /><result property="FILE_SIZE" column="file_size" javaType="long" jdbcType="BIGINT" /><result property="FILE_SUFFIX" column="file_suffix" javaType="string" jdbcType="VARCHAR" /><result property="STATUS" column="status" javaType="int" jdbcType="INTEGER" /><result property="CREATE_DATE" column="create_date" javaType="date" jdbcType="TIMESTAMP" /><result property="ORD" column="ord" javaType="int" jdbcType="INTEGER" /><result property="TYPE" column="type" javaType="int" jdbcType="INTEGER" /></resultMap>

選擇當前日期

YEAR (
from_days(datediff(now(), c.birthday))
) AS age,

遍歷集合

<if test="type ==1">AND type = #{type}AND data_id IN<foreach collection="ids" item="id" open="(" close=")" separator=",">#{id}</foreach>
</if>

查詢條件中有多個or

<if test="unitKind==2">AND (unit_type = 10 OR unit_type=11 OR unit_type = 12 )
</if>

?

寫操作三種語句

<insert id="add" parameterType="com.xgh.sportsite.entity.Activity">INSERT INTO tb_activity(id,unit_id,user_id,activity_name,activity_type,activity_organizer,activity_count,activity_address,sport_id,venue_id,activity_price,activity_introduce,activity_contacts,contacts_phone,activity_path,activity_real_path,declares,is_recommend,is_free,start_date,end_date,enroll_date,create_date,update_date,ord,is_check,status,data_1,data_2,data_3,data_4,data_5,data_6,data_7,data_8,data_9,data_10,data_11)VALUES(#{id},#{unitId},#{userId},#{activityName},#{activityType},#{activityOrganizer},#{activityCount},#{activityAddress},#{sportId},#{venueId},#{activityPrice},#{activityIntroduce},#{activityContacts},#{contactsPhone},#{activityPath},#{activityRealPath},#{declares},#{isRecommend},#{isFree},#{startDate},#{endDate},#{enrollDate},#{createDate},#{updateDate},#{ord},#{isCheck},#{status},#{data1},#{data2},#{data3},#{data4},#{data5},#{data6},#{data7},#{data8},#{data9},#{data10},#{data11})</insert>

<update id="update" parameterType="com.xgh.sportsite.entity.Activity">UPDATE tb_activitySETunit_id = #{unitId},user_id = #{userId},activity_name = #{activityName},activity_type = #{activityType},activity_organizer = #{activityOrganizer},activity_count = #{activityCount},activity_address = #{activityAddress},sport_id = #{sportId},venue_id = #{venueId},activity_price = #{activityPrice},activity_introduce = #{activityIntroduce},activity_contacts = #{activityContacts},contacts_phone = #{contactsPhone},activity_path = #{activityPath},activity_real_path = #{activityRealPath},declares = #{declares},is_recommend = #{isRecommend},is_free = #{isFree},start_date = #{startDate},end_date = #{endDate},enroll_date = #{enrollDate},create_date = #{createDate},update_date = #{updateDate},ord = #{ord},is_check = #{isCheck},status = #{status},data_1 = #{data1},data_2 = #{data2},data_3 = #{data3},data_4 = #{data4},data_5 = #{data5},data_6 = #{data6},data_7 = #{data7},data_8 = #{data8},data_9 = #{data9},data_10 = #{data10},data_11 = #{data11}WHERE id = #{id}</update>

<update id="updateById" parameterType="com.xgh.sportsite.entity.ChildVenueCutting">UPDATE tb_child_venue_cutting<set> <if test="childVenueId != null">child_venue_id = #{childVenueId},</if><if test="orderId != null">order_id = #{orderId},</if><if test="timePeriod != null">time_period = #{timePeriod},</if><if test="status != null">status = #{status},</if><if test="startDate != null">start_date = #{startDate},</if><if test="endDate != null">end_date = #{endDate},</if><if test="createDate != null">create_date = #{createDate},</if><if test="updateDate != null">update_date = #{updateDate},</if><if test="ord != null">ord = #{ord},</if><if test="data1 != null">data_1 = #{data1},</if><if test="data2 != null">data_2 = #{data2},</if><if test="data3 != null">data_3 = #{data3},</if><if test="data4 != null">data_4 = #{data4},</if></set>WHERE id = #{id}</update>

<insert id="add" parameterType="com.xgh.sportsite.entity.ChildVenue"><selectKey keyProperty="id" resultType="long" order="AFTER">SELECT max(id)  AS value  from tb_child_venue</selectKey>INSERT INTO tb_child_venue(id,venue_name,sport_id,venue_no,parent_id,picture_url,pic_real_path,price,sales_price,unit,service_info,tips,ord,facility,is_recommend,capacity,status,create_date,update_date,data_1,data_2,data_3,data_4,data_5,data_6,data_7,data_8)VALUES(#{id},#{venueName},#{sportId},#{venueNo},#{parentId},#{pictureUrl},#{picRealPath},#{price},#{salesPrice},#{unit},#{serviceInfo},#{tips},#{ord},#{facility},#{isRecommend},#{capacity},#{status},#{createDate},#{updateDate},#{data1},#{data2},#{data3},#{data4},#{data5},#{data6},#{data7},#{data8})</insert>

批量更新

    <update id="batchUpdateByIdList" parameterType="java.util.List">UPDATE tb_house set status=0,update_date=now() where id in<foreach collection="list" item="idItem" index="index" open="("separator="," close=")">#{idItem}</foreach></update><update id="batchUpdateList" parameterType="java.util.List"><foreach collection="list" item="item" index="index" open="" close="" separator=";">UPDATE tb_house<set>member_id = #{item.memberId},`type` = #{item.type},data_id = #{item.dataId},create_date = #{item.createDate},update_date = #{item.updateDate},status = #{item.status},data_1 = #{item.data1},data_2 = #{item.data2},data_3 = #{item.data3},data_4 = #{item.data4}</set>where id=#{item.id}</foreach></update><update id="batchUpdateMap" parameterType="java.util.Map">UPDATE tb_house SET type = #{type},status=-1 WHERE id IN<foreach collection="idList" index="index" item="idItem" open="("separator="," close=")">#{idItem}</foreach></update><update id="batchUpdateWithArray" parameterType="java.lang.String">UPDATE tb_house set status=-1 where id in<foreach item="idItem" collection="array" open="(" separator=","close=")">#{idItem}</foreach></update>

批量插入

<insert id="addBatch" parameterType="com.xgh.sportsite.entity.Zone">insert into tb_zone(nid,code,name,pid,pcode,pre_fix,level,is_last,longitude,latitude,location,spell_name,fir_spell_name,ord,create_date,update_date,status,remark)values <foreach collection="list" item="item" separator=",">(#{item.nid,jdbcType=BIGINT},#{item.code,jdbcType=VARCHAR},#{item.name,jdbcType=VARCHAR},#{item.pid,jdbcType=VARCHAR},#{item.pcode,jdbcType=VARCHAR},#{item.preFix,jdbcType=VARCHAR},#{item.level,jdbcType=VARCHAR},#{item.isLast,jdbcType=VARCHAR},#{item.longitude,jdbcType=DOUBLE},#{item.latitude,jdbcType=DOUBLE},#{item.location,jdbcType=VARCHAR},#{item.spellName,jdbcType=VARCHAR},#{item.firSpellName,jdbcType=VARCHAR},#{item.ord,jdbcType=BIGINT},#{item.createDate,jdbcType=TIMESTAMP},#{item.updateDate,jdbcType=TIMESTAMP},#{item.status,jdbcType=INTEGER},#{item.remark,jdbcType=VARCHAR})</foreach></insert>

?