SQLGrammarException with Hibernate and Oracle11g

Question

两个问题 -如果我查询一个实体，我会得到...

ERROR: ORA-00933: SQL command not properly ended
Nov 11, 2013 8:47:36 PM org.hibernate.event.internal.DefaultLoadEventListener onLoad
INFO: HHH000327: Error performing load command : org.hibernate.exception.SQLGrammarException: could not extract ResultSet

如果我尝试插入一个实体，我会得到...

Hibernate: insert into MyDB.dbo.BOSS_CONTACTS_C_LVL (CNTCT_PHN_NBR, CVM_CNTCT_EMAIL, DTID, BUSINESS_NAME, CONTACT_NAME, FIRST_NAME, LAST_NAME, PHONE_NUMBER, CONTACT_TITLE, EMPLOYEES_HERE, REGION, AREA, BRANCH_NAME, BRANCH_ID, IN_OUTBOUND, PROSPECT_TYPE, RATING, DUNS_NUMBER, ADDRESS, CITY, STATE, ZIP, LOCAL_STREET, LOCAL_CITY, LOCAL_STATE_PROVINCE, LOCAL_ZIP_POSTAL_CODE, HANDSET_POTENTIAL, CAMPAIGN_NAME, CAMPAIGN_CODE, ASSIGNED_REP, TOTAL_TELECOM, PROFILE_ID, BUSINESS_ID, KEYCODE, SIC, CVM_UNQ_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Nov 11, 2013 9:00:53 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 926, SQLState: 42000
Nov 11, 2013 9:00:53 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ORA-00926: missing VALUES keyword

我已经尝试了几种不同的Oracle驱动程序，认为这可能是问题所在。我还在某处读到一篇文章，其中一个字段名称可能是 Oracle 关键字，但我删除了除要测试的主键之外的所有字段，并且仍然发生错误。

休眠.cfg -

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
                                         "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
 <session-factory>
  <property name="show_sql">true</property>
  <property name="hibernate.current_session_context_class">org.hibernate.context.internal.ThreadLocalSessionContext</property>
  <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
  <property name="hibernate.connection.password">oasisd_02</property>
  <property name="hibernate.connection.url">jdbc:oracle:thin:@MyServer:1521/MyDB</property>
  <property name="hibernate.connection.username">sa</property>
  <property name="hibernate.default_catalog">MyDB</property>
  <property name="hibernate.default_schema">dbo</property>
  <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
  <mapping class="myPrj.domain.Contact" resource="myPrj/domain/Contact.hbm.xml"/>
 </session-factory>
</hibernate-configuration>

映射文件是从 Eclipse 中的 JBOSS Tools 生成的。

另外，我已经尝试了JDK 1.6和1.7

已更新 -我应该补充一点，我没有使用自定义 HQL 查询。

使用存储库模式 -

public class ContactRepository implements IContactRepository {
    public Contact find(String cvmUnqId) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        Contact aContact = (Contact) session.load(Contact.class, cvmUnqId);
        return aContact;
    }
    public Contact findByZipCode(String zipcode) {
        // TODO Auto-generated method stub
        return null;
    }
    public void store(Contact contact) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        session.save(contact);
        session.getTransaction().commit();
    }
    public void remove(Contact contact)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        session.delete(contact);
        session.getTransaction().commit();
    }
}

而测试用例——

@Test
public void testContactList(){
    ContactRepository contactRepo = new ContactRepository();
    Object result = contactRepo.find("FakeID");
    assertEquals(result, null);
}
@Test
public void addRemoveContactTest() {
    ContactRepository contactRepo = new ContactRepository();
    Contact contact = new Contact();
    contact.setAddress("123 Anywhere");
    contact.setArea('A');
    contact.setAssignedRep('B');
    contact.setBranchId('C');
    contact.setBranchName('D');
    contact.setBusinessId("Business ID");
    contact.setBusinessName("Fake Business");
    contact.setZip("11111");
    contact.setCvmUnqId("FakeID");
    contactRepo.store(contact);
    Contact foundContact = contactRepo.find(contact.getCvmUnqId());
    assertEquals("Contact was not found in repository", contact, foundContact);
    contactRepo.remove(contact);
    foundContact = contactRepo.find(contact.getCvmUnqId());
    assertEquals("Contact was not removed from repository", null, foundContact);
}

如果相关，还有工厂实用程序 -

public class HibernateUtil {
    private static SessionFactory sessionFactory = configureSessionFactory();
    private static ServiceRegistry serviceRegistry;
    private static SessionFactory configureSessionFactory() throws HibernateException {
        Configuration configuration = new Configuration();
        configuration.configure();
        serviceRegistry = new ServiceRegistryBuilder().applySettings(configuration.getProperties()).buildServiceRegistry();        
        sessionFactory = configuration.buildSessionFactory(serviceRegistry);
        return sessionFactory;
    }
    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }
}

问题已解决。多部分表标识符不正确。我从休眠配置中删除了默认目录属性，它现在可以正常工作。我很惊讶这不是来自 Oracle 的更好的错误消息。类似"表不存在"之类的东西...

Answer 1

找出导致预言机错误的语句的最简单方法是将会话连接到数据库并打开 sql 跟踪或启用事件 10046 跟踪。

要启用跟踪，请使用 更改会话集 sql_trace=true

打开 10046 跟踪 1 更改会话集事件"10046 跟踪名称上下文永久，级别 12";

10046 也可以由 DBA 用于其他数据库会话。

然后启动工作负载并分析相应的跟踪文件。跟踪文件是在 init.ora 参数 user_dump_dest 指定的目录中创建的。

Answer 2

问题已解决。多部分表标识符不正确。我从休眠配置中删除了默认目录属性，它现在可以正常工作。我很惊讶这不是来自 Oracle 的更好的错误消息。类似"表不存在"之类的东西...