Onyx Database: Kotlin Query Builder DSL API

You can download the code for this example here:

OnyxDevTools/onyx-database-examples/querying/kotlin.

Onyx Database 2.0.0 has added additional support for Kotlin. This document contains examples and reference regarding the Kotlin DSL Query Builder Syntax

Table of Contents

1. Getting Started
   1. Maven Configuration
   2. Gradle Configuration
2. Querying Entities
   1. Kotlin Query Builder
   2. Where Criteria
   3. Using Select
   4. Order By
   5. Group By
   6. Query Functions
   7. Getting Results
      1. List
      2. Lazy List
      3. Limiting Results
      4. For Each
      5. Count Results
      6. Filter Results
   8. Updating
   9. Deleting
   10. Query Change Listener

1. Example
2. Getting Started

   Maven Configuration

   Gradle Configuration

   Add Onyx Database dependency

   Add Kotlin dependency and plugin
   Gradle Configuration will be slightly different for Android implementations

   Setting up your entities is covered in other tutorials. The same annotations apply to Kotlin managed entities as it does Java.
3. Querying Entities

   Kotlin Query Builder

   Kotlin Query Builder was added in version 2.0.0. Although you can use it with Java it was designed to use a DSL like syntax within Kotlin. The QueryBuilder class provides a less verbose syntax for querying Onyx Database. It can be used to query an embedded, remote, or in memory database using Kotlin.

   For Example:

   The Query can be given commands to filter and define query definition with the following methods:

   The following methods can be used to retrieve, aggregate, listen to, delete, or update data.
4. Where

   Define Criteria

   Kotlin Query Builder DSL syntax varies from the traditional Onyx Database syntax. The following criteria infix methods can be used to define criteria. The criteria operators are mapped using the following:

   Since Kotlin requires parenthesis around chained infix method invocations, the order of operations of criteria must be grouped using parenthesis. The and and or infix methods should operate the same as any standard SQL or DSL querying language.

   The property names for the criteria are string values.

   The IN and notIn methods require list values and cannot be used with varargs since Kotlin does not support varargs for infix methods.

   The equivalent Query Criteria in Onyx Database using Java would be
5. Using Select

   Select fields rather than a typed query

   The select method is not required for building a query within the Kotlin DSL but, it can be used to retrieve only desired fields.
6. Using Order By

   Order By method is used to specify query sorting. The following methods are available to define sort order.

   Example:
7. Using Group By

   Group By method is used to group results to allow aggregation. The following methods can be used with

   This is an example of using the Group By to group players by rushing yards:
8. Query Functions

   The Kotlin DSL supports the following functions

   The following would be an example of getting the leading rusher for NFL stats using the max function.

   For more details on Query Functions and Group By read this tutorial.

   Group By and Query Functions

9. Getting Results

   The Kotlin Query Builder DSL has several methods to retrieve data. Using Kotlin, you can use the inherent commands including list, lazy, and count. Alternatively, you can use the lambda friendly methods forEach, map, first, firstOrNull.

   List Results

   Lazy Results

   Using the .lazy method will only load the references of the results. It is not until you reference the item within the result set that the entity will be loaded.

   First / First or Null

   Limiting results can be done using the .limit and the .first method. The first method can be used either to retrieve the first result or, it can be used to define the first result to skip to if you pass an int value into the method. Also note, the first() method will throw an exception if results are not present. To avoid an exception use firstOrNull().

   For Each

   For each provides a lambda friendly way of iterating through results

   Map Results

   The map method is used to aggregate results. It works the same as the map method within Kotlin for a collection

   Count Results

   Filter Results

   The filter method is not optimized to define criteria and does not reduce query cardinality. It is only an additional tool for aggregating query results.
10. Updating Results

    In order to update values, invoke the .update method.

    Setting property values are done by specifying the property as a string and using the to infix method to instruct what value to set it to.

    Example:

    The set can either be invoked multiple times for multiple properties, or by passing in vararg set instructions.
11. Deleting Results

    Delete query is executed by invoking the delete method.

    The limit and filtering methods that apply to the list also apply to delete and update methods.
12. Listening for Changes

    Query Listeners

    The following methods apply to Query listeners.

    Query listeners can be either attached to a query upon execution or explicitly subscribed using the listen method. In order to ensure listeners are not leaked, you should keep a handle on the original Kotlin QueryBuilder object.

    Below is an example of using an update listener. When finished, the stopListening method is called to un-subscribe the listener.
13. Query Change Listeners