/**
 * 
 */
package psql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.concurrent.TimeUnit;

public class TestJdbc {

	// Temps cumulé de getColums
	private long _timeC;
	// Temps cumulé de getIndexInfos
	private long _timeII;
	// Temps cumulé de getPrimaryKeys
	private long _timePK;
	// Temps cumulé de getExportedKeys
	private long _timeEK;
	// Temps cumulé de getImportedKeys
	private long _timeIK;

	private int _tableCount;
	private int _countC;
	private int _countII;
	private int _countPK;
	private int _countEK;
	private int _countIK;

	public TestJdbc() {

		System.out.println("Start Test Driver");

		loadDriver();

		String url = "jdbc:postgresql://localhost:5432/MPADMIN";
		String user = "postgres";
		String pwd = "poutre";
		String schemaName = "public";

		displayInfoDriver(url);

		Connection con = getConnexion(url, user, pwd);

		displayDatabaseVersion(con);

		DatabaseMetaData dbmd = getDatabaseMetaData(con);

		// Liste des tables
		ResultSet rsTables = getRsTables(dbmd, schemaName);

		getMetaDataTables(dbmd, schemaName, rsTables);

		displayTotalTimes();

	}

	private void displayDatabaseVersion(Connection con) {
		String query = "SELECT version();";

		Statement stmt = getStatement(con);

		ResultSet results = executeSql(con, query, stmt);

		displayValue(results);
	}

	private void getMetaDataTables(DatabaseMetaData dbmd, String schemaName,
			ResultSet rsTables) {
		long start = System.nanoTime();
		String tableName = null;
		_tableCount=0;
		_countC=0;
		_countII=0;
		_countPK=0;
		_countEK=0;
		_countIK=0;
		try {
			rsTables.beforeFirst();
			while (rsTables.next()) {
				tableName = rsTables.getString("TABLE_NAME");
				getMetaDataTable(dbmd, schemaName, tableName);
				_tableCount++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("");
		long time = System.nanoTime() - start;
		displayTotalTime("#### Total time: " + _tableCount + " tables: ", time);
		System.out.println("");
	}

	private ResultSet getRsTables(DatabaseMetaData dbmd, String schemaName) {
		ResultSet rsTables = null;
		String[] types = { "TABLE" };
		try {
			rsTables = dbmd.getTables(null, schemaName, null, types);
			// rsTables = dbmd.getTables(dbmd.getConnection().getCatalog(),
			// schemaName, null, types);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rsTables;
	}

	private DatabaseMetaData getDatabaseMetaData(Connection con) {
		DatabaseMetaData dbmd = null;
		try {
			dbmd = con.getMetaData();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return dbmd;
	}

	public void getMetaDataTable(DatabaseMetaData dbmd, String schemaName,
			String tableName) throws SQLException {
		String key = schemaName == null ? "/".concat(tableName.toLowerCase())
				: schemaName.toLowerCase().concat("/")
						.concat(tableName.toLowerCase());
		long startC = System.nanoTime();
		ResultSet columns = dbmd.getColumns(null, schemaName, tableName, null);
		_timeC += System.nanoTime() - startC;
        _countC += getRowCount(columns);
		long startII = System.nanoTime();
		ResultSet indexInfo = dbmd.getIndexInfo(null, schemaName, tableName, false, false);
		_timeII += System.nanoTime() - startII;
		_countII += getRowCount(indexInfo);
		long startPK = System.nanoTime();
		ResultSet primaryKeys = dbmd.getPrimaryKeys(null, schemaName, tableName);
		_timePK += System.nanoTime() - startPK;
		_countPK += getRowCount(primaryKeys);
		long startEK = System.nanoTime();
		ResultSet exportedKeys = dbmd.getExportedKeys(null, schemaName, tableName);
		_timeEK += System.nanoTime() - startEK;
		_countEK += getRowCount(exportedKeys);
		long startIK = System.nanoTime();
		ResultSet importedKeys = dbmd.getImportedKeys(null, schemaName, tableName);
		_timeIK += System.nanoTime() - startIK;
		_countIK += getRowCount(importedKeys);
	}
	
	private int getRowCount(ResultSet rs) {
		int size = 0;
		try {
		    rs.last();
		    size = rs.getRow();
		    rs.beforeFirst();
		}
		catch(Exception ex) {
		    return 0;
		}
		return size;
	}

	public void displayTotalTimes() {
		System.out.println("#### Details: ");
		displayTotalTime("#### getColumns: " + _countC + " columns: ", _timeC);
		displayTotalTime("#### getIndexInfos: "+ _countII + " indexInfos: ", _timeII);
		displayTotalTime("#### getPrimaryKeys: "+ _countPK + " primaryKeys: ", _timePK);
		displayTotalTime("#### getExportedKeys: "+ _countEK + " exportedKeys: ", _timeEK);
		displayTotalTime("#### getImportedKeys: "+ _countIK + " importedKeys: ", _timeIK);
	}

	/**
	 * 
	 */
	private void displayTotalTime(String msg, long time) {
		System.out.println(msg
				+ TimeUnit.MILLISECONDS.convert(time, TimeUnit.NANOSECONDS)
				+ " milliseconds");
	}

	private Connection getConnexion(String url, String user, String pwd) {
		Connection con = null;
		try {
			// Local
			con = DriverManager.getConnection(url, user, pwd);
		} catch (SQLException e1) {
			System.out
					.println("Problème dans la connection de la base: " + url);
			e1.printStackTrace();
			return null;
		}

		if (con == null)
			System.out.println("Impossible d'obtenir une connection");
		System.out.println("Connection effectuée avec la base: " + url);
		return con;
	}

	private void loadDriver() {
		try {
			Class.forName("org.postgresql.Driver").newInstance();
		} catch (InstantiationException e1) {
			System.out
					.println("Problème dans la création de l'intance du driver");
			e1.printStackTrace();
			return;
		} catch (IllegalAccessException e1) {
			System.out.println("Problème dans la sécurité d'accès du driver");
			e1.printStackTrace();
			return;
		} catch (ClassNotFoundException e1) {
			System.out
					.println("Problème dans la recherche du driver. Le jar n'est pas dans le classpath");
			e1.printStackTrace();
			return;
		}

		System.out.println("Driver enregistré");

	}

	private void displayInfoDriver(String url) {

		for (Enumeration<?> e = DriverManager.getDrivers(); e.hasMoreElements();) {
			Driver driver = (Driver) e.nextElement();
			int majorVersion = driver.getMajorVersion();
			int minorVersion = driver.getMinorVersion();
			System.out.println("Driver = " + driver.getClass() + " v"
					+ majorVersion + "." + minorVersion);
		}
	}

	private ResultSet executeSql(Connection con, String query, Statement stmt) {
		ResultSet results = null;
		try {
			results = stmt.executeQuery(query);
		} catch (SQLException e1) {
			System.out.println("Problème dans l'exécution de la requete: "
					+ query);
			e1.printStackTrace();
			if (con != null) {
				try {
					System.out.println("Fermeture de la connection.");
					con.close();
				} catch (Exception e) {
					System.out
							.println("Problème dans la fermeture de la connection.");
					e.printStackTrace();
					return null;
				}
			}
			return null;
		}
		// System.out.println("Execution de la requete OK: " + query);
		return results;
	}

	private Statement getStatement(Connection con) {

		Statement stmt = null;
		try {
			stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e1) {
			System.out.println("Problème dans la création du statement");
			e1.printStackTrace();
			if (con != null) {
				try {
					System.out.println("Fermeture de la connection.");
					con.close();
				} catch (Exception e) {
					System.out
							.println("Problème dans la fermeture de la connection.");
					e.printStackTrace();
				}
			}
			return null;
		}

		System.out.println("Création du statement OK");
		return stmt;
	}

	private int getColumnCount(ResultSet results) {
		int colCount = -1;
		try {
			colCount = results.getMetaData().getColumnCount();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return colCount;
	}

	private void displayValue(ResultSet results) {
		System.out.println("");
		int colCount = getColumnCount(results);
		try {
			ResultSetMetaData dbmd = results.getMetaData();
			while (results.next()) {
				for (int c = 1; c <= colCount; c++) {
					System.out.println(dbmd.getColumnName(c).concat(": ")
							.concat(results.getObject(c).toString()));
				}
			}
		} catch (SQLException e) {
			System.out.println("Problème dans la récupération de la valeur.");
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		new TestJdbc();
	}

}
