Refactoring da classe de conexão

package repository;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

final public class SQLDao {
	
	// DATABASE CONFIGURATION
	private final static String DBURL = "jdbc:mysql://SERVERADREES/DATABASE";
	private final static String DRIVER = "com.mysql.jdbc.Driver";
	private final static String USER = "USER";
	private final static String PASSWD = "PASSWD";

	//Private intern attributes
	private static SQLDao instance;
	private Connection connection;
	private static PreparedStatement preparedStatement;
	private static int i = 0;
	
	/**
	 * Singleton pattern
	 */
	private SQLDao() {
		try {
			connection = getConnection();
		} catch (Exception exception) {
			exception.printStackTrace();
		}
	}

	/**
	 * 
	 * @return a instance of SQLDao, part of the Singleton pattern
	 */
	static SQLDao getInstance() {
		i = 0;
		if (instance == null) {
			instance = new SQLDao();
		}
		return instance;
	}

	/**
	 * 
	 * @return A connection open and ready;
	 * @throws SQLException Possible errors generated by a bad configuration of Database Server;
	 * @throws ClassNotFoundException Possible errors generated by a bad driver properly configuration;
	 */
	private static Connection getConnection() throws SQLException {
		try {
			Class.forName(DRIVER);
			return DriverManager.getConnection(DBURL, USER, PASSWD);
		} catch ( ClassNotFoundException exception ) {
			throw new SQLException("The driver is properly configured?\n " + exception.getMessage());
		} catch ( SQLException exception ) {
			throw exception;
		}
	}
	
	/**
	 * 
	 * @param parameter Parameter to be added to the query; 
	 * @throws SQLException Possible errors generated by a bad object;
	 */
	private static void addParameter( Object parameter ) throws SQLException {
		preparedStatement.setObject(++i, parameter);
	}

	/**
	 * 
	 * @param query The query to be executed in the database;
	 * @param keys Variables to be replaced in the query,
	 *        adds here the sanitization of queries;
	 * @return ResultSet containing the query result;
	 * @throws SQLException Possible errors generated by a bad query;
	 */
	ResultSet select( String query, Object ... keys ) throws SQLException {
		try {
		if ( connection.isClosed() )
			connection = getConnection();
		
			preparedStatement = connection.prepareStatement(query);
			for ( Object parameter : keys )
				addParameter(parameter);
			return preparedStatement.executeQuery();
		} catch ( SQLException exception ) {
			throw exception;
		} finally {
			i = 0;
			preparedStatement.clearParameters();
		}
	}
	
	/**
	 * 
	 * @param query The query to be executed in the database;
	 * @return ResultSet containing the query result;
	 * @throws SQLException Possible errors generated by a bad query;
	 */
	ResultSet select( String query ) throws SQLException {
		try {
		if ( connection.isClosed() )
			connection = getConnection();
		
			preparedStatement = connection.prepareStatement(query);
			return preparedStatement.executeQuery();
		} catch ( SQLException exception ) {
			throw exception;
		} finally {
			i = 0;
			preparedStatement.clearParameters();
		}
	}

	/**
	 * 
	 * @param query The query to be executed in the database;
	 * @param keys Variables to be replaced in the query,
	 *        adds here the sanitization of queries; 
	 * @return integer value that represents the number of rows affected
	 * @throws SQLException Possible errors generated by a bad query;
	 */
	int execute( String query, Object ... keys ) throws SQLException {
		try {
		if (connection.isClosed())
			connection = getConnection();
		
			preparedStatement = connection.prepareStatement(query);
			for ( Object parameter : keys )
				addParameter(parameter);
			return preparedStatement.executeUpdate();
		} catch ( SQLException exception ) {
			throw exception;
		} finally {
			i = 0;
			preparedStatement.clearParameters();
		}
	}
	
	/**
	 * 
	 * @param query The query to be executed in the database;
	 * @return integer value that represents the number of rows affected
	 * @throws SQLException Possible errors generated by a bad query;
	 */
	int execute( String query) throws SQLException {
		try {
		if (connection.isClosed())
			connection = getConnection();
		
			preparedStatement = connection.prepareStatement(query);
			return preparedStatement.executeUpdate();
		} catch ( SQLException exception ) {
			throw exception;
		} finally {
			i = 0;
			preparedStatement.clearParameters();
		}
	}

	/**
	 * 
	 * @param query The query to be executed in the database;
	 * @param keys Variables to be replaced in the query,
	 *        adds here the sanitization of queries; 
	 * @return A list of integer values that represents the IDs affected 
	 * @throws SQLException Possible errors generated by a bad query;
	 */
	List<Integer> executeRetriveIdAffected( String query, Object ... keys ) throws SQLException {
		try {
		if ( connection.isClosed() )
			connection = getConnection();

			preparedStatement = connection.prepareStatement( query, java.sql.Statement.RETURN_GENERATED_KEYS );
			for ( Object parameter : keys )
				addParameter(parameter);
			ResultSet retrivedKeys = preparedStatement.getGeneratedKeys();
			List<Integer> dataBD = new ArrayList<Integer>();
			while ( retrivedKeys.next() )
				dataBD.add(retrivedKeys.getInt(1));
			return dataBD;
		} catch ( SQLException exception ) {
			throw exception;
		} finally {
			i = 0;
			preparedStatement.clearParameters();
		}
	}
	
	/**
	 * 
	 * @param query The query to be executed in the database;
	 * @return A list of integer values that represents the IDs affected 
	 * @throws SQLException Possible errors generated by a bad query;
	 */
	List<Integer> executeRetriveIdAffected( String query ) throws SQLException {
		try {
		if ( connection.isClosed() )
			connection = getConnection();
		
			preparedStatement = connection.prepareStatement( query, java.sql.Statement.RETURN_GENERATED_KEYS );
			ResultSet retrivedKeys = preparedStatement.getGeneratedKeys();
			List<Integer> dataBD = new ArrayList<Integer>();
			while ( retrivedKeys.next() )
				dataBD.add(retrivedKeys.getInt(1));
			return dataBD;
		} catch ( SQLException exception ) {
			throw exception;
		} finally {
			i = 0;
			preparedStatement.clearParameters();
		}
	}
	
	
	/**
	 * 
	 * @throws SQLException Possible errors generated by a already closed connection;
	 */
	void dispose() throws SQLException {
		if (preparedStatement != null)
			preparedStatement.close();

		if (!connection.isClosed())
			connection.close();
	}

}