Creating my own Object Relational Mapper “because reasons”
I was working on a project and frequently got frustrated whenever I had to update my database design. This was a by-product of rapidly altering my programs workflows and thus class definitions. I realized that I needed a more effective way to make changes to my back-end in order to improve turnaround times.
As someone who was only familiar with relational databases until recently, I decided to investigate the use of Object Relational Mappers(ORMs). However, I am really, REALLY, lazy. I couldn’t bring myself to read official documentation nor to fully understand how they worked. Furthermore, conventional ORMS seemed to require mapping classes to defined schema’s in the back-end, what I wanted to avoid in the first place.
Given all that I decided to create a library that would allow me to easily create classes and persist data in a relational database without requiring me to design any schemas or worry about modifying their structures.
The solution, introducing the Jenesis Data Store
Jenesis Data Store (JDS) was created to help me persist java classes to relational databases in a fast and reliable manner, without requiring the design of relational schema’s. The library eliminates the need to modify schema’s once a class has been altered. It also eliminates all concerns regarding “breaking changes” in regards to fields and to an extent their values. Fields, Objects and Array Types can be added, modified or removed at will. Beyond that the libraries data is structured in a way to promote fast and efficient Data Mining queries that can be used to support the application in question or to feed into specialized analytic software.
Overall, the main aim of JDS is to allow for the rapid creation and modification of java classes in order to facilitate rapid prototyping and quick development. Put simply, JDS is useful for any developer that requires a flexible schema running on top of a traditional Relational Database. Some use-cases include:
- Rapid prototyping
- Academic projects
- Single or Multi-user applications
- REST/SOAP services
JDS is licensed under the 3-Clause BSD License
Design
The concept behind JDS is quite simple. Extend a base “Entity” class, define “Fields” of a particular datatype and lastly “Map” the said fields to a JavaFX Bean. The code is available on GitHub.
Features
- Transparent persistence
- Serialization of JavaFX bean values
- Easily integrates with new or existing databases
- Supports MySQL, T-SQL, PostgreSQL and SQLite
- Underlying database implemented using the Star Schema
- Save, Updates and Deletes cascade to child objects and collections
- Eager Loading is applied to embedded objects as well as on collections
Using the library
The library is hosted on Maven Central and you can import it into your project using your dependency manager. Examples for Maven and Gradle are listed below.
Current version
The current version is listed here, (1.170511 as of writing):
Maven
<dependency>
<groupId>io.github.subiyacryolite</groupId>
<artifactId>jds</artifactId>
<version>the.current.version</version>
</dependency>Gradle
compile 'io.github.subiyacryolite:jds:the.current.version'Dependencies
The library depends on Java 8. Both 64 and 32 bit variants should suffice. Both the Development Kit and Runtime can be downloaded from here.
Supported Databases
The API currently supports the following Relational Databases, each of which has their own dependencies, versions and licensing requirements. Please consult the official sites for specifics.
Database & Version Tested Against:
Official Site:
JDBC Driver Tested Against:=====================
PostgreSQL 9.5
Official Site
org.postgresqlMySQL 5.7.14
Official Site
com.mysql.cj.jdbc.DriverMicrosoft SQL Server 2008 R2
Official Site
com.microsoft.sqlserverSQLite 3.16.1
Official Site
org.sqlite.JDBC
1 How it works
1.1 Creating Classes
Classes that use JDS need to extend JdsEntity.
public class Customer extends JdsEntityFollowing that the following steps need to be taken.
1.1.1 Annotating Classes
Every class that extends JdsEntity must have its own unique Entity Id as well as Entity Name. This is done by annotating the class in the following manner
@JdsEntityAnnotation(entityId = 5, entityName = "Customer")
public class Customer extends JdsEntityEntity IDs MUST be unique in your application, any value of type long is valid. Entity Names do not enforce unique constraints but its best to use a unique name regardless. These values can be referenced to mine data.
1.1.2 Defining Fields
JdsFields are big part of the JDS framework. Each field MUST have a unique Field Id. Field Names do not enforce unique constraints but its best to use a unique name regardless. These values can be referenced to mine data. Every field that you define can be one of the following types.
BOOLEAN: Boolean values
FLOAT: Numeric float values
INT: Numeric integer values
DOUBLE: Numeric double values
LONG: Numeric long values
TEXT: String values with no max limit
DATE_TIME: DateTime instances based on the host machines local timezone
ZONED_DATE_TIME: Zoned DateTime instances
TIME: Local time instances
DATE: Local date instances
ARRAY_FLOAT: Lists of type Float
ARRAY_INT: Lists of type Integer
ARRAY_DOUBLE: Lists of type Double
ARRAY_LONG: Lists of type Long
ARRAY_TEXT: Lists of type String
ARRAY_DATE_TIME: Lists of type LocalDateTime
ENUM_TEXT: Lists of type StringI recommend defining your fields as static constants
import io.github.subiyacryolite.jds.JdsField;
import io.github.subiyacryolite.jds.enums.JdsFieldType;public class NewTestFields {//all items are "public static final JdsField"STRING_FIELD = new JdsField(1000, "STRING_FIELD", JdsFieldType.TEXT);TIME_FIELD = new JdsField(1009, "TIME_FIELD", JdsFieldType.TIME);DATE_FIELD = new JdsField(1001, "DATE_FIELD", JdsFieldType.DATE);DATE_TIME_FIELD = new JdsField(1002, "DATE_TIME_FIELD", JdsFieldType.DATE_TIME);ZONED_DATE_TIME_FIELD = new JdsField(1003, "ZONED_DATE_TIME_FIELD", JdsFieldType.ZONED_DATE_TIME);LONG_FIELD = new JdsField(1004, "LONG_FIELD", JdsFieldType.LONG);INT_FIELD = new JdsField(1005, "INT_FIELD", JdsFieldType.INT);DOUBLE_FIELD = new JdsField(1006, "DOUBLE_FIELD", JdsFieldType.DOUBLE);FLOAT_FIELD = new JdsField(1007, "FLOAT_FIELD", JdsFieldType.FLOAT);BOOLEAN_FIELD = new JdsField(1008, "BOOLEAN_FIELD", JdsFieldType.BOOLEAN);}
1.1.3 Defining Enums
JdsEnums are an extension of fields. However, they are designed for cases where one or more constant values are required. Usually these values would be represented by Check Boxes or Radio Buttons in a UI. In this example, we will define Sex as an enumerated value with the following options (Male, Female, Other). First of all, we’d have to define a standard field of type ENUM_TEXT.
public class NewTestFields
{
//all items are "public static final JdsField"
SEX_ENUM = new JdsField(6, "sex_enum", JdsFieldType.ENUM_TEXT);
}Then, we can define our actual enum in the following manner.
public class NewTestEnums
{
//all items are "public static final JdsFieldEnum"
SEX_ENUMS = new JdsFieldEnum(NewTestFields.SEX_ENUM, "M", "Fe", "Other");
}Behind the scenes these enums will be stored as an Integer Array. However, you’d be presented with a LinkedList<String> in-memory containing one or more of the defined values.
1.1.4 Binding Properties
Depending on the type of field, JDS will require that you set you objects properties to one of the following JavaFX bean container types.
Kindly note that none of the JavaFX beans are serializable, however JDS supports serialization via the explicit implementation of Javas Externalizable interface. That said only property values are serialized, not listeners or their states.
BOOLEAN( SimpleBooleanProperty )FLOAT( SimpFloatProperty )INT( SimpleIntegerProperty )DOUBLE( SimpleDoubleProperty )LONG( SimpleLongProperty )TEXT( SimpleStringProperty )DATE_TIME( SimpleObjectProperty<LocalDateTime> )ZONED_DATE_TIME( SimpleObjectProperty<ZonedDateTime> )TIME( SimpleObjectProperty<LocalTime> )DATE( SimpleObjectProperty<LocalDate> )ARRAY_FLOAT( SimpleListProperty<Float> )ARRAY_INT( SimpleListProperty<Integer> )ARRAY_DOUBLE( SimpleListProperty<Double> )ARRAY_LONG( SimpleListProperty<Long> )ARRAY_TEXT( SimpleListProperty<String> )ARRAY_DATE_TIME( SimpleListProperty<LocalDateTime> )ENUM_TEXT( SimpleListProperty<String> )
After your class and its properties have been defined you must map the property to its corresponding field using the map() method. I recommend doing this in your constructor.
The example below shows a class definition with valid properties and bindings. With this your class can be persisted.
import io.github.subiyacryolite.jds.JdsEntity;
import io.github.subiyacryolite.jds.annotations.JdsEntityAnnotation;
import javafx.beans.property.*;import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZonedDateTime;@JdsEntityAnnotation(entityId = 3, entityName = "Type Class")
public class TypeClass extends JdsEntity {
private final SimpleStringProperty stringField;
private final SimpleObjectProperty<LocalTime> timeField;
private final SimpleObjectProperty<LocalDate> dateField;
private final SimpleObjectProperty<LocalDateTime> dateTimeField;
private final SimpleObjectProperty<ZonedDateTime> zonedDateTimeField;
private final SimpleLongProperty longField;
private final SimpleIntegerProperty intField;
private final SimpleDoubleProperty doubleField;
private final SimpleFloatProperty floatField;
private final SimpleBooleanProperty booleanField; public TypeClass() {
stringField = new SimpleStringProperty("");
timeField = new SimpleObjectProperty<LocalTime>(LocalTime.now());
dateField = new SimpleObjectProperty<LocalDate>(LocalDate.now());
dateTimeField = new SimpleObjectProperty<LocalDateTime>(LocalDateTime.now());
zonedDateTimeField = new SimpleObjectProperty<ZonedDateTime>(ZonedDateTime.now());
longField = new SimpleLongProperty(0);
intField = new SimpleIntegerProperty(0);
doubleField = new SimpleDoubleProperty(0);
floatField = new SimpleFloatProperty(0);
booleanField = new SimpleBooleanProperty(false);
//map
map(NewTestFields.STRING_FIELD, stringField);
map(NewTestFields.DATE_FIELD, dateField);
map(NewTestFields.TIME_FIELD, timeField);
map(NewTestFields.DATE_TIME_FIELD, dateTimeField);
map(NewTestFields.ZONED_DATE_TIME_FIELD, zonedDateTimeField);
map(NewTestFields.LONG_FIELD, longField);
map(NewTestFields.INT_FIELD, intField);
map(NewTestFields.DOUBLE_FIELD, doubleField);
map(NewTestFields.FLOAT_FIELD, floatField);
map(NewTestFields.BOOLEAN_FIELD, booleanField);
} public TypeClass(String str,
LocalTime timeField,
LocalDate localDate,
LocalDateTime localDateTime,
ZonedDateTime zonedDateTime,
long l,
int i,
double d,
float f,
boolean b) {
this();
setStringField(str);
setTimeField(timeField);
setDateField(localDate);
setDateTimeField(localDateTime);
setZonedDateTimeField(zonedDateTime);
setLongField(l);
setIntField(i);
setDoubleField(d);
setFloatField(f);
setBooleanField(b);
} public String getStringField() {
return stringField.get();
} public void setStringField(String stringField) {
this.stringField.set(stringField);
} public LocalTime getTimeField() {
return timeField.get();
} public void setTimeField(LocalTime dateField) {
this.timeField.set(dateField);
} public LocalDate getDateField() {
return dateField.get();
} public void setDateField(LocalDate dateField) {
this.dateField.set(dateField);
} public LocalDateTime getDateTimeField() {
return dateTimeField.get();
} public void setDateTimeField(LocalDateTime dateTimeField) {
this.dateTimeField.set(dateTimeField);
} public ZonedDateTime getZonedDateTimeField() {
return zonedDateTimeField.get();
} public void setZonedDateTimeField(ZonedDateTime zonedDateTimeField) {
this.zonedDateTimeField.set(zonedDateTimeField);
} public long getLongField() {
return longField.get();
} public void setLongField(long longField) {
this.longField.set(longField);
} public int getIntField() {
return intField.get();
} public void setIntField(int intField) {
this.intField.set(intField);
} public double getDoubleField() {
return doubleField.get();
} public void setDoubleField(double doubleField) {
this.doubleField.set(doubleField);
} public float getFloatField() {
return floatField.get();
} public void setFloatField(float floatField) {
this.floatField.set(floatField);
} public boolean getBooleanField() {
return booleanField.get();
} public void setBooleanField(boolean booleanField) {
this.booleanField.set(booleanField);
} @Override
public String toString() {
return "TypeClass{" +
"stringField = " + getStringField() +
", timeField = " + getTimeField() +
", dateField = " + getDateField() +
", dateTimeField = " + getDateTimeField() +
", zonedDateTimeField = " + getZonedDateTimeField() +
", longField = " + getLongField() +
", intField = " + getIntField() +
", doubleField = " + getDoubleField() +
", floatField = " + getFloatField() +
", booleanField = " + getBooleanField() +
'}';
}
}
1.1.5 Binding Objects and Object Arrays
Beyond saving numeric, string and date values JDS can also persist embedded objects and object arrays. All that’s required is a valid JdsEntity subclass to be mapped based on the embedded objects annotations.
The class below shows how you can achieve this.
import javafx.beans.property.SimpleListProperty;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import io.github.subiyacryolite.jds.JdsEntity;
import io.github.subiyacryolite.jds.annotations.JdsEntityAnnotation;import java.util.List;@JdsEntityAnnotation(entityId = 2, entityName = "Simple Address Book")
public class SimpleAddressBook extends JdsEntity {
private final SimpleListProperty<SimpleAddress> addresses = new SimpleListProperty<>(FXCollections.observableArrayList()); public SimpleAddressBook() {
map(SimpleAddress.class, addresses);
} public List<SimpleAddress> getAddresses() {
return this.addresses.get();
} public void setAddresses(List<SimpleAddress> value) {
this.addresses.set((ObservableList<SimpleAddress>) value);
} @Override
public String toString() {
return "SimpleAddressBook{" +
"addresses = " + getAddresses() +
'}';
}
}
1.2 CRUD Operations
1.2.1 Initializing the database
In order to use JDS you will need an instance of JdsDb. Your instance of JdsDb will have to extend one of the following classes and override the getConnection() method: JdsDbMySql, JdsDbPostgreSql, JdsDbSqlite or JdsDbTransactionalSql.
Please note that your project must have the correct JDBC driver in its class path. The drivers that were used during development are listed under Supported Databases above.
Postgres example
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JdsDbPostgreSqlmplementation extends JdsDbPostgreSql {
@Override
public Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName("org.postgresql.Driver");
return DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/DB_NAME", "DB_USER", "DB_PASSWORD");
}
}
........
JdsDb jdsDb= new JdsDbPostgreSqlmplementation();
jdsDb.init();MySQL Example
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JdsDbMySqlImplementation extends JdsDbMySql {
@Override
public Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/DB_NAME?autoReconnect=true&useSSL=false", "DB_USER", "DB_PASSWORD");
}
}
...
JdsDb jdsDb= new JdsDbMySqlImplementation();
jdsDb.init();Microsoft SQL Server Example
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JdsDbTransactionalSqllmplementation extends JdsDbTransactionalSql {
@Override
public Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
return DriverManager.getConnection("jdbc:sqlserver://127.0.0.1\\DB_INSTANCE;databaseName=DB_NAME", "DB_USER", "DB_PASSWORD");
}
}
....
JdsDb jdsDb= new JdsDbTransactionalSqllmplementation();
jdsDb.init();Sqlite Example
import org.sqlite.SQLiteConfig;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JdsDbSqliteImplementation extends JdsDbSqlite {
@Override
public Connection getConnection() throws ClassNotFoundException, SQLException {
String url = "jdbc:sqlite:" + getDatabaseFile();
SQLiteConfig sqLiteConfig = new SQLiteConfig();
sqLiteConfig.enforceForeignKeys(true); //You must enable foreign keys in SQLite
Class.forName("org.sqlite.JDBC");
return DriverManager.getConnection(url, sqLiteConfig.toProperties());
}
public String getDatabaseFile() {
File path = new File(System.getProperty("user.home") + File.separator + ".jdstest" + File.separator + "jds.db");
if (!path.exists()) {
File directory = path.getParentFile();
if (!directory.exists()) {
directory.mkdirs();
}
}
String absolutePath = path.getAbsolutePath();
return absolutePath;
}
}
...
JdsDb jdsDb = new JdsDbSqliteImplementation();
jdsDb.init();With this you should have a valid connection to your database and JDS will setup its tables and procedures automatically. Furthermore, you can use the getConnection() method from your JdsDb instance in order to return a standard java.sql.Connection in your application.
1.2.2 Initialising JDS
Once you have initialized your database you can go ahead and initialize all your JDS classes. You can achieve this by mapping ALL your JDS classes in the following manner.
public void initialiseJdsClasses()
{
JdsEntityClasses.map(TypeClass.class);
JdsEntityClasses.map(SimpleAddress.class);
JdsEntityClasses.map(SimpleAddressBook.class);
}You only have to do this once at start-up but it is vital that you do so. Without this you will face problems when loading or saving records
1.2.3 Creating objects
Once you have defined your class you can initialize them. A dynamic Entity Guid is created for every jdsEntity by default, this value is used to uniquely identify an object and it data in the database. You can set your own values if you wish.
private List<TypeClass> getCollection() {
List<TypeClass> collection = new ArrayList<>();
TypeClass instance1 = new TypeClass();
instance1.setEntityGuid("instance1");
instance1.setStringField("One");
TypeClass instance2 = new TypeClass();
instance2.setStringField("tWO");
instance2.setTimeField(LocalTime.of(15, 24));
instance2.setDateField(LocalDate.of(2012, 8, 26));
instance2.setDateTimeField(LocalDateTime.of(1991, 07, 01, 8, 33, 12));
instance2.setZonedDateTimeField(ZonedDateTime.of(LocalDateTime.now().minusMonths(3), ZoneId.systemDefault()));
instance2.setIntField(99);
instance2.setLongField(888);
instance2.setDoubleField(777.666);
instance2.setFloatField(5555.4444f);
instance2.setBooleanField(false);
instance2.setEntityGuid("instance2");
TypeClass instance3 = new TypeClass();
instance3.setStringField("Three");
instance3.setTimeField(LocalTime.of(03, 14));
instance3.setDateField(LocalDate.of(2034, 6, 14));
instance3.setDateTimeField(LocalDateTime.of(1987, 07, 24, 13, 22, 45));
instance3.setZonedDateTimeField(ZonedDateTime.of(LocalDateTime.now().plusDays(3), ZoneId.systemDefault()));
instance3.setIntField(22);
instance3.setLongField(333);
instance3.setDoubleField(444.555);
instance3.setFloatField(5555.6666f);
instance3.setBooleanField(false);
instance3.setEntityGuid("instance3");
TypeClass instance4 = new TypeClass();
instance4.setStringField("Four");
instance4.setTimeField(LocalTime.of(12, 44));
instance4.setDateField(LocalDate.of(3034, 12, 1));
instance4.setDateTimeField(LocalDateTime.of(1964, 10, 24, 2, 12, 14));
instance4.setZonedDateTimeField(ZonedDateTime.of(LocalDateTime.now().minusDays(3), ZoneId.systemDefault()));
instance4.setIntField(10);
instance4.setLongField(100);
instance4.setDoubleField(100.22);
instance4.setFloatField(1000.0f);
instance4.setBooleanField(false);
instance4.setEntityGuid("instance4");
collection.add(instance1);
collection.add(instance2);
collection.add(instance3);
collection.add(instance4);
return collection;
}1.2.4 Save
The API has a single save() method within the class JdsSave. The method can takes either one of the following arguments (JdsEntity… entities) or (Collection<JdsEntity> entities). The method also expects the user to supply a batch size.
List<TypeClass> collection = getCollection();
JdsSave.save(jdsDb, 1, collection);
System.out.printf("Saved %s\n", collection);1.2.5 Load
The system currently has three variants of the load() method. The first variant loads ALL the instances of a JdsEntity class. The second variant loads ALL the instances of a JdsEntity class with matching Entity Guids which are supplied by the user. The second variant adds an optional parameter “Comparator<? extends JdsEntity>” which allows you to load a sorted collection
List<TypeClass> allInstances;
List<TypeClass> specificInstance;//load all entities of type SimpleAddressBook
allInstances = JdsLoad.load(jdsDb, TypeClass.class);//load all entities of type SimpleAddressBook with Entity Guids in range
specificInstance = JdsLoad.load(jdsDb, TypeClass.class, "instance3");//load all entities of type SimpleAddressBook with Entity Guids in range SORTED by creation date
Comparator<TypeClass> comparator = Comparator.comparing(TypeClass::getDateField);
specificAddressBook = JdsLoad.load(jdsDb, TypeClass.class, comparator, "testGuid0001");
1.2.6 Load with Filter
A filter mechanism is present. It is fairly basic and is still being refined. An example to sample usage is shown below.
JdsFilter query = new JdsFilter().equals(TestFields.AREA_NAME, "Riverdale").like(TestFields.COUNTRY_NAME, "Zam").or().equals(TestFields.PROVINCE_NAME, "Copperbelt");
List<SimpleAddress> output = query.find(jdsDb, SimpleAddress.class);1.2.7 Delete
You can delete by providing one or more JdsEntities or via a collection of strings representing JdsEntity UUIDS.
public void deleteUsingStrings() {
JdsDelete.delete(jdsDb, "instance2");
}public void deleteUsingObjectOrCollection() {
List<TypeClass> collection = getCollection();
JdsDelete.delete(jdsDb, collection);
}
1.3 Backend Design
The underlying database is based off of the star schema.
Each JdsStore[X] relation stores live data and is always up-to-date.
The JdsRef[X] and JdsBind[X] tables are used by the systems to link different relations and metadata.
JdsSoreOldFieldValues stores every single update/insert in the system and is the single point of reference for historical data. It is written to by calling logEdits(true) on an instance of JdsDb.
Contributing to Jenesis Data Store
If you would like to contribute code you can do so through Github by forking the repository and sending a pull request targeting the current development branch.
When submitting code, please make every effort to follow existing conventions and style in order to keep the code as readable as possible.
Bugs and Feedback
For bugs, questions and discussions please use the Github Issues.
