Skip to content

Automatic SQL Perfecting

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

Since SqliteMagic supports complex columns, they have also received a special attention in building queries.

If table has complex columns, its evident, that in some point in time one would want to query the whole object tree structure. In order to achieve that, data must be queried from multiple tables. Composing multiple queries and parsing them seperately can have very big performance impact, which is why SqliteMagic queries and parses the whole object tree structure from a single query result.

In order to achieve everything mentioned, tables must be joined with JOIN SQL clauses. For even a bit more complex data structure, writing joins for every table is very tedious and error prone. SqliteMagic solves this problem by supporting automatic SQL perfecting during query compilation phase.

By knowing the whole object tree structure, SqliteMagic knows which joins are needed for building the whole queried object structure. It respects all user defined joins and columns and adds only the missing parts if needed.

For database schema

@Table(persistAll = true)
public final class Country {
  @NonNull
  String name;
  @NonNull
  String code;
}

@Table(persistAll = true)
public final class Author {
  @NonNull
  String firstName;
  @NonNull
  String lastName;
  @NonNull
  Country birthCountry;
}

@Table(persistAll = true)
public final class Book {
  @NonNull
  String title;
  @NonNull
  Author author;
  @Nullable
  Author coAuthor;
}

Example queries

SQL SqliteMagic 
SELECT *
FROM BOOK
LEFT JOIN AUTHOR
AS FGyqNc
ON BOOK.AUTHOR=FGyqNc._ID
LEFT JOIN COUNTRY
AS otabMl
ON FGyqNc.BIRTH_COUNTRY=otabMl._ID
LEFT JOIN AUTHOR
AS peIzzK
ON BOOK.CO_AUTHOR=peIzzK._ID
LEFT JOIN COUNTRY
AS LpXICB
ON peIzzK.BIRTH_COUNTRY=LpXICB._ID;
 
import static com.siimkinks.sqlitemagic.BookTable.BOOK;
CompiledSelect<Book, SelectN> compiledSelect = Select
.from(BOOK)
.queryDeep()
.compile();
  </code></pre>
</td>
SQL SqliteMagic 
SELECT BOOK.TITLE,
BOOK.AUTHOR,
AUTHOR.,
COUNTRY.
FROM BOOK
LEFT JOIN AUTHOR
ON BOOK.AUTHOR=AUTHOR._ID
LEFT JOIN COUNTRY
ON AUTHOR.BIRTH_COUNTRY=COUNTRY._ID;
 
import static com.siimkinks.sqlitemagic.AuthorTable.AUTHOR;
import static com.siimkinks.sqlitemagic.BookTable.BOOK;
import static com.siimkinks.sqlitemagic.CountryTable.COUNTRY;
CompiledSelect<Book, SelectN> compiledSelect = Select
.columns(BOOK.TITLE,
BOOK.AUTHOR,
AUTHOR.all(),
COUNTRY.all())
.from(BOOK)
.queryDeep()
.compile();
  </code></pre>
</td>
SQL SqliteMagic 
SELECT BOOK.TITLE,
BOOK.AUTHOR,
a.,
c.
FROM BOOK
LEFT JOIN AUTHOR
AS 'a'
ON BOOK.AUTHOR=a._id
LEFT JOIN COUNTRY
AS 'c'
ON a.BIRTH_COUNTRY=c._id;
 
import static com.siimkinks.sqlitemagic.AuthorTable.AUTHOR;
import static com.siimkinks.sqlitemagic.BookTable.BOOK;
import static com.siimkinks.sqlitemagic.CountryTable.COUNTRY;
AuthorTable a = AUTHOR.as("a");
CountryTable c = COUNTRY.as("c");
CompiledSelect<Book, SelectN> compiledSelect = Select
.columns(BOOK.TITLE,
BOOK.AUTHOR,
a.all(),
c.all())
.from(BOOK)
.leftJoin(a.on(BOOK.AUTHOR.is(a._ID)))
.leftJoin(c.on(a.BIRTH_COUNTRY.is(c._ID)))
.queryDeep()
.compile();
  </code></pre>
</td>

See Next

Clone this wiki locally