Spring Data JPA使用规范

Myth丶恋晨 2022-01-25 22:31 241阅读 0赞

### 1、不同个常场景使用方式 ###

**1）极简场景**

在dao中，使用方法命名；

Employee getByEmployeeCodeAndDeleteStatus(String employeeCode, int deleteStatus);

**2）复杂场景**

在dao中，方法上，使用注解@Query拼写sql；

@Query(value = "select e.* from hr_employee e, hr_employee_approval_role_section ea where ea.employeeId = e.id and approvalRoleId = ?1 and sectionId in ?2 ",nativeQuery = true)
    List<Employee> findByApprovalRoleIdAndSectionIdIn(Long id, Set<Long> sectionIds);

**3）条件查询**

在service层拼写sql，调用baseDao的自己封装的nativeFindAll()方法；

因为条件查询或许有或许无，需增加程序判断，拼接sql的where条件，以及参数的设定

@Override
        public List<ApplicationTypeVO> findGroupList(Set<Long> applicationIdSet) {
            String sql = "select at.id as typeId, at.typeName, a.id,a.name,a.iconUrl,a.url " +
                    " from emp_application a,emp_application_type_application atp,emp_application_type at where " +
                    "a.id  = atp.applicationId and atp.applicationTypeId = at.id and a.enabled = 0 " +
                    " and a.id in (:applicationIdSet)" +
                    " order by at.orderBy ASC,atp.orderBy asc,a.id asc ";
            Map<String, Object> map = new HashMap<>(1);
            map.put("applicationIdSet", applicationIdSet);
            List<ApplicationDetailVO> list = applicationDao.nativeFindAll(sql,map, ApplicationDetailVO.class);
    
            if (CollectionUtils.isEmpty(list)){
                return Collections.emptyList();
            }
            List<ApplicationTypeVO> list1 = new ArrayList<>();
            Long nowTypeId = null;
            ApplicationTypeVO typeVO = null;
            for (ApplicationDetailVO vo : list) {
    
                if (nowTypeId == null || !nowTypeId.equals(vo.getTypeId())){
                    nowTypeId = vo.getTypeId();
                    typeVO = new ApplicationTypeVO(vo.getTypeId(), vo.getTypeName());
                    list1.add(typeVO);
                }
                ApplicationSimpleVO simpleVO = new ApplicationSimpleVO(vo.getId(), vo.getName(), vo.getIconUrl(), vo.getUrl());
                typeVO.getApplicationList().add(simpleVO);
            }
            return list1;
        }

**4）条件分页查询**

单表/多表分页查询：在service层拼写sql，调用baseDao的自己封装的nativeFindPage()方法；

@Override
        public Page<EmployeeAllListVO> getWaitForEntryList(EmployeeAllVO vo) {
    
            Map<String, Object> map = new HashMap<>(12);
            StringBuilder sql = new StringBuilder();
    
            sql.append(" SELECT                                   ");
            sql.append("  h.id,h.employeeCode,h.employeeName,h.phoneNumber,h.identityCard,h.entryTime,h.status,h.createTime ");
            sql.append(" FROM                                     ");
            sql.append("   hr_employee h,                         ");
            sql.append("   hr_employee_detail d                   ");
            sql.append(" WHERE h.id = d.employeeId                ");
    
            //数据权限
    
            Set<Long> secIdList = findUserPowerSectionIds();
            log.info("待入职员工查询,当前用户查询数据范围为：{}", secIdList);
            if ((null != vo.getSectionId() || !CollectionUtils.isEmpty(secIdList))) {
                sql.append(" AND h.id in (");
                sql.append(" SELECT DISTINCT(employeeId)            ");
                sql.append("        FROM            ");
                sql.append("   hr_employee_section_position p,            ");
                sql.append(" hr_section_position s            ");
                sql.append("  WHERE p.sectionPositionId = s.id            ");
    
                if (null != vo.getSectionId()) {
                    sql.append("   AND s.sectionId= :sectionId  ");
                    map.put("sectionId", vo.getSectionId());
                }
    
                if (!CollectionUtils.isEmpty(secIdList)) {
                    sql.append(" and sectionId in (:sectionIds)");
                    map.put("sectionIds", secIdList);
                }
                sql.append(")");
    
            }
    
            if (null != vo.getHr()) {
                sql.append("   AND d.hr = :hr                            ");
                map.put("hr", vo.getHr());
            }
    
            if (!CollectionUtils.isEmpty(vo.getStatus())) {
                sql.append("   AND h.status in (:status)                  ");
                map.put("status", vo.getStatus());
    
            } else {
                sql.append("   AND h.status<20                        ");
            }
    
            if (StringUtils.isNotBlank(vo.getEmployeeName())) {
                sql.append("   AND h.employeeName like :employeeName  ");
                map.put("employeeName", "%" + vo.getEmployeeName() + "%");
            }
    
            if (StringUtils.isNotBlank(vo.getPhoneNumber())) {
                sql.append("   AND h.phoneNumber = :phoneNumber  ");
                map.put("phoneNumber", vo.getPhoneNumber());
            }
    
            if (StringUtils.isNotBlank(vo.getOaSuccPassTimeEnd()) && StringUtils.isNotBlank(vo.getOaSuccPassTimeStart())) {
                map.put("oaSuccPassTimeStart", vo.getOaSuccPassTimeStart());
                map.put("oaSuccPassTimeEnd", vo.getOaSuccPassTimeEnd());
                sql.append(" and d.oaSuccPassTime >= :oaSuccPassTimeStart and d.oaSuccPassTime<=:oaSuccPassTimeEnd and h.status=13");
            }
    
            sql.append("  AND h.deleteStatus = 0 ORDER BY h.createTime desc");
    
            log.info("待入职员工查询：sql:{};参数：{}", sql.toString(), map);
    
            Page<EmployeeAllListVO> page = employeeDao.nativeFindPage(sql.toString(), map, EmployeeAllListVO.class, vo);
    
            if (CollectionUtils.isEmpty(page.getData())) {
                return page;
            }
    
            List<EmployeeAllListVO> list = page.getData();
    
            for (EmployeeAllListVO eVO : list) {
                eVO.setStatusDesc(EmployeeStatus.getDescByCode(eVO.getStatus()));
                Long employeeId = eVO.getId();
                EmployeePositioninfo positionInfo = employeePositioninfoService.findByEmployeeId(employeeId);
                eVO.setPositionLevel(positionInfo.getPositionLevel());
                eVO.setEntryAddress(!EntryAddress.ELSE_ADDRESS.getValue().equals(positionInfo.getEntryAddress())?positionInfo.getEntryAddress():positionInfo.getOtherAddress());
    
                if (null != positionInfo.getLeader()) {
                    Employee employee = employeeDao.getOne(positionInfo.getLeader());
                    if (employee != null) {
                        eVO.setLeader(employee.getEmployeeName());
                        eVO.setLeaderPhone(employee.getPhoneNumber());
                    }
                }
    
                EmployeePositioninfo leaderPositionInfo = employeePositioninfoService.findByEmployeeId(employeeId);
                eVO.setLeaderPositionLevel(leaderPositionInfo.getPositionLevel());
    
                List<String> sectionFullNames = sectionDao.findSectionFullNameByEmployeeId(employeeId);
                if ((!sectionFullNames.isEmpty())&&sectionFullNames.size()==1){
                    eVO.setSection(sectionFullNames.get(0));
                }else {
                    new BusinessException("该待入职员工存在多个部门,请检查!");
                }
    
            }
    
            return page;
    
        }

**5）复杂查询**

多表联合、多条件、分页查询；或特殊封装，减少代码量

多表分页条件查询：特别是复杂报表的查询，JdbcTemplate

@Override
    public List<SelectVO<String, Long>> applicationTypeSelectList() {
        return jdbcTemplate.query("select typeName, id from emp_application_type where enabled = 0 order by orderBy asc ", new RowMapper<SelectVO<String, Long>>() {
            @Override
            public SelectVO<String, Long> mapRow(ResultSet rs, int rowNum) throws SQLException {
                return SelectVO.newSelectVO(rs.getString(1), rs.getLong(2));
            }
        });
    }

### 2、Dao代码示例 ###

1、EmployeeContractDao 示例

package com.myfutech.employee.service.provider.dao;
    
    import com.myfutech.common.spring.jpa.base.BaseDao;
    import com.myfutech.employee.service.provider.model.EmployeeContract;
    import org.springframework.data.jpa.repository.Modifying;
    import org.springframework.data.jpa.repository.Query;
    
    import java.util.List;
    
    /**
     * 员工合同信息表 dao接口
     */
    public interface EmployeeContractDao extends BaseDao<EmployeeContract>{
    
        @Query(value = "SELECT * FROM hr_employee_contract t WHERE t.employeeId=?1 AND t.parentId=0 AND  t.deleteStatus=0",nativeQuery = true)
        List<EmployeeContract> findMainContract(Long employeeId);
    
        @Query(value = "SELECT count(1) FROM hr_employee_contract t WHERE t.employeeId=?1 AND t.parentId=0 AND  t.deleteStatus=0",nativeQuery = true)
        long countMainContract(Long employeeId);
    
        @Query(value = "SELECT count(1) FROM hr_employee_contract t WHERE t.parentId=?1 AND t.parentId=0 AND  t.deleteStatus=0",nativeQuery = true)
        long countSubContract(Long parentId);
    
        @Query(value = "SELECT * FROM hr_employee_contract t WHERE t.parentId=?1 AND  t.deleteStatus=0 ",nativeQuery = true)
        List<EmployeeContract> findSubContract(Long parentId);
    
        @Query(value = "SELECT * FROM hr_employee_contract t WHERE t.parentId=?1  AND  t.deleteStatus=0 order by t.createTime desc limit 1",nativeQuery = true)
        EmployeeContract lastOne(Long parentId);
    
        @Modifying
        @Query(value="UPDATE hr_employee_contract t SET t.deleteStatus=1 WHERE t.employeeId=?1  AND  t.deleteStatus=0",nativeQuery = true)
        void deleteByEmployeeId(Long employeeId);
    
        @Modifying
        @Query(value="UPDATE hr_employee_contract t SET t.deleteStatus=1 WHERE t.id=?1",nativeQuery = true)
        void delete(Long id);
    
        @Modifying
        @Query(value="UPDATE hr_employee_contract t SET t.deleteStatus=1 WHERE t.parentId=?1",nativeQuery = true)
        void deleteSubByParentId(Long parentId);
    
    }

2、BaseDao示例

package com.myfutech.common.spring.jpa.base;
    
    import com.myfutech.common.util.vo.Page;
    import com.myfutech.common.util.vo.Pageable;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.repository.NoRepositoryBean;
    
    import java.util.List;
    import java.util.Map;
    
    @NoRepositoryBean
    public interface BaseDao<T> extends JpaRepository<T,Long> {
    
        /**
         *  SQL 计数
         */
        long nativeCount(String sql, Map<String, Object> params);
    
        /**
         *  变量使用？
         *  SQL 计数
         */
        long nativeCount(String sql, Object... params);
    
        /**
         * SQL 查询全部
         */
        <E> List<E> nativeFindAll(String sql, Map<String, Object> params, Class<E> domainClazz);
    
        /**
         * 变量使用？
         * SQL 查询全部
         */
        <E> List<E> nativeFindAll(String sql, Class<E> domainClazz, Object... params);
    
        /**
         * SQL 查询单个
         */
        <E> E nativeFindOne(String sql, Map<String, Object> params, Class<E> domainClazz);
    
        /**
         *  变量使用？
         * SQL 查询单个
         */
        <E> E nativeFindOne(String sql, Class<E> domainClazz, Object... params);
    
        /**
         * SQL 查询分页
         */
        <E> Page<E> nativeFindPage(String sql, Map<String, Object> params, Class<E> domainClazz, Pageable pageable);
    
        /**
         * 变量使用？
         * SQL 查询分页
         */
        <E> Page<E> nativeFindPage(String sql, Class<E> domainClazz, Pageable pageable, Object... params);
    
        /**
         *  只保存非空字段
         */
        T saveNotNull(T t);
    
        /**
         * Returns a reference to the entity with the given identifier.
         *
         * @param id must not be {@literal null}.
         * @return a reference to the entity with the given identifier.
         */
        @Override
        T getOne(Long id);
    }

3、BaseJpaRepositoryImpl是BaseDao实现类

package com.myfutech.common.spring.jpa.base.impl;
    
    import com.myfutech.common.spring.jpa.base.BaseDao;
    import com.myfutech.common.util.exception.DaoException;
    import com.myfutech.common.util.vo.Page;
    import com.myfutech.common.util.vo.Pageable;
    import org.hibernate.query.NativeQuery;
    import org.hibernate.transform.Transformers;
    import org.springframework.data.jpa.repository.support.JpaEntityInformation;
    import org.springframework.data.jpa.repository.support.SimpleJpaRepository;
    import org.springframework.transaction.annotation.Transactional;
    import org.springframework.util.Assert;
    
    import javax.persistence.Entity;
    import javax.persistence.EntityManager;
    import javax.persistence.Query;
    import java.lang.reflect.Field;
    import java.lang.reflect.Modifier;
    import java.math.BigInteger;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    public class BaseJpaRepositoryImpl<T> extends SimpleJpaRepository<T,Long> implements BaseDao<T> {
    
        private final static Map<Class,AliasToBeanResultTransformer> TRANSFORMER_MAP = new HashMap<>();
    
        private final EntityManager entityManager;
        private final JpaEntityInformation<T, Long> entityInformation;
    
        public BaseJpaRepositoryImpl(JpaEntityInformation<T, Long> entityInformation, EntityManager entityManager) {
            super(entityInformation, entityManager);
            this.entityManager = entityManager;
            this.entityInformation = entityInformation;
        }
    
        @Override
        public T getOne(Long id) {
            Assert.notNull(id, "The given id must not be null!");
            return findById(id).orElse(null);
        }
    
        @Override
        public long nativeCount(String sql, Object... params) {
            Query countQuery = entityManager.createNativeQuery(sql);
            initParams(params, countQuery);
            BigInteger records = (BigInteger) countQuery.getSingleResult();
            if(records == null || records.longValue() < 1){
                return 0;
            }
            return records.longValue();
        }
    
        @Override
        public long nativeCount(String sql, Map<String, Object> params) {
            Query countQuery = entityManager.createNativeQuery(sql);
            initParams(params, countQuery);
            BigInteger records = (BigInteger) countQuery.getSingleResult();
            if(records == null || records.longValue() < 1){
               return 0;
            }
            return records.longValue();
        }
    
        @Override
        public <E> List<E> nativeFindAll(String sql, Map<String, Object> params, Class<E> domainClazz) {
            Query query = createLocalQuery(domainClazz , sql);
            initParams(params, query);
            return query.getResultList();
        }
    
        @Override
        public <E> List<E> nativeFindAll(String sql, Class<E> domainClazz, Object... params) {
            Query query = createLocalQuery(domainClazz , sql);
            initParams(params, query);
            return query.getResultList();
        }
    
        @Override
        public <E> E nativeFindOne(String sql, Map<String, Object> params, Class<E> domainClazz) {
            List<E> data = nativeFindAll(sql, params, domainClazz);
            if (data == null  || data.size() == 0){
                return null;
            }
            if (data.size() != 1){
                throw new DaoException("查询结果不止一个");
            }
            return data.get(0);
        }
    
        @Override
        public <E> E nativeFindOne(String sql, Class<E> domainClazz, Object... params) {
            List<E> data = nativeFindAll(sql, domainClazz, params);
            if (data == null || data.size() == 0 ){
                return null;
            }
            if (data.size() != 1){
                throw new DaoException("查询结果不止一个");
            }
            return data.get(0);
        }
    
        @Override
        public <E> Page<E> nativeFindPage(String sql, Class<E> domainClazz, Pageable pageable, Object... params) {
            if(pageable == null){
                return new Page<>(nativeFindAll(sql,domainClazz, params));
            }
    
            String countSql = "select count(1) from (" + sql + ") tmp";
            Long totalCount = nativeCount(countSql, params);
            pageable.setTotalNum(totalCount);
    
            if (totalCount <= 0){
                return new Page<>(pageable);
            }
            Query query = createLocalQuery(domainClazz , sql);
            initParams(params, query);
            int startIndex = (pageable.getPageNum() - 1) * pageable.getPageSize();
    
            if (startIndex > totalCount){
                return new Page<>(pageable);
            }
    
            query.setFirstResult(startIndex);
            query.setMaxResults(pageable.getPageSize());
            List data = query.getResultList();
            return new Page<E>(pageable,data);
        }
    
        @Override
        public <E> Page<E> nativeFindPage(String sql, Map<String, Object> params, Class<E> domainClazz, Pageable pageable) {
            if(pageable == null){
                return new Page<>(nativeFindAll(sql,params,domainClazz));
            }
    
            String countSql = "select count(1) from (" + sql + ") tmp";
            Long totalCount = nativeCount(countSql, params);
            pageable.setTotalNum(totalCount);
    
            if (totalCount <= 0){
                return new Page<>(pageable);
            }
            Query query = createLocalQuery(domainClazz , sql);
            initParams(params, query);
            int startIndex = (pageable.getPageNum() - 1) * pageable.getPageSize();
    
            if (startIndex > totalCount){
                return new Page<>(pageable);
            }
    
            query.setFirstResult(startIndex);
            query.setMaxResults(pageable.getPageSize());
            List data = query.getResultList();
            return new Page<E>(pageable,data);
        }
    
        private void initParams(Map<String, Object> params, Query query) {
            if (params != null && !params.isEmpty()){
                for (String key : params.keySet()) {
                    query.setParameter(key , params.get(key));
                }
            }
        }
    
        private void initParams(Object[] params, Query query) {
            if (params != null && params.length != 0){
                for (int i = 0; i < params.length; i++) {
                    query.setParameter(i+1 , params[i]);
    
                }
            }
        }
    
        private Query createLocalQuery(Class<?> domainClazz , String sql){
            Query query = null;
            if(domainClazz != null){
                if(domainClazz.getAnnotation(Entity.class) != null){
                    query = entityManager.createNativeQuery( sql , domainClazz);
                }else{
                    query = entityManager.createNativeQuery( sql);
                    if(domainClazz.isAssignableFrom(Map.class)){
                        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                    }else if (domainClazz.isAssignableFrom(List.class)){
                        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.TO_LIST);
                    }else {
                        AliasToBeanResultTransformer transformer = TRANSFORMER_MAP.get(domainClazz);
                        if (transformer == null){
                            synchronized (TRANSFORMER_MAP){
                                transformer = TRANSFORMER_MAP.get(domainClazz);
                                if (transformer == null){
                                    transformer = new AliasToBeanResultTransformer(domainClazz);
                                    TRANSFORMER_MAP.put(domainClazz, transformer);
                                }
                            }
                        }
                        query.unwrap(NativeQuery.class).setResultTransformer(transformer);
                    }
                }
            }else {
                query = entityManager.createNativeQuery( sql);
            }
            return  query;
        }
    
        @Override
        @Transactional(rollbackFor = Exception.class)
        public T saveNotNull(T t) {
            Long id =  entityInformation.getId(t);
    
            if (id == null){
                return save(t);
            }
    
           T old = findById(id).orElse(null);
            if (old == null){
                throw new DaoException("没有找到对应对象");
            }
            Field[] fields = t.getClass().getDeclaredFields();
            try {
                for (Field field : fields) {
                    //去除静态属性
                    if (Modifier.isStatic(field.getModifiers())){
                        continue;
                    }
    
                    field.setAccessible(true);
                    Object value = field.get(t);
                    if (value == null){
                        continue;
                    }
                    field.set(old, value);
                }
            } catch (IllegalAccessException e) {
               throw new DaoException("对象属性存取权限错误", e);
            }
            return saveAndFlush(old);
        }
    }