Hibernate Native Query

Question

我想写一个本地查询'Select * from table'我的repo。表名和实体名不一致

运行查询时，

1如果我输入实体名称，它返回Table not found。

2如果我把表名放在query中，我得到Validation failed for query。

查询为

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

如果我使用"Select * from TariffPacks r2…， nativeQuery = true "，我得到的错误关税包不存在。如果我使用"Select * from RECHARGEDATAPACKS r2 ..， nativeQuery = true"，我得到Hibernate中的验证错误。

实体:

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

我有三个问题:1如何使hibernate寻找表名(RECHARGEDATAPACKS)，而使用nativeQuery = true?

2我可以写"Select * "在Hibernate中不使用nativeQuery = true?我想我必须写所有的列名，如TariffId, Operator, region ..但我希望实体能被归还。如何将所有列映射到输出中的实体?

是否有其他的方法来写单独的列名和映射到一个实体类?比如我写Select TariffId, Operator, region。我怎样才能直接领取关税单?

Answer 1

1如果我输入实体名称，它返回Table not found

如果您正在使用nativeQuery = true那么你必须写表名.

@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如果我把表名放在query中，我得到Validation failed for query。

我尝试了相同的代码，工作得很好。请详细说明错误/异常。

1如何使hibernate查找表名(RECHARGEDATAPACKS)而使用nativeQuery = true?

如果使用nativeQuery = true那么你必须提供表名只能，否则你可以用实体类名写HQL/JPQL.

2我可以写"Select * "在Hibernate中不使用nativeQuery = true?我想我必须写所有的列名，如TariffId, Operator, region ..但我希望实体能被归还。如何将所有列映射到输出中的实体?

你可以这样写:

@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是否有其他方法来编写单独的列名并将它们映射到实体类?比如我写Select TariffId, Operator, region。我怎样才能直接领取关税单?

你可以这样做:

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

，然后你可以提取。

Answer 2

使用本机查询时，必须使用表名，而不是实体名。您还必须使用数据库列名，而不是java字段名。

当使用非本机查询(称为JPQL或在hibernate中特定的HQL)时，您必须使用实体和字段名。语法为select e from Entity e ...，而不是select * from Entity e ...。