准备
实体
@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();
参考
- spel-support-in-spring-data-jpa-query-definitions
- Spring Data JPA @Query
- Spring JPA selecting specific columns
- spring-data-jpa-query-with-parameter-properties
- Spring @Query annotation with enum parameter
- Can I use enum parameter into JpaRepository nativeQuery?
- Spring Expression Language (SpEL)
- Using Literals in JPQL
- Persisting Enums in JPA
网友评论