query.md

Query

中文版请见这里

The SELECT statements are more complex than others, because SELECT statements have more clauses.

Basic

The simplest usage is querying all data in the table:

fun sample() {
    lateinit var selectStatement: SelectStatement<Person>
    database {
        PersonTable { table ->
             selectStatement = table SELECT X
        }
    }
    selectStatement.getResult().forEach { person ->
        println(person)
    }
}

The X represents without any clause, we’ve seen it in DELETE statements.

The SELECT statement owns the querying results, this is another difference with other statements. So, you need to declare a variable that type is SelectStatement<T>. The generic parameter T is your database entity's type that you expect to deserialize. You should assign a SELECT statement you built to this variable.

Note, all statements will only be executed when the DatabaseScope ends, we mentioned this in the Modify Database and Transaction. So, you must invoke the getResults function outside the database { ... } block, SQLlin will help you deserialize querying results to objects that you expected.

Single Clause

In SQL, we usually use some clauses to make a conditional query. These clauses could be used alone: WHERE, ORDER BY, LIMIT and GROUP BY. The sample code like this:

fun sample() {
    database {
        PersonTable { table ->
            table SELECT WHERE(age LTE 5)
            table SELECT ORDER_BY(age to DESC)
            table SELECT ORDER_BY(age)
            table SELECT LIMIT(3)
            table SELECT GROUP_BY(name)
        }
    }
}

Clause Connection

Sometimes we need to use multiple clauses once. In SQL, some clauses must be added after other clauses. For example, the HAVING behind with the GROUP BY. SQLlin makes sure you don't make mistakes in the order of clauses, the clauses connection regular like this chart:

Clause/Statement	Connectable
SELECT	WHERE, ORDER BY, LIMIT, GROUP BY
WHERE	LIMIT, ORDER BY, GROUP BY
GROUP BY	HAVING, ORDER BY
HAVING	ORDER BY, LIMIT
ORDER BY	LIMIT
LIMIT	OFFSET
OFFSET	/

A SELECT statement with multiple clauses like this:

fun sample() {
    lateinit var selectStatement: SelectStatement<Person>
    database {
        PersonTable { table ->
             selectStatement = table SELECT WHERE (age LTE 5) GROUP_BY age HAVING (upper(name) EQ "TOM") ORDER_BY (age to DESC) LIMIT 2 OFFSET 1
        }
    }
    selectStatement.getResult().forEach { person ->
        println(person)
    }
}

Next Step

Next step, we will learn the concurrency safety, how to use SQL functions and advanced query：

• Concurrency Safety
• SQL Functions
• Advanced Query