0001: /**
0002: * Copyright 2006 Webmedia Group Ltd.
0003: *
0004: * Licensed under the Apache License, Version 2.0 (the "License");
0005: * you may not use this file except in compliance with the License.
0006: * You may obtain a copy of the License at
0007: *
0008: * http://www.apache.org/licenses/LICENSE-2.0
0009: *
0010: * Unless required by applicable law or agreed to in writing, software
0011: * distributed under the License is distributed on an "AS IS" BASIS,
0012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0013: * See the License for the specific language governing permissions and
0014: * limitations under the License.
0015: **/package org.araneaframework.backend.list.helper;
0016:
0017: import java.sql.Connection;
0018: import java.sql.PreparedStatement;
0019: import java.sql.ResultSet;
0020: import java.sql.SQLException;
0021: import java.util.ArrayList;
0022: import java.util.Arrays;
0023: import java.util.Collection;
0024: import java.util.Iterator;
0025: import java.util.List;
0026:
0027: import javax.sql.DataSource;
0028:
0029: import org.apache.commons.logging.Log;
0030: import org.apache.commons.logging.LogFactory;
0031: import org.araneaframework.backend.list.SqlExpression;
0032: import org.araneaframework.backend.list.helper.builder.ValueConverter;
0033: import org.araneaframework.backend.list.helper.builder.compexpr.StandardCompExprToSqlExprBuilder;
0034: import org.araneaframework.backend.list.helper.builder.expression.StandardExpressionToSqlExprBuilder;
0035: import org.araneaframework.backend.list.helper.fields.ConcatFields;
0036: import org.araneaframework.backend.list.helper.fields.Fields;
0037: import org.araneaframework.backend.list.helper.naming.ColumnNameVariableResolver;
0038: import org.araneaframework.backend.list.helper.naming.NamingStrategy;
0039: import org.araneaframework.backend.list.helper.naming.OrNamingStrategy;
0040: import org.araneaframework.backend.list.helper.reader.DefaultResultSetColumnReader;
0041: import org.araneaframework.backend.list.helper.reader.ResultSetColumnReader;
0042: import org.araneaframework.backend.list.memorybased.ComparatorExpression;
0043: import org.araneaframework.backend.list.memorybased.Expression;
0044: import org.araneaframework.backend.list.memorybased.expression.VariableResolver;
0045: import org.araneaframework.backend.list.model.ListItemsData;
0046: import org.araneaframework.backend.list.model.ListQuery;
0047: import org.araneaframework.backend.list.sqlexpr.SqlCollectionExpression;
0048: import org.araneaframework.backend.list.sqlexpr.SqlExpressionUtil;
0049: import org.araneaframework.backend.list.sqlexpr.constant.SqlStringExpression;
0050: import org.araneaframework.backend.util.BeanMapper;
0051: import org.araneaframework.core.AraneaRuntimeException;
0052: import org.araneaframework.core.util.ExceptionUtil;
0053:
0054: /**
0055: * This class provides an SQL based implementation of the list. It takes care of
0056: * the filtering, ordering and returning data to the web components.
0057: * Implementations should override abstract methods noted in those methods.
0058: * <p>
0059: * Note, that all operations on items are made on the list of "processed", that
0060: * is ordered and filtered items.
0061: * <p>
0062: *
0063: * @author Jevgeni Kabanov (ekabanov <i>at</i> araneaframework <i>dot</i> org)
0064: * @author <a href="mailto:rein@araneaframework.org">Rein Raudjärv</a>
0065: *
0066: * @since 1.1
0067: */
0068: public abstract class BaseListSqlHelper {
0069:
0070: private static final Log log = LogFactory
0071: .getLog(BaseListSqlHelper.class);
0072:
0073: protected static final Long DEFAULT_RANGE_START = new Long(0);
0074:
0075: // *******************************************************************
0076: // FIELDS
0077: // *******************************************************************
0078:
0079: protected Fields fields;
0080: protected NamingStrategy namingStrategy;
0081: protected ValueConverter valueConverter;
0082: protected ResultSetColumnReader resultSetColumnReader;
0083:
0084: // FILTER AND ORDER
0085:
0086: protected Expression filterExpr;
0087: protected ComparatorExpression orderExpr;
0088: protected VariableResolver variableResolver;
0089:
0090: protected SqlExpression filterSqlExpr;
0091: protected SqlExpression orderSqlExpr;
0092:
0093: private boolean filterSqlExprInited = false;
0094: private boolean orderSqlExprInited = false;
0095:
0096: // ITEM RANGE
0097:
0098: protected Long itemRangeStart;
0099: protected Long itemRangeCount;
0100:
0101: // CONNECTION
0102:
0103: protected DataSource ds;
0104:
0105: // *********************************************************************
0106: // * CONSTRUCTORS
0107: // *********************************************************************
0108:
0109: /**
0110: * Creates <code>ListSqlHelper</code> without initializing any fields.
0111: */
0112: public BaseListSqlHelper() {
0113: this (null, null);
0114: }
0115:
0116: /**
0117: * Creates <code>ListSqlHelper</code> and provides it with the
0118: * <code>DataSource</code>.
0119: */
0120: public BaseListSqlHelper(DataSource dataSource) {
0121: this (dataSource, null);
0122: }
0123:
0124: /**
0125: * Creates <code>ListSqlHelper</code> initializing the appropriate fields.
0126: */
0127: public BaseListSqlHelper(ListQuery query) {
0128: this (null, query);
0129: }
0130:
0131: /**
0132: * Creates <code>ListSqlHelper</code> initializing the appropriate fields
0133: * and providing it with the <code>DataSource</code>.
0134: */
0135: public BaseListSqlHelper(DataSource dataSource, ListQuery query) {
0136: setDataSource(dataSource);
0137: setListQuery(query);
0138: init();
0139: }
0140:
0141: protected void init() {
0142: fields = new ConcatFields();
0143: namingStrategy = new OrNamingStrategy();
0144: valueConverter = null;
0145: resultSetColumnReader = DefaultResultSetColumnReader
0146: .getInstance();
0147: }
0148:
0149: // *********************************************************************
0150: // * PUBLIC METHODS
0151: // *********************************************************************
0152:
0153: /**
0154: * Sets the starting index and count of items in the range and
0155: * filtering and ordering expressions.
0156: */
0157: public void setListQuery(ListQuery query) {
0158: if (query != null) {
0159: setFilterExpression(query.getFilterExpression());
0160: setOrderExpression(query.getOrderExpression());
0161: setItemRangeStart(query.getItemRangeStart());
0162: setItemRangeCount(query.getItemRangeCount());
0163: }
0164: }
0165:
0166: /**
0167: * Sets the order expression saving it for later automatic SQL query
0168: * creation.
0169: *
0170: * @see #getDatabaseOrder()
0171: * @see #getDatabaseOrderWith(String, String)
0172: * @see #getDatabaseOrderParams()
0173: */
0174: public void setOrderExpression(ComparatorExpression orderExpr) {
0175: this .orderExpr = orderExpr;
0176: }
0177:
0178: /**
0179: * Sets the filter expression saving it for later automatic SQL query
0180: * creation.
0181: *
0182: * @see #getDatabaseFilter()
0183: * @see #getDatabaseFilterWith(String, String)
0184: * @see #getDatabaseFilterParams()
0185: */
0186: public void setFilterExpression(Expression filterExpr) {
0187: this .filterExpr = filterExpr;
0188: }
0189:
0190: /**
0191: * Sets the (0-based) starting index of the item range.
0192: */
0193: public void setItemRangeStart(Long itemRangeStart) {
0194: if (itemRangeStart == null) {
0195: itemRangeStart = DEFAULT_RANGE_START;
0196: }
0197: this .itemRangeStart = itemRangeStart;
0198: }
0199:
0200: /**
0201: * Sets the count of items in the range.
0202: */
0203: public void setItemRangeCount(Long itemRangeCount) {
0204: this .itemRangeCount = itemRangeCount;
0205: }
0206:
0207: // *********************************************************************
0208: // * DATABASE MAPPING AND CONVERTERS
0209: // *********************************************************************
0210:
0211: public Fields getFields() {
0212: return fields;
0213: }
0214:
0215: public void setFields(Fields fields) {
0216: this .fields = fields;
0217: }
0218:
0219: public NamingStrategy getNamingStrategy() {
0220: return namingStrategy;
0221: }
0222:
0223: public void setNamingStrategy(NamingStrategy namingStrategy) {
0224: this .namingStrategy = namingStrategy;
0225: }
0226:
0227: public void setValueConverter(ValueConverter valueConverter) {
0228: this .valueConverter = valueConverter;
0229: }
0230:
0231: public ValueConverter getValueConverter() {
0232: return valueConverter;
0233: }
0234:
0235: public void setResultSetColumnReader(
0236: ResultSetColumnReader resultSetColumnReader) {
0237: this .resultSetColumnReader = resultSetColumnReader;
0238: }
0239:
0240: public ResultSetColumnReader getResultSetColumnReader() {
0241: return resultSetColumnReader;
0242: }
0243:
0244: // *********************************************************************
0245: // * BUILDING SQL EXPRESSIONS ACCORDING TO ORDERING AND FILTERING
0246: // *********************************************************************
0247:
0248: /**
0249: * Returns the fields <code>SqlExpression</code>, which can be used in
0250: * "SELECT" clause.
0251: *
0252: * @return the fields <code>SqlExpression</code>, which can be used in
0253: * "SELECT" clause.
0254: */
0255: protected SqlExpression getFieldsSqlExpression() {
0256: SqlCollectionExpression result = new SqlCollectionExpression();
0257:
0258: if (fields.getNames().isEmpty()) {
0259: throw new IllegalStateException(
0260: "No fields defined for SELECT");
0261: }
0262:
0263: for (Iterator it = fields.getNames().iterator(); it.hasNext();) {
0264: String variable = (String) it.next();
0265: String dbField = namingStrategy.fieldToColumnName(variable);
0266: String dbAlias = namingStrategy
0267: .fieldToColumnAlias(variable);
0268:
0269: String sql;
0270: if (dbAlias.equals(dbField)) {
0271: sql = dbField;
0272: } else {
0273: sql = new StringBuffer(dbField).append(" ").append(
0274: dbAlias).toString();
0275: }
0276: result.add(new SqlStringExpression(sql));
0277: }
0278: return result;
0279: }
0280:
0281: /**
0282: * Returns the order <code>SqlExpression</code>, which can be used in
0283: * "ORDER BY" clause.
0284: *
0285: * @return the order <code>SqlExpression</code>, which can be used in
0286: * "ORDER BY" clause.
0287: */
0288: protected SqlExpression getOrderSqlExpression() {
0289: if (orderSqlExprInited) {
0290: return this .orderSqlExpr;
0291: }
0292:
0293: if (this .orderExpr != null) {
0294: StandardCompExprToSqlExprBuilder builder = createOrderSqlExpressionBuilder();
0295: builder.setMapper(createExpressionBuilderResolver());
0296: this .orderSqlExpr = SqlExpressionUtil.toSql(this .orderExpr,
0297: builder);
0298: }
0299:
0300: orderSqlExprInited = true;
0301: return this .orderSqlExpr;
0302: }
0303:
0304: /**
0305: * Returns the filter <code>SqlExpression</code>, which can be used in
0306: * "WHERE" clause.
0307: *
0308: * @return the filter <code>SqlExpression</code>, which can be used in
0309: * "WHERE" clause.
0310: */
0311: protected SqlExpression getFilterSqlExpression() {
0312: if (filterSqlExprInited) {
0313: return this .filterSqlExpr;
0314: }
0315:
0316: if (this .filterExpr != null) {
0317: StandardExpressionToSqlExprBuilder builder = createFilterSqlExpressionBuilder();
0318: builder.setMapper(createExpressionBuilderResolver());
0319: builder.setConverter(valueConverter);
0320: this .filterSqlExpr = SqlExpressionUtil.toSql(
0321: this .filterExpr, builder);
0322: }
0323:
0324: filterSqlExprInited = true;
0325: return this .filterSqlExpr;
0326: }
0327:
0328: /**
0329: * Creates new ordering SQL Expression builder.
0330: */
0331: protected StandardCompExprToSqlExprBuilder createOrderSqlExpressionBuilder() {
0332: return new StandardCompExprToSqlExprBuilder();
0333: }
0334:
0335: /**
0336: * Creates new filtering SQL Expression builder.
0337: */
0338: protected StandardExpressionToSqlExprBuilder createFilterSqlExpressionBuilder() {
0339: return new StandardExpressionToSqlExprBuilder();
0340: }
0341:
0342: /**
0343: * Returns the database fields list seperated by commas, which can be used
0344: * in "SELECT" clause.
0345: *
0346: * @return the database fields list seperated by commas, which can be used
0347: * in "SELECT" clause.
0348: */
0349: public String getDatabaseFields() {
0350: return getSqlString(getFieldsSqlExpression());
0351: }
0352:
0353: /**
0354: * Returns the filter database condition, which can be used in "WHERE"
0355: * clause.
0356: *
0357: * @return the filter database condition, which can be used in "WHERE"
0358: * clause.
0359: *
0360: * @see #getDatabaseFilterWith(String, String)
0361: * @see #getDatabaseFilterParams()
0362: */
0363: public String getDatabaseFilter() {
0364: return getSqlString(getFilterSqlExpression());
0365: }
0366:
0367: /**
0368: * Returns the database filter query with <code>prefix</code> added before and
0369: * <code>suffix</code> after it if the query is not empty.
0370: *
0371: * @param prefix Prefix added before the expression.
0372: * @param suffix Suffix added after the expression.
0373: *
0374: * @return the database filter query with <code>prefix</code> added before and
0375: * <code>suffix</code> after it if the query is not empty.
0376: *
0377: * @see #getDatabaseFilter()
0378: * @see #getDatabaseFilterParams()
0379: */
0380: public String getDatabaseFilterWith(String prefix, String suffix) {
0381: return this .filterExpr != null ? getSqlStringWith(
0382: getFilterSqlExpression(), prefix, suffix) : "";
0383: }
0384:
0385: /**
0386: * Returns the <code>List</code> of parameters that should be set in the
0387: * <code>PreparedStatement</code> that
0388: * belong to the filter database conditions.
0389: *
0390: * @return the <code>List</code> of parameters that should be set in the
0391: * <code>PreparedStatement</code> that
0392: * belong to the filter database conditions.
0393: *
0394: * @see #getDatabaseFilter()
0395: * @see #getDatabaseFilterWith(String, String)
0396: */
0397: public List getDatabaseFilterParams() {
0398: return getSqlParams(getFilterSqlExpression());
0399: }
0400:
0401: /**
0402: * Returns the order database representation, which can be used in "ORDER BY" clause.
0403: *
0404: * @return the order database representation, which can be used in "ORDER BY" clause.
0405: *
0406: * @see #getDatabaseOrderWith(String, String)
0407: * @see #getDatabaseOrderParams()
0408: */
0409: public String getDatabaseOrder() {
0410: return getSqlString(getOrderSqlExpression());
0411: }
0412:
0413: /**
0414: * Returns the database order query with <code>prefix</code> added before and
0415: * <code>suffix</code> after it if the query is not empty.
0416: *
0417: * @param prefix Prefix added before the expression.
0418: * @param suffix Suffix added after the expression.
0419: *
0420: * @return the database order query with <code>prefix</code> added before and
0421: * <code>suffix</code> after it if the query is not empty.
0422: *
0423: * @see #getDatabaseOrder()
0424: * @see #getDatabaseOrderParams()
0425: */
0426: public String getDatabaseOrderWith(String prefix, String suffix) {
0427: return this .orderExpr != null ? getSqlStringWith(
0428: getOrderSqlExpression(), prefix, suffix) : "";
0429: }
0430:
0431: /**
0432: * Returns the <code>List</code> of parameters that should be set in the
0433: * <code>PreparedStatement</code> that belong to the order database representation.
0434: *
0435: * @return the <code>List</code> of parameters that should be set in the
0436: * <code>PreparedStatement</code> that belong to the order database representation.
0437: *
0438: * @see #getDatabaseOrder()
0439: * @see #getDatabaseOrderWith(String, String)
0440: */
0441: public List getDatabaseOrderParams() {
0442: return getSqlParams(getOrderSqlExpression());
0443: }
0444:
0445: // *********************************************************************
0446: // * PREPARING DATABASE QUERIES
0447: // *********************************************************************
0448:
0449: /**
0450: * Sets the SQL query (with arguments) that will be used to retrieve the
0451: * item range from the list and count the items.
0452: * <p>
0453: * <code>ListQuery</code> filter and order conditions are used
0454: * automatically.
0455: * </p>
0456: * <p>
0457: * To use additional custom filter (and order) conditions,
0458: * use {@link #setSimpleSqlQuery(String, String, Object[])} or
0459: * {@link #setSimpleSqlQuery(String, String, Object[], String, Object[])}
0460: * method. To use more complex query, use {@link #setSqlQuery(String)}
0461: * method.
0462: * </p>
0463: * <p>
0464: * The constrcuted SQL query format is following
0465: * (LQ = <ocde>ListQuery</code>):<br/>
0466: * SELECT (fromSql) [WHERE (LQ filter conditions)]
0467: * [ORDER BY (LQ order conditions)]
0468: * </p>
0469: * Query arguments are automatically added in the appropriate order.
0470: *
0471: * @param fromSql FROM clause String.
0472: */
0473: public void setSimpleSqlQuery(String fromSql) {
0474: setSimpleSqlQuery(fromSql, null, null, null, null);
0475: }
0476:
0477: /**
0478: * Sets the SQL query (with arguments) that will be used to retrieve the
0479: * item range from the list and count the items.
0480: * <p>
0481: * <code>ListQuery</code> filter and order conditions are used automatically
0482: * and they must not be added to this metohd's arguments.
0483: * This method's Where arguments are only for additional
0484: * conditions that are not contained in <code>ListQuery</code> already.
0485: * </p>
0486: * <p>
0487: * In simpler cases, use {@link #setSimpleSqlQuery(String)} method.
0488: * To use also custom order by conditions, use
0489: * {@link #setSimpleSqlQuery(String, String, Object[], String, Object[])} method.
0490: * To use more complex query, use {@link #setSqlQuery(String)} method.
0491: * </p>
0492: * <p>
0493: * The constrcuted SQL query format is following
0494: * (LQ = <ocde>ListQuery</code>):<br/>
0495: * SELECT (fromSql) [WHERE (customWhereSql) AND (LQ filter conditions)]
0496: * [ORDER BY (customOrderbySql), (LQ order conditions)]
0497: * </p>
0498: * Query arguments are automatically added in the appropriate order.
0499: *
0500: * @param fromSql FROM clause String.
0501: * @param customWhereSql custom WHERE clause String.
0502: * @param customWhereArgs custom WHERE clause arguments.
0503: */
0504: public void setSimpleSqlQuery(String fromSql,
0505: String customWhereSql, Object[] customWhereArgs) {
0506: setSimpleSqlQuery(fromSql, customWhereSql, customWhereArgs,
0507: null, null);
0508: }
0509:
0510: /**
0511: * Sets the SQL query (with arguments) that will be used to retrieve the
0512: * item range from the list and count the items.
0513: * <p>
0514: * <code>ListQuery</code> filter and order conditions are used automatically
0515: * and they must not be added to this metohd's arguments.
0516: * This method's Where and Order by arguments are only for additional
0517: * conditions that are not contained in <code>ListQuery</code> already.
0518: * </p>
0519: * <p>
0520: * In simpler cases, use {@link #setSimpleSqlQuery(String)} or
0521: * {@link #setSimpleSqlQuery(String, String, Object[])} method.
0522: * To use more complex query, use {@link #setSqlQuery(String)} method.
0523: * </p>
0524: * <p>
0525: * The constrcuted SQL query format is following
0526: * (LQ = <ocde>ListQuery</code>):<br/>
0527: * SELECT (fromSql) [WHERE (customWhereSql) AND (LQ filter conditions)]
0528: * [ORDER BY (customOrderbySql), (LQ order conditions)]
0529: * </p>
0530: * Query arguments are automatically added in the appropriate order.
0531: *
0532: * @param fromSql FROM clause String.
0533: * @param customWhereSql custom WHERE clause String.
0534: * @param customWhereArgs custom WHERE clause arguments.
0535: * @param customOrderbySql custom ORDER BY clause String.
0536: * @param customOrderbyArgs custom ORDER BY clause arguments.
0537: */
0538: public void setSimpleSqlQuery(String fromSql,
0539: String customWhereSql, Object[] customWhereArgs,
0540: String customOrderbySql, Object[] customOrderbyArgs) {
0541:
0542: if (fromSql == null) {
0543: throw new IllegalArgumentException(
0544: "FROM SQL String must be specified");
0545: }
0546: if (customWhereSql == null && customWhereArgs != null) {
0547: throw new IllegalArgumentException(
0548: "WHERE SQL String and args must be both specified or null");
0549: }
0550: if (customOrderbySql == null && customOrderbyArgs != null) {
0551: throw new IllegalArgumentException(
0552: "ORDER BY SQL String and args must be both specified or null");
0553: }
0554:
0555: // SQL String
0556: StringBuffer sb = new StringBuffer("SELECT ");
0557: sb.append(getDatabaseFields());
0558: sb.append(" FROM ");
0559: sb.append(fromSql);
0560: if (customWhereSql == null) {
0561: sb.append(getDatabaseFilterWith(" WHERE ", ""));
0562: } else {
0563: sb.append(" WHERE (");
0564: sb.append(customWhereSql);
0565: sb.append(")");
0566: sb.append(getDatabaseFilterWith(" AND ", ""));
0567: }
0568: if (customOrderbySql == null) {
0569: sb.append(getDatabaseOrderWith(" ORDER BY ", ""));
0570: } else {
0571: sb.append(" ORDER BY ");
0572: sb.append(customOrderbySql);
0573: sb.append(getDatabaseOrderWith(", ", ""));
0574: }
0575: setSqlQuery(sb.toString());
0576:
0577: // SQL arguments
0578: if (customWhereArgs != null) {
0579: addStatementParams(Arrays.asList(customWhereArgs));
0580: }
0581: addStatementParams(getDatabaseFilterParams());
0582: if (customOrderbyArgs != null) {
0583: addStatementParams(Arrays.asList(customOrderbyArgs));
0584: }
0585: addStatementParams(getDatabaseOrderParams());
0586: }
0587:
0588: /**
0589: * Sets the SQL query that will be used to retrieve the item range from the
0590: * list and count the items. SQL query must start with SELECT.
0591: * All query arguments must be added additionally.
0592: * <p>
0593: * <code>ListQuery</code> filter and order conditions are not added
0594: * automatically. To add them, use <code>getDatabaseFilter*</code> and
0595: * <code>getDatabaseOrder*</code> methods.
0596: * </p>
0597: * <p>
0598: * For simpler cases, use
0599: * one of the <code>setSimpleSqlQuery</code> methods instead.
0600: * </p>
0601: *
0602: * @param sqlQuery
0603: * the SQL query that will be used to retrieve the item range
0604: * from the list and count the items.
0605: */
0606: public abstract void setSqlQuery(String sqlQuery);
0607:
0608: /**
0609: * Sets the SQL query used to count the items in the database. SQL query
0610: * must start with SELECT.
0611: * <p>
0612: * By default, total items count and items range queries are constructed
0613: * automatically based on the original query. This method should only be
0614: * used, if it can considerably boost the perfomacne of count query.
0615: * </p>
0616: *
0617: * @param countSqlQuery
0618: * the SQL query used to count the items in the database.
0619: */
0620: public abstract void setCountSqlQuery(String countSqlQuery);
0621:
0622: /**
0623: * Adds a <code>NULL</code> <code>PreparedStatement</code> parameter for
0624: * later setting.
0625: * <p>
0626: * This method should not be used with one of the
0627: * <code>setSimpleSqlQuery</code> methods.
0628: * </p>
0629: *
0630: * @param valueType
0631: * the type of the NULL value.
0632: */
0633: public abstract void addNullParam(int valueType);
0634:
0635: /**
0636: * Adds a <code>PreparedStatement</code> parameter for later setting.
0637: * <p>
0638: * This method should not be used with one of the
0639: * <code>setSimpleSqlQuery</code> methods.
0640: * </p>
0641: *
0642: * @param param
0643: * a <code>PreparedStatement</code> parameter.
0644: */
0645: public abstract void addStatementParam(Object param);
0646:
0647: /**
0648: * Adds <code>PreparedStatement</code> parameters for later setting.
0649: * <p>
0650: * This method should not be used with one of the
0651: * <code>setSimpleSqlQuery</code> methods.
0652: * </p>
0653: *
0654: * @param params
0655: * <code>PreparedStatement</code> parameters.
0656: */
0657: public abstract void addStatementParams(List params);
0658:
0659: /**
0660: * Returns the total count SQL query String and parameters.
0661: */
0662: protected abstract SqlStatement getCountSqlStatement();
0663:
0664: /**
0665: * Returns the itme range SQL query String and parameters.
0666: */
0667: protected abstract SqlStatement getRangeSqlStatement();
0668:
0669: // *********************************************************************
0670: // * EXECUTING SQL AND RETURING RESULTS
0671: // *********************************************************************
0672:
0673: /**
0674: * Stores the <code>DataSource</code>.
0675: */
0676: public void setDataSource(DataSource ds) {
0677: this .ds = ds;
0678: }
0679:
0680: /**
0681: * Execute a JDBC data access operation, implemented as callback action
0682: * working on a JDBC Connection.
0683: *
0684: * The stored <code>DataSource</code> is used to provide JDBC connection for
0685: * the action. The connection is always closed after the action.
0686: *
0687: * This method is used by all other <code>execute</code> methods in
0688: * <code>ListSqlHelper</code>. To override getting the connection, you
0689: * have to use one of the <code>ConnectionCallback</code> returning methods
0690: * and use your own implementation to execute it.
0691: *
0692: * @param action callback object that specifies the action.
0693: * @return a result object returned by the action, or null.
0694: */
0695: public Object execute(ConnectionCallback action) {
0696: if (this .ds == null) {
0697: throw new RuntimeException(
0698: "Please pass a DataSource to the ListSqlHelper!");
0699: }
0700:
0701: Connection con = null;
0702: try {
0703: con = ds.getConnection();
0704: return action.doInConnection(con);
0705: } catch (SQLException e) {
0706: throw ExceptionUtil.uncheckException(e);
0707: } finally {
0708: DbUtil.closeDbObjects(con, null, null);
0709: }
0710: }
0711:
0712: /**
0713: * Executes SQL queries that should retrieve 1) the total count of items in
0714: * the list and 2) a range of items from the list
0715: *
0716: * Provided <code>ResultReader</code> is used to convert the
0717: * <code>ResultSet</code> into a <code>List</code>.
0718: *
0719: * The stored <code>DataSource</code> is used to provide JDBC connection for
0720: * the action. The connection will be closed automatically.
0721: *
0722: * @param reader
0723: * <code>ResultSet</code> reader.
0724: * @return <code>ListItemsData</code> containing the item range and total
0725: * count.
0726: */
0727: public ListItemsData execute(ResultReader reader) {
0728: return (ListItemsData) execute(getListItemsDataCallback(reader));
0729: }
0730:
0731: /**
0732: * Executes SQL queries that should retrieve 1) the total count of items in
0733: * the list and 2) a range of items from the list
0734: *
0735: * <code>ListSqlHelper</code>'s <code>BeanResultReader</code> is used
0736: * to convert the <code>ResultSet</code> into a <code>List</code>.
0737: *
0738: * The stored <code>DataSource</code> is used to provide JDBC connection for
0739: * the action. The connection will be closed automatically.
0740: *
0741: * @param itemClass
0742: * Bean class.
0743: * @return <code>ListItemsData</code> containing the item range and total
0744: * count.
0745: */
0746: public ListItemsData execute(Class itemClass) {
0747: return (ListItemsData) execute(getListItemsDataCallback(createBeanResultReader(itemClass)));
0748: }
0749:
0750: /**
0751: * Executes a SQL query that should retrieve the total count of items in the
0752: * list.
0753: *
0754: * The stored <code>DataSource</code> is used to provide JDBC connection for
0755: * the action. The connection will be closed automatically.
0756: *
0757: * @return the total count of items in the list.
0758: */
0759: public Long executeCountSql() {
0760: return (Long) execute(getCountSqlCallback());
0761: }
0762:
0763: /**
0764: * Executes a SQL query that should retrieve a range of items from the
0765: * list.
0766: *
0767: * Provided <code>ResultReader</code> is used to convert the
0768: * <code>ResultSet</code> into a <code>List</code>.
0769: *
0770: * The stored <code>DataSource</code> is used to provide JDBC connection for
0771: * the action. The connection will be closed automatically.
0772: *
0773: * @param reader
0774: * <code>ResultSet</code> reader.
0775: * @return <code>List</code> containing the item range.
0776: */
0777: public List executeItemRangeSql(ResultReader reader) {
0778: return (List) execute(getItemRangeSqlCallback(reader));
0779: }
0780:
0781: /**
0782: * Executes a SQL query that should retrieve a range of items from the
0783: * list.
0784: *
0785: * <code>ListSqlHelper</code>'s <code>BeanResultReader</code> is used
0786: * to convert the <code>ResultSet</code> into a <code>List</code>.
0787: *
0788: * The stored <code>DataSource</code> is used to provide JDBC connection for
0789: * the action. The connection will be closed automatically.
0790: *
0791: * @param itemClass
0792: * Bean class.
0793: * @return <code>List</code> containing the item range.
0794: */
0795: public List executeItemRangeSql(Class itemClass) {
0796: return (List) execute(getItemRangeSqlCallback(createBeanResultReader(itemClass)));
0797: }
0798:
0799: // *********************************************************************
0800: // * CALLBACKS
0801: // *********************************************************************
0802:
0803: /**
0804: * Returns the total count and item ragne queries callback.
0805: *
0806: * In most cases, you should not use this method directly, instead
0807: * using one of the <code>execute</code> methods is recommended.
0808: */
0809: public ConnectionCallback getListItemsDataCallback(
0810: ResultReader reader) {
0811: return new ListItemsDataCallback(getCountSqlCallback(),
0812: getItemRangeSqlCallback(reader));
0813: }
0814:
0815: /**
0816: * Returns the total count query callback.
0817: *
0818: * In most cases, you should not use this method directly, instead
0819: * using one of the <code>execute</code> methods is recommended.
0820: */
0821: public ConnectionCallback getCountSqlCallback() {
0822: return new CountSqlCallback();
0823: }
0824:
0825: /**
0826: * Returns the item range query callback.
0827: *
0828: * In most cases, you should not use this method directly, instead
0829: * using one of the <code>execute</code> methods is recommended.
0830: */
0831: public ConnectionCallback getItemRangeSqlCallback(
0832: ResultReader reader) {
0833: return new ItemRangeSqlCallback(reader);
0834: }
0835:
0836: /**
0837: * The item range and total count querites callback that returns
0838: * <code>ListItemsData</code> object.
0839: *
0840: * @author <a href="mailto:rein@araneaframework.org">Rein RaudjƤrv</a>
0841: */
0842: public static class ListItemsDataCallback implements
0843: ConnectionCallback {
0844:
0845: protected ConnectionCallback countSqlCallback;
0846: protected ConnectionCallback itemRangeSqlCallback;
0847:
0848: /**
0849: * @param countSqlCallback total count query callback.
0850: * @param itemRangeSqlCallback item range query callback.
0851: */
0852: public ListItemsDataCallback(
0853: ConnectionCallback countSqlCallback,
0854: ConnectionCallback itemRangeSqlCallback) {
0855: this .countSqlCallback = countSqlCallback;
0856: this .itemRangeSqlCallback = itemRangeSqlCallback;
0857: }
0858:
0859: /**
0860: * Executes both queries, creates and returns <code>ListItemsData</code>
0861: * object containing results of both queries.
0862: *
0863: * @return the whole results as <code>ListItemsData</code> object.
0864: */
0865: public Object doInConnection(Connection con)
0866: throws SQLException {
0867: ListItemsData result = new ListItemsData();
0868: result.setTotalCount((Long) countSqlCallback
0869: .doInConnection(con));
0870: result.setItemRange((List) itemRangeSqlCallback
0871: .doInConnection(con));
0872: return result;
0873: }
0874: }
0875:
0876: /**
0877: * The total count query callback.
0878: *
0879: * @author <a href="mailto:rein@araneaframework.org">Rein RaudjƤrv</a>
0880: */
0881: public class CountSqlCallback implements ConnectionCallback {
0882: /**
0883: * Executes total count query and returns the result.
0884: *
0885: * @return the total count as <code>Long</code> object.
0886: */
0887: public Object doInConnection(Connection con)
0888: throws SQLException {
0889: PreparedStatement stmt = null;
0890: ResultSet rs = null;
0891: try {
0892: SqlStatement countSqlStatement = getCountSqlStatement();
0893:
0894: if (log.isDebugEnabled()) {
0895: log.debug("Counting database query: "
0896: + countSqlStatement.getQuery());
0897: log.debug("Counting statement parameters: "
0898: + countSqlStatement.getParams());
0899: }
0900:
0901: stmt = con.prepareStatement(countSqlStatement
0902: .getQuery());
0903: countSqlStatement.propagateStatementWithParams(stmt);
0904:
0905: try {
0906: rs = stmt.executeQuery();
0907: } catch (SQLException e) {
0908: throw createQueryFailedException(countSqlStatement
0909: .getQuery(), countSqlStatement.getParams(),
0910: e);
0911: }
0912:
0913: if (rs.next()) {
0914: return new Long(rs.getLong(1));
0915: }
0916: return null;
0917: } finally {
0918: DbUtil.closeDbObjects(null, stmt, rs);
0919: }
0920: }
0921: }
0922:
0923: /**
0924: * The itme range query callback that returns <code>List</code> of items.
0925: *
0926: * @author <a href="mailto:rein@araneaframework.org">Rein RaudjƤrv</a>
0927: */
0928: public class ItemRangeSqlCallback implements ConnectionCallback {
0929: protected ResultReader reader;
0930:
0931: /**
0932: * @param reader <code>ResultSet</code> reader that processes the data
0933: * and returns items as <code>List</code>.
0934: */
0935: public ItemRangeSqlCallback(ResultReader reader) {
0936: this .reader = reader;
0937: }
0938:
0939: /**
0940: * Executes the item range query and returns the reuslts.
0941: *
0942: * @return list items as <code>List</code> object.
0943: */
0944: public Object doInConnection(Connection con)
0945: throws SQLException {
0946: PreparedStatement stmt = null;
0947: ResultSet rs = null;
0948:
0949: try {
0950: SqlStatement rangeSqlStatement = getRangeSqlStatement();
0951:
0952: if (log.isDebugEnabled()) {
0953: log.debug("Item range database query: "
0954: + rangeSqlStatement.getQuery());
0955: log.debug("Item range statement parameters: "
0956: + rangeSqlStatement.getParams());
0957: }
0958:
0959: stmt = con.prepareStatement(rangeSqlStatement
0960: .getQuery());
0961: rangeSqlStatement.propagateStatementWithParams(stmt);
0962:
0963: try {
0964: rs = stmt.executeQuery();
0965: } catch (SQLException e) {
0966: throw createQueryFailedException(rangeSqlStatement
0967: .getQuery(), rangeSqlStatement.getParams(),
0968: e);
0969: }
0970:
0971: while (rs.next()) {
0972: reader.processRow(rs);
0973: }
0974: return reader.getResults();
0975: } finally {
0976: DbUtil.closeDbObjects(null, stmt, rs);
0977: }
0978: }
0979: }
0980:
0981: // *********************************************************************
0982: // * BEAN RESULT READER
0983: // *********************************************************************
0984:
0985: /**
0986: * Returns Bean <code>ResultSet</code> reader.
0987: *
0988: * In most cases, you should not use this method directly, instead
0989: * using one of the <code>execute</code> methods is recommended.
0990: */
0991: public ResultReader createBeanResultReader(Class itemClass) {
0992: return new BeanResultReader(itemClass);
0993: }
0994:
0995: /**
0996: * Resultset reader that uses <code>beanToResultSetMapping</code> in
0997: * <code>ListSqlHelper</code> to construct a given type of Bean list.
0998: *
0999: * @author Rein Raudjärv
1000: */
1001: public class BeanResultReader implements ResultReader {
1002:
1003: protected Class itemClass;
1004: protected List results;
1005: protected BeanMapper beanMapper;
1006:
1007: // For caching
1008: protected String[] fieldNames;
1009: protected Class[] fieldTypes;
1010: protected String[] columnNames;
1011:
1012: /**
1013: * @param itemClass Bean type.
1014: */
1015: public BeanResultReader(Class itemClass) {
1016: this .itemClass = itemClass;
1017: this .results = new ArrayList();
1018: this .beanMapper = new BeanMapper(itemClass, true);
1019: init();
1020: }
1021:
1022: /**
1023: * Cache all the field names, field types and column names to be used for each row.
1024: */
1025: public void init() {
1026: Collection names = fields.getResultSetNames();
1027: int count = names.size();
1028: fieldNames = new String[count];
1029: fieldTypes = new Class[count];
1030: columnNames = new String[count];
1031:
1032: int i = 0;
1033: for (Iterator it = names.iterator(); it.hasNext(); i++) {
1034: String fieldName = (String) it.next();
1035:
1036: // Check get-method
1037: if (!this .beanMapper.isWritable(fieldName))
1038: throw new RuntimeException(
1039: "Bean of type '"
1040: + itemClass.getName()
1041: + "' does not have accessible setter corresponding to field '"
1042: + fieldName + "'");
1043:
1044: fieldNames[i] = fieldName;
1045: fieldTypes[i] = beanMapper.getFieldType(fieldName);
1046: columnNames[i] = namingStrategy
1047: .fieldToColumnAlias(fieldName);
1048: }
1049: }
1050:
1051: /**
1052: * Processes <code>ResultSet</code> row passing it with the
1053: * new Bean instance to {@link #readBeanFields(ResultSet, Object)}
1054: * method.
1055: */
1056: public void processRow(ResultSet rs) {
1057: Object record = createBean();
1058: readBeanFields(rs, record);
1059: this .results.add(record);
1060: }
1061:
1062: /**
1063: * @return new Bean instance.
1064: */
1065: protected Object createBean() {
1066: try {
1067: return itemClass.newInstance();
1068: } catch (Exception e) {
1069: throw ExceptionUtil.uncheckException(e);
1070: }
1071: }
1072:
1073: /**
1074: * Reads the bean from <code>ResultSet</code>. Implementations
1075: * may override it to read beans in a custom way.
1076: *
1077: * @param rs
1078: * <code>ResultSet</code> containing the results of database
1079: * query.
1080: * @param bean
1081: * bean to read.
1082: */
1083: protected void readBeanFields(ResultSet rs, Object bean) {
1084: for (int i = 0; i < fieldNames.length; i++) {
1085: readBeanField(rs, columnNames[i], bean, fieldNames[i],
1086: fieldTypes[i]);
1087: }
1088: }
1089:
1090: /**
1091: * Reads the bean field from <code>ResultSet</code>.
1092: * Implementations may override it to read bean fields in a custom
1093: * way. A usual situation would be when a bean field is read from
1094: * more than one <code>ResultSet</code> field.
1095: *
1096: * @param rs <code>ResultSet</code> containing the results of database
1097: * query.
1098: * @param rsColumn name of the result set column to read from.
1099: * @param bean bean to read.
1100: * @param beanField name of the bean field to read to.
1101: * @param fieldType type of the bean field.
1102: */
1103: protected void readBeanField(ResultSet rs, String rsColumn,
1104: Object bean, String beanField, Class fieldType) {
1105: Object value = resultSetColumnReader.readFromResultSet(
1106: rsColumn, rs, fieldType);
1107: this .beanMapper.setFieldValue(bean, beanField, value);
1108: }
1109:
1110: /**
1111: * Returns the results.
1112: */
1113: public List getResults() {
1114: return this .results;
1115: }
1116: }
1117:
1118: // *********************************************************************
1119: // * HELPER METHODS
1120: // *********************************************************************
1121:
1122: /**
1123: * Creates the VariableResolver for SqlExpressionBuilder that converts
1124: * Variable names to their Database Field names according to the naming strategy.
1125: *
1126: * @return the VariableResolver for SqlExpressionBuilder that converts
1127: * Variable names to their Database Field names according to the naming strategy.
1128: */
1129: protected VariableResolver createExpressionBuilderResolver() {
1130: if (variableResolver == null) {
1131: return new ColumnNameVariableResolver(namingStrategy);
1132: }
1133: return variableResolver;
1134: }
1135:
1136: // *********************************************************************
1137: // * UTIL METHODS
1138: // *********************************************************************
1139:
1140: private static String getSqlString(SqlExpression expr) {
1141: return expr != null ? expr.toSqlString() : "";
1142: }
1143:
1144: private static String getSqlStringWith(SqlExpression expr,
1145: String prefix, String suffix) {
1146: StringBuffer sb = new StringBuffer();
1147: if (expr != null) {
1148: sb.append(prefix);
1149: sb.append(expr.toSqlString());
1150: sb.append(suffix);
1151: }
1152: return sb.toString();
1153: }
1154:
1155: private static List getSqlParams(SqlExpression expr) {
1156: return (expr != null && expr.getValues() != null) ? Arrays
1157: .asList(expr.getValues()) : new ArrayList();
1158: }
1159:
1160: /**
1161: * Returns query failed Exception that contains query String and params.
1162: */
1163: protected static RuntimeException createQueryFailedException(
1164: String QueryString, List queryParams,
1165: SQLException nestedException) {
1166: String str = new StringBuffer("Executing list query [").append(
1167: QueryString).append("] with params: ").append(
1168: queryParams).append(" failed").toString();
1169: return new AraneaRuntimeException(str, nestedException);
1170: }
1171: }
|