CODEWITHSUNDEEP
springjpa
Basil Battikhi
Basil Battikhi

Reputation: 2668

How to pull an alias from native query in JPA

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
}

Repository

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();

}

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

Upvotes: 1

Views: 10546

Answers (1)

Andrii Vdovychenko
Andrii Vdovychenko

Reputation: 300

You can use JPQL, something like this

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

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;
}

Example:

Repository:

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();
}

Entity: 

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 annotation is used to indicate that a field is not to be persisted in the database.

Upvotes: 2

Related Questions