Oracle 在休眠时遇到问题 生成的查询，生成行号中包含 _ 的查询

Question

我正在使用休眠和使用条件查询，数据库是Oracle。下面是生成的查询。

">

WITH query AS (
      SELECT inner_query.*, 
             ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ 
      FROM ( 
           select this_.MODIFIEDTIME as MODIFIED1_1_1_, 
                  this_.FIELDNAME as FIELDNAM2_1_1_, 
                  this_.EMPNBR as EMPNBR3_1_1_
           from EMPLOYEEAUDIT this_ 
           left outer join EMPLOYEE employee2_ on this_.EMPNBR=employee2_.EMPNBR 
       ) inner_query 
) 
SELECT MODIFIED1_1_1_,FIELDNAM2_1_1_,EMPNBR3_1_1_
FROM query WHERE __hibernate_row_nr__ >= 1 
 AND __hibernate_row_nr__ < 10;"

它抛出以下错误：-SQL 错误： 911， SQLState： 22019ORA-00911：无效字符

但是，如果我使用 SQL 查询并使用 hibernate_row_nr__ 重新处理hibernate_row_nr并在 DbVisualizer 中运行它，它可以工作。Oracle 似乎无法将开头的"_"识别为有效字符。如何解决这个问题？

以下是我的java代码：-

public List<Employee> getEmployeeList(int start, int limit, AgSort sorter) throws UIFilterException {
    DetachedCriteria detached = getEmployeeListCriteria(sorter, filters);
    return (List<DealSetupAudit>) hibernateTemplate.findByCriteria(detached, start, limit);
}
    private DetachedCriteria getEmployeeListCriteria(AgSort sorter) throws UIFilterException {
    DetachedCriteria detached = getBaseCriteria(DealSetupAudit.class, sorter);

    return detached;
}
protected <T> DetachedCriteria getBaseCriteria(Class<T> genericType, AgSort sorter) throws UIFilterException {
    DetachedCriteria criteria = DetachedCriteria.forClass(genericType);
    addSorter(criteria, sorter);
    return criteria;
}
    protected void addSorter(DetachedCriteria criteria, AgSort sort) {
    if (sort != null) {
        Order order = null;
        if (sort.getDir().equalsIgnoreCase(Constants.DESC)) {
            order = Order.desc(sort.getColumn());
        } else {
            order = Order.asc(sort.getColumn());
        }
        criteria.addOrder(order);
    }
}

我正在使用Hibernate4，下面是我的应用程序属性：-

oracle.datasource.driver-class-name=oracle.jdbc.OracleDriver
oracle.datasource.url=******
oracle.datasource.username=*****
oracle.datasource.password=*****
oracle.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect

oracle.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

Answer 1

双引号？通常是一个非常非常糟糕的主意。我建议您避免此类事情并使用有效的列名/别名。

SQL> select dname _invalid_alias_
  2  from dept
  3  where deptno = 10;
select dname _invalid_alias_
             *
ERROR at line 1:
ORA-00911: invalid character

SQL> select dname "_valid_alias_"
  2  from dept
  3  where deptno = 10;
_valid_alias_
--------------
ACCOUNTING
SQL>