Hi,
I have a legacy database in my Spring Boot project (Hibernate JPA) and there is one particular problem I don't know how to resolve...
I have one DB (MySQL) table defined like this (it holds code descriptions for various other columns and tables):
Code:
CREATE TABLE `sifarnik` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ref_table` varchar(45) NOT NULL,
`ref_column` varchar(45) NOT NULL,
`seq_no` int(11) DEFAULT NULL,
`code` varchar(45) NOT NULL,
`description` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `u_table_col_code` (`ref_table`,`ref_column`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And I have multiple other tables which relate (by values) to this table, e.g.:
Code:
CREATE TABLE `table2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`field1` varchar(100) NOT NULL,
`code1` varchar(45) NOT NULL,
`code2` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
In SQL I would join this two tables like this to get what I need:
Code:
SELECT t2.field1, s1.description, s2.description
FROM table2 AS t2
JOIN sifarnik AS s1 ON (s1.ref_table='table2' AND s1.ref_column='code1' AND s1.code=code1)
JOIN sifarnik AS s2 ON (s2.ref_table='table2' AND s2.ref_column='code2' AND s2.code=code2);
How do I annotate my entity classes so that I get multiple references to table2 entity/entities ?
Is it even possible?
I tried to do it like this...
First I created abstract entity class as a base for inheritance on sifarnik table:
Code:
@Entity
@Table(name="sifarnik")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "ref_table", discriminatorType = DiscriminatorType.STRING, length = 45)
public abstract class Sifarnik implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private int id;
private String code;
private String description;
@Column(name="ref_column")
private String refColumn;
@Column(name="ref_table")
private String refTable;
@Column(name="seq_no")
private int seqNo;
// getter and setters bellow this but I won't C/P them
}
Then I created abstract class that inherits from the root one and filters only rows which have ref_table = 'table2' (if I understood the meaning of the annotations correctly)
Code:
@DiscriminatorValue(value = "table2")
@MappedSuperclass
@DiscriminatorColumn(name = "ref_column", discriminatorType = DiscriminatorType.STRING, length = 45)
public abstract class Table2Sifarnik extends Sifarnik {
static final long serialVersionUID = -2671589845350982604L;
}
And finally I defined two entity classes one for each ref_column values possible for the given ref_table:
Code:
@Entity
@DiscriminatorValue(value = "code1")
public class Code1Table2Sifarnik extends RezultatSifarnik {
private static final long serialVersionUID = -3544912451370487468L;
@OneToMany(mappedBy = "t2Code1")
private List<Table2> table2Rows;
}
Code:
public class Code2Table2Sifarnik extends RezultatSifarnik {
private static final long serialVersionUID = -3544912451370487468L;
@OneToMany(mappedBy = "t2Code2")
private List<Table2> table2Rows;
}
And I also referenced those two entities in Table2 entity:
Code:
@Entity
@Table(name = "table2")
public class Table2 implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(unique = true, nullable = false)
private int id;
@Column(nullable = false, length = 45)
private String field1;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "code1", referencedColumnName = "code", nullable = false, updatable = false)
private Code1Table2Sifarnik code1;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "code2", referencedColumnName = "code", nullable = false, updatable = false)
private Code2Table2Sifarnik code2;
}
After all this I'm getting this error:
Code:
Caused by: org.hibernate.AnnotationException: referencedColumnNames(code) of Table2.code1 referencing Code1Table2Sifarnik not mapped to a single property
Could someone, please, point me to what I'm doing wrong?
Just note that I'm not allowed to change the DB schema so I must make do with Java and Hibernate to define this relations properly.
Thank You in advance!
Login
FAQ
Search