Making Queries - novus/novus-jdbc GitHub Wiki

Queries are performed against a database using a [QueryExecutor]. There are five main methods for making queries against a database: select, insert, update, delete and merge. These methods form the basic data manipulation language of most SQL databases and are the five types of query for which Novus-JDBC has specialized handling.

General

Method invocation can be broken into two camps: those with developer supplied arguments and those which are just a plain query string. For queries which are just a query string, that is they do not contain a "?" wildcard placeholder, a PreparedStatement is not generated. This saves on the overhead of a round-trip communication with the database but it does not guard against SQL Injection attacks. Developers are encouraged not to pass plain query strings that were created via string concatenation.

Objects of type Iterable passed as arguments are handled differently from other arguments. For an IN clause, only one '?' placeholder is required in the query string. The Queryable will unwind the query string and substitute in the proper number of '?' based on the length/size of the Iterable. This also means that anything which is iterable only once (such as an Iterator) will fail to work properly with Novus-JDBC.

Select Statements

The standard select statement returns an Iterator containing the parsed contents of the underlying ResultSet. Each iteration through the returned Iterator causes the evaluation of the next row. If all rows are desired at once or if only one row is needed, two other forms of select methods are available.

Select One

The selectOne method works almost identical to the select method except that it returns an [Option]. Only the first row of a query is evaluated. Hence, if the query would result in no rows found, a None is returned.

Eagerly Select

More of a convenience member function, returns a [List] instead of an Iterator. The full cost of evaluating each row is paid at the time of evocation rather than delayed until needed.

Insert Statements

Insert returns an Iterator of the auto-generated key column. It is assumed, then, that the database has been set up to have an auto-incremented column of type integer as the primary key.

Update and Delete Statements

Update and delete statements return a count of how many columns were affected by the query statement.

Merge Statements

Not all databases support merge. The merge method assumes that the contents of one or more tables is being moved to another table. Returns an Iterator of the auto-incremented primary key like an insert statement. For all merge statements that would not work this way, it is suggested to use the update method.