CODEWITHSUNDEEP
javaspringoracle-databasehibernatepostgresql
malavock
malavock

Reputation: 371

Migrating Spring application from Postgres to Oracle: defining table's schema

FINAL EDIT

The reason why I didn't see any logs is that I was using an old version of log4j. Hibernate hbm2ddl uses slf4j for logging and so all the logs were ignored by log4j 1. Now I upgraded to log4j2 that has a slf4j bridge and I see all the errors in the log.

Godd new is that from here I can pick the conversion errors one by one and fix them, bad news (for me) is that they are a lot!

Thanks to everyone!

FINAL EDIT END

I've a very complex java7+Spring3.2.0+hibernate3 web application that works with a Postgresql database. A client now imposed as requirement to use Oracle as a database for a project. I'm having trouble figuring out the Schema use in Oracle.

The Postgresql database is divided in about 10 different schemas, plus separated schemas for the audit records. I define tables and schemas in the annotation. I.e.:

@Entity
@Table(name = "language", schema = "live")
@Audited
@AuditTable(value="language_aud", schema="live_aud")
public class Language implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Integer id;
etc...

This works quite well in Postgres. To create the database I create manually the schemas first:

CREATE SCHEMA live;

And then start the application with hibernate.hbm2ddl.auto=create. This works like a charm and the database is correctly built.

Now moving to Oracle I have problems with the schema definition. I tried the CREATE SCHEMA statement with no succes but then found out that in Oracle schema=user (very strange to me but there must be a reason). So I created all the users to match the Schema structure. I.e.:

CREATE USER live IDENTIFIED BY 'password';

** EDIT **

But this still doesn't work, when I run the application the tables are not created by hibernate (I fixed the previous exception adding try/catch to postProcess methods, they failed as the database was not created)

I can get the connection correctly with Oracle, I use the parameter:

jdbc.driver=oracle.jdbc.driver
jdbc.url=jdbc:oracle:thin:@//localhost:1521/XE
jdbc.username=system
jdbc.password='password'
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

What I am doing wrong? do I have to create the schemas in a different way? or is the system user not to have the right privileges?

The application is shared by many clients so a requirement I have is to try to avoid to change completely the structure removing the schemas. In a perfect world the application should be database indipendant so work with any database.

Thank you for any help you can give and please ask if you need more info.

Mattia

Upvotes: 1

Views: 2056

Answers (1)

tshenolo
tshenolo

Reputation: 463

In oracle id is not auto generated. You have to manage it manually or use a sequence.

Do not use 

@GeneratedValue(strategy = GenerationType.AUTO)

Try this

@Id 
@SequenceGenerator(name="language_generator", sequenceName="language_sequence")
@GeneratedValue(generator="language_generator")
@Column(name = "id")
private Integer id;

Make sure you do this for all your models

** EDIT ** Try this when creating the Oracle user:

SQL> CREATE USER live IDENTIFIED BY 'password'
2  QUOTA UNLIMITED ON SYSTEM
3  QUOTA UNLIMITED ON SYSAUX;

SQL> GRANT CREATE SESSION TO live;

SQL> GRANT CREATE TABLE TO live;

SQL> GRANT SELECT_CATALOG_ROLE TO live; 

SQL> GRANT EXECUTE_CATALOG_ROLE TO live;

SQL> EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => 'live');


SQL> GRANT UNLIMITED TABLESPACE TO live;

SQL> GRANT RESOURCE TO live;

Lock it down when you move to production. As in take away some of the privileges like ability to drop tables etc. PS do not call your model user. Just as a precaution.

Upvotes: 3

Related Questions