JPA: How to add the first entity of recursive non-null relation

Question

How to add the first entity of recursive non-null relation?

The trouble occurs when trying to use my default audit guard (EntityListener) for user entities. Hibernate isnt able to insert first user. The example is simplification of situation:

@Entity
class User {
    @Id @GeneratedValue
    private Long id;
    @Basic
    private String name;
    @ManyToOne(optional=false)
    @JoinColumn(nullable=false,updatable=false)
    private User createdBy;

    // getters & setters
    // equals & hashcode are based on name
}

And I have tried something like:

User user = new User();
user.setName( "Some one" );
user.setCreatedBy( user );

Hibernate fails and root exception is:

 com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into 
column 'createdby_id', table 'mydb.user'; column does not allow nulls. INSERT fails.

I know several workarounds: insert first entity manually (SQL insert) or set nullable=true. First is just annoying and second is fail point of integrity (custom audit listener & db trigger required). Is there better options? Or, in the other words: is there JPA-only solution?

My platform is: SQL Server 2008 and Hibernate 3.6 as JPA2 provider.

Edit: At first, I was using persist(). I tried merge() which could make more intelligent guess but no difference. And I tried CascadeType.MERGE too.

score 1 · Accepted Answer · answered Apr 01 '11 at 11:29

The not null constraint mandates that you insert the user.id in the reference field, but if you have set the id field to be autogenerated, that value is not known until after the insert. To solve this, you could use a sequence to generate the @Id

create sequence SEQ_USER

and insert the first user by fetching the next value of that sequence, and setting both fields to that value. With a native SQL query from JPA this looks like:

User u = new User();
u.setName("First User");
Query q = em.createNativeQuery("VALUES nextval for SEQ_USER");
Long nextId = Long.valueOf((Integer) q.getSingleResult());
q = em.createNativeQuery("insert into user (id, name, created_by) values (?, ?, ?)");
q.setParameter(1, nextId).setParameter(2, u.getName()).setParameter(3, nextId);
q.executeUpdate();
// don't forget to bring u into persistence context
u = em.find(User.class, nextId);

which is a little kludgy but has to be called only once. All other users would be persisted the normal way (using SEQ_USER as Generator sequence for the @Id)

Alternatively, you could write your own SequenceGenerator do just that:

public class UserIdGenerator implements IdentifierGenerator {

  @Override
  public Serializable generate(SessionImplementor session, Object object) throws HibernateException {

    User o = (User) object;
    Connection connection = session.connection();
    try {
      PreparedStatement ps = connection.prepareStatement("VALUES nextval for SEQ_USER");
      ResultSet rs = ps.executeQuery();
      if (rs.next()) {
        return rs.getLong(1);
      }
    } catch (SQLException e) {
      log.error("Unable to generate Id: " + e.getMessage(), e);
    }
    return null;
  }
}

In User, you qould set that generator as:

@Id
@GenericGenerator(name = "idSource", strategy = "com.example.db.UserIdGenerator", parameters = { @Parameter(name = "sequence", value = "SEQ_USER") })
@GeneratedValue(generator = "idSource")
private Long id;

and then

User u = new User();
u.setName("First User");
u.setCreatedBy(u);
em.persist(u);

works as expected.

I updated orginal post: I had stripped `@GeneratedValue`. As you mentioned, generated identity is the issue. But there is no sequences in SQL Server. SQL Insert looks like: `insert into user ( name, createdBy ) values ('john doe',scope_identity());`. But real User class has 13 non-null columns... — Antti Pihlaja, Apr 01 '11 at 17:25
You could try to simulate a sequence, cf. this: http://stackoverflow.com/questions/282943/how-would-you-implement-sequences-in-microsoft-sql-server. Or you could take the id generation completely out of the database and into the JPA layer, maybe using UUIDs as primary keys for the User table, as described here: http://onjava.com/pub/a/onjava/2006/09/13/dont-let-hibernate-steal-your-identity.html?page=3 — wallenborn, Apr 02 '11 at 14:28

JPA: How to add the first entity of recursive non-null relation

1 Answers1