returnType) throws DBException
{
if (Util.isEmpty(sqlFunction, true))
{
throw new DBException("No Aggregate Function defined");
}
if (Util.isEmpty(sqlExpression, true))
{
if (AGGREGATE_COUNT == sqlFunction)
{
sqlExpression = "*";
}
else
{
throw new DBException("No Expression defined");
}
}
StringBuilder sqlSelect = new StringBuilder("SELECT ").append(sqlFunction).append("(")
.append(sqlExpression).append(")")
.append(" FROM ").append(table.getTableName());
T value = null;
T defaultValue = null;
String sql = buildSQL(sqlSelect, false);
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
pstmt = DB.prepareStatement(sql, this.trxName);
rs = createResultSet(pstmt);
if (rs.next())
{
if (returnType.isAssignableFrom(BigDecimal.class))
{
value = (T)rs.getBigDecimal(1);
defaultValue = (T)Env.ZERO;
}
else if (returnType.isAssignableFrom(Double.class))
{
value = (T)Double.valueOf(rs.getDouble(1));
defaultValue = (T)Double.valueOf(0.00);
}
else if (returnType.isAssignableFrom(Integer.class))
{
value = (T)Integer.valueOf(rs.getInt(1));
defaultValue = (T)Integer.valueOf(0);
}
else if (returnType.isAssignableFrom(Timestamp.class))
{
value = (T)rs.getTimestamp(1);
}
else if (returnType.isAssignableFrom(Boolean.class))
{
value = (T) Boolean.valueOf("Y".equals(rs.getString(1)));
defaultValue = (T) Boolean.FALSE;
}
else
{
value = (T)rs.getObject(1);
}
}
if (rs.next())
{
throw new DBException(Msg.getMsg(Env.getCtx(), "QueryMoreThanOneRecordsFound"));
}
}
catch (SQLException e)
{
throw new DBException(e, sql);
}
finally
{
DB.close(rs, pstmt);
rs = null; pstmt = null;
}
//
if (value == null)
{
value = defaultValue;
}
return value;
}
/**
* Count records that match query criteria
* @return count
* @throws DBException
*/
public int count() throws DBException
{
return aggregate("*", AGGREGATE_COUNT).intValue();
}
/**
* SUM sqlExpression for records that match query criteria
* @param sqlExpression
* @return sum
*/
public BigDecimal sum(String sqlExpression)
{
return aggregate(sqlExpression, AGGREGATE_SUM);
}
/**
* Check if there are any matching records for this query criteria
* @return true if exists, false otherwise
* @throws DBException
*/
public boolean match() throws DBException
{
String sql = buildSQL(new StringBuilder("SELECT 1 FROM ").append(table.getTableName()), false);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, this.trxName);
rs = createResultSet(pstmt);
if (rs.next())
return true;
}
catch (SQLException e) {
throw new DBException(e, sql);
}
finally {
DB.close(rs, pstmt);
}
return false;
}
/**
* Get an Stream implementation to fetch one PO at a time. This method will only create POs on-demand and
* they will become eligible for garbage collection once they have been consumed by the stream, so unlike
* {@link #list()} it doesn't have to hold a copy of all the POs in the result set in memory at one time.
*
* And unlike {#link #iterate()}, it only creates one ResultSet and iterates over it, creating a PO for each
* row. ({@link #iterate()}, on the other hand, has to re-run the query for each element).
*
* For situations where you need to iterate over a result set and operate on the results one-at-a-time rather
* than operate on the group as a whole, this method is likely to give better performance than list()
* or iterate().
*
* However, because it keeps the underlying {@code ResultSet} open, you need to make sure that the
* stream is properly disposed of using {@code close()} or else you will get resource leaks. As {@link Stream}
* extends {@link AutoCloseable}, you can use it in a try-with-resources statement to automatically close it when
* you are done.
*
* @return Stream of POs.
* @throws DBException
*/
public Stream stream() throws DBException
{
String sql = buildSQL(null, true);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement (sql, trxName);
final PreparedStatement finalPstmt = pstmt;
rs = createResultSet(pstmt);
final ResultSet finalRS = rs;
return StreamSupport.stream(new Spliterators.AbstractSpliterator(
Long.MAX_VALUE,Spliterator.ORDERED) {
@Override
public boolean tryAdvance(Consumer action) {
try {
if(!finalRS.next()) return false;
final T newRec = getPO(finalRS);
action.accept(newRec);
return true;
} catch(SQLException ex) {
log.log(Level.SEVERE, sql, ex);
throw new DBException(ex, sql);
}
}
}, false).onClose(() -> DB.close(finalRS, finalPstmt));
}
catch (SQLException e)
{
DB.close(rs, pstmt);
log.log(Level.SEVERE, sql, e);
throw new DBException(e, sql);
}
}
/**
* Get an Iterator implementation to fetch one PO at a time.
* The implementation first retrieve
* all IDS that match the query criteria and issue SQL query to fetch each PO when caller want to
* fetch the next PO.
* This minimize memory usage (at both application and DB server end) but it is slower than the list, stream and scroll method.
* @return Iterator
* @throws DBException
*/
public Iterator iterate() throws DBException
{
String[] keys = table.getKeyColumns();
StringBuilder sqlBuffer = new StringBuilder(" SELECT ");
for (int i = 0; i < keys.length; i++) {
if (i > 0)
sqlBuffer.append(", ");
if (!joinClauseList.isEmpty())
sqlBuffer.append(table.getTableName()).append(".");
sqlBuffer.append(keys[i]);
}
sqlBuffer.append(" FROM ").append(table.getTableName());
String sql = buildSQL(sqlBuffer, true);
PreparedStatement pstmt = null;
ResultSet rs = null;
List idList = new ArrayList();
try
{
pstmt = DB.prepareStatement (sql, trxName);
rs = createResultSet(pstmt);
while (rs.next ())
{
Object[] ids = new Object[keys.length];
for (int i = 0; i < ids.length; i++) {
ids[i] = rs.getObject(i+1);
}
idList.add(ids);
}
}
catch (SQLException e)
{
log.log(Level.SEVERE, sql, e);
throw new DBException(e, sql);
} finally {
DB.close(rs, pstmt);
rs = null; pstmt = null;
}
return new POIterator(table, idList, trxName);
}
/**
* Get a simple wrapper over a jdbc resultset.
* It is the caller responsibility to call the close method to release the underlying database resources.
* Since POResultSet implements the AutoCloseable interface, it is recommended to use it in a try-with-resources
* statement to automatically close it when you are done.
* @return POResultSet
* @throws DBException
*/
public POResultSet scroll() throws DBException
{
String sql = buildSQL(null, true);
PreparedStatement pstmt = null;
ResultSet rs = null;
POResultSet rsPO = null;
try
{
pstmt = DB.prepareStatement (sql, trxName);
rs = createResultSet(pstmt);
rsPO = new POResultSet(table, pstmt, rs, trxName);
rsPO.setCloseOnError(true);
if (selectColumns != null && selectColumns.length > 0)
{
rsPO.setSelectColumns(selectColumns);
}
return rsPO;
}
catch (SQLException e)
{
log.log(Level.SEVERE, sql, e);
throw new DBException(e, sql);
}
finally
{
// If there was an error, then close the statement and resultset
if (rsPO == null) {
DB.close(rs, pstmt);
rs = null; pstmt = null;
}
}
}
/**
* Build SQL SELECT statement.
* @param selectClause optional; if null the select statement will be built by {@link POInfo}
* @param useOrderByClause
* @return final SQL
*/
private final String buildSQL(StringBuilder selectClause, boolean useOrderByClause)
{
if (selectClause == null)
{
POInfo info = POInfo.getPOInfo(this.ctx, table.getAD_Table_ID(), trxName);
if (info == null)
{
throw new IllegalStateException("No POInfo found for AD_Table_ID="+table.getAD_Table_ID());
}
boolean isFullyQualified = !joinClauseList.isEmpty();
if (selectColumns != null && selectColumns.length > 0)
{
selectClause = info.buildSelectForColumns(isFullyQualified, selectColumns);
}
else
{
if(virtualColumns == null)
selectClause = info.buildSelect(isFullyQualified, noVirtualColumn);
else
selectClause = info.buildSelect(isFullyQualified, virtualColumns);
}
}
if (!joinClauseList.isEmpty())
{
for(String joinClause : joinClauseList)
{
selectClause.append(" ").append(joinClause);
}
}
StringBuilder whereBuffer = new StringBuilder();
if (!Util.isEmpty(this.whereClause, true))
{
if (whereBuffer.length() > 0)
whereBuffer.append(" AND ");
whereBuffer.append("(").append(this.whereClause).append(")");
}
if (this.onlyActiveRecords)
{
if (whereBuffer.length() > 0)
whereBuffer.append(" AND ");
if (!joinClauseList.isEmpty())
whereBuffer.append(table.getTableName()).append(".");
whereBuffer.append("IsActive=?");
}
if (this.onlyClient_ID) //red1
{
if (whereBuffer.length() > 0)
whereBuffer.append(" AND ");
if (!joinClauseList.isEmpty())
whereBuffer.append(table.getTableName()).append(".");
whereBuffer.append("AD_Client_ID=?");
}
if (this.onlySelection_ID > 0)
{
String[] keys = table.getKeyColumns();
if (keys.length != 1)
{
throw new DBException("Table "+table+" has 0 or more than 1 key columns");
}
//
if (whereBuffer.length() > 0)
whereBuffer.append(" AND ");
whereBuffer.append(" EXISTS (SELECT 1 FROM T_Selection s WHERE s.AD_PInstance_ID=? AND s.");
if (table.isUUIDKeyTable())
whereBuffer.append("T_Selection_UU=");
else
whereBuffer.append("T_Selection_ID=");
whereBuffer.append(table.getTableName()).append(".").append(keys[0]).append(")");
}
StringBuilder sqlBuffer = new StringBuilder(selectClause);
if (whereBuffer.length() > 0)
{
sqlBuffer.append(" WHERE ").append(whereBuffer);
}
if (useOrderByClause && !Util.isEmpty(orderBy, true))
{
sqlBuffer.append(" ORDER BY ").append(orderBy);
}
String sql = sqlBuffer.toString();
if (applyAccessFilter)
{
MRole role = MRole.getDefault(this.ctx, false);
sql = role.addAccessSQL(sql, table.getTableName(), applyAccessFilterFullyQualified, applyAccessFilterRW);
}
if (forUpdate) {
sql = sql + " FOR UPDATE";
if (DB.isPostgreSQL())
sql = sql + " OF " + table.getTableName();
}
// If have pagination
if (pageSize > 0 || recordsToSkip > 0) {
sql = appendPagination(sql);
}
if (log.isLoggable(Level.FINEST))
log.finest("TableName = " + table.getTableName() + "... SQL = " + sql); // red1 - to assist in debugging SQL
return sql;
}
/**
* Set the pagination of the query.
*
* @param pPageSize Limit number of query rows to return.
*/
public Query setPageSize(int pPageSize) {
this.pageSize = pPageSize;
return this;
}
/**
* Set the pagination of the query.
*
* @param pPageSize Limit number of rows to return.
* @param pPagesToSkip Number of pages to skipped on query run. ZERO for first page.
*/
public Query setPage(int pPageSize, int pPagesToSkip) {
if (pPageSize > 0) {
this.pageSize = pPageSize;
this.recordsToSkip = pPagesToSkip * pageSize;
} else {
log.warning("Wrong PageSize <= 0");
}
return this;
}
/**
* Set the number of records to skip (a.k.a. OFFSET)
*
* @param pRecordsToSkip Number of records to skip
*/
public Query setRecordstoSkip(int pRecordsToSkip) {
this.recordsToSkip = pRecordsToSkip;
return this;
}
/**
* Append pagination clause to pQuery
* @param pQuery SQL query statement
*/
private String appendPagination(String pQuery) {
String query = pQuery;
if (pageSize > 0 || recordsToSkip > 0) {
if (DB.getDatabase().isPagingSupported()) {
query = DB.getDatabase().addPagingSQL(query, recordsToSkip+1, pageSize <= 0 ? 0 : recordsToSkip + pageSize);
} else {
throw new IllegalArgumentException("Pagination not supported by database");
}
}
return query;
}
/**
* Create result set
* @param pstmt
* @return result set
* @throws SQLException
*/
private final ResultSet createResultSet (PreparedStatement pstmt) throws SQLException
{
DB.setParameters(pstmt, parameters);
int i = 1 + (parameters != null ? parameters.length : 0);
if (this.onlyActiveRecords)
{
DB.setParameter(pstmt, i++, true);
if (log.isLoggable(Level.FINEST)) log.finest("Parameter IsActive = Y");
}
if (this.onlyClient_ID)
{
int AD_Client_ID = Env.getAD_Client_ID(ctx);
DB.setParameter(pstmt, i++, AD_Client_ID);
if (log.isLoggable(Level.FINEST)) log.finest("Parameter AD_Client_ID = "+AD_Client_ID);
}
if (this.onlySelection_ID > 0)
{
DB.setParameter(pstmt, i++, this.onlySelection_ID);
if (log.isLoggable(Level.FINEST)) log.finest("Parameter Selection AD_PInstance_ID = "+this.onlySelection_ID);
}
if (queryTimeout > 0)
{
pstmt.setQueryTimeout(queryTimeout);
}
return pstmt.executeQuery();
}
/**
* Get all matching record IDs for this Query
* @return Array of matching record IDs
*/
public int[] getIDs ()
{
String[] keys = table.getKeyColumns();
if (keys.length != 1)
{
throw new DBException("Table "+table+" has 0 or more than 1 key columns");
}
StringBuilder selectClause = new StringBuilder("SELECT ");
if (!joinClauseList.isEmpty())
selectClause.append(table.getTableName()).append(".");
selectClause.append(keys[0]);
selectClause.append(" FROM ").append(table.getTableName());
String sql = buildSQL(selectClause, true);
ArrayList list = new ArrayList();
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
pstmt = DB.prepareStatement(sql, trxName);
rs = createResultSet(pstmt);
while (rs.next())
{
list.add(rs.getInt(1));
}
}
catch (SQLException e)
{
throw new DBException(e, sql);
}
finally
{
DB.close(rs, pstmt);
rs = null; pstmt = null;
}
// Convert to array
int[] retValue = list.stream().mapToInt(Integer::intValue).toArray();
return retValue;
} // get_IDs
/**
* Virtual columns to be included in the query.
* @param virtualColumns virtual column names
*/
public Query setVirtualColumns(String ... virtualColumns) {
this.virtualColumns = virtualColumns;
return this;
}
/**
* Set the columns to include in select query.
* Note that this doesn't effect {@link #iterate()}.
* @param columns
*/
public Query selectColumns(String ...columns) {
this.selectColumns = columns;
return this;
}
}