如何从JPA的本机查询中提取别名 [英] How to pull an alias from native query in JPA

问题描述

我正在尝试从JPA中的本机查询中提取一个别名，类似(SUM，COUNT)，那么如果我完美地提取了SUM或COUNT，该方法可以返回一个整数(仅当我单独提取它时才可以) . 但是我如何将其与其余物体拉在一起?这是我正在尝试做的一个示例

I'm trying to pull an alias from native query in JPA, something like (SUM,COUNT), Well the method can return an integer if i pulled SUM or COUNT perfectly (ONLY if i pulled it alone) . but how can i pull it with the rest of object? here is a sample what i am trying to do

@Entity
@Table("hotels")

public class Hotel {
    @Column(name="id")
    @Id
    private int hotelId;

    @Column(name="hotel_name")
    private String hotelName;
    @OneToMany
    private List<Availability>list;

    private int avaialbeCount; //this one should be Aliased and need to be pulled by none column
}

存储库

public interface HotelRepository extends JpaRepository<Hotel,Integer>{
@Query(value="select h.*,a.count(1) as avaialbeCount from hotels h INNER JOIN availability a on (a.hotel_id=h.hotel_id) group by a.date",nativeQuery=true)
public List<Hotel> getHotels();

}

在上述存储库中.我正在尝试获取带有酒店列的avaialbeCount，但是我无法拉出它，但是我可以通过删除select h.*来拉出它，并保持select COUNT only并使该方法返回Integer而不是Hotel

in the above repository. im trying to get avaialbeCount with hotel columns but im unable to pull it, however i can pull it by removing the select h.* and keep select COUNT only and make the method returns Integer instead of Hotel

推荐答案

您可以使用JPQL，诸如此类

You can use JPQL, something like this

@Query("SELECT new test.Hotel(h.hotelName, count(h)) FROM Hotel h GROUP BY h.hotelName")

要使用此new test.Hotel(h.hotelName, count(h))构造，您需要类似

to use this new test.Hotel(h.hotelName, count(h)) construction, you need constructor like

public Hotel(String hotelName, Long avaialbeCount) {
    this.hotelName = hotelName;
    this.avaialbeCount = avaialbeCount;
} 

示例:

存储库:

package test;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface HotelRepo extends JpaRepository<Hotel, Long> {
    @Query("SELECT new test.Hotel(h.hotelName, count(h)) FROM Hotel h GROUP BY h.hotelName")
    List<Hotel> getHotelsGroupByName();
}

实体:

package test;
import javax.persistence.*;

@Entity
@Table(name = "hotels")
public class Hotel {

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

@Column(name = "hotel_name")
private String hotelName;

@Transient
private Long avaialbeCount;

public Hotel() {
}

public Hotel(String hotelName) {
    this.hotelName = hotelName;
}

public Hotel(String hotelName, Long avaialbeCount) {
    this.hotelName = hotelName;
    this.avaialbeCount = avaialbeCount;
}

@Override
public String toString() {
    return "Hotel{" +
            "hotelId=" + hotelId +
            ", hotelName='" + hotelName + '\'' +
            ", avaialbeCount=" + avaialbeCount +
            '}';
    }
}

@Transient批注用于指示字段不会在数据库中保留.

@Transient annotation is used to indicate that a field is not to be persisted in the database.

这篇关于如何从JPA的本机查询中提取别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！