6.动态sql语句

1.创建一个查询对象

package com.gzcgxt.oa.query;

import lombok.Data;

@Data
public class UserQuery {
    //主键
    private Integer id;
    //名字
    private String name;
    //最小年龄
    private Integer beginAge;
    //最大年龄
    private Integer endAge;
    //关键词
    private String keyword;
}

2.映射文件userMapper.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.gzcgxt.oa.mapper.UserMapper">
    
    <resultMap id="UserMap" type="User">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
    </resultMap>
    
    <select id="query" resultMap="UserMap">
        select * from user 
        <where>
            <if test="id!=null">
                and id=#{id}
            </if>
            <if test="beginAge!=null">
                and age &gt;=#{beginAge}
            </if>
            <if test="endAge!=null">
                and age &lt;=#{endAge}
            </if>
            <if test="name!=null">
                and name like concat('%',#{name},'%')
            </if>
            <if test="keyword!=null">
                and name like '%${keyword}%'
            </if>
        </where>
    </select>
    
</mapper>

3.测试<where>代码

package com.gzcgxt.oa.test;

import java.io.InputStream;
import java.util.Arrays;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import com.gzcgxt.oa.domain.User;
import com.gzcgxt.oa.mapper.UserMapper;
import com.gzcgxt.oa.query.UserQuery;

public class App {
    @Test
    public void sayInterface() throws Exception
    {
        InputStream in = Resources.getResourceAsStream("mybatis.xml");
        // 构造mybatis工厂对像
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        // 获取连接
        SqlSession session = factory.openSession();
        //获取UserMapper接口对象
        UserMapper userMapper = session.getMapper(UserMapper.class);
        
        UserQuery q=new UserQuery();
        q.setKeyword("l");
        List<User> list = userMapper.query(q);
        System.out.println(Arrays.asList(list));
    }
}

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

DEBUG [main] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@8a7b551]
DEBUG [main] - ==> Preparing: select * from user WHERE name like '%l%'
DEBUG [main] - ==> Parameters:
TRACE [main] - <== Columns: id, name, age
TRACE [main] - <== Row: 3, Lucy, 44
TRACE [main] - <== Row: 5, Ella, 18
TRACE [main] - <== Row: 8, LILI, 44
[[User(id=3, name=Lucy, age=44), User(id=5, name=Ella, age=18), User(id=8, name=LILI, age=44)]]

 

#这里使用in 查询　参数parameterType要注明是list集合　　java.util.List也可以

<select id="findUserById" resultMap="UserMap" parameterType="list">
        select * from user
        <where>
            <if test="list!=null and list.size >0">
                <foreach collection="list" item="id" open="and id in(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

package com.gzcgxt.oa.mapper;

import java.sql.Savepoint;
import java.util.List;

import com.gzcgxt.oa.domain.User;
import com.gzcgxt.oa.query.UserQuery;

public interface UserMapper {
    
    /**
     * 查询User对象
     * <p>Title: query</p>
     * <p>Description: </p>
     * @param q
     * @return
     */
    List<User> query(UserQuery q);
    
    /**
     * 按id查询
     * <p>Title: query</p>
     * <p>Description: </p>
     * @param q
     * @return
     */
    List<User> findUserById(List<Integer> ids);
}

    @Test
    public void sayId() throws Exception
    {
        InputStream in = Resources.getResourceAsStream("mybatis.xml");
        // 构造mybatis工厂对像
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        // 获取连接
        SqlSession session = factory.openSession();
        //获取UserMapper接口对象
        UserMapper userMapper = session.getMapper(UserMapper.class);
        
        List<Integer> ids=new ArrayList<>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        
        List<User> list = userMapper.findUserById(ids);
        System.out.println(Arrays.asList(list));
    }