How to create specific table in MySQL DB using Hibernate

Question

so following situation:

I am using Hibernate to upload/download data to and from the database, which works fine.

Currently I have two tables, "Parent"(which is the parent) and "Child"(which is the child). I have a OneToMany / ManyToOne relationship. I only upload the parent and the child is automatically uploaded due to cascade. The Parent.class has a List(Child).

If an entity of "Parent" is changed/updated, I want to move the "old version" into a table called "Parent_OLD" (and of course the child via cascade) and add a time stamp to the old entity.

So the problem I am currently encountering is: I have tried to solve it by creating a new class "Parent_OLD" which looks exactly like the "Parent" except for the time stamp. So what I did is: Whenever I upload new data to the DB, I check if the entity already exists and if it does I pass the data via "getters" to "Parent_OLD". This works fine so far, because I only have like 3 attributes I need to pass, but if I end up with more fields, it would be handy to have a different solution to this.

But what does not work is to move the "child_OLD" to the "Child_OLD". So as mentioned above, the Parent.class has a List(Child). Child_NEW and Child_OLD actually look exactly alike, because they do not need a time stamp. But due to the Hibernate Annotation on top of the Child.class every entity of Child.class is loaded into "Child"-table.

So I created a Child_OLD.class. This means: I have a current Parent entity, with a List(Child). I want to pass the parent (including the list) to the Parent_OLD entity.

But I cannot cast the List(Child) to List(Child_OLD). I have tried casting it to a list of objects and then to Child_OLD, but this did not work. Passing the attributes via "getters" is no option as the class has like 60 fields. I also tried creating an abstract class "Child" but I still did not manage to get it to work.

So I thought it would be amazingly simple if I could tell Hibernate to load certain entities into a different table. So maybe something like an annotation above the CheckIfExistsAndIfItDoesCopyToOldTable-Method. Is this possible? Or does anyone know how I could solve my problem?

I guess I need to apologize because I do not know how clear I managed to explain my problem. So sorry for the possibly confusing text and thank you in advance for your ideas!

Edit: I want to backup the Parent as well as the Child. But this should happen via cascade if possible. So, by backing up the Parent, the List with the children should be moved into the Child-BackUp-Table.

score 0 · Answer 1 · answered Nov 13 '17 at 10:12

0

as i understand you are not planning to save List(Child) to a backup table, this means you don't have to include to field as a DB column.

For the general problem you are having it might be possible to solve it with DB side solution and not a server side one.. have you checked DB triggers ? and DB date columns with default value/ auto generated values ?

MySQL trigger to insert row into another table

answered Nov 13 '17 at 10:12

nabeel

269
1
7

Okay sorry, in that case I did not express myself clearly enough. I want to save the Parent as well as the Child in two OLD_Tables – Philomatic Nov 13 '17 at 10:15
isn't using triggers a good solution ? must be done in Java side ? – nabeel Nov 13 '17 at 10:17
Thank you, I will check it otu! – Philomatic Nov 13 '17 at 10:18
So I had a brief look at it this afternoon, and the problem is that I have never used MySQL commands before and I am not familiar with them at all. I would definitely prefer a solution on the java side in case you have anything else in mind.. – Philomatic Nov 13 '17 at 15:03

score 0 · Answer 2 · answered Nov 13 '17 at 16:01

Hibernate supports Table Per Concrete class

as you mentioned you will need to "clone" the table with same data adding only time stamp.

you may try this:

@Entity
@Table(name = "child")
class Child
    // variables ..

@Entity
@Table(name = "child_history")
class Child_OLD extends Child
    // adding time stamp variable

@Entity
@Table(name = "parent")
class Parent
    // variables ..
    List<Child> ..

@Entity
@Table(name = "parent_history")
class Parent_OLD extends Parent
    // adding time stamp variable

the 2 sub-classes Child_OLD and Parent_OLD will be almost empty.. all you have to implement the base Child and Parent classes, and in the sub-classes (Child_OLD and Parent_OLD) you will have to create a clone constructors.

the clone constructors will have to preform a deep copy of the object (also for the List..)

to have minimal dates/timestamps manipulations in java, you may use mysql built-in features which gives a default value on insert/update and will save you time instead of managing date variables in java as below:

/* managed by DB column definition */
@Column(
        name = "SYS_CREATION_DATE",
        insertable=false,
        updatable=false,
        columnDefinition="DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
        )
private Date sysCreationDate;

pom.xml dependencies i used (not sure if relevant)

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.5</version>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
    <version>1.5.7.RELEASE</version>
</dependency>

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-entitymanager</artifactId>
    <version>5.2.11.Final</version>
</dependency>

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.2.11.Final</version>
</dependency>

hope it helps

How to create specific table in MySQL DB using Hibernate

2 Answers2