spring-data-jpa 使用

前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。点击跳转到教程。

只是记录下使用方法 ：

1.  RPC接口 service实现类调用，PageRequest对象会实现分页、排序。

 @Override
    public Object getAAAByPage(Pageable pageable, String startTime, String endTime, String XXX, String XXX,
                       String XXX, String XXX, String position, String jobTypeId,
                       String projectId, String busId, String jobContent, String status) {

        Page<Workxxx> page = workxxxRepository.findAll(new Specification<Workxxx>() {
            @Override
            public Predicate toPredicate(Root<Workxxx> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> list = new ArrayList<>();

                if(null != employeeNo && !"".equals("employeeNo")){
                    list.add(cb.equal(root.<String>get("employeeNo"), employeeNo));
                }
     
                if(null != subDepartmentId && !"".equals("subDepartmentId")){
                    list.add(cb.equal(root.<String>get("subDepartmentId"), subDepartmentId));
                }
                if(null != status && !"".equals("status")){
                    list.add(cb.like(root.<String>get("status"), status));
                }
                if (null != startTime && !"".equals("startTime")) {
                    list.add(cb.greaterThanOrEqualTo(root.<Date>get("startTime"), DateUtil.stringToDate(startTime)));
                }
                if (null != endTime && !"".equals("endTime")) {
                    list.add(cb.lessThanOrEqualTo(root.<Date>get("endTime"), DateUtil.stringToDate(endTime)));
                }

                if (list.size() != 0) {
                    Predicate[] p = new Predicate[list.size()];
                    return cb.and(list.toArray(p));
                } else {
                    return null;
                }
            }
        }, new PageRequest(pageable.getPageNumber(), pageable.getPageSize(), pageable.getSort()));
        return page;
    }

rest 接口直接调用RPC接口实现，rest接口实现 ：

  @Path("/我是路径名")
    @GET
    @Override
    public Payload getWorkxxxFillSituationByPage(
            @QueryParam("page") @DefaultValue("0") int page,
            @QueryParam("size") @DefaultValue("10") int size,
            @QueryParam("startTime") String startTime,
            @QueryParam("endTime") String endTime, ... ...           
            @QueryParam("status") String status) {

        List<Order> orders = new ArrayList<Order>();
        orders.add(new Order(Direction.DESC,"startTime")); // startTime 为 排序依据字段
        Pageable pageable = new PageRequest(page, size, new Sort(orders));
        try {
            return new Payload(workXXXService.getAAAByPage(pageable, startTime,
                    endTime, XXX, departmentId, XXX, employeeName, position, XXX, XXX, busId, jobContent, status));
        } catch (Exception ex) {
            logger.debug("XXX查询失败：",ex.getMessage());
            return null;
        }
    }

用GET请求访问接口：

http://localhost:8082/类上注解的路径/我是路径名?page=0&size=2

2.  这个接口只用声明就可以了，基本的增、删、改、查。JPA已经实现，并且只要被调用会自动执行，会自动解析方法名并处理对应业务。

这个相当于dao的声明，也就是mybatis中的mapper :

@Repository
public interface WorkxxxRepository extends JpaRepository<Workxxx, Integer>, JpaSpecificationExecutor<Workxxx> {}

要用自己写的原生sql,也可以实现它，并写上自己的sql,给上注解说明使用本地sql 

 @Query(value = "select u.id, u.username from XXX_user u inner join XXXX gpr on u.id =  gpr.USER_ID  where PROJECT_ID=?1 and USER_ID in (select USER_ID from " +
            "XXX where department_id = ?2)", nativeQuery=true)
    public List<Object[]> getXXXUserIds(String projectId, String departmentId);

3.  实体给上对应注解，会自动关联到实体对应数据库表

@Entity(name = "workxxx")
@Table(name = "XXX_work_xxx")
@JsonIgnoreProperties(ignoreUnknown = true)
public class WorkWeight implements Serializable {

    private static final long serialVersionUID = 8245791221237374426L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "employee_id", nullable = false)
    private String employeeId; ... ...

4. RPC接口  service 也可以不调用 repository ,直接写sql 并运行：PageImpl 对象可以帮助封装返回的数据

  @Override
    public Page<WorkxxxDto> getXXXCollectByPage(Pageable pageable, String startTime, String endTime,
                         String projectId, String XXX, String employeeName, boolean isLastDetail) {

        EntityManager em = emf.createEntityManager();
        String sql =
                "select ad.id, gww.start_time,ad.name,gww.XXX," +
                " COUNT(gww.id) human,FORMAT(SUM(gww.proportion*5),1) days," +
                " gww.employee_no,gww.XXX,gww.XXX,gww.job_content,gww.XXX,gww.end_time "+
                " FROM XXX gww ,XXX ad " +
                " WHERE gww.department_id = ad.id and gww.job_type_id != 3 ";

        if(null != startTime && !"".equals(startTime)){  // 时间周期条件查询
            sql += " and gww.start_time >= '"+startTime+"'";
        }
        if(null != endTime && !"".equals(endTime)){  // 时间周期条件查询
            sql += " and gww.end_time <= '"+endTime+"'";
        }
        if(null != projectId && !"".equals("projectId")){
            sql += " and gww.projectId="+employeeName;
        }
        

        if(isLastDetail){ // 详情
            sql +=  " and ad.id = '"+organizationId+"' GROUP BY gww.id";
        }else{
            // 根据组织分组
            if(null != organizationId && !"".equals(organizationId)){
                sql +=  " and ad.parent_id = '"+organizationId+"' GROUP BY WEEKOFYEAR(gww.start_time),ad.id";
            }else{ // 按部门分组，参数组织id为空
                sql +=  " and ad.parent_id in(SELECT id FROM XXX WHERE parent_id ='') GROUP BY WEEKOFYEAR(gww.start_time),ad.id,gww.project_id";
            }
        }

        String countSql ="select count(*) from ("+sql+") a"; // 查询总条数(别忘了给子查询起个别名，比如:a )
        Query queryCount =  em.createNativeQuery(countSql);
        long totalElements = ((BigInteger)queryCount.getSingleResult()).longValue();
        //  查询sql
        sql += " ORDER BY gww.start_time DESC LIMIT "+pageable.getPageNumber()*pageable.getPageSize() +","+pageable.getPageSize();

        Query queryList =  em.createNativeQuery(sql);
        List<Object> objecArraytList = queryList.getResultList();
        List<WorxxxDto> dtoList = new ArrayList<WorkxxxDto>();
        for(int i=0;i<objecArraytList.size();i++) {
            Object[] obj = (Object[]) objecArraytList.get(i);
            WorkxxxDto wwdto = new WorkxxxDto();
            wwdto.setOrganizationId(String.valueOf(obj[0]));
            wwdto.setStartTime(String.valueOf(obj[1]));
            wwdto.setOrganizationName(String.valueOf(obj[2]));
            wwdto.setProjectName(String.valueOf(obj[3]));
            String inputHumanDays = String.valueOf(obj[4]);                   
            dtoList.add(wwdto);
        }
        em.close();
        return new PageImpl(dtoList,pageable,totalElements);
    }

rest 接口直接调用RPC接口实现，rest接口实现 ：
   在sql中已经人工分页排序

    @Path("/getBBB")
    @GET
    @Override
    public Payload getWorkWeightApprovaEevolveByPage(
            @QueryParam("page") @DefaultValue("0") int page,
            @QueryParam("size") @DefaultValue("10") int size,
            @QueryParam("startTime") String startTime,
            @QueryParam("endTime") String endTime,
            @QueryParam("organizationId") String XXX,
            @QueryParam("isComplete") @DefaultValue("false") boolean isComplete) {

        Pageable pageable = new PageRequest(page, size);
        Map<String,Object> resultMap = null;
        try{
            return new Payload(workxxxCollectService.getWorkxxxApprovaEevolveByPage(pageable, startTime,
                    endTime, organizationId, isComplete));
        }catch (Exception ex){
            logger.debug("XXX查询失败：",ex.getMessage());
            logger.debug(ex.getMessage());
            return null;
        }
    }

5.  接口返回 json 数据样例：

 {
        "payload": {
            "content": [
                {
                    "id": 20,
                    "xxxyeeId": "21",
                    "xxxeeNo": "2",
                    "exeName": "xx需",                   
                    "xxxtName": "XXXxx部",
                    "sxxxxxtId": "005FD84B2xxx3C0D956D",
                    "xxxxntName": "XXX室",                   
                    "xxxxn": 0.4,
                    "sxxime": "2017-08-28",
                    "xxime": "2017-09-03",
                    "status": 2,                   
                    "updateTime": "2017-12-21",
                    "createBy": "ABC46xxx7CD79D2",
                    "updateBy": "22",
                    "xag": "0",
                    "cxxxt": 0
                }
            ],
            "last": true,
            "totalElements": 1,  // 总记录条数
            "totalPages": 1,
            "number": 0,
            "size": 2,
            "first": true,
            "sort": [
                {
                    "direction": "DESC",  // 倒序
                    "property": "startTime",   // 排序依据字段，可以多字段
                    "ignoreCase": false,
                    "nullHandling": "NATIVE",
                    "ascending": false
                }
            ],
            "numberOfElements": 1   // 当前页记录条数
        },
        "request_id": "2993e849-d4bf-442f-8690-6056b3f80377"
    }