| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| */ |
| 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.UnexpectedReturnValueException; |
| import org.slf4j.Logger; |
| import org.slf4j.LoggerFactory; |
| |
| /** |
| * DBUtils |
| * This class provides various query functions and functions for command execution. |
| * It also performs logging |
| * @author doebele |
| */ |
| public class DBUtils implements DBContextAware |
| { |
| // Logger (Use logger from DBDatabase.class) |
| protected static final Logger log = LoggerFactory.getLogger(DBUtils.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; |
| // Log max String length |
| protected int LOG_MAX_STRING_LENGTH = 40; |
| // Log New-Line |
| protected String LOG_NEW_LINE = "\r\n"; |
| |
| // the context |
| protected final DBContext context; |
| // the dbms |
| protected final DBMSHandler dbms; |
| |
| /** |
| * DBUtils constructor |
| * @param context the database context |
| */ |
| public DBUtils(DBContext context) |
| { |
| this.context = context; |
| this.dbms = context.getDbms(); |
| } |
| |
| /** |
| * Returns the current Context |
| * @return |
| */ |
| @Override |
| public DBContext getContext() |
| { |
| return context; |
| } |
| |
| /** |
| * Get single parameter as string (for logging only) |
| * @param param the parameter |
| */ |
| protected String paramValueToString(Object param) |
| { |
| if (param==null) |
| return "NULL"; |
| DataType dataType = DataType.fromJavaType(param.getClass()); |
| if (dataType.isText()) |
| { // text handling |
| String str = param.toString(); |
| // limit length |
| if (str.length()>LOG_MAX_STRING_LENGTH) |
| { StringBuilder b = new StringBuilder(LOG_MAX_STRING_LENGTH+10); |
| b.append(str.substring(0, LOG_MAX_STRING_LENGTH)); |
| b.append("~("); |
| b.append(String.valueOf(str.length())); |
| b.append(")"); |
| str = b.toString(); |
| } |
| // make sure param does not contain the separator |
| if (str.indexOf('|')>=0) |
| str = str.replace('|', '?'); |
| // done |
| return str; |
| } |
| if (dataType==DataType.UNKNOWN || |
| dataType==DataType.BLOB || |
| dataType==DataType.CLOB) |
| { // get the class name |
| return param.getClass().getName(); |
| } |
| // just convert to String |
| return String.valueOf(param); |
| } |
| |
| /** |
| * Get all parameters as string (for logging only) |
| * @param params the parameter |
| */ |
| protected String paramsToString(Object[] params) |
| { |
| if (params == null || params.length < 1) |
| return null; // Empty |
| if (params.length > 1) |
| { // more than one |
| StringBuilder buf = new StringBuilder(); |
| for (int i = 0; i < params.length; i++) |
| { |
| if (i>0) |
| buf.append('|'); |
| // append |
| buf.append(paramValueToString(params[i])); |
| } |
| return buf.toString(); |
| } |
| // Only one parameter |
| return paramValueToString(params[0]); |
| } |
| |
| /** |
| * Log Query Statement |
| * @param sqlCmd the sql command |
| * @param sqlParams the command params |
| */ |
| protected void logQueryStatement(String sqlCmd, Object[] sqlParams) |
| { |
| if (log.isDebugEnabled()) |
| { // Log with or without parameters |
| if (sqlParams!=null && sqlParams.length>0) |
| log.debug("Executing DQL: {}{}{}Parameters: [{}]", LOG_NEW_LINE, sqlCmd, LOG_NEW_LINE, paramsToString(sqlParams)); |
| else |
| log.debug("Executing DQL: {}{}", LOG_NEW_LINE, sqlCmd); |
| } |
| } |
| |
| /** |
| * Log Update Statement |
| * @param sqlCmd the sql command |
| * @param sqlParams the command params |
| */ |
| protected void logUpdateStatement(String sqlCmd, Object[] sqlParams) |
| { |
| if (log.isInfoEnabled()) |
| { // Log with or without parameters |
| if (sqlParams!=null && sqlParams.length>0) |
| log.info("Executing DML: {}{}{}Parameters: [{}]", LOG_NEW_LINE, sqlCmd, LOG_NEW_LINE, paramsToString(sqlParams)); |
| else |
| log.info("Executing DML: {}{}", LOG_NEW_LINE, sqlCmd); |
| } |
| } |
| |
| /** |
| * 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 |
| logUpdateStatement(sqlCmd, sqlParams); |
| // 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 |
| logQueryStatement(sqlCmd, sqlParams); |
| // 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 failOnNoResult 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 failOnNoResult) |
| { |
| // Debug |
| logQueryStatement(sqlCmd, sqlParams); |
| // Read value |
| long start = System.currentTimeMillis(); |
| Object result = dbms.querySingleValue(sqlCmd, sqlParams, dataType, context.getConnection()); |
| if (result==ObjectUtils.NO_VALUE) |
| { // Query returned no result |
| if (failOnNoResult) |
| 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 resultType the expected data type |
| * @param failOnNoResult flag whether to fail on empty resultset |
| * |
| * @return the value of the first column in the first row of the query |
| */ |
| public final <T> T querySingleValue(DBCommandExpr cmd, Class<T> resultType, boolean failOnNoResult) |
| { |
| Object value = querySingleValue(cmd.getSelect(), cmd.getParamValues(), cmd.getDataType(), failOnNoResult); |
| return ObjectUtils.convert(resultType, value); |
| } |
| |
| /** |
| * 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 failOnNoResult flag whether to fail on empty resultset |
| * |
| * @return the value of the first column in the first row of the query |
| */ |
| public final Object querySingleValue(DBCommandExpr cmd, boolean failOnNoResult) |
| { |
| return querySingleValue(cmd.getSelect(), cmd.getParamValues(), cmd.getDataType(), failOnNoResult); |
| } |
| |
| /** |
| * 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, 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 sqlCmd the sql command |
| * @param sqlParams the command params |
| * @param defaultValue the default value |
| * |
| * @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 sqlCmd the sql command |
| * @param sqlParams the command params |
| * @param defaultValue the default value |
| * |
| * @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, subCmd.getParamValues(), 0); |
| } |
| // find any rowset |
| DBRowSet rs = exprList[0].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 <T> the type for the list |
| * @param c the class type for the list |
| * @param sqlCmd the sql command |
| * @param sqlParams the command params |
| * @param dataType the expected data type |
| * @param result the reusult colletion |
| * @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 |
| { |
| logQueryStatement(sqlCmd, sqlParams); |
| // Log performance |
| long start = System.currentTimeMillis(); |
| // 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(), cmd.getDataType(), 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. |
| * Optionally a third column may provide a boolean value for active or disabled. |
| * |
| * IMPORTANT: The query must contain unique values in the first column! |
| * |
| * @param sqlCmd the sql command |
| * @param sqlParams the command params |
| * @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 |
| { |
| logQueryStatement(sqlCmd, sqlParams); |
| // Debug |
| long start = System.currentTimeMillis(); |
| // Get the next Value |
| rs = dbms.executeQuery(sqlCmd, sqlParams, false, context.getConnection()); |
| if (rs == null) |
| throw new UnexpectedReturnValueException(rs, "dbms.executeQuery()"); |
| // Load options |
| int colCount = rs.getMetaData().getColumnCount(); |
| int count = 0; |
| while (rs.next()) |
| { |
| Object value = rs.getObject(1); |
| String text = rs.getString((colCount>=2) ? 2 : 1); |
| boolean active = (colCount>=3) ? ObjectUtils.getBoolean(rs.getObject(3)) : true; |
| options.append(value, text, active); |
| 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 command |
| * @param sqlParams the command params |
| * @param result the result colleciton |
| * @param maxRows the maximum number of rows |
| * @return the number of rows queried |
| */ |
| public int queryObjectList(String sqlCmd, Object[] sqlParams, Collection<Object[]> result, int maxRows) |
| { // Perform query |
| ResultSet rs = null; |
| try |
| { |
| logQueryStatement(sqlCmd, sqlParams); |
| // Log performance |
| long start = System.currentTimeMillis(); |
| // 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 cmd the command |
| * @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 <T extends DataListEntry> List<T> queryDataList(DBCommandExpr cmd, Class<T> entryClass, int first, int maxItems) |
| { |
| return queryDataList(cmd, createDefaultDataListFactory(entryClass, createDefaultDataListHead(cmd, entryClass)), first, maxItems); |
| } |
| |
| /** |
| * Queries a list of DataListEntry items |
| */ |
| public final List<DataListEntry> queryDataList(DBCommandExpr cmd) |
| { |
| return queryDataList(cmd, DataListEntry.class); |
| } |
| |
| /** |
| * Queries a list of DataListEntry items |
| */ |
| public final List<DataListEntry> queryDataList(DBCommandExpr cmd, int first, int maxItems) |
| { |
| return queryDataList(cmd, DataListEntry.class, first, maxItems); |
| } |
| |
| /** |
| * Queries a single DataListEntry item |
| * @param failOnNoResult flag whether to fail on empty resultset |
| */ |
| public final <T extends DataListEntry> T queryDataEntry(DBCommandExpr cmd, Class<T> entryClass, boolean failOnNoResult) |
| { |
| DataListHead head = createDefaultDataListHead(cmd, entryClass); |
| List<T> dle = queryDataList(cmd, createDefaultDataListFactory(entryClass, head), 0, 1); |
| if (dle.isEmpty()) |
| { if (failOnNoResult) |
| 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 cmd the command |
| * @param factory the factory for creating record objects |
| * @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.trace("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 keyColumns the key columns |
| * @param selectColumns the select columns |
| * @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.debug("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 cmd the command |
| * @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.debug("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 command |
| * @param factory the bean factory |
| * @param parent the parent object for the created beans (optional) |
| * @param first the first row |
| * @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 rowset the rowset |
| * @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 |
| * @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)); |
| } |
| } |
