springboot的jpa可以根据方法名自动解析sql 非常方便, 只需要在 dao接口中定义方法即可;
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()]));
}
};
}
想实现这样的效果
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);
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。
