中文版请见这里
In Getting Start, we have learned how to create the Database instance and define your database entities. Now,
we start to learn how to write SQL statements with SQLlin.
SQLlin provides type-safe DSL operations for managing table structures: CREATE, DROP, and ALTER (referred to as ALERT in the API).
You can create tables directly from your data class definitions using the CREATE operation:
import com.ctrip.sqllin.dsl.annotation.DBRow
import com.ctrip.sqllin.dsl.annotation.PrimaryKey
import kotlinx.serialization.Serializable
@DBRow
@Serializable
data class Person(
@PrimaryKey(autoIncrement = true)
val id: Long = 0,
val name: String,
val age: Int,
)
fun sample() {
database {
// Create table using infix notation
CREATE(PersonTable)
// Or using extension function
PersonTable.CREATE()
}
}The CREATE operation automatically generates the appropriate SQL CREATE TABLE statement based on your data class definition, including:
- Correct column types (String → TEXT, Int → INT, Long → INTEGER/BIGINT, etc.)
- NOT NULL constraints for non-nullable properties
- PRIMARY KEY constraints (single or composite)
- AUTOINCREMENT for auto-incrementing primary keys
The DROP operation permanently removes a table and all its data from the database:
fun sample() {
database {
// Drop table using infix notation
DROP(PersonTable)
// Or using extension function
PersonTable.DROP()
}
}SQLlin provides several ALTER (ALERT) operations for modifying existing table structures:
Add a new column to an existing table:
@DBRow
@Serializable
data class Person(
val name: String,
val age: Int,
val email: String? = null, // New column
)
fun sample() {
database {
PersonTable ALERT_ADD_COLUMN PersonTable.email
}
}Rename an existing table to a new name:
fun sample() {
database {
// Rename using Table object
PersonTable ALERT_RENAME_TABLE_TO NewPersonTable
// Or rename using old table name as String
"old_person" ALERT_RENAME_TABLE_TO NewPersonTable
}
}Rename a column within a table:
fun sample() {
database {
// Using ClauseElement references (type-safe)
PersonTable.RENAME_COLUMN(PersonTable.age, PersonTable.yearsOld)
// Or using String for old column name
PersonTable.RENAME_COLUMN("age", PersonTable.yearsOld)
}
}Remove a column from an existing table:
fun sample() {
database {
PersonTable DROP_COLUMN PersonTable.email
}
}These operations are particularly useful in database creation and upgrade callbacks when using DSLDBConfiguration:
import com.ctrip.sqllin.dsl.DSLDBConfiguration
val database = Database(
DSLDBConfiguration(
name = "Person.db",
path = getGlobalDatabasePath(),
version = 2,
create = {
CREATE(PersonTable)
CREATE(AddressTable)
},
upgrade = { oldVersion, newVersion ->
when (oldVersion) {
1 -> {
// Upgrade from version 1 to 2
PersonTable ALERT_ADD_COLUMN PersonTable.email
CREATE(AddressTable)
}
}
}
)
)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 only will 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 is annotated the @DBRow will be generated a Table object, its name is
class name + 'Table'.
Now, let's do the real _INSERT_s:
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 statements could insert objects directly. You can insert one or multiple objects once.
The DELETE statements 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 are used for representing columns'
names, they are extension property with Table, their type are ClauseElement, and they are 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 ClauseCondictions would use the AND or OR operator to 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 personIn 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 is similar with DELETE, it also use a WHERE clause to limit update conditions. But, the difference is the 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 is only available in SET
clauses, it is different with readonly property age in WHERE clauses.
You also could write the UPDATE statements without the WHERE clause that used for update all rows, but you should use it with caution.
Using transaction is simple in SQLlin, you just need to use a 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 is inside or outside of TABLE(databaseName) {...} doesn't matter.
You have learned how to use INSERT, DELETE and UPDATE statements. Next step you will learn SELECT statements. The SELECT statement is more complex than other statements, be ready :).