1.双表查询
<select id="findAll" resultType="plan"> select * from plan join dept on plan.dept_id=dept.id <where> <if test="null!=name and name!=''"> and plan.name like concat('%',#{name},'%') </if> </where> </select> <resultMap type="Plan" id="plan"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="amount" property="amount"/> <result column="manager" property="manager"/> <result column="content" property="content"/> <association property="dept" javaType="Dept"> <id column="id" property="id"/> <result column="name" property="name"/> </association> </resultMap>
2.三表查询
<resultMap type="com.liujin.cms.domain.Students" id="students"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="date" property="date"/> <collection property="types" ofType="com.liujin.cms.domain.Types"> <id column="x_id" property="x_id"/> <result column="type" property="type"/> </collection> </resultMap> <select id="findAll" resultMap="students"> select s.id id,s.name name,s.date date,t.x_id x_id,t.type type from students s join s_t on s.id=s_t.id join types t on s_t.x_id=t.x_id <where> <if test="null!=name and name!=''"> and name like concat('%',#{name},'%') </if> <if test="null!=x_id and x_id"> and x_id=#{x_id} </if> </where> </select>
大于小于号
<if test="null!=date1 and date1!=''"> and date >=#{date1} </if> <if test="null!=date2 and date2!=''"> and date <=#{date2} </if>