时间:2021-05-19
springboot的jpa可以根据方法名自动解析sql 非常方便, 只需要在 dao接口中定义方法即可;
下面展示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; @Servicepublic 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的复杂查询
想实现这样的效果
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);以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
前一阵子接手了一个使用SpringBoot和spring-data-jpa开发的项目,后期新加入一个小伙伴,表示jpa相比mybatis太难用,多表联合的查询写
本文演示了SpringBoot下,实用Spring-Data-Jpa来实现CRUD操作,视图层采用Freemarker这里我们先把application.pro
springboot中集成jpa需要再pom文件中添加jpa的jar包,使用springboot的话iju不用自己规定版本号了,自动管理依赖版本即可。org.s
本文介绍了SpringDataJPA系列之投影(Projection)的用法,分享给大家在JPA的查询中,有一个不方便的地方,@Query注解,如果查询直接是S
我们在前面已经分别介绍了如何在springBoot中使用JPA以及如何在SpringBoot中输出REST资源。那么关于数据库访问还有一个核心操作那就是事务的处