表采用 MySql中group_concat的使用 的staff表
<?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" >
<resultMap id="baseResultMap" type="Staff"> <id column="id" property="id"> <result column="name" property="name"> <result column="salary" property="salary"> <result column="depart" property="depart"> #关联查询，这里切记resultMap必须要有一个唯一确定记录的id，否则如果返回列表，只会返回一条# <association property="family" javaType="com.*.Family"> <id column="family_id" property="familyId"/> <result column="phone" property="phone"/> </association>
</resultMap>
<sql id="baseColumnList"> if, name,salary,depart</sql>
<insert id="add" parameterType="Staff" useGeneratedKeys="true" keyColumn="id" keyProperty="id"> insert into staff (name,salary,depart) values(#{name}, #{salary}, #{depart})</insert>
<select id="getById" resultMap="baseResultMap" parameterType="Integer"> select <include refid="baseColumnList"/> from staff where id=#{id}</select>
<!-- 批量查询 foreach遍历--><select id="getByIds" resultMap="baseResultMap" parameterType="Integer"> select <include refid="baseColumnList" /> from staff where id in <foreach collection="list" item="item" index="index" open="(" separator="," close=")"> #{item} </foreach></select>
<！-- bind、trim、if的使用--><!-- bind元素可让你创建一个变量出OGNL表达式的值，并将其绑定到上下文 --><select id="selectStaffs" resultMap="baseResultMap" parameterType="queryPO"> <bind value="'%' + queryInfo+ '%'" name = "_queryInfo"> select <include refid="baseColumnList" /> from staff <where> <trim prefix="(" prefixOverrides="OR" suffix=")"> <if test="queryInfo != null"> name like #{_queryInfo} or depart like #{_queryInfo} </if> </trim> <if test="staff != null"> and staff = #{staff} </if> </where> <choose> <when test="sortRule != null"> order by #{sortRule} </when> <otherwise> order by salary desc </otherwise> </choose></select>
<！-- exists,not exists的使用-->
<select id="selectNameSameStaffs" resultMap="baseResultMap" parameterType="String"> select <include refid="baseColumnList"/> from staff where exists (select 1 from table B where B.name = staff.name)</select>
<select id="selectNameNotSameStaffs" resultMap="baseResultMap" parameterType="String"> select <include refid="baseColumnList"/> from staff where not exists (select 1 from table B where B.name = staff.name)</select>
</mapper>
转载于:https://www.cnblogs.com/mucheng/p/5999611.html
