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);
以上为个人经验，希望能给大家一个参考，也希望大家多多支持www.887551.com。如有错误或未考虑完全的地方，望不吝赐教。
黄山市民网：https://www.huangshanshimin.com/
