jpa Repository的常用写法总结

一、前言

之前项目在xml中写sql，感觉标签有很多，比较灵活；

最近在写新项目，使用了jpa，只能在java中写sql了，感觉不太灵活，但是也得凑付用。

以下总结下常用入参出参写法。

二、Repository代码样例

@SuppressWarnings("unused")
@Repository
public interface HourRepository extends JpaRepository<Dlhour, Long>, JpaSpecificationExecutor<Dlhour> {

    @Query(value = "SELECT * " +
            "FROM dlhour d,bsc_user_t A " +
            "WHERE A.user_name = d.ename AND A.user_identity = '0' " +
            "and IF(?1 !='' and ?1 is not null , A.user_name = ?1 ,1=1 )  " +
            "and IF(?2 !='' and ?2 is not null,A.`NAME` LIKE concat('%', ?2, '%') ,1=1 )  " +
            "and IF(?3 !='' and ?3 is not null , d.fact_startdate >= ?3 ,1=1 )  " +
            "and if(COALESCE(?4,NULL) IS NOT NULL,   A.company in  (?4) ,1=1 ) " +
            "GROUP BY user_id "
            , countQuery = "SELECT count(*) " +
            "FROM dlhour d,bsc_user_t A " +
            "WHERE A.user_name = d.ename AND A.user_identity = '0' " +
            "and IF(?1 !='' and ?1 is not null , A.user_name = ?1 ,1=1 )  " +
            "and IF(?2 !='' and ?2 is not null,A.`NAME` LIKE concat('%', ?2, '%') ,1=1 )  " +
            "and IF(?3 !='' and ?3 is not null , d.fact_startdate >= ?3 ,1=1 )  " +
            "and if(COALESCE(?4,NULL) IS NOT NULL,   A.company in  (?4) ,1=1 ) " +
            "GROUP BY user_id "
            , nativeQuery = true)
    Page<Map<String, Object>> findClassHour(String user_name, String name, String fact_startdate,  List<String> companys, Pageable pageable);

}

说明：
1.这是一个根据条件查询结果列表的接口，有分页，有入参pageable（不用自己手写分页了）
2.Dlhour是javabean，与数据库中的一张表对应，字段一一对应，注意必须有id列。
3.user_name是第一个入参，放入sql中就是等于的条件
4.name是第二个入参，放入sql中可以模糊查询
5.fact_startdate是第三个入参，可以用来查询时间范围，string类型就可以，例如2023-11-06
6.companys是第四个入参，是List<String>类型，放入sql用来查询in条件

三、其余部分代码样例

1.Service层

@Service
public class ClassHourService {
    private final Logger log = LoggerFactory.getLogger(ClassHourService.class);
    
    @Autowired
    private HourRepository hourRepository;

    public Page<Map<String, Object>> findClassHour(JSONObject reqJson, Pageable pageable) {
        List<String> companys = null;
        String company = reqJson.getString("company");
        if(company!=null && !"".equals(company)){
            companys = Arrays.asList(company.split(","));
        }
       
       return hourRepository.findClassHour(reqJson.getString("user_name"), reqJson.getString("name"), reqJson.getString("fact_startdate"), companys, pageable);
       
    }
}

说明：
(1)前端传来的参数company，是逗号分隔的，转为List<String>类型的companys，当入参
(2)直接从前端传来的json报文中拿到user_name, name, fact_startdate参数
(3)pageable参数是前端传来的（好像在header里），直接传下去

2.Controller层

@RestController
@RequestMapping("/first")
public class ClassHourResource {
    private final Logger log = LoggerFactory.getLogger(ClassHourResource.class);

    @Autowired
    private ClassHourService classHourService;

    @GetMapping("/second/findClassHour")
    public ResponseEntity<List<Map<String,Object>>> findClassHour(@RequestBody JSONObject reqJson, Pageable pageable) {
        log.debug("REST request to findClassHour: {}", reqJson.toJSONString());
        Page<Map<String,Object>> page = classHourService.findClassHour(reqJson, pageable);
        HttpHeaders headers = PaginationUtil.generatePaginationHttpHeaders(page, "/first/second/findClassHour");
        return ResponseEntity.ok().headers(headers).body(page.getContent());
    }

}

说明：
(1)入参是json请求体reqJson，pageable（前端框架自带了，这个其实是请求url?后的page与size参数决定的）
(2)然后把参数扔到service层，获取返回值
(3)使用PaginationUtil获取了一个响应头headers，里面包含总页数、当前页数等前端框架需要的信息（这些信息在响应header里）
(4)最后把查询到的数据放入响应体（格式可以认为是JsonArray），返回给前端

3.PaginationUtil样例

import org.springframework.data.domain.Page;
import org.springframework.http.HttpHeaders;
import org.springframework.web.util.UriComponentsBuilder;

public final class PaginationUtil {

    private PaginationUtil() {
    }

    public static <T> HttpHeaders generatePaginationHttpHeaders(Page<T> page, String baseUrl) {

        HttpHeaders headers = new HttpHeaders();
        headers.add("X-Total-Count", Long.toString(page.getTotalElements()));
        String link = "";
        if ((page.getNumber() + 1) < page.getTotalPages()) {
            link = "<" + generateUri(baseUrl, page.getNumber() + 1, page.getSize()) + ">; rel=\"next\",";
        }
        // prev link
        if ((page.getNumber()) > 0) {
            link += "<" + generateUri(baseUrl, page.getNumber() - 1, page.getSize()) + ">; rel=\"prev\",";
        }
        // last and first link
        int lastPage = 0;
        if (page.getTotalPages() > 0) {
            lastPage = page.getTotalPages() - 1;
        }
        link += "<" + generateUri(baseUrl, lastPage, page.getSize()) + ">; rel=\"last\",";
        link += "<" + generateUri(baseUrl, 0, page.getSize()) + ">; rel=\"first\"";
        headers.add(HttpHeaders.LINK, link);
        return headers;
    }

    private static String generateUri(String baseUrl, int page, int size) {
        return UriComponentsBuilder.fromUriString(baseUrl).queryParam("page", page).queryParam("size", size).toUriString();
    }
}

说明：
(1)这个方法大概就是把总页数、当前页等信息放入响应头，供前端框架使用
(2)前端框架是react antd框架
(3)对应的这个方法也是后端框架自己封装的，与前端antd对应