springboot 之jpa高级查询操作
程序员文章站
2022-09-26 09:39:00
springboot的jpa可以根据方法名自动解析sql 非常方便, 只需要在 dao接口中定义方法即可;下面是一个 demopackage com.bus365.root.dao; import j...
springboot的jpa可以根据方法名自动解析sql 非常方便, 只需要在 dao接口中定义方法即可;
下面是一个 demo
package com.bus365.root.dao; import java.io.serializable; import java.util.list; import org.springframework.data.jpa.repository.jparepository; import org.springframework.data.jpa.repository.jpaspecificationexecutor; import org.springframework.data.jpa.repository.query; import org.springframework.data.repository.query.param; import com.bus365.root.model.user; public interface userdao extends jparepository<user,long>,jpaspecificationexecutor<user>,serializable { user findbyname(string name); user findbynameandage(string name, integer age); user findbynameorage(string name, integer age); /*@query(value = "from user where name = :name") list<user> findbyname(@param("name") string name);*/ }
下面展示service层调用:
@override public user findbyname(string name) { user user = userdao.findbyname(name); return user; } @override public user findbynameandage(string name, integer age) { user user = userdao.findbynameandage(name,age); return user; } @override public user findbynameorage(string name, integer age) { user user = userdao.findbynameorage(name,age); return user; }
具体的关键字,使用方法和生产成sql如下表所示
keyword | sample | jpql snippet |
---|---|---|
and | findbylastnameandfirstname | … where x.lastname = ?1 and x.firstname = ?2 |
or | findbylastnameorfirstname | … where x.lastname = ?1 or x.firstname = ?2 |
is,equals | findbyfirstnameis,findbyfirstnameequals | … where x.firstname = ?1 |
between | findbystartdatebetween | … where x.startdate between ?1 and ?2 |
lessthan | findbyagelessthan | … where x.age < ?1 |
lessthanequal | findbyagelessthanequal | … where x.age ⇐ ?1 |
greaterthan | findbyagegreaterthan | … where x.age > ?1 |
greaterthanequal | findbyagegreaterthanequal | … where x.age >= ?1 |
after | findbystartdateafter | … where x.startdate > ?1 |
before | findbystartdatebefore | … where x.startdate < ?1 |
isnull | findbyageisnull | … where x.age is null |
isnotnull,notnull | findbyage(is)notnull | … where x.age not null |
like | findbyfirstnamelike | … where x.firstname like ?1 |
notlike | findbyfirstnamenotlike | … where x.firstname not like ?1 |
startingwith | findbyfirstnamestartingwith | … where x.firstname like ?1 (parameter bound with appended %) |
endingwith | findbyfirstnameendingwith | … where x.firstname like ?1 (parameter bound with prepended %) |
containing | findbyfirstnamecontaining | … where x.firstname like ?1 (parameter bound wrapped in %) |
orderby | findbyageorderbylastnamedesc | … where x.age = ?1 order by x.lastname desc |
not | findbylastnamenot | … where x.lastname <> ?1 |
in | findbyagein(collection ages) | … where x.age in ?1 |
notin | findbyagenotin(collection age) | … where x.age not in ?1 |
true | findbyactivetrue() | … where x.active = true |
false | findbyactivefalse() | … where x.active = false |
ignorecase | findbyfirstnameignorecase | … where upper(x.firstame) = upper(?1) |
下面介绍使用java原生的jpa操作数据库,对jpa熟悉的朋友应该很快就能理解,springboot使用原生jpa的关键是引入entitymanger
看一下service层
package com.bus365.root.service.impl; import java.util.list; import javax.persistence.entitymanager; import javax.persistence.persistencecontext; import org.springframework.stereotype.service; import com.bus365.root.model.address; import com.bus365.root.service.addressservice; @service public class addressserviceimpl implements addressservice { @persistencecontext private entitymanager entitymanager; public list<address> listaddress(){ list resultlist = entitymanager.createnativequery("select * from address ", address.class).getresultlist(); return resultlist; } }
注意 @persistencecontext
private entitymanager entitymanager;
动态引入entitymanger , 之后就能正常使用了;
createnativequery是操作原生mysql方法;支持跨表查询;
jpa的事务 直接使用注解transactional 参数rollbackon表示回滚条件, 这个注解一搬加在service层; 注意getsingleresult 如果查不到数据会报错;
@transactional(rollbackon= {exception.class}) public address getaddressbyid(long id) { address singleresult = null; try { singleresult = (address) entitymanager .createnativequery("select * from address where id = :id", address.class).setparameter("id", id) .getsingleresult(); } catch (exception e) { e.printstacktrace(); } return singleresult; }
jpa实现多表联查;
@transactional public list<object[]> getuserwithaddrbyid(long id) { list resultlist = entitymanager.createnativequery( "select u.id id,u.age age,u.name name,a.name aname,a.completeaddress addre from user u left join address a on u.addressid = a.id where u.id = :id") .setparameter("id", id).getresultlist(); return resultlist; }
这是一个联查user 和address的例子, 返回的结果是个list<object[]> 项目中一般封装成vo 类,或者list<map<string,object>> 的形式
github项目地址 https://github.com/christain1993/springbootintegration
补充:springbootjpa的复杂查询
分页
/** * 条件查询+分页 * @param wheremap * @param page * @param size * @return */ public page<casemanage> findsearch(map wheremap, int page, int size,integer createid) { sort sort = new sort(sort.direction.desc,"id"); specification<casemanage> specification = createspecification(wheremap,createid); pagerequest pagerequest = new pagerequest(page,size,sort); return casedao.findall(specification, pagerequest); } /** * 条件查询 * @param wheremap * @return */ public list<casemanage> findsearch(map wheremap,integer createid) { specification<casemanage> specification = createspecification(wheremap, createid); return casedao.findall(specification); } /** * 动态条件构建 * @param searchmap * @return */ private specification<casemanage> createspecification(map searchmap,integer createid) { return new specification<casemanage>() { @override public predicate topredicate(root<casemanage> root, criteriaquery<?> query, criteriabuilder cb) { list<predicate> predicatelist = new arraylist<predicate>(); // 案件名称 if (searchmap.get("case_name")!=null && !"".equals(searchmap.get("case_name"))) { predicatelist.add(cb.like(root.get("case_name").as(string.class), "%"+(string)searchmap.get("case_name")+"%")); } // 案件编号uuid类型 if (searchmap.get("case_uuid")!=null && !"".equals(searchmap.get("case_uuid"))) { predicatelist.add(cb.equal(root.get("case_uuid").as(string.class), (string)searchmap.get("case_uuid"))); } return cb.and( predicatelist.toarray(new predicate[predicatelist.size()])); } }; }
or查询
想实现这样的效果
where (state=1 or state=2)and name='zhangsan'
java代码
list<predicate> predicatelist = new arraylist<predicate>(); predicate or = cb.or(cb.and(cb.equal(root.get("case_authority").as(string.class), "0")), cb.and(cb.equal(root.get("create_id").as(string.class), string.valueof(createid)))); predicatelist.add(or);
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。
上一篇: python lambda的使用详解
下一篇: python热力图实现简单方法
推荐阅读