Dynamic @Table name based on input

Question

We have Hibernate based application where due to a large data set, two sets of tables are created where user_id will either be mapped in the UserTickets table or RestOfWorldTickets table. Would like to know how @Table on the entity java objects can be dynamically mapped based on some user selection.

@Entity
@Table(name = "**UserTickets**")
public class UserTickets {

  @Id
  @Column("Internal_id")
  @GeneratedValue(strategy = IDENTITY)
  private int internalId;

  @Column("user_id")
  private int userId;

  @Column("state")
  private String state;

  @Column("city")
  private String city;

  @Column("address")
  private String address;

  @Column("ticketNumber")
  private String ticketNumber;
  ..
  // Setters and Getters 

}

UserTickets DB Table
Internal_id  |  User_id | State  |  City   | Address | ticket_number | ...
101          |  1025    | AZ     |  Tuscan | ..      | 10256912      |
102          |  1026    | NC     |  Durham | ..      | 10256983   

RestOfWorldTickets DB Table
Internal_id  |  User_id | State  |  City   | Address | ticket_number |..
101          |  1058    | {null} |  London | ..      | 102578963     |..
102          |  1059    | {null} |  Berlin | ..      | 112763458     |.. 


The user and table mapping are now defined in a new table.

TableMapping Database table.
Internal_id  | User_id  | TableMapped        |
1            | 1025     | UserTickets        |
2            | 1026     | UserTickets        |
3            | 1058     | RestOfWorldTickets |
4            | 1059     | RestOfWorldTickets |

So, using the UserTickets result set, how I map @Table attribute on the UserTickets Java object dynamically so that my Criteria API queries will work automatically without changing them to HQL queries?

Maybe using Interceptors http://docs.jboss.org/hibernate/orm/3.5/javadocs/org/hibernate/Interceptor.html?

Answer 1

I am quite unsure what you actually need but i try to give my solution based on a few quesses. Changing @Table dynamically is not -afaik- possible but if i guessed right you could have some benefit of inheritance in this case:

1st modify UserTickets to allow inheritance

@Entity
//@Table(name = "**UserTickets**")
@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)
public class UserTickets {

    @Id // this annotation was missing from yours ? 
    @Column(name="Internal_id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
       // identity generated problems in openjpa so i changed it to SEQUENCE
    private int internalId;
    @Column(name="user_id") private int userId;
    @Column(name="state") private String state;
    @Column(name="city") private String city;
    @Column(name="address") private String address;
    @Column(name="ticketNumber") private String ticketNumber;
}

2nd create a new entity

@Entity
public class RestOfWorldTickets extends UserTickets {
   // yes i am just an empty class, TABLE_PER_CLASS gives me the fields
}

This allows you to use criteriaqueries against UserTickets but in addition the queries are done against RestOfWorldTickets also. So now when you search with user id result set will contain results from both tables. Checking/ loggin -for example with instanceof operator- you can see which one the ticket is.

"Disclaimer" i am using and testing with openjpa so there can be some differences/probkems with this solution...