001/*
002 * This library is part of OpenCms -
003 * the Open Source Content Management System
004 *
005 * Copyright (c) Alkacon Software GmbH & Co. KG (http://www.alkacon.com)
006 *
007 * This library is free software; you can redistribute it and/or
008 * modify it under the terms of the GNU Lesser General Public
009 * License as published by the Free Software Foundation; either
010 * version 2.1 of the License, or (at your option) any later version.
011 *
012 * This library is distributed in the hope that it will be useful,
013 * but WITHOUT ANY WARRANTY; without even the implied warranty of
014 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
015 * Lesser General Public License for more details.
016 *
017 * For further information about Alkacon Software, please see the
018 * company website: http://www.alkacon.com
019 *
020 * For further information about OpenCms, please see the
021 * project website: http://www.opencms.org
022 *
023 * You should have received a copy of the GNU Lesser General Public
024 * License along with this library; if not, write to the Free Software
025 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
026 */
027
028package org.opencms.db;
029
030import java.util.Collections;
031import java.util.HashSet;
032import java.util.List;
033import java.util.Set;
034
035import com.google.common.base.Joiner;
036import com.google.common.collect.Lists;
037
038/**
039 * A class for generating SQL SELECT statements.<p>
040 *
041 * @since 8.0.0
042 */
043public class CmsSelectQuery implements I_CmsQueryFragment {
044
045    /**
046     * Helper class which wraps a table alias.<p>
047     */
048    public class TableAlias {
049
050        /** The table alias. */
051        String m_name;
052
053        /**
054         * Creates a new instance.<p>
055         *
056         * @param name the table alias
057         */
058        public TableAlias(String name) {
059
060            m_name = name;
061        }
062
063        /**
064         * Adds the table alias before a column name.<p>
065         *
066         * @param colName the column name
067         *
068         * @return the column name, qualified by the table alias
069         */
070        public String column(String colName) {
071
072            return m_name + "." + colName;
073        }
074
075        /**
076         * Returns the name of the table alias.<p>
077         *
078         * @return the name of the table alias
079         */
080        public String getName() {
081
082            return m_name;
083        }
084    }
085
086    /** The columns of the result. */
087    private CmsCompositeQueryFragment m_columns = new CmsCompositeQueryFragment();
088
089    /** The conditions for the WHERE clause. */
090    private CmsCompositeQueryFragment m_conditions = new CmsCompositeQueryFragment();
091
092    /** The result ordering. */
093    private I_CmsQueryFragment m_ordering;
094
095    /** SQL clauses which will be added after the other ones. */
096    private CmsCompositeQueryFragment m_otherClauses = new CmsCompositeQueryFragment();
097
098    /** The tables from which the data should be fetched. */
099    private List<String> m_tables = Lists.newArrayList();
100
101    /** The table aliases which have already been used. */
102    private Set<String> m_usedAliases = new HashSet<String>();
103
104    /**
105     * Creates a new instance.<p>
106     */
107    public CmsSelectQuery() {
108
109        // always use 1 = 1 as a condition so we don't have to worry about whether we need a "WHERE" keyword
110        m_conditions.add(new CmsSimpleQueryFragment("1 = 1", Collections.<Object> emptyList()));
111        m_conditions.setSeparator(" AND ");
112        m_otherClauses.setSeparator("\n");
113        m_columns.setSeparator(", ");
114    }
115
116    /**
117     * Adds another clause to the query.<p>
118     *
119     * @param clause the clause to add
120     */
121    public void addClause(I_CmsQueryFragment clause) {
122
123        m_otherClauses.add(clause);
124    }
125
126    /**
127     * Adds an expression which should be added as a column in the result set.<p>
128     *
129     * @param node the expression which should be added as a column
130     */
131    public void addColumn(I_CmsQueryFragment node) {
132
133        m_columns.add(node);
134    }
135
136    /**
137     * Adds an expression which should be added as a column in the result set.<p>
138     *
139     * @param column the expression which should be added as a column
140     */
141    public void addColumn(String column) {
142
143        m_columns.add(new CmsSimpleQueryFragment(column, Collections.<Object> emptyList()));
144    }
145
146    /**
147     * Adds a new condition to the query.<p>
148     *
149     * @param node the condition to add to the query
150     */
151    public void addCondition(I_CmsQueryFragment node) {
152
153        m_conditions.add(node);
154    }
155
156    /**
157     * Adds a new condition to the query.<p>
158     *
159     * @param fragment the condition SQL
160     * @param params the condition parameters
161     */
162    public void addCondition(String fragment, Object... params) {
163
164        m_conditions.add(new CmsSimpleQueryFragment(fragment, params));
165    }
166
167    /**
168     * Adds a table to the query's FROM clause.<p>
169     *
170     * @param table the table to add
171     */
172    public void addTable(String table) {
173
174        m_tables.add(table);
175    }
176
177    /**
178     * Adds a table the query's FROM clause.<p>
179     *
180     * @param table the table to add
181     * @param aliasPrefix the prefix used to generate the alias
182     *
183     * @return an alias for the table
184     */
185    public TableAlias addTable(String table, String aliasPrefix) {
186
187        String alias = makeAlias(aliasPrefix);
188        m_tables.add(table + " " + alias);
189        return new TableAlias(alias);
190
191    }
192
193    /**
194     * Returns the fragment for the ORDER BY clause.<p>
195     *
196     * @return the fragment for the ORDER BY clause
197     */
198    public I_CmsQueryFragment getOrdering() {
199
200        return m_ordering;
201    }
202
203    /**
204     * Sets the SQL used for the ORDER BY clause.<p>
205     *
206     * @param ordering the SQL used for the ORDER BY clause
207     */
208    public void setOrdering(String ordering) {
209
210        if (ordering != null) {
211            m_ordering = new CmsSimpleQueryFragment(ordering, Collections.<Object> emptyList());
212        } else {
213            m_ordering = null;
214        }
215    }
216
217    /**
218     * @see org.opencms.db.I_CmsQueryFragment#visit(org.opencms.db.CmsStatementBuilder)
219     */
220    public void visit(CmsStatementBuilder builder) {
221
222        builder.add("SELECT ");
223        Joiner commaJoin = Joiner.on(", ");
224        m_columns.visit(builder);
225        builder.add("\nFROM ");
226        builder.add(commaJoin.join(m_tables));
227        builder.add("\nWHERE ");
228        m_conditions.visit(builder);
229        if (m_ordering != null) {
230            builder.add("\nORDER BY ");
231            m_ordering.visit(builder);
232        }
233        m_otherClauses.visit(builder);
234
235    }
236
237    /**
238     * Helper method for generating an alias by taking a prefix and appending the first number to it for which
239     * the resulting string is not already used as an alias.<p>
240     *
241     * @param prefix the alias prefix
242     *
243     * @return the table alias
244     */
245    private String makeAlias(String prefix) {
246
247        int i = 0;
248        String result;
249        do {
250            if (i == 0) {
251                result = prefix;
252            } else {
253                result = prefix + i;
254            }
255
256        } while (m_usedAliases.contains(result));
257        m_usedAliases.add(result);
258        return result;
259
260    }
261
262}