er.extensions.eof

Class ERXSQLQueryWithBindingsUtilities

java.lang.Object

er.extensions.eof.ERXSQLQueryWithBindingsUtilities

public class ERXSQLQueryWithBindingsUtilities
extends Object

This class allows SQL queries with binded variables to be run against the database. It's possible to obtain EOs, raw rows or a batch iterator, depending on the used method.

Binded values are passed in using ERXSQLBinding implementations. Currently, there are two available implementations: ERXKeyValueBinding and ERXObjectBinding. The first one should be used if the binding matches a column modeled in an EOModel. This way, any data type conversions that would happen on normal EOF usage are also applied when generating the SQL query. An example of this can be converting a boolean value to a string or an integer. ERXObjectBinding can be used for non-modeled columns. Please read the ERXObjectBinding class documentation for important notes regarding some databases that expect SQL queries with binding typification.

This class is used by calling the most appropriate static method for the intended usage. Specific requirements for the SQL query resulting columns are detailed on each of the method's documentation. Generally, you need to provide a query returning the appropriate columns and using the '?' character for each binded value. Depending on the database plug-in being used, the '?' character will be automatically replaced by a different expression if the ERXKeyValueBinding class is used to wrap the binded value. In PostgreSQL, for instance, the '?' character could be replaced by '?::varchar(1000)'.

Here are some sample uses of this class:

   // Obtains EOs that match the query
   ERXSQLQueryWithBindingsUtilities.selectObjectsOfEntityForSqlWithBindings(editingContext(), Song.ENTITY_NAME,
     "SELECT * FROM SONG WHERE FAVORITE = ? AND DURATION > ? ORDER BY NUMBER ASC", true,
     new ERXKeyValueBinding("favorite", true), new ERXKeyValueBinding("duration", 120));
     
   // Obtains raw rows for query
   ERXSQLQueryWithBindingsUtilities.rawRowsForSqlWithBindings(editingContext(), Song.ENTITY_NAME,
     "SELECT t0.NAME, t0.DURATION FROM SONG t0 WHERE COMPOSER = ?", new ERXKeyValueBinding("composer", "Mozart"));
     
   // Runs a query that returns no objects
   ERXSQLQueryWithBindingsUtilities.runSqlQueryWithBindings(editingContext(), "SongsModel",
     "DELETE FROM SONG WHERE FAVORITE = ?", new ERXKeyValueBinding("favorite", false));
   
   // Obtains ERXFetchSpecificationBatchIterator
   // Note the query must obtain the primary key!
   ERXSQLQueryWithBindingsUtilities.batchIteratorForObjectsWithSqlWithBindings(editingContext(), Employee.ENTITY_NAME,
     "SELECT ID FROM EMPLOYEE WHERE HEIGHT < ? AND FIRST_NAME = ? ORDER BY NUMBER DESC", false, 100,
     new NSArray<EOSortOrdering>(new EOSortOrdering[] {new EOSortOrdering("number", EOSortOrdering.CompareDescending)}),
     new ERXObjectBinding(190), new ERXKeyValueBinding("firstName", "John"));
 

Constructor Summary

Constructors

Constructor and Description
ERXSQLQueryWithBindingsUtilities()

Method Summary

Modifier and Type	Method and Description
static ERXFetchSpecificationBatchIterator	batchIteratorForObjectsWithSqlWithBindings(EOEditingContext ec, String entityName, String query, boolean refreshesCache, int batchSize, NSArray sortOrderings, ERXSQLBinding... bindings) Obtains a batch iterator for the objects resulting from the passed in SQL query with binded variables.
protected static String	processedQueryString(String query, EOSQLExpression expression, ERXSQLBinding... bindings) Replaces the binding placeholder characters by the appropriate token for the used database.
static NSArray	rawRowsForSqlWithBindings(EOEditingContext ec, String entityName, String query, ERXSQLBinding... bindings) Runs a select query against the database, obtaining the resulting raw rows.
static void	runSqlQueryWithBindings(EOEditingContext ec, String modelName, String query, ERXSQLBinding... bindings) Runs an SQL query against the database with binded variables.
static NSArray	selectObjectsOfEntityForSqlWithBindings(EOEditingContext ec, String entityName, String query, boolean refreshesCache, ERXSQLBinding... bindings) Runs a select query against the database, obtaining the resulting EOs.
static NSArray	selectObjectsOfEntityForSqlWithBindings(EOEditingContext ec, String entityName, String query, boolean refreshesCache, Integer fetchLimit, ERXSQLBinding... bindings) Runs a select query against the database, obtaining the resulting EOs.
static NSArray	selectObjectsOfEntityForSqlWithBindings(EOEditingContext ec, String entityName, String query, ERXSQLBinding... bindings) Runs a select query against the database, obtaining the resulting EOs.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

ERXSQLQueryWithBindingsUtilities

public ERXSQLQueryWithBindingsUtilities()

Method Detail

selectObjectsOfEntityForSqlWithBindings

public static NSArray selectObjectsOfEntityForSqlWithBindings(EOEditingContext ec,
                                                              String entityName,
                                                              String query,
                                                              ERXSQLBinding... bindings)

Runs a select query against the database, obtaining the resulting EOs.

Please read the selectObjectsOfEntityForSqlWithBindings(EOEditingContext, String, String, boolean, Integer, ERXSQLBinding...) documentation for the necessary details on how to use both methods.

Parameters:

ec - The editing context where the objects will be placed

entityName - The name of the entity whose objects are returned by the query

query - The SQL query

bindings - The variable bindings, wrapped in ERXSQLBinding objects

Returns:

array of objects obtained by the query

See Also:

selectObjectsOfEntityForSqlWithBindings(EOEditingContext, String, String, boolean, Integer, ERXSQLBinding...)

selectObjectsOfEntityForSqlWithBindings

public static NSArray selectObjectsOfEntityForSqlWithBindings(EOEditingContext ec,
                                                              String entityName,
                                                              String query,
                                                              boolean refreshesCache,
                                                              ERXSQLBinding... bindings)

Runs a select query against the database, obtaining the resulting EOs.

Please read the selectObjectsOfEntityForSqlWithBindings(EOEditingContext, String, String, boolean, Integer, ERXSQLBinding...) documentation for the necessary details on how to use both methods.

Parameters:

ec - The editing context where the objects will be placed

entityName - The name of the entity whose objects are returned by the query

query - The SQL query

refreshesCache - If true, the cached snapshots will be refreshed. See EOFetchSpecification.setRefreshesRefetchedObjects(boolean).

bindings - The variable bindings, wrapped in ERXSQLBinding objects

Returns:

array of objects obtained by the query

See Also:

selectObjectsOfEntityForSqlWithBindings(EOEditingContext, String, String, boolean, Integer, ERXSQLBinding...)

selectObjectsOfEntityForSqlWithBindings

public static NSArray selectObjectsOfEntityForSqlWithBindings(EOEditingContext ec,
                                                              String entityName,
                                                              String query,
                                                              boolean refreshesCache,
                                                              Integer fetchLimit,
                                                              ERXSQLBinding... bindings)

Runs a select query against the database, obtaining the resulting EOs.

This method allows a SELECT SQL query to be run, using binded variables. The method will process the query result and return EOs of the requested entity.

There are some important requirements regarding the passed in query:

• The query must return all the columns of the affected table that are mapped in the modeled Entity. 'SELECT * FROM Table ...' is the easiest way to achieve this. Alias are supported, like 'SELECT t0.* FROM Table t0 ...'.
• The placeholder for binded variables must be the '?' character. This method will process the query before executing it, replacing the generic placeholder character by the one appropriate to the used database. Ex: when using PostgreSQL, '... WHERE FIRST_NAME = ?' may be replaced by '... WHERE FIRST_NAME = ?::varchar(1000)'.
• The '?' character does not need to be quoted if it appears on the query inside a string literal. This situation is automatically detected and the substitution is not performed.
• The number of binding wrappers passed in as last arguments of this method must match the number of binding placeholders in the query.

Parameters:

ec - The editing context where the objects will be placed

entityName - The name of the entity whose objects are returned by the query

query - The SQL query

refreshesCache - If true, the cached snapshots will be refreshed. See EOFetchSpecification.setRefreshesRefetchedObjects(boolean).

fetchLimit - Fetch limit, or null for no limit

bindings - The variable bindings, wrapped in ERXSQLBinding objects

Returns:

array of objects obtained by the query

batchIteratorForObjectsWithSqlWithBindings

public static ERXFetchSpecificationBatchIterator batchIteratorForObjectsWithSqlWithBindings(EOEditingContext ec,
                                                                                            String entityName,
                                                                                            String query,
                                                                                            boolean refreshesCache,
                                                                                            int batchSize,
                                                                                            NSArray sortOrderings,
                                                                                            ERXSQLBinding... bindings)

Obtains a batch iterator for the objects resulting from the passed in SQL query with binded variables.

The requirements for the passed in SQL query are the same as selectObjectsOfEntityForSqlWithBindings(EOEditingContext, String, String, boolean, ERXSQLBinding...), except for an important difference: the query must return only one column with the entity primary keys. Instead of doing a query like 'SELECT * FROM Person ...', assuming ID as the name of the primary key column, the query would be 'SELECT ID FROM Person ...'. Despite this, the batch iterator will provide you the full initialized EOs.

SORTING - If you want to obtain sorted results, you'll have to add the sorting information to the query (using ORDER BY clauses) and pass an array of EOSortOrderings to this method. The sorting information in the query will be used to sort the primary key array. The EOSortOrderings are used to sort objects inside each batch. To obtain sorted results as expected, both sorting criteria should be similar.

Parameters:

ec - The editing context where the objects will be placed

entityName - The name of the entity whose objects are returned by the query

query - The SQL query

refreshesCache - If true, the cached snapshots will be refreshed. See EOFetchSpecification.setRefreshesRefetchedObjects(boolean).

batchSize - The size of each batch

sortOrderings - EOSortOrderings for batches (see description above)

bindings - The variable bindings, wrapped in ERXSQLBinding objects

Returns:

batch iterator for the passed in query

runSqlQueryWithBindings

public static void runSqlQueryWithBindings(EOEditingContext ec,
                                           String modelName,
                                           String query,
                                           ERXSQLBinding... bindings)

Runs an SQL query against the database with binded variables.

The requirements for the passed in SQL query are the same as selectObjectsOfEntityForSqlWithBindings(EOEditingContext, String, String, boolean, ERXSQLBinding...), except for the fact it should not return any objects. This method is intended to be used with non-SELECT queries, like UPDATE, INSERT or DELETE.

Parameters:

ec - The current editing context (used to obtain the correct OSC)

modelName - The name of the model (used to apply the query on the correct DB)

query - The SQL query

bindings - The variable bindings, wrapped in ERXSQLBinding objects

rawRowsForSqlWithBindings

public static NSArray rawRowsForSqlWithBindings(EOEditingContext ec,
                                                String entityName,
                                                String query,
                                                ERXSQLBinding... bindings)

Runs a select query against the database, obtaining the resulting raw rows.

This method allows a SELECT SQL query to be run, using binded variables. The method will return the resulting rows without any additional processing.

The requirements for the passed in SQL query are the same as selectObjectsOfEntityForSqlWithBindings(EOEditingContext, String, String, boolean, ERXSQLBinding...), except for the fact you are free to return whatever columns you want.

Parameters:

ec - The current editing context (used to obtain the correct OSC)

entityName - The name of an entity affected by the query. This does not necessarily have to be accurate, as this value is only used to find out the correct database that the query will be run against.

query - The SQL query

bindings - The variable bindings, wrapped in ERXSQLBinding objects

Returns:

The requested raw rows

processedQueryString

protected static String processedQueryString(String query,
                                             EOSQLExpression expression,
                                             ERXSQLBinding... bindings)

Replaces the binding placeholder characters by the appropriate token for the used database.

This method will also register each of the bindings in the given expression.

Parameters:

query - The original SQL query

expression - EOSQLExpression to be modified

bindings - The variable bindings, wrapped in ERXSQLBinding objects

Returns:

The processed query