Using Oracle sequence with JBOSS

[Nei San]

Hi,

I'm trying to use JBOSS IDE for Eclipse but I'm having trouble using an automatic generated id populated from a Oracle sequence previously created in the database. I guess there is some aditional configuration that I need to do in my application but I don't know what it is.

In the persistence XML I have:

<?xml version="1.0" encoding="UTF-8"?>
<persistence>
  <persistence-unit name="shoestringPU">>
      <jta-data-source>java:/OracleDS</jta-data-source>
    <properties>
      <property name="hibernate.dialect" 
        value="org.hibernate.dialect.OracleDialect"/>
    </properties>    
  </persistence-unit>
</persistence>

At the entity bean I have:

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
public int getId() {
return id;
}

I'm getting the error "java.sql.SQLException: ORA-02289: sequence does not exist".

Thank you.

Nei

[Jaikiran Pai]

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
public int getId() {
return id;
}

Try this:

@Id
@GeneratedValue(strategy=GenerationType.[B]SEQUENCE[/B])
 public int getId() {
    return id;
    }

See if it works.

[Jaikiran Pai]

And most importantly, do you have the Sequence created in your database?

[Mark Spritzler]

Actually, keep the AUTO in there, but make sure Oracle has a Hibernate Sequence tabled, named "hibernate_seq" or something along those lines. But if you have a hibernate sequence all tables will use that one sequence generator.

If you have a sequence table for each table, then you need another annotation that you put the name of the sequence table to use, but I forget the exact name of that Annotation.

Mark

[Jaikiran Pai]

Originally posted by Mark Spritzler:

If you have a sequence table for each table, then you need another annotation that you put the name of the sequence table to use, but I forget the exact name of that Annotation.

@SequenceGenerator(name="MySeq",sequenceName="MY_SEQ")

This one?

[Mark Spritzler]

Originally posted by Jaikiran Pai:

@SequenceGenerator(name="MySeq",sequenceName="MY_SEQ")

This one?

Nah, I don't think so, so I looked it up and it is

@SequenceGenerator(name="MySeq",sequenceName="MY_SEQ")

Mark

[Jaikiran Pai]

[Nei San]

Hi,

I need an sequence for each table so I changed my code according to your information and worked with the sequence but now I'm getting an error with the foreign key. I have two entity beans Person and Address both of them with the key being generated by two different sequences: person_seq and address_seq. Person has an foreign key that references the field "id" of the Address table.
In the Person ejb I have:

@Entity
@SequenceGenerator(name = "PERSON_SEQ", sequenceName = "PERSON_SEQ")
 
@Id
@GeneratedValue(strategy=GenerationType.AUTO, generator = "PERSON_SEQ")
  public int getId() {
    return id;
  }
 
@ManyToOne(cascade=CascadeType.ALL)
@JoinColumn(name="addressId", referencedColumnName="id")
  public Address getAddress() {
    return address;
  }

In the Address ejb I have:

@Entity
@SequenceGenerator(name = "ADDRESS_SEQ", sequenceName = "ADDRESS_SEQ")
 
@Id
@GeneratedValue(strategy=GenerationType.AUTO, generator = "ADDRESS_SEQ")
  public int getId() {
    return id;
  }

Both tables are empty and I'm trying to add an register in the table Person and Address but I'm getting the following error:

ORA-02291: integrity constraint (ADM.FK_ADDRESS) violated - parent key not found

I'm getting some warnings when I start JBOSS:

"14:32:28,570 WARN [Ejb3Configuration] Persistence provider caller does not implements the EJB3 spec correctly. PersistenceUnitInfo.getNewTempClassLoader() is null."

"14:32:28,898 WARN [AnnotationBinder] Hibernate does not support SequenceGenerator.initialValue()"

Do you think these warns have to do with the error message?

Thank you.

Nei

[Mark Spritzler]

@JoinColumn(name="addressId", referencedColumnName="id")

I don't think addressId and id are the correct values to do for your join. What is the actual column name, and if it is not address_id, then you will need an @Column for the id attribute of the Address object, and probably the same for the Person object.

If you have an attribute called "id" and no @Column, Hibernate will assume the primary key field is the name of the class plus an underscore plus "id" so for a Person object with an "id" attribute marked at the @Id, then the primary field name in the database should be "person_id"

Mark

[Nei San]

Hi,

I added the Column(name =�id�) annotation before the getId() method of Person and Address ejb but I�m getting the same error message about the foreign key violation. My Oracle tables have the following fields:

* Person

Name Type Nullable Default
-------- ------------- -------- -------
ID NUMBER
NAME VARCHAR2(80) Y
ADDRESSID NUMBER(10) Y
EMAIL VARCHAR2(80) Y
PASSWORD VARCHAR2(64) Y
GENDER VARCHAR2(20) Y NULL
...and some more fields

The field ID is the primary key and ADDRESSID is a foreign key.

* Address

Name Type Nullable Default
-------- ------------- -------- -------
ID NUMBER(10)
LINE1 VARCHAR2(255) Y NULL
COUNTRY VARCHAR2(150) Y NULL
POSTCODE VARCHAR2(50) Y NULL

The field ID is the primary key

I have two entity beans called Person and Address.
The data of the tables is entered using the register.jsp page that uses the Register.java class to save the contents to the tables as showed below:

<f:facet name="footer">
<h :-P	 anelGroup>
<h:commandButton value="#{msg.submit}" 
  action="#{registrationBean.register}" />
<h:commandButton value="#{msg.reset}" type="reset"/>
</h :-P	 anelGroup>
</f:facet>

* Register.java:

public String register() throws Exception {
String toReturn = "failure";
   
if (validateData()) {
try {
// save locale information, in case the user chose a language on the welcome page
Locale locale = FacesContext.getCurrentInstance().getViewRoot().getLocale();
person.setLocaleCountry(locale.getCountry());
person.setLocaleLanguage(locale.getLanguage());

Context context = new InitialContext();
EntityFacade entities = (EntityFacade) context.lookup("EntityFacadeBean/remote");
person = entities.createPerson(person);
toReturn = "success";
} 
catch (PersonEntityExistsException exist) {
MessageFactory msg = new MessageFactory();
FacesContext ctx = FacesContext.getCurrentInstance();

ctx.addMessage("registerForm:email", 
new FacesMessage(FacesMessage.SEVERITY_ERROR, 
msg.getMessage("errorEmailExists"), null));
}
}
return toReturn;
}

@Stateless
public class EntityFacadeBean implements EntityFacade{
@PersistenceContext(unitName="shoestringPU") EntityManager em;
 
public Person getPerson(String email) {
    Person entity = null;
    try {
      Query query = em.createQuery("SELECT p FROM Person p WHERE p.email = ?1");
      query.setParameter(1, email);
      entity = (Person) query.getSingleResult();
    }
    catch (NoResultException noneFound) {
      // if not found, just return null
    }
    return entity;
  }
/**
   * Perform last minute validation, then if OK save 
   * entities (Person and Address in Person)
   * @param toCreate person record to persist
   * @return person record just created, with the
   *     primary key set to that just insrted.
   *  @throws PersonEntityExistsException if email given exists
   *  @throws PersonPasswordException if password is < 6 characters long
   *  @throws PersonEmailException if email is blank or null
   */
  public Person createPerson(Person toCreate) 
    throws PersonEntityExistsException,  PersonPasswordException,
           PersonEmailException {  
    String email = toCreate.getEmail();
    if (email == null || email.trim().length() == 0) {
      throw new PersonEmailException("Length is zero");
    }
    String password = toCreate.getPassword();
    if (password == null || password.length() < 6) {
      throw new PersonPasswordException("Length is less than 6");
    }
    if (getPerson(email) != null) {
      throw new PersonEntityExistsException(
                  "Person record already exists with an email of " + 
                  toCreate.getEmail());
    }
    em.persist(toCreate);
    return toCreate;
  }
 
}

The EntityFacadeBean uses a EntityManager to save the records in the table and the annotations in the beans are:

* Person.java

@Entity
@SequenceGenerator(name = "PERSON_SEQ", sequenceName = "PERSON_SEQ")
 
@Id
@Column(name="id")
@GeneratedValue(strategy=GenerationType.AUTO, generator = "PERSON_SEQ")
public int getId() {
return id;
}
 
@ManyToOne(cascade=CascadeType.ALL)
@JoinColumn(name="addressId", 
    referencedColumnName="id")
public Address getAddress() {
return address;
}

* Address.java

@Id
@Column(name="id")
@GeneratedValue(strategy=GenerationType.AUTO, generator = "ADDRESS_SEQ")
public int getId() {
return id;
}

The entity manager is responsible for recording the informations in the database, then shouldn�t it be recording the registers in the correct order: the Address first and the Person data after that?

Thank you.

Nei

[Mark Spritzler]

I don't see where you add an Address record. So basically what I see is you create a Person object, with no Address object in it, and you go to insert the person into the database, but there is no corresponding address record. Since addressid is a FK it must have a value. In your model Address is the parent table of person, so there would have to be an Address record inserted before you can insert a Person record. (which normally it is the other way around)

Mark

[Nei San]

Hi,

I have a JSP page that receives the data entered by the user. This page uses JSF.

* register.jsp:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@ taglib uri="http://java.sun.com/jsf/html" prefix="h" %>
<%@ taglib uri="http://java.sun.com/jsf/core" prefix="f" %>
<%@ page errorPage="errorpage.jsp" %>
...
<f:facet name="footer">
<h:panelGroup>
<h:commandButton value="#{msg.submit}" 
  action="#{registrationBean.register}" />
<h:commandButton value="#{msg.reset}" type="reset"/>
</h:panelGroup>
</f:facet>
...
<h:outputLabel for="title" value="#{msg.registrationTitle}"/>
<h:panelGroup>
<h:inputText id="title" value="#{registrationBean.person.title}"
maxlength="45" size="10"/><f:verbatim><br/></f:verbatim>
<h:outputText value=" "/><h:message for="title" styleClass="formUserError"/>
</h:panelGroup>

<h:outputLabel for="firstName" value="#{msg.registrationName}"/>
<h:panelGroup>
<h:inputText id="firstName" value="#{registrationBean.person.firstName}"
maxlength="255" size="30"/><f:verbatim><br/></f:verbatim>
<h:outputText value=" "/><h:message for="firstName" styleClass="formUserError"/>
</h:panelGroup>
...
<h:outputLabel for="line1" value="#{msg.registrationAddress1}"/>
<h:panelGroup>
<h:inputText id="line1" value="#{registrationBean.person.address.line1}"
maxlength="255" size="50"/><f:verbatim><br/></f:verbatim>
<h:outputText value=" "/><h:message for="line1" styleClass="formUserError"/>
</h:panelGroup>

<h:outputLabel for="postcode" value="#{msg.registrationPostcode}"/>
<h:panelGroup>
<h:inputText id="postcode" value="#{registrationBean.person.address.postcode}"
maxlength="50" size="20"/><f:verbatim><br/></f:verbatim>
<h:outputText value=" "/><h:message for="postcode" styleClass="formUserError"/>
</h:panelGroup>

<h:outputLabel for="country" value="#{msg.registrationCountry}"/>
<h:panelGroup>
<h:inputText id="country" value="#{registrationBean.person.address.country}"
maxlength="150" size="40"/><f:verbatim><br/></f:verbatim>
<h:outputText value=" "/><h:message for="country" styleClass="formUserError"/>
</h:panelGroup>

The JSF configuration is:

* faces-config.xml:

<managed-bean>
    <managed-bean-name>registrationBean</managed-bean-name>
    <managed-bean-class>app.web.Register</managed-bean-class>
    <managed-bean-scope>request</managed-bean-scope>
    <managed-property>
    <property-name>person</property-name>
    <value>#{registeringPersonBean}</value>
    </managed-property>
  </managed-bean>  
  
  <managed-bean>
  <managed-bean-name>registeringPersonBean</managed-bean-name>
  <managed-bean-class>app.server.entities.Person</managed-bean-class>
  <managed-bean-scope>none</managed-bean-scope>
    <managed-property>
    <property-name>address</property-name>
    <value>#{addressBean}</value>
    </managed-property>
    <managed-property>
    <property-name>gender</property-name>
    <value>Undisclosed</value>
    </managed-property>
     <managed-property>
    <property-name>maritalStatus</property-name>
    <value>Undisclosed</value>
    </managed-property>    
  </managed-bean>
  
  <managed-bean>
    <managed-bean-name>addressBean</managed-bean-name>
    <managed-bean-class>app.server.entities.Address</managed-bean-class>
    <managed-bean-scope>none</managed-bean-scope>
  </managed-bean> 

So when the user enters the data in the JSP/JSF page and press submit the class Register.java is called with the Person ejb data filled, is that correct? As the Person ejb has the methods getAddress and setAddress, shouldn�t the entity manager create the records in the table Address and in the table Person?

Thank you again.

Nei

[Mark Spritzler]

OK, so if set a break point just before you call persist or save method, does the Person object have an Address object, or actually, in your case the Address object have a Person?

Mark

[Nei San]

Hi,

Sorry the late reply but I was busy with other activities. I just discovered why my code wasn't working. I had created an Oracle before insert trigger for Person and Address table that used the sequences to insert the primary key value in the tables. I didn't realize that JBOSS invokes the sequences to generate the primary key itself.

Thank you very much for your help.

Nei