注解@Query手写本地sql反参
查询使用*
当使用*查询所有时，会自动封装到对应的实体类上
@Query(value = "select r.* from VBAP3_RESULT_INFO r " +
"left join VBAP3_SEC_USER secUser on r.sec_user_id=secUser.id " +
"left join VBAP3_SEC_USER creatUser on r.created_by_id=creatUser.id " +
"where secUser.id<>?1 and creatUser.id<>?2 and r.creator=?3",nativeQuery = true)
List<ResultInfo> testOne(String secUserId,String createdById,Boolean creator);
如果想要自定义反参可以使用@NamedNativeQuery与@SqlResultSetMapping使用
ResultInfo实体类
package com.bonc.vbap.domain;
import com.bonc.BaseDomain;
import com.bonc.security.domain.SecGroup;
import com.bonc.security.domain.SecOrg;
import com.bonc.security.domain.SecUser;
import org.hibernate.annotations.GenericGenerator;
import org.hibernate.annotations.Parameter;
import javax.persistence.Cacheable;
import javax.persistence.ColumnResult;
import javax.persistence.ConstructorResult;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.SqlResultSetMappings;
import javax.persistence.Table;
import java.io.Serializable;
import java.util.Date;
/**
* 作品分享信息
*
* @author zr
*
*/
@NamedNativeQueries({
@NamedNativeQuery(
name="ResultInfo.findToBeSharedResultInfoList",
resultClass = ResultInfo.class,
query = "select " +
"r.display_name displayName," +
"result.id resultId," +
"result.created_date resultCreateDate," +
"creatUserTwo.display_name resultCreatedByDisplayName" +
" from VBAP3_RESULT_INFO r " +
"left join VBAP3_SEC_USER secUser on r.sec_user_id=secUser.id " +
"left join VBAP3_SEC_USER creatUserOne on r.created_by_id=creatUserOne.id " +
"left join vbap3_result result on r.RESULT_ID=result.id " +
"left join VBAP3_SEC_USER creatUserTwo on result.created_by_id=creatUserTwo.id " +
"left join VBAP3_CATEGORY category on r.CATEGORY_ID=category.id " +
"where secUser.id<>:secUserId " +
"and creatUserOne.id<>:createdById " +
"and r.creator=:creator " +
"and if(IFNULL(:displayName,'')!='',r.display_name like :displayName,1=1) " +
"and if(IFNULL(:categoryId,'')!='',category.id=:categoryId,1=1) " +
"order by r.created_date desc " +
"limit :pageNumber,:pageSize",
resultSetMapping = "resultMapTwo"
),
@NamedNativeQuery(
name="ResultInfo.findToBeSharedResultInfoListOther",
resultClass = ResultInfo.class,
query = "select " +
"r.display_name displayName," +
"result.id resultId," +
"result.created_date resultCreateDate," +
"creatUserTwo.display_name resultCreatedByDisplayName" +
" from VBAP3_RESULT_INFO r " +
"left join VBAP3_SEC_USER secUser on r.sec_user_id=secUser.id " +
"left join VBAP3_SEC_USER creatUserOne on r.created_by_id=creatUserOne.id " +
"left join vbap3_result result on r.RESULT_ID=result.id " +
"left join VBAP3_SEC_USER creatUserTwo on result.created_by_id=creatUserTwo.id " +
"left join VBAP3_CATEGORY category on r.CATEGORY_ID=category.id " +
"where secUser.id<>:secUserId " +
"and result.id not in :beSharedIdList " +
"and creatUserOne.id<>:createdById " +
"and r.creator=:creator " +
"and if(IFNULL(:displayName,'')!='',r.display_name like :displayName,1=1) " +
"and if(IFNULL(:categoryId,'')!='',category.id=:categoryId,1=1) " +
"order by r.created_date desc " +
"limit :pageNumber,:pageSize",
resultSetMapping = "resultMapTwo"
),
})
@SqlResultSetMappings({
@SqlResultSetMapping(
name = "resultMap",
classes = @ConstructorResult(
targetClass = ResultInfo.class,
columns = {
@ColumnResult(name = "categoryDisplayName", type = String.class),
@ColumnResult(name = "categoryId", type = String.class),
@ColumnResult(name = "categoryPath", type = String.class),
//@ColumnResult(name = "resultContent", type = String.class),
//@ColumnResult(name = "resulDisplayName", type = String.class),
@ColumnResult(name = "resultId", type = String.class),
@ColumnResult(name = "resultMemo", type = String.class),
@ColumnResult(name = "resultPublish", type = Boolean.class),
//@ColumnResult(name = "resultPublishContent", type = String.class),
@ColumnResult(name = "resultState", type = Integer.class),
@ColumnResult(name = "resultThumbnail", type = String.class),
@ColumnResult(name = "resultType", type = String.class),
@ColumnResult(name = "resultVersion", type = Integer.class),
@ColumnResult(name = "creator", type = Boolean.class),
@ColumnResult(name = "displayName", type = String.class),
@ColumnResult(name = "id", type = String.class),
@ColumnResult(name = "memo", type = String.class),
@ColumnResult(name = "shareAuthority", type = String.class),
@ColumnResult(name = "version", type = Integer.class),
@ColumnResult(name = "publish", type = Boolean.class),
@ColumnResult(name = "lastModifiedDate", type = Date.class),
@ColumnResult(name = "userDisplayName", type = String.class),
}
)
),
@SqlResultSetMapping(
name = "resultMapTwo",
classes = @ConstructorResult(
targetClass = ResultInfo.class,
columns = {
@ColumnResult(name = "displayName", type = String.class),
@ColumnResult(name = "resultId", type = String.class),
@ColumnResult(name = "resultCreateDate", type = Date.class),
@ColumnResult(name = "resultCreatedByDisplayName", type = String.class),
}
)
)
})
@Entity
@Cacheable
@Table(name = "VBAP3_RESULT_INFO")
public class ResultInfo extends BaseDomain implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
/**
* 作品
*/
@ManyToOne
@JoinColumn(name = "RESULT_ID")
private Result result;
/**
* 使用人
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "SEC_USER_ID")
private SecUser secUser;
/**
* 使用组
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "SEC_GROUP_ID")
private SecGroup secGroup;
/**
* 使用组织机构
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "SEC_ORG_ID")
private SecOrg secOrg;
/**
* 是否创建人
*/
private Boolean creator;
/**
* 分享权限
*/
private String shareAuthority;
@ManyToOne
@JoinColumn(name = "CATEGORY_ID")
private Category category;
/**
* 是否发布(0:未发布，1:已发布)
*/
private Boolean publish;
@Id
@GeneratedValue(generator = "RESULT_INFO")
@GenericGenerator(name = "RESULT_INFO", strategy = "com.bonc.SecTableGenerator",
parameters = {
@Parameter(name="table_name",value = "VBAP3_SYS_PK_GENERATOR"),
@Parameter(name="value_column_name",value = "VALUE_COLUMN_NAME"),
@Parameter(name="segment_column_name",value = "PK_COLUMN_NAME"),
@Parameter(name="segment_value",value = "ResultInfo_id"),
@Parameter(name="increment_size",value = "10"),
@Parameter(name="optimizer",value = "pooled-lo")
})
private String id;
@Override
public String getId() {
return id;
}
@Override
public void setId(String id) {
this.id = id;
}
public Result getResult() {
return result;
}
public void setResult(Result result) {
this.result = result;
}
public SecUser getSecUser() {
return secUser;
}
public void setSecUser(SecUser secUser) {
this.secUser = secUser;
}
public SecGroup getSecGroup() {
return secGroup;
}
public void setSecGroup(SecGroup secGroup) {
this.secGroup = secGroup;
}
public SecOrg getSecOrg() {
return secOrg;
}
public void setSecOrg(SecOrg secOrg) {
this.secOrg = secOrg;
}
public Boolean getCreator() {
return creator;
}
public void setCreator(Boolean creator) {
this.creator = creator;
}
public String getShareAuthority() {
return shareAuthority;
}
public void setShareAuthority(String shareAuthority) {
this.shareAuthority = shareAuthority;
}
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
@Override
public String toString() {
return String.format("%s[%s,%nresult=%s,%nsecUser=%s,creator=%s,shareAuthority=%s,category=%s]",
this.getClass().getSimpleName(), super.toString(), result.toString(), secUser.getUsername(),
creator, shareAuthority, category == null ? "" : category.getDisplayName());
}
public Boolean getPublish() {
return publish;
}
public void setPublish(Boolean publish) {
this.publish = publish;
}
public ResultInfo() {
}
public ResultInfo(Category category,Result result,String displayName) {
this.category=category;
this.result = result;
this.setDisplayName(displayName);
}
public ResultInfo(CategoryType categoryType,String categoryDisplayName,String categoryId,String categoryPath,
String resultContent,String resulDisplayName,String resultId,String resultMemo,Boolean resultPublish,String resultPublishContent,Integer resultState,String resultThumbnail,String resultType,Integer resultVersion,
Boolean creator,String displayName,String id,String memo,String shareAuthority,Integer version) {
Category category = new Category();
category.setCategoryType(categoryType);
category.setDisplayName(categoryDisplayName);
category.setId(categoryId);
category.setPath(categoryPath);
Result result = new Result();
result.setContent(resultContent);
result.setDisplayName(resulDisplayName);
result.setId(resultId);
result.setMemo(resultMemo);
result.setPublish(resultPublish);
result.setPublishContent(resultPublishContent);
result.setState(resultState);
result.setThumbnail(resultThumbnail);
result.setType(resultType);
result.setVersion(resultVersion);
this.category= category;
this.result=result;
this.creator=creator;
this.setDisplayName(displayName);
this.id=id;
this.setMemo(memo);
this.shareAuthority=shareAuthority;
this.setVersion(version);
}
public ResultInfo(String categoryDisplayName,String categoryId,String categoryPath,
String resultId,String resultMemo,Boolean resultPublish,Integer resultState,String resultThumbnail,String resultType,Integer resultVersion,
Boolean creator,String displayName,String id,String memo,String shareAuthority,Integer version,Boolean publish,Date lastModifiedDate,
String userDisplayName) {
Category category = new Category();
category.setDisplayName(categoryDisplayName);
category.setId(categoryId);
category.setPath(categoryPath);
Result result = new Result();
//result.setContent(resultContent);
//result.setDisplayName(resulDisplayName);
result.setId(resultId);
result.setMemo(resultMemo);
result.setPublish(resultPublish);
//result.setPublishContent(resultPublishContent);
result.setState(resultState);
result.setThumbnail(resultThumbnail);
result.setType(resultType);
result.setVersion(resultVersion);
SecUser secUser=new SecUser();
secUser.setDisplayName(userDisplayName);
this.category= category;
this.result=result;
this.creator=creator;
this.setDisplayName(displayName);
this.id=id;
this.setMemo(memo);
this.shareAuthority=shareAuthority;
this.setVersion(version);
this.publish=publish;
this.setLastModifiedDate(lastModifiedDate);
this.setCreatedBy(secUser);
}
public ResultInfo(Boolean creator,String displayName,String id,String memo,String shareAuthority,Integer version) {
this.creator=creator;
this.setDisplayName(displayName);
this.id=id;
this.setMemo(memo);
this.shareAuthority=shareAuthority;
this.setVersion(version);
}
public ResultInfo(String resultId) {
this.id = resultId;
}
public ResultInfo(String displayName,String resultId,Date resultCreateDate,String resultCreatedByDisplayName) {
this.setDisplayName(displayName);
Result result = new Result();
result.setCreatedDate(resultCreateDate);
SecUser secUser = new SecUser();
secUser.setDisplayName(resultCreatedByDisplayName);
result.setCreatedBy(secUser);
result.setId(resultId);
this.result=result;
}
}
注意：实体类必须有相应的构造方法
public ResultInfo(String displayName,String resultId,Date resultCreateDate,String resultCreatedByDisplayName) {
this.setDisplayName(displayName);
Result result = new Result();
result.setCreatedDate(resultCreateDate);
SecUser secUser = new SecUser();
secUser.setDisplayName(resultCreatedByDisplayName);
result.setCreatedBy(secUser);
result.setId(resultId);
this.result=result;
}
Repository
package com.bonc.vbap.repository;
import com.bonc.vbap.domain.ResultInfo;
import com.bonc.vbap.utils.AclDomainConstant;
import com.bonc.vbap.utils.SecAuthorityConstant;
import com.bonc.vbap.utils.SecAuthorityConstant.ResultAuthority;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.TiJpaRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.data.rest.core.annotation.RepositoryRestResource;
import org.springframework.data.rest.core.annotation.RestResource;
import org.springframework.security.access.prepost.PostAuthorize;
import org.springframework.security.access.prepost.PreAuthorize;
import java.util.List;
import java.util.Set;
/**
* 作品分享
* @author zr
*
*/
@RepositoryRestResource
public interface ResultInfoRepository
extends TiJpaRepository<ResultInfo, String>, JpaSpecificationExecutor<ResultInfo> {
@RestResource(exported = false)
List<ResultInfo> findToBeSharedResultInfoList(@Param("secUserId") String secUserId,
@Param("createdById") String createdById,
@Param("creator") Boolean creator,
@Param("displayName")String displayName,
@Param("categoryId")String categoryId,
@Param("pageNumber") Integer pageNumber,
@Param("pageSize") Integer pageSize);
@RestResource(exported = false)
List<ResultInfo> findToBeSharedResultInfoListOther(@Param("beSharedIdList") List<String>beSharedIdList,
@Param("secUserId") String secUserId,
@Param("createdById") String createdById,
@Param("creator") Boolean creator,
@Param("displayName")String displayName,
@Param("categoryId")String categoryId,
@Param("pageNumber") Integer pageNumber,
@Param("pageSize") Integer pageSize);
}
参考文档：
cnblogs.com/caofanqi/p/11863594.html
https://my.oschina.net/joryqiao/blog/4522953
浙公网安备 33010602011771号