Mapping strategies tutorial
This tutorial shows the result of the main strategies when applied to a simple class
To make it interesting, we define some top level class with 2 sub-classes with different attributes:
public class BaseClass extends IDNamedProperty {
}
public class SubClass1 extends BaseClass {
public String attribute1 = null;
}
public class SubClass2 extends BaseClass {
@Required
public int attribute2 = 1;
}
download test source
The output below is generated by this test, which creates the schema, saves an instance of each type and reloads them.
Running this test with each
TableMappingStrategy produces the following database mapping and SQL calls:
ALL_INLINE
This strategy rolls up all the fields from the sub-classes into the same table
CREATE TABLE BaseClass (attribute1 VARCHAR(255), attribute2 INT, name VARCHAR(255), id INT NOT NULL PRIMARY KEY , class VARCHAR(128) NOT NULL)
INSERT INTO BaseClass(id,name,class) VALUES (7,'baseinstance','class com.metastores.system.database.mapping.MultiClassMappingTest$BaseClass')
INSERT INTO BaseClass(id,attribute1,name,class) VALUES (13,'at1','subclass1','class com.metastores.system.database.mapping.MultiClassMappingTest$SubClass1')
INSERT INTO BaseClass(id,attribute2,name,class) VALUES (19,123,'subclass2','class com.metastores.system.database.mapping.MultiClassMappingTest$SubClass2')
SELECT attribute1,attribute2,name,id,class FROM BaseClass WHERE id=7 ORDER BY name ASC
SELECT attribute1,attribute2,name,id,class FROM BaseClass WHERE id=13 ORDER BY name ASC
SELECT attribute1,attribute2,name,id,class FROM BaseClass WHERE id=19 ORDER BY name ASC
Note: when reloading the objects, once the object type is known, the fields which aren't used are simply ignored.
INLINE_SUBCLASSES
This strategy is similar to the ALL_INLINE
strategy, except that all the subclasses are rolled up in a table separate from the main table
CREATE TABLE BaseClass (name VARCHAR(255), id INT PRIMARY KEY , class VARCHAR(128))
CREATE TABLE BaseClass_subclass (id INT NOT NULL PRIMARY KEY , attribute1 VARCHAR(255), attribute2 INT)
ALTER TABLE BaseClass_subclass ADD CONSTRAINT FK_BaseClass FOREIGN KEY (id) REFERENCES BaseClass (id)
INSERT INTO BaseClass(id,name,class) VALUES (7,'baseinstance','class com.metastores.system.database.mapping.MultiClassMappingTest$BaseClass')
INSERT INTO BaseClass(id,name,class) VALUES (13,'subclass1','class com.metastores.system.database.mapping.MultiClassMappingTest$SubClass1')
INSERT INTO BaseClass_subclass(attribute1,id) VALUES ('at1',13)
INSERT INTO BaseClass(id,name,class) VALUES (19,'subclass2','class com.metastores.system.database.mapping.MultiClassMappingTest$SubClass2')
INSERT INTO BaseClass_subclass(attribute2,id) VALUES (123,19)
SELECT t.name, t.id, t.class, t1.attribute1, t1.attribute2 FROM BaseClass t LEFT JOIN BaseClass_subclass t1 ON t1.id=t.id WHERE t.id=7 ORDER BY name ASC
SELECT t.name, t.id, t.class, t1.attribute1, t1.attribute2 FROM BaseClass t LEFT JOIN BaseClass_subclass t1 ON t1.id=t.id WHERE t.id=13 ORDER BY name ASC
SELECT t.name, t.id, t.class, t1.attribute1, t1.attribute2 FROM BaseClass t LEFT JOIN BaseClass_subclass t1 ON t1.id=t.id WHERE t.id=19 ORDER BY name ASC
TABLE_PER_SUBCLASS
This strategy creates a new table for each subclass:
CREATE TABLE BaseClass (name VARCHAR(255), id INT NOT NULL PRIMARY KEY , class VARCHAR(128) NOT NULL)
CREATE TABLE SubClass1 (id INT NOT NULL PRIMARY KEY , attribute1 VARCHAR(255))
CREATE TABLE SubClass2 (id INT NOT NULL PRIMARY KEY , attribute2 INT NOT NULL)
CREATE TABLE ClusteredSequenceCacheEntry (D_value INT, D_database VARCHAR(64), tableName VARCHAR(64), columnName VARCHAR(64), CONSTRAINT UQ_database_tableName_columnName UNIQUE (D_database,tableName,columnName))
ALTER TABLE SubClass1 ADD CONSTRAINT FK_BaseClass FOREIGN KEY (id) REFERENCES BaseClass (id)
ALTER TABLE SubClass2 ADD CONSTRAINT FK_BaseClass_2 FOREIGN KEY (id) REFERENCES BaseClass (id)
SELECT D_value,D_database,tableName,columnName FROM ClusteredSequenceCacheEntry WHERE columnName='id' AND tableName='BaseClass' AND D_database='test'
INSERT INTO BaseClass(id,name,class) VALUES (7,'baseinstance','class com.metastores.system.database.mapping.MultiClassMappingTest$BaseClass')
INSERT INTO BaseClass(id,name,class) VALUES (13,'subclass1','class com.metastores.system.database.mapping.MultiClassMappingTest$SubClass1')
INSERT INTO SubClass1(attribute1,id) VALUES ('at1',13)
INSERT INTO BaseClass(id,name,class) VALUES (19,'subclass2','class com.metastores.system.database.mapping.MultiClassMappingTest$SubClass2')
INSERT INTO SubClass2(attribute2,id) VALUES (123,19)
SELECT t.name, t.id, t.class, t1.attribute1, t2.attribute2 FROM BaseClass t LEFT JOIN SubClass1 t1 ON t1.id=t.id LEFT JOIN SubClass2 t2 ON t2.id=t.id WHERE t.id=7 ORDER BY name ASC
SELECT t.name, t.id, t.class, t1.attribute1, t2.attribute2 FROM BaseClass t LEFT JOIN SubClass1 t1 ON t1.id=t.id LEFT JOIN SubClass2 t2 ON t2.id=t.id WHERE t.id=13 ORDER BY name ASC
SELECT t.name, t.id, t.class, t1.attribute1, t2.attribute2 FROM BaseClass t LEFT JOIN SubClass1 t1 ON t1.id=t.id LEFT JOIN SubClass2 t2 ON t2.id=t.id WHERE t.id=19 ORDER BY name ASC
PROPERTIES_TABLE
This strategy uses a single table where each field is stored as a row with the value serialized:
CREATE TABLE BaseClass (D_value VARCHAR(255), D_position INT NOT NULL, D_column VARCHAR(255), id INT)
# Saving an instance of BaseClass:
INSERT INTO BaseClass(id,D_position,D_value,D_column) VALUES (7,0,'baseinstance','name_3')
INSERT INTO BaseClass(id,D_position,D_value,D_column) VALUES (7,0,'class com.metastores.system.database.mapping.MultiClassMappingTest$BaseClass','class_4')
# Saving an instance of SubClass1:
INSERT INTO BaseClass(id,D_position,D_value,D_column) VALUES (13,0,'at1','attribute1')
INSERT INTO BaseClass(id,D_position,D_value,D_column) VALUES (13,0,'subclass1','name_3')
INSERT INTO BaseClass(id,D_position,D_value,D_column) VALUES (13,0,'class com.metastores.system.database.mapping.MultiClassMappingTest$SubClass1','class_4')
# Saving an instance of SubClass2:
INSERT INTO BaseClass(id,D_position,D_value,D_column) VALUES (19,0,'123','attribute2')
INSERT INTO BaseClass(id,D_position,D_value,D_column) VALUES (19,0,'subclass2','name_3')
INSERT INTO BaseClass(id,D_position,D_value,D_column) VALUES (19,0,'class com.metastores.system.database.mapping.MultiClassMappingTest$SubClass2','class_4')
# Reloading all the objects:
SELECT D_value FROM BaseClass WHERE id=7 AND D_column='class_4'
SELECT D_value FROM BaseClass WHERE id=7 AND D_column='name_3'
SELECT D_value FROM BaseClass WHERE id=13 AND D_column='class_4'
SELECT D_value FROM BaseClass WHERE id=13 AND D_column='attribute1'
SELECT D_value FROM BaseClass WHERE id=13 AND D_column='name_3'
SELECT D_value FROM BaseClass WHERE id=13 AND D_column='class_4'
SELECT D_value FROM BaseClass WHERE id=19 AND D_column='class_4'
SELECT D_value FROM BaseClass WHERE id=19 AND D_column='attribute2'
SELECT D_value FROM BaseClass WHERE id=19 AND D_column='name_3'
SELECT D_value FROM BaseClass WHERE id=19 AND D_column='class_4'