tkmybatis 插入 mysql geometry

转载

gulaotou 2024-07-07 13:36:54

文章标签 mybatis TkMybatis sql List 字段 文章分类 MySQL 数据库

1. 什么是TKMybatis

简单来说，类似mybatis-plus，就是对原有的mybatis进行封装，简化我们的SQL操作

与mybatis不同的是，我们不要要写对应的xml文件来进行SQL操作了，只需要在对应的service层去写TKMybatis封装好的代码即可

2. 怎么使用TKMybatis

引入依赖

<!--通用mapper起步依赖-->
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>2.0.4</version>
</dependency>

POJO加依赖

<!--每个工程都有Pojo，都需要用到该包对应的注解-->
<dependency>
    <groupId>javax.persistence</groupId>
    <artifactId>persistence-api</artifactId>
    <version>1.0</version>
    <scope>compile</scope>
</dependency>

在启动类中配置 @MapperScan 扫描

@SpringBootApplication
@MapperScan(basePackages = {"com.tom.order.mapper"})
public class OrderApplication {
    public static void main(String[] args) {
        SpringApplication.run(OrderApplication.class, args);
    }
}

crud（增删改查）方法

操作类型介绍

增加
Mapper.insert(record)；保存一个实体，null的属性也会保存，不会使用数据库默认值
Mapper.insertSelective(record)；保存一个实体，忽略空值，即没提交的值会使用使用数据库默认值
删除
Mapper.delete(record); 根据实体属性作为条件进行删除，查询条件使用等号
Mapper.deleteByExample(example) 根据Example条件删除数据
Mapper.deleteByPrimaryKey(key) 根据主键字段进行删除，方法参数必须包含完整的主键属性
修改
Mapper.updateByExample(record,example) 根据Example条件更新实体record包含的全部属性，null值会被更新
Mapper.updateByExampleSelective(record,example) 根据Example条件更新实体record包含的不是null的属性值
Mapper.updateByPrimaryKey(record) 根据主键更新实体全部字段，null值会被更新
Mapper.updateByPrimaryKeySelective(record) 根据主键更新属性不为null的值
查询
Mapper.select(record) 根据实体中的属性值进行查询，查询条件使用等号
Mapper.selectAll() 查询全部结果
Mapper.selectByExample(example) 根据Example条件进行查询
Mapper.selectByPrimaryKey(key) 根据主键字段进行查询，方法参数必须包含完整的主键属性，查询条件使用等号
Mapper.selectCount(record) 根据实体中的属性查询总数，查询条件使用等号
Mapper.selectCountByExample(example) 根据Example条件进行查询总数
Mapper.selectOne(record) 根据实体中的属性进行查询，只能有一个返回值，有多个结果是抛出异常，查询条件使用等号。
但是如果存在某个属性为int，则会初始化为0。可能影响到实际使用

例子：

@Autowired
    private BrandMapper brandMapper;
 
    public Example createExample(Brand brand) {
        // 自定义条件搜索对象 Example
        Example example = new Example(Brand.class);
        //条件构造器
        Example.Criteria criteria = example.createCriteria(); 
 
        if (brand != null) {
            if (!StringUtils.isEmpty(brand.getName())) {
                criteria.andLike("name", '%' + brand.getName() + '%');
            }
 
            if (!StringUtils.isEmpty(brand.getLetter())) {
                criteria.andEqualTo("letter", brand.getLetter());
            }
        }
        return example;
    }
    
    @Override
    public List<Brand> findAll() {
        return brandMapper.selectAll();
    }
 
    @Override
    public List<Brand> findList(Brand brand) {
        Example example = createExample(brand);
        return brandMapper.selectByExample(example);
    }
 
    @Override
    public Brand findById(Integer id) {
        return brandMapper.selectByPrimaryKey(id);
    }

例子：

这是service一小段代码，我们看看都简化一些操作

比如

tkmybatis 插入 mysql geometry_字段

然后我们也发现了核心的代码是哪里

tkmybatis 插入 mysql geometry_sql_02

是这里然后我们又发现有几行代码是我们还没看懂的

tkmybatis 插入 mysql geometry_List_03

这个是TKMybatis的另一个简化操作，简化我们的SQL的and和or

and/or方法

我们一般的sql写法是 where a=1 and b=1

select name from student where name=“zhangsan1” and age = 18;

复杂一点的写法 where (a=1 and b=2) or (b=1 and d=2)

select name from student where (name=“zhangsan1” and `code=16) or
(age = 18 and address = “beijing”);

分析
使用tkmapper一般为了快速实现逻辑，都使用的example，比如上面的一般sql写法转换为tkmapper的example写法

> Example e = new Example(Student.class);
>
> 
> Example.Criteria criteria = e.createCriteria();
> 
> criteria.andEqualTo("name", zhangsan1);
> 
> criteria.andEqualTo("age ", 18);

对于复杂的sql写法怎么转换？其实一般不建议还用example写复杂sql，后期不好维护，如果为了快速实现逻辑不注重规范的话，这里教一下怎么写

分析结果拆分：where (a=1 and b=2) or (b=1 and d=2)，这里是两个小的嵌套子查询

实现逻辑
总查询

Example e = new Example(Student.class);

Example.Criteria criteria = e.createCriteria();

criteria.andEqualTo("a", 1);

criteria.andEqualTo("b", 2);

但是我们是不推荐这么去书写的

总结一些常用的

and/or方法具体API

and方法

andEqualTo(“field”,value)
表示条件为实体类字段"field"等于value值

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andEqualTo("createUserId","1")
                .andEqualTo("isDelete",0);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

相当于

select * from tb_work_guide where( ( create_user_id = ? and is_delete
= ? ) )

where后为什么会多两层括号我也不知道…反正查询结果是对的,下面为了美观和方便,就手动把括号去掉了

另一种单参数写法: 参数为map

Map<String, String> param = new HashMap<>();
        param.put("createUserId","1");
        param.put("isDelete","0");
        
        Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andEqualTo(param);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

andAllEqualTo（param）
与andEqualTo的单参数形式一样,参数为map
andNotEqualTo(“field”,value)
与andEqualTo相反,条件为实体类字段"field"不等于value值,同时此方法没有单参数

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andNotEqualTo("createUserId","1");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id <> ?

andIn(“field”,list)
表示条件为实体类"field"字段的值包含ids里的值,与sql语句中的in()相同

List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
 
        Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andIn("createUserId",ids);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id in ( ? , ? )

list中不一定要是Integer类型,也可以是String

List<String> titles = new ArrayList<>();
        titles.add("标题1");
        titles.add("标题2");
 
        Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andIn("title",titles);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

举一反三:

与sql语句中的FIND_IN_SET也相同

需要注意的是,FIND_IN_SET(str,strList),这里的str为数据库中的字段名,如create_user_id,而不是实体类的createUserId

执行sql:

select * from tb_work_guide where FIND_IN_SET (create_user_id , ‘1,2’)

andNotIn(“field”,list)
与andIn()相反,查询"field"字段不包含list中的值的结果

执行sql:

select * from tb_work_guide where create_user_id not in ( ? , ? )

andIsNull(“field”)
表示实体类"field"字段为null

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andIsNull("createUserId");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id is null

andIsNotNull(“field”)
与andIsNull()相反,表示实体类"field"字段不为null

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andIsNotNull("createUserId");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id is not null

andBetween(“field”,value1,value2)
表示"field"字段的值在value1和value2之间,注意:这个区间是包头包尾的,1 <= field <= 7

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andBetween("createUserId",1,7);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id between ? and ?

andNotBetween(“field”,value1,value2)
与andBetween()相反,表示"field"字段的值不在value1和value2之间,注意:这个区间也是包头包尾的,field < 1 或 field >7

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andNotBetween("createUserId",1,7);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id not between ? and ?

andLike(“field”,str)
表示模糊查询,注意:需要自己拼接%或_

String str = "缴费";
 
Example example = new Example(WorkGuideModel.class);
example.createCriteria()
        .andLike("title","%"+str+"%");
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

执行sql:

select * from tb_work_guide where title like ?

andNotLike(“field”,str)
与andLike相反的查询,也需要自己拼接%或_

String str = "缴费";
 
        Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andNotLike("title","%"+str+"%");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where title not like ?

andGreaterThan(“field”,value)
表示查询"field"字段中大于value的值

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andGreaterThan("age",20);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where age > ?

andGreaterThanOrEqualTo(“field”,value)
与andGreaterThan()差不多,表示"field"字段中大于等于value的值

执行sql:

select * from tb_work_guide where age >= ?

andLessThan(“field”,value)
表示查询"field"字段中小于value的值

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andLessThan("age",20);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where age < ?

andLessThanOrEqualTo(“field”,value)
与andLessThan()差不多,表示"field"字段中小于等于value的值

执行sql:

select * from tb_work_guide where age <= ?

andCondition(condition)
在where后直接拼接条件,注意:此时拼接的field字段名是数据库的列名

写法1:

直接写完全where后面的条件语句,会自动将create_user_id = 1拼接到where后

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andCondition("create_user_id = 1");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id = 1

写法2:

andCondition(condition,value) ,condition写查询字段(注意要记得加上=,不然会报错),value传值,这样value值就可以写活了

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andCondition("create_user_id = ",1);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id = ?

andCondition()这个方法写的大佬都太少了,目前我只知道这是拼接作用的,就先这么写着,如果以后用到或者学习到,再回来补

or方法
我感觉or方法跟and方法其实差不多,只是and方法是在多条件中间拼接and,or方法是拼接or,所以下面就只举几个例子加深印象好了,主要还是and和or一同使用

orEqualTo(param)
Map<String, String> param = new HashMap<>();
        param.put("createUserId","1");
        param.put("isDelete","0");
 
        Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .orEqualTo(param);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id = ? or is_delete = ?

orIn("field",list)
List<String> titles = new ArrayList<>();
        titles.add("标题1");
        titles.add("标题2");
 
Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .orIn("title",titles);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where title in ( ? , ? )

从sql中可以看到,orIn和andIn的执行sql是一样的,其余方法就不测试了,大概是一样的,只有多个条件时,中间的连接符号不同,

如下:

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .orLike("title","%标题1%")
                .orLike("createUserName","%小明%");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where title like ? or create_user_name
like ?

List<String> titles = new ArrayList<>();
        titles.add("标题1");
        titles.add("标题2");
 
Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .orIn("title",titles)
                .orLike("createUserName","%小明%");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where title in ( ? , ? ) or
create_user_name like ?

and和or一同使用
a and ( b or c)

Example example = new Example(WorkGuideModel.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("userId",1);
        
        Example.Criteria criteria2 = example.createCriteria();
        criteria2.orLike("title","%标题%")
                .orBetween("age",1,5);
        example.and(criteria2);
 
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where ( ( user_id = ? ) and ( title like
? or age between ? and ? ) )

现在知道多出来的括号是干啥的了…

(a and b) or (c or d)

List<String> titles = new ArrayList<>();
titles.add("标题1");
titles.add("标题2");
 
Example example = new Example(WorkGuideModel.class);
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo("userId",1)
        .andIn("title",titles);
 
Example.Criteria criteria2 = example.createCriteria();
criteria2.orLike("name","%小明%")
        .orBetween("age",1,5);

example.or(criteria2);
 
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

执行sql:

select * from tb_work_guide where ( create_user_id = ? and title in (
? , ? ) ) or ( title like ? or open between ? and ? )

将代码中的example.or(criteria2);改成example.and(criteria2);后会发现sql变成了:

select * from tb_work_guide where ( create_user_id = ? and title in (
? , ? ) ) and ( title like ? or open between ? and ? )

例子

WHERE (
  user_name = ? 
  OR police_no = ? 
  OR real_name = ?
) 
AND (department_id = ?)

对应的TKMybatis

Example e = new Example(User.class);
        Example.Criteria c = e.createCriteria();

        //关键字查询部分
        String keyword = pageReq.getKeyword();
        if (StringUtils.isNotEmpty(keyword)) {
            c.orEqualTo("userName", keyword).orEqualTo("policeNo",keyword).orEqualTo("realName",keyword);
        }
        //部门查询部门
        Example.Criteria criteria = e.createCriteria();
        criteria.andEqualTo("departmentId", departmentId);
        e.and(criteria);

        PageHelper.startPage(pageReq.getPageIndex(), pageReq.getPageSize());
        List<User> users = userMapper.selectByExample(e);

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。