Mybatis的动态sql

发表于 | 分类于 ，
字数统计: 1.4k 字 | 阅读时长 ≈ 8 分钟

本文是关于mybatis的早期使用方法。

0.前期准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE tb_employee
(
  id        INT AUTO_INCREMENT
    PRIMARY KEY,
  loginname VARCHAR(18) NULL,
  password  VARCHAR(18) NULL,
  name      VARCHAR(18) NULL,
  sex       CHAR(2)     NULL,
  age       INT         NULL,
  phone     VARCHAR(21) NULL,
  sal       DOUBLE      NULL,
  state     VARCHAR(18) NULL,
  CONSTRAINT tb_employee_id_uindex
  UNIQUE (id)
)

POJO类：

1
2
3
4
5
6
7
8
9
10
11
12
public class TbEmployee {

  private long id;
  private String loginname;
  private String password;
  private String name;
  private String sex;
  private long age;
  private String phone;
  private double sal;
  private String state;
//getter,setter

1.xml配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
<?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="org.fkit.mapper.EmployeeMapper">
    <select id="selectEmployeeByIdLike" resultType="org.fkit.domain.TbEmployee">
        SELECT * FROM tb_employee
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="sex != null">
                and sex = #{sex}
            </if>
        </where>
    </select>
    <select id="selectEmployeeChoose" resultType="org.fkit.domain.TbEmployee">
        select * from tb_employee
        <where>
            <choose>
                <when test="id != null">
                    and id = #{id}
                </when>
                <when test="loginname != null and password != null">
                    and loginname = #{loginname} and password = #{password}
                </when>
                <otherwise>
                    and sex = '1'
                </otherwise>
            </choose>
        </where>
    </select>
    <select id="selectEmployeeWithId" parameterType="int" resultType="org.fkit.domain.TbEmployee">
        SELECT * FROM tb_employee where id = #{id}
    </select>
    <update id="updateEmployeeIfNecessary" parameterType="org.fkit.domain.TbEmployee">
        UPDATE tb_employee
        <set>
            <if test="loginname != null">loginname = #{loginname},</if>
            <if test="password != null">password = #{password},</if>
            <if test="name != null">name = #{name},</if>
            <if test="sex != null">sex = #{sex},</if>
            <if test="age != null">age = #{age},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="sal != null">sal = #{sal},</if>
            <if test="state != null">state = #{state},</if>
        </set>
        where id = #{id}
    </update>
    <select id="selectEmployeeIn" resultType="org.fkit.domain.TbEmployee">
        select * from tb_employee where id in
        <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="selectEmployeeLikeName" resultType="org.fkit.domain.TbEmployee">
        <bind name="pattern" value="'%' + _parameter.getName() + '%'"/>
        <bind name="pattern2" value="'%' + _parameter.getLoginname() +'%'  "/>
            SELECT * FROM tb_employee where name like #{pattern} and loginname like #{pattern2}
    </select>
</mapper>

2.测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
public class DynamicSQLTest {
    public static void main(String args[]) {
        SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
        DynamicSQLTest dynamicSQLTest = new DynamicSQLTest();
        dynamicSQLTest.testSelectEmployeeByIdLike(sqlSession);
        dynamicSQLTest.testSelectEmployeeChoose(sqlSession);
        dynamicSQLTest.testUpdateEmployeeIfNecessary(sqlSession);
        dynamicSQLTest.testSelectEmployeeIn(sqlSession);
        dynamicSQLTest.testSelectEmployeeLikeName(sqlSession);
        sqlSession.commit();

        sqlSession.close();
    }

    public void testSelectEmployeeByIdLike(SqlSession sqlSession) {
        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
        HashMap<String, Object> params = new HashMap<String, Object>();
        params.put("id", 1);
        params.put("sex", 1);
        List<TbEmployee> tbEmployees = employeeMapper.selectEmployeeByIdLike(params);
        tbEmployees.forEach(tbEmployee -> System.out.println(tbEmployee));
    }
    public void testSelectEmployeeChoose(SqlSession sqlSession) {
        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
        HashMap<String, Object> params = new HashMap<String, Object>();
        params.put("loginname", "jack");
        params.put("password", "123");
        params.put("id", 2);
        List<TbEmployee> tbEmployees = employeeMapper.selectEmployeeChoose(params);
        tbEmployees.forEach(tbEmployee -> System.out.println(tbEmployee));
    }

    public void testUpdateEmployeeIfNecessary(SqlSession sqlSession) {
        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
        TbEmployee tbEmployee = employeeMapper.selectEmployeeWithId(1);
        System.out.println(tbEmployee);
        tbEmployee.setLoginname("newJack");
        employeeMapper.updateEmployeeIfNecessary(tbEmployee);

    }

    public void testSelectEmployeeIn(SqlSession sqlSession) {
        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
        List<Integer> integers = new ArrayList<Integer>();
        integers.add(1);
        integers.add(3);
        List<TbEmployee> tbEmployees = employeeMapper.selectEmployeeIn(integers);
        tbEmployees.forEach(tbEmployee -> System.out.println(tbEmployee));

    }

    public void testSelectEmployeeLikeName(SqlSession sqlSession) {
        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
        TbEmployee tbEmployee = new TbEmployee();
        tbEmployee.setName("a");
        tbEmployee.setLoginname("J");
        List<TbEmployee> tbEmployees = employeeMapper.selectEmployeeLikeName(tbEmployee);
        tbEmployees.forEach(tbEmployee1 -> System.out.println(tbEmployee1));
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
//output:
DEBUG [main] - ==>  Preparing: SELECT * FROM tb_employee WHERE id = ? and sex = ? 
DEBUG [main] - ==> Parameters: 1(Integer), 1(Integer)
DEBUG [main] - <==      Total: 1
TbEmployee{id=1, loginname='newJack', password='123', name='jaccck', sex='1', age=26, phone='123456', sal=3211.0, state='active'}
DEBUG [main] - ==>  Preparing: select * from tb_employee WHERE id = ? 
DEBUG [main] - ==> Parameters: 2(Integer)
DEBUG [main] - <==      Total: 1
TbEmployee{id=2, loginname='rose', password='456', name='rooose', sex='2', age=21, phone='654321', sal=1121.0, state='active'}
DEBUG [main] - ==>  Preparing: SELECT * FROM tb_employee where id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
TbEmployee{id=1, loginname='newJack', password='123', name='jaccck', sex='1', age=26, phone='123456', sal=3211.0, state='active'}
DEBUG [main] - ==>  Preparing: UPDATE tb_employee SET loginname = ?, password = ?, name = ?, sex = ?, age = ?, phone = ?, sal = ?, state = ? where id = ? 
DEBUG [main] - ==> Parameters: newJack(String), 123(String), jaccck(String), 1(String), 26(Long), 123456(String), 3211.0(Double), active(String), 1(Long)
DEBUG [main] - <==    Updates: 1
DEBUG [main] - ==>  Preparing: select * from tb_employee where id in ( ? , ? ) 
DEBUG [main] - ==> Parameters: 1(Integer), 3(Integer)
DEBUG [main] - <==      Total: 2
TbEmployee{id=1, loginname='newJack', password='123', name='jaccck', sex='1', age=26, phone='123456', sal=3211.0, state='active'}
TbEmployee{id=3, loginname='tom', password='tom', name='tooom', sex='1', age=12, phone='132', sal=32.2, state='active'}
DEBUG [main] - ==>  Preparing: SELECT * FROM tb_employee where name like ? and loginname like ? 
DEBUG [main] - ==> Parameters: %a%(String), %J%(String)
DEBUG [main] - <==      Total: 1
TbEmployee{id=1, loginname='newJack', password='123', name='jaccck', sex='1', age=26, phone='123456', sal=3211.0, state='active'}

使用like的另一种写法：

1
2
3
4
5
6
7
8
9
10
11
12
<select id="getUsersByConditionIf" resultType="com.model.User" parameterType="com.model.User">
    select * from user where
    <if test="id!=null">
        id = #{id}
    </if>
    <if test="username!=null and username !=''">
        or username like "%"#{username}"%"
    </if>
    <if test="sex != null">
        and sex = #{sex}
    </if>
</select>

使用”%”#{parameter}”%”，运行结果如下：

1
2
3
4
5
6
7
8
9
10
11
DEBUG [main] - ==>  Preparing: select id, username,birthday, sex, address,dept_id from user where id = ? 
DEBUG [main] - ==> Parameters: 27(Integer)
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: select id, name from department where id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
User{id=27, username='tes111t', birthday=Thu Jul 12 08:00:00 CST 2018, sex='1', address='1'}
DEBUG [main] - ==>  Preparing: select * from user where id = ? or username like "%"?"%" and sex = ? 
DEBUG [main] - ==> Parameters: 27(Integer), 1(String), 1(String)
DEBUG [main] - <==      Total: 2
[User{id=27, username='tes111t', birthday=Thu Jul 12 08:00:00 CST 2018, sex='1', address='1'}, User{id=32, username='11231', birthday=Thu Jul 12 08:00:00 CST 2018, sex='1', address='1'}]

要使每个都写为and xxx= #{xx}的形式，而不是第一个需要去掉and，可以在sql前面的语句中写为

1
select * from xxx where 1=1

这样保证每个都可以写为and xxx = #{xx}的形式。

也可以使用where标签将所有拼接的动态条件放入。

但是where只能去掉第一个and。

一次更新多条数据：

1
void addUsers(@Param("userList") List<User> userList);

1
2
3
4
5
6
<insert id="addUsers" >
    insert into user(username,address) VALUES
    <foreach collection="userList" item="user" separator=",">
        (#{user.username},#{user.address})
    </foreach>
</insert>

需要插入别的字段需要自己加入。

-------------本文结束 感谢您的阅读-------------