二 MyBatis逆向工程
1 介绍
Maven中的插件,可根据数据表生成实体类 Mapper 接口和 Mapper XML,可单独创建一个Maven项目,将所需的资源生成后,拷贝到对应的项目中(推荐),或者直接在项目中使用。
2 配置pom依赖插件
<build>
<plugins>
<plugin>
<groupId>org.apache.tomcat.maven</groupId>
<artifactId>tomcat7-maven-plugin</artifactId>
<version>2.1</version>
<configuration>
<port>8080</port> <!-- 端口 -->
<path>/</path> <!-- 上下路径 -->
<uriEncoding>UTF-8</uriEncoding> <!-- 针对 GET 方式乱码处理 -->
</configuration>
</plugin>
<!-- MyBatis 逆向工程插件 -->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.2</version>
<configuration>
<verbose>true</verbose>
<overwrite>false</overwrite>
</configuration>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.45</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
3 数据库表
# 部门表
CREATE TABLE `department` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '表示部门编号,由数字组成',
`name` varchar(14) DEFAULT NULL COMMENT '部门名称,最多由14个字符所组成',
`sn` varchar(13) DEFAULT NULL COMMENT '部门编号',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
# 员工表
CREATE TABLE `employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`admin` bit(1) DEFAULT NULL,
`dept_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
4 配置generatorConfig.xml – resources目录
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<!-- 配置生成器 -->
<generatorConfiguration>
<!--选用MyBatis3版本的简化模型-->
<context id="mysql" defaultModelType="hierarchical" targetRuntime="MyBatis3Simple">
<!-- 自动识别数据库关键字,默认false,如果设置为true,根据SqlReservedWords中定义的关键字列表; 一般保留默认值,遇到数据库关键字(Java关键字),使用columnOverride覆盖 -->
<property name="autoDelimitKeywords" value="false"/>
<!-- 生成的Java文件的编码 -->
<property name="javaFileEncoding" value="UTF-8"/>
<!-- 格式化java代码 -->
<property name="javaFormatter"
value="org.mybatis.generator.api.dom.DefaultJavaFormatter"/>
<!-- 格式化XML代码 -->
<property name="xmlFormatter"
value="org.mybatis.generator.api.dom.DefaultXmlFormatter"/>
<!-- beginningDelimiter和endingDelimiter:指明数据库的用于标记数据库对象名的符号,比如ORACLE就是双引号,MYSQL默认是`反引号; -->
<property name="beginningDelimiter" value="`"/>
<property name="endingDelimiter" value="`"/>
<commentGenerator>
<property name="suppressDate" value="true"/>
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!-- 必须要有的,使用这个配置链接数据库 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql:///ssm" userId="root" password="root">
<!-- 这里面可以设置property属性,每一个property属性都设置到配置的Driver上 -->
</jdbcConnection>
<!-- java类型处理器 用于处理DB中的类型到Java中的类型,默认使用JavaTypeResolverDefaultImpl; 注意一点,默认会先尝试使用Integer,Long,Short等来对应DECIMAL和 NUMERIC数据类型; -->
<javaTypeResolver
type="org.mybatis.generator.internal.types.JavaTypeResolverDefaultImpl">
<!-- true:使用BigDecimal对应DECIMAL和 NUMERIC数据类型 false:默认, scale>0;length>18:使用BigDecimal; scale=0;length[10,18]:使用Long; scale=0;length[5,9]:使用Integer; scale=0;length<5:使用Short; -->
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!-- java模型创建器,是必须的元素 负责:1,key类(见context的defaultModelType);2,java类;3,查询类 targetPackage:生成的类要放的包,真实的包受enableSubPackages属性控制; targetProject:当前项目指定一个存在的目录下,生成的内容会放到指定目录中,如果目录不存在,MBG不会自动建目录,于该目录下创建对应的包 -->
<javaModelGenerator targetPackage="cn.tj.domain"
targetProject="src/main/java">
<!-- for MyBatis3/MyBatis3Simple 自动为每一个生成的类创建一个构造方法,构造方法包含了所有的field;而不是使用setter; -->
<property name="constructorBased" value="false"/>
<!-- for MyBatis3 / MyBatis3Simple 是否创建一个不可变的类,如果为true, 那么MBG会创建一个没有setter方法的类,取而代之的是类似constructorBased的类 -->
<property name="immutable" value="false"/>
<!-- 设置是否在getter方法中,对String类型字段调用trim()方法
<property name="trimStrings" value="true" /> -->
</javaModelGenerator>
<!-- 生成SQL map的XML文件生成器, 注意,在Mybatis3之后,我们可以使用mapper.xml文件+Mapper接口(或者不用mapper接口),或者只使用Mapper接口+Annotation,所以,如果 javaClientGenerator配置中配置了需要生成XML的话,这个元素就必须配置targetPackage/targetProject:同javaModelGenerator -->
<sqlMapGenerator targetPackage="cn.tj.mapper"
targetProject="src/main/resources">
<!-- 在targetPackage的基础上,根据数据库的schema再生成一层package,最终生成的类放在这个package下,默认为false -->
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!--生成Mapper接口,注意,如果没有配置该元素,那么默认不会生成Mapper接口targetPackage/targetProject:同javaModelGenerator type:选择怎么生成mapper接口(在MyBatis3/MyBatis3Simple下): 1,ANNOTATEDMAPPER:会生成使用Mapper接口+Annotation的方式创建(SQL生成在annotation中),不会生成对应的XML;2,MIXEDMAPPER:使用混合配置,会生成Mapper接口,并适当添加合适的Annotation,但是XML会生成在XML中; 3,XMLMAPPER:会生成Mapper接口,接口完全依赖XML;注意,如果context是MyBatis3Simple:只支持ANNOTATEDMAPPER和XMLMAPPER -->
<javaClientGenerator targetPackage="cn.tj.mapper"
type="XMLMAPPER" targetProject="src/main/java">
<!-- 在targetPackage的基础上,根据数据库的schema再生成一层package,最终生成的类放在这个package下,默认为false -->
<property name="enableSubPackages" value="true"/>
<!-- 可以为所有生成的接口添加一个父接口,但是MBG只负责生成,不负责检查 <property name="rootInterface" value=""/> -->
</javaClientGenerator>
<!--通过修改tableName,完成对指定表的逆向工程,一个table标签代表一张表-->
<table tableName="employee">
<property name="useActualColumnNames" value="true"/>
<property name="constructorBased" value="false"/>
<generatedKey column="id" sqlStatement="JDBC"/>
</table>
<table tableName="department">
<property name="useActualColumnNames" value="true"/>
<property name="constructorBased" value="false"/>
<generatedKey column="id" sqlStatement="JDBC"/>
</table>
</context>
</generatorConfiguration>
5 运行逆向工程
双击运行即可,此时生成的为单表的增删改查操作,多表关联需进行编辑
6 生成的实体类
// Department
@Data
public class Department {
private Long id;
private String name;
private String sn;
}
// Employee
@Data
public class Employee {
private Long id;
private String username;
private String name;
private String password;
private String email;
private Integer age;
private Boolean admin;
private Long dept_id;
private Department dept;
}
7 生成的接口
// DepartmentMapper
public interface DepartmentMapper {
/*删除*/
int deleteByPrimaryKey(Long id);
/*增加*/
int insert(Department record);
/*根据id查询*/
Department selectByPrimaryKey(Long id);
/*查询所有*/
List<Department> selectAll();
/*修改*/
int updateByPrimaryKey(Department record);
/*查询总条数*/
public int selectForCount(QueryObject qo);
/*分页查询*/
public List<Department> selectForList(QueryObject qo);
}
// EmployeeMapper
public interface EmployeeMapper {
int deleteByPrimaryKey(Long id);
int insert(Employee record);
Employee selectByPrimaryKey(Long id);
List<Employee> selectAll();
int updateByPrimaryKey(Employee record);
/*查询总条数*/
public int selectForCount(EmployeeQueryObject qo);
/*分页查询*/
public List<Employee> selectForList(EmployeeQueryObject qo);
}
8 生成的xml
DepartmentMapper.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="cn.tj.mapper.DepartmentMapper" >
<resultMap id="BaseResultMap" type="cn.tj.domain.Department" >
<id column="id" property="id" jdbcType="BIGINT" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="sn" property="sn" jdbcType="VARCHAR" />
</resultMap>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
delete from department
where id = #{id,jdbcType=BIGINT}
</delete>
<insert id="insert" parameterType="cn.tj.domain.Department" useGeneratedKeys="true" keyProperty="id" >
insert into department (name, sn)
values (#{name,jdbcType=VARCHAR}, #{sn,jdbcType=VARCHAR})
</insert>
<update id="updateByPrimaryKey" parameterType="cn.tj.domain.Department" >
update department
set name = #{name,jdbcType=VARCHAR},
sn = #{sn,jdbcType=VARCHAR}
where id = #{id,jdbcType=BIGINT}
</update>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
select id, name, sn
from department
where id = #{id,jdbcType=BIGINT}
</select>
<select id="selectAll" resultMap="BaseResultMap" >
select id, name, sn
from department
</select>
<!--查询总条数-->
<select id="selectForCount" resultType="int">
SELECT count(*) from department
</select>
<!--分页查询部门-->
<select id="selectForList" resultMap="BaseResultMap">
SELECT * from department limit #{start},#{pageSize}
</select>
</mapper>
EmployeeMapper.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="cn.tj.mapper.EmployeeMapper" >
<resultMap id="BaseResultMap" type="cn.tj.domain.Employee" >
<id column="id" property="id" jdbcType="BIGINT" />
<result column="username" property="username" jdbcType="VARCHAR" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<result column="email" property="email" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
<result column="admin" property="admin" jdbcType="BIT" />
<result column="dept_id" property="dept_id" jdbcType="BIGINT" />
<result column="d_id" property="dept.id" jdbcType="BIGINT" />
<result column="d_name" property="dept.name" jdbcType="VARCHAR" />
<result column="d_sn" property="dept.sn" jdbcType="VARCHAR" />
</resultMap>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
delete from employee
where id = #{id,jdbcType=BIGINT}
</delete>
<insert id="insert" parameterType="cn.tj.domain.Employee" useGeneratedKeys="true" keyProperty="id" >
insert into employee (username, name, password,
email, age, admin, dept_id
)
values (#{username,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},
#{email,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, #{admin,jdbcType=BIT}, #{dept.id}
)
</insert>
<update id="updateByPrimaryKey" parameterType="cn.tj.domain.Employee" >
update employee
set username = #{username,jdbcType=VARCHAR},
name = #{name,jdbcType=VARCHAR},
email = #{email,jdbcType=VARCHAR},
age = #{age,jdbcType=INTEGER},
admin = #{admin,jdbcType=BIT},
dept_id = #{dept.id}
where id = #{id,jdbcType=BIGINT}
</update>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
select id, username, name, password, email, age, admin, dept_id
from employee
where id = #{id,jdbcType=BIGINT}
</select>
<select id="selectAll" resultMap="BaseResultMap" >
select id, username, name, password, email, age, admin, dept_id
from employee
</select>
<!--根据条件查询总条数-->
<select id="selectForCount" resultType="int">
SELECT count(*) from employee e
<include refid="where_sql"></include>
</select>
<!--根据条件分页查询-->
<select id="selectForList" resultMap="BaseResultMap">
SELECT e.*,d.id d_id,d.`name` d_name,d.sn d_sn
from employee e JOIN department d on e.dept_id=d.id
<include refid="where_sql"></include>
LIMIT #{start},#{pageSize};
</select>
<!--条件sql片段-->
<sql id="where_sql">
<where>
<if test="keyword != null">
and e.name like concat("%",#{keyword},"%") or e.email like concat("%",#{keyword},"%")
</if>
<if test="deptId !=null">
and e.dept_id =#{deptId}
</if>
</where>
</sql>
</mapper>