package persistence;

import java.sql.*;

public class SQLConnection
{
     private Connection connection 			= null;
     private final String url 			= "jdbc:sqlserver://localhost\\SQLEXPRESS";
     private final String DRIVER		= "com.microsoft.sqlserver.jdbc.SQLServerDriver";
 /*    private final String serverName	= "localhost";
     private final String portNumber 	= "1433";
     private final String databaseName	= "ProjectManagement2";
     private final String userName 		= "Nelis";
     private final String password 		= "nelis";
     private final String selectMethod  = "cursor"; */
     private static SQLConnection SQLconnection;
     private Statement statement = null;
     private PreparedStatement preparedStatement= null;

     public SQLConnection() throws SQLException, ClassNotFoundException
     {
    	 Class.forName(DRIVER);
    	 connection = DriverManager.getConnection("jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=ProjectManagement;userName=Lennart;passWord=lennart");
     }


     public Connection getConnection()
     {
          return connection;
      }
     public void displayDbProperties()
     {
          java.sql.DatabaseMetaData dm = null;
          java.sql.ResultSet rs = null;

          try
          {
               connection = this.getConnection();

               if (connection != null) {
                    dm = connection.getMetaData();
                    System.out.println("Information about the drivers:");
                    System.out.println("\tName driver: "+ dm.getDriverName());
                    System.out.println("\tVersion drivers: "+ dm.getDriverVersion ());
                    System.out.println("\nInformation about database:");
                    System.out.println("\tName database: "+ dm.getDatabaseProductName());
                    System.out.println("\tVersion database: "+ dm.getDatabaseProductVersion());
                    System.out.println("Available catalogs ");

                    rs = dm.getCatalogs();

                    while(rs.next())
                    {
                         System.out.println("\tCatalogs: "+ rs.getString(1));
                    }

                    rs.close();
                    rs = null;

                    closeConnection();
               }
               else {
            	   System.out.println("Error: No connection is activated");
               }
          }
          catch(Exception e) {
               e.printStackTrace();
          }
          dm = null;
     }

     private void closeConnection() throws SQLException
     {
         connection.close();
     }

     public static SQLConnection getInstance() throws SQLException, ClassNotFoundException
     {
 		if (SQLconnection == null){
 			SQLconnection = new SQLConnection();
 		}
 		return SQLconnection;

 	}

/*     public ResultSet executeQuery(String query) throws SQLException, ClassNotFoundException, IllegalStateException
     {
    	 if(connection == null)
    	 {
    		 throw new IllegalStateException("No connection to the database!");
    	 }

    	 statement = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_FORWARD_ONLY);
    	 return statement.executeQuery(query);
     }
*/
     public PreparedStatement getPreparedStatement(String query) throws SQLException, ClassNotFoundException
 	 {
 		preparedStatement = getConnection().prepareStatement(query);
 		return preparedStatement;
 	 }

     public int updateQuery(String query) throws SQLException, IllegalStateException, ClassNotFoundException
 	{
 		if(connection == null)
 		{
 			throw new IllegalStateException("No connection to the database.");
 		}
 		int recordsUpdated=0;
 		recordsUpdated = statement.executeUpdate(query);
 		return recordsUpdated;

        }
}