com.manydesigns.portofino.persistence

Class QueryUtils

java.lang.Object

com.manydesigns.portofino.persistence.QueryUtils

public class QueryUtils
extends Object

Utility class for running queries (SQL and HQL) against the database. Provides methods for many common cases, but you can always use the Hibernate Session directly.

Author:

Paolo Predonzani - paolo.predonzani@manydesigns.com, Angelo Lupo - angelo.lupo@manydesigns.com, Giampiero Granatella - giampiero.granatella@manydesigns.com, Alessio Stalla - alessio.stalla@manydesigns.com

Field Summary

Fields

Modifier and Type	Field and Description
static String	copyright
static String	FAKE_SELECT_PREFIX
protected static Pattern	FROM_PATTERN
protected static org.slf4j.Logger	logger
protected static String	WHERE_STRING

Constructor Summary

Constructors

Constructor and Description
QueryUtils()

Method Summary

Methods

Modifier and Type	Method and Description
static void	commit(Persistence persistence, String databaseName) Cleanly commits the current (for this thread) transaction of the given database.
protected static net.sf.jsqlparser.expression.Alias	getEntityAlias(String entityName, net.sf.jsqlparser.statement.select.PlainSelect query)
static Object	getObjectByPk(Persistence persistence, String database, String entityName, Serializable pk) Loads an object by primary key.
static Object	getObjectByPk(Persistence persistence, String database, String entityName, Serializable pk, String hqlQueryString, Object rootObject) Loads an object by primary key.
static Object	getObjectByPk(Persistence persistence, Table baseTable, Serializable pkObject) Loads an object by primary key.
static Object	getObjectByPk(Persistence persistence, Table baseTable, Serializable pkObject, String query, Object rootObject) Loads an object by primary key.
static Object	getObjectByPk(Session session, TableAccessor table, Serializable pk) Loads an object by primary key.
static List<Object>	getObjects(Session session, String queryString, Integer firstResult, Integer maxResults) Runs a query against the database.
static List<Object>	getObjects(Session session, String queryString, Object rootObject, Integer firstResult, Integer maxResults) Runs a query against the database.
static List<Object>	getObjects(Session session, String queryString, TableCriteria criteria, Object rootObject, Integer firstResult, Integer maxResults) Runs a query against the database.
static List<Object>	getObjects(Session session, TableCriteria criteria, Integer firstResult, Integer maxResults) Runs a query, expressed as TableCriteria, against the database.
static QueryStringWithParameters	getQueryStringWithParametersForCriteria(TableCriteria criteria) Tranforms a TableCriteria to a query string with an associated array of parameters.
static QueryStringWithParameters	getQueryStringWithParametersForCriteria(TableCriteria criteria, String alias, int initialParameterIndex) Tranforms a TableCriteria to a query string with an associated array of parameters.
static List<Object>	getRelatedObjects(Persistence persistence, String databaseName, String entityName, Object obj, String oneToManyRelationshipName) Navigates a ...
static Table	getTableFromQueryString(Database database, String queryString) Extracts the name of the main entity from a HQL query string, i.e. the first entity in the from clause.
static QueryStringWithParameters	mergeQuery(String queryString, TableCriteria criteria, Object rootObject) Merges a HQL query string with a TableCriteria object representing a search.
static net.sf.jsqlparser.statement.select.PlainSelect	parseQuery(net.sf.jsqlparser.parser.CCJSqlParserManager parserManager, String query)
protected static String	processTextMatchMode(TextMatchMode textMatchMode, String value)
static List<Object>	runHqlQuery(Session session, String queryString, Object[] parameters) Runs a HQL query against the database.
static List<Object>	runHqlQuery(Session session, String queryString, Object[] parameters, Integer firstResult, Integer maxResults) Runs a HQL query against the database.
static List<Object[]>	runSql(Session session, String sql) Runs a SQL query against a session.
static List<Object[]>	runSql(Session session, String queryString, Object[] parameters) Runs a SQL query against a session.

Methods inherited from class java.lang.Object

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

Field Detail

copyright

public static final String copyright

See Also:

Constant Field Values

WHERE_STRING

protected static final String WHERE_STRING

See Also:

Constant Field Values

FROM_PATTERN

protected static final Pattern FROM_PATTERN

logger

protected static final org.slf4j.Logger logger

FAKE_SELECT_PREFIX

public static final String FAKE_SELECT_PREFIX

See Also:

Constant Field Values

Constructor Detail

QueryUtils

public QueryUtils()

Method Detail

runSql

public static List<Object[]> runSql(Session session,
                    String sql)

Runs a SQL query against a session. The query is processed with an OgnlSqlFormat, so it can access values from the OGNL context.

Parameters:

session - the session

sql - the query string

Returns:

the results of the query as an Object[] (an array cell per column)

runSql

public static List<Object[]> runSql(Session session,
                    String queryString,
                    Object[] parameters)

Runs a SQL query against a session. The query can contain placeholders for the parameters, as supported by PreparedStatement.

Parameters:

session - the session

queryString - the query

parameters - parameters to substitute in the query

Returns:

the results of the query as an Object[] (an array cell per column)

getObjects

public static List<Object> getObjects(Session session,
                      TableCriteria criteria,
                      @Nullable
                      Integer firstResult,
                      @Nullable
                      Integer maxResults)

Runs a query, expressed as TableCriteria, against the database.

Parameters:

session - the session

criteria - the search criteria

firstResult - index of the first result to return

maxResults - maximum number of results to return

Returns:

at most maxResults results from the query

getObjects

public static List<Object> getObjects(Session session,
                      String queryString,
                      Object rootObject,
                      @Nullable
                      Integer firstResult,
                      @Nullable
                      Integer maxResults)

Runs a query against the database. The query is processed with an OgnlSqlFormat, so it can access values from the OGNL context, as well as from an (optional) root object.

Parameters:

session - the session

queryString - the query

rootObject - the root object passed to the ognl evaluator (can be null).

firstResult - index of the first result to return

maxResults - maximum number of results to return

Returns:

at most maxResults results from the query

getObjects

public static List<Object> getObjects(Session session,
                      String queryString,
                      @Nullable
                      Integer firstResult,
                      @Nullable
                      Integer maxResults)

Runs a query against the database. The query is processed with an OgnlSqlFormat, so it can access values from the OGNL context.

Parameters:

session - the session

queryString - the query

firstResult - index of the first result to return

maxResults - maximum number of results to return

Returns:

at most maxResults results from the query

getQueryStringWithParametersForCriteria

public static QueryStringWithParameters getQueryStringWithParametersForCriteria(TableCriteria criteria)

Tranforms a TableCriteria to a query string with an associated array of parameters.

Parameters:

criteria - the criteria.

Returns:

the same criteria encoded as a HQL query with parameters.

getQueryStringWithParametersForCriteria

public static QueryStringWithParameters getQueryStringWithParametersForCriteria(@Nullable
                                                                TableCriteria criteria,
                                                                @Nullable
                                                                String alias,
                                                                int initialParameterIndex)

Tranforms a TableCriteria to a query string with an associated array of parameters.

Parameters:

criteria - the criteria.

alias - the alias to use for the main entity.

Returns:

the same criteria encoded as a HQL query with parameters.

processTextMatchMode

protected static String processTextMatchMode(TextMatchMode textMatchMode,
                          String value)

getTableFromQueryString

public static Table getTableFromQueryString(Database database,
                            String queryString)

Extracts the name of the main entity from a HQL query string, i.e. the first entity in the from clause.

Parameters:

database - the database containing the table.

queryString - the query to analyze.

Returns:

the main entity selected by the query

getObjects

public static List<Object> getObjects(Session session,
                      String queryString,
                      TableCriteria criteria,
                      @Nullable
                      Object rootObject,
                      @Nullable
                      Integer firstResult,
                      @Nullable
                      Integer maxResults)

Runs a query against the database. The query is expressed as a TableCriteria object plus a query string to be merged with it (the typical case of a search in a crud defined by a query). The query string is processed with an OgnlSqlFormat, so it can access values from the OGNL context, as well as from an (optional) root object.

Parameters:

session - the session

queryString - the query

criteria - the search criteria to merge with the query.

rootObject - the root object passed to the ognl evaluator (can be null).

firstResult - index of the first result to return

maxResults - maximum number of results to return

Returns:

at most maxResults results from the query

mergeQuery

public static QueryStringWithParameters mergeQuery(String queryString,
                                   @Nullable
                                   TableCriteria criteria,
                                   Object rootObject)

Merges a HQL query string with a TableCriteria object representing a search. The query string is processed with an OgnlSqlFormat, so it can access values from the OGNL context, as well as from an (optional) root object.

Parameters:

queryString - the base query

criteria - the criteria to merge with the query

rootObject - the OGNL root object (can be null)

Returns:

the merged query

parseQuery

public static net.sf.jsqlparser.statement.select.PlainSelect parseQuery(net.sf.jsqlparser.parser.CCJSqlParserManager parserManager,
                                                        String query)
                                                                 throws net.sf.jsqlparser.JSQLParserException

Throws:

net.sf.jsqlparser.JSQLParserException

runHqlQuery

public static List<Object> runHqlQuery(Session session,
                       String queryString,
                       @Nullable
                       Object[] parameters)

Runs a HQL query against the database.

Parameters:

session - the session

queryString - the query

parameters - the query parameters

Returns:

the results of the query

See Also:

runHqlQuery(Session, String, Object[], Integer, Integer)

runHqlQuery

public static List<Object> runHqlQuery(Session session,
                       String queryString,
                       @Nullable
                       Object[] parameters,
                       @Nullable
                       Integer firstResult,
                       @Nullable
                       Integer maxResults)

Runs a HQL query against the database.

Parameters:

session - the session

queryString - the query

parameters - the query parameters

firstResult - index of the first result to return

maxResults - maximum number of results to return

Returns:

the results of the query

See Also:

runHqlQuery(Session, String, Object[], Integer, Integer)

getObjectByPk

public static Object getObjectByPk(Persistence persistence,
                   String database,
                   String entityName,
                   Serializable pk)

Loads an object by primary key.

Parameters:

persistence - the persistence object

database - the database (connection provider) to use

entityName - the name of the entity to load - usually the normalized (lowercased, etc.) table name.

pk - the primary key object. Might be an atomic value (String, Integer, etc.) for single-column primary keys, or a composite object for multi-column primary keys.

Returns:

the loaded object, or null if an object with that key does not exist.

getObjectByPk

public static Object getObjectByPk(Session session,
                   TableAccessor table,
                   Serializable pk)

Loads an object by primary key.

Parameters:

session - the Hibernate session

table - the table where to load the object from

pk - the primary key object. Might be an atomic value (String, Integer, etc.) for single-column primary keys, or a composite object for multi-column primary keys.

Returns:

the loaded object, or null if an object with that key does not exist.

getObjectByPk

public static Object getObjectByPk(Persistence persistence,
                   Table baseTable,
                   Serializable pkObject)

Loads an object by primary key.

Parameters:

persistence - the persistence object

baseTable - the table to query

pkObject - the primary key object.

Returns:

the loaded object, or null if an object with that key does not exist.

getObjectByPk

public static Object getObjectByPk(Persistence persistence,
                   Table baseTable,
                   Serializable pkObject,
                   String query,
                   Object rootObject)

Loads an object by primary key. It also verifies that the object falls within the results of a given query.

Parameters:

persistence - the persistence object

baseTable - the table to load from

pkObject - the primary key object

query - the query (where condition) that the object must fulfill

rootObject - the OGNL root object against which to evaluate the query string.

Returns:

the loaded object, or null if an object with that key does not exist or falls outside the query.

getObjectByPk

public static Object getObjectByPk(Persistence persistence,
                   String database,
                   String entityName,
                   Serializable pk,
                   String hqlQueryString,
                   Object rootObject)

Loads an object by primary key. It also verifies that the object falls within the results of a given query.

Parameters:

persistence - the persistence object

database - the database (connection provider)

entityName - the name of the entity to load

pk - the primary key object

hqlQueryString - the query (where condition) that the object must fulfill

rootObject - the OGNL root object against which to evaluate the query string.

Returns:

the loaded object, or null if an object with that key does not exist or falls outside the query.

getEntityAlias

protected static net.sf.jsqlparser.expression.Alias getEntityAlias(String entityName,
                                                net.sf.jsqlparser.statement.select.PlainSelect query)

commit

public static void commit(Persistence persistence,
          String databaseName)

Cleanly commits the current (for this thread) transaction of the given database.

Parameters:

persistence - the persistence object

databaseName - the name of the database (connection provider)

getRelatedObjects

public static List<Object> getRelatedObjects(Persistence persistence,
                             String databaseName,
                             String entityName,
                             Object obj,
                             String oneToManyRelationshipName)

Navigates a ...-to-many relationship returning the list of objects associated with a given entity.

Parameters:

persistence - the persistence object

databaseName - the name of the database (connection provider)

entityName - the type (entity name) of the master object

obj - the master object

oneToManyRelationshipName - the name of the relationship to navigate

Returns:

the list of associated objects