Java tutorial
/** * 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]; } }