Where 条件
SELECT id,username,age from tablename
<trim prefix="WHERE" prefixOverrides="AND">
<if test="id != null and id != ''">
AND id = #{id}
</if>
<if test="username!= null and username!= ''">
AND username= #{username}
</if>
<if test="age != null">
AND age = #{age}
</if>
</trim>
update
<update id="updateById">
UPDATE tablename
<set>
<if test="username != null">
username = #{username,jdbcType=VARCHAR},
</if>
</set>
WHERE id = #{id,jdbcType=VARCHAR}
</update>
新增(单条)
<insert id="insertTable">
INSERT INTO tablename
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="username != null">
username,
</if>
<if test="createTime != null">
create_time,
</if>
</trim>
<trim prefix="VALUES (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=VARCHAR},
</if>
<if test="username != null">
#{username,jdbcType=VARCHAR},
</if>
<if test="createTime != null">
now(),
</if>
</trim>
</insert>
新增多条(批量新增)
<insert id="insertBatch" parameterType="java.util.List">
INSERT INTO tablename (id, username, create_time)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.id,jdbcType=VARCHAR},
#{item.username,jdbcType=VARCHAR},
now()
)
</foreach>
</insert>
In 条件
SELECT id,username,age from tablename
<trim prefix="WHERE" prefixOverrides="AND">
<if test="idList != null">
AND id IN
<foreach item="list" index="index" collection="idList"
open="(" separator="," close=")"> #{list}
</foreach>
</if>
</trim>
sql片段
<sql id="Base_Column_List">
id, username,age,create_time
</sql>
<!-- 引用 -->
SELECT
<include refid="Base_Column_List" />
FROM tablename
嵌套查询子查询column传多个参数,举个例子:
<!-- 根据商家查询优惠券总量实例 -->
<resultMap id="entity" type="Entity1" >
<result property="businessId" column="BUSI_ID"/>
<result property="businessName" column="BUSI_NAME"/>
<association property="couponCount1"
column="{BRAND_ID=BUSI_ID,VOUCHER_ID=VOUCHER_ID5,OFF_TIME=createTime}"
javaType="int" select="getCountByCondition"/>
</resultMap>
<select id="getList" resultMap="entity">
SELECT
ID AS BUSI_ID,
NAME AS BUSI_NAME,
4 AS VOUCHER_ID4,
5 AS VOUCHER_ID5,
#{lastTime} AS lastTime,
#{createTime} AS createTime
FROM
T_ENTITY
WHERE
IS_DELETE = 0
<if test="businessId != null and businessId != ''">
AND ID = #{businessId}
</if>
</select>
<select id="getCountByCondition" resultType="int">
SELECT
COUNT(1)
FROM
T_COUPON
<trim prefix="WHERE" prefixOverrides="AND">
<if test="CREATE_TIME != null and CREATE_TIME != ''">
AND TO_DAYS(CREATE_TIME) = TO_DAYS(#{CREATE_TIME})
</if>
<if test="VOUCHER_ID != null and VOUCHER_ID != ''">
AND VOUCHER_ID = #{VOUCHER_ID}
</if>
<if test="BRAND_ID != null and BRAND_ID != ''">
AND BRAND_ID = #{BRAND_ID}
</if>
<if test="OFF_TIME != null">
AND TO_DAYS(CREATE_TIME) <![CDATA[<=]]> TO_DAYS(#{OFF_TIME})
</if>
AND IS_DELETE = 0
</trim>
</select>
一对多查询
<resultMap id="entity2" type="Entity2" >
<id property="id" column="id" />
<id property="username" column="username" />
<collection property="carPhotos" ofType="CarPhoto" column="id"
select="mapper.standard.CarPhotoMapper.getCarPhotoCollectionByEntityId">
</collection>
</resultMap>
有待更新哦