CODEWITHSUNDEEP
javamysqlhibernate
Nikolas
Nikolas

Reputation: 44476

Hibernate mapping join on one column of constants table

I have 2 tables, the first one is quite variable, the second one contains only constants:

USER.ID   USER.NAME   USER.USER_TYPE (FK on USER_TYPE.ID)
INT       VARCHAR(64) INT(1)
----------------------------------
1         Alex        3
2         Jane        1
3         Carl        3

USER_TYPE.ID   USER_TYPE.VALUE
INT(1)         VARCHAR(64)
------------------------------
1              PENDING
2              REGISTERED
3              BANNED
4              ACTIVE

The foreign key USER.USER_TYPE is required and refering to a primary key USER_TYPE.ID in table USER_TYPE (one-to-one relation). Here is my mapping in Hibernate.

User.java

@Entity
@Table(name = "USER")
public class User {

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

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

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)   
    @JoinColumn(name = "USER_TYPE") 
    private UserType userType;
}

UserType.java

@Entity
@Table(name = "USER_TYPE")
public class UserType {

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

    @Column(name = "VALUE")
    private String value;
}

My goal is to keep the enumerated values in the database. How to map UserType's value instead of id to User and validate it? I want to pass the constant VALUE to the String instead of its ID.

private String userType;

The expected result of the first user would be:

User[id=1, name=Alex, userType=Banned]
User[id=2, name=Jane, userType=Pending]
User[id=3, name=Carl, userType=Banned]

My attempt was to use this annotation on definition of table twice with both colums switched

@SecondaryTable(name="USER_TYPE", 
    pkJoinColumns={@PrimaryKeyJoinColumn(name="ID", referencedColumnName="USER_TYPE")}
)

and get the VALUE with 

@Column(table="USER_TYPE", name="VALUE")
private String UserType;

however it leads to the error

Unable to find column with logical name: USER_TYPE in org.hibernate.mapping.Table(USER) and its related supertables and secondary tables

Upvotes: 4

Views: 3034

Answers (4)

egorlitvinenko
egorlitvinenko

Reputation: 2776

Enumerations could be simpler:

enum UserType {
    PENDING,
    REGISTERED,
    BANNED,
    ACTIVE
}

@Entity
@Table(name = "USER")
public class User {

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

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

    @javax.persistence.Enumerated
    private UserType userType;
}

If you really need separated table and @OneToOne relation, you can use @Formula from Hibernate:

@Formula("(select ut.value from user_type ut where ut.ID = USER_TYPE)")
private String userType;

Upvotes: 1

O.Badr
O.Badr

Reputation: 3141

First you need to change the relation from @OneToOne to @ManyToOne as UserType can be used by one or many User and User can have one and one UserType.

Secondly use referencedColumnName which references :

The name of the column referenced by this foreign key column.

So User entity will be:

@Entity
@Table(name = "USER")
public class User {

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

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

    @ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)   
    @JoinColumn(name = "USER_TYPE", referencedColumnName = "VALUE") 
    private UserType userType;    
}

In UserType you should apply a unique constraint using @NaturalId to value field + do not provide its setter, to prevent duplicate values as It may lead to inconsistency:

@Entity
@Table(name = "USER_TYPE")
public class UserType {

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

    @NaturalId 
    @Column(name = "VALUE")
    private String value;
}

Hope it solves the issue!

Upvotes: 2

ssk
ssk

Reputation: 106

First of all, I suggest you use ManyToOne relation. and Not CascadeType.ALL if you are not planning update or delete on USER_TYPE table.

If you do not need adding new UserTypes frequently use enum for it. It will just work as you want.

Second solution: As long as fetch = FetchType.EAGER you can add A transient field and return value of UserType in getter.

Upvotes: 0

Ramiz
Ramiz

Reputation: 464

For this really special requirement you could use SecondaryTable annotation. That is, you don't need UserType entity, but declare attribute userType as String in User entity with column mapping to the secondary table "USER_TYPE".

Upvotes: 0

Related Questions