经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库/运维 » MyBatis » 查看文章
放弃老旧的Mybatis,强类型替换字符串,这是一款你不应该错过的ORM
来源：cnblogs　　作者：薛家明　　时间：2023/10/30 9:15:43　　对本文有异议

一款轻量级、高性能、强类型、易扩展符合C#开发者的JAVA自研ORM

github地址 easy-query https://github.com/xuejmnet/easy-query

gitee地址 easy-query https://gitee.com/xuejm/easy-query

背景

转java后的几年时间里面一直在寻找一个类.net的orm,不需要很特别的功能仅希望90%的场景都可以通过强类型语法来编写符合直觉的sql,来操作数据库编写业务,但是一直没有找到仅Mybatis-Plus的单表让我在最初的时间段内看到了希望，不过随着使用的深入越发的发现Mybatis-Plus只是一个残缺的orm,因为大部分场景不支持表达式或者强类型会导致它本身的很多特性都无法使用,比如你配置了软删除,那么如果你遇到了join不好意思软删除你需要自己处理,很多配置会随着手写sql的加入变的那么的不智能,甚至表现得和sqlhelper没区别,别说Mybatis-Plus-Join了,这玩意更逆天,如果一个orm想写出符合自己的sql需要不断地调试尝试来“拼接”出想要的语句那么他就称不上一个ORM连sqlbuilder也算不上Mybatis-Plus-Join就是这样.

所以在4-5年后我终于忍受不了了,决定自研一款orm,参考现有.net生态十分完整的orm代码,和几乎完美符合扩展性和语义性的链式表达式让.net的orm带到java中。

查询

查询第一条数据

  1. Topic topic = easyQuery.queryable(Topic.class)
  2.                     .where(o -> o.eq(Topic::getId, "123"))
  3.                     .firstOrNull();
  5. ==> Preparing: SELECT `id`,`stars`,`title`,`create_time` FROM `t_topic` WHERE `id` = ? LIMIT 1
  6. ==> Parameters: 123(String)
  7. <== Time Elapsed: 2(ms)
  8. <== Total: 0

查询并断言至多一条数据

  1. Topic topic = easyQuery.queryable(Topic.class)
  2.                     .where(o -> o.eq(Topic::getId, "123"))
  3.                     .singleOrNull();
  5. ==> Preparing: SELECT `id`,`stars`,`title`,`create_time` FROM `t_topic` WHERE `id` = ?
  6. ==> Parameters: 123(String)
  7. <== Time Elapsed: 2(ms)
  8. <== Total: 0

查询多条数据

  1. List<Topic> topics = easyQuery.queryable(Topic.class)
  2.                     .where(o -> o.eq(Topic::getId, "123"))
  3.                     .toList();
  5. ==> Preparing: SELECT `id`,`stars`,`title`,`create_time` FROM `t_topic` WHERE `id` = ? LIMIT 1
  6. ==> Parameters: 123(String)
  7. <== Time Elapsed: 2(ms)
  8. <== Total: 0

查询自定义列

  1. Topic topic = easyQuery.queryable(Topic.class)
  2.                     .where(o -> o.eq(Topic::getId, "1"))
  3.                     .select(o->o.column(Topic::getId).column(Topic::getTitle))
  4.                     .firstOrNull();
  6. ==> Preparing: SELECT `id`,`title` FROM `t_topic` WHERE `id` = ? LIMIT 1
  7. ==> Parameters: 1(String)
  8. <== Time Elapsed: 2(ms)
  9. <== Total: 1

分页查询

  1.  EasyPageResult<Topic> topicPageResult = easyQuery
  2.                 .queryable(Topic.class)
  3.                 .where(o -> o.isNotNull(Topic::getId))
  4.                 .toPageResult(1, 20);
  6. ==> Preparing: SELECT  COUNT(1)  FROM t_topic t WHERE t.`id` IS NOT NULL
  7. <== Total: 1
  8. ==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM t_topic t WHERE t.`id` IS NOT NULL LIMIT 20
  9. <== Total: 20

将表达式转成匿名表嵌套查询

  1. //  SELECT `id`,`title` FROM `t_topic` WHERE `id` = ? 
  2. Queryable<Topic> query = easyQuery.queryable(Topic.class)
  3.                     .where(o -> o.eq(Topic::getId, "1"))
  4.                     .select(Topic.class, o -> o.column(Topic::getId).column(Topic::getTitle));
  6. List<Topic> list = query.leftJoin(Topic.class, (t, t1) -> t.eq(t1, Topic::getId, Topic::getId))
  7.                     .where((t, t1) -> {
  8.                         t1.eq(Topic::getId, "123");
  9.                         t.eq(Topic::getId, "456");
  10.                     }).toList();
  12. SELECT t1.`id`,t1.`title` 
  13. FROM (SELECT t.`id`,t.`title` FROM `t_topic` t WHERE t.`id` = ?) t1 
  14. LEFT JOIN `t_topic` t2 ON t1.`id` = t2.`id` WHERE t2.`id` 
  16. ==> Preparing: SELECT t1.`id`,t1.`title` FROM (SELECT t.`id`,t.`title` FROM `t_topic` t WHERE t.`id` = ?) t1 LEFT JOIN `t_topic` t2 ON t1.`id` = t2.`id` WHERE t2.`id` = ? AND t1.`id` = ?
  17. ==> Parameters: 1(String),123(String),456(String)
  18. <== Time Elapsed: 5(ms)
  19. <== Total: 0

子查询

  1. //SELECT * FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ?
  2.  Queryable<BlogEntity> subQueryable = easyQuery.queryable(BlogEntity.class)
  3.                 .where(o -> o.eq(BlogEntity::getId, "1"));
  6. List<Topic> x = easyQuery
  7.         .queryable(Topic.class).where(o -> o.exists(subQueryable.where(q -> q.eq(o, BlogEntity::getId, Topic::getId)))).toList();
  10. ==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic` t WHERE EXISTS (SELECT 1 FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ? AND t1.`id` = t.`id`)
  11. ==> Parameters: false(Boolean),1(String)
  12. <== Time Elapsed: 3(ms)
  13. <== Total: 1

多表join查询

  1. Topic topic = easyQuery
  2.                 .queryable(Topic.class)
  3.                 .leftJoin(BlogEntity.class, (t, t1) -> t.eq(t1, Topic::getId, BlogEntity::getId))
  4.                 .where(o -> o.eq(Topic::getId, "3"))
  5.                 .firstOrNull();
  7. ==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM t_topic t LEFT JOIN t_blog t1 ON t1.`deleted` = ? AND t.`id` = t1.`id` WHERE t.`id` = ? LIMIT 1
  8. ==> Parameters: false(Boolean),3(String)
  9. <== Total: 1

流式结果大数据迭代返回

  1. try(JdbcStreamResult<BlogEntity> streamResult = easyQuery.queryable(BlogEntity.class).where(o -> o.le(BlogEntity::getStar, 100)).orderByAsc(o -> o.column(BlogEntity::getCreateTime)).toStreamResult()){
  3.             LocalDateTime begin = LocalDateTime.of(2020, 1, 1, 1, 1, 1);
  4.             int i = 0;
  5.             for (BlogEntity blog : streamResult.getStreamIterable()) {
  6.                 String indexStr = String.valueOf(i);
  7.                 Assert.assertEquals(indexStr, blog.getId());
  8.                 Assert.assertEquals(indexStr, blog.getCreateBy());
  9.                 Assert.assertEquals(begin.plusDays(i), blog.getCreateTime());
  10.                 Assert.assertEquals(indexStr, blog.getUpdateBy());
  11.                 Assert.assertEquals(begin.plusDays(i), blog.getUpdateTime());
  12.                 Assert.assertEquals("title" + indexStr, blog.getTitle());
  13. //            Assert.assertEquals("content" + indexStr, blog.getContent());
  14.                 Assert.assertEquals("http://blog.easy-query.com/" + indexStr, blog.getUrl());
  15.                 Assert.assertEquals(i, (int) blog.getStar());
  16.                 Assert.assertEquals(0, new BigDecimal("1.2").compareTo(blog.getScore()));
  17.                 Assert.assertEquals(i % 3 == 0 ? 0 : 1, (int) blog.getStatus());
  18.                 Assert.assertEquals(0, new BigDecimal("1.2").multiply(BigDecimal.valueOf(i)).compareTo(blog.getOrder()));
  19.                 Assert.assertEquals(i % 2 == 0, blog.getIsTop());
  20.                 Assert.assertEquals(i % 2 == 0, blog.getTop());
  21.                 Assert.assertEquals(false, blog.getDeleted());
  22.                 i++;
  23.             }
  24.         } catch (SQLException e) {
  25.             throw new RuntimeException(e);
  26.         }
  28. ==> Preparing: SELECT `id`,`create_time`,`update_time`,`create_by`,`update_by`,`deleted`,`title`,`content`,`url`,`star`,`publish_time`,`score`,`status`,`order`,`is_top`,`top` FROM `t_blog` WHERE `deleted` = ? AND `star` <= ? ORDER BY `create_time` ASC
  29. ==> Parameters: false(Boolean),100(Integer)
  30. <== Time Elapsed: 6(ms)

自定义VO返回

  1. List<QueryVO> list = easyQuery
  2.                 .queryable(Topic.class)
  3.                 //第一个join采用双参数,参数1表示第一张表Topic 参数2表示第二张表 BlogEntity
  4.                 .leftJoin(BlogEntity.class, (t, t1) -> t.eq(t1, Topic::getId, BlogEntity::getId))
  5.                 //第二个join采用三参数,参数1表示第一张表Topic 参数2表示第二张表 BlogEntity 第三个参数表示第三张表 SysUser
  6.                 .leftJoin(SysUser.class, (t, t1, t2) -> t.eq(t2, Topic::getId, SysUser::getId))
  7.                 .where(o -> o.eq(Topic::getId, "123"))//单个条件where参数为主表Topic
  8.                 //支持单个参数或者全参数,全参数个数为主表+join表个数 链式写法期间可以通过then来切换操作表
  9.                 .where((t, t1, t2) -> t.eq(Topic::getId, "123").then(t1).like(BlogEntity::getTitle, "456")
  10.                         .then(t2).eq(BaseEntity::getCreateTime, LocalDateTime.now()))
  11.                 //如果不想用链式的then来切换也可以通过lambda 大括号方式执行顺序就是代码顺序,默认采用and链接
  12.                 .where((t, t1, t2) -> {
  13.                     t.eq(Topic::getId, "123");
  14.                     t1.like(BlogEntity::getTitle, "456");
  15.                     t1.eq(BaseEntity::getCreateTime, LocalDateTime.now());
  16.                 })
  17.                 .select(QueryVO.class, (t, t1, t2) ->
  18.                         //将第一张表的所有属性的列映射到vo的列名上,第一张表也可以通过columnAll将全部字段映射上去
  19.                         // ,如果后续可以通过ignore方法来取消掉之前的映射关系
  20.                         t.column(Topic::getId)
  21.                                 .then(t1)
  22.                                 //将第二张表的title字段映射到VO的field1字段上
  23.                                 .columnAs(BlogEntity::getTitle, QueryVO::getField1)
  24.                                 .then(t2)
  25.                                 //将第三张表的id字段映射到VO的field2字段上
  26.                                 .columnAs(SysUser::getId, QueryVO::getField2)
  27.                 ).toList();

表单条件动态查询

  1. BlogQuery2Request query = new BlogQuery2Request();
  2. query.setContent("标题");
  3. query.setPublishTimeEnd(LocalDateTime.now());
  4. query.setStatusList(Arrays.asList(1,2));
  6. List<BlogEntity> queryable = easyQuery.queryable(BlogEntity.class)
  7.         .whereObject(query).toList();
  10. ==> Preparing: SELECT `id`,`create_time`,`update_time`,`create_by`,`update_by`,`deleted`,`title`,`content`,`url`,`star`,`publish_time`,`score`,`status`,`order`,`is_top`,`top` FROM `t_blog` WHERE `deleted` = ? AND `content` LIKE ? AND `publish_time` <= ? AND `status` IN (?,?)
  11. ==> Parameters: false(Boolean),%标题%(String),2023-07-14T22:37:47.880(LocalDateTime),1(Integer),2(Integer)
  12. <== Time Elapsed: 2(ms)
  13. <== Total: 0

基本类型结果返回

  1. List<String> list = easyQuery.queryable(Topic.class)
  2.                 .where(o -> o.eq(Topic::getId, "1"))
  3.                 .select(String.class, o -> o.column(Topic::getId))
  4.                 .toList();
  6. ==> Preparing: SELECT t.`id` FROM `t_topic` t WHERE t.`id` = ?
  7. ==> Parameters: 1(String)
  8. <== Time Elapsed: 2(ms)
  9. <== Total: 1

分组查询

  2. List<TopicGroupTestDTO> topicGroupTestDTOS = easyQuery.queryable(Topic.class)
  3.                 .where(o -> o.eq(Topic::getId, "3"))
  4.                 .groupBy(o->o.column(Topic::getId))
  5.                 .select(TopicGroupTestDTO.class, o->o.columnAs(Topic::getId,TopicGroupTestDTO::getId).columnCount(Topic::getId,TopicGroupTestDTO::getIdCount))
  6.                 .toList();
  9. ==> Preparing: SELECT t.`id` AS `id`,COUNT(t.`id`) AS `idCount` FROM t_topic t WHERE t.`id` = ? GROUP BY t.`id`
  10. ==> Parameters: 3(String)
  11. <== Total: 1
  13. //groupKeysAs快速选择并且给别名
  14. List<TopicGroupTestDTO> topicGroupTestDTOS = easyQuery.queryable(Topic.class)
  15.                 .where(o -> o.eq(Topic::getId, "3"))
  16.                 .groupBy(o->o.column(Topic::getId))
  17.                 .select(TopicGroupTestDTO.class, o->o.groupKeysAs(0, TopicGroupTestDTO::getId).columnCount(Topic::getId,TopicGroupTestDTO::getIdCount))
  18.                 .toList();
  21. ==> Preparing: SELECT t.`id` AS `id`,COUNT(t.`id`) AS `idCount` FROM t_topic t WHERE t.`id` = ? GROUP BY t.`id`
  22. ==> Parameters: 3(String)
  23. <== Total: 1

原生sql片段

  1. String sql = easyQuery.queryable(H2BookTest.class)
  2.             .where(o -> o.sqlNativeSegment("regexp_like({0},{1})", it -> it.expression(H2BookTest::getPrice)
  3.                             .value("^Ste(v|ph)en$")))
  4.             .select(o -> o.columnAll()).toSQL();
  6. SELECT id,name,edition,price,store_id FROM t_book_test WHERE regexp_like(price,?)

数据库函数列

用户存储的数据是base64结果,但是内存中是普通的字符串或者其他数据,easy-query提供了无感的使用，譬如pgsql的geo等地理相关数据
数据库函数列 https://xuejm.gitee.io/easy-query-doc/guide/adv/column-sql-func-auto.html

支持like的高性能加密解密

用来实现支持like模式的高性能加密解密,支持emoji和非emoji两种用户可以自行选择
数据库加密解密 https://xuejm.gitee.io/easy-query-doc/guide/adv/column-encryption.html

更多功能比如数据追踪差异更新,数据原子更新,分库分表(老行当了肯定要支持),一款本无依赖双语(java/kotlin)都支持的高性能orm

原文链接：https://www.cnblogs.com/xuejiaming/p/17794480.html

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

MyBatis热门文章

  • Mybatis使用连表查询的操作代码
  • Mybatis-plus如何查询表中指定字段(不查询全部字段)
  • mybatis/mybatis-plus模糊查询语句特殊字符转义拦截器的实现
  • MyBatis注解开发-@Insert和@InsertProvider的使用
  • springboot+mybatis报错找不到实体类的问题
  • mybatis 忽略实体对象的某个属性(2种方式)
  • mybatis update set 多个字段实例
  • 如何通过一个注解实现MyBatis字段加解密
  • oracle+mybatis-plus+springboot实现分页查询的实例
  • MybatisPlus中的insert操作详解

    • MyBatis推荐文章

  • Mybatis-Plus最优化持久层开发
  • MyBatis 的在使用上的注意事项及其辨析 - Rainbow-Sea
  • 在 Wed 中应用 MyBatis（同时使用MVC架构模式，以及ThreadLocal 事务控制） - Rainbow-Sea
  • MyBatis源码之前言—JDBC编码存在的问题和Mybatis的介绍
  • mybatis plugin源码解析
  • Spring Boot + MyBatis-Plus 实现 MySQL 主从复制动态数据源切换
  • 一种轻量分表方案-MyBatis拦截器分表实践
  • 使用Mybatis自定义插件实现不侵入业务的公共参数自动追加
  • MyBatis-Plus雪花算法实现源码解析
  • Mybatis和其他主流框架的整合使用

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号