SpringCloud（2）——SpringDataJpa事物详例

Spring cloud 原本对Springmvc架构做了进一步的封装,同样SpringDataJpa对底层做了进一步的封装，正是由于这样，使得我们在运用JPA写一些相对复杂的SQL语句时，总会出现对一些原生的语法出现无法支持情况，下面这些正是我在微服务架构中对JPA的运用，包括：如何进行联表查询、复杂SQL进行拆分、事物操作等

1、普通查询、包括如何将查询结果直接转化为DTO对象，同样可以直接转为Map

package com.sinosoft.repository;

import com.sinosoft.domain.Usersinfo;
import com.sinosoft.dto.TerritoryDTO;
import com.sinosoft.dto.UsersinfoDTO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface UsersinfoRepository extends JpaRepository<Usersinfo, Long> {

    /**
     * 获取领域（省）信息
     *
     * @return T
     */
    @Query("select new com.sinosoft.dto.TerritoryDTO(t.id as id, t.territoryname as territoryname, t.territoryparentid as territoryparentid) " +
            "from Territory t where t.territorylevel = '1' order by t.id")
    List<TerritoryDTO> territoryProvinceInfo();

    /**
     * 根据领域省获取领域（市）信息
     *
     * @return LIST
     */
    @Query("select new com.sinosoft.dto.TerritoryDTO(t.id as id, t.territoryname as territoryname, t.territoryparentid as territoryparentid) " +
            "from Territory t where t.territorylevel = '2' and t.territoryparentid = ?1 order by t.id")
    List<TerritoryDTO> territoryCityInfo(String id);

    /**
     * 根据领域市获取领域（县）信息
     *
     * @return LIST
     */
    @Query("select new com.sinosoft.dto.TerritoryDTO(t.id as id, t.territoryname as territoryname, t.territoryparentid as territoryparentid) " +
            "from Territory t where t.territorylevel = '3' and t.territoryparentid = ?1 order by t.id")
    List<TerritoryDTO> territoryCountyInfo(String id);

    /**
     * 根据主键数组获取用户列表信息
     *
     * @param usersArrayIds 主键数组
     * @return page
     */
    @Query("select new com.sinosoft.dto.UsersinfoDTO(u.id as id, u.realname as realname, u.certificatetype as certificatetype, " +
            "u.certificatenum as certificatenum, u.mobile as mobile, '' as password, u.headimg as headimg, u.nickname as nickname, " +
            "u.sex as sex, u.age as age,u.birthday as birthday, u.registerprovince as registerprovince, u.registercity as registercity, u.registercounty as registercounty, " +
            "u.locateprovince as locateprovince, u.locatecity as locatecity, u.locatecounty as locatecounty, u.persondeclaration as persondeclaration, " +
            "u.height as height, u.bodyweight as bodyweight, u.integralvalue as integralvalue, u.title as title, u.targetsteps as targetsteps, " +
            "u.userCode as userCode, u.createdate as createdate, u.modifydate as modifydate) from Usersinfo u where u.id in ?1")
    List<UsersinfoDTO> findUsersByArrayIds(List<Long> usersArrayIds);

    /**
     * 根据userCode来判断用户是否存在
     * @param userCode
     * @return
     */
    @Query("select u from Usersinfo u where u.userCode = ?1")
    Usersinfo findByUserCode(String userCode);

    /**
     * 根据用户的ID获取对应Token
     *
     * @return MAP
     */
    @Query("select u.devicetoken as devicetoken, u.terminalsystem as TerminalSystem from Userpushinforelation u where u.userid = ?1")
    List<Map<String,Object>> findTokenByUserID(Long userID);
}

1、JPA分页查询

先创建分页对象Pagebale对象

//开始执行排序
List<Sort.Order> orders = new ArrayList<Sort.Order>();
orders.add(new Sort.Order(Sort.Direction.DESC, "messagedate"));
orders.add(new Sort.Order(Sort.Direction.DESC, "messagetime"));
Pageable pageable = new PageRequest(messagePageReqDTO.getCurrentPage(), messagePageReqDTO.getPageSize(), new Sort(orders));

说明：

“messagePageReqDTO.getCurrentPage()”：当前页码，这个值是由前端传递过来的，默认从 0 开始，逐渐往上递增

“messagePageReqDTO.getPageSize()”：每页显示条数，这个值是由前端传递过来的，前端定死的，比如：如果定 5，则查询所有的数据则根据5进行分页（总条数/5=总页数）

执行分页查询：

//执行分页查询
List<Map<String, Object>> list = this.messageinfoRepository.findMessInfoByMessType(messageType, token, pageable);

/**
     * 根据消息类型查询消息列表的分页信息
     *
     * @param messageType 消息类型
     * @param pageable    分页
     * @return T
     */
    @Query("select m.id as id, m.messagetype as messagetype, m.messagecontent as messagecontent, " +
            "date_format(m.messagedate,'%Y/%m/%d') as messagedate, substring(m.messagetime, 1, 5) as messagetime, m.messageimg as messageimg, m.batchnum as batchnum  " +
            "from Messageinfo m, Pushdetail p \n" +
            "where p.batchnum = m.batchnum and m.messagetype = ?1 and p.devicetoken = ?2")
    List<Map<String, Object>> findMessInfoByMessType(String messageType, String token, Pageable pageable);

备注：

JPA不支持limit原生语法，如果需要使用limit、需要借助Pageable来解决

JPA不支持join原生语法，如果需要使用需要进行SQL拆分