中文版请见这里
In Getting Start, we have learned how to create the Database
instance and define your database entity. Now,
we start learn how to write SQL statements with SQLlin.
The class Database
has overloaded function operator that type is <T> Database.(Database.() -> T) -> T
. When you invoke
the operator function, it will produce a DatabaseScope. Yeah, that is an operator function's lambda parameter. Any SQL statement
must be written in DatabaseScope. And, the inner SQL statements will only be executed when the DatabaseScope ended.
You already know, the INSERT, DELETE, UPDATE and SELECT SQL statements are used for table operation. So, before you write
your SQL statements, you also need to get a Table
instance, like this:
private val database = Database(name = "Person.db", path = getGlobalPath(), version = 1)
fun sample() {
database {
PersonTable { table ->
// Write your SQL statements...
}
}
}
The PersonTable
is generated by sqllin-processor, because Person
is annotated the @DBRow
annotation. Any class that be annotated the @DBRow
will be generated a Table
object, its name is
class name + 'Table'
.
Now, let's do the real INSERT:
fun sample() {
database {
PersonTable { table ->
table INSERT Person(age = 4, name = "Tom")
table INSERT listOf(
Person(age = 10, name = "Nick"),
Person(age = 3, name = "Jerry"),
Person(age = 8, name = "Jack"),
)
}
}
}
The INSERT statement could insert objects directly. You can insert one or multiple objects once.
The DELETE statement will be slightly more complex than INSERT. SQLlin doesn't delete objects like Jetpack Room, we use the WHERE clause:
fun sample() {
database {
PersonTable { table ->
table DELETE WHERE(age GTE 10 OR (name NEQ "Jerry"))
}
}
}
Let's understand the WHERE clause. WHERE
function receives a ClauseCondiction
as a parameter. The age
and name
in the example is used for representing columns'
name, they are extension property with Table
, their type are ClauseElement
, and be generated by sqllin-processor(KSP).
The ClauseElement
has a series of operators that used for representing the SQL operators like: =
, >
, <
, LIKE
, IN
, IS
etc.. When a ClauseElement
invoke a
operator, we will get a ClauseCondiction
. Multiple ClauseCondiction
s would use the AND
or OR
operator link and produce a new ClauseCondiction
.
The chart of between SQL operators and SQLlin operators like this:
SQL | SQLlin |
---|---|
= | EQ |
!= | NEQ |
< | LT |
<= | LTE |
> | GT |
>= | GTE |
BETWEEN | BETWEEN |
IN | IN |
LIKE | LIKE |
GLOB | GLOB |
OR | OR |
AND | AND |
Sometimes, we want to delete all data in the table. At this time, the DELETE statement doesn't have WHERE clause:
DELETE FROM person
In SQLlin we can write this to achieve the same effect:
fun sample() {
database {
PersonTable { table ->
table DELETE X
}
}
}
The X
is a Kotlin object
(singleton).
The UPDATE statement is similar to DELETE, it also use a WHERE clause to limit update conditions. But, the difference is UPDATE statement owns a particular SET clause:
fun sample() {
database {
PersonTable { table ->
table UPDATE SET { age = 5 } WHERE (name NEQ "Tom")
}
}
}
The SET clause is different from other clauses, it receives a lambda as parameter, you can set the new value to column in the
lambda. The age
in the set lambda is a writable property that also be generated by KSP, and it only available in SET
clause, it different with age
readonly property in WHERE clause.
You also could write the UPDATE statement without WHERE clause that used for update all rows, but you should use it with caution.
Use transaction is simple in SQLlin, you just need to use the transaction {...}
wrap your SQL statements:
fun sample() {
database {
transaction {
PersonTable { table ->
table INSERT Person(age = 4, name = "Tom")
table INSERT listOf(
Person(age = 10, name = "Nick"),
Person(age = 3, name = "Jerry"),
Person(age = 8, name = "Jack"),
)
table UPDATE SET { age = 5 } WHERE (name NEQ "Tom")
}
}
}
}
The transaction {...}
is a member function in Database
, it inside or outside of TABLE(databaseName) {...}
is doesn't matter.
You have learned how to use INSERT, DELETE and UPDATE statements. Next step you will learn SELECT statement. The SELECT statement is more complex than other statements, be ready :).