准备系列-Mybatis(六) TKmybatis-springboot 项目的多种查询方式实现

贾子杰
290 阅读3分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 6 天，点击查看活动详情

在之前的几篇中，我们已经介绍了项目配置及项目启动，Druid，tkMybatis的配置信息，今天讲一下具体项目中，本文我们介绍几种查询方式的实现,方便在具体的项目中进行实战操作

配置及数据准备

1.增加pom配置工具类

增加一个 java的工具类 hutool，方便我们使用一些集成工具

<dependency>
   <groupId>cn.hutool</groupId>
   <artifactId>hutool-all</artifactId>
   <version>5.3.5</version>
</dependency>
准备mysql数据
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (1, '11', 'aa', 1, '北京', '[1, 2, 3]', '{\"deptId\": 1, \"deptName\": \"部门1\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (2, '22', 'bb', 2, '上海', '[4, 5, 6]', '{\"deptId\": 1, \"deptName\": \"部门1\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (3, '33', 'cc', 31, '广州', '[7, 5, 6]', '{\"deptId\": 2, \"deptName\": \"部门2\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (4, '44', 'dd', 14, '深圳', '[9, 5, 6]', '{\"deptId\": 2, \"deptName\": \"部门2\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (5, '55', 'ee', 25, '上海', '[8, 5, 6]', '{\"deptId\": 2, \"deptName\": \"部门2\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (6, '66', 'ff', 34, '西安', '[10, 5, 6]', '{\"deptId\": 2, \"deptName\": \"部门2\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (7, '77', 'gg', 41, '郑州', '[8, 5, 6]', '{\"deptId\": 2, \"deptName\": \"部门2\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (8, '88', 'hh', 4, '武汉', '[8, 5, 6]', '{\"deptId\": 2, \"deptName\": \"部门2\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (9, '99', 'ii', 14, '武汉', '[8, 5, 6]', '{\"deptId\": 2, \"deptName\": \"部门2\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (10, '10', 'jj', 26, '武汉', '[18, 5, 6]', '{\"deptId\": 3, \"deptName\": \"部门3\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (11, '126', 'kk', 49, '深圳', '[18, 15, 6]', '{\"deptId\": 3, \"deptName\": \"部门3\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (12, '5896', 'll', 14, '北京', '[28, 35, 36]', '{\"deptId\": 4, \"deptName\": \"部门4\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (13, '456', 'mm', 14, '上海', '[28, 25, 26]', '{\"deptId\": 4, \"deptName\": \"部门4\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
INSERT INTO `test`.`user_info` (`id`, `user_id`, `user_name`, `age`, `address`, `order_ids`, `goods`, `sort_order`, `is_del`,`is_del2`, `addtime`, `modtime`) VALUES (14, '456', 'nn', 34, '上海', '[18, 15, 16]', '{\"deptId\": 4, \"deptName\": \"部门4\", \"deptLeaderId\": 4}', 0, 0, 0, 0, 0);
2.查询请求

2.查询请求

下面我们来看一下有几种查询可以满足我们的查询请求

2.1 @Select 注解的方式

UserInfoMaper文件中 注解实现 找年龄 >30的 员工

@Select("select * from user_info where age > #{age}")
List<UserInfoPO> selectGreaterAge(@Param("age") Integer age);

curl 127.0.0.1:8800/temp/query1 查询结果

image.png

2.2 XML文件形式

UserInfoMaper文件中 定义接口

List<UserInfoPO> countUser(@Param("age") Integer age,@Param("names")List<String> names);

UserInfoMaper.xml 文件中写实现

<select id="countUser" resultType="com.jzj.tdmybatis.domain.po.UserCity">
  select address as city  , count(*) as count from user_info
  where user_name in
  <foreach close=")" collection="names" item="iterm" open="(" separator=",">
    #{iterm}
  </foreach>
  group by city
</select>

curl 127.0.0.1:8800/temp/query2 查询结果 正确分组 image.png

2.3 Example.builder 方式
@Override
public List<UserInfoPO> getUserBuilder(Integer age, List<String> names) {
    Example example = Example.builder(UserInfoPO.class)
            .where(
                    Sqls.custom()
                            .andGreaterThan("age", age)
                            .andIn("userName", names))
            .orderByDesc("age")
            .build();
    return mapper.selectByExample(example);
}

curl 127.0.0.1:8800/temp/query3 查询结果

image.png

2.4 Example Criteria动态拼接方式
@Override
public List<UserInfoPO> getUserExample(Integer age, List<String> names) {
    Example example = new Example(UserInfoPO.class);
    example.createCriteria().
            andGreaterThan("age", age)
            .andIn("userName", names);

    // 排序
    example.orderBy("age").desc();

    // 获得结果
    return mapper.selectByExample(example);
}

curl 127.0.0.1:8800/temp/query4 查询结果

image.png

2.5 Weekend的操作方式(java8流式处理)
@Override
public List<UserInfoPO> getUserWeekend(Integer age, List<String> names) {
    Weekend<UserInfoPO> weekend = Weekend.of(UserInfoPO.class);
    WeekendCriteria<UserInfoPO, Object> criteria = weekend.weekendCriteria();
    //java 8 :: 流式处理
    criteria.andGreaterThan(UserInfoPO::getAge, age);
    weekend.setOrderByClause("age desc");
    return mapper.selectByExample(weekend);
}

curl 127.0.0.1:8800/temp/query4 查询结果

image.png

2.7 selectOneByExample 和 selectByExample的区别
  • selectOneByExample 就是查询单个请求，如果数据库db中有两条重复的数据，就会报错
  • selectByExample 就是查询多个数据，根据criteria构造的条件进行多条件查询

3. 出错解决办法

Mapper文件找不到xml文件的 方法

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.jzj.tdmybatis.repository.mapper.UserInfoMapper.countUser] with root cause

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.jzj.tdmybatis.repository.mapper.UserInfoMapper.countUser

需要 配置Mybatis 的mapper 扫描

否则找不到 xml文件 报错

mybatis.mapper-locations=classpath:sqlmapper/*Mapper.xml
avatar
文章
阅读
粉丝