com.tera.common.database.query.CQueryService.java Source code

Java tutorial

Introduction

Here is the source code for com.tera.common.database.query.CQueryService.java

Source

/**
 * This file is part of tera-api.
 * 
 * tera-api is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * tera-api is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with tera-api.  If not, see <http://www.gnu.org/licenses/>.
 */
package com.tera.common.database.query;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;

import org.apache.commons.dbutils.DbUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.inject.Inject;
import com.tera.common.database.DatabaseFactory;

/**
 * @author ATracer
 */
public class CQueryService implements QueryService {

    private static final Logger log = LoggerFactory.getLogger(CQueryService.class);

    /**
     * Database connection factory
     */
    private DatabaseFactory databaseFactory;

    /**
     * @param databaseFactory
     */
    @Inject
    public CQueryService(DatabaseFactory databaseFactory) {
        this.databaseFactory = databaseFactory;
    }

    @Override
    public boolean update(String query, String errorMessage) {
        Connection connection = null;
        Statement statement = null;

        try {
            connection = databaseFactory.getConnection();
            statement = connection.createStatement();
            statement.executeUpdate(query);
        } catch (Exception ex) {
            if (errorMessage == null)
                log.error("Error executing select query " + ex, ex);
            else
                log.error(errorMessage + " " + ex, ex);
            return false;
        } finally {
            close(null, statement, connection);
        }
        return true;
    }

    @Override
    public <T> T select(String select, ReadQuery<T> query) {
        return select(select, query, null);
    }

    @Override
    public <T> T select(String select, ReadQuery<T> query, String errorMessage) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = databaseFactory.getConnection();
            statement = connection.prepareStatement(select);
            if (query instanceof ParamReadQuery)
                ((ParamReadQuery<T>) query).setParams(statement);
            resultSet = statement.executeQuery();
            return query.handleRead(resultSet);
        } catch (Exception ex) {
            if (errorMessage == null)
                log.error("Error executing select query " + ex, ex);
            else
                log.error(errorMessage + " " + ex, ex);
        } finally {
            close(resultSet, statement, connection);
        }
        return null;
    }

    @Override
    public <T> T call(String call, CallReadQuery<T> query) {
        return call(call, query, null);
    }

    @Override
    public <T> T call(String call, CallReadQuery<T> query, String errorMessage) {
        Connection connection = null;
        CallableStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = databaseFactory.getConnection();
            statement = connection.prepareCall(call);
            query.setParams(statement);
            resultSet = statement.executeQuery();
            return query.handleRead(resultSet);
        } catch (Exception ex) {
            if (errorMessage == null)
                log.error("Error calling stored procedure " + ex, ex);
            else
                log.error(errorMessage + " " + ex, ex);
        } finally {
            close(resultSet, statement, connection);
        }
        return null;
    }

    @Override
    public boolean insertUpdate(String insertUpdate, InsertUpdateQuery query) {
        return insertUpdate(insertUpdate, query, null);
    }

    @Override
    public boolean insertUpdate(String insertUpdate, InsertUpdateQuery query, String errorMessage) {
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = databaseFactory.getConnection();
            statement = connection.prepareStatement(insertUpdate);
            if (query != null)
                query.handleInsertUpdate(statement);

            statement.executeUpdate();
        } catch (Exception e) {
            if (errorMessage == null)
                log.error("Failed to execute InsertUpdate query {}", e, e);
            else
                log.error(errorMessage + " " + e, e);
            return false;
        } finally {
            close(null, statement, connection);
        }
        return true;
    }

    @Override
    public <T> boolean batchUpdate(String batchUpdate, BatchUpdateQuery<T> query) {
        return this.batchUpdate(batchUpdate, query, null);
    }

    @Override
    public <T> boolean batchUpdate(String batchUpdate, BatchUpdateQuery<T> query, String errorMessage) {
        return this.batchUpdate(batchUpdate, query, errorMessage, true);
    }

    @Override
    public <T> boolean batchUpdate(String batchUpdate, BatchUpdateQuery<T> query, String errorMessage,
            boolean autoCommit) {
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = databaseFactory.getConnection();
            statement = connection.prepareStatement(batchUpdate);
            connection.setAutoCommit(autoCommit);

            Collection<T> items = query.getItems();
            for (T item : items) {
                query.handleBatch(statement, item);
                statement.addBatch();
            }
            statement.executeBatch();

            if (!autoCommit) {
                connection.commit();
            }
        } catch (Exception e) {
            if (errorMessage == null)
                log.error("Failed to execute BatchUpdate query {}", e, e);
            else
                log.error(errorMessage + " " + e, e);
            return false;
        } finally {

            close(null, statement, connection);
        }
        return true;
    }

    @Override
    public PreparedStatement prepareStatement(String query) {
        return prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    }

    @Override
    public PreparedStatement prepareStatement(String query, int resultSetType, int resultSetConcurrency) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = databaseFactory.getConnection();
            preparedStatement = connection.prepareStatement(query, resultSetType, resultSetConcurrency);
        } catch (Exception e) {
            log.error("Can't create PreparedStatement for querry: " + query, e);
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e1) {
                    log.error("Can't close connection after exception", e1);
                }
            }
        }
        return preparedStatement;
    }

    @Override
    public void closeStatement(Statement preparedStatement) {
        try {
            if (preparedStatement.isClosed()) {
                // noinspection ThrowableInstanceNeverThrown
                log.error("Attempt to close PreparedStatement that is closed already", new Exception());
                return;
            }

            Connection connection = preparedStatement.getConnection();
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            log.error("Error while closing PreparedStatement", e);
        }
    }

    @Override
    public void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            DbUtils.close(resultSet);
            DbUtils.close(statement);
            DbUtils.close(connection);
        } catch (Exception e) {
            log.error("Failed to close DB connection {}", e, e);
        }
    }

    @Override
    public void close(ResultSet resultSet, Statement statement) {
        try {
            DbUtils.close(resultSet);
            closeStatement(statement);
        } catch (SQLException e) {
            log.error("Failed to close DB connection {}", e, e);
        }
    }

    @Override
    public int[] getUsedIds(String query, String idColumn) {
        PreparedStatement statement = prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = null;
        try {
            resultSet = statement.executeQuery();
            resultSet.last();
            int count = resultSet.getRow();
            resultSet.beforeFirst();
            int[] ids = new int[count];
            for (int i = 0; i < count; i++) {
                resultSet.next();
                ids[i] = resultSet.getInt(idColumn);
            }
            return ids;
        } catch (SQLException e) {
            log.error("Can't get id's using query {}", query, e);
        } finally {
            close(resultSet, statement);
        }

        return new int[0];
    }
}