package cs160.jdbcdemo;

import java.sql.*;

public class JDBCDemo
{
    private static final String DRIVER =
                             "org.gjt.mm.mysql.Driver";
    private static final String URL =
                             "jdbc:mysql://localhost/school";

    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";

    private Connection conn;
    private PreparedStatement classesTaught;

    private void openConnection() throws Exception
    {
        Class.forName(DRIVER).newInstance();
        conn = DriverManager.getConnection(URL,
                                           USERNAME, PASSWORD);

        classesTaught = conn.prepareStatement(
            "SELECT code, subject " +
            "FROM teacher, class " +
            "WHERE last = ? AND first = ? " +
            "AND id = teacher_id");
    }

    public void classesTaughtBy(String last, String first)
        throws SQLException
    {
        // Set query parameters.
        classesTaught.setString(1, last);
        classesTaught.setString(2, first);

        System.out.println("\nThe classes taught by " +
                           first + " " + last + ":\n");

        // Do the query.
        ResultSet rs = classesTaught.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();

        int colCount = rsmd.getColumnCount();

        // Print column headers.
        for (int i = 0; i < colCount; ++i) {
            String header = rsmd.getColumnName(i + 1);
            System.out.print(header.toUpperCase() + "\t");
        }
        System.out.println();

        // Print values.
        while (rs.next()) {
            for (int i = 0; i < colCount; ++i) {
                String value = rs.getString(i + 1);
                System.out.print(value + "\t");
            }
            System.out.println();
        }
    }

    private void closeConnection()
    {
        if (conn != null) {
            try {
                conn.close();
            }
            catch (SQLException ignore) {}
        }
    }

    public static void main(String[] args)
    {
        JDBCDemo demo = new JDBCDemo();

        try {
            demo.openConnection();
            demo.classesTaughtBy("Lane", "John");
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
        finally {
            demo.closeConnection();
        }
    }
}