MyBatis动态SQL
使用动态SQL可简化代码开发,减少开发者工作量,程序可以自动根据业务参数来决定SQL的组成。
- ⭐if标签
<select id="findByAccount" parameterType="com.oyrf.frist.Account" resultType="com.oyrf.frist.Account">
select * from t_account where
<if test="id!=0">id = #{id} </if>
<if test="username!=null">and username = #{username} </if>
<if test="password!=null">and password = #{password }</if>
<if test="age!=0"> and age = #{age}</if>
</select>
if标签可以自动根据表达式的结果来决定是否将对应的语句添加到SQL中,如果条件不成立则不添加,如果条件成立则添加。
- ⭐where标签
<select id="findByAccount" parameterType="com.oyrf.frist.Account" resultType="com.oyrf.frist.Account">
select * from t_account
<where>
<if test="id!=0">id = #{id} </if>
<if test="username!=null">and username = #{username} </if>
<if test="password!=null">and password = #{password }</if>
<if test="age!=0"> and age = #{age}</if>
</where>
</select>
where标签可以自动判断是否要删除语句块中的and关键字,如果检测到where直接跟and拼接,则自动删除and,通常情况下if和where结合起来使用。
- ⭐choose、when标签
<select id="findByAccount" parameterType="com.oyrf.frist.Account" resultType="com.oyrf.frist.Account">
select * from t_account
<where>
<choose>
<when test="id!=0">id=#{id}</when>
<when test="username!=null">and username=#{username</when>
<when test="password!=null">andpassword=#{password</when>
<when test="age!=0">and age=#{age}</when>
</choose>
</where>
</select>
- ⭐trim标签
trim标签中的prefix和suffix属性会被用于生成实际的SQL语句,会和标签内部的语句进行拼接,如果语句前后出现了prefixOverrides或者suffixOverrides属性中指定的值,MyBatis框架自动将其删除。
<select id="findByAccount" parameterType="com.oyrf.frist.Account" resultType="com.oyrf.frist.Account">
select * from t_account
<trim prefix="where" prefixOverrides="and">
<if test="id!=0">id=#{id}</if>
<if test="username!=null">and username=#{username}</if>
<if test="password!=null">and password=#{password}</if>
<if test="age!=null">and age=#{age}</if>
</trim>
</select>
- ⭐set标签
set标签用于update操作,会自动根据参数选择生成SQL语句。
<update id="update" parameterType="com.oyrf.frist.Account">
update t_account
<set>
<if test="username!=null">username=#{username}</if>
<if test="password!=null">password=#{password}</if>
<if test="age!=0">age=#{age}</if>
where id=#{id}
</set>
</update>
- ⭐foreach标签
foreach标签可以迭代生成一系列的值,这个标签主要用于SQL的in语句。
<select id="findByIds" parameterType="com.oyrf.frist.Account" resultType="com.oyrf.frist.Account">
select * from t_account
<where>
<foreach collection="ids" open="id in(" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
Account.java
private List<Long> ids;
AccountRepository.java
public List<Account> findByIds(Account account);
Test5.java
InputStream inputStream = Test5.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
Account account = new Account();
List<Long> ids =new ArrayList<Long>();
ids.add(2L);
ids.add(3L);
ids.add(5L);
account.setIds(ids);
AccountRepository accountRepository = sqlSession.getMapper(AccountRepository.class);
System.out.println( accountRepository.findByIds(account));
sqlSession.commit();
sqlSession.close();