Mybatis运行

首先运行一下MyBatis生成一下mapper文件
建一个java文件
MBGTest.java

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
package com.ceit.crud.test;
import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.exception.InvalidConfigurationException;
import org.mybatis.generator.exception.XMLParserException;
import org.mybatis.generator.internal.DefaultShellCallback;
import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MBGTest {
    public static void main(String[] args) throws IOException, XMLParserException, InvalidConfigurationException, SQLException, InterruptedException {
        List<String> warnings = new ArrayList<String>();
        boolean overwrite = true;
        File configFile = new File("F:\\default\\SSM\\src\\main\\resources\\generatorConfig.xml");
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(configFile);
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
    }
}

注意要修改路径

运行之后，mapper和dao中就会出现两个文件

bean下也会出现相应的文件

实现多表联合查询

生成之后的文件以及查询方法，不太能满足我们的需要，所以需要进行一些更改，来满足通过部门信息就能查询出来，实现一个多表联合查询

在dao包下的EmployeeMapper.java中
添加上:

1
2
3
List<Employee> selectByExampleWithDept(EmployeeExample example);

Employee selectByPrimaryKeyWithDept(Integer empId);

并在bean包下的Emplotee.java员工信息中添加
1
private Department  department;

并加上getter和setter

在EmployeeMapper.xml 添加sql标签

1
2
3
<sql id="WithDept_Column_List">
  e.emp_id, e.emp_name, e.gender, e.email, e.d_id, d.dept_id, d.dept_name
</sql>

接着添加查询方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<select id="selectByExampleWithDept" >
		 select
  <if test="distinct">
    distinct
  </if>
  <include refid="WithDept_Column_List" />
  from tbl_emp e
  LEFT JOIN tbl_dept d ON e.`d_id`=d.`dept_id`
  <if test="_parameter != null">
    <include refid="Example_Where_Clause" />
  </if>
  <if test="orderByClause != null">
    order by ${orderByClause}
  </if>
</select>

在EmployeeMapper.xml下添加resultMap

1
2
3
4
5
6
7
8
9
10
11
<resultMap id="WithDeptResultMap" type="com.ceit.crud.bean.Employee">
  <id column="emp_id" jdbcType="INTEGER" property="empId" />
  <result column="emp_name" jdbcType="VARCHAR" property="empName" />
  <result column="gender" jdbcType="CHAR" property="gender" />
  <result column="email" jdbcType="VARCHAR" property="email" />
  <result column="d_id" jdbcType="INTEGER" property="dId" />
  <association property="department" javaType="com.ceit.crud.bean.Department" >
      <id column="dept_id" property="deptId" />
      <result column="dept_name" property="deptName"/>
  </association>
</resultMap>

并修改selectByExampleWithDept为

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<select id="selectByExampleWithDept" resultMap="WithDeptResultMap">
  select
  <if test="distinct">
    distinct
  </if>
  <include refid="WithDept_Column_List" />
  from tbl_emp e
  LEFT JOIN tbl_dept d ON e.`d_id`=d.`dept_id`
  <if test="_parameter != null">
    <include refid="Example_Where_Clause" />
  </if>
  <if test="orderByClause != null">
    order by ${orderByClause}
  </if>
</select>

部门的查询就完成了

接着同理写一个主键的sql标签

1
2
3
4
5
6
7
<select id="selectByPrimaryKeyWithDept" resultMap="WithDeptResultMap">
  select
  <include refid="WithDept_Column_List" />
  from tbl_emp e
  LEFT JOIN tbl_dept d ON e.`d_id`=d.`dept_id`
  where emp_id = #{empId,jdbcType=INTEGER}
</select>

最终的EmployeeMapper.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
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
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
<?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.ceit.crud.dao.EmployeeMapper">
  <resultMap id="BaseResultMap" type="com.ceit.crud.bean.Employee">
    <id column="emp_id" jdbcType="INTEGER" property="empId" />
    <result column="emp_name" jdbcType="VARCHAR" property="empName" />
    <result column="gender" jdbcType="CHAR" property="gender" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="d_id" jdbcType="INTEGER" property="dId" />
  </resultMap>
  <resultMap id="WithDeptResultMap" type="com.ceit.crud.bean.Employee">
    <id column="emp_id" jdbcType="INTEGER" property="empId" />
    <result column="emp_name" jdbcType="VARCHAR" property="empName" />
    <result column="gender" jdbcType="CHAR" property="gender" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="d_id" jdbcType="INTEGER" property="dId" />
<!--    指定联合查询出的部门字段的封装-->
    <association property="department" javaType="com.ceit.crud.bean.Department" >
        <id column="dept_id" property="deptId" />
        <result column="dept_name" property="deptName"/>
    </association>
  </resultMap>
  <sql id="Example_Where_Clause">
    <where>
      <foreach collection="oredCriteria" item="criteria" separator="or">
        <if test="criteria.valid">
          <trim prefix="(" prefixOverrides="and" suffix=")">
            <foreach collection="criteria.criteria" item="criterion">
              <choose>
                <when test="criterion.noValue">
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue">
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue">
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Update_By_Example_Where_Clause">
    <where>
      <foreach collection="example.oredCriteria" item="criteria" separator="or">
        <if test="criteria.valid">
          <trim prefix="(" prefixOverrides="and" suffix=")">
            <foreach collection="criteria.criteria" item="criterion">
              <choose>
                <when test="criterion.noValue">
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue">
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue">
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Base_Column_List">
    emp_id, emp_name, gender, email, d_id
  </sql>
  <sql id="WithDept_Column_List">
    e.emp_id, e.emp_name, e.gender, e.email, e.d_id, d.dept_id, d.dept_name
  </sql>
<!--      List<Employee> selectByExampleWithDept(EmployeeExample example);

    Employee selectByPrimaryKeyWithDept(Integer empId);
-->

<!--  查询员工同时带部门-->
  <select id="selectByExampleWithDept" resultMap="WithDeptResultMap">
    select
    <if test="distinct">
      distinct
    </if>
    <include refid="WithDept_Column_List" />
    from tbl_emp e
    LEFT JOIN tbl_dept d ON e.`d_id`=d.`dept_id`
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
  </select>
  <select id="selectByPrimaryKeyWithDept" resultMap="WithDeptResultMap">
    select
    <include refid="WithDept_Column_List" />
    from tbl_emp e
    LEFT JOIN tbl_dept d ON e.`d_id`=d.`dept_id`
    where emp_id = #{empId,jdbcType=INTEGER}
  </select>

<!-- 查询员工不带部门-->
  <select id="selectByExample" parameterType="com.ceit.crud.bean.EmployeeExample" resultMap="BaseResultMap">
    select
    <if test="distinct">
      distinct
    </if>
    <include refid="Base_Column_List" />
    from tbl_emp
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
  </select>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from tbl_emp
    where emp_id = #{empId,jdbcType=INTEGER}
  </select>

  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from tbl_emp
    where emp_id = #{empId,jdbcType=INTEGER}
  </delete>
  <delete id="deleteByExample" parameterType="com.ceit.crud.bean.EmployeeExample">
    delete from tbl_emp
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
  </delete>

  <insert id="insert" parameterType="com.ceit.crud.bean.Employee">
    insert into tbl_emp (emp_id, emp_name, gender, 
      email, d_id)
    values (#{empId,jdbcType=INTEGER}, #{empName,jdbcType=VARCHAR}, #{gender,jdbcType=CHAR}, 
      #{email,jdbcType=VARCHAR}, #{dId,jdbcType=INTEGER})
  </insert>
  <insert id="insertSelective" parameterType="com.ceit.crud.bean.Employee">
    insert into tbl_emp
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="empId != null">
        emp_id,
      </if>
      <if test="empName != null">
        emp_name,
      </if>
      <if test="gender != null">
        gender,
      </if>
      <if test="email != null">
        email,
      </if>
      <if test="dId != null">
        d_id,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="empId != null">
        #{empId,jdbcType=INTEGER},
      </if>
      <if test="empName != null">
        #{empName,jdbcType=VARCHAR},
      </if>
      <if test="gender != null">
        #{gender,jdbcType=CHAR},
      </if>
      <if test="email != null">
        #{email,jdbcType=VARCHAR},
      </if>
      <if test="dId != null">
        #{dId,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>

  <select id="countByExample" parameterType="com.ceit.crud.bean.EmployeeExample" resultType="java.lang.Long">
    select count(*) from tbl_emp
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
  </select>

  <update id="updateByExampleSelective" parameterType="map">
    update tbl_emp
    <set>
      <if test="record.empId != null">
        emp_id = #{record.empId,jdbcType=INTEGER},
      </if>
      <if test="record.empName != null">
        emp_name = #{record.empName,jdbcType=VARCHAR},
      </if>
      <if test="record.gender != null">
        gender = #{record.gender,jdbcType=CHAR},
      </if>
      <if test="record.email != null">
        email = #{record.email,jdbcType=VARCHAR},
      </if>
      <if test="record.dId != null">
        d_id = #{record.dId,jdbcType=INTEGER},
      </if>
    </set>
    <if test="_parameter != null">
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByExample" parameterType="map">
    update tbl_emp
    set emp_id = #{record.empId,jdbcType=INTEGER},
      emp_name = #{record.empName,jdbcType=VARCHAR},
      gender = #{record.gender,jdbcType=CHAR},
      email = #{record.email,jdbcType=VARCHAR},
      d_id = #{record.dId,jdbcType=INTEGER}
    <if test="_parameter != null">
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>

  <update id="updateByPrimaryKeySelective" parameterType="com.ceit.crud.bean.Employee">
    update tbl_emp
    <set>
      <if test="empName != null">
        emp_name = #{empName,jdbcType=VARCHAR},
      </if>
      <if test="gender != null">
        gender = #{gender,jdbcType=CHAR},
      </if>
      <if test="email != null">
        email = #{email,jdbcType=VARCHAR},
      </if>
      <if test="dId != null">
        d_id = #{dId,jdbcType=INTEGER},
      </if>
    </set>
    where emp_id = #{empId,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.ceit.crud.bean.Employee">
    update tbl_emp
    set emp_name = #{empName,jdbcType=VARCHAR},
      gender = #{gender,jdbcType=CHAR},
      email = #{email,jdbcType=VARCHAR},
      d_id = #{dId,jdbcType=INTEGER}
    where emp_id = #{empId,jdbcType=INTEGER}
  </update>
</mapper>

测试过程

接下来就创建一个MapperTest.java来简单测试一下

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
package com.ceit.crud.test;

import com.ceit.crud.bean.Department;
import com.ceit.crud.bean.DepartmentExample;
import com.ceit.crud.dao.DepartmentMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;



public class MapperTest {
    /**
     * 测试DepartmentMapper
     */
    @Test
    public void testCRUD(){
        //1.创建SpringIOC容器
        ApplicationContext ioc=new ClassPathXmlApplicationContext("applicationContext.xml");
        //2.从容器中获取mapper
        DepartmentMapper bean = ioc.getBean(DepartmentMapper.class);
        System.out.println(bean);

        //1.插入几个部门
        bean.insertSelective(new Department(null,"开发部"));
        bean.insertSelective(new Department(null,"测试部"));

    }

}

运行完成后，查看数据库已经发生变化，插入成功

剩下的基本上就是基本操作了