Hibernate Native Query与实体映射相关技术问题咨询

I'm facing a problem writing native queries in my repository where the entity name doesn't match the actual database table name. My entity class TariffPacks is mapped to the table RECHARGEDATAPACKS using the @Table(name = "RECHARGEDATAPACKS") annotation, but I'm running into two errors:

1. When I use the entity name TariffPacks as the table name with nativeQuery = true, I get an error saying "Tariffpacks does not exist".
2. When I use the actual table name RECHARGEDATAPACKS, I get a Hibernate validation error.

Here's my repository query code and entity class code:

Repository Query Methods #

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

Entity Class Code #

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)
    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 technical questions:

1. How can I get Hibernate to correctly recognize the actual database table name RECHARGEDATAPACKS when setting nativeQuery = true?
2. Without using nativeQuery = true, can I use Select * in Hibernate? If I have to specify column names, how do I map the query results back to the TariffPacks entity class?
3. When writing queries with specific column names (e.g., Select TariffId, Operator, regionname...), what other methods are available to map the results to the entity class besides the current approach? Also, how can I directly get a single field value like tariffId?

Answers #

1. Fixing native query table/column recognition

First off, when you enable nativeQuery = true, Hibernate passes your query straight to the database—so you must use the real database table name (RECHARGEDATAPACKS) instead of the entity name. The error you saw when using the actual table name is almost certainly about column name mismatches, not the table itself.

Looking at your entity, fields like TariffID use mixed casing, but most databases (Oracle, PostgreSQL, etc.) treat unquoted identifiers as lowercase by default. So when you write r2.TariffID in your native query, the database looks for a column named tariffid (all lowercase) which doesn't exist.

Here's how to fix it:

• Quote column names to preserve casing (syntax varies by database: Oracle/PostgreSQL use double quotes "TariffID", MySQL uses backticks `TariffID`)
• Or add explicit @Column annotations to your entity fields to clarify the mapping:

  @Column(name = "TariffID")
  private Long TariffID;
  @Column(name = "regionname")
  private String regionname;
  // Repeat for all fields where casing matters
  

Then update your native query to use RECHARGEDATAPACKS and properly cased columns. Example for Oracle:

@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 )

Also, your delete query is missing nativeQuery = true—that's a critical oversight! Without it, Hibernate treats it as a JPQL query and looks for the entity name instead of the table name. Add that flag to fix the validation error.

2. Using Select * in JPQL (no native query)

Yes, you can use the equivalent of Select * in JPQL—just select the entity alias instead of *. For example:

@Query("Select r2 from TariffPacks r2 where r2.TariffID = :tariffId and ...")
List<TariffPacks> findByCriteria(...);

JPQL will automatically fetch all fields and map them back to TariffPacks instances.

If you need to specify columns (for performance reasons), use the entity constructor in your query:

@Query("Select new com.yourpackage.TariffPacks(r2.packid, r2.TariffID, r2.operator, r2.regionname, ...) " +
       "from TariffPacks r2 where ...")
List<TariffPacks> findPartialTariffPacks(...);

Your existing @AllArgsConstructor should work here—just make sure the order of selected columns matches the constructor parameter order.

3. Mapping specific columns to entities & fetching single fields

Besides the constructor approach, here are other options:

a. @SqlResultSetMapping for native queries

Define a result set mapping directly on your entity to map selected columns to entity properties:

@Entity
@Table(name = "RECHARGEDATAPACKS")
@SqlResultSetMapping(
    name = "TariffPacksPartialMapping",
    entities = @EntityResult(
        entityClass = TariffPacks.class,
        fields = {
            @FieldResult(name = "packid", column = "packid"),
            @FieldResult(name = "TariffID", column = "TariffID"),
            @FieldResult(name = "operator", column = "operator"),
            // Add all columns you're selecting
        }
    )
)
public class TariffPacks {
    // ... existing entity code
}

Then reference this mapping in your native query:

@Query( value = "Select packid, TariffID, operator from RECHARGEDATAPACKS r2 where ...", 
        nativeQuery = true,
        resultSetMapping = "TariffPacksPartialMapping" )
List<TariffPacks> findPartialTariffPacks(...);

b. Fetching single field values

To get a single field like tariffId, just select that field directly and specify the return type:

• For JPQL:

  @Query("Select r2.TariffID from TariffPacks r2 where ...")
  List<Long> findTariffIdsByCriteria(...);
  

• For native queries:

  @Query( value = "Select \"TariffID\" from RECHARGEDATAPACKS r2 where ...", 
          nativeQuery = true )
  List<Long> findTariffIdsByCriteria(...);
  

If you expect only one result, use Optional<Long> instead of a list to avoid NoResultException:

@Query("Select r2.TariffID from TariffPacks r2 where ...")
Optional<Long> findSingleTariffId(...);

内容的提问来源于stack exchange，提问作者Joy