Skip to content

Column Expressions

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

SqliteMagic allows you to freely create arbitrary column expressions using a fluent expression construction API. Many expressions can be formed as functions from Select static methods, other expressions can be formed based on a pre-existing column expression. For example:

// A function created from the
// com.siimkinks.sqlitemagic.Select
// using "prefix" notation
Column<String, String, CharSequence, Author> upper = upper(AUTHOR.FIRST_NAME);

// A function created from
// a pre-existing column
// using "postfix" notation
Column<String, String, CharSequence, ?> trim = AUTHOR.FIRST_NAME.trim();

// A more complex example of functions using
// both "prefix" and "postfix" notations
Column<String, String, CharSequence, ?> concat = concat(
        AUTHOR.FIRST_NAME,
        val(" "),
        AUTHOR.LAST_NAMENAME.trim());

Constant Values

To define a constant value just call Select.val(). For character sequence values this will return a column with CharSequence as its defining type. For numeric values this will return a numeric column with Number as its defining type. For any other type a column will be returned with the same defining type as the provided value.

When a transformer is defined for the provided value, it is used to translate the Java value into SQL compatible type.

// String type results in Column type
Column<String, String, CharSequence, ?> a = val("a");

// Integer type results in NumericColumn type
NumericColumn<Integer, Integer, Number, ?> val = val(2);

// Any other type results in Column type
Column<Boolean, Boolean, Boolean, ?> val1 = val(true);

// Types with transformers are also supported
Column<Date, Date, Date, ?> val2 = val(new Date());

Since SQLite views cannot have variables, all view query input parameters must be defined with val() methods.

Column Aliases

Just like tables, columns can be renamed using aliases.

SQL SqliteMagic 
   SELECT AUTHOR.FIRST_NAME ||
             ' ' ||
             AUTHOR.LAST_NAME AS author_name,
          COUNT(*) AS nr_of_books
     FROM BOOK
LEFT JOIN AUHTOR ON AUTHOR.ID = BOOK.AUTHOR
GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME;


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

CompiledSelect<Book, SelectN> compile = Select
    .columns(
        concat(AUTHOR.FIRST_NAME, 
            val(" "),
            AUTHOR.LAST_NAME).as("author_name"),
        count().as("nr_of_books"))
    .from(BOOK)
    .leftJoin(AUTHOR.on(AUTHOR.ID.is(BOOK.AUTHOR)))
    .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
    .compile();
_Query created above can be used to define a [SQL view](https://github.com/SiimKinks/sqlitemagic/wiki/Views), for example._

Numeric Arithmetic Expressions

SQLite can do math. For numeric columns SqliteMagic adds support for arithmetic expressions. You can use the following operators:

+ - * / %

Example:

SQL SqliteMagic 
((1 + 2) * (5 - 3) / 2) % 10
 
val(1).add(2).mul(val(5).sub(3)).div(2.0).mod(10.0)

In addition to the arithmetic expressions, SqliteMagic also supports the Select.abs() prefix numeric function, which gets the absolute value of a value.

Operator Precedence

SqliteMagic does not know any operator precedence. All operators are evaluated from left to right, as with any object-oriented API. The two following expressions are the same:

val(1).add(2) .mul(val(5).sub(3)) .div(2.0) .mod(10.0);
(((val(1).add(2)).mul(val(5).sub(3))).div(2.0)).mod(10.0);

String Concatenation

SQLite defines the concatenation operator to be an infix operator and it looks like this: ||. SqliteMagic supports string concatenation both as a prefix and postfix operator:

SQL SqliteMagic 
SELECT AUTHOR.FIRST_NAME ||
       ' ' ||
       AUTHOR.LAST_NAME
  FROM AUTHOR;






// Use the postfix operator to 
// create a concatenation spaghetti
List<String> authorNames = Select
    .column(AUTHOR.NAME
           .concat(val(" "))
           .concat(AUTHOR.NAME))
    .from(AUTHOR)
    .execute();

// Or use the prefix operator
// with multiple parameters
// which produces identical result
List<String> otherAuthorNames = Select
    .column(concat(
            AUTHOR.NAME,
            val(" "),
            AUTHOR.NAME))
    .from(AUTHOR)
    .execute();

String Functions

String formatting can be done efficiently in the database before returning results to your Java application.

SqliteMagic supports the following string functions:

  • concat Concatenates strings.
  • length Get the length of a string.
  • lower Get a string in lower case letters.
  • replace Replace a string within another string.
  • substring Get a substring of a string.
  • trim Trim a string on both sides.
  • upper Get a string in upper case letters.

Aggregate Functions

Aggregate functions work just like functions. SqliteMagic supports the following aggregate functions:

Standard SQL aggregate functions

  • avg(NumericColumn) Get the average value of all non-NULL columns.
  • count() Get the total number of rows.
  • count(Column)Get the number of times that column is not NULL.
  • max(Column) Get the maximum value of all values.
  • min(Column) Get the minimum non-NULL value of all values.
  • sum(NumericColumn) Get the sum of all non-NULL values.

DISTINCT keyword in aggregate functions

  • avgDistinct(NumericColumn) Gets the average value of distinct values of provided column.
  • countDistinct(Column) Get the number of distinct values of column.
  • maxDistinct(Column) Get the maximum value of distinct values of column.
  • minDistinct(Column) Get the minimum value of distinct values of column.
  • sumDistinct(NumericColumn) Get the sum of distinct values of column.

String aggregate functions

  • groupConcat(Column) Get a string which is the concatenation of all non-NULL values of provided column. A comma (",") is used as the separator.
  • groupConcat(Column, String) Get a string which is the concatenation of all non-NULL values of provided column. The second parameter is used as the separator between instances of columns.
  • groupConcatDistinct(Column) Get a string which is the concatenation of distinct values of provided column.
  • groupConcatDistinct(Column, String) Get a string which is the concatenation of distinct values of provided column. The second parameter is used as the separator between instances of columns.

For more detailed information about aggregate functions see the SqliteMagic javadoc or SQLite website.

Example of getting the average page count of all books:

SQL SqliteMagic 
SELECT avg(BOOK.PAGES)
  FROM BOOK;


Double avg = Select
    .column(avg(BOOK.PAGES))
    .from(BOOK)
    .takeFirst()
    .execute();

See Next

Clone this wiki locally