MyBatis 动态数据库查询 数据库查询语句XML

动态数据库查询

<?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="CustomerMapper">
	
	<resultMap id="BaseResultMap"
		type="com.hlq.ch8.dao.Customer">
		<id column="id" jdbcType="INTEGER" property="id" />
		<result column="username" jdbcType="VARCHAR"
			property="username" />
		<result column="jobs" jdbcType="VARCHAR" property="jobs" />
		<result column="phone" jdbcType="VARCHAR" property="phone" />
	</resultMap>
	
	<sql id="Base_Column_List">
		id, username, jobs, phone
	</sql>
	
	<!-- where标签的使用 -->
	<select id="findCustomersByNameAndJobs" parameterType="com.hlq.ch8.dao.Customer" resultMap="BaseResultMap">
		SELECT * FROM customer
		<where>
			<if test="username!=null and username!=''">
				and username like concat('%', #{username}, '%')
			</if>
			<if test="jobs!=null and jobs!=''">
				and jobs = #{jobs}
			</if>
		</where>
	</select>
	
	<!-- choose标签的使用 -->
	<select id="findCustomersByNameOrJobs" parameterType="com.hlq.ch8.dao.Customer" resultMap="BaseResultMap">
		SELECT * FROM customer
		<where>
			<choose>
				<when test="username!=null and username!=''">
					and username like concat('%', #{username}, '%')
				</when>
				<when test="jobs!=null and jobs!=''">
					and jobs = #{jobs}
				</when>
				<otherwise>
					and phone IS NOT NULL
				</otherwise>
			</choose>
		</where>
	</select>
	
	<!-- foreach的使用 -->
	<select id="findCustomersByIds" parameterType="Integer" resultMap="BaseResultMap">
		SELECT * FROM customer
		WHERE id in 
		<!-- collection是必须的，使用open为开始，separator为分隔符， close为技术 -->
		<foreach item="id" index="index" collection="list" open="(" separator="," close=")">
			#{id}
		</foreach>
	</select>
	
	<!-- bind标签的使用 -->
	<select id="findCustomerByName" parameterType="String" resultMap="BaseResultMap">
		<!-- 定义一个绑定, 并定义名字 -->
		<bind name="pattern_username" value="'%'+username+'%'"></bind>
		SELECT * FROM customer WHERE username LIKE #{pattern_username}
	</select>
	
	
	
	<!-- 动态插入 -->
	<insert id="insertSelective" parameterType="com.hlq.ch8.dao.Customer">
		insert into customer
		<!-- prefix前缀  suffix后缀 suffixOverrides后缀重写 -->
		<trim prefix="(" suffix=")" suffixOverrides=",">
		<!-- 不为空则添加 -->
			<if test="id != null">
				id,
			</if>
			<if test="username != null">
				username,
			</if>
			<if test="jobs != null">
				jobs,
			</if>
			<if test="phone != null">
				phone,
			</if>
		</trim>
		<trim prefix="values (" suffix=")" suffixOverrides=",">
			<if test="id != null">
				#{id,jdbcType=INTEGER},
			</if>
			<if test="username != null">
				#{username,jdbcType=VARCHAR},
			</if>
			<if test="jobs != null">
				#{jobs,jdbcType=VARCHAR},
			</if>
			<if test="phone != null">
				#{phone,jdbcType=VARCHAR},
			</if>
		</trim>
	</insert>
	
	<!-- 动态根据字段进行更新 -->
	<update id="updateByPrimaryKeySelective" parameterType="com.hlq.ch8.dao.Customer">
		update customer
		<!-- 根据出现的字段进行更新 -->
		<set>
			<if test="username != null">
				username=#{username},
			</if>
			<if test="jobs!=null">
				jobs = #{jobs}
			</if>
			<if test="phone!=null">
				phone = #{phone}
			</if>
		</set>
		WHERE id = #{id}
	</update>
	
</mapper>