org.apache.empire.db.DBUtils Maven / Gradle / Ivy
/*
* 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.
* We recommend to use a DBCommand object in order to build the sqlCmd.
*
* @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.
* This function returns a JDBC ResultSet.
* Instead of using this function directly you should use a DBReader object instead.
*
* @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 querySingleValue(DBCommandExpr cmd, Class 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 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 int querySimpleList(Class c, String sqlCmd, Object[] sqlParams, DataType dataType, Collection 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=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 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 int querySimpleList(Class c, DBCommandExpr cmd, Collection 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 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 List querySimpleList(Class c, DBCommandExpr cmd)
{ // Execute the Statement
List result = new ArrayList();
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