Skip to content

Automatic SQL Perfecting

Jump to bottom
Siim Kinks edited this page Mar 21, 2017 · 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();
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();
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();

See Next

Clone this wiki locally