Convenience Methods

If you're not a fan of writing raw SQL, the SQLite API provides a set of convenience methods that you can use to query data more easily.

Creating Tables

To use the convenience methods, you must create a Table. A Table defines the name in the database, as well as each column and its datatype.

Let's create a Table that represents our ServerMember object. Because a Table is an object, we'll create a static class that contains all of our tables inside it. It is perfectly acceptable (And recommended) to keep your Table objects static. They will never need instantiated and do not hold state.

            public final class Tables {
                public static final Table MEMBERS = Table.of("server_members");
                public static final Table.Column<UUID> world = MEMBERS.col("name", SqlType.UUID_TEXT);
                public static final Table.Column<String> name = MEMBERS.col("name", SqlType.TEXT);
                public static final Table.Column<Integer> age = MEMBERS.col("enabled", SqlType.I32);
                public static final Table.Column<Boolean> activeMember = MEMBERS.col("activeMember", SqlType.BOOLEAN);

                // Because Tables.class is just a bunch of static objects, we're making the constructor private to hide it and prevent instantiation.
                private Tables() {
                }
            }
        

To create our table, we call the static Table.of() method. The value passed into this method becomes the table name in the database. Alongside the Table itself, we also create a bunch of Table.Column<T> objects, which represent the columns in the table itself.

You may have noticed the SqlType object when defining the columns. The static col() method on Table takes a column name and its backend datatype. Both Param and Row use SqlType under the hood, and you will learn more about this type when creating custom data types to use in the SQLite API. For now, just now that SqlType is the object that contains the real logic for converting Java objects to SQLite datatypes and back.

Using the Convenience Methods

Now that we have a Table, we can finally use the convenience methods. Several convenience methods exist for developers to use:

selectAll()
selectWhere()
insert()
update()
delete()

`selectAll()`

The first convenience method available is selectAll(). This method simply returns all records in a table.

                List<ServerMember> members = db.selectAll(
                        Tables.MEMBERS,
                        row -> new ServerMember(
                                row.uuid("uuid"),
                                row.string("name"),
                                row.i32("age"),
                                row.bool("activeMember")
                        ));
            

When you use any of the convenience methods, the first parameter is always the Table. Depending on the method, you may also provide a RowMapper or a list of Param objects.

`selectWhere()`

A similar method to selectAll() is selectWhere(). This method, as the name implies, lets you select objects using a WHERE clause. This method still returns a list, but will only return objects that meet your filter criteria.

                List<ServerMember> members = db.selectWhere(
                        Tables.MEMBERS,
                        "name = ?",
                        row -> new ServerMember(
                                row.uuid("uuid"),
                                row.string("name"),
                                row.i32("age"),
                                row.bool("activeMember")
                        ),
                        Tables.name.param("TerrorByte"));
            

The second argument is a SQL WHERE clause. You are still required to type some raw SQL here, but not much. Do not include the WHERE keyword, as it is automatically prepended by the SQLite API for you.

We can provide Param arguments as normal to replace the placeholders in the where clause. However, instead of explicitly providing a Param.text("TerrorByte") argument, we have used a new helper method, Table.Column<T>#param(). This method returns the correct type of Param automatically instead of you having to know what type that column is represented by. This is especially useful when using custom data types or when working with large tables.

`insert()`

You can insert records into a table with the insert() method. This method takes a Table in the first argument, and a Map in the second argument. This Map maps values to columns.

                int affectedRows = db.insert(
                    Tables.MEMBERS,
                    Map.of(
                        Tables.uuid, new UUID(),
                        Tables.name, "TerrorByte",
                        Tables.age, 27,
                        Tables.activeMember, true
                    ));
            

In the Map, you should provide a value for each column in the table. If you do not provide a column, it will be omitted from the INSERT query. If null values are acceptable in your DB, this is fine. However, if SQLite expects a value, your insert will fail.

The return value of this method is the number of rows affected, just like execute() which we read about in a previous guide.

`update()`

If you need to update a specific record or records, you may use the update() method. This method takes a Table in the first argument, and a Map in the second argument, just like with insert() However, it also takes a WHERE clause in a third argument, and a bunch of Param objects in the fourth and later arguments.

                int affectedRows = db.update(
                    Tables.MEMBERS,
                    Map.of(
                        Tables.uuid, new UUID(),
                        Tables.name, "TerrorByte",
                        Tables.age, 27,
                        Tables.activeMember, true
                    ),
                    "age = ?",
                    Tables.age.param(26)
                );
            

In the above example, any ServerMember whose age is 26 will be updated with a new UUID, the name of "TerrorByte", the age of 27, and will be set as an active member. This isn't a great example, but shows how powerful the update() method can be.

The return value of this method is the number of rows affected, just like insert()

`delete()`

The last convenience method is delete(). This method takes a Table in the first argument, and a WHERE clause in the second argument. The last argument, as expected, is a bunch of Param objects.

                int affectedRows = db.delete(
                    Tables.MEMBERS,
                    "name = ?",
                    Tables.name.param("TerrorByte")
                );
            

By now, these examples should be fairly straight forward. In this instance, all records with a name of "TerrorByte" will be deleted.

The return value of this method is the number of rows affected, just like update() and insert()

11 March 2026