In all these tests I am using some strategies to make sure that tables and fields name generated by Hibernate will be the same as Eclipse-Link.
Persistence classes scanning
Eclipse-Link
Control Loop runtime uses persistence.xml file: is the deployment descriptor file for persistence using JPA. It specifies the persistence units and declares the managed persistence classes, the object/relation mapping, and the database connection details.
Hibernate
SpringBoot auto-configuration can automatically scan entity classes. In Control Loop Runtime we can use @EntityScan annotation because entity classes are not placed in the main application package or its sub-packages. In this situation, we need declare the package or list of packages in the main configuration class within @EntityScan annotation.
Application.java
.............................
@EntityScan({"org.onap.policy.models.tosca.simple.concepts",
"org.onap.policy.clamp.controlloop.models.controlloop.persistence.concepts"})
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
Implications using Hibernate and SpringBoot scan
persistence.xml file doesn't contain all JPA classes in "org.onap.policy.models.tosca.simple.concepts" package, so SpringBoot will intercept additional classes (not defined into persistence.xml file) and will generate additional tables into controlloop database for them: ToscaEventFilter, ToscaModel, ToscaServiceTemplates, ToscaServiceTemplates_ToscaServiceTemplate, ToscaTimeInterval, ToscaTrigger.
Schema generation
Boolean and TimeStamp
JpaExample
@Embeddable
@Data
public class ExampleKey implements Serializable {
private static final long serialVersionUID = 1L;
@Column(name = "name", length = 120)
private String name;
@Column(name = "version", length = 20)
private String version;
}
@Entity
@Table(name = "Example")
@Data
@EqualsAndHashCode
public class JpaExample implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@Column
private Boolean primed;
@Column(name = "timeStamp", precision = 3)
@Temporal(TemporalType.TIMESTAMP)
@NotNull
private Date timeStamp;
}
Eclipse-Link
MariaDB [controlloop]> describe Example;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| PRIMED | tinyint(1) | YES | | 0 | |
| timeStamp | datetime(3) | YES | | NULL | |
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+-----------+--------------+------+-----+---------+-------+
Hibernate
MariaDB [controlloop]> describe Example;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
| primed | bit(1) | YES | | NULL | |
| timeStamp | datetime(6) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
Map of Strings
Eclipse-Link
MariaDB [controlloop]> describe ExampleMap;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+-------+
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleMap_ATTRIBUTES;
+--------------------------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------------+----------------------------------------------------------------------------------------------------------+
| JpaExampleMap_ATTRIBUTES | CREATE TABLE `JpaExampleMap_ATTRIBUTES` (
`name` varchar(120) DEFAULT NULL,
`version` varchar(20) DEFAULT NULL,
`ATTRIBUTES` longtext DEFAULT NULL,
`ATTRIBUTES_KEY` varchar(255) DEFAULT NULL,
KEY `FK_JpaExampleMap_ATTRIBUTES_name` (`name`,`version`),
CONSTRAINT `FK_JpaExampleMap_ATTRIBUTES_name` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleMap` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------------------+----------------------------------------------------------------------------------------------------------+
Hibernate
MariaDB [controlloop]> describe ExampleMap;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+-------+
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleMap_ATTRIBUTES;
+--------------------------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------------+----------------------------------------------------------------------------------------------------------+
| JpaExampleMap_ATTRIBUTES | CREATE TABLE `JpaExampleMap_ATTRIBUTES` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
`attributes` longtext DEFAULT NULL,
`attributes_KEY` varchar(255) NOT NULL,
PRIMARY KEY (`name`,`version`,`attributes_KEY`),
CONSTRAINT `FKrwvpo54nao070vsy5p23xnps7` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleMap` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------------------+----------------------------------------------------------------------------------------------------------+
JpaToscaCapabilityAssignment_ATTRIBUTES table
Eclipse-Link
MariaDB [controlloop]> SHOW CREATE TABLE JpaToscaCapabilityAssignment_ATTRIBUTES;
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| JpaToscaCapabilityAssignment_ATTRIBUTES | CREATE TABLE `JpaToscaCapabilityAssignment_ATTRIBUTES` (
`name` varchar(120) DEFAULT NULL,
`version` varchar(20) DEFAULT NULL,
`ATTRIBUTES` longtext DEFAULT NULL,
`ATTRIBUTES_KEY` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
Currently in controlloop database, I have not found out why foreign key has not be generated in this table and in all '_ATTRIBUTES', '_META' and '_CONSTRAINTS' tables as well.
Hibernate
MariaDB [controlloop]> SHOW CREATE TABLE JpaToscaCapabilityAssignment_ATTRIBUTES;
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| JpaToscaCapabilityAssignment_ATTRIBUTES | CREATE TABLE `JpaToscaCapabilityAssignment_ATTRIBUTES` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
`attributes` longtext DEFAULT NULL,
`attributes_KEY` varchar(255) NOT NULL,
PRIMARY KEY (`name`,`version`,`attributes_KEY`),
CONSTRAINT `FKjuqj4nashp9jx76h5eh5psp7l` FOREIGN KEY (`name`, `version`)
REFERENCES `ToscaCapabilityAssignment` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
Implications using Hibernate
To be continued
Map of Objects using OneToMany
JpaExampleObjMap
@Entity
@Table(name = "ExampleObjMap")
@Data
@EqualsAndHashCode
public class JpaExampleObjMap implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true)
@Lob
private Map<@NotNull String, @NotNull JpaExample> examples;
}
Representation of the example in YAML of what we could save using JpaExampleObjMap entity.
Example
ExampleObjMap:
name: ExampleObjMap
version: 1.0.0
examples:
MyKey1:
name: example1
version: 1.0.0
MyKey2:
name: example2
version: 1.0.0
Eclipse-Link
MariaDB [controlloop]> describe ExampleObjMap;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| EXAMPLES | longblob | YES | | NULL | |
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+----------+--------------+------+-----+---------+-------+
After saving the example:
MariaDB [controlloop]> select * from ExampleObjMap;
+--------------------------+---------------+---------+
| EXAMPLES | name | version |
+--------------------------+---------------+---------+
| .... <binary code> .... | ExampleObjMap | 1.0.0 |
+--------------------------+---------------+---------+
Removing @Lob Annotation
The keys name and version of the JpaExampleObjMap as the same as JpaExample, so Eclipse-Link creates a wrong ExampleObjMap_Example table.
MariaDB [controlloop]> describe ExampleObjMap;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+-------+
MariaDB [controlloop]> SHOW CREATE TABLE ExampleObjMap_Example;
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| ExampleObjMap_Example | CREATE TABLE `ExampleObjMap_Example` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
`EXAMPLES_KEY` varchar(255) DEFAULT NULL,
PRIMARY KEY (`name`,`version`),
CONSTRAINT `FK_ExampleObjMap_Example_name` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleObjMap` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
Using these tables I have got this error: Cannot add or update a child row: a foreign key constraint fails (`controlloop`.`ExampleObjMap_Example`, CONSTRAINT `FK_ExampleObjMap_Example_name` FOREIGN KEY (`name`, `version`) REFERENCES `ExampleObjMap` (`name`, `version`))
Error Code: 1452
Map of Objects using OneToMany (fixed)
JpaExampleObjMap
@Entity
@Table(name = "ExampleObjMap")
@Data
@EqualsAndHashCode
public class JpaExampleObjMap implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinTable(
joinColumns = {@JoinColumn(name = "parent_name", referencedColumnName = "name"),
@JoinColumn(name = "parent_version", referencedColumnName = "version")},
inverseJoinColumns = {@JoinColumn(name = "child_name", referencedColumnName = "name"),
@JoinColumn(name = "child_version", referencedColumnName = "version")})
private Map<@NotNull String, @NotNull JpaExample> examples;
}
Eclipse-Link
MariaDB [controlloop]> SHOW CREATE TABLE ExampleObjMap_Example;
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| ExampleObjMap_Example | CREATE TABLE `ExampleObjMap_Example` (
`parent_name` varchar(120) NOT NULL,
`parent_version` varchar(20) NOT NULL,
`child_name` varchar(120) NOT NULL,
`child_version` varchar(20) NOT NULL,
`EXAMPLES_KEY` varchar(255) DEFAULT NULL,
PRIMARY KEY (`parent_name`,`parent_version`,`child_name`,`child_version`),
KEY `FK_ExampleObjMap_Example_child_name` (`child_name`,`child_version`),
CONSTRAINT `FK_ExampleObjMap_Example_child_name` FOREIGN KEY (`child_name`, `child_version`)
REFERENCES `Example` (`name`, `version`),
CONSTRAINT `FK_ExampleObjMap_Example_parent_name` FOREIGN KEY (`parent_name`, `parent_version`)
REFERENCES `ExampleObjMap` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
After saving the example:
MariaDB [controlloop]> select * from ExampleObjMap;
+---------------+---------+
| name | version |
+---------------+---------+
| ExampleObjMap | 1.0.0 |
+---------------+---------+
MariaDB [controlloop]> select * from Example;
+--------+-----------+----------+---------+
| PRIMED | timeStamp | name | version |
+--------+-----------+----------+---------+
| NULL | NULL | example1 | 1.0.0 |
| NULL | NULL | example2 | 1.0.0 |
+--------+-----------+----------+---------+
MariaDB [controlloop]> select * from ExampleObjMap_Example;
+---------------+----------------+------------+---------------+--------------+
| parent_name | parent_version | child_name | child_version | EXAMPLES_KEY |
+---------------+----------------+------------+---------------+--------------+
| ExampleObjMap | 1.0.0 | example1 | 1.0.0 | MyKey1 |
| ExampleObjMap | 1.0.0 | example2 | 1.0.0 | MyKey2 |
+---------------+----------------+------------+---------------+--------------+
Hibernate
MariaDB [controlloop]> SHOW CREATE TABLE ExampleObjMap_Example;
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| ExampleObjMap_Example | CREATE TABLE `ExampleObjMap_Example` (
`parent_name` varchar(120) NOT NULL,
`parent_version` varchar(20) NOT NULL,
`child_name` varchar(120) NOT NULL,
`child_version` varchar(20) NOT NULL,
`examples_KEY` varchar(255) NOT NULL,
PRIMARY KEY (`parent_name`,`parent_version`,`examples_KEY`),
UNIQUE KEY `UK_ma8t20i58tt5ilqjrk2ged242` (`child_name`,`child_version`),
CONSTRAINT `FK2n1x5g38x55wejkvnvhqin980` FOREIGN KEY (`parent_name`, `parent_version`)
REFERENCES `ExampleObjMap` (`name`, `version`),
CONSTRAINT `FKstwyuf69lrj4tjpafwriuwp3h` FOREIGN KEY (`child_name`, `child_version`)
REFERENCES `Example` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
After saving the example:
MariaDB [controlloop]> select * from ExampleObjMap;
+---------------+---------+
| name | version |
+---------------+---------+
| ExampleObjMap | 1.0.0 |
+---------------+---------+
MariaDB [controlloop]> select * from Example;
+----------+---------+--------+-----------+
| name | version | primed | timeStamp |
+----------+---------+--------+-----------+
| example1 | 1.0.0 | NULL | NULL |
| example2 | 1.0.0 | NULL | NULL |
+----------+---------+--------+-----------+
MariaDB [controlloop]> select * from ExampleObjMap_Example;
+---------------+----------------+------------+---------------+--------------+
| parent_name | parent_version | child_name | child_version | examples_KEY |
+---------------+----------------+------------+---------------+--------------+
| ExampleObjMap | 1.0.0 | example1 | 1.0.0 | MyKey1 |
| ExampleObjMap | 1.0.0 | example2 | 1.0.0 | MyKey2 |
+---------------+----------------+------------+---------------+--------------+
Map of Objects using ElementCollection
JpaExampleObjMap
@Entity
@Table(name = "ExampleObjMapEc")
@Data
@EqualsAndHashCode
public class JpaExampleObjMapEc implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@ElementCollection
@Lob
private Map<@NotNull String, @NotNull JpaExample> examples;
}
Eclipse-Link
MariaDB [controlloop]> describe ExampleObjMapEc;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+-------+
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleObjMapEc_EXAMPLES;
+-----------------------------+-------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------------+-------------------------------------------------------------------------------------------------------+
| JpaExampleObjMapEc_EXAMPLES | CREATE TABLE `JpaExampleObjMapEc_EXAMPLES` (
`name` varchar(120) DEFAULT NULL,
`version` varchar(20) DEFAULT NULL,
`EXAMPLES` longblob DEFAULT NULL,
`EXAMPLES_KEY` varchar(255) DEFAULT NULL,
KEY `FK_JpaExampleObjMapEc_EXAMPLES_name` (`name`,`version`),
CONSTRAINT `FK_JpaExampleObjMapEc_EXAMPLES_name` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleObjMapEc` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------------+-------------------------------------------------------------------------------------------------------+
After saving the example:
MariaDB [controlloop]> select * from ExampleObjMapEc;
+---------------+---------+
| name | version |
+---------------+---------+
| ExampleObjMap | 1.0.0 |
+---------------+---------+
MariaDB [controlloop]> select * from JpaExampleObjMapEc_EXAMPLES;
+---------------+---------+---------------------------+--------------+
| name | version | EXAMPLES | EXAMPLES_KEY |
+---------------+---------+---------------------------+--------------+
| ExampleObjMap | 1.0.0 | .... <binary code> .... | MyKey1 |
| ExampleObjMap | 1.0.0 | .... <binary code> .... | MyKey2 |
+---------------+---------+---------------------------+--------------+
Hibernate
MariaDB [controlloop]> describe ExampleObjMapEc;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+-------+
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleObjMapEc_EXAMPLES;
+-----------------------------+-------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------------+-------------------------------------------------------------------------------------------------------+
| JpaExampleObjMapEc_EXAMPLES | CREATE TABLE `JpaExampleObjMapEc_EXAMPLES` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
`examples_name` varchar(120) NOT NULL,
`examples_version` varchar(20) NOT NULL,
`examples_KEY` varchar(255) NOT NULL,
PRIMARY KEY (`name`,`version`,`examples_KEY`),
UNIQUE KEY `UK_o5b5lwcueiue1x7wf2j9l0ohb` (`examples_name`,`examples_version`),
CONSTRAINT `FKeu2s8p9mhstus32uliuwib8rr` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleObjMapEc` (`name`, `version`),
CONSTRAINT `FKf66pkeal9tdygic4pvspxhph5` FOREIGN KEY (`examples_name`, `examples_version`)
REFERENCES `Example` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------------+-------------------------------------------------------------------------------------------------------+
After saving the example (it needs an extra step: save JpaExample objects before JpaExampleObjMapEc object):
MariaDB [controlloop]> select * from ExampleObjMapEc;
+-----------------+---------+
| name | version |
+-----------------+---------+
| ExampleObjMapEc | 1.0.0 |
+-----------------+---------+
MariaDB [controlloop]> select * from Example;
+----------+---------+--------+-----------+
| name | version | primed | timeStamp |
+----------+---------+--------+-----------+
| example1 | 1.0.0 | NULL | NULL |
| example2 | 1.0.0 | NULL | NULL |
+----------+---------+--------+-----------+
MariaDB [controlloop]> select * from JpaExampleObjMapEc_EXAMPLES;
+-----------------+---------+---------------+------------------+--------------+
| name | version | examples_name | examples_version | examples_KEY |
+-----------------+---------+---------------+------------------+--------------+
| ExampleObjMapEc | 1.0.0 | example1 | 1.0.0 | MyKey1 |
| ExampleObjMapEc | 1.0.0 | example2 | 1.0.0 | MyKey2 |
+-----------------+---------+---------------+------------------+--------------+