What's wrong with my relatoships?

Myryan · **Joined:** Sat Jul 03, 2010 3:19 am **Posts:** 5

Hi.

I am trying to implement simple relationships between two classes User and Role. But I've got an error message when try
to save a user entity into a database. Could you please tell me what's is going on and why I get an error message?

Here is my code.
Table roles:

Code:

CREATE TABLE users.roles
(
  id bigserial NOT NULL,
  "name" character varying(255),
  user_id bigint,
  CONSTRAINT roles_pkey PRIMARY KEY (id)
)

Table users:

Code:

CREATE TABLE users.users
(
  id bigserial NOT NULL,
  create_date timestamp without time zone,
  deleted character(1),
  email character varying(255),
  "name" character varying(255),
  pass character varying(255),
  state character varying(255),
  CONSTRAINT users_pkey PRIMARY KEY (id)
)

Role.class:

Code:

@Entity
@Table(name = "users.roles")
public class Role implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "user_id")
    private Long userId;

    @Column(name = "name")
    private String name;
    
    getters and setters here...

User.class:

Code:

@Entity
@Table(name = "users.users")
public class User implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "user_id")
    private Long id;

    @Basic
    @Column(name = "pass")
    private String pass;

    @Basic
    @Column(name = "name")
    private String name;

    @Basic
    @Column(name = "email")
    private String email;

    @Column(name = "deleted")
    @Type(type = "true_false")
    private boolean deleted;

    @Column(name = "state")
    private String state;

    @Temporal(value = TemporalType.TIMESTAMP)
    @Column(name = "create_date")
    private Date createDate;


    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    private Set<Role> roles;

And here is my error:

Code:

922 [main] INFO org.hibernate.impl.SessionFactoryObjectFactory - Not binding factory to JNDI, no JNDI name configured
INFO : [HibernateTransactionManager.afterPropertiesSet] Using DataSource [org.springframework.jdbc.datasource.DriverManagerDataSource@c1186f] of Hibernate SessionFactory for HibernateTransactionManager
Hibernate: insert into users.users (create_date, deleted, email, name, pass, state) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into users.roles (name, user_id) values (?, ?)
[b]Hibernate: insert into users.users_users.roles (users.users_user_id, roles_id) values (?, ?)[/b]
1563 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 0A000
1563 [main] ERROR org.hibernate.util.JDBCExceptionReporter - Batch entry 0 insert into users.users_users.roles (users.users_user_id, roles_id) values ('35', '28') was aborted.  Call getNextException to see the cause.
1578 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 0A000
1578 [main] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: cross-database references are not implemented: "users.users_users.roles"
  .о.....: 13
1578 [main] ERROR org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
....
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into users.users_users.roles (users.users_user_id, roles_id) values ('35', '28') was aborted.  Call getNextException to see the cause.
...
INFO : [XmlBeanDefinitionReader.loadBeanDefinitions] Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
INFO : [SQLErrorCodesFactory.<init>] SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]

org.springframework.jdbc.UncategorizedSQLException: Hibernate flushing: Could not execute JDBC batch update; uncategorized SQLException for SQL [insert into users.users_users.roles (users.users_user_id, roles_id) values (?, ?)]; SQL state [0A000]; error code [0]; Batch entry 0 insert into users.users_users.roles (users.users_user_id, roles_id) values ('35', '28') was aborted.  Call getNextException to see the cause.; nested exception is java.sql.BatchUpdateException: Batch entry 0 insert into users.users_users.roles (users.users_user_id, roles_id) values ('35', '28') was aborted.  Call getNextException to see the cause.
....
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into users.users_users.roles (users.users_user_id, roles_id) values ('35', '28') was aborted.  Call getNextException to see the cause.

Please tell me, why the hibernate try to insert data into users.users_users.roles? As you can see I use a unidirectional association and they don't mean that I should have a share table to store user_id and role_id.

FYI: the property hibernate.hbm2ddl.auto is disabled(or use default value, it's not in the config)

Thank you.

sanne.grinovero · **Posted:** Sat Jul 03, 2010 7:30 am

Quote:

ERROR: cross-database references are not implemented: "users.users_users.roles"

"users" is likely a keyword in the database you're using, give it a different name

Myryan · **Joined:** Sat Jul 03, 2010 3:19 am **Posts:** 5

Hi s.grinovero.

I think it does not matter how my schema named. I didn't found any illegal keywords in pg manual.
But I've renamed both tables and database too just to be sure. It's not take an effect.

Does hibernate can normally work with hibernate.hbm2ddl.auto=update?
Can it help me and create tables instead of me?
If I try to set hbm2ddl.auto=update it will blow the hibernate up:

Code:

[main] INFO org.hibernate.tool.hbm2ddl.DatabaseMetadata - table not found: clients.persons
[main] INFO org.hibernate.tool.hbm2ddl.DatabaseMetadata - table not found: clients.persons_clients.roles
[main] INFO org.hibernate.tool.hbm2ddl.DatabaseMetadata - table not found: clients.roles
[main] INFO org.hibernate.tool.hbm2ddl.DatabaseMetadata - table not found: clients.persons
[main] INFO org.hibernate.tool.hbm2ddl.DatabaseMetadata - table not found: clients.persons_clients.roles
[main] INFO org.hibernate.tool.hbm2ddl.DatabaseMetadata - table not found: clients.roles
[main] ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate - Unsuccessful: create table clients.persons (user_id  bigserial not null, create_date timestamp, deleted char(1), email varchar(255), name varchar(255), pass varchar(255), state varchar(255), primary key (user_id))
[main] ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate - ERROR: relation "persons" already exists
[main] ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate - Unsuccessful: create table clients.persons_clients.roles (clients.persons_user_id int8 not null, roles_id int8 not null, primary key (clients.persons_user_id, roles_id), unique (roles_id))
[main] ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate - ERROR: syntax error at or near "."

What I do wrong?

p.s. sry for my bad english

Myryan · **Joined:** Sat Jul 03, 2010 3:19 am **Posts:** 5

I've solved the problem.

But I would be got an answer for one question: does the hibernate can normally work with hibernate.hbm2ddl.auto=update?
I am talking about updating and creating relatioships.

sanne.grinovero · **Posted:** Tue Jul 06, 2010 4:36 am

Quote:

But I would be got an answer for one question: does the hibernate can normally work with hibernate.hbm2ddl.auto=update?
I am talking about updating and creating relatioships.

AFAIK it doesn't handle all corner cases, it's meant a useful tool for most common situations but doesn't cover them all. I guess nobody uses it in production, but it's able to generate a nice script which is a good starting point to be reviewed by a DBA, or just nice in development.
The point is "update" won't delete anything which existed before, even contraints and indexes, so the older objects might conflict (think about a unique contraint).

To have a perfect 1-1 match between your schema and your model, drop database and use "create".

Myryan · **Joined:** Sat Jul 03, 2010 3:19 am **Posts:** 5

Thank you for your reply.

I knew that hbm2ddl.auto is using only for non production. I asked only about interaction with Postgres.
Never mind. But if I implement bidirectional association between Role and Person(*-1) it will be work.
And hbm2ddl.auto=update will help me and create 2 tables.

In this case my class looks like in the following:

Code:

@Entity
@Table(name = "clients.person")
public class User implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL/*, mappedBy="user"*/)
    private Set<Role> roles;
   
   another fields and getters and setters

@Entity
@Table(name = "clients.role")
public class Role implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id")
    private Long id;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "user_id")
    private User user;
     
    another fields and getters and setters

But indead I would like to use bidirectional association. In this case the Role.class should not know about User.class:

Code:

@Entity
@Table(name = "clients.role")
public class Role implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id")
    private Long id;
   
    another fields and getters and setters

And the hbm2ddl will produce the error:

Code:

890 [main] INFO org.hibernate.tool.hbm2ddl.SchemaExport - Running hbm2ddl schema export
906 [main] INFO org.hibernate.tool.hbm2ddl.SchemaExport - exporting generated schema to database
1156 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - Unsuccessful: create table clients.person_clients.role (clients.person_id int8 not null, roles_id int8 not null, primary key (clients.person_id, roles_id), unique (roles_id))
1156 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - ERROR: syntax error at or near "."
  Position: 50
1218 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - Unsuccessful: alter table clients.person_clients.role add constraint FKDFD91060E22FB067 foreign key (clients.person_id) references clients.person
1218 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - ERROR: syntax error at or near "."
  Position: 95
1218 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - Unsuccessful: alter table clients.person_clients.role add constraint FKDFD910608C09C910 foreign key (roles_id) references clients.role
1218 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - ERROR: cross-database references are not implemented: "clients.person_clients.role"
1218 [main] INFO org.hibernate.tool.hbm2ddl.SchemaExport - schema export complete
INFO : [HibernateTransactionManager.afterPropertiesSet] Using DataSource [org.springframework.jdbc.datasource.DriverManagerDataSource@162522b] of Hibernate SessionFactory for HibernateTransactionManager
Hibernate: insert into clients.person (create_date, deleted, email, name, pass, state) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into clients.role (name) values (?)
Hibernate: insert into clients.person_clients.role (clients.person_id, roles_id) values (?, ?)
1875 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 0A000
1875 [main] ERROR org.hibernate.util.JDBCExceptionReporter - Batch entry 0 insert into clients.person_clients.role (clients.person_id, roles_id) values ('1', '1') was aborted.  Call getNextException to see the cause.
1875 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 0A000
1875 [main] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: cross-database references are not implemented: "clients.person_clients.role"
  Position: 13

As you could see there is several 'Position: XX' words. I gues it's position of error in sql create script.
Is there are any possibility to see what script was genereted by hbm2ddl?