项目结构
依赖
<!-- mybatis-spring-starter-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.2</version>
</dependency>
<!-- jdbc starter-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>2.2.4.RELEASE</version>
</dependency>
<!-- mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
<!-- mybatis逆向工程插件-->
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.4.0</version>
</dependency>
配置文件
<?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>
<context id="DB2Tables" targetRuntime="MyBatis3">
<commentGenerator>
<!-- 是否去除自动生成的注释 -->
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!-- Mysql数据库连接的信息:驱动类、连接地址、用户名、密码 -->
<jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
connectionURL="jdbc:mysql://ip:3306/数据库"
userId="root"
password="1234qwe">
</jdbcConnection>
<!-- targetProject:生成POJO类的位置 -->
<javaModelGenerator targetPackage="com.xin.demo.pojo" targetProject="./src/main/java">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false" />
<!-- 从数据库返回的值被清理前后的空格 -->
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!-- targetProject:mapper映射文件生成的位置 -->
<sqlMapGenerator targetPackage="mybatis-config.mapper-sql" targetProject="./src/main/resources">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false" />
</sqlMapGenerator>
<!-- targetProject:mapper接口生成的的位置 -->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.xin.demo.mapper" targetProject="./src/main/java">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false" />
</javaClientGenerator>
<!-- 指定数据表 -->
<table schema="" tableName="tb_column"></table>
</context>
</generatorConfiguration>
代码生成类
package com.xin.demo;
import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
/**
* @author XiaoXin
* @date 2020/3/25 上午10:39
*/
public class GenerateSqlMapper {
public void generator() throws Exception {
List<String> warnings = new ArrayList<String>();
boolean overwrite = true;
// 指定配置文件
File configFile = new File("src/main/resources/mybatis-config/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);
}
// 执行main方法以生成代码
public static void main(String[] args) {
try {
GenerateSqlMapper generatorSqlmap = new GenerateSqlMapper();
generatorSqlmap.generator();
} catch (Exception e) {
e.printStackTrace();
}
}
}
数据库表与生成的类和接口
主要接口
查询
List<TbColumn> selectByExampleWithBLOBs(TbColumnExample example);
List<TbColumn> selectByExample(TbColumnExample example);
TbColumn selectByPrimaryKey(Integer id);
selectByExampleWithBLOBs
根据条件查询,包含大文本对象.当条件为空,就是查询全部.
比如,想查询专栏名称等于"redis学习"的数据时,加上criteria.andColumnnameEqualTo("redis学习"
,不加时就是查询全部
//测试electByExampleWithBLOBs
@Test
public void selectByExampleWithBLOBs() throws SQLException {
TbColumnExample example = new TbColumnExample();
TbColumnExample.Criteria criteria = example.createCriteria();
// criteria.andColumnnameEqualTo("redis学习");//查询条件
List<TbColumn> tbColumns = tbColumnMapper.selectByExampleWithBLOBs(example);
System.out.println(tbColumns.size());
for(TbColumn column: tbColumns){
System.out.println(column);
}
}
另外,对于pojo的每一个属性(或者说数据库的相应字段),mybatis自动生成了许多查询条件,比如:
- 等于
- between 与in
- 大于小于(及包含边界值)
- 空与非空
- 模糊等
selectByExample
根据条件查询,不包含大文本对象.当条件为空,就是查询全部.用法同上
//测试selectByExample
@Test
public void testselectByExample() throws SQLException {
TbColumnExample example = new TbColumnExample();
TbColumnExample.Criteria criteria = example.createCriteria();
List<TbColumn> tbColumns = tbColumnMapper.selectByExample(example);
System.out.println(tbColumns.size());
}
插入
int insert(TbColumn record);
int insertSelective(TbColumn record);
二者的区别就在于,当传入的参数record对象的属性值有空值时,insert就是直接插入该空值,而insertSelective则不会将该空值属性加入待插入列,即该空值属性的字段为数据库表的默认值.sql映射xml部分代码如下:
<insert id="insert" parameterType="com.xin.demo.pojo.TbColumn">
insert into tb_column (id, columnName, columnImg,
isfree, visiable, bognum,
subscribeNum, price, lastReleaseTime,
updateFrency, account, columnInfo
)
values (#{id,jdbcType=INTEGER}, #{columnname,jdbcType=VARCHAR}, #{columnimg,jdbcType=VARCHAR},
#{isfree,jdbcType=CHAR}, #{visiable,jdbcType=INTEGER}, #{bognum,jdbcType=INTEGER},
#{subscribenum,jdbcType=INTEGER}, #{price,jdbcType=REAL}, #{lastreleasetime,jdbcType=TIMESTAMP},
#{updatefrency,jdbcType=INTEGER}, #{account,jdbcType=INTEGER}, #{columninfo,jdbcType=LONGVARCHAR}
)
</insert>
<insert id="insertSelective" parameterType="com.xin.demo.pojo.TbColumn">
insert into tb_column
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="columnname != null">
columnName,
</if>
....略
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=INTEGER},
</if>
<if test="columnname != null">
#{columnname,jdbcType=VARCHAR},
</if>
....略
</trim>
</insert>
删除
同上,一个是按主键删,一个是按条件删
int deleteByExample(TbColumnExample example);
int deleteByPrimaryKey(Integer id);
更新
int updateByExampleSelective(@Param("record") TbColumn record, @Param("example") TbColumnExample example);
int updateByExampleWithBLOBs(@Param("record") TbColumn record, @Param("example") TbColumnExample example);
int updateByExample(@Param("record") TbColumn record, @Param("example") TbColumnExample example);
int updateByPrimaryKeySelective(TbColumn record);
int updateByPrimaryKeyWithBLOBs(TbColumn record);
int updateByPrimaryKey(TbColumn record);
updateByPrimaryKey
按主键更新,不包含大文本对象,若传入的record对象主键不存在,不更新.
//测试updateByPrimaryKey
@Test
public void updateByPrimaryKey() throws SQLException {
TbColumn record = new TbColumn();
record.setColumnname("redis学习");
record.setColumninfo("记录redis.....持续更新中");
tbColumnMapper.updateByPrimaryKey(record);
}
updateByPrimaryKeySelective
与updateByPrimaryKey相同,都是主键更新,包含大文本对象,区别就在于是否把空值属性加入到待修改列.
updateByPrimaryKeyWithBLOBs
与updateByPrimaryKey相同,都是主键更新,但包含大文本对象
updateByExample
按条件更新,不包含大文本对象
比如,将价格大于3的专栏订阅数量修改为999....等.
修改前;
运行
//测试updateByExample
//测试updateByExample
@Test
public void updateByExample() throws SQLException {
TbColumn record1 = new TbColumn();
record1.setId(10);
record1.setColumninfo("订阅数修,hhahhhhhhhh");
record1.setColumnname("订阅数修改专栏");
record1.setSubscribenum(999);
TbColumnExample example = new TbColumnExample();
TbColumnExample.Criteria criteria = example.createCriteria();
criteria.andPriceGreaterThan(3f);
tbColumnMapper.updateByExample(record1,example);
}
报错了:主键重复了
由于我数据库的id自增非空,因此,此时无论record的id无论是为空还是不为空,都会报错.因为要查出来的数据有一堆,而传入的对象只有一个,且主键一样.因此,想实现该功能,这个方法好像不行.当然,如果满足条件的记录只有一条,那么修改是可以成功的.那有没有忽略主键进行批量更新的呢? 有的,马上就到了
updateByExampleWithBLOBs
按条件更新,但包含大文本对象
updateByExampleSelective
按条件更新,包含大文本对象,若属性值不为空,才加入到更新列
继续之前的修改订阅数功能.代码改变如下:
//测试updateByExampleSelective
@Test
public void updateByExampleSelective() throws SQLException {
TbColumn record1 = new TbColumn();
record1.setColumninfo("订阅数修,hhahhhhhhhh");
record1.setColumnname("订阅数修改专栏");
record1.setSubscribenum(999);
TbColumnExample example = new TbColumnExample();
TbColumnExample.Criteria criteria = example.createCriteria();
criteria.andPriceGreaterThan(3f);
tbColumnMapper.updateByExampleSelective(record1,example);
}
结果:
总结
- 反是***selective的接口,属性值不为空则加入到更新列或插入列,包含大文本对象.
- 凡是***WithBLOBs的接口,都包含大文本对象
- 自动生成,没有查询特定字段的方法,都是全字段,但可能没有大文本字段列,当然,也可以自己写