This discussion is archived
4 Replies Latest reply: Nov 7, 2012 3:07 PM by 269855 RSS

Conditional Sequence Generator Selection

269855 Newbie
Currently Being Moderated
I'm still a JPA neophyte but starting to get the hang of things.

Attempting to integrate JPA with a legacy RDBMS supporting both SQL Server and Oracle. For Oracle, we use sequences and for SQL, a table.

I need to be able to conditionally use one of two generators based on database type:

Oracle

@Id
@GeneratedValue(generator="generator")
@SequenceGenerator(name="generator", sequenceName="PBX_SEQ", allocationSize=1)
@Column(name="PBX_ID")
protected long pbxId;

SQL Server

@Id
@GeneratedValue(generator="generator")
@TableGenerator(name="generator", table="SEQTAB", pkColumnName="TABLENAM", valueColumnName="ID", pkColumnValue="PBX", allocationSize=1)
@Column(name="PBX_ID")
protected long pbxId;

How best would one approach this challenge?

Thanks.
  • 1. Re: Conditional Sequence Generator Selection
    cdelahun Pro
    Currently Being Moderated
    Hello,

    How will you differentiate access to the two databases? I assume you will need a the least two different persistence units, since one unit can only connect to one database at a time. So you can use the same entities in multiple units, and just override the sequence generator using an orm.xml file as needed. An example JPA orm.xml is here:
    http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Configuration/JPA/orm.xml

    Hope this helps.

    Regards,
    Chris
  • 2. Re: Conditional Sequence Generator Selection
    269855 Newbie
    Currently Being Moderated
    Chris,

    You're correct, I have two persistent units defined in my persistence.xml - one for SQL Server and one for Oracle.

    This challenge lies in the fact that I'm not using an orm.xml file. All mapping is via annotations. To exacerbate things, this is a legacy RDBMS with which I'm attempting to integrate and can't change things such as sequence methodology, names, etc.

    I thought I had it with the following SessionCustomizer:

    public class SequenceCustomizer implements SessionCustomizer {

    public void customize(Session session) throws Exception {
    Sequence sequence = null;
    String s = null;
    Reflections reflections = new Reflections("com.telesoft");
    Set<Class<?>> classes = reflections.getTypesAnnotatedWith(Entity.class);
    for (Class<?> clazz : classes) {
    s = clazz.getSimpleName().toUpperCase();
    if (session.getDatasourcePlatform() instanceof SQLServerPlatform)
    sequence = new TableSequence(s, 1, "seqtab", "tablenam", "id");
    else
    sequence = new NativeSequence(s + "_SEQ", 1);
    session.getLogin().addSequence(sequence);
    }
    }
    }

    However, I'm still unable to use a single name:

    @Id
    @GeneratedValue(generator="PBX_SEQ") // PBX_SEQ for Oracle and PBX for SQL Server
    @Column(name="PBX_ID")
    protected long pbxId;

    I'm trying to avoid having to implement my own Sequence wrapper to handle the name issue.

    Any ideas?

    Thanks.
  • 3. Re: Conditional Sequence Generator Selection
    cdelahun Pro
    Currently Being Moderated
    If you cannot use "PBX_SEQ" for the row id within the table generator, you might be able to add it to the sequence map directly under the "PBX_SEQ" name:
    if (session.getDatasourcePlatform() instanceof SQLServerPlatform) {
        sequence = new TableSequence(s, 1, "seqtab", "tablenam", "id");
        session.getLogin().getSequences().put(s + "_SEQ", sequence );
    else {
        sequence = new NativeSequence(s + "_SEQ", 1);
        session.getLogin().addSequence(sequence);
    }
    I have not tested if it will work.

    Best Regards,
    Chris
  • 4. Re: Conditional Sequence Generator Selection
    269855 Newbie
    Currently Being Moderated
    With minor modification, that did work.

    session.getLogin().getSequences() was null but I instantiated a HashMap, called the setter, and all is well.

    Thanks Chris!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points