KQLite SELECT

KQLite provides rich DSL to select from table. There is a quick convenience API to select rows including where clause.

Every KQLite SelectStatement returns KQLiteCursor which is a single use Iterator. KQLiteCursor is AutoCloseable and is closed automatically once iteration is finished.

SELECT query is executed over table only when data is read from KQLiteCursor.

Quickly selecting data from KQLiteTable

// SELECT * FROM Artists;
val cursor: KQLiteCursor = Artists.quickSelect()
cursor.forEach {  it: KQLiteCursor 
    val id: Long = it[Artists.artistId]
    val name: String? = it[Artists.artistName]
    println("Artist: $id, $name")
}
cursor.close()

// SELECT ArtistId, Name FROM Artists;
Artists
    .quickSelect(Artists.artistId, Artists.artistName)
    .forEach {  it: KQLiteCursor 
        val id: Long = it[Artists.artistId]
        val name: String? = it[Artists.artistName]
        println("Artist: $id, $name")
    }

// SELECT Name FROM Artists WHERE Name LIKE ? COLLATE NOCASE;
Artists.quickSelect(
    Artists.artistName,
    where = {  it: Artists 
        it.artistName.LIKE("%cool%").COLLATE(Collate.NOCASE)
    }
)
.forEach {  it: KQLiteCursor 
    println("Artist: ${it[Artists.artistName]}")
}

ORDER BY and LIMIT

// SELECT Name, Milliseconds, UnitPrice FROM Tracks
// WHERE UnitPrice > ? AND Milliseconds < ?
// ORDER BY TrackId LIMIT ?;
Tracks
    .select(Tracks.trackName, Tracks.milliseconds, Tracks.unitPrice)
    .where {  it: Tracks 
        it.unitPrice.GT(1.0) AND it.milliseconds.LT(120000)
    }.orderBy(Tracks.trackId)
    .limit(10)
    .execute()
    .forEach {  it: KQLiteCursor 
        val name = it[Tracks.trackName]
        val unitPrice = it[Tracks.unitPrice]
        println("Track $name is costlier at $unitPrice")
    }

GROUP BY and HAVING

// SELECT AlbumId, COUNT(TrackId) FROM Tracks
// GROUP BY AlbumId
// HAVING COUNT(TrackId) BETWEEN ? AND ?
// ORDER BY AlbumId;
Tracks
    .select(Albums.albumId, COUNT(Tracks.trackId))
    .groupBy(Albums.albumId)
    .having {  it: Tracks 
        COUNT(Tracks.trackId).BETWEEN(18L..20L)
    }
    .orderBy(Albums.albumId)
    .execute()
    .forEach {  it: KQLiteCursor 
        val albumId = it[Albums.albumId]
        val trackCount = it[COUNT(Tracks.trackId)]
        println("$albumId, $trackCount")
    }

Mapping KQLiteCursor

KQLiteCursor implements and provides all the benifits of Iterator. See also KQLiteAdapter

Artists
    .select()
    .where {  it: Artists 
        it.artistId EQ 5
    }
    .execute()
    .asSequence()
    .map {  it: KQLiteCursor 
        it[Artists.artistId] to it[Artists.artistName]
    }
    .forEach {  it: Pair<Long, String?> 
        println("Artist: ${it.first}, ${it.second}")
    }

JOIN statement

KQLite supports following types of JOIN

// SELECT artists.ArtistId, albums.AlbumId
// FROM artists LEFT JOIN albums
// ON albums.ArtistId = artists.ArtistId
// WHERE AlbumId IS NULL;
val cursor = Artists
    .select(Artists.artistId, Albums.albumId)
    .leftJoin(Albums)
    .on(Albums.artistId, Artists.artistId)
    .where {  it: Artists 
        Albums.albumId IS null
    }
    .execute()

val artistList = cursor.asSequence()
    .map {  it: KQLiteCursor 
        it[Artists.artistId]
    }
    .toList()

println("Artists with no albums: ${artistList.joinToString()}")

SELECT DISTINCT

// SELECT DISTINCT city FROM customers ORDER BY city;
Customers
    .select(Customers.city, distinct = true)
    .orderBy(Customers.city)
    .execute()
    .forEach {
        println(it[Customers.city])
    }

⬅ KQLite INSERT KQLite UPDATE ⮕