java为什么oracle表ALL_TAB_列的本机查询由于列无效而失败?
为什么我在spring boot hibernate createNativeQuery
中出现这个错误Invalid column name
?我正在尝试从oracle数据库获取表信息。我把我的查询放在db eaver它成功了,告诉我本地查询的最佳实践是什么,请
Hibernate: SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='BPN_AKTA'
2020-09-25 10:27:53.005 WARN 60208 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 17006, SQLState: 99999
2020-09-25 10:27:53.005 ERROR 60208 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : Invalid column name
2020-09-25 10:27:53.006 ERROR 60208 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query] with root cause
java.sql.SQLException: Invalid column name
Query q= em.createNativeQuery("SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='PC'",QueryTableAttModel.class);
List<QueryTableAttModel> tableColl = q.getResultList();
我的模型
@Entity(name="ALL_TAB_COLUMNS")
public class QueryTableAttModel {
public String getTABLE_NAME() {
return TABLE_NAME;
}
public void setTABLE_NAME(String TABLE_NAME) {
this.TABLE_NAME = TABLE_NAME;
}
public String getCOLUMN_NAME() {
return COLUMN_NAME;
}
public void setCOLUMN_NAME(String COLUMN_NAME) {
this.COLUMN_NAME = COLUMN_NAME;
}
public String getDATA_TYPE() {
return DATA_TYPE;
}
public void setDATA_TYPE(String DATA_TYPE) {
this.DATA_TYPE = DATA_TYPE;
}
private String TABLE_NAME;
private String COLUMN_NAME;
private String DATA_TYPE;
private String id;
@Id
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
}
# 1 楼答案
问题是表
ALL_TAB_COLUMNS
没有单列主键。应该使用基于OWNER
、TABLE_NAME
、COLUMN_NAME
列的复合主键例如,可以使用
@IdClass
annotation进行复合主键映射:其中
QueryTableAttModelPK
类是然后可以使用本机查询:
jpql/hql查询:
或者通过PK查找实体: