com.wizarius.orm.database.connection.DBConnection Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of wizarius-orm Show documentation
Java orm for Postgres or Mysql with migration system and connection pool
The newest version!
package com.wizarius.orm.database.connection;

import com.wizarius.orm.database.DBException;
import lombok.extern.slf4j.Slf4j;

import java.io.Closeable;
import java.sql.*;
import java.util.concurrent.*;

/**
 * Created by Vladyslav Shyshkin on 12.11.17.
 */
@SuppressWarnings({"UnusedReturnValue", "unused"})
@Slf4j
public class DBConnection implements Closeable {
    private final ExecutorService executor = Executors.newCachedThreadPool();
    private final DBConnectionPool pool;
    private final Connection connection;
    private PreparedStatement preparedStatement;
    private Statement statement;

    public DBConnection(Connection connection, DBConnectionPool pool) {
        this.connection = connection;
        this.pool = pool;
    }

    /**
     * Create prepare statement
     *
     * @param sql sql to prepare statement
     * @return prepare statement
     * @throws SQLException on unable to create prepared statement
     */
    public PreparedStatement createPrepareStatement(String sql) throws SQLException {
        preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        return preparedStatement;
    }

    /**
     * Execute prepare statement
     *
     * @return prepare statement execution result
     * @throws SQLException on unable to execute prepared statement
     */
    public ResultSet executePrepareStatement() throws SQLException {
        return preparedStatement.executeQuery();
    }

    /**
     * Create statement
     *
     * @return statement
     * @throws SQLException on unable to create statement
     */
    private Statement createStatement() throws SQLException {
        statement = connection.createStatement();
        return statement;
    }

    /**
     * Execute statement sql query
     *
     * @param sql query to execute
     * @return result set
     * @throws SQLException on unable to execute query
     */
    public ResultSet executeSqlQuery(String sql) throws SQLException {
        return createStatement().executeQuery(sql);
    }

    /**
     * Execute statement query
     *
     * @param sql sql to execute
     * @throws SQLException on unable to execute query
     */
    public boolean executeQuery(String sql) throws SQLException {
        return createStatement().execute(sql);
    }

    /**
     * Execute query
     *
     * @param sql sql to execute
     * @return result of execution
     * @throws SQLException on unable to execute sql query
     */
    public long executeQueryWithKey(String sql) throws SQLException {
        Statement statement = createStatement();
        statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
        ResultSet rs = statement.getGeneratedKeys();
        long productId = 0;
        if (rs.next()) {
            productId = rs.getLong(1);
        }
        return productId;
    }

    /**
     * Connection
     *
     * @return sql connection
     */
    public Connection getConnection() {
        return connection;
    }

    /**
     * Start transaction
     *
     * @throws DBException on unable to start transaction
     */
    public void startTransaction() throws DBException {
        // if set true, commit method will be called
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            throw new DBException("Unable to start transaction", e);
        }
    }

    /**
     * End transaction
     *
     * @throws DBException on unable to commit
     */
    public void commitTransaction() throws DBException {
        try {
            connection.commit();
        } catch (SQLException e) {
            throw new DBException("Unable to commit transaction", e);
        }
    }

    /**
     * Rollback transaction
     *
     * @throws DBException on unable to execute
     */
    public void rollbackTransaction() throws DBException {
        try {
            connection.rollback();
        } catch (SQLException e) {
            throw new DBException("Unable to rollback transaction", e);
        }
    }

    /**
     * Close
     */
    @Override
    public void close() {
        clearResources();
        pool.releaseConnection(this);
    }

    /**
     * Clear connections
     */
    private void clearResources() {
        try {
            if (preparedStatement != null && !preparedStatement.isClosed()) {
                preparedStatement.close();
            }
        } catch (SQLException e) {
            log.error("Unable to close prepare statement " + e.getMessage(), e);
        }
        try {
            if (statement != null && !statement.isClosed()) {
                statement.close();
            }
        } catch (SQLException e) {
            log.trace("Unable to close statement " + e.getMessage(), e);
        }
        try {
            if (statement != null && !statement.isClosed() && statement.getResultSet() != null && !statement.getResultSet().isClosed()) {
                statement.getResultSet().close();
            }
        } catch (SQLException e) {
            log.error("Unable to close result set. " + e.getMessage(), e);
        }
        try {
            if (!connection.getAutoCommit()) {
                //rollbackTransaction to discard pending transaction changes
                connection.rollback();
                connection.setAutoCommit(true);
            }
        } catch (SQLException e) {
            log.error("Unable to set auto commitTransaction to true. " + e.getMessage(), e);
        }
    }


    /**
     * Check if is connection connected
     *
     * @param seconds timeout seconds
     * @return true if connected
     */
    boolean checkConnection(int seconds) {
        Callable task = () -> {
            final String CHECK_SQL_QUERY = "SELECT 1";
            try {
                PreparedStatement statement = createPrepareStatement(CHECK_SQL_QUERY);
                if (!statement.isClosed()) {
                    statement.execute();
                }
                return true;
            } catch (SQLException | NullPointerException e) {
                log.error("Unable to execute query " + CHECK_SQL_QUERY + " . " + e.getMessage(), e);
            } catch (Exception e) {
                log.error("Another exception in query execution" + CHECK_SQL_QUERY + " . " + e.getMessage(), e);
            }
            return false;
        };
        Future future = executor.submit(task);
        try {
            return future.get(seconds, TimeUnit.SECONDS);
        } catch (TimeoutException | InterruptedException | ExecutionException e) {
            log.error("Unable to check connection " + e.getMessage(), e);
            return false;
        } catch (Exception e) {
            log.error("Unable to check connection another exception" + e.getMessage(), e);
            return false;
        } finally {
            clearResources();
            future.cancel(true);
        }
    }
}