实体Person.java
package com.mouday.pojo; import lombok.Getter; import lombok.Setter; @Setter @Getter public class Person { private Integer id; private String name; private Integer age; }
mapper接口 PersonMapper.java
package com.mouday.mapper; import com.mouday.pojo.Person; import java.util.List; public interface PersonMapper { List<Person> selectAll(); /** * 根据输入的信息进行条件检索 * 1. 当只输入用户名时， 使用用户名进行 【模糊检索】 * 2. 当只输入年龄时， 使用性别进行 【完全匹配】 * 3. 当用户名和年龄都存在时， 用这两个条件进行查询匹配的用 */ List<Person> selectByPersonSelective(Person person); /** * 更新非空属性 */ int updateByPrimaryKeySelective(Person person); /** * 插入非空字段 */ int insertSelective(Person person); /** * 当 name 没有值时， 使用 name 进行查询 * 否则使用 id 进行查询 */ List<Person> selectByNameOrId(Person person); }
mapper映射文件 PersonMapper.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="com.mouday.mapper.PersonMapper"> <sql id="Base_Column_List"> id, name, age </sql> <select id="selectAll" resultType="Person"> select <include refid="Base_Column_List"/> from person </select> <select id="selectByPersonSelective" resultType="Person" parameterType="Person"> select <include refid="Base_Column_List" /> from person <where> <if test="name != null and name !=''"> and name like concat('%', #{name}, '%') </if> <if test="age != null"> and age=#{age} </if> </where> </select> <update id="updateByPrimaryKeySelective" parameterType="Person"> update person <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> <if test="age != null"> `age` = #{age,jdbcType=INTEGER}, </if> </set> where id = #{id,jdbcType=INTEGER} </update> <insert id="insertSelective" parameterType="Person"> insert into person <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null"> id, </if> <if test="name != null"> `name`, </if> <if test="age != null"> age, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null"> #{id,jdbcType=INTEGER}, </if> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> <if test="age != null"> #{age,jdbcType=INTEGER}, </if> </trim> </insert> <select id="selectByNameOrId" resultType="Person" parameterType="Person"> select <include refid="Base_Column_List" /> from person where 1=1 <choose> <when test="id != null"> and id=#{id} </when> <otherwise> and name=#{name} </otherwise> </choose> </select> </mapper>
mapper映射文件 PersonMapper.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="com.mouday.mapper.PersonMapper"> <sql id="Base_Column_List"> id, name, age </sql> <select id="selectAll" resultType="Person"> select <include refid="Base_Column_List"/> from person </select> <select id="selectByPersonSelective" resultType="Person" parameterType="Person"> select <include refid="Base_Column_List" /> from person <where> <if test="name != null and name !=''"> and name like concat('%', #{name}, '%') </if> <if test="age != null"> and age=#{age} </if> </where> </select> <update id="updateByPrimaryKeySelective" parameterType="Person"> update person <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> <if test="age != null"> `age` = #{age,jdbcType=INTEGER}, </if> </set> where id = #{id,jdbcType=INTEGER} </update> <insert id="insertSelective" parameterType="Person"> insert into person <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null"> id, </if> <if test="name != null"> `name`, </if> <if test="age != null"> age, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null"> #{id,jdbcType=INTEGER}, </if> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> <if test="age != null"> #{age,jdbcType=INTEGER}, </if> </trim> </insert> <select id="selectByNameOrId" resultType="Person" parameterType="Person"> select <include refid="Base_Column_List" /> from person where 1=1 <choose> <when test="id != null"> and id=#{id} </when> <otherwise> and name=#{name} </otherwise> </choose> </select> </mapper>
测试文件PersonTest.java
package com.mouday; import com.mouday.mapper.PersonMapper; import com.mouday.pojo.Person; import com.mouday.util.MyBatisUtil; import org.apache.ibatis.session.SqlSession; import org.junit.After; import org.junit.Before; import org.junit.Test; import java.io.IOException; public class PersonTest { private SqlSession session; private PersonMapper mapper; @Before public void init() throws IOException { this.session = MyBatisUtil.getSqlSession(); this.mapper = this.session.getMapper(PersonMapper.class); } @After public void destroy() { this.session.close(); } @Test public void testSelect() { System.out.println(mapper.selectAll()); } /** * 选择数据 */ @Test public void testSelectByStudentSelective() { Person person = new Person(); System.out.println(mapper.selectByPersonSelective(person)); // select id, name, age from person person.setName("操"); System.out.println(mapper.selectByPersonSelective(person)); // select id, name, age from person WHERE name like concat('%', ?, '%') person.setAge(25); System.out.println(mapper.selectByPersonSelective(person)); // select id, name, age from person WHERE name like concat('%', ?, '%') and age=? } /** * 更新数据 */ @Test public void testUpdateByPrimaryKeySelective() { Person person = new Person(); person.setId(1); person.setAge(26); mapper.updateByPrimaryKeySelective(person); // update person SET `age` = ? where id = ? session.commit(); person.setName("刘禅"); mapper.updateByPrimaryKeySelective(person); session.commit(); // update person SET `name` = ?, `age` = ? where id = ? } /** * 插入数据 */ @Test public void testInsertSelective() { Person person = new Person(); person.setName("司马懿"); mapper.insertSelective(person); // insert into person ( `name` ) values ( ? ) session.commit(); person.setAge(26); mapper.insertSelective(person); // insert into person ( `name`, age ) values ( ?, ? ) session.commit(); } /** * 选择查询 */ @Test public void testSelectByNameOrId() { Person person = new Person(); person.setName("司马懿"); mapper.selectByNameOrId(person); // select id, name, age from person where 1=1 and name=? person.setId(1); mapper.selectByNameOrId(person); // select id, name, age from person where 1=1 and id=? } }
Where
set 和 where 其实都是 trim 标签的一种类型
where 等价于
Where set 和 where 其实都是 trim 标签的一种类型 where 等价于
表示当 trim 中含有内容时， 添加 where， 且第一个为 and 或 or 时， 会将其去掉。
而如果没有内容， 则不添加 where。
set 等价于
<trim prefix="SET" suffixOverrides=",">
...
</trim>
表示当 trim 中含有内容时， 添加 set， 且最后的内容为 , 时， 会将其去掉。而没有内容， 不添加 set
trim 的几个属性
prefix: 当 trim 元素包含有内容时， 增加 prefix 所指定的前缀
prefixOverrides: 当 trim 元素包含有内容时， 去除 prefixOverrides 指定的 前缀
suffix: 当 trim 元素包含有内容时， 增加 suffix 所指定的后缀
suffixOverrides：当 trim 元素包含有内容时， 去除 suffixOverrides 指定的后缀
参考
