Query Lite

Query builder library for SQLite.

Examples

• Create table
• Create index
• Rename table
• Add column
• Insert rows
• Update rows
• Select rows
• Delete rows
• Drop table
• Drop index

Create table

Query create = new Create()
                .table("person")
                .ifNotExist()
                .columns(new Column("id", DataType.INTEGER).primary().autoincrement().notNull(),
                         new Column("email", DataType.TEXT).unique().notNull(),
                         new Column("age", DataType.INTEGER).notNull().check("age >= 0"),
                         new Column("weight", DataType.REAL).notNull().check("weight >= 0"));

CREATE TABLE IF NOT EXISTS person (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                                   email TEXT UNIQUE NOT NULL,
                                   age INTEGER NOT NULL CHECK (age >= 0),
                                   weight REAL NOT NULL CHECK (weight >= 0));

Create index

Query create = new Create()
                .index("index_email")
                .unique()
                .on("person")
                .columns("email");

CREATE UNIQUE INDEX index_email ON person (email);

Rename table

Query alter = new Alter("person")
                .rename("human");

ALTER TABLE person RENAME TO human;

Add column

Query alter = new Alter("person")
                .addColumn(new Column("name", DataType.TEXT));

ALTER TABLE person ADD COLUMN name TEXT;

Insert rows

Query insert = new Insert()
                .into("person")
                .set("email", "[email protected]")
                .set("name", "John Doe")
                .set("age", 45)
                .set("weight", 81.2);

INSERT INTO person (email, name, age, weight) VALUES ("[email protected]", "John Doe", 45, 81.2);

Update rows

Query insert = new Update()
                .table("person")
                .set("age", 18)
                .where("id = 100");

UPDATE person SET age = 18 WHERE (id = 100);

Select rows

Query select = new Select()
                .columns("id, email, name")
                .from("person")
                .where("age >= 18")
                .groupBy("age")
                .orderBy("age")
                .limit(1000);

SELECT id, email, name FROM person WHERE (age >= 18) GROUP BY age ORDER BY age LIMIT 1000;

Delete rows

Query delete = new Delete()
                .from("person")
                .where("age >= 18");

DELETE FROM person WHERE (age >= 18);

Drop table

Query drop = new Drop()
                .table("person");

DROP TABLE person;

Drop index

Query drop = new Drop()
                .index("index_email");

DROP INDEX index_email;

Query parameters

Query select = new Select()
                .columns("id, email, name")
                .from("person")
                .where("age >= ?")
                .groupBy("age")
                .orderBy("age")
                .limit("?");

String query = select.query(20, 500);

The variable query will contain:

SELECT id, email, name FROM person WHERE (age >= 20) GROUP BY age ORDER BY age LIMIT 500;

Installation

Add the following code to your pom.xml:

<repositories>
    <repository>
        <id>jcenter</id>
        <url>https://jcenter.bintray.com</url>
    </repository>
</repositories>

and the dependency:

<dependency>
    <groupId>com.mauriciotogneri</groupId>
    <artifactId>querylite</artifactId>
    <version>0.5.0</version>
</dependency>

or if you use Gradle:

dependencies
{
    compile 'com.mauriciotogneri:querylite:0.5.0'
}