Home

Find and Query

The terms “find” and “query” for data retrieval are used in the same sense in PriDE as you may know it from other persistence concepts. Finding means to select data with the expectation to retrieve 1 result or none treating the presence of multiple results as an exception case. The most common example is a selection by primary key.

A query means to select data with an unpredictable number of result. PriDE is designed to take “unpredictable” literally and allows to process even millions of results in an efficient way in Java.

Find

Examples for finding a record with PriDE were already part of the quick start tutorial and the chapter about entity, adapter, and descriptor. But let’s go into some details here for a deeper understanding. The important things to know:

When you are working with a generated hybrid entity, a find operation by primary key fields is a single line of code like that:

Customer customer = new Customer(1);

PriDE’s generator produces a so-called “re-constructor” if the referred database table has a primary key. The re-constructor expects a parameter for all attributes making up the primary key, initializes the entity accordingly and calls the entity’s findXE() method. I.e. if the retrieval by primary key fails, the re-constructor throws a pm.pride.FindException. The FindException is derived from java.sql.SQLException which must be handled anyway.

When you are working with separate adapter classes, the same operation takes two lines of code:

CustomerEntity customer = new CustomerEntity(1);
new CustomerAdapter(customer).findXE();

Query

Whenever selecting multiple records from the database, PriDE returns a pm.pride.ResultIterator to iterate through the results. The ResultIterator encapsulates a java.sql.ResultSet, i.e. it is an open database cursor which is suitable for any amount of results. Taking up the example from the quick start tutorial, you can select all customers from the CUSTOMER table by the following lines of code:

Customer customer = new Customer();
ResultIterator ri = customer.queryAll();

To allow the processing of large amounts of records, the ResultIterator works slightly different from what you may be used to.

Pulling all this together, an iteration for direct result processing looks like that, e.g. if we would like to print all customers to the console:

Customer customer = new Customer();
ResultIterator ri = customer.queryAll();
if (!ri.isNull()) {
    do {
        System.out.println(customer);
    }
    while(ri.next());
}
else {
    System.out.println("No customers found");
}

A ResultIterator must be closed when the iteration is over, because it holds an open java.sql.ResultSet inside which in turn holds an open database connection. For convenience, the ResultIterator closes its ResultSet automatically when your code iterates to the end or if there occurs a database exception while fetching results. So usually you don’t have to care about closing the iterator. For special cases, call the close() method.

The direct iteration is a highly efficient option on the one hand (PriDE is a lot faster with this approach than any JPA implementation), but on the other hand it is not the typical case. Usually the amount of results is small and they don’t need to be processed on such a low layer of the application. Instead you may want to pass them as a list or array to a higher application layer where the business logic resides in. In this case, you can call appropriate functions on the ResultIterator:

// Extract the customers as list
List<Customer> allCustomers = ri.toList(Customer.class);

// Extract the customers as list with a limitation for the amount of results
List<Customer> allCustomers = ri.toList(Customer.class, 100);

// Extract the customers as array
Customer[] allCustomers = ri.toArray(Customer.class);

PriDE produces the entities in the lists and arrays in the same way as mentioned earlier for the findRC() method: by cloning the original entity with a public clone() method, by a copy constructor, getting passed the original entity as a parameter, or by a default constructor. All entity types generated by PriDE have a clone() method with public visibility implemented, based on Java’s protected default implementation. This is a single-line implementation, so it’s very simple to provide even if you are not using the generator.

In general it is strongly recommended to define a base class for all entity types where all those standard capabilities are encapsulated. Not only a clone() implementation but also a reasonable default (reflection-based) toString() method and maybe even a set of standard attributes as explained in chapter Entity Inheritance.

Streaming

Another alternative form of result processing are the ResultIterator’s stream methods. There are two different methods available.

The method stream(Class) provides the results as a “real” stream with a new result instance for every result. The resulting stream is suitable for any kind of Java stream operations but should be used with care when selecting a very large number of results combined with stream operations which have to keep all the results (e.g. sort and collect operations). This may cause serious memory problems.

The method streamOE(Class) provides all results in the original entity just as it is the case in direct iterating and processing demonstrated above. This kind of stream is suitable for any amount of results but can only be used for a limited set of stream operations. Especially operations that rely on object identity will usually not work. Operations for direct processing like forEach() or count() won’t cause any problems. The direct processing example from the beginning of the query section would look like this when using streams:

Customer customer = new Customer();
customer.queryAll().streamOE()
    .forEach(c -> System.out.println(c));

Examples for find and query code can by found in the class QueryClient in the package query of the PriDE manual source code repository on GitHub.

Selection criteria

PriDE has a few different features to assemble SQL where-clauses for queries:

Query-by-example is something you already come across in the section about finder methods. It is addressed by the method queryByExample(String… dbfields) which is available in every adapter and every hybrid entity class. The where-clause is assembled from an equality expression for all the database columns being passed to the function call where the values are taken from the corresponding attributes of the entity. Of course, the entity must be initialized accordingly first. Taking up the Customer entity from the quick start tutorial, the following query-by-example would allow to find all customers with first name “Peter”:

Customer customer = new Customer();
customer.setFirstName("Peter");
ResultIterator ri = customer.queryByExample(Customer.COL_FIRST_NAME);

The query takes all the specified columns into account, considering also Null-values in appropriate attributes. E.g. passing Customer.COL_FIRST_NAME, Customer.COL_NAME to the query method without setting a name value in the customer entity, the resulting SQL query will look like this:

select id,name,first_name from CUSTOMER where ( first_name = 'Peter' AND name IS NULL )

Remember that yo can always check the SQL log file to find out what SQL statements have been assembled by PriDE. And always remember to use constants for the column names as you can see above rather than string literals. This allows you to keep track of which code depends on which aspects of your data model.

WhereCondition

Query-by-example is easy to use but limited to equality expressions. A more sophisticated tool in PriDE is the WhereCondition class. It allows to assemble more complicated queries with a fluent API as a compromise between syntax and type safety on the one hand and code readability and simplicity on the other hand. Lets start with a simple example of a where condition, producing the same query as above for an empty name and the first name “Paddy”:

WhereCondition byFirstNameAndEmptyName =
    new WhereCondition(Customer.COL_FIRST_NAME, "Paddy")
    .and(Customer.COL_NAME, null);
ResultIterator ri = new Customer().query(byFirstNameAndEmptyName);

The basic principle of the class is straight-forward:

WhereCondition byFirstNameAndEmptyName = new WhereCondition()
    .and(Customer.COL_FIRST_NAME, "Paddy")
    .and(Customer.COL_NAME, null);
ResultIterator ri = new Customer().query(byFirstNameAndEmptyName);

It doesn’t make a difference if you start with the or() method or the and() method at the top. Both methods are available in the more flexible variant xxx(String field, String operator, Object... value). The parameter operator is an SQL operator with the commonly known ones listed in the Operator interface within the WhereCondition class. Using String rather than a type-safe Enum keeps the API open for future extensions and vendor-specific operators. Multiple values can be passed for the operators WhereCondition.Operator.BETWEEN and WhereCondition.Operator.IN. E.g. selecting customers with first name “Paddy” or “Mary” can be expressed by

.and(COL_FIRST_NAME, IN, "Paddy", "Mary");

The variants xxxNotNull(...) will only add the sub-condition if the (first) field value differs from Null. This is of interest for the assembly of conditions from interactive search criteria input. An empty criterion usually means ‘do not consider’ rather than ‘must be empty’.

The variant without parameters opens up a sub-condition which must be completed by function bracketClose(). The following condition looks for early customers (id less than 1000) that registered with a suspicious name “Mickey Mouse”:

WhereCondition byMickeyMouse = new WhereCondition()
    .and(COL_ID, LESS, 1000)
    .and()
        .or(COL_FIRST_NAME, IN, "Mickey", "Mouse")
        .or(COL_NAME, IN, "Mickey", "Mouse")
    .bracketClose();

What of you are interested in other suspicious cases where name and first name are equal. In this case, the value is a field name itself and you have to bypass the value formatting. This is achieved by passing pre-formatted SQL values like that:

.and(COL_FIRST_NAME, SQL.pre(COL_NAME))

Finally the WhereCondition can be extended by ordering and grouping clauses. E.g. the following condition selects all customers ordered by name and first name:

new WhereCondition().orderBy(COL_NAME).orderBy(COL_FIRST_NAME)

Especially when you select data in order, the ResultIterator provides the additional methods spoolToList() and spoolToArray() to read results in chunks. This allows to run multiple co-ordinated selects in parallel as an alternative to joins when selecting along 1:N relationships. Joins cause a duplication of transfer data in such a case which may cause mentionable latency in very large selections.

And finally finally the methods bindvars…() in the WhereCondition class give you fine-grained control over which parts of the expression should use bind variables and which ones should be plain SQL. Bind variables become a relevant issue for databases on heavy duty and therefore are also discussed in the prepared operations chapter.

Arbitrary Criteria

When the going gets tough there is a method query(String where) available in all adapters and hybrid entities. The function gets passed a fully assembled where-clause without the leading where keyword. It takes any limitations away but also a lot of convenience and safety. Assembly of complicated SQL expressions may not only become an issue in where-clauses but in any multi-record operation likes joins, merge statements, or mass updates. PriDE can help you assembling these expressions with the class pm.pride.SQLExpressionBuilder resp. the function pride.pm.SQL.build(). It preserves the native readability of complicated SQL on the one hand and allows you to work with table name and column name constants on the other hand to preserve code dependency tracking. In the preceding chapters you learned already that the PriDE principles heavily emphasize this central aspect for robust application design. The expression builder is addressed in a separate chapter. However, to give you a first impression, here is an example how to build the most complicated expression above - the Mickey Mouse case - using the expression builder:

String byMickeyMouse = SQL.build(
    "@ID < 1000 AND (" +
    "  @FIRST_NAME IN ('Mickey', 'Mouse') OR " +
    "  @NAME IN ('Mickey', 'Mouse')" +
    ")",
    COL_ID, COL_FIRST_NAME, COL_NAME);
ResultIterator ri = new Customer().query(byMickeyMouse);

This is a very limited example but you may already recognize the advantage over using the convenient WhereCondition fluent API: The SQL code is plain to see in nearly its native structure and notation although the actual assembly still makes use of the table and column name constants. The expression string may contain ? characters for bind variables. In this case, the variable values must be appended to the expression in the query() method call. The following example uses that feature for the ID threshold of 1000, making up the same selection as above:

String byMickeyMouse = SQL.build(
    "@ID < ? AND (" +
    "  @FIRST_NAME IN ('Mickey', 'Mouse') OR " +
    "  @NAME IN ('Mickey', 'Mouse')" +
    ")",
    COL_ID, COL_FIRST_NAME, COL_NAME);
ResultIterator ri = new Customer().query(byMickeyMouse, 1000);

Using bind variables is the easiest way for you as a developer to overcome the problem of value formating, which may become a bit tricky for complex data types like dates and timestamps. However, this should of course not be the main reason to use bind variables. See chapter Prepared Operations for purposeful usage. As long as you are working with small databases, it is OK just to utilize the formatting side effect.

If you want to learn more about the expression builder right now, read the chapter SQL Expression Builder.