中文版请见这里
We have learned basic querying and using SQL functions in querying conditions. Let's learn some advanced skills of querying.
The UNION operator is used for merge two SELECT statements' results and these results must be of the same type.
In SQL, the UNION operator between with the two SELECT statements, but in SQLlin, we use a higher-order function to implement UNION:
fun sample() {
lateinit var selectStatement: SelectStatement<Person>
database {
PersonTable { table ->
selectStatement = UNION {
table SELECT WHERE (age GTE 5)
table SELECT WHERE (length(name) LTE 8)
}
}
}
}
You just need to write your SELECT statements in the UNION {...}
block. There must be at least two SELECT statements
inside the UNION {...}
block, if not, you will get a IllegalStateException
at runtime.
If you want to use UNION and UNION ALL interchangeably, just use UNION {...}
or UNION_ALL {...}
block nesting:
fun sample() {
lateinit var selectStatement: SelectStatement<Person>
database {
PersonTable { table ->
selectStatement = UNION {
table SELECT WHERE (age GTE 5)
UNION_ALL {
table SELECT WHERE (length(name) LTE 8)
table SELECT WHERE (name EQ "Tom")
}
}
}
}
}
Above code equals the SQL:
SELECT * FROM person WHERE age >= 5
UNION
SELECT * FROM person WHERE length(name) <= 8
UNION ALL
SELECT * FROM person WHERE name = "Tom"
SQLlin doesn't support subqueries yet, we will develop as soon as possible.
SQLlin supports joining tables now.
We need other two database entities:
@DBRow("transcript")
@Serializable
data class Transcript(
val name: String?,
val math: Int,
val english: Int,
)
@Serializable
data class Student(
val name: String?,
val age: Int?,
val math: Int,
val english: Int,
)
@Serializable
data class CrossJoinStudent(
val age: Int?,
val math: Int,
val english: Int,
)
The Transcript
represents an other table. And the Student
represents the join querying results' type (so Student
doesn't need to be annotated @DBRow
), it owns all column names that belong to Person
and Transcript
.
fun joinSample() {
db {
PersonTable { table ->
table SELECT CROSS_JOIN<CrossJoinStudent>(TranscriptTable)
}
}
}
The CROSS_JOIN
function receives one or multiple Table
s as parameters. In normal SELECT statements, the statements' querying results' type is
depended on the Table
that be generated by sqllin-processor, but JOIN operator will change it to specific type. In above sample, CROSS_JOIN
changes
the type to CrossJoinStudent
.
Note, because CROSS JOIN owns feature in SQL. If the columns that be queried by a SELECT statement that with CROSS JOIN clause include the same name columns in the two tables, this will cause the querying to fail. Because a class isn't allowed to have multiple properties those have same names, sqllin-dsl doesn't support the CROSS JOIN with columns of the same name.
fun joinSample() {
db {
PersonTable { table ->
table SELECT INNER_JOIN<Student>(TranscriptTable) USING name
table SELECT NATURAL_INNER_JOIN<Student>(TranscriptTable)
table SELECT INNER_JOIN<CrossJoinStudent>(TranscriptTable) ON (name EQ TranscriptTable.name)
}
}
}
The INNER_JOIN
is similar to CROSS_JOIN
, the deference is INNER_JOIN
need to connect a USING
or ON
clause. If a INNER JOIN statement
without the USING
or ON
clause, it is incomplete, but your code still be compiled and will do nothing at runtime.
The NATURAL_INNER_JOIN
will produce a complete SELECT statement (same with CROSS_JOIN
). So, you can't add a USING
or ON
clause behind it, this is
guaranteed by Kotlin compiler.
Note, the behavior of a INNER_JOIN
clause with a ON
clause is same with CROSS_JOIN
, you can't select a column that has same name in two tables.
The INNER_JOIN
have an alias that named JOIN
, and NATURAL_INNER_JOIN
also have an alias that named NATURAL_JOIN
. This is just like you can
bypass a INNER
keyword in SQL's inner join querying.
fun joinSample() {
db {
PersonTable { table ->
table SELECT LEFT_OUTER_JOIN<Student>(TranscriptTable) USING name
table SELECT NATURAL_LEFT_OUTER_JOIN<Student>(TranscriptTable)
table SELECT LEFT_OUTER_JOIN<CrossJoinStudent>(TranscriptTable) ON (name EQ TranscriptTable.name)
}
}
}
The LEFT_OUTER_JOIN
's usage is very similar with INNER_JOIN
, the difference just is their API names.
You have learned all usages with SQLlin, enjoy it and stay Stay tuned for SQLlin's updates :)