美文网首页
JPA使用之@Query的常用写法

JPA使用之@Query的常用写法

作者: Mr靖哥哥 | 来源:发表于2020-06-29 21:59 被阅读0次

    准备

    实体

    @Data
    @Table(name = "task_apply")
    @Entity
    public class TaskApply {
        @Id
        @GeneratedValue
        @Column(name = "apply_id")
        private Long applyId;
        
        private Integer status;
        
        private SyncType type;
        
        @Column(name = "task_message")
        private String taskMessage;
    }
    

    其中同步类型

    package com.charles.enums
    
    public enum SyncType {
        /**
         * 手动同步
         */
        MANUAL("M", "手动同步"),
        /**
         * 任务同步
         */
        SCHEDULED("S", "任务同步");
    
        private final String value;
        private final String text;
    
        SyncType(String value, String text) {
            this.value = value;
            this.text = text;
        }
    
        public String text() {
            return text;
        }
    
        public String getValue() {
            return value;
        }
    
        public static SyncType fromValue(String type) {
            for (SyncType value : SyncType.values()) {
                if (value.equals(type)) {
                    return value;
                }
            }
            return null;
        }
    }
    

    枚举的转换器

    import javax.persistence.AttributeConverter;
    
    public class SyncTypeConverter implements AttributeConverter<SyncType, String> {
    
        @Override
        public String convertToDatabaseColumn(SyncType e) {
            if (e == null) {
                return null;
            }
            return e.getValue();
        }
    
        @Override
        public SyncType convertToEntityAttribute(String value) {
            if (value == null) {
                return null;
            }
            return SyncType.fromValue(value);
        }
    }
    

    修改

    使用冒号传参

    @Modifying
    @Query("update TaskApply set status = :status where applyId = :applyId")
    void updateStatusByApplyId(@Param("applyId") Long applyId, @Param("status") Integer status);
    

    使用问号传参

    @Modifying
    @Query("update TaskApply set status = ?2 where applyId = ?1")
    void updateStatusByApplyId(Long applyId, Integer status);
    

    查询

    返回指定列第1种写法

    package com.charles.vo;
    
    @Data
    public class TaskMessageVO {
        private Long applyId;
        private String taskMessage;
    }
    
    @Query("select new com.charles.vo.TaskMessageVO(applyId, taskMessage) from TaskApply where applyId in (:applyIds)")
    List<TaskMessageVO> findTaskMessages(@Param("applyIds") List<Long> applyIds);
    

    返回指定列第2种写法

    @Query(nativeQuery = true, value = 
    "SELECT id as applyId, task_message as taskMessage FROM task_apply WHERE apply_id IN (:applyIds)")
    List<Object> findTaskMessages(@Param("applyIds") List<Long> applyIds);
    

    这种写法是nativeQuery,返回的结果中每个Object中返回的是一个数组,数组下标0对应的是applyId,下标1对应的是taskMessage。

    查询单列

    @Query("select distinct status from TaskApply where applyId in (:applyIds)")
    List<Integer> findDistinctStatus(@Param("applyIds") List<Long> applyIds);
    

    查询条件为常量

    @Query("select applyId from TaskApply where type <> com.charles.enums.SyncType.MANUAL")
    List<Long> findNotManualSyncApplyIds();
    

    参考

    相关文章

      网友评论

          本文标题:JPA使用之@Query的常用写法

          本文链接:https://www.haomeiwen.com/subject/tjxzfktx.html