欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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);

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。