jpa Repository的常用写法总结

r囧r小猫 2024-02-19 08:57 18阅读 0赞

## 一、前言 ##

之前项目在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对应