package gdb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class JDBCClass {
	
	/*
	 * sDriver enthält den Datenbanktreiber
	 * für DB2: com.ibm.db2.jcc.DB2Driver
	 * für SQLServer: com.microsoft.sqlserver.jdbc.SQLServerDriver
	 * für MaxDB: com.sap.dbtech.jdbc.DriverSapDB
	 */
	private static String sDriver = "DATABASE_DRIVER";
	/*
	 * sURL enthält den JDBC-Connection String
	 * für DB2: jdbc:db2://<host>:<port>/<database>
	 * für SQLServer: jdbc:sqlserver://<host>\<database>:<port> (default port: 1433)
	 * für MaxDB: jdbc:sapdb://<host>[:<port>]/<database> (port nötig falls anders als default)
	 */
	private static String sURL = "DATABASE_URL";
	
	/*
	 * sUser ist der Datenbankbenutzer, sPW das Passwort
	 * für DB2: Anmeldedaten für das Betriebssystem
	 * für SQLServer: Daten des angelegten Users
	 * für MaxDB: Daten des angelegten Users
	 */
	private static String sUser = "USER";
	private static String sPW = "PW";
	
	private static Connection m_dbConnection;
	private static Statement m_statement;
	
	
	private static String sSQL = "with VorlesungsSWS as (select p.PersNr, " + 
					"(case when sum(v.SWS) is null then 0 else sum(v.SWS) end) as AnzSWS " +
					"from Professoren p left outer join Vorlesungen v on p.PersNr = v.gelesenVon " +
					"group by p.PersNr), " + 
					"PrüfungsSWS as (select p.PersNr, " + 
					"(case when count(*) is null then 0 else 0.5 * count(*) end) as AnzPrüfSWS " +
					"from Professoren p left outer join prüfen pr on p.PersNr = pr.PersNr " +
					"group by p.PersNr) " +
         
					"select p.PersNr, p.Name, vorl.AnzSWS + prüf.AnzPrüfSWS as Betreuungsleistung " +
					"from Professoren p, VorlesungsSWS vorl, PrüfungsSWS prüf " +
					"where p.PersNr = vorl.PersNr and p.PersNr = prüf.PersNr";
	
	
	private static void connect2DB() {
		try {
			if (m_dbConnection != null && !m_dbConnection.isClosed())
				return;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		try {
			Class.forName(sDriver);
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		try {
			m_dbConnection = DriverManager.getConnection(sURL, sUser, sPW);
			m_statement = m_dbConnection.createStatement();
			m_dbConnection.setAutoCommit(true);
		} catch (SQLException e2) {
			e2.printStackTrace();
			System.exit(-1);
		}
	}
	
	private static void disconnect() {
		try {
			m_statement.close();
			m_dbConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private static void submitQuery(String query) {
		try {
			ResultSet rs = m_statement.executeQuery(query);
			System.out.println("Betreuungsleistung der Professoren:");
			System.out.println("___________________________________\n");
			while(rs.next()) {
				System.out.println(rs.getString(2) + " (PersNr " + rs.getInt(1) + "): " + rs.getFloat(3) + "SWS");
			}
			System.out.println("___________________________________\n");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		connect2DB();
		submitQuery(sSQL);
		disconnect();
	}

}