[FIXED] Hibernate Native Query

Issue

I want to write a native query 'Select * in from table' my repo. The table name is different from the entity name.

When running the query,

1 if I put the entity name it returns Table not found.

2 if I put the table name in query, I get Validation failed for query.

The queries are

@Transactional
    @Query(
            value = "Select * from " +
                    "TariffPacks r2 where r2.TariffID = :tariffId " +
                    "and r2.regionname = :regionname " +
                    "and r2.category = :category " +
                    "and r2.amount = :amount " +
                    "and r2.operator = :operator", nativeQuery = true
    )
    List<TariffPacks> findByTariffID_RegionName_Category_Amount_Operator(
            @Param("tariffId") Long tariffId,
            @Param("regionname") String regionname,
            @Param("category") String category,
            @Param("amount") Integer amount,
            @Param("operator") String operator
    );

    @Transactional
    @Modifying
    @Query(
            value = "Delete from " +
                    "TariffPacks r2 where r2.TariffID = :tariffId " +
                    "and r2.regionname = :regionname " +
                    "and r2.category = :category " +
                    "and r2.amount = :amount " +
                    "and r2.operator = :operator"
    )
    List<TariffPacks> deleteByTariffID_RegionName_Category_Amount_Operator(
            @Param("tariffId") Long tariffId,
            @Param("regionname") String regionname,
            @Param("category") String category,
            @Param("amount") Integer amount,
            @Param("operator") String operator
    );

If I use "Select * from TariffPacks r2 ..., nativeQuery = true ", I get the error Tariffpacks doesn't exist. If I use "Select * from RECHARGEDATAPACKS r2 .., nativeQuery = true", I get Validation error in Hibernate.

The entity :

import lombok.*;

import javax.persistence.*;
import java.time.LocalDateTime;

@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "RECHARGEDATAPACKS")
public class TariffPacks {
    @Id
    @GeneratedValue(generator = "RECHARGEDATAPACKS_SEQ")
    @SequenceGenerator(name = "RECHARGEDATAPACKS_SEQ", sequenceName = "RECHARGEDATAPACKS_SEQ", allocationSize = 1)
    // @GeneratedValue(strategy = GenerationType.AUTO)
    private Long packid;

    private Long TariffID;
    private String operator;
    private String operatoralias;
    private String regionname;
    private String regionalias;
    private String category;
    private Integer amount;
    private String talktime;
    private String validity;
    private String description;
    private String billercategory;
    private String updatedOn;
    private String entryDate;

}

I have three questions : 1 How to make hibernate look for the table name (RECHARGEDATAPACKS) while using nativeQuery = true?

2 Can I write "Select * " in Hibernate without using nativeQuery = true? I think I have to write all the column names like TariffId, Operator, region .., but I want the entity to be returned. How to map all the columns to an entity in output?

3 Is there any other way to write individual column names and map them to an entity class? Like if I write Select TariffId, Operator, region .., how can I fetch the tariffId directly?

Solution

1 if I put the entity name it returns Table not found.

If you are using nativeQuery = true then you have to write Table name.

    @Transactional
@Query(value = "Select * from " + "RECHARGEDATAPACKS r2 where r2.TariffID = :tariffId "
        + "and r2.regionname = :regionname " + "and r2.category = :category " + "and r2.amount = :amount "
        + "and r2.operator = :operator", nativeQuery = true)
List<TariffPacks> findByTariffID_RegionName_Category_Amount_Operator(@Param("tariffId") Long tariffId,
        @Param("regionname") String regionname, @Param("category") String category, @Param("amount") Integer amount,
        @Param("operator") String operator);

2 if I put the table name in query, I get Validation failed for query.

I tried with the same code and is working fine. please specify the exact error/exception.

1 How to make hibernate look for the table name (RECHARGEDATAPACKS) while using nativeQuery = true?

If using nativeQuery = true then you have to provide table name only, else you can write HQL/JPQL with entity class name.

2 Can I write "Select * " in Hibernate without using nativeQuery = true? I think I have to write all the column names like TariffId, Operator, region .., but I want the entity to be returned. How to map all the columns to an entity in output?

You can write like this:

@Query(value = "from " + "TariffPacks r2 where r2.TariffID = :tariffId " + "and r2.regionname = :regionname " + "and r2.category = :category " + " and r2.amount = :amount " + "and r2.operator = :operator")

3 Is there any other way to write individual column names and map them to an entity class? Like if I write Select TariffId, Operator, region .., how can I fetch the tariffId directly?

You can do like this:

    @Query(value = "select TariffID, category from RECHARGEDATAPACKS where packid = :packid", nativeQuery = true)
List<Object[]> someMethodName(Long packid);

and then you can extract.

Answered By - Ajit Singh