package library;

import java.sql.*;

public class DBConnection {
	 private static int CONNECTION_RETRIES = 10;
	    private static int QUERY_RETRIES = 10;
	    
	    private String dbUrl;
	    private String password;
	    private String username;
	    private String jdbcClassName;
	    private Connection dbCon;	

	    @SuppressWarnings("unused")
		private boolean hasError = false;
	    @SuppressWarnings("unused")
		private String errorString = null;
	    private static DBConnection myInstance = null;
	    
	    public DBConnection() {}
	    
	    public DBConnection( String inUrl, String inJdbcClassName, String inUserName, String inPassWord )
		throws Exception
	    {
		dbUrl = inUrl;
		jdbcClassName = inJdbcClassName;
		username = inUserName;
		password = inPassWord;
		connect();
	    }
	    
	    public void connectAsDefaultCteLibrary()
		throws Exception
	    {
		dbUrl = "jdbc:mysql://localhost/library";
	        jdbcClassName = "org.gjt.mm.mysql.Driver";
	        username = "root";
	        password = "linux";
		closeConnections();
		connect();
	    }

	    /**
	     * closeConnections closes any currently open connections
	     * @return void
	     */
	    private void closeConnections() 
		throws Exception {
		if (dbCon!=null) {
		    dbCon.close();
		}
	    }
	    
	    /**
	     * DBWrapper Instance()
	     * Get a singleton instance of the DBWrapper object.
	     * @return DBWrapper
	     */
	    public static DBConnection Instance() 
		throws Exception {
		if (myInstance == null) {
		    myInstance = new DBConnection();
		    myInstance.connectAsDefaultCteLibrary();
		}
		return myInstance;
	    }

	   
	    private boolean connect() 
		throws Exception {
		
		boolean opened = false;
		
		Driver driver = (Driver) Class.forName(jdbcClassName).newInstance();
		DriverManager.registerDriver(driver);
		
		int retry = 0;
		while (retry++ <= CONNECTION_RETRIES) {
		    dbCon = DriverManager.getConnection(dbUrl, username, password);
		    opened = true;
		    break;
		}
		return opened;
	    }

	    public boolean connect( String inUrl, String inJdbcClassName, String inUserName, String inPassWord ) 
		throws Exception {
		dbUrl = inUrl;
	        jdbcClassName = inJdbcClassName;
	        username = inUserName;
	        password = inPassWord;
		closeConnections();
		
		return connect();
	    }
	    
	    public ResultSet runQuery( String sqlQuery ) 
		throws Exception {
		
		int retry = 0;
		
		ResultSet resultSet = null;
		Statement dbStatement = null;
		while (retry++ < QUERY_RETRIES) {    
		    dbStatement = dbCon.createStatement();
		    resultSet = dbStatement.executeQuery(sqlQuery);
		    break;
		}
		return resultSet;
	    }
	    
	    public boolean runUpdate( String sqlQuery ) 
		throws Exception {
		
	        int retry = 0;
		boolean wasExecuted = false;
		
		
	        Statement dbStatement = null;
	        while (retry++ < QUERY_RETRIES) {
		  
		    dbStatement = dbCon.createStatement();
		   
		    dbStatement.executeUpdate(sqlQuery);
		    wasExecuted = true;
		}
	        return wasExecuted;
	    }
	    
	 
	    public ResultSet runChainedQuery( String sqlQuery, String isolationLevel ) 
		throws Exception {
		
	        int retry = 0;
		
		
	        ResultSet resultSet = null;
	        Statement dbStatement = null;
		
		dbStatement = dbCon.createStatement();
		
	        while (retry++ < QUERY_RETRIES) {
		   
		    dbStatement.executeUpdate( "Begin Transaction" );
		  
		    dbStatement.executeUpdate( new String( "Set Transaction Isolation level " + isolationLevel ) );
		   
		    resultSet = dbStatement.executeQuery( sqlQuery );
		
		    dbStatement.executeUpdate( "commit" );
		    
		    dbStatement.close();
		    break;
		}
	        return resultSet;
	    }
	    
	  
	    public boolean runChainedUpdate( String [] sqlQuery, String isolationLevel ) 
		throws Exception {
		int retry = 0;
		
	        Statement dbStatement = null;
		boolean wasExecuted = false;
	
		dbStatement = dbCon.createStatement();
		
	        while (retry++ < QUERY_RETRIES) {
		   
		    try
			{
			    dbStatement.executeUpdate( "Begin Transaction" );
			    // Set the isolation level.
			    dbStatement.executeUpdate( new String( "Set Transaction Isolation level " + isolationLevel ) );
			    // For each sql statement, perform the update.
			    for( int i=0; i<sqlQuery.length; i++ ) {
				dbStatement.executeUpdate( sqlQuery[i] );
			    }
			    // Commit the transaction and close.
			    dbStatement.executeUpdate( "commit" );
			    dbStatement.close();
			    wasExecuted = true;
			} catch (Exception e) {
			    errorString = new String( "Error executing: " + sqlQuery + "\nCause: " + e.toString() );
			    hasError = true;
			    // Rollback if an error has occured.
			    dbStatement.executeUpdate( "rollback" );
			    dbStatement.close();
			}
		}
	        return wasExecuted;
	    }
}