springboot的jpa可以根據(jù)方法名自動解析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層調(diào)用:

@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; }

具體的關(guān)鍵字，使用方法和生產(chǎn)成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操作數(shù)據(jù)庫,對jpa熟悉的朋友應(yīng)該很快就能理解,springboot使用原生jpa的關(guān)鍵是引入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 { @PersistenceContextprivate EntityManager entityManager;public List<Address> listAddress(){List resultList = entityManager.createNativeQuery('select * from address ', Address.class).getResultList();return resultList;}}

注意 @PersistenceContext

private EntityManager entityManager;

動態(tài)引入entitymanger , 之后就能正常使用了;

createNativeQuery是操作原生mysql方法;支持跨表查詢;

jpa的事務(wù) 直接使用注解Transactional 參數(shù)rollbackon表示回滾條件, 這個注解一搬加在service層; 注意getSingleResult 如果查不到數(shù)據(jù)會報錯;

@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實現(xiàn)多表聯(lián)查;

@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; }

這是一個聯(lián)查user 和address的例子, 返回的結(jié)果是個List<Object[]> 項目中一般封裝成vo 類,或者List<Map<String,Object>> 的形式

github項目地址 https://github.com/Christain1993/SpringBootIntegration

補充：springBootJpa的復(fù)雜查詢

分頁

/** * 條件查詢+分頁 * @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); } /** * 動態(tài)條件構(gòu)建 * @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查詢

想實現(xiàn)這樣的效果

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

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持好吧啦網(wǎng)。如有錯誤或未考慮完全的地方，望不吝賜教。