代码之家 › 专栏 › 技术社区 › Sunny

将本机查询转换为JPQL

spring-data-jpa jpa hibernate sql java

Sunny · 技术社区 · 6 年前

我有一个本机查询,如下所示:-

SELECT T1.component_name,T1.model,T1.models_uid,r.revision_uid     from    
(    select cm.component_name,    cm.models_uid,    cm.model,    MAX(r.modified_epoch) 
 As created_at from cmla1.components_models cm    left join cmla1.revisions r on cm.models_uid=r.models_uid
 group by cm.component_name,cm.models_uid    ) T1  
left join cmla1.revisions r    on T1.created_at =r.modified_epoch

我想将这个本机查询转换为JPQL,并将结果转换为另一个POJO类ComponentModelData,如下所示:-

@Getter
@Setter
@NoArgsConstructor
public class ComponentModelData {
    @Override
    public String toString() {
        return "ComponentModelData [componentName=" + componentName + ", componentModelName=" + componentModelName
                + ", latestRevId=" + latestRevId + ", componentModelId=" + componentModelId + "]";
    }
    public ComponentModelData(String componentName, String componentModelName, Integer componentModelId,Integer latestRevId) {
        super();
        this.componentName = componentName;
        this.componentModelName = componentModelName;
        this.latestRevId = latestRevId;
        this.componentModelId = componentModelId;
    }

    private String componentName;
    private String componentModelName;
    private Integer latestRevId;
    private Integer componentModelId;

}

我正在使用下面的JPQL查询。但它给了我错误

 @Query("SELECT new dto.componentmodel.ComponentModelData(cm.name,cm.model,cm.id,r.revisionId)"+
            "   from (   select cm.name," + 
            "   cm.id," + 
            "   cm.model," + 
            "   MAX(r.modifiedEpoch) As created_at" + 
            "   from ComponentModel cm" + 
            "   left join ComponentModel.revisions r"+ 
            "   group by cm.name,cm.id    ) T1" + 
            "   left join ComponentModel.revisions r")
    List<ComponentModelData> findComponentModelRevisionData();

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 116 [SELECT new dto.componentmodel.ComponentModelData(cm.name,cm.model,cm.id,r.revisionId)   from (   select cm.name,   cm.id,  cm.model,   MAX(r.modifiedEpoch) As created_at  from cmla.entity.ComponentModel cm  left join ComponentModel.revisions r    group by cm.name,cm.id    ) T1  left join ComponentModel.revisions r]

@Entity
@Table(name = "components_models")
@Getter
@Setter
@NoArgsConstructor
public class ComponentModel implements Serializable {

    public ComponentModel(String comment, String alias, Integer id, Boolean isDeleted, String model, String name,
            String vendor) {
        super();
        this.comment = comment;
        this.alias = alias;
        this.id = id;
        this.isDeleted = isDeleted;
        this.model = model;
        this.name = name;
        this.vendor = vendor;
    }

    private static final long serialVersionUID = -695799009535171917L;
    @Column(name = "comment", columnDefinition = "text")
    private String comment;

    @Column(name = "alias", columnDefinition = "text")
    private String alias;

    @Column(name = "models_uid")
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "is_deleted")
    private Boolean isDeleted;

    @Column(name = "model", columnDefinition = "text") // ,unique = true, nullable = false
    private String model;

    @Column(name = "component_name", columnDefinition = "text")
    private String name;

    @Column(name = "vendor", columnDefinition = "text")
    private String vendor;

    @Column(name = "display_name", columnDefinition = "text")
    private String displayName;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "componentModel")
    private List<Revisions> revisions = new ArrayList<>();

}

有人知道我该怎么做吗。我们将非常感谢您的帮助!

0 回复 | 直到 6 年前