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
030/**
031 * Wrapper for {@link CmsSelectQuery} objects which adds SQL code for results paging.<p>
032 *
033 * The wrapper can either use the window function approach to paging or append a LIMIT/OFFSET clause.
034 *
035 * @see "http://troels.arvin.dk/db/rdbms/#select-limit-offset"
036 *
037 * @since 8.0.0
038 */
039public class CmsPagingQuery implements I_CmsQueryFragment {
040
041    /** Flag which indicates whether subqueries should be named. */
042    private boolean m_nameSubquery;
043
044    /** The current page. */
045    private int m_page;
046
047    /** The page size. */
048    private int m_pageSize;
049
050    /** The wrapped query. */
051    private CmsSelectQuery m_select;
052
053    /** If true, use window functions, else use a LIMIT/OFFSET clause. */
054    private boolean m_useWindowFunctions;
055
056    /**
057     * Creates a new instance.<p>
058     *
059     * @param select the wrapped query
060     */
061    public CmsPagingQuery(CmsSelectQuery select) {
062
063        m_select = select;
064    }
065
066    /**
067     * Enables or disables the naming of subqueries.<p>
068     *
069     * @param nameSubquery if true, enables naming of subqueries
070     */
071    public void setNameSubquery(boolean nameSubquery) {
072
073        m_nameSubquery = nameSubquery;
074    }
075
076    /**
077     * Sets both the page size and current page to use for the query.<p>
078     *
079     * @param pageSize the page size
080     * @param page the current page (counting starts at 1)
081     */
082    public void setPaging(int pageSize, int page) {
083
084        m_pageSize = pageSize;
085        m_page = page;
086    }
087
088    /**
089     * Enables the use of window functions.<p>
090     *
091     * @param useWindowFunctions if true, enables window functions
092     */
093    public void setUseWindowFunctions(boolean useWindowFunctions) {
094
095        m_useWindowFunctions = useWindowFunctions;
096    }
097
098    /**
099     * @see org.opencms.db.I_CmsQueryFragment#visit(org.opencms.db.CmsStatementBuilder)
100     */
101    public void visit(CmsStatementBuilder builder) {
102
103        if (m_useWindowFunctions) {
104            I_CmsQueryFragment order = m_select.getOrdering();
105            assert order != null;
106            CmsCompositeQueryFragment rownumFragment = new CmsCompositeQueryFragment();
107            rownumFragment.add(new CmsSimpleQueryFragment("ROW_NUMBER() OVER (ORDER BY "));
108            rownumFragment.add(order);
109            rownumFragment.add(new CmsSimpleQueryFragment(") AS rownumber"));
110            m_select.addColumn(rownumFragment);
111            builder.add("SELECT * FROM ( ");
112            m_select.visit(builder);
113            int start = 1 + (m_pageSize * (m_page - 1));
114            int end = (start + m_pageSize) - 1;
115            builder.add(")");
116            if (m_nameSubquery) {
117                builder.add(" AS rnsq ");
118            }
119            builder.add(" WHERE rownumber BETWEEN " + start + " AND " + end);
120        } else {
121            m_select.visit(builder);
122            int offset = (m_page - 1) * m_pageSize;
123            builder.add("\nLIMIT " + m_pageSize + " OFFSET " + offset);
124        }
125    }
126}