Skip to content

User Defined Objects as Columns

Jump to bottom
Siim Kinks edited this page Nov 21, 2016 · 6 revisions

SqliteMagic supports user defined objects as column (or "complex column") without any hustle, complications or special configuration. Just define complex column as normal column and thats it.

Prerequisite for defining complex column is that both parent and child object are included in the database schema with both having @Table annotation.

Example:

POJO Result in SQL 
@Table(persistAll = true)
public class Author {
String firstName;
String lastName;
...
}
@Table(persistAll = true)
public class Book {
String title;
Author author;
...
}
 
CREATE TABLE IF NOT EXISTS author (
_id        INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name  TEXT
);
CREATE TABLE IF NOT EXISTS book (
_id        INTEGER PRIMARY KEY AUTOINCREMENT,
title      TEXT,
author     INTEGER
);
  </code></pre>
</td>

Internally referenced table id is saved in parent table (as seen in example: author INTEGER). Note that there is no foreign key constraint - there is no real need for it and in that way we can keep things simple (e.g. in migrations).

Complex Objects Handling During Persisting

By default when parent object is persisted (in example above its Book object), its complex column are also persisted. In that case the whole operation is wrapped in transaction internally (source code can be viewed in the generated code).

Java Result in Database 
Author author = new Author("Foo", "Bar");
Book book = new Book("The Bar Life", author);
long id = book
.persist()
.execute();
 Author:
_id first_name last_name
...
73 Foo Bar

Book:
_id title author
...
42 The Bar Life 73

If above mentioned functionality is not requested annotate column with @Column(handleRecursively = false) annotation. In that case only referenced table id is persisted.

@Table(persistAll = true)
public class Book {
  
  String title;
  
  @Column(handleRecursively = false)
  Author author;
  
  ...
}

Author author = new Author("Foo", "Bar");
Book book = new Book("The Bar Life", author);

// no changes to Author table here
long id = book
    .persist()
    .execute();

ON DELETE CASCADE

If ON DELETE CASCADE constraint is needed annotate complex column with annotation @Column(onDeleteCascade = true). The default value is false.

POJO Result in SQL 
@Table(persistAll = true)
public class Book {
String title;
@Column(onDeleteCascade = true)
Author author;
...
}
 
CREATE TABLE IF NOT EXISTS book (
_id        INTEGER PRIMARY KEY AUTOINCREMENT,
title      TEXT,
author     INTEGER REFERENCES author(_id) ON DELETE CASCADE
);
  </code></pre>
</td>
Clone this wiki locally