如果有误和遗漏,欢迎小伙伴发邮件 xiwelu@qq.com ,我会第一时间更正和完善
Mybatis-Generator
第一章:Generator工具使用
通过Mybatis-Generator自动生成Dao、Mapper、Model。Mybatis-Generator下载
generator详解
<?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>
<!--数据库驱动-->
<classPathEntry location="mysql-connector-java-5.0.8-bin.jar"/>
<context id="DB2Tables" targetRuntime="MyBatis3">
<commentGenerator>
<property name="suppressDate" value="true"/>
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!--数据库链接URL、用户名、密码-->
<jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost/mymessages" userId="root" password="root">
</jdbcConnection>
<javaTypeResolver>
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!--生成Model类存放位置-->
<javaModelGenerator targetPackage="com.jk.model" targetProject="src">
<property name="enableSubPackages" value="true"/>
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!--生成映射文件存放位置-->
<sqlMapGenerator targetPackage="com.jk.mapper" targetProject="src">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!--生成Dao类存放位置-->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.jk.dao" targetProject="src">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<!--生成对应表及类名-->
<table tableName="t_user" domainObjectName="User" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
</context>
</generatorConfiguration>
在cmd窗口输入生成语句:
D:\Software\generator> java -jar mybatis-generator-core-1.3.2.jar -configfile generatorConfig.xml -overwrite
MyBatis Generator finished successfully.
第二章:Eclipse中Mybatis-generator的使用
配置dependency
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.5</version>
</dependency>
配置plugin
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.2</version>
<executions>
<execution>
<id>Generate MyBatis Files</id>
<goals>
<goal>generate</goal>
</goals>
<phase>generate</phase>
<configuration>
<verbose>true</verbose>
<overwrite>true</overwrite>
</configuration>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.5</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.2</version>
</dependency>
</dependencies>
</plugin>
resource目录下创建generator.xml配置文件
<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPEgeneratorConfiguration PUBLIC"-//mybatis.org//DTDMyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<properties resource="schemeone/properties/mysql/mysql.properties"/>
<context id="mysqlTables" targetRuntime="MyBatis3">
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="${jdbc.url}" userId="${jdbc.username}" password="${jdbc.password}"/>
<!--指定生成的类型为java类型,避免数据库中number等类型字段 -->
<javaTypeResolver>
<propertyname="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!--自动生成的实体的存放包路径 -->
<javaModelGenerator targetPackage="com.jk.model" argetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!--自动生成的*Mapper.xml文件存放路径 -->
<sqlMapGenerator targetPackage="com.jk.mapper" targetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!--自动生成的*Mapper.java存放路径 -->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.jk.dao" targetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<table tableName="t_user" domainObjectName="User" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false">
<generatedKey column="ID" sqlStatement="select uuid_short()" identity="false"/>
</table>
<table tableName="t_book" domainObjectName="Book">
<generatedKey column="ID" sqlStatement="select uuid_short()" identity="false"/>
</table>
</context>
</generatorConfiguration>
在项目上点击右键
弹出的对话框中找到Maven Build,右键点击new,照下图操作执行 mybatis-generator:generate 命令,完成自动创建。
Mybatis参数传递
第一种:下标获取参数
UserDao.java
Public User selectUser(String name,String password);
UserMapper.xml
<select id="selectUser" resultMap="com.jk.model.User">
SELECT * FROM user WHERE name=#{0} AND password=#{1}
</select>
第二种:Map
UserDao.java
Public User selectUser(Map paramMap);
UserMapper.xml
<select id="selectUser" resultMap="com.jk.model.User">
SELECT * FROM user WHERE name=#{name,jdbcType=VARCHAR} AND password=#{password,jdbcType=VARCHAR}
</select>
第三种:@param注解
UserDao.java
Public User selectUser(@param("name")String name,@param("password")String password);
UserMapper.xml
<select id="selectUser" resultMap="com.jk.model.User">
SELECT * FROM user WHERE name=#{name,jdbcType=VARCHAR} AND password=#{password,jdbcType=VARCHAR}
</select>
第四种:对象
UserDao.java
Public User selectUser(User user);
UserMapper.xml
<select id="selectUser" parameterType="com.jk.model.User" resultMap="com.jk.model.User">
SELECT * FROM user WHERE name=#{user.name,jdbcType=VARCHAR}
AND password=#{user.password,jdbcType=VARCHAR}
</select>
第五种:数组和集合
请查阅本文批量删除
Mybatis模糊查询
第一种:CONCAT函数
#推荐
SELECT * FROM user WHERE name LIKE CONCAT(CONCAT('%',#{name}),'%')
#不推荐
SELECT * FROM user WHERE name LIKE CONCAT(CONCAT('%',${name}),'%')
第二种:%或||拼接
<!--推荐-->
SELECT * FROM user WHERE name LIKE "%"#{name}"%"
<!--推荐-->
SELECT * FROM user WHERE name LIKE "%"||#{name}||"%"
<!--不推荐-->
SELECT * FROM user WHERE name LIKE '%${name}%'
第三种:<bind>标签
<select id="selectUser" resultType="com.jk.model.User" parameterType="String">
<bind name="pattern" value="'%' + name + '%'" />
SELECT * FROM user WHERE name LIKE #{pattern}
</select>
第四种:当成参数传递
Mybatis最新版使用%或||会失效,建议使用CONCAT函数或当参数传;如下所示:
UserServiceImpl
String name = "%"+"张"+"%";
UserDao
Public User selectUser(name);
UserMapper
SELECT * FROM user WHERE name LIKE #{name}
Mybatis相关知识点
第一章:#{}和${}区别
解析前
SELECT * FROM user WHERE name = ${name};
SELECT * FROM user WHERE name = #{name};
解析后
<!-- ${} 动态解析过程中,只是简单的字符串替换 -->
SELECT * FROM user WHERE name = 'zhangsan';
<!-- #{} 在 DBMS 预处理时,会把参数部分用一个占位符 ? 代替 -->
SELECT * FROM user WHERE name = ?;
假如
String tableName = "user; delete user; --";
SELECT * FROM ${tableName} where name = #{name}
结果
<!-- 后果很严重,无法防止SQL注入,避免使用 ${} -->
SELECT * FROM user; delete user; -- WHERE name = 'zhangsan';
第二章:Mybatis特殊字符处理
<!-- 第一种 -->
<select id="selectUserByAge" resultType="com.jk.model.User" parameterType="String">
SELECT * FROM user WHERE 1=1 <![CDATA[ AND age > #{user.age} ]]>
</select>
<!-- 第二种 -->
<select id="selectUserByAge" resultType="com.jk.model.User" parameterType="String">
SELECT * FROM user WHERE 1=1 AND age > #{user.age}
</select>
特殊字符 | 替代符号 |
---|---|
& | & |
< | < |
> | > |
" | " |
’ | ' |
MybatisCRUD
第一章:新增 和 批量新增返回主键Id
关联: UUID总结
单对象新增
<!-- 实体类主键 必须和 keyProperty 一致才能正确返回主键Id,
传过来的实体类对象当中自会拥有返回的Id值;无需接收,也不能接收,接收会发生日常 -->
<insert id="saveUser" parameterType="com.jk.model.User" useGeneratedKeys="true" keyProperty="id">
INSERT INTO user VALUES(null,#{user.name},#{user.password})
<selectKey resultType="int" order="AFTER" keyProperty="id">
SELECT LAST_INSERT_ID() AS id
</selectKey>
</insert>
批量新增
<!-- 实体类主键 必须和 keyProperty 一致才能正确返回主键Id,
传过来的List集合当中自会拥有返回的Id值;无需接收,也不能接收,接收会发生日常 -->
<insert id="saveUsers" parameterType="list" useGeneratedKeys="true" keyProperty="id">
INSERT INTO user VALUES
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id},#{item.name},#{item.password})
</foreach>
</insert>
注解:如果无法正常返回主键请留言,请发邮件 xiwelu@qq.com
第二章:删除 ,批量删除
<!-- 支持批量需要使数据库开启 allowMultiQueries ,支持批量设置 -->
jdbc:mysql://localhost:3306/mysqlTest?characterEncoding=utf-8&allowMultiQueries=true
UserServiceImpl
// 单个删除,返回int是删除条数,也可以选择不写
int deleteUser(String id);
// 第一种:批量删除,返回int是删除条数,也可以选择不写
int deleteUserByList(List<Integer> list);
// 第二种:批量删除,返回int是删除条数,也可以选择不写
int deleteUserByArray(String [] str);
// 第三种:批量删除,返回int是删除条数,也可以选择不写
// String ids=" ( 1,2,3,4,5)";
int deleteUserByString(String ids);
// 单个修改,返回int是修改条数,也可以选择不写
int updateUser(User user);
// 批量修改,返回int是修改条数,也可以选择不写
int updateUsers(List<User> users);
UserMapper
<!-- 单个删除 parameterType可以直接写string,也可以不写此属性-->
<delete id="deleteUser" parameterType="java.lang.string">
DELETE FROM user WHERE id =#{id}
</delete>
<!-- 第一种:批量删除:parameterType可以不写此属性-->
<delete id="deleteUserByList" parameterType="java.util.List">
DELETE FROM user WHERE id IN
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
<!-- 第二种:批量删除,不能写parameterType属性;collection属性必须是"array",而不是str-->
<delete id="deleteUserByArray" >
DELETE FROM user WHERE id IN
<foreach collection="array" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
<!-- 第三种:批量删除:parameterType可以不写此属性-->
<delete id="deleteUserByString" parameterType="String">
DELETE FROM user WHERE id IN #{ids}
</delete>
<!-- 第四种:批量删除:parameterType可以不写此属性,效率低下,不推荐-->
<delete id="deleteUserByList2" parameterType="List">
<foreach collection="list" item="item">
DELETE FROM user WHERE id = #{item}
</foreach>
</delete>
第三章:新增返回主键ID
# keyProperty = id,会将值绑定到User对象的id属性上,然后再使用 #{id}从对象中取值
# order=BEFORE,先执行selectKey语句
<insert id="save" parameterType="User">
<selectKey keyProperty="id" resultType="string" order="BEFORE">
select replace(uuid(), '-', '') as id from dual
</selectKey>
insert into t_user(id, user_sex) values( #{id}, #{user_sex} )
</insert>
// before = true 等于 order = BEFORE
@Insert("insert into t_user(id, user_sex) values(#{id}, #{user_sex})")
@SelectKey(keyProperty = "id", resultType = String.class, before = true,
statement = "select replace(uuid(), '-', '') as id from dual")
public int save(User user);
属性 | 注释 |
---|---|
keyProperty | 设置需要自动生成键值的列 |
order | BEFORE / AFTER,BEFORE先执行selectKey语句,执行外层语句;AFTER先执行外层语句,再执行selectKey语句 |
resultType | 执行selectKey语句返回的类型 |
statementType | 支持STATEMENT(Statement)、PREPARED( PreparedStatement) 和CALLABLE(CallableStatement) 语句类型 |
欢迎网友提出宝贵意见,不断更新,使文章最全最完整;
参考:MyBatis生成UUID.
第四章:修改返回全部数据
@RestController
@RequestMapping(path = "/test/")
public class TestController {
@Resource
private TestMapper testMapper;
@RequestMapping(value = "/v1/insertObj", method = RequestMethod.GET)
public SimpleResponseDto insertObj() {
TestModel testModel = new TestModel();
testMapper.updateReturnObj( testModel);
....
return ... ;
}
}
@Repository
public interface TestMapper{
@Insert("UPDATE test1 set value = value+1 where id = 1")
@SelectKey(keyProperty = "value,time",resultType = TestModel.class, before = false,
statement = "select value,time from test1 where id = 1")
void updateReturnObj(TestModel testModel);
}
@Data
public class TestModel {
private Integer id;
private Integer value;
private String time;
}
CREATE TABLE IF NOT EXISTS `test1` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`value` int(10) DEFAULT '1',
`time` varchar(256) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表'
INSERT INTO test1 (id,value, time )VALUES( 1,123, '2019-03-11');
总结:
- @SelectKey 标签 ,返回对象的 key 值,不是单纯的返回主键;
- before 英文意思是“之前” ,设置 true 是在 SQL 之前执行,false 是在 SQL 之后执行;
- 做项目的时候有这个需求,百度很久没有方法,自己琢磨出来的,灵感是来源于 新增返回主键;
Mybatis参考博客
参考: 使用Mybatis-Generator自动生成Dao、Model、Mapping相关文件.
参考: Maven插件-mybatis-generator(mybatis自动生成实体代码的插件).
参考: Mybatis 自动生成代码工具(maven方式).
参考: @Param 注解在Mybatis中的使用 以及传递参数的三种方式.
参考: 对象传递参数.
参考: 关于mybatis中llike模糊查询中#和$的使用.
参考: Mabatis中#{}和${}的区别.
参考: MyBatis 返回(批量)新增数据的自增id.
参考: mybatis动态SQL–传入参数为集合,数组类型.
参考: mybatis特殊字符处理.