5

Is there a posibility to define with JPA different column types depending on the used database?

I need to store the id as uuid and it must be protable. That's the problem. PostgreSQL has 'uuid', MSSQL 'uniqueidentifier' and Oracle has nothing, there must be used 'RAW', I think.

Have anybody an idea and can help me?

Edit: Currently the ids are generated with java.util.UUID and stored in the database as varchar. But because of performance problems with that data type I want to store the ids as a uuid type. For Oracle must use the RAW type because no uuid type exist. How can I tell JPA use uuid type with PostgreSQ/MSSQL and RAW type with Oracle?

JulianG
  • 1,551
  • 1
  • 19
  • 29
  • How is a varchar creating performance issues? This is the first time I hear that. I do not think the performance issue comes from the varchar but maybe from missing indexes and/or keys. – tom Jan 31 '14 at 07:39
  • 1
    I don't think that the varchars are the cause of the performance problems. But using uuid types give 10% to 30% more performance. And now my task is to find out how to use uuid type at all supported databases – JulianG Jan 31 '14 at 07:51
  • 2
    Doing anything like that would need separation of metadata from classes, so 1 class can be used with different metadata depending on datastore (and you can't do that with annotations). JPA isn't flexible enough to provide that (even with its XML metadata); you may get some vendor specific option, but then lose vendor portability. The only persistence standard that allows all of that is JDO. – DataNucleus Feb 12 '14 at 14:15

2 Answers2

0

You can use java.util.UUID to generate your uuids and store it into a character varying(60) column type. Now since you want to be a primary key it should be good to auto generate it a persist. So you can add on your class an entity listener at PrePersist:

@EntityListeners({ AbstractEntity.AbstractEntityListener.class})
public class Entity {

    public static class AbstractEntityListener {
        @PrePersist
        public void onPrePersist(Entity entity) {
            entity.uid();
        }
    }

    private String uid() {
        if (uid == null)
          uid = UUID.randomUUID().toString();
        return uid;
    }
}

Note that i didn't test this. So it may not work 100%.

BBogdan
  • 361
  • 1
  • 7
  • Thank you for your fast answer. I didn't explain it enough. That's the current state, the id is generated with java.util.UUID and stored in the database as varchar. Now I want to store it as a uuid type, but Oracle doesn't have one. This is my problem, how could I solve it? I edit my question to make it more precise. – JulianG Jan 31 '14 at 07:16
  • If you are using Hibernate as jpa provider, you could use something like this: @Id @GeneratedValue(generator="system-uuid") @GenericGenerator(name="system-uuid", strategy = "uuid") – BBogdan Jan 31 '14 at 10:13
  • I can do this, but then JPA creates crazy column types in the different databases. I want to specify what type is created for each database. – JulianG Feb 11 '14 at 08:36
0

I would use a SequenceGenerator. Check this answer for more details. You will have to make one seq. generator per entity in order to make sure you do not get concurrency problems. Also make sure that you set a good/not existent initial value.

Community
  • 1
  • 1
V G
  • 18,822
  • 6
  • 51
  • 89