hyorm 框架进阶用法之框架的原生使用方法
作者：demo123567
- 2023-06-15 重庆
本文字数：8004 字
阅读完需：约 26 分钟
使用简介
使用方法：引入 maven
<dependency> <groupId>com.hyutils</groupId> <artifactId>core</artifactId> <version>1.0-SNAPSHOT</version></dependency>
复制代码
不带条件简单查询
simpleGet 方法
查询单个对象，如果有多个，只返回第一个
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); System.out.println(wordsQuery.find("value").find("id").find("thesaurus_code").size(1).simpleGet()); }
复制代码
生成的 sql 为：
SELECT value,id,thesaurus_code FROM material_thesaurus_words limit 1 ;
复制代码
listMapGet 方法
查询全部对象
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); System.out.println(wordsQuery.find("value").find("id").find("thesaurus_code").size(2).listMapGet()); }
复制代码
生成的 sql 为：
SELECT value,id,thesaurus_code FROM material_thesaurus_words limit 2 ;
复制代码
结果为：
[{value=xxxxxxxxx, id=585730307940941826, thesaurus_code=23}, {value=18&xxxxx, id=585730307940941827, thesaurus_code=23}]
复制代码
find 方法
指查询某个字段
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); System.out.println(wordsQuery.find("value").find("id").find("thesaurus_code").size(1).simpleGet()); }
复制代码
生成的 sql 为：
SELECT value,id,thesaurus_code FROM material_thesaurus_words limit 1 ;
复制代码
page 方法和 size 方法
多少页，页的大小为多少，page 方法需要和 size 一起使用，但是 size 方法可以单独使用
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); System.out.println(wordsQuery.find("value").find("id").find("thesaurus_code").page(1).size(1).simpleGet()); }
复制代码
生成的 sql 为
SELECT value,id,thesaurus_code FROM material_thesaurus_words offset 0 limit 1 ;
复制代码
orderBy 方法
按条件排序的方法
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); System.out.println(wordsQuery .find("value").find("id").find("thesaurus_code") .orderBy("thesaurus_code","desc").orderBy("value","desc") .page(1).size(2) .listMapGet()); }
复制代码
生成的 sql 为：
SELECT value,id,thesaurus_code FROM material_thesaurus_words order by thesaurus_code desc,value desc offset 0 limit 2 ;
复制代码
带条件查询
where 方法
该方法的入参是WhereSyntaxTree类，该类有两个继承类，分别是AndWhereSyntaxTree和OrWhereSyntaxTree，这两种不同的 where 树代表了两种不同的语义。
以下的内容均来源于上面两个语义类
andWheres 方法
AndWhereSyntaxTree 语义，即内部的是用 and 连接起来的操作
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); System.out.println(wordsQuery .find("value").find("id").find("thesaurus_code") .orderBy("thesaurus_code","desc").orderBy("value","desc") .page(1).size(2) .andWheres(new HashMap<String, Object>(){ { put("thesaurus_code","14"); put("value","demo123"); } }) .listMapGet()); }
复制代码
生成的 sql 为：
SELECT value,id,thesaurus_code FROM material_thesaurus_words WHERE value = :value AND thesaurus_code = :thesaurus_code order by thesaurus_code desc,value desc offset 0 limit 2 ;
复制代码
参数为：
{value=demo123, thesaurus_code=14}
复制代码
orwhere 方法
OrWhereSyntaxTree 语义，即内部使用 or 连接
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); System.out.println(wordsQuery .find("value").find("id").find("thesaurus_code") .orderBy("thesaurus_code","desc").orderBy("value","desc") .page(1).size(2) .orWheres(new HashMap<String, Object>(){ { put("thesaurus_code","14"); put("value","demo123"); } }) .listMapGet()); }
复制代码
生成的 sql 为：
SELECT value,id,thesaurus_code FROM material_thesaurus_words WHERE value = :value OR thesaurus_code = :thesaurus_code order by thesaurus_code desc,value desc offset 0 limit 2 ;
复制代码
多个相同的参数 or/and 起来
由于 hashmap 本身是去重的，导致传入的时候无法做一个没有去重的参数，所以这里引入了 andWheres 和 orWheres 的重载方法，传入一个三元组。除了在这里可用之外，三元组还有别的用法，这里先介绍重复参数的用法。
orwheres 传入三元组
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); System.out.println(wordsQuery .find("value").find("id").find("thesaurus_code") .page(1).size(2) .orWheres(new ArrayList<Triplet<String, String, Object>>(){ { add(new Triplet<>("thesaurus_code","=","99")); add(new Triplet<>("thesaurus_code","=","14")); } }) .listMapGet()); }
复制代码
生成的 sql 为
SELECT value,id,thesaurus_code FROM material_thesaurus_words WHERE thesaurus_code = :thesaurus_code OR thesaurus_code = :a231e89bab64bed3388a6f9d0745be11 offset 0 limit 2 ;
复制代码
参数为：
{thesaurus_code=99, 96000698b09c6b1afa517dd677fb90f5=14}
复制代码
andwheres 传入三元组
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); System.out.println(wordsQuery .find("value").find("id").find("thesaurus_code") .page(1).size(2) .andWheres(new ArrayList<Triplet<String, String, Object>>(){ { add(new Triplet<>("thesaurus_code","=","99")); add(new Triplet<>("thesaurus_code","=","14")); } }) .listMapGet()); }
复制代码
生成的 sql 为：
SELECT value,id,thesaurus_code FROM material_thesaurus_words WHERE thesaurus_code = :thesaurus_code AND thesaurus_code = :5363ad8bf3fa098218eb28a15b2f3872 offset 0 limit 2 ;
复制代码
参数为：
{5363ad8bf3fa098218eb28a15b2f3872=14, thesaurus_code=99}
复制代码
组合使用
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); System.out.println(wordsQuery .find("value").find("id").find("thesaurus_code") .page(1).size(2) .orWheres(new ArrayList<Triplet<String, String, Object>>(){ { add(new Triplet<>("thesaurus_code","=","99")); add(new Triplet<>("thesaurus_code","=","14")); } }) .andWheres(new ArrayList<Triplet<String, String, Object>>(){ { add(new Triplet<>("thesaurus_code","=","99")); add(new Triplet<>("thesaurus_code","=","14")); } }) .listMapGet()); }
复制代码
生成的 sql 为
SELECT value, id, thesaurus_codeFROM material_thesaurus_wordsWHERE thesaurus_code = :thesaurus_code OR thesaurus_code = :f6c7a5c9ac4f6fb2f77539b6b31c8f14 AND (thesaurus_code = :f65a9d7f4cafa174ce97dfb216321307 AND thesaurus_code = :27d714567083ce319446329df70ea1ae)offset 0 limit 2;
复制代码
参数为：
{f6c7a5c9ac4f6fb2f77539b6b31c8f14=14, 27d714567083ce319446329df70ea1ae=14, f65a9d7f4cafa174ce97dfb216321307=99, thesaurus_code=99}
复制代码
多级参数
前面介绍的参数均出现在单层，所以在进行 or 操作的时候，如果再和别的条件进行 and，可以发现其实生成的 sql 是有问题的。那么此时就需要引入多级参数了。
多级参数中，三元组的第三个参数可以传入一个WhereSyntaxTree对象，则这个对象作为二级参数存在。来看一个例子
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); WhereSyntaxTree whereSyntaxTree = new WhereSyntaxTree(); System.out.println(wordsQuery .find("value").find("id").find("thesaurus_code") .page(1).size(2) .andWheres(new ArrayList<Triplet<String, String, Object>>(){ { add(new Triplet<>("thesaurus_code","=", whereSyntaxTree.createOrTreeByOperate(new ArrayList<Triplet<String, String, Object>>(){ { add(new Triplet<>("thesaurus_code","=","99")); add(new Triplet<>("thesaurus_code","=","14")); } }))); add(new Triplet<>("这个随便写","=",whereSyntaxTree.createAndTreeByOperate(new ArrayList<Triplet<String, String, Object>>(){ { add(new Triplet<>("value","=","22")); add(new Triplet<>("value","=","11")); } }))); add(new Triplet<>("deleted_mark","=",false)); } }) .listMapGet()); }
复制代码
生成的 sql 为：
SELECT value, id, thesaurus_codeFROM material_thesaurus_wordsWHERE (thesaurus_code = :thesaurus_code OR thesaurus_code = :824abb38c3759e0a61a01f434237d6ed) AND (value = :value AND value = :c621bd6517d712bda0a05c313111ac67) AND deleted_mark = :deleted_markoffset 0 limit 2;
复制代码
参数为
{824abb38c3759e0a61a01f434237d6ed=14, deleted_mark=false, c621bd6517d712bda0a05c313111ac67=11, value=22, thesaurus_code=99}
复制代码
可以看见，这次 or 语句就被括号包裹起来了。
条件不是等号的查询
利用三元组
三元组法上面已经介绍了，这里简单提供一些使用例子
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); WhereSyntaxTree whereSyntaxTree = new WhereSyntaxTree(); System.out.println(wordsQuery .find("value").find("id").find("thesaurus_code") .page(1).size(2) .andWheres(new ArrayList<Triplet<String, String, Object>>(){ { add(new Triplet<>("thesaurus_code","=", whereSyntaxTree.createOrTreeByOperate(new ArrayList<Triplet<String, String, Object>>(){ { add(new Triplet<>("thesaurus_code","like","99%")); add(new Triplet<>("thesaurus_code","like","14%")); } }))); add(new Triplet<>("这个随便写","=",whereSyntaxTree.createAndTreeByOperate(new ArrayList<Triplet<String, String, Object>>(){ { add(new Triplet<>("value","!=","22")); add(new Triplet<>("value","!=","11")); } }))); add(new Triplet<>("id",">",1L)); add(new Triplet<>("deleted_mark","=",false)); } }) .listMapGet()); }
复制代码
生成的 sql 为：
SELECT value, id, thesaurus_codeFROM material_thesaurus_wordsWHERE (thesaurus_code like :thesaurus_code OR thesaurus_code like :2cdde9107318c2af77faa6bc8d62df95) AND (value != :value AND value != :5408b2f7440d44234993b8ae44ae3860) AND id > :id AND deleted_mark = :deleted_markoffset 0 limit 2;
复制代码
除了 like，数据库支持的其他简单操作也都能支持，只不过有一些特殊的函数需要手动编写 SQL。下面简单介绍下编写 SQL 时，该框架的使用方法。
直接执行
获取列表
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); System.out.println(wordsQuery.totalSql("select thesaurus_code,count(id) from material_thesaurus_words group by thesaurus_code;").listMapGet());}
复制代码
输出结果为：
[{thesaurus_code=19, count=3772}, {thesaurus_code=23, count=6226}, {thesaurus_code=99, count=2}, {thesaurus_code=57, count=411}, {thesaurus_code=97, count=3353}, {thesaurus_code=12, count=2}, {thesaurus_code=27, count=3427}, {thesaurus_code=24, count=6}, {thesaurus_code=30, count=229}, {thesaurus_code=16, count=289}]
复制代码
获取一个
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); System.out.println(wordsQuery.totalSql("select thesaurus_code,count(id) from material_thesaurus_words group by thesaurus_code order by count desc limit 1;").simpleGet());}
复制代码
执行的结果为：
{thesaurus_code=23, count=6226}
复制代码
带参数
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); System.out.println(wordsQuery.totalSql("select thesaurus_code,count(id) from material_thesaurus_words where deleted_mark = :deleted_mark group by thesaurus_code order by count desc;").addParams(new HashMap<String, Object>(){ { put("deleted_mark",false); } }).listMapGet());}
复制代码
方法为 addParams，用法与 jdbc 差不多。
更新数据
update 方法
与条件查询方法一样，update 方法也可以传入 hashmap 和三元组列表两种，适用于不同的场景。
传入 map
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); Map<String,Object> condition = wordsQuery.find("id").size(1).simpleGet(); wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); wordsQuery.update(condition,new HashMap<String, Object>(){ { put("deleted_mark",true); } });}
复制代码
生成的 sql 为：
UPDATE material_thesaurus_words SET deleted_mark=:setdeleted_mark,modified_time=:setmodified_time WHERE id = :id ;
复制代码
参数为：
{modified_time=2023-06-05T17:37:57, setmodified_time=2023-06-05T17:37:56.998, id=585730307940941826, setdeleted_mark=true}
复制代码
其中 modified_time 为默认更新字段
传入三元组列表
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); Map<String,Object> condition = wordsQuery.find("id").size(1).simpleGet(); wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); wordsQuery.update(new ArrayList<Triplet<String, String, Object>>(){ { add(new Triplet<>("deleted_mark","!=",true)); } },new HashMap<String, Object>(){ { put("deleted_mark",true); } });}
复制代码
生成的 sql 是：
UPDATE material_thesaurus_words SET deleted_mark=:setdeleted_mark,modified_time=:setmodified_time WHERE deleted_mark != :deleted_mark ;
复制代码
保存数据
insert 方法
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); wordsQuery.insert(new HashMap<String, Object>(){ { put("value","123"); put("thesaurus_code","1"); } });}
复制代码
生成的 sql 为：
INSERT INTO material_thesaurus_words(value,thesaurus_code) VALUES (:value,:thesaurus_code) RETURNING id;
复制代码
batchInsert 方法
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class); wordsQuery.batchInsert(new ArrayList<Map<String, Object>>(){ { add(new HashMap<String, Object>(){ { put("value","123"); put("thesaurus_code","1"); } }); add(new HashMap<String, Object>(){ { put("value","1234"); put("thesaurus_code","1"); } }); } });
复制代码
生成的 sql 为：
INSERT INTO material_thesaurus_words(value,thesaurus_code) VALUES (:value0,:thesaurus_code0),(:value1,:thesaurus_code1);
复制代码
删除
delete 方法
按 id 删除
@Test public void demo2(){ SimpleQuery wordsQuery = new SimpleQuery(MaterialThesaurusWords.class);
wordsQuery.delete(wordsQuery.find("id").size(1).simpleGet().get("id"));}
复制代码
生成 sql：
UPDATE material_thesaurus_words SET deleted_mark=:setdeleted_mark,deleted_time=:setdeleted_time WHERE id = :1c950fec1d9bb9f2a2a14502fa82d6d8 ;
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 3
demo123567
关注
还未添加个人签名 2018-05-03 加入
还未添加个人简介
评论