Java 错误：ORA-02289：序列不存在 - org.hibernate.exception.SQLGrammarException：无法提取 ResultSet

ERROR: ORA-02289: sequence does not exist - org.hibernate.exception.SQLGrammarException: could not extract ResultSet

提问by user2682305

I am creating a basic hibernate application for employee table in which I am adding, updating, deleting and displaying the record with the help of ManageEmployee class.

我正在为员工表创建一个基本的休眠应用程序，我在 ManageEmployee 类的帮助下添加、更新、删除和显示记录。

Actually, ManageEmployee is my test class and i am using Employee.java and Employee.hbm.xml file to do above things.

实际上，ManageEmployee 是我的测试类，我使用 Employee.java 和 Employee.hbm.xml 文件来做上述事情。

Details: Database: Oracle 11 g Hibernate version: hibernate-release-4.3.8.Final Java Version: Java 1.8

详细信息：数据库：Oracle 11 g Hibernate 版本：hibernate-release-4.3.8.Final Java 版本：Java 1.8

Employee Table Details:

员工表详细信息：

USER_ID NUMBER(15,0) No 1 1
FIRST_NAME VARCHAR2(20 BYTE) Yes 2
LAST_NAME VARCHAR2(20 BYTE) Yes 3
SALARY NUMBER(10,0) Yes 4

USER_ID NUMBER(15,0) 否 1 1
FIRST_NAME VARCHAR2(20 BYTE) 是 2
LAST_NAME VARCHAR2(20 BYTE) 是 3
SALARY NUMBER(10,0) 是 4

Error Log: you can see in the log that error is coming 3 times because I am adding Employee object 3 times in ManageEmployee.java class with addEmployee(...) method so I am assuming that problem is with the sequence generator for the column id.

错误日志：您可以在日志中看到错误发生了 3 次，因为我使用 addEmployee(...) 方法在 ManageEmployee.java 类中添加了 Employee 对象 3 次，所以我假设问题出在列的序列生成器上ID。

Here I am using naive generator. i.g. Integer empID1 = ME.addEmployee("Zara", "Ali", 1000);

在这里，我使用的是朴素的生成器。ig Integer empID1 = ME.addEmployee("Zara", "Ali", 1000);

May 12, 2015 3:34:39 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ORA-02289: sequence does not exist

org.hibernate.exception.SQLGrammarException: could not extract ResultSet
 at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80)
 at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
 at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
 at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
 at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
 at org.hibernate.id.SequenceGenerator.generateHolder(SequenceGenerator.java:122)
 at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:115)
 at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:117)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:209)
 at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:55)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:194)
 at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:49)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
 at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:715)
 at org.hibernate.internal.SessionImpl.save(SessionImpl.java:707)
 at org.hibernate.internal.SessionImpl.save(SessionImpl.java:702)
 at ManageEmployee.addEmployee(ManageEmployee.java:47)
 at ManageEmployee.main(ManageEmployee.java:23)
Caused by: java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist

 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
 at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
 at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
 at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
 at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
 at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
 at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
 at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
 at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
 at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
 ... 13 more
May 12, 2015 3:34:39 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 2289, SQLState: 42000
May 12, 2015 3:34:39 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ORA-02289: sequence does not exist

org.hibernate.exception.SQLGrammarException: could not extract ResultSet
 at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80)
 at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
 at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
 at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
 at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
 at org.hibernate.id.SequenceGenerator.generateHolder(SequenceGenerator.java:122)
 at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:115)
 at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:117)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:209)
 at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:55)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:194)
 at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:49)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
 at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:715)
 at org.hibernate.internal.SessionImpl.save(SessionImpl.java:707)
 at org.hibernate.internal.SessionImpl.save(SessionImpl.java:702)
 at ManageEmployee.addEmployee(ManageEmployee.java:47)
 at ManageEmployee.main(ManageEmployee.java:24)
Caused by: java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist

 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
 at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
 at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
 at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
 at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
 at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
 at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
 at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
 at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
 at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
 ... 13 more
May 12, 2015 3:34:39 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 2289, SQLState: 42000
May 12, 2015 3:34:39 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ORA-02289: sequence does not exist

org.hibernate.exception.SQLGrammarException: could not extract ResultSet
 at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80)
 at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
 at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
 at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
 at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
 at org.hibernate.id.SequenceGenerator.generateHolder(SequenceGenerator.java:122)
 at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:115)
 at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:117)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:209)
 at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:55)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:194)
 at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:49)
 at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
 at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:715)
 at org.hibernate.internal.SessionImpl.save(SessionImpl.java:707)
 at org.hibernate.internal.SessionImpl.save(SessionImpl.java:702)
 at ManageEmployee.addEmployee(ManageEmployee.java:47)
 at ManageEmployee.main(ManageEmployee.java:25)
Caused by: java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist

 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
 at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
 at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
 at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
 at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
 at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
 at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
 at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
 at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
 at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
 ... 13 more
First Name: a  Last Name: b  Salary: 2000

ManageEmployee.java file Code:

ManageEmployee.java 文件代码：

import java.util.List; 
import java.util.Date;
import java.util.Iterator; 
 
import org.hibernate.HibernateException; 
import org.hibernate.Session; 
import org.hibernate.Transaction;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class ManageEmployee {
   private static SessionFactory factory; 
   public static void main(String[] args) {
      try{
         factory = new Configuration().configure().buildSessionFactory();
      }catch (Throwable ex) { 
         System.err.println("Failed to create sessionFactory object." + ex);
         throw new ExceptionInInitializerError(ex); 
      }
      ManageEmployee ME = new ManageEmployee();

      /* Add few employee records in database */
      Integer empID1 = ME.addEmployee("Zara", "Ali", 1000);
      Integer empID2 = ME.addEmployee("Daisy", "Das", 5000);
      Integer empID3 = ME.addEmployee("John", "Paul", 10000);

      /* List down all the employees */
      ME.listEmployees();

      /* Update employee's records */
     // ME.updateEmployee(empID1, 5000);

      /* Delete an employee from the database */
     // ME.deleteEmployee(empID2);

      /* List down new list of the employees */
     // ME.listEmployees();
   }
   /* Method to CREATE an employee in the database */
   public Integer addEmployee(String fname, String lname, int salary){
      Session session = factory.openSession();
      Transaction tx = null;
      Integer employeeID = null;
      try{
         tx = session.beginTransaction();
         Employee employee = new Employee(fname, lname, salary);
         employeeID = (Integer) session.save(employee); 
         tx.commit();
      }catch (HibernateException e) {
         if (tx!=null) tx.rollback();
         e.printStackTrace(); 
      }finally {
         session.close(); 
      }
      return employeeID;
   }
   /* Method to  READ all the employees */
   public void listEmployees( ){
      Session session = factory.openSession();
      Transaction tx = null;
      try{
         tx = session.beginTransaction();
         List employees = session.createQuery("FROM Employee").list(); 
         for (Iterator iterator = 
                           employees.iterator(); iterator.hasNext();){
            Employee employee = (Employee) iterator.next(); 
            System.out.print("First Name: " + employee.getFirstName()); 
            System.out.print("  Last Name: " + employee.getLastName()); 
            System.out.println("  Salary: " + employee.getSalary()); 
         }
         tx.commit();
      }catch (HibernateException e) {
         if (tx!=null) tx.rollback();
         e.printStackTrace(); 
      }finally {
         session.close(); 
      }
   }
   /* Method to UPDATE salary for an employee */
   public void updateEmployee(Integer EmployeeID, int salary ){
      Session session = factory.openSession();
      Transaction tx = null;
      try{
         tx = session.beginTransaction();
         Employee employee = 
                    (Employee)session.get(Employee.class, EmployeeID); 
         employee.setSalary( salary );
   session.update(employee); 
         tx.commit();
      }catch (HibernateException e) {
         if (tx!=null) tx.rollback();
         e.printStackTrace(); 
      }finally {
         session.close(); 
      }
   }
   /* Method to DELETE an employee from the records */
   public void deleteEmployee(Integer EmployeeID){
      Session session = factory.openSession();
      Transaction tx = null;
      try{
         tx = session.beginTransaction();
         Employee employee = 
                   (Employee)session.get(Employee.class, EmployeeID); 
         session.delete(employee); 
         tx.commit();
      }catch (HibernateException e) {
         if (tx!=null) tx.rollback();
         e.printStackTrace(); 
      }finally {
         session.close(); 
      }
   }
}

Mapping File:

映射文件：

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
 "-//Hibernate/Hibernate Mapping DTD//EN"
 "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> 

<hibernate-mapping>
   <class name="Employee" table="EMPLOYEE">
      <meta attribute="class-description">
         This class contains the employee detail. 
      </meta>
      <id name="user_id" type="int" column="user_id">
         <generator class="native"/>
      </id>
      <property name="firstName" column="first_name" type="string"/>
      <property name="lastName" column="last_name" type="string"/>
      <property name="salary" column="salary" type="int"/>
   </class>
</hibernate-mapping>

public class Employee {
   
   private int user_id;
   private String firstName; 
   private String lastName;   
   private int salary;  

   public Employee() {}
   public Employee(String fname, String lname, int salary) {
      this.firstName = fname;
      this.lastName = lname;
      this.salary = salary;
   }
  
   public String getFirstName() {
      return firstName;
   }
   public void setFirstName( String first_name ) {
      this.firstName = first_name;
   }
   public String getLastName() {
      return lastName;
   }
   public void setLastName( String last_name ) {
      this.lastName = last_name;
   }
   public int getSalary() {
      return salary;
   }
   public void setSalary( int salary ) {
      this.salary = salary;
   }
public int getUser_id() {
 return user_id;
}
public void setUser_id(int user_id) {
 this.user_id = user_id;
}
}

回答by Asura

Employee class is missing a sequence generator so hibernate cannot insert the row. it should be something like this which increase the id count column by 1:

Employee 类缺少序列生成器，因此 hibernate 无法插入该行。它应该是这样的，它将 id 计数列增加 1：

@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQUENCE1")
@SequenceGenerator(name="SEQUENCE1", sequenceName="SEQUENCE1", allocationSize=1)
private int user_id;

回答by user2682305

after read up on Oracle sequencesand how they function, as well as telling Hibernate that you are using a SEQUENCE(section 5.1.2.2.4) rather than a native generator, I have fixed my errors.

在阅读了Oracle 序列及其运作方式，并告诉 Hibernate 您使用的是 SEQUENCE（第 5.1.2.2.4 节）而不是本机生成器之后，我已经修复了我的错误。

I have created a sequence in oracle database by the below query and updated my hbm.xml file

我通过以下查询在 oracle 数据库中创建了一个序列并更新了我的 hbm.xml 文件

CREATE SEQUENCE TAB_CUSTOMER_SEQ
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

<id name="user_id" type="int" column="user_id">
          <generator class="sequence">
            <param name="sequence">TAB_CUSTOMER_SEQ</param>
          </generator>         
      </id>

回答by Sang9xpro

You didn't create a sequence for table EMPLOYEE to generate automatic USER_ID. This is your problem. So now you need to create a sequence for table EMPLOYEE in both DATABASE and your mapping file

您没有为表 EMPLOYEE 创建序列来自动生成 USER_ID。这是你的问题。所以现在你需要在 DATABASE 和你的映射文件中为表 EMPLOYEE 创建一个序列