| package org.apache.empire.db; |
| |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.SQLIntegrityConstraintViolationException; |
| import java.util.ArrayList; |
| import java.util.Collection; |
| import java.util.List; |
| |
| import org.apache.empire.commons.ObjectUtils; |
| import org.apache.empire.commons.Options; |
| import org.apache.empire.commons.StringUtils; |
| import org.apache.empire.data.Column; |
| import org.apache.empire.data.DataType; |
| import org.apache.empire.data.list.DataListEntry; |
| import org.apache.empire.data.list.DataListFactory; |
| import org.apache.empire.data.list.DataListFactoryImpl; |
| import org.apache.empire.data.list.DataListHead; |
| import org.apache.empire.db.context.DBContextAware; |
| import org.apache.empire.db.exceptions.CommandWithoutSelectException; |
| import org.apache.empire.db.exceptions.ConstraintViolationException; |
| import org.apache.empire.db.exceptions.QueryFailedException; |
| import org.apache.empire.db.exceptions.QueryNoResultException; |
| import org.apache.empire.db.exceptions.StatementFailedException; |
| import org.apache.empire.db.exceptions.UnknownBeanTypeException; |
| import org.apache.empire.db.expr.compare.DBCompareExpr; |
| import org.apache.empire.db.list.DBBeanFactoryCache; |
| import org.apache.empire.db.list.DBBeanListFactory; |
| import org.apache.empire.db.list.DBBeanListFactoryImpl; |
| import org.apache.empire.db.list.DBRecordListFactory; |
| import org.apache.empire.db.list.DBRecordListFactoryImpl; |
| import org.apache.empire.db.list.DataBean; |
| import org.apache.empire.dbms.DBMSFeature; |
| import org.apache.empire.dbms.DBMSHandler; |
| import org.apache.empire.exceptions.InternalException; |
| import org.apache.empire.exceptions.InvalidArgumentException; |
| import org.apache.empire.exceptions.UnexpectedReturnValueException; |
| import org.slf4j.Logger; |
| import org.slf4j.LoggerFactory; |
| |
| public class DBUtils implements DBContextAware |
| { |
| // Logger (Use logger from DBDatabase.class) |
| protected static final Logger log = LoggerFactory.getLogger(DBDatabase.class); |
| |
| // Threshold for long running queries in milliseconds |
| protected long longRunndingStmtThreshold = 30000; |
| // Default list capacity |
| protected int DEFAULT_LIST_CAPACITY = 10; |
| // Max-Rows for list queries |
| protected int MAX_QUERY_ROWS = 999; |
| |
| // the context |
| protected final DBContext context; |
| // the dbms |
| protected final DBMSHandler dbms; |
| |
| /** |
| * Constructs an empty DBRecordSet object. |
| * @param useFieldIndexMap |
| */ |
| public DBUtils(DBContext context) |
| { |
| this.context = context; |
| this.dbms = context.getDbms(); |
| } |
| |
| /** |
| * Returns the current Context |
| * @return |
| */ |
| @Override |
| public DBContext getContext() |
| { |
| return context; |
| } |
| |
| /** |
| * Executes an update, insert or delete SQL-Statement.<BR> |
| * We recommend to use a DBCommand object in order to build the sqlCmd.<BR> |
| * <P> |
| * @param sqlCmd the SQL-Command |
| * @param sqlParams a list of objects to replace sql parameters |
| * @param setGenKeys callback to set the generated key for a each new record |
| * @return the row count for insert, update or delete or 0 for SQL statements that return nothing |
| */ |
| public int executeSQL(String sqlCmd, Object[] sqlParams, DBMSHandler.DBSetGenKeys setGenKeys) |
| { |
| try |
| { // Debug |
| if (log.isInfoEnabled()) |
| log.info("Executing: " + sqlCmd); |
| // execute SQL |
| long start = System.currentTimeMillis(); |
| int affected = dbms.executeSQL(sqlCmd, sqlParams, context.getConnection(), setGenKeys); |
| // number of affected records |
| if (affected < 0) |
| log.warn("Unexpected return value {} from dbms.executeSQL(\"{}\")", affected, sqlCmd); |
| // Log |
| long execTime = (System.currentTimeMillis() - start); |
| if (log.isInfoEnabled()) |
| log.info("executeSQL affected {} Records in {} ms ", affected, execTime); |
| else if (execTime>=longRunndingStmtThreshold) |
| log.warn("Long running statement took {} seconds for statement {}.", execTime / 1000, sqlCmd); |
| // Return number of affected records |
| return affected; |
| |
| } catch (SQLIntegrityConstraintViolationException sqle) { |
| // ConstraintViolation |
| throw new ConstraintViolationException(dbms, sqlCmd, sqle); |
| } catch (SQLException sqle) { |
| // Other error |
| throw new StatementFailedException(dbms, sqlCmd, sqle); |
| } |
| } |
| |
| /** |
| * Executes a select SQL-Statement and returns a ResultSet containing the query results.<BR> |
| * This function returns a JDBC ResultSet.<BR> |
| * Instead of using this function directly you should use a DBReader object instead.<BR> |
| * <P> |
| * @param sqlCmd the SQL-Command |
| * @param sqlParams a list of parameters for parameter queries (may depend on dbms) |
| * @param scrollable true if the reader should be scrollable or false if not |
| * @return the JDBC ResutSet |
| */ |
| public ResultSet executeQuery(String sqlCmd, Object[] sqlParams, boolean scrollable) |
| { |
| try |
| { // Debug |
| if (log.isDebugEnabled()) |
| log.debug("Executing: " + sqlCmd); |
| // Execute the Statement |
| long start = System.currentTimeMillis(); |
| ResultSet rs = dbms.executeQuery(sqlCmd, sqlParams, scrollable, context.getConnection()); |
| if (rs == null) |
| throw new UnexpectedReturnValueException(rs, "dbms.executeQuery()"); |
| // Debug |
| long queryTime = (System.currentTimeMillis() - start); |
| if (log.isDebugEnabled()) |
| log.debug("executeQuery successful in {} ms", queryTime); |
| else if (queryTime>=longRunndingStmtThreshold) |
| log.warn("Long running query took {} seconds for statement {}.", queryTime / 1000, sqlCmd); |
| // Return number of affected records |
| return rs; |
| |
| } catch (SQLException sqle) |
| { // Error |
| throw new QueryFailedException(dbms, sqlCmd, sqle); |
| } |
| } |
| |
| /** |
| * Returns the value of the first row/column of a sql-query as an object. |
| * If the query does not return a result the value ObjectUtils.NO_VALUE is returned. |
| * |
| * @param sqlCmd the SQL-Command |
| * @param sqlParams list of query parameter values |
| * @param dataType the expected data type |
| * @param forceResult if true a QueryNoResultException result is thrown if no record exists otherwise null is returned |
| * |
| * @return the value of the first column in the first row of the query |
| */ |
| public Object querySingleValue(String sqlCmd, Object[] sqlParams, DataType dataType, boolean forceResult) |
| { |
| // Debug |
| long start = System.currentTimeMillis(); |
| if (log.isDebugEnabled()) |
| log.debug("Executing: " + sqlCmd); |
| // Read value |
| Object result = dbms.querySingleValue(sqlCmd, sqlParams, dataType, context.getConnection()); |
| if (result==ObjectUtils.NO_VALUE) |
| { // Query returned no result |
| if (forceResult) |
| throw new QueryNoResultException(sqlCmd); |
| else |
| result = null; |
| } |
| // Debug |
| long queryTime = (System.currentTimeMillis() - start); |
| if (log.isDebugEnabled()) |
| log.debug("querySingleValue successful in {} ms. Result value={}.", queryTime, result); |
| else if (queryTime>=longRunndingStmtThreshold) |
| log.warn("Long running query took {} seconds for statement {}.", queryTime / 1000, sqlCmd); |
| // done |
| return result; |
| } |
| |
| /** |
| * Returns the value of the first row/column of a sql-query as an object. |
| * If the query does not return a result a QueryNoResultException is thrown |
| * |
| * @param cmd the Command object that contains the select statement |
| * @param dataType the expected data type |
| * |
| * @return the value of the first column in the first row of the query |
| */ |
| public final Object querySingleValue(DBCommandExpr cmd, DataType dataType, boolean forceResult) |
| { |
| return querySingleValue(cmd.getSelect(), cmd.getParamValues(), dataType, forceResult); |
| } |
| |
| /** |
| * Returns the value of the first row/column of a sql-query as an object. |
| * If the query does not return a result a QueryNoResultException is thrown |
| * |
| * @param cmd the Command object that contains the select statement |
| * |
| * @return the value of the first column in the first row of the query |
| */ |
| public final Object querySingleValue(DBCommandExpr cmd, boolean forceResult) |
| { |
| return querySingleValue(cmd, DataType.UNKNOWN, forceResult); |
| } |
| |
| /** |
| * Returns the value of the first row/column of a sql-query as an object. |
| * If the query does not return a result a QueryNoResultException is thrown |
| * |
| * @param cmd the Command object that contains the select statement |
| * |
| * @return the value of the first column in the first row of the query |
| */ |
| public final Object querySingleValue(DBCommandExpr cmd) |
| { |
| return querySingleValue(cmd, DataType.UNKNOWN, true); |
| } |
| |
| /** |
| * Returns the value of the first row/column of a sql-query as an int. |
| * If the query does not return a result or if the query result is NULL, then the defaultValue is returned |
| * |
| * @param cmd the Command object that contains the select statement |
| * @param defaultValue the default value if no value was returned by the database |
| * |
| * @return the result as a int value |
| */ |
| public final int querySingleInt(String sqlCmd, Object[] sqlParams, int defaultValue) |
| { |
| Object value = querySingleValue(sqlCmd, sqlParams, DataType.INTEGER, false); |
| return ObjectUtils.getInteger(value, defaultValue); |
| } |
| |
| /** |
| * Returns the value of the first row/column of a sql-query as an int. |
| * If the query does not return a result or if the query result is NULL, then the defaultValue is returned |
| * |
| * @param cmd the Command object that contains the select statement |
| * @param defaultValue the default value if no value was returned by the database |
| * |
| * @return the result as a int value |
| */ |
| public final int querySingleInt(DBCommandExpr cmd, int defaultValue) |
| { |
| Object value = querySingleValue(cmd.getSelect(), cmd.getParamValues(), DataType.INTEGER, false); |
| return ObjectUtils.getInteger(value, defaultValue); |
| } |
| |
| /** |
| * Returns the value of the first row/column of a sql-query as an int. |
| * If the query does not return a result a QueryNoResultException is thrown |
| * |
| * @param cmd the Command object that contains the select statement |
| * |
| * @return the result as a int value |
| */ |
| public final int querySingleInt(DBCommandExpr cmd) |
| { |
| Object value = querySingleValue(cmd.getSelect(), cmd.getParamValues(), DataType.INTEGER, true); |
| return ObjectUtils.getInteger(value); |
| } |
| |
| /** |
| * Returns the value of the first row/column of a sql-query as an int. |
| * If the query does not return a result or if the query result is NULL, then the defaultValue is returned |
| * |
| * @param cmd the Command object that contains the select statement |
| * @param defaultValue the default value if no value was returned by the database |
| * |
| * @return the result as a int value |
| */ |
| public final long querySingleLong(String sqlCmd, Object[] sqlParams, long defaultValue) |
| { |
| Object value = querySingleValue(sqlCmd, sqlParams, DataType.INTEGER, false); |
| return ObjectUtils.getLong(value, defaultValue); |
| } |
| |
| /** |
| * Returns the value of the first row/column of a sql-query as a long. |
| * If the query does not return a result or if the query result is NULL, then the defaultValue is returned |
| * |
| * @param cmd the Command object that contains the select statement |
| * @param defaultValue the default value |
| * |
| * @return the result as a long value |
| */ |
| public final long querySingleLong(DBCommandExpr cmd, long defaultValue) |
| { |
| Object value = querySingleValue(cmd.getSelect(), cmd.getParamValues(), DataType.INTEGER, false); |
| return ObjectUtils.getLong(value, defaultValue); |
| } |
| |
| /** |
| * Returns the value of the first row/column of a sql-query as a long. |
| * If the query does not return a result a QueryNoResultException is thrown |
| * |
| * @param cmd the Command object that contains the select statement |
| * |
| * @return the result as a long value |
| */ |
| public final long querySingleLong(DBCommandExpr cmd) |
| { |
| Object value = querySingleValue(cmd.getSelect(), cmd.getParamValues(), DataType.INTEGER, true); |
| return ObjectUtils.getLong(value); |
| } |
| |
| /** |
| * Returns the value of the first row/column of a sql-query as a string. |
| * If the query does not return a result or if the query result is NULL, then the defaultValue is returned |
| * |
| * @param cmd the Command object that contains the select statement |
| * @param defaultValue the default value if no value was returned by the database |
| * |
| * @return the result as a String object, if no result a empty String |
| */ |
| public final String querySingleString(DBCommandExpr cmd, String defaultValue) |
| { |
| Object value = querySingleValue(cmd.getSelect(), cmd.getParamValues(), DataType.VARCHAR, false); |
| return StringUtils.toString(value, defaultValue); |
| } |
| |
| /** |
| * Returns the value of the first row/column of a sql-query as a string. |
| * If the query does not return a result a QueryNoResultException is thrown. |
| * If the query result is NULL an empty string is returned. |
| * |
| * @param cmd the Command object that contains the select statement |
| * |
| * @return the result as a String object, if no result a empty String |
| */ |
| public final String querySingleString(DBCommandExpr cmd) |
| { |
| Object value = querySingleValue(cmd.getSelect(), cmd.getParamValues(), DataType.VARCHAR, true); |
| return StringUtils.toString(value); |
| } |
| |
| /** |
| * Returns the number of rows returned by executing the select statement |
| * @param cmd the select command |
| * @return the number of rows that will be returned |
| */ |
| public int queryRowCount(DBCommand cmd) |
| { // execute Native Query |
| if (cmd==null || !cmd.isValid()) |
| return 0; |
| // Check for aggregation |
| boolean aggregate = false; |
| DBColumnExpr[] exprList = cmd.getSelectExprList(); |
| for (int i=0; i<exprList.length; i++) |
| { |
| if (exprList[i].isAggregate()) |
| { aggregate = true; |
| break; |
| } |
| } |
| // check if aggregate |
| if (aggregate) |
| { // For Aggregations: Wrap |
| DBCommand subCmd = cmd.clone(); |
| subCmd.clearOrderBy(); |
| String sql = "SELECT COUNT(*) FROM ("+subCmd.getSelect() + ") q"; |
| return querySingleInt(sql, null, 0); |
| } |
| // find any rowset |
| DBRowSet rs = exprList[0].getSourceColumn().getRowSet(); |
| // create the count command |
| DBCommand countCmd = cmd.clone(); |
| countCmd.clearSelect(); |
| countCmd.clearOrderBy(); |
| countCmd.select(rs.count()); |
| // perform query |
| return querySingleInt(countCmd); |
| } |
| |
| /** |
| * Adds the first column of a query result to a collection. |
| * If the query has no result, an empty list is returned. |
| * |
| * @param c the class type for the list |
| * @param <T> the type for the list |
| * @param sqlCmd the SQL statement |
| * @param dataType the expected data type |
| * @param maxRows maximum number of rows or -1 for all rows |
| * |
| * @return the number of elements that have been added to the collection |
| */ |
| public <T> int querySimpleList(Class<T> c, String sqlCmd, Object[] sqlParams, DataType dataType, Collection<T> result, int maxRows) |
| { // Start query |
| ResultSet rs = null; |
| try |
| { // Log performance |
| long start = System.currentTimeMillis(); |
| if (log.isDebugEnabled()) |
| log.debug("Executing: " + sqlCmd); |
| // Get the next Value |
| rs = dbms.executeQuery(sqlCmd, sqlParams, false, context.getConnection()); |
| if (rs == null) |
| throw new UnexpectedReturnValueException(rs, "dbms.executeQuery()"); |
| // Check Result |
| int count=0; |
| while (rs.next() && (maxRows<0 || count<maxRows)) |
| { |
| T item = ObjectUtils.convert(c, dbms.getResultValue(rs, 1, dataType)); |
| result.add(item); |
| count++; |
| } |
| // Debug |
| long queryTime = (System.currentTimeMillis() - start); |
| if (log.isDebugEnabled()) |
| log.debug("querySimpleList retured {} items in {} ms.", count, queryTime); |
| else if (queryTime>=longRunndingStmtThreshold) |
| log.warn("Long running query took {} seconds for statement {}.", queryTime / 1000, sqlCmd); |
| // done |
| return count; |
| } catch (ClassCastException e) |
| { log.error("querySingleValue cast exception: ", e); |
| throw new InternalException(e); |
| } catch (SQLException sqle) |
| { // Error |
| throw new QueryFailedException(dbms, sqlCmd, sqle); |
| } finally |
| { // Cleanup |
| dbms.closeResultSet(rs); |
| } |
| } |
| |
| /** |
| * Adds the first column of a query result to a collection. |
| * If the query has no result, an empty list is returned. |
| * |
| * @param c the class type for the list |
| * @param <T> the type for the list |
| * @param cmd the Command object that contains the select statement |
| * |
| * @return the number of elements that have been added to the collection |
| */ |
| public final <T> int querySimpleList(Class<T> c, DBCommandExpr cmd, Collection<T> result) |
| { |
| return querySimpleList(c, cmd.getSelect(), cmd.getParamValues(), DataType.UNKNOWN, result, MAX_QUERY_ROWS); |
| } |
| |
| /** |
| * Returns a one dimensional array from an sql query. |
| * The array is filled with the values of the first column. |
| * |
| * @param c the class type for the list |
| * @param <T> the type for the list |
| * @param cmd the Command object that contains the select statement |
| * |
| * @return a list of the values of the first column of an sql query |
| */ |
| public final <T> List<T> querySimpleList(Class<T> c, DBCommandExpr cmd) |
| { // Execute the Statement |
| List<T> result = new ArrayList<T>(); |
| if (querySimpleList(c, cmd, result)<0) |
| return null; |
| return result; |
| } |
| |
| /** |
| * Returns a one dimensional array from an sql query. |
| * The array is filled with the values of the first column. |
| * |
| * @param cmd the Command object that contains the select statement |
| * @return a list of values of type Object |
| */ |
| public final List<Object> querySimpleList(DBCommandExpr cmd) |
| { // Execute the Statement |
| return querySimpleList(Object.class, cmd); |
| } |
| |
| /** |
| * Fills an option list provided with the result from a query. |
| * The option list is filled with the values of the first and second column. |
| * |
| * @param sqlCmd the SQL statement |
| * @param options the option list to where the options are added |
| * @return an Options object containing a set a of values and their corresponding names |
| */ |
| public int queryOptionList(String sqlCmd, Object[] sqlParams, Options options) |
| { // Execute the Statement |
| ResultSet rs = null; |
| try |
| { // Debug |
| long start = System.currentTimeMillis(); |
| if (log.isDebugEnabled()) |
| log.debug("Executing: " + sqlCmd); |
| // Get the next Value |
| rs = dbms.executeQuery(sqlCmd, sqlParams, false, context.getConnection()); |
| if (rs == null) |
| throw new UnexpectedReturnValueException(rs, "dbms.executeQuery()"); |
| if (rs.getMetaData().getColumnCount()<2) |
| throw new InvalidArgumentException("sqlCmd", sqlCmd); |
| // Check Result |
| int count = 0; |
| while (rs.next()) |
| { |
| Object value = rs.getObject(1); |
| String text = rs.getString(2); |
| options.add(value, text, true); |
| count++; |
| } |
| // Debug |
| long queryTime = (System.currentTimeMillis() - start); |
| if (log.isDebugEnabled()) |
| log.debug("queryOptionList retured {} items in {} ms.", count, queryTime); |
| else if (queryTime>=longRunndingStmtThreshold) |
| log.warn("Long running query took {} seconds for statement {}.", queryTime / 1000, sqlCmd); |
| // done |
| return count; |
| } catch (SQLException sqle) |
| { // Error |
| throw new QueryFailedException(dbms, sqlCmd, sqle); |
| } finally |
| { // Cleanup |
| dbms.closeResultSet(rs); |
| } |
| } |
| |
| /** |
| * Fills an option list provided with the result from a query. |
| * The option list is filled with the values of the first and second column. |
| * |
| * @param cmd the Command object that contains the select statement |
| * @param options the option list to where the options are added |
| * @return an Options object containing a set a of values and their corresponding names |
| */ |
| public final int queryOptionList(DBCommandExpr cmd, Options options) |
| { // Execute the Statement |
| return queryOptionList(cmd.getSelect(), cmd.getParamValues(), options); |
| } |
| |
| /** |
| * Returns a list of key value pairs from an sql query. |
| * The option list is filled with the values of the first and second column. |
| * |
| * @param cmd the Command object that contains the select statement |
| * @return an Options object containing a set a of values and their corresponding names |
| */ |
| public final Options queryOptionList(DBCommandExpr cmd) |
| { // Execute the Statement |
| Options options = new Options(); |
| queryOptionList(cmd.getSelect(), cmd.getParamValues(), options); |
| return options; |
| } |
| |
| /** |
| * Adds the result of a query to a given collection.<br> |
| * The individual rows will be added as an array of objects (object[]) |
| * <p>This function should only be used for small lists. |
| * Otherwise a DBReader should be used!</p> |
| * |
| * @param sqlCmd the SQL statement |
| * @return a list of object arrays |
| */ |
| public int queryObjectList(String sqlCmd, Object[] sqlParams, Collection<Object[]> result, int maxRows) |
| { // Perform query |
| ResultSet rs = null; |
| try |
| { // Log performance |
| long start = System.currentTimeMillis(); |
| if (log.isDebugEnabled()) |
| log.debug("Executing: " + sqlCmd); |
| // Get the next Value |
| rs = dbms.executeQuery(sqlCmd, sqlParams, false, context.getConnection()); |
| if (rs == null) |
| throw new UnexpectedReturnValueException(rs, "dbms.executeQuery()"); |
| // Read List |
| int colCount = rs.getMetaData().getColumnCount(); |
| int count = 0; |
| while (rs.next() && (maxRows<0 || count<maxRows)) |
| { // Read row |
| Object[] item = new Object[colCount]; |
| for (int i=0; i<colCount; i++) |
| { // Read from Resultset |
| item[i] = dbms.getResultValue(rs, i+1, DataType.UNKNOWN); |
| } |
| result.add(item); |
| count++; |
| } |
| // Debug |
| long queryTime = (System.currentTimeMillis() - start); |
| if (log.isDebugEnabled()) |
| log.debug("queryObjectList retured {} items in {} ms.", count, queryTime); |
| else if (queryTime>=longRunndingStmtThreshold) |
| log.warn("Long running query took {} seconds for statement {}.", queryTime / 1000, sqlCmd); |
| // done |
| return count; |
| } catch (SQLException sqle) |
| { // Error |
| throw new QueryFailedException(dbms, sqlCmd, sqle); |
| } finally |
| { // Cleanup |
| dbms.closeResultSet(rs); |
| } |
| } |
| |
| /** |
| * Returns the result of a query as a list Object-Arrays |
| * This function should only be used for small lists. |
| * |
| * @param cmd the Command object that contains the select statement |
| * @return a list of object arrays |
| */ |
| public final List<Object[]> queryObjectList(DBCommandExpr cmd) |
| { // Execute the Statement |
| List<Object[]> result = new ArrayList<Object[]>(); |
| queryObjectList(cmd.getSelect(), cmd.getParamValues(), result, MAX_QUERY_ROWS); |
| return result; |
| } |
| |
| /** |
| * Returns all values of the first row of a sql-query as an array. |
| * If the query does not return a result a QueryNoResultException is thrown |
| * |
| * @param sqlCmd the SQL-Command |
| * @param sqlParams list of query parameter values |
| * |
| * @return the values of the first row |
| */ |
| public Object[] querySingleRow(String sqlCmd, Object[] sqlParams) |
| { |
| List<Object[]> result = new ArrayList<Object[]>(1); |
| queryObjectList(sqlCmd, sqlParams, result, 1); |
| if (result.size()<1) |
| throw new QueryNoResultException(sqlCmd); |
| return result.get(0); |
| } |
| |
| /** |
| * Returns all values of the first row of a sql-query as an array. |
| * If the query does not return a result a QueryNoResultException is thrown |
| * |
| * @param cmd the Command object that contains the select statement |
| * |
| * @return the values of the first row |
| */ |
| public final Object[] querySingleRow(DBCommandExpr cmd) |
| { |
| return querySingleRow(cmd.getSelect(), cmd.getParamValues()); |
| } |
| |
| |
| /** |
| * Called to inform that the limit for DataList, Record and Bean queries has exceeded the maximum value |
| */ |
| protected void queryRowLimitExeeded() |
| { |
| log.warn("********************************************************"); |
| log.warn("Query Result was limited to {} by MAX_QUERY_ROWS", MAX_QUERY_ROWS); |
| log.warn("********************************************************"); |
| } |
| |
| /** |
| * Crates a default DataListFactory for a DataListEntry class |
| * The DataListEntry class must provide the following constructor |
| * DataListEntry(DataListFactory<? extends DataListEntry> head, int rownum, Object values[]) |
| * @param entryClass the entryClass for which to create the list head |
| * @return |
| */ |
| protected <T extends DataListEntry> DataListFactory<T> createDefaultDataListFactory(Class<T> entryClass, DataListHead head) |
| { |
| return new DataListFactoryImpl<T>(entryClass, head); |
| } |
| |
| /** |
| * Crates a default DataListHead for a DataListEntry class |
| * @param cmd the cmd for which to create the DataListHead |
| * @return the DataListHead instance |
| */ |
| protected DataListHead createDefaultDataListHead(DBCommandExpr cmd, Class<? extends DataListEntry> entryClass) |
| { |
| return new DataListHead(cmd.getSelectExprList()); |
| } |
| |
| /** |
| * Executes a query and returns a list of DataListEntry items |
| * @param sqlCmd the SQL-Command for the query |
| * @param factory the Factory to be used for each list item |
| * @param first the number of records to skip from the beginning of the result |
| * @param pageSize the maximum number of items to add to the list or -1 (default) for all |
| * @return the list |
| */ |
| public <T extends DataListEntry> List<T> queryDataList(DBCommandExpr cmd, DataListFactory<T> factory, int first, int pageSize) |
| { |
| List<T> list = null; |
| DBReader r = new DBReader(context); |
| try |
| { // prepare |
| factory.prepareQuery(cmd, context); |
| // check pageSize |
| if (pageSize==0) |
| { log.warn("PageSize must not be 0. Setting to -1 for all records!"); |
| pageSize = -1; |
| } |
| // set range |
| DBMSHandler dbms = context.getDbms(); |
| if (pageSize>0 && dbms.isSupported(DBMSFeature.QUERY_LIMIT_ROWS)) |
| { // let the database limit the rows |
| if (first>0 && dbms.isSupported(DBMSFeature.QUERY_SKIP_ROWS)) |
| { // let the database skip the rows |
| cmd.skipRows(first); |
| // no need to skip rows ourself |
| first = 0; |
| } |
| cmd.limitRows(first+pageSize); |
| } |
| // Runquery |
| r.open(cmd); |
| if (first>0) |
| { // skip rows |
| r.skipRows(first); |
| } |
| // Create a list of data entries |
| int maxCount = (pageSize>=0) ? pageSize : MAX_QUERY_ROWS; |
| list = factory.newList((pageSize>=0) ? pageSize : 10); |
| // add data |
| int rownum = 0; |
| while (r.moveNext() && maxCount != 0) |
| { // Create bean an init |
| T entry = factory.newEntry(rownum, r); |
| if (entry==null) |
| continue; |
| // add entry |
| list.add(entry); |
| rownum++; |
| // Decrease count |
| if (maxCount > 0) |
| maxCount--; |
| } |
| // check |
| if (rownum==MAX_QUERY_ROWS) |
| queryRowLimitExeeded(); |
| // done |
| return list; |
| } |
| finally |
| { // close reader |
| r.close(); |
| // complete |
| if (list!=null) |
| factory.completeQuery(list); |
| } |
| } |
| |
| /** |
| * Queries a list of DataListEntry items |
| */ |
| public final <T extends DataListEntry> List<T> queryDataList(DBCommandExpr cmd, Class<T> entryClass, DataListHead head) |
| { |
| return queryDataList(cmd, createDefaultDataListFactory(entryClass, head), 0, -1); |
| } |
| |
| /** |
| * Queries a list of DataListEntry items |
| */ |
| public final <T extends DataListEntry> List<T> queryDataList(DBCommandExpr cmd, Class<T> entryClass) |
| { |
| return queryDataList(cmd, entryClass, createDefaultDataListHead(cmd, entryClass)); |
| } |
| |
| /** |
| * Queries a list of DataListEntry items |
| */ |
| public final List<DataListEntry> queryDataList(DBCommandExpr cmd) |
| { |
| return queryDataList(cmd, DataListEntry.class); |
| } |
| |
| /** |
| * Queries a single DataListEntry item |
| */ |
| public final <T extends DataListEntry> T queryDataEntry(DBCommandExpr cmd, Class<T> entryClass, boolean forceResult) |
| { |
| DataListHead head = createDefaultDataListHead(cmd, entryClass); |
| List<T> dle = queryDataList(cmd, createDefaultDataListFactory(entryClass, head), 0, 1); |
| if (dle.isEmpty()) |
| { if (forceResult) |
| throw new QueryNoResultException(cmd.getSelect()); |
| return null; |
| } |
| return dle.get(0); |
| } |
| |
| /** |
| * Queries a single DataListEntry item |
| */ |
| public final <T extends DataListEntry> T queryDataEntry(DBCommandExpr cmd, Class<T> entryClass) |
| { |
| return queryDataEntry(cmd, entryClass, true); |
| } |
| |
| /** |
| * Queries a single DataListEntry item |
| */ |
| public final DataListEntry queryDataEntry(DBCommandExpr cmd) |
| { |
| return queryDataEntry(cmd, DataListEntry.class); |
| } |
| |
| /** |
| * Crates a default DBRecordListFactory for a DBRecord class |
| * The DBRecord class must provide the following constructor |
| * DBRecord(DBContext context, DBRowSet rowset) |
| * @param recordClass the recordClass for which to create the list head |
| * @return |
| */ |
| protected <R extends DBRecordBase> DBRecordListFactory<R> createDefaultRecordListFactory(Class<R> recordClass, DBRowSet rowset) |
| { |
| return new DBRecordListFactoryImpl<R>(recordClass, context.getClass(), rowset); |
| } |
| |
| /** |
| * Executes a query and returns a list of DBRecord items |
| * @param sqlCmd the SQL-Command for the query |
| * @param listHead the HeadInfo to be used for each list item |
| * @param first the number of records to skip from the beginning of the result |
| * @param pageSize the maximum number of items to add to the list or -1 (default) for all |
| * @return the list |
| */ |
| public <R extends DBRecordBase> List<R> queryRecordList(DBCommand cmd, DBRecordListFactory<R> factory, int first, int pageSize) |
| { |
| List<R> list = null; |
| DBReader r = new DBReader(context); |
| try |
| { // prepare |
| factory.prepareQuery(cmd, context); |
| // check pageSize |
| if (pageSize==0) |
| { log.warn("PageSize must not be 0. Setting to -1 for all records!"); |
| pageSize = -1; |
| } |
| // set range |
| DBMSHandler dbms = context.getDbms(); |
| if (pageSize>0 && dbms.isSupported(DBMSFeature.QUERY_LIMIT_ROWS)) |
| { // let the database limit the rows |
| if (first>0 && dbms.isSupported(DBMSFeature.QUERY_SKIP_ROWS)) |
| { // let the database skip the rows |
| cmd.skipRows(first); |
| // no need to skip rows ourself |
| first = 0; |
| } |
| cmd.limitRows(first+pageSize); |
| } |
| // Runquery |
| r.open(cmd); |
| if (first>0) |
| { // skip rows |
| r.skipRows(first); |
| } |
| // Create a list of data entries |
| int maxCount = (pageSize>=0) ? pageSize : MAX_QUERY_ROWS; |
| list = factory.newList((pageSize>=0) ? pageSize : DEFAULT_LIST_CAPACITY); |
| // add data |
| int rownum = 0; |
| while (r.moveNext() && maxCount != 0) |
| { // Create bean an init |
| R entry = factory.newRecord(rownum, r); |
| if (entry==null) |
| continue; |
| // check |
| if (entry.isValid()) |
| { // add entry |
| list.add(entry); |
| rownum++; |
| } |
| else |
| log.warn("Record {} is not valid thus it will not be added to the RecordListQuery.", rownum); |
| // Decrease count |
| if (maxCount > 0) |
| maxCount--; |
| } |
| // check |
| if (rownum==MAX_QUERY_ROWS) |
| queryRowLimitExeeded(); |
| // done |
| return list; |
| } |
| finally |
| { // close reader |
| r.close(); |
| // complete |
| if (list!=null) |
| factory.completeQuery(list); |
| } |
| } |
| |
| /** |
| * Executes a query and returns a list of DBRecord items |
| * @param cmd the command holding the constraints and order or the query |
| * @param rowset the rowset for which to query the records |
| * @return the list of DBRecord items |
| */ |
| public final <R extends DBRecordBase> List<R> queryRecordList(DBCommand cmd, DBRowSet rowset, Class<R> recordType) |
| { |
| @SuppressWarnings("cast") |
| DBRecordListFactory<R> factory = (DBRecordListFactory<R>)createDefaultRecordListFactory(recordType, rowset); |
| return queryRecordList(cmd, factory, 0, -1); |
| } |
| |
| /** |
| * Executes a query and returns a list of DBRecord items |
| * @param cmd the command holding the constraints and order or the query |
| * @param rowset the rowset for which to query the records |
| * @return the list of DBRecord items |
| */ |
| public final List<DBRecord> queryRecordList(DBCommand cmd, DBRowSet rowset) |
| { |
| return queryRecordList(cmd, rowset, DBRecord.class); |
| } |
| |
| /** |
| * Crates a default DBBeanListFactory for Java bean class |
| * The DBRecord class must provide |
| * either a standard construtor with correspondig property set fundtions |
| * or a constructor using the fields of the query |
| * @param beanType the beanType for which to create the list head |
| * @param constructorParams the columns to be used for the constructor (optional) |
| * @return the bean factory |
| */ |
| protected <T> DBBeanListFactory<T> createDefaultBeanListFactory(Class<T> beanType, Column[] keyColumns, List<? extends DBColumnExpr> selectColumns) |
| { |
| return new DBBeanListFactoryImpl<T>(beanType, keyColumns, selectColumns); |
| } |
| |
| /** |
| * gets or creates DBBeanListFactory for the given rowset |
| * @param beanType the beanType for which to create the list head |
| * @param rowset the rowset for which to return the factory |
| * @return the bean factory |
| */ |
| public synchronized <T> DBBeanListFactory<T> getRowsetBeanListFactory(Class<T> beanType, DBRowSet rowset) |
| { |
| DBBeanListFactory<T> factory = DBBeanFactoryCache.getFactoryForType(beanType); |
| if (factory==null) |
| { // Create default factory |
| log.info("No factory found for bean type '{}' and rowset {}. Creating default", beanType.getName(), rowset.getName()); |
| factory= createDefaultBeanListFactory(beanType, rowset.getKeyColumns(), rowset.getColumns()); |
| DBBeanFactoryCache.setFactoryForType(beanType, factory); |
| } |
| return factory; |
| } |
| |
| /** |
| * gets or creates DBBeanListFactory for the given rowset |
| * @param beanType the beanType for which to create the list head |
| * @param rowset the rowset for which to return the factory |
| * @return the bean factory |
| */ |
| public synchronized <T> DBBeanListFactory<T> getCommandBeanListFactory(Class<T> beanType, DBCommandExpr cmd) |
| { |
| DBBeanListFactory<T> factory = DBBeanFactoryCache.getFactoryForType(beanType); |
| if (factory==null) |
| { // Check command: Must have select! |
| if (!cmd.hasSelectExpr()) |
| throw new CommandWithoutSelectException(cmd); |
| // Create default factory |
| log.info("No factory found for bean type '{}'. Creating default", beanType.getName()); |
| factory= createDefaultBeanListFactory(beanType, null, cmd.getSelectExpressions()); |
| DBBeanFactoryCache.setFactoryForType(beanType, factory); |
| } |
| return factory; |
| } |
| |
| /** |
| * Query a list of simple Java objects (beans) |
| * @param cmd the comman |
| * @param type |
| * @param first |
| * @param pageSize the maximum number of items to add to the list or -1 (default) for all |
| * @return |
| */ |
| public <T> List<T> queryBeanList(DBCommandExpr cmd, DBBeanListFactory<T> factory, Object parent, int first, int pageSize) |
| { |
| List<T> list = null; |
| DBReader r = new DBReader(context); |
| try |
| { // prepare |
| factory.prepareQuery(cmd, context); |
| // check pageSize |
| if (pageSize==0) |
| { log.warn("PageSize must not be 0. Setting to -1 for all records!"); |
| pageSize = -1; |
| } |
| // set range |
| DBMSHandler dbms = context.getDbms(); |
| if (pageSize>0 && dbms.isSupported(DBMSFeature.QUERY_LIMIT_ROWS)) |
| { // let the database limit the rows |
| if (first>0 && dbms.isSupported(DBMSFeature.QUERY_SKIP_ROWS)) |
| { // let the database skip the rows |
| cmd.skipRows(first); |
| // no need to skip rows ourself |
| first = 0; |
| } |
| cmd.limitRows(first+pageSize); |
| } |
| // Runquery |
| r.open(cmd); |
| if (first>0) |
| { // skip rows |
| r.skipRows(first); |
| } |
| // Create a list of data entries |
| int maxCount = (pageSize>=0) ? pageSize : MAX_QUERY_ROWS; |
| list = factory.newList((pageSize>=0) ? pageSize : DEFAULT_LIST_CAPACITY); |
| // add data |
| int rownum = 0; |
| while (r.moveNext() && maxCount != 0) |
| { // Create bean an init |
| T item = factory.newItem(rownum, r); |
| if (item==null) |
| continue; |
| // add entry |
| list.add(item); |
| // post processing |
| if (item instanceof DataBean<?>) |
| ((DataBean<?>)item).initialize(((DBObject)r).getDatabase(), context, rownum, parent); |
| // next |
| rownum++; |
| // Decrease count |
| if (maxCount > 0) |
| maxCount--; |
| } |
| // check |
| if (rownum==MAX_QUERY_ROWS) |
| queryRowLimitExeeded(); |
| // done |
| return list; |
| } |
| finally |
| { |
| r.close(); |
| // complete |
| if (list!=null) |
| factory.completeQuery(list); |
| } |
| } |
| |
| /** |
| * Queries a list of Java beans for a given command |
| * @param cmd the query command |
| * @param factory the beanType factory used to instantiate the bean |
| * @param parent (optional) the parent bean if any |
| * @return the list of java beans |
| */ |
| public final <T> List<T> queryBeanList(DBCommandExpr cmd, DBBeanListFactory<T> factory, Object parent) |
| { |
| return queryBeanList(cmd, factory, parent, 0, -1); |
| } |
| |
| /** |
| * Queries a list of Java beans for a given command |
| * @param cmd the query command |
| * @param beanType the beanType |
| * @param constructorParams the list of params used for the bean constructor (optional, may be null) |
| * @param parent (optional) the parent bean if any |
| * @return the list of java beans |
| */ |
| public <T> List<T> queryBeanList(DBCommandExpr cmd, Class<T> beanType, DBRowSet rowset, Object parent) |
| { |
| return queryBeanList(cmd, getRowsetBeanListFactory(beanType, rowset), parent, 0, -1); |
| } |
| |
| /** |
| * Queries a list of Java beans for a given command |
| * @param cmd the query command |
| * @param beanType the beanType |
| * @param parent (optional) the parent bean if any |
| * @return the list of java beans |
| */ |
| public <T> List<T> queryBeanList(DBCommandExpr cmd, Class<T> beanType, Object parent) |
| { |
| return queryBeanList(cmd, getCommandBeanListFactory(beanType, cmd), parent); |
| } |
| |
| /** |
| * queries a single Java Bean for a given command |
| * @param cmd the query command |
| * @param factory the factory to create the bean instance |
| * @return the bean instance |
| */ |
| public <T> T queryBean(DBCommandExpr cmd, DBBeanListFactory<T> factory) |
| { |
| DBReader r = new DBReader(context); |
| try |
| { // prepare |
| factory.prepareQuery(cmd, context); |
| // Runquery |
| r.getRecordData(cmd); |
| // add data |
| T item = factory.newItem(-1, r); |
| // post processing |
| if (item instanceof DataBean<?>) |
| ((DataBean<?>)item).initialize(((DBObject)r).getDatabase(), context, -1, null); |
| // done |
| return item; |
| } |
| finally |
| { |
| r.close(); |
| // complete |
| factory.completeQuery(null); |
| } |
| } |
| |
| /** |
| * Queries a single Java Bean for a given command |
| * @param cmd the query command |
| * @param beanType the beanType |
| * @param parent (optional) the parent bean if any |
| * @return the list of java beans |
| */ |
| public <T> T queryBean(DBCommandExpr cmd, Class<T> beanType) |
| { |
| return queryBean(cmd, getCommandBeanListFactory(beanType, cmd)); |
| } |
| |
| /** |
| * Queries a single bean based on a where constraint |
| * @param beanType the beanType |
| * @param rowset the rowset used for the query |
| * @param whereConstraints the constraints for the query |
| * @return the entity bean |
| */ |
| public final <T> T queryBean(Class<T> beanType, DBRowSet rowset, DBCompareExpr whereConstraints) |
| { |
| DBObject.checkParamNull("rowset", rowset); |
| DBObject.checkParamNull("whereConstraints", whereConstraints); |
| // find |
| DBCommand cmd = context.createCommand(); |
| cmd.where(whereConstraints); |
| // use factory of rowset |
| return queryBean(cmd, getRowsetBeanListFactory(beanType, rowset)); |
| } |
| |
| /** |
| * Queries a single bean based on a where constraint |
| * @param beanType the beanType |
| * @param whereConstraints the constraints for the query |
| * @return the entity bean |
| */ |
| public final <T> T queryBean(Class<T> beanType, DBCompareExpr whereConstraints) |
| { |
| DBObject.checkParamNull("whereConstraints", whereConstraints); |
| // must have a factory |
| DBBeanListFactory<T> factory = DBBeanFactoryCache.getFactoryForType(beanType); |
| if (factory==null) |
| throw new UnknownBeanTypeException(beanType); |
| // add constraints |
| DBCommand cmd = context.createCommand(); |
| cmd.where(whereConstraints); |
| // query now |
| return queryBean(cmd, factory); |
| } |
| |
| /** |
| * Queries a single bean based on primary key values |
| * @param beanType the beanType |
| * @param rowset the rowset used for the query |
| * @param key the primary key |
| * @return the entity bean |
| */ |
| public final <T> T queryBean(Class<T> beanType, DBRowSet rowset, Object[] key) |
| { |
| DBObject.checkParamNull("rowset", rowset); |
| DBObject.checkParamNull("key", key); |
| // set key constraints |
| DBCommand cmd = context.createCommand(); |
| cmd.where(rowset.getKeyConstraints(key)); |
| // use factory of rowset |
| return queryBean(cmd, getRowsetBeanListFactory(beanType, rowset)); |
| } |
| } |