Query Types

Raw

A raw query simply means that Quill will pass along the query to the driver and execute it, this can be useful if your query is very complex and normal Quill methods don't let you implement it easily.

In order to run a raw query you will need to call the raw() method on the QuillExecutor.

Example:

        async()
                .raw()
                .method(QueryMethod.RETRIEVE_DATA)
                .query("SELECT * FROM a_table WHERE user = ?")
                .param("UserName")
                .sendAndIgnore();

There are a few things to understand here. Primarily, the method(QueryMethod) indicates what the scope of our query is. The specification for the QueryMethod enum is to distinguish between reading and writing operations. The possible values are:

    UPDATE            We want to modify, i.e. write something
    RETRIEVE_DATA     Our query will return a Result containing the selected data
    NORMAL            Our query is used to manage the database, for example a "CREATE" query

We will then need to specify the query itself with the appropriate query(String) method. Every parameter in our query should be substituted for an interrogation mark ? in order to safely pass them to the database.

Then, we will repeatedly call param(Object) for every parameter we have in our query. The object here represents any SQL object which includes Java primitives and custom types implemented by your Quill Driver.

Then we can call any "query finishing" method. This includes send(), sendAndIgnore() and send(QueryOption...). Note that this last part largely depends on what you have set as the query method. If you want to retrieve data from the database, you should obviously use the send() method which returns the Result containing all of the data. If you only need to manage databases or tables you can prefer sendAndIgnore() which automatically discards any Result, simplifying your code.

Notice

The query literal, which you give in the query(String) method, should be as safe as possible in the sense that Quill does not perform any kind of check on it. If you are taking the query from a user supplied source you should be sanitizing it and responsible for the possible SQL Injection that you might cause. If the source of your query is doubtful, you should ALWAYS prefer a specified query depending on your needs. In general, raw() should not be used unless the query is static and decided in your code.

Select

A select query is, as in any SQL database, used to retrieve sets of data from a table. Quill implements it and provides methods to modify the query as you prefer.

Example of a Quill select query:

        Result send = async()
                .select()
                .columns("first_column", "second_column")
                .from("table_name")
                .where().isEqual("third_column", "A_Cool_String")
                .send();

This is equivalent to the SQL query:

SELECT first_column, second_column FROM table_name WHERE third_column = 'A_Cool_String'

There is a lot going on here. The first thing we can see is the specification of which columns we want to select. This is done in the columns(String...) method. Inside we can pass any column name and also aggregate functions, for example:

        Result result = async()
                .select()
                .columns("SUM(first_column)", "AVG(second_column)")
                .from("table_name")
                .where().isEqual("third_column", "A_Cool_String")
                .send();

Next we can add any where clause. Please see the specification for it in this wiki.

The result here is kept, because, by standard, select is a RETRIEVE_DATA query, which means that it will always give data back. Because we are working in async, the Result is a commodity class which helps you use the data whenever it is returned. Note that when you run send(), the execution of the underlining thread from which you call it will continue, and the result will be populated only when the database returns data.

Example reading of data:

        Result result = async()...send();

        result.await(reader -> {
            try {
                Long sum = reader.get(0);
            } catch (Exception e) {
                e.printStackTrace();
            }
        });

The code inside of await will be called whenever the query finishes.