java - 为什么对 oracle 表 ALL_TAB_COLUMNS 的本机查询由于列无效而失败?
问题描述
为什么我Invalid column name
在 spring boot hibernate 中得到这个错误createNativeQuery
?我正在尝试从 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;
}
}
解决方案
问题是该表ALL_TAB_COLUMNS
没有单列主键。您应该使用基于OWNER
, TABLE_NAME
,COLUMN_NAME
列的复合主键。
例如,您可以使用复合主键映射的@IdClass
注释:
@Entity
@Table(name = "ALL_TAB_COLUMNS")
@IdClass(QueryTableAttModelPK.class)
public class QueryTableAttModel
{
private String owner;
private String tableName;
private String columnName;
private String dataType;
public QueryTableAttModel()
{
}
@Id
@Column(name = "OWNER")
public String getOwner()
{
return owner;
}
public void setOwner(String owner)
{
this.owner = owner;
}
@Id
@Column(name = "TABLE_NAME")
public String getTableName()
{
return tableName;
}
public void setTableName(String tableName)
{
this.tableName = tableName;
}
@Id
@Column(name = "COLUMN_NAME")
public String getColumnName()
{
return columnName;
}
public void setColumnName(String columnName)
{
this.columnName = columnName;
}
@Column(name = "DATA_TYPE")
public String getDataType()
{
return dataType;
}
public void setDataType(String dataType)
{
this.dataType = dataType;
}
}
QueryTableAttModelPK
上课在哪里
import java.io.Serializable;
import java.util.Objects;
public class QueryTableAttModelPK implements Serializable
{
private String owner;
private String tableName;
private String columnName;
public QueryTableAttModelPK()
{
}
public QueryTableAttModelPK(String owner, String tableName, String columnName)
{
this.owner = owner;
this.tableName = tableName;
this.columnName = columnName;
}
public String getOwner()
{
return owner;
}
public void setOwner(String owner)
{
this.owner = owner;
}
public String getTableName()
{
return tableName;
}
public void setTableName(String tableName)
{
this.tableName = tableName;
}
public String getColumnName()
{
return columnName;
}
public void setColumnName(String columnName)
{
this.columnName = columnName;
}
@Override
public boolean equals(Object obj) {
if (this == obj) return true;
if (obj == null) return false;
if (getClass() != obj.getClass()) return false;
QueryTableAttModelPK other = (QueryTableAttModelPK) obj;
return Objects.equals(owner, other.owner)
&& Objects.equals(tableName, other.tableName)
&& Objects.equals(columnName, other.columnName);
}
@Override
public int hashCode() {
return Objects.hash(owner, tableName, columnName);
}
}
然后您可以使用本机查询:
List<QueryTableAttModel> results = em.createNativeQuery(
"select OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE from ALL_TAB_COLUMNS where TABLE_NAME = :table",
QueryTableAttModel.class)
.setParameter("table", "ALL_TAB_COLUMNS")
.getResultList();
jpql/hql 查询:
List<QueryTableAttModel> results = em.createQuery(
"select q from QueryTableAttModel q where q.tableName = :table",
QueryTableAttModel.class)
.setParameter("table", "ALL_TAB_COLUMNS")
.getResultList();
或通过PK查找实体:
QueryTableAttModel qTable = em.find(
QueryTableAttModel.class,
new QueryTableAttModelPK("SYS", "ALL_TAB_COLUMNS", "CHAR_LENGTH")
);
推荐阅读
- amazon-web-services - 什么是弹性网络接口 (ENI),您什么时候使用它?
- android - 如何使用颤振将图像转换为pdf?
- c# - Unity中无限循环背景?
- r - 如何在R中创建可视化数据表以快速可视化筛选数据
- c++ - 删除运算符似乎没有调用全局运算符删除重载
- python-social-auth - 将 django_social 添加到 INSTALLED_APPS 时出现“TypeError:'map' 类型的对象没有 len()”错误
- vba - 将存储在数组中的电子邮件地址放入 Outlook 电子邮件的“收件人”字段
- axios - 无法使用 axios 发出 POST 请求
- javascript - Vue Reactivity:为什么替换对象的属性(数组)不会触发更新
- java - Java中的Gson Json解析