net.urosk.mifss.core.lib.db.PaginationHelper.java Source code

Introduction

Here is the source code for net.urosk.mifss.core.lib.db.PaginationHelper.java

Source

/*
 * 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.
 *
 **************************************************************************
 * MIFSS  - content storage system
 * 
 *
 * @uthors: uros.kristan@gmail.com (Uro Kristan ) Urosk.NET
 *         jernej.svigelj@gmail.com (Jernej vigelj) 
 */
package net.urosk.mifss.core.lib.db;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.MetaDataAccessException;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class PaginationHelper {

    public static final String COUNT_QUERY = " SELECT COUNT(0) FROM ( :query )  ";

    public static final String MYSQL_PAGING = "  ( :query ) LIMIT :from , :limit; ";

    public static final String ORACLE_PAGING = " SELECT * FROM ( SELECT row_.*, rownum rownum_      FROM ( :query )  row_    WHERE   rownum <= :to       ) where rownum_ > :from ";

    protected Logger logger = Logger.getLogger(PaginationHelper.class);

    public DataResult fetchPage(final DataSource dataSource, final NamedParameterJdbcTemplate jt,
            final String sqlFetchRows, final MapSqlParameterSource namedParameters, RowMapper rowMapper,
            final long offset, final long limit) {

        String countSql = StringUtils.replace(COUNT_QUERY, ":query", sqlFetchRows);

        final int rowCount = jt.queryForObject(countSql, namedParameters, Integer.class);

        Map<String, String> params = new HashMap<String, String>();
        params.put("query", sqlFetchRows);
        params.put("from", offset + "");
        params.put("to", (offset + limit) + "");
        params.put("limit", (limit) + "");

        // get database product name for paging!
        String dbProductName = getDatabaseProductName(dataSource);
        dbProductName = dbProductName.toLowerCase();

        String pagingSql = "NOT IMPELEMNTED FOR THIS DATABASE! - " + dbProductName;

        if (dbProductName.contains("mysql")) {

            pagingSql = StringUtils.replace(MYSQL_PAGING, ":query", sqlFetchRows);
            pagingSql = StringUtils.replace(pagingSql, ":from", offset + "");
            pagingSql = StringUtils.replace(pagingSql, ":limit", (limit) + "");

        } else if (dbProductName.contains("oracle")) {

            pagingSql = StringUtils.replace(ORACLE_PAGING, ":query", sqlFetchRows);
            pagingSql = StringUtils.replace(pagingSql, ":from", offset + "");
            pagingSql = StringUtils.replace(pagingSql, ":to", (offset + limit) + "");

        } else {
            logger.error("Paging for selected database is not yet implemented!! + Check paginationHelper for this");

        }

        List list = jt.query(pagingSql, namedParameters, rowMapper);

        // create the page object
        final DataResult dataResult = new DataResult();
        dataResult.setCount(rowCount);
        dataResult.setFrom(offset);
        dataResult.setLimit(limit);
        dataResult.setList(list);

        return dataResult;
    }

    public String getDatabaseProductName(final DataSource dataSource) {

        String dbName = null;

        try {

            dbName = (String) JdbcUtils.extractDatabaseMetaData(dataSource, "getDatabaseProductName");

        } catch (MetaDataAccessException e) {
            logger.error("getDatabaseProductName" + e, e);
        }

        return dbName;
    }
}