dankSQL

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class DBAccess {
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	public void readDataBase() throws Exception {
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager
					.getConnection("jdbc:mysql://localhost/cs435_lab4?"
							+ "user=final&password=project");

			// Statements allow to issue SQL queries to the database
			statement = connect.createStatement();

			question1(statement);



			/*
			// Result set get the result of the SQL query
			resultSet = statement
					.executeQuery("select * from bus");
			writeResultSet(resultSet);


			// PreparedStatements can use variables and are more efficient
			preparedStatement = connect
					.prepareStatement("insert into  feedback.comments values (default, ?, ?, ?, ? , ?, ?)");
			// "myuser, webpage, datum, summery, COMMENTS from feedback.comments");
			// Parameters start with 1
			preparedStatement.setString(1, "Test");
			preparedStatement.setString(2, "TestEmail");
			preparedStatement.setString(3, "TestWebpage");
			preparedStatement.setDate(4, new java.sql.Date(2009, 12, 11));
			preparedStatement.setString(5, "TestSummary");
			preparedStatement.setString(6, "TestComment");
			preparedStatement.executeUpdate();

			preparedStatement = connect
					.prepareStatement("SELECT myuser, webpage, datum, summery, COMMENTS from feedback.comments");
			resultSet = preparedStatement.executeQuery();
			writeResultSet(resultSet);

			// Remove again the insert comment
			preparedStatement = connect
					.prepareStatement("delete from feedback.comments where myuser= ? ; ");
			preparedStatement.setString(1, "Test");
			preparedStatement.executeUpdate();
			resultSet = statement
					.executeQuery("select * from trip");
			writeMetaData(resultSet);
			*/


		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}

	}

	private ResultSet question1(Statement statement) throws SQLException{
		String startName = "Pittsburgh";
		String destName = "Canada";
		String date = "2/19/12";

		ResultSet result = statement.executeQuery("SELECT t.StartLocationName, t.DestinationName, tof.TripDate, tof.ScheduledStartTime, tof.ScheduledArrivalTime, tof.DriverName, tof.BusID "+
												  "FROM trip t, tripoffering tof "+
												  "WHERE t.TripNumber = tof.TripNumber "+
												  "AND t.StartLocationName = '" + startName +"' "+
												  "AND t.DestinationName = '" + destName + "' " +
												  "AND tof.TripDate = '" + date + "'");
		writeResultSet(result);
		return result;
	}

	private ResultSet question2Add(Statement statement) throws SQLException{

		String tripNumber = "1";
		String date = "2/19/12";
		String schedStartTime = "12:00PM";
		String schedArrivalTime = "2:00PM";
		String driverName = "Bob";
		String busId = "1";

		ResultSet result = statement.executeQuery("SELECT t.StartLocationName, t.DestinationName, tof.TripDate, tof.ScheduledStartTime, tof.ScheduledArrivalTime, tof.DriverName, tof.BusID "+
												  "FROM trip t, tripoffering tof "+
												  "WHERE t.TripNumber = tof.TripNumber "+
												  "AND t.StartLocationName = '" + startName +"' "+
												  "AND t.DestinationName = '" + destName + "' " +
												  "AND tof.TripDate = '" + date + "'");
		writeResultSet(result);
		return result;
	}

	private void writeMetaData(ResultSet resultSet) throws SQLException {
		// Now get some metadata from the database
		// Result set get the result of the SQL query

		System.out.println("The columns in the table are: ");

		System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
		for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
			System.out.println("Column " + i + " "
					+ resultSet.getMetaData().getColumnName(i));
		}
	}

	private void writeResultSet(ResultSet resultSet) throws SQLException {
		// ResultSet is initially before the first data set
		ResultSetMetaData rsmd = resultSet.getMetaData();

		int totalColumns = rsmd.getColumnCount();

		for(int j = 1; j <= totalColumns; j++){
			System.out.printf("%30s", rsmd.getColumnName(j));
		}

		while (resultSet.next()) {
			System.out.println("");
			for(int i = 1; i <= totalColumns; i ++){
				System.out.printf("%30s", resultSet.getObject(i).toString());
			}
		}
	}

	// You need to close the resultSet
	private void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}
}