MyBatis中的动态SQL

注意：这里是在《一对一关系实现员工—＞部门的查询》的基础进行编写代码
《一对一关系实现员工—＞部门的查询》

参考代码：

EmpMapper:

package com.yzh7.mapper;

import com.yzh7.entity.Emp;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface EmpMapper {
    //查询所有员工，并且每个员工关联一个部门对象
    List listAll();
    //条件查询1
    List list1(@Param("empName") String name,
                    @Param("phone") String phone);
    //条件查询2
    List list2(Map map);

    //条件查询3
    List list3(Map map);

    //条件查询4
    List list4(Map map);

    //根据编号查询数据
    List listByIds(List ids);

    //分支条件查询
    List listByChoose(Emp emp);

    //针对所有字段进行修改
    int update1(Emp emp);
    //根据传入的数据是否为空，不为空的才修改
    int update2(Emp emp);

    //批量插入
    int insertBatch(List empList);
    //插入并接收返回的自增值
    int insertAndGetAutoVal(Emp emp);
}

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

EmpMapper.xml:

    
    
        emp_id,emp_name,sex,phone
    


    
        select
        
        from t_emp
    

    
        
        
        
    

    
        select e.emp_id ,e.emp_name ,e.sex,e.phone,
               d.dept_id,d.dept_name,d.description
        from t_emp e
        join t_dept d
        on e.dept_id = d.dept_id
    
    
        
        
            
                and emp_name like concat('%',#{empName},'%')
            
        
    

    
    
        insert into t_emp
        (emp_name,sex,phone)
        values
        (#{empName},#{sex},#{phone})
    

    
    
        select * from t_emp
        
            
                
                    and emp_name = #{empName}
                
                
                    and sex = #{sex}
                
                
                    and emp_id=1
                
            
        
    

    
    
        insert into t_emp
            (emp_name,sex,phone)
        VALUES
        
            (#{e.empName},#{e.sex},#{e.phone})
        
    

    
    
        select * from t_emp
        where emp_id in
        
        
            #{data}
        
    

    
        update t_emp
        
        
            
                emp_name = #{empName},
            
            
                sex = #{sex},
            
            
                phone = #{phone},
            
            
                dept_id = #{dept.deptId},
            
        
        where emp_id = #{empId}
    


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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122

demo6:

package com.yzh7.test;

import com.yzh7.entity.Dept;
import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Demo6 {


    //条件查询
    @Test
    public void test(){
        EmpMapper empMapper = session.getMapper(EmpMapper.class);
        //构建要修改的员工对象
        Emp emp = new Emp();
        emp.setEmpId(1);
        emp.setEmpName("张三丰");

        Dept dept = new Dept();
        dept.setDeptId(2);
        emp.setDept(dept);

        int count = empMapper.update1(emp);
        System.out.println("修改的记录数:"+count);

    }

    @Test
    public void test2(){
        EmpMapper empMapper = session.getMapper(EmpMapper.class);
        //构建要修改的员工对象
        Emp emp = new Emp();
        emp.setEmpId(2);
        emp.setEmpName("李四西");

        Dept dept = new Dept();
        dept.setDeptId(3);
        emp.setDept(dept);
        //根据数据本身是否为空，进行修改
        int count = empMapper.update2(emp);
        System.out.println("修改的记录数:"+count);

    }




    //会话工厂
    private static SqlSessionFactory factory;
    //会话
    private SqlSession session;

    @BeforeClass
    public static void befCls() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        factory = new SqlSessionFactoryBuilder().build(is);
    }

    @Before
    public void bf(){
        session = factory.openSession();
    }

    @After
    public void af(){
        //提交事务
        session.commit();
        //关闭会话
        session.close();
    }
}

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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

demo7:

package com.yzh7.test;

import com.yzh7.entity.Dept;
import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class Demo7 {
	

    //条件查询
    @Test
    public void test(){
        EmpMapper empMapper = session.getMapper(EmpMapper.class);
        List ids = new ArrayList<>();
        ids.add(1);
        ids.add(3);
        ids.add(4);

        List empList =  empMapper.listByIds(ids);
        System.out.println(empList);

    }

    @Test
    public void test2(){
        EmpMapper empMapper = session.getMapper(EmpMapper.class);
        Emp e1 = new Emp();
        e1.setEmpName("aa1");
        e1.setSex("男");
        e1.setPhone("1212");

        Emp e2 = new Emp();
        e2.setEmpName("aa2");
        e2.setSex("男");
        e2.setPhone("1212");

        Emp e3 = new Emp();
        e3.setEmpName("aa2");
        e3.setSex("男");
        e3.setPhone("1212");

        List empList = new ArrayList<>();
        empList.add(e1);
        empList.add(e2);
        empList.add(e3);

        int count = empMapper.insertBatch(empList);
        System.out.println("记录数:"+count);

    }

    //条件查询
    @Test
    public void test3(){
        Emp emp = new Emp();
        //emp.setEmpName("张三丰");
        //emp.setSex("男");

        EmpMapper empMapper = session.getMapper(EmpMapper.class);
        List empList =  empMapper.listByChoose(emp);
        System.out.println(empList);

    }

    //条件查询
    @Test
    public void test4(){
        Emp emp = new Emp();
        emp.setEmpName("武松");
        emp.setSex("男");
        emp.setPhone("12312");

        EmpMapper empMapper = session.getMapper(EmpMapper.class);
        int count = empMapper.insertAndGetAutoVal(emp);
        System.out.println("插入的记录数:"+count);
        System.out.println("插入之后，返回的自增值:"+emp);

    }


    //会话工厂
    private static SqlSessionFactory factory;
    //会话
    private SqlSession session;

    @BeforeClass
    public static void befCls() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        factory = new SqlSessionFactoryBuilder().build(is);
    }

    @Before
    public void bf(){
        session = factory.openSession();
    }

    @After
    public void af(){
        //提交事务
        session.commit();
        //关闭会话
        session.close();
    }
}

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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118

demo8:

package com.yzh7.test;

import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Demo8 {


    //条件查询
    @Test
    public void test(){
        EmpMapper empMapper = session.getMapper(EmpMapper.class);
        Map map = new HashMap();
        map.put("empName","李");
        List empList =  empMapper.list4(map);
        System.out.println(empList);
    }





    //会话工厂
    private static SqlSessionFactory factory;
    //会话
    private SqlSession session;

    @BeforeClass
    public static void befCls() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        factory = new SqlSessionFactoryBuilder().build(is);
    }

    @Before
    public void bf(){
        session = factory.openSession();
    }

    @After
    public void af(){
        //提交事务
        session.commit();
        //关闭会话
        session.close();
    }
}

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
61
62

知识点：

3.1 if:

3.2 where:

3.3 trim, set:

3.4 foreach:

3.5 choose(when,otherwise):

3.6 返回自动增长的主键

3.7 sql标签定义通用sql片段

案例演练：（员工）

（仅展示主要添加代码，其他的mybatis配置等与上篇保持一致）

1.条件查询：if where trim

代码整体布局：

代码如下：

EmpMapper:

package com.yzh7.mapper;

import com.yzh7.entity.Emp;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface EmpMapper {
    //查询所有员工，并且每个员工关联一个部门对象
    List listAll();

    //条件查询1(多参查询)
    List test1(@Param("empName") String empName,
                    @Param("phone") String phone);

    List test11(@Param("empName") String empName,
                    @Param("phone") String phone);
    //条件查询2（对象查询）
    List test2(Map map);

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

EmpMapper.xml:

    


        
        

        
        
        
        
        


        
        

            
            
            
        
    

        
    
        select e.emp_id,e.emp_name,e.sex,e.phone,
                d.dept_id,d.dept_name,d.description
        from t_emp e
        join t_dept d
        on e.dept_id=d.dept_id
       


    
        select e.emp_id,e.emp_name,e.sex,e.phone
        from t_emp e
        
            
                and emp_name like concat('%',#{empName},'%')
            
            
                and phone like concat('%',#{phone},'%')
            
        
    

    
        select e.emp_id,e.emp_name,e.sex,e.phone
        from t_emp e
        
            
                and emp_name like concat('%',#{empName},'%')
            
            
                and phone like concat('%',#{phone},'%')
            
        
    

    
        select e.emp_id ,e.emp_name ,e.sex,e.phone
        from t_emp e
        
            
                and emp_id > #{startId}
            
            
                
            
            
                and emp_name like concat('%',#{empName},'%')
            
        
    


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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

demo5:

package com.yzh7.test;

import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Demo5 {

    //条件查询
    @Test
    public void testTest1(){
        EmpMapper empMapper=session.getMapper(EmpMapper.class);
        //List empList=empMapper.test1(null,null);
        List empList=empMapper.test1("张","123");
        System.out.println(empList);
    }

    @Test
    public void testTest11(){
        EmpMapper empMapper=session.getMapper(EmpMapper.class);
        //List empList=empMapper.test1(null,null);
        List empList=empMapper.test11("张","123");
        System.out.println(empList);
    }

    @Test
    public void test2(){
        Map map = new HashMap();
        map.put("startId",1);
        map.put("endId",3);
        map.put("empName","四");

        EmpMapper empMapper = session.getMapper(EmpMapper.class);
        List empList = empMapper.test2(map);
        System.out.println(empList);
    }


    private static SqlSessionFactory factory;    //静态
    private SqlSession session;

    @BeforeClass
    public static void befCla() throws IOException {
        String resource = "mybatis_config.xml";   //mybatis_config.xml
        InputStream inputStream = Resources.getResourceAsStream(resource);
        factory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Before
    public void bf(){
        session=factory.openSession();
    }

    @After
    public void af(){
        session.commit();
        session.close();
    }
}

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
61
62
63
64
65
66
67
68
69
70
71
72
73

结果展示：

testTest1():

testTest11():

testTest2():

2.修改：set

代码整体布局：

代码如下：

EmpMapper:

package com.yzh7.mapper;

import com.yzh7.entity.Emp;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface EmpMapper {
    //查询所有员工，并且每个员工关联一个部门对象
    List listAll();

    //条件查询1(多参查询)
    List test1(@Param("empName") String empName,
                    @Param("phone") String phone);

    List test11(@Param("empName") String empName,
                    @Param("phone") String phone);
    //条件查询2（对象查询）
    List test2(Map map);

    //针对所有字段进行修改
    int update1(Emp emp);
    //根据传入的数据是否为空，不为空的才修改
    int update2(Emp emp);


}

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

EmpMapper.xml:

    


        
        

        
        
        
        
        


        
        

            
            
            
        
    

        
    
        select e.emp_id,e.emp_name,e.sex,e.phone,
                d.dept_id,d.dept_name,d.description
        from t_emp e
        join t_dept d
        on e.dept_id=d.dept_id
    


    
        select e.emp_id,e.emp_name,e.sex,e.phone
        from t_emp e
        
            
                and emp_name like concat('%',#{empName},'%')
            
            
                and phone like concat('%',#{phone},'%')
            
        
    

    
        select e.emp_id,e.emp_name,e.sex,e.phone
        from t_emp e
        
            
                and emp_name like concat('%',#{empName},'%')
            
            
                and phone like concat('%',#{phone},'%')
            
        
    

    
        select e.emp_id ,e.emp_name ,e.sex,e.phone
        from t_emp e
        
            
                and emp_id > #{startId}
            
            
                
            
            
                and emp_name like concat('%',#{empName},'%')
            
        
    


    
        update t_emp
        set emp_name=#{empName},
            sex=#{sex},
            phone=#{phone},
            dept_id=#{dept.deptId}
        where emp_id=#{empId}
    


        
        update t_emp
        
            
                emp_name=#{empName},
            
            
                sex=#{sex},
            
            
                phone=#{phone},
            
            
                dept_id=#{dept.deptId},
            
        
        where emp_id=#{empId}
    


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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118

demo6:

package com.yzh7.test;

import com.yzh7.entity.Dept;
import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

public class Demo6 {
    @Test
    public void testUpdate1(){
        EmpMapper empMapper=session.getMapper(EmpMapper.class);
        //int update1(Emp emp); //构建要修改的员工对象
        Emp emp=new Emp();
        emp.setEmpId(1);
        emp.setEmpName("张三疯"); //如果表中的列不赋值，将会赋空值

        Dept dept=new Dept();
        dept.setDeptId(2);
        emp.setDept(dept); //注意

        int count=empMapper.update1(emp);
        System.out.println("修改的记录数："+count);

    }

    @Test
    public void testUpdate2(){
        EmpMapper empMapper=session.getMapper(EmpMapper.class);
        //int update1(Emp emp); //构建要修改的员工对象
        Emp emp=new Emp();
        emp.setEmpId(2);
        emp.setEmpName("李四皮"); //如果表中的列不赋值，将会赋空值

        Dept dept=new Dept();
        dept.setDeptId(3);
        emp.setDept(dept); //注意

        int count=empMapper.update2(emp);
        System.out.println("修改的记录数："+count);
    }






    private static SqlSessionFactory factory;    //静态
    private SqlSession session;

    @BeforeClass
    public static void befCla() throws IOException {
        String resource = "mybatis_config.xml";   //mybatis_config.xml
        InputStream inputStream = Resources.getResourceAsStream(resource);
        factory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Before
    public void bf(){
        session=factory.openSession();
    }

    @After
    public void af(){
        session.commit();
        session.close();
    }
}

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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

结果展示：

testUpdate1():

比较生硬霸道的修改方式：

修改后的数据表：

testUpdate2():

使用set、if标签之后：

修改后的数据表：

3.循环拼接和批量插入：foreach

思路：

代码整体布局：

代码如下：

EmpMapper:

package com.yzh7.mapper;

import com.yzh7.entity.Emp;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface EmpMapper {
    //查询所有员工，并且每个员工关联一个部门对象
    List listAll();

    //条件查询1(多参查询)
    List test1(@Param("empName") String empName,
                    @Param("phone") String phone);

    List test11(@Param("empName") String empName,
                    @Param("phone") String phone);
    //条件查询2（对象查询）
    List test2(Map map);

    //针对所有字段进行修改
    int update1(Emp emp);
    //根据传入的数据是否为空，不为空的才修改
    int update2(Emp emp);

    //根据编号查询数据
    List testByIds(List ids);
    //批量插入
    int insertBatch(List empList);

}
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

EmpMapper.xml:

    


        
        

        
        
        
        
        


        
        

            
            
            
        
    

        
    
        select e.emp_id,e.emp_name,e.sex,e.phone,
                d.dept_id,d.dept_name,d.description
        from t_emp e
        join t_dept d
        on e.dept_id=d.dept_id
    


    
        select e.emp_id,e.emp_name,e.sex,e.phone
        from t_emp e
        
            
                and emp_name like concat('%',#{empName},'%')
            
            
                and phone like concat('%',#{phone},'%')
            
        
    

    
        select e.emp_id,e.emp_name,e.sex,e.phone
        from t_emp e
        
            
                and emp_name like concat('%',#{empName},'%')
            
            
                and phone like concat('%',#{phone},'%')
            
        
    

    
        select e.emp_id ,e.emp_name ,e.sex,e.phone
        from t_emp e
        
            
                and emp_id > #{startId}
            
            
                
            
            
                and emp_name like concat('%',#{empName},'%')
            
        
    


    
        update t_emp
        set emp_name=#{empName},
            sex=#{sex},
            phone=#{phone},
            dept_id=#{dept.deptId}
        where emp_id=#{empId}
    


    
        update t_emp
        
            
                emp_name=#{empName},
            
            
                sex=#{sex},
            
            
                phone=#{phone},
            
            
                dept_id=#{dept.deptId},
            
        
        where emp_id=#{empId}
    





    
    
        select * from t_emp
        where emp_id in
        
            #{data}
        
    
    
    
        insert into t_emp
        (emp_name,sex,phone)
        values
        
            (#{e.empName},#{e.sex},#{e.phone})
        

    
    

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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153

Demo7:

package com.yzh7.test;

import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class Demo7 {
    @Test
    public void testTestByIds(){
        EmpMapper empMapper=session.getMapper(EmpMapper.class);
        List ids=new ArrayList<>();
        ids.add(1);
        ids.add(3);
        ids.add(4);

        List empList=empMapper.testByIds(ids);
        System.out.println(empList);
    }

    @Test
    public void testInsertBatch(){
        EmpMapper empMapper=session.getMapper(EmpMapper.class);
        Emp emp1=new Emp();
        emp1.setEmpName("aaa");
        emp1.setSex("男");
        emp1.setPhone("1234561");

        Emp emp2=new Emp();
        emp2.setEmpName("bbb");
        emp2.setSex("女");
        emp2.setPhone("1234562");

        Emp emp3=new Emp();
        emp3.setEmpName("ccc");
        emp3.setSex("男");
        emp3.setPhone("1234563");

        List empList=new ArrayList<>();
        empList.add(emp1);
        empList.add(emp2);
        empList.add(emp3);

        int count =empMapper.insertBatch(empList);
        System.out.println("批量插入的记录数；"+count);
    }


    private static SqlSessionFactory factory;    //静态
    private SqlSession session;

    @BeforeClass
    public static void befCla() throws IOException {
        String resource = "mybatis_config.xml";   //mybatis_config.xml
        InputStream inputStream = Resources.getResourceAsStream(resource);
        factory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Before
    public void bf(){
        session=factory.openSession();
    }

    @After
    public void af(){
        session.commit();
        session.close();
    }
}

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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

结果展示：

testTestByIds():

testInsertBatch():

批量插入之后，数据表如下：

4.choose 返回自动增长的主键

结果展示：

代码整体布局：

代码如下：

EmpMapper:

package com.yzh7.mapper;

import com.yzh7.entity.Emp;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface EmpMapper {
    //查询所有员工，并且每个员工关联一个部门对象
    List listAll();

    //条件查询1(多参查询)
    List test1(@Param("empName") String empName,
                    @Param("phone") String phone);

    List test11(@Param("empName") String empName,
                    @Param("phone") String phone);
    //条件查询2（对象查询）
    List test2(Map map);

    //针对所有字段进行修改
    int update1(Emp emp);
    //根据传入的数据是否为空，不为空的才修改
    int update2(Emp emp);

    //根据编号查询数据
    List testByIds(List ids);
    //批量插入
    int insertBatch(List empList);

    //分支条件查询
    List testByChoose(Emp emp);


    //插入并接受返回的自增值
    int insertAndGetAutoVal(Emp emp);

}
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

EmpMapper.xml:

    


        
        

        
        
        
        
        


        
        

            
            
            
        
    

        
    
        select e.emp_id,e.emp_name,e.sex,e.phone,
                d.dept_id,d.dept_name,d.description
        from t_emp e
        join t_dept d
        on e.dept_id=d.dept_id
    


    
        select e.emp_id,e.emp_name,e.sex,e.phone
        from t_emp e
        
            
                and emp_name like concat('%',#{empName},'%')
            
            
                and phone like concat('%',#{phone},'%')
            
        
    

    
        select e.emp_id,e.emp_name,e.sex,e.phone
        from t_emp e
        
            
                and emp_name like concat('%',#{empName},'%')
            
            
                and phone like concat('%',#{phone},'%')
            
        
    

    
        select e.emp_id ,e.emp_name ,e.sex,e.phone
        from t_emp e
        
            
                and emp_id > #{startId}
            
            
                
            
            
                and emp_name like concat('%',#{empName},'%')
            
        
    


    
        update t_emp
        set emp_name=#{empName},
            sex=#{sex},
            phone=#{phone},
            dept_id=#{dept.deptId}
        where emp_id=#{empId}
    


    
        update t_emp
        
            
                emp_name=#{empName},
            
            
                sex=#{sex},
            
            
                phone=#{phone},
            
            
                dept_id=#{dept.deptId},
            
        
        where emp_id=#{empId}
    





    
    
        select * from t_emp
        where emp_id in
        
            #{data}
        
    
    
    
        insert into t_emp
        (emp_name,sex,phone)
        values
        
            (#{e.empName},#{e.sex},#{e.phone})
        

    

    
        select * from t_emp
        
            
                
                    and emp_name=#{empName}
                
                
                    and sex=#{sex}
                
                
                    and emp_id=1
                
            
        
    

    
    
        insert into t_emp
        (emp_name,sex,phone)
        values
        (#{empName},#{sex},#{phone})
    


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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181

Demo8:

package com.yzh7.test;

import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;


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

public class Demo8 {
    // List testByChoose(Emp emp);
    @Test
    public void testTestByChoose(){
        EmpMapper empMapper=session.getMapper(EmpMapper.class);
        Emp emp=new Emp();
        emp.setEmpName("王五");
        emp.setSex("女");

        List empList=empMapper.testByChoose(emp);
        System.out.println(empList);

    }

    @Test
    public void testInsertAndGetAutoVal(){
        EmpMapper empMapper=session.getMapper(EmpMapper.class);
        Emp emp=new Emp();
        emp.setEmpName("王十一");
        emp.setSex("男");
        emp.setPhone("1234561");

        int count=empMapper.insertAndGetAutoVal(emp);
        System.out.println("记录数："+count);
        System.out.println("插入之后，返回的子增值："+emp);
    }


    private static SqlSessionFactory factory;
    private SqlSession session;

    @BeforeClass
    public static void befCla() throws IOException {
        String resource="mybatis_config.xml";
        InputStream inputStream= Resources.getResourceAsStream(resource);
        factory=new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Before
    public void bf(){
        session=factory.openSession();
    }

    @After
    public void af(){
        session.commit();
        session.close();
    }
}

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
61
62
63
64
65
66
67
68

5.sql标签定义通用sql片段 sql

结果展示：

代码整体布局：

代码如下：

EmpMapper:

package com.yzh7.mapper;

import com.yzh7.entity.Emp;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface EmpMapper {
    //查询所有员工，并且每个员工关联一个部门对象
    List listAll();

    //条件查询1(多参查询)
    List test1(@Param("empName") String empName,
                    @Param("phone") String phone);

    List test11(@Param("empName") String empName,
                    @Param("phone") String phone);
    //条件查询2（对象查询）
    List test2(Map map);

    //针对所有字段进行修改
    int update1(Emp emp);
    //根据传入的数据是否为空，不为空的才修改
    int update2(Emp emp);

    //根据编号查询数据
    List testByIds(List ids);
    //批量插入
    int insertBatch(List empList);

    //分支条件查询
    List testByChoose(Emp emp);


    //插入并接受返回的自增值
    int insertAndGetAutoVal(Emp emp);

    //条件查询3
    Emp test3(Integer empId);

    //条件查询4
    List test4(Map map);


}

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

EmpMapper.xml:

    


        
        

        
        
        
        
        


        
        

            
            
            
        
    

        
    
        select e.emp_id,e.emp_name,e.sex,e.phone,
                d.dept_id,d.dept_name,d.description
        from t_emp e
        join t_dept d
        on e.dept_id=d.dept_id
    


    
        select e.emp_id,e.emp_name,e.sex,e.phone
        from t_emp e
        
            
                and emp_name like concat('%',#{empName},'%')
            
            
                and phone like concat('%',#{phone},'%')
            
        
    

    
        select e.emp_id,e.emp_name,e.sex,e.phone
        from t_emp e
        
            
                and emp_name like concat('%',#{empName},'%')
            
            
                and phone like concat('%',#{phone},'%')
            
        
    

    
        select e.emp_id ,e.emp_name ,e.sex,e.phone
        from t_emp e
        
            
                and emp_id > #{startId}
            
            
                
            
            
                and emp_name like concat('%',#{empName},'%')
            
        
    


    
        update t_emp
        set emp_name=#{empName},
            sex=#{sex},
            phone=#{phone},
            dept_id=#{dept.deptId}
        where emp_id=#{empId}
    


    
        update t_emp
        
            
                emp_name=#{empName},
            
            
                sex=#{sex},
            
            
                phone=#{phone},
            
            
                dept_id=#{dept.deptId},
            
        
        where emp_id=#{empId}
    





    
    
        select * from t_emp
        where emp_id in
        
            #{data}
        
    
    
    
        insert into t_emp
        (emp_name,sex,phone)
        values
        
            (#{e.empName},#{e.sex},#{e.phone})
        

    

    
        select * from t_emp
        
            
                
                    and emp_name=#{empName}
                
                
                    and sex=#{sex}
                
                
                    and emp_id=1
                
            
        
    

    
    
        insert into t_emp
        (emp_name,sex,phone)
        values
        (#{empName},#{sex},#{phone})
    

    
    
        emp_id,emp_name,sex,phone
    

    
        select
        
        from t_emp
        where emp_id=#{empId}
    

    
        select e.emp_id ,e.emp_name ,e.sex,e.phone,
               d.dept_id,d.dept_name,d.description
        from t_emp e
                 join t_dept d
                      on e.dept_id = d.dept_id
    
    
        
        
            
                and emp_name like concat('%',#{empName},'%')
            
        
    

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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208

Demo9:

package com.yzh7.test;

import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;


import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Demo9 {
    @Test
    public void testTest3(){
        EmpMapper empMapper=session.getMapper(EmpMapper.class);

        System.out.println(empMapper.test3(1));
    }

    @Test
    public void testTest4(){
        EmpMapper empMapper=session.getMapper(EmpMapper.class);
        Map map=new HashMap();
        map.put("empName","李");
        List empList=empMapper.test4(map);
        System.out.println(empList);
    }

    private static SqlSessionFactory factory;
    private SqlSession session;

    @BeforeClass
    public static void befCla() throws IOException {
        String resource="mybatis_config.xml";
        InputStream inputStream= Resources.getResourceAsStream(resource);
        factory=new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Before
    public void bf(){
        session=factory.openSession();
    }

    @After
    public void af(){
        session.commit();
        session.close();
    }
}

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