Mybatis操作mysql数据库（条件查询，表连接）

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>

有待更新哦

转载于:https://my.oschina.net/u/3902753/blog/1929173