devloop icon
Metastores

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'

Related Links