package connector;

import java.sql.Connection;
import java.sql.DriverManager;

public class CronusDB {

	public static Connection startConnection() {
		Connection con = null;
		try {
			DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
		} catch (Exception e) {
			System.out.println("Kan inte database driver class: " + e);
		}
		try {
			con = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1433;" + "databaseName=Demo Database NAV (5-0);user=sa;password=kalle;");
			if (con == null)
				System.out.println("No driver found!");
			else
				System.out.println("Connection Established - SUCCESS!");

		} catch (Exception e) {
			System.out.println("Kaaaaaaaan inte hitta database driver class: " + e);
		}
		return con;

	}
}

********************************************************************************************************
package databaseAccesslayer;

import java.sql.*;
import java.util.ArrayList;
import java.util.Vector;

import connector.CronusDB;
import connector.*;

public class CronusDal {

	String sqlString;

	/**
	 * ********** MetaDataMetoder **********
	 */

	public Vector<String> getStandardMetaData(String from) throws SQLException {
		Vector<String> vectorList = new Vector<String>();

		Connection con = CronusDB.startConnection();

		sqlString = "select * from [" + from + "]";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		ResultSetMetaData rsmd = rs.getMetaData();
		int j = 5;
		for (int i = 1; i <= j; i++) {
			vectorList.add(rsmd.getColumnLabel(i));
		}
		return vectorList;
	}

	public Vector<String> getStatsMetaData(String from) throws SQLException {
		Vector<String> vectorList = new Vector<String>();

		Connection con = CronusDB.startConnection();

		sqlString = "select * from [" + from + "]";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		ResultSetMetaData rsmd = rs.getMetaData();
		int j = rsmd.getColumnCount();
		for (int i = 1; i <= j; i++) {
			vectorList.add(rsmd.getColumnLabel(i));
		}
		return vectorList;
	}

//	public Vector<String> getKeysMetaData() throws SQLException {
//		Vector<String> vectorList = new Vector<String>();
//
//		Connection con = CronusDB.startConnection();
//
//		sqlString = "select table_name, column_name from information_schema.key_column_usage";
//
//		Statement stmt = con.createStatement();
//		ResultSet rs = stmt.executeQuery(sqlString);
//		ResultSetMetaData rsmd = rs.getMetaData();
//		int j = rsmd.getColumnCount();
//
//		for (int i = 1; i <= j; i++) {
//			vectorList.add(rsmd.getColumnLabel(i));
//		}
//		return vectorList;
//	}

//	public Vector<String> getIndexesMetaData() throws SQLException {
//		Vector<String> vectorList = new Vector<String>();
//
//		Connection con = CronusDB.startConnection();
//
//		sqlString = "select * from sys.indexes";
//
//		Statement stmt = con.createStatement();
//		ResultSet rs = stmt.executeQuery(sqlString);
//		ResultSetMetaData rsmd = rs.getMetaData();
//		int j = rsmd.getColumnCount();
//
//		for (int i = 1; i <= j; i++) {
//			vectorList.add(rsmd.getColumnLabel(i));
//		}
//		return vectorList;
//
//	}

	public Vector<String> getConstraintsMetaData() throws SQLException {
		Vector<String> vectorList = new Vector<String>();

		Connection con = CronusDB.startConnection();

		sqlString = "select * from information_schema.table_constraints";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		ResultSetMetaData rsmd = rs.getMetaData();
		int j = rsmd.getColumnCount();
		for (int i = 1; i <= j; i++) {
			vectorList.add(rsmd.getColumnLabel(i));
		}
		return vectorList;
	}

	public Vector<String> getTablesOneMetaData() throws SQLException {
		Vector<String> vectorList = new Vector<String>();

		Connection con = CronusDB.startConnection();

		sqlString = "select * from sys.tables";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		ResultSetMetaData rsmd = rs.getMetaData();

		vectorList.add(rsmd.getColumnLabel(1));

		return vectorList;
	}

	public Vector<String> getTablesTwoMetaData() throws SQLException {
		Vector<String> vectorList = new Vector<String>();

		Connection con = CronusDB.startConnection();

		sqlString = "select * from information_schema.tables";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		ResultSetMetaData rsmd = rs.getMetaData();

		vectorList.add(rsmd.getColumnLabel(3));

		return vectorList;
	}

	public Vector<String> getTableMaxRowsMetaData() throws SQLException {
		Vector<String> vectorList = new Vector<String>();

		Connection con = CronusDB.startConnection();

		sqlString = "select top 1 t.name, p.rows " + "from sys.tables t join sys.partitions p "
				+ "ON t.object_id = p.object_id " + "order by rows desc";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		ResultSetMetaData rsmd = rs.getMetaData();

		int j = rsmd.getColumnCount();

		for (int i = 1; i <= j; i++) {
			vectorList.add(rsmd.getColumnLabel(i));
		}
		return vectorList;
	}

	/**
	 * ********** VanligaDataMetoder **********
	 */

	public Vector<Vector<String>> getStandardData(String from) throws SQLException {
		Vector<Vector<String>> list = new Vector<Vector<String>>();

		Connection con = CronusDB.startConnection();

		sqlString = "select * from [" + from + "];";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		int j = 5;

		while (rs.next()) {
			Vector<String> sa = new Vector<String>();
			for (int i = 1; i <= j; i++) {
				sa.add(rs.getString(i));
			}
			list.add(sa);
		}
		return list;
	}

	public Vector<Vector<String>> getStatGroup(String from) throws SQLException {
		Vector<Vector<String>> list = new Vector<Vector<String>>();

		Connection con = CronusDB.startConnection();

		sqlString = "select * from [" + from + "];";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		int j = 3;

		while (rs.next()) {
			Vector<String> sa = new Vector<String>();
			for (int i = 1; i <= j; i++) {
				sa.add(rs.getString(i));
			}
			list.add(sa);
		}
		return list;
	}

	public Vector<Vector<String>> getKeys() throws SQLException {

		Connection con = CronusDB.startConnection();

		Vector<Vector<String>> list = new Vector<Vector<String>>();

		sqlString = "select table_name, column_name from information_schema.key_column_usage";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		int j = 2;

		while (rs.next()) {
			Vector<String> sa = new Vector<String>();
			for (int i = 1; i <= j; i++) {
				sa.add(rs.getString(i));
			}
			list.add(sa);
		}
		return list;
	}

	public Vector<Vector<String>> getConstraints() throws SQLException {
		Vector<Vector<String>> list = new Vector<Vector<String>>();

		Connection con = CronusDB.startConnection();

		sqlString = "select * from information_schema.table_constraints";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		ResultSetMetaData rsmd = rs.getMetaData();
		int j = rsmd.getColumnCount();

		while (rs.next()) {
			Vector<String> sa = new Vector<String>();
			for (int i = 1; i <= j; i++) {
				sa.add(rs.getString(i));
			}
			list.add(sa);
		}
		return list;
	}

	public Vector<Vector<String>> getTablesOne() throws SQLException {
		Vector<Vector<String>> list = new Vector<Vector<String>>();

		Connection con = CronusDB.startConnection();

		sqlString = "select * from sys.tables";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);

		while (rs.next()) {
			Vector<String> sa = new Vector<String>();

			sa.add(rs.getString(1));

			list.add(sa);
		}
		return list;
	}

	public Vector<Vector<String>> getTablesTwo() throws SQLException {
		Vector<Vector<String>> list = new Vector<Vector<String>>();

		Connection con = CronusDB.startConnection();

		sqlString = "select * from information_schema.tables";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);

		while (rs.next()) {
			Vector<String> sa = new Vector<String>();

			sa.add(rs.getString(3));

			list.add(sa);
		}
		return list;
	}

	public Vector<Vector<String>> getIndex() throws SQLException {
		Vector<Vector<String>> list = new Vector<Vector<String>>();

		Connection con = CronusDB.startConnection();

		sqlString = "select * from sys.indexes";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		ResultSetMetaData rsmd = rs.getMetaData();
		int j = rsmd.getColumnCount();

		while (rs.next()) {
			Vector<String> sa = new Vector<String>();
			for (int i = 1; i <= j; i++) {
				sa.add(rs.getString(i));
			}
			list.add(sa);
		}
		return list;
	}

	// public Vector<Vector<String>> getEmployeeColumnsOne() throws SQLException
	// {
	// Vector<Vector<String>> list = new Vector<Vector<String>>();
	//
	// Connection con = CronusDB.startConnection();
	//
	// sqlString = "select column_name from information_schema.columns "
	// + "where table_name = 'CRONUS Sverige AB$Employee'";
	//
	// Statement stmt = con.createStatement();
	// ResultSet rs = stmt.executeQuery(sqlString);
	// ResultSetMetaData rsmd = rs.getMetaData();
	// int j = rsmd.getColumnCount();
	//
	// while (rs.next()) {
	// Vector<String> sa = new Vector<String>();
	// for (int i = 1; i <= j; i++) {
	// sa.add(rs.getString(i));
	// }
	// list.add(sa);
	// }
	// return list;
	// }
	//
	// public Vector<Vector<String>> getEmployeeColumnsTwo() throws SQLException
	// {
	// Vector<Vector<String>> list = new Vector<Vector<String>>();
	//
	// Connection con = CronusDB.startConnection();
	//
	// sqlString = "select * from [CRONUS Sverige AB$Employee]";
	//
	// Statement stmt = con.createStatement();
	// ResultSet rs = stmt.executeQuery(sqlString);
	// ResultSetMetaData rsmd = rs.getMetaData();
	// int j = rsmd.getColumnCount();
	//
	// for (int i = 1; i <= j; i++) {
	// Vector<String> sa = new Vector<String>();
	// sa.add(rsmd.getColumnLabel(i));
	// list.add(sa);
	// }
	// return list;
	// }

	public Vector<Vector<String>> getTableMaxRows() throws SQLException {
		Vector<Vector<String>> list = new Vector<Vector<String>>();

		Connection con = CronusDB.startConnection();

		sqlString = "select top 1 t.name, p.rows " + "from sys.tables t join sys.partitions p "
				+ "on t.object_id = p.object_id " + "order by rows desc;";

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		ResultSetMetaData rsmd = rs.getMetaData();
		int j = rsmd.getColumnCount();

		while (rs.next()) {
			Vector<String> sa = new Vector<String>();
			for (int i = 1; i <= j; i++) {
				sa.add(rs.getString(i));
			}
			list.add(sa);
		}
		return list;
	}
	// public void openFile(String fileName)throws Exception{
	// File filePath = new File("C:\\Users\\toaap\\Documents\\Databasfiler\\" +
	// fileName + "");
	// Desktop.getDesktop().open(filePath);
	// }

}

********************************************************************************************************
********************************************************************************************************