Hibernate Native Query

Question

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?

Answer 1

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.

Answer 2

When using native query you have to use the table name, not the entity name. You also have to use the database column names, instead of the java field names.

When using non-native queryies (known as JPQL or in hibernate specifically HQL) you have to use the entity and field names. Instead of select * from Entity e ... the syntax is select e from Entity e ....