Skip to content

Querying Specific Columns

Jump to bottom
Siim Kinks edited this page Mar 21, 2017 · 2 revisions

Without defining columns or column clause in query builder, all columns will be selected and parsed. SqliteMagic also supports selecting only specific columns from tables and single column queries.

Multiple Columns

By defining columns clause only provided columns will be selected and parsed. It is important to note here that if table contract says that column is non-null (with @NonNull annotation) and this column is not selected SQLException will be thrown.

Database schema

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

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

   SELECT BOOK.TITLE,
          BOOK.AUTHOR,
          AUTHOR.*
     FROM BOOK
LEFT JOIN AUTHOR
          ON BOOK.AUTHOR=AUTHOR._ID;

import static com.siimkinks.sqlitemagic.BookTable.BOOK;
import static com.siimkinks.sqlitemagic.AuthorTable.AUTHOR;

// only book title and full Author object are filled
List<Book>books = Select
    .columns(BOOK.TITLE,
             BOOK.AUTHOR,
             AUTHOR.all())
    .from(BOOK)
    .queryDeep()
    .execute();
 
// throws SQLException since Author last name is not nullable,
// but is not selected
List<Book>books = Select
    .columns(BOOK.TITLE, BOOK.AUTHOR, AUTHOR.FIRST_NAME)
    .from(BOOK)
    .queryDeep()
    .execute();

Single Column

By defining column clause only the defined column will be selected and parsed. The resulting object is of same type as the column defined in the table object.

SQL SqliteMagic 
SELECT BOOK.TITLE
  FROM BOOK
 WHERE BOOK.TITLE LIKE '%Foo%' 
       AND BOOK.PAGES > 200;



SELECT BOOK.PAGES
  FROM BOOK;


import static com.siimkinks.sqlitemagic.BookTable.BOOK;

List<String> bookTitles = Select
    .column(BOOK.TITLE)
    .from(BOOK)
    .where(BOOK.TITLE.like("%Foo%")
            .and(BOOK.PAGES.greaterThan(200)))
    .execute();

Integer firstBookPages = Select
    .column(BOOK.PAGES)
    .from(BOOK)
    .takeFirst()
    .execute();

See Next

Clone this wiki locally