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

/**
   Example of a complete version of the Java program to access
   Mr. Frumble's data table on IISQLSRV, using JDBC.

   @author Michael Ratanapintha
 */
class JdbcSection4_Solution
{
	static final String CONNECTION_URL = 
		"jdbc:sqlserver://iisqlsrv.cs.washington.edu;database=michaelr";
	static final String USERNAME = "michaelr";
	static final String PASSWORD = "Complex_PASS";
	
	public static void main (String argv[]) throws Exception
	{
		// next line is not needed in recent versions of Java
		// Class.forName ("com.microsoft.sqlserver.jdbc.SQLServerDriver");

		// getConnection() parses the connection URL to find and load
		// the correct driver for this DBMS
		System.out.println("Connecting to IISQLSRV...");
		Connection conn = DriverManager.getConnection (
			CONNECTION_URL,
			USERNAME,
			PASSWORD
		);
		System.out.println("... connected");
		System.out.println();
		
		runStaticQuery(conn);
		runUpdate(conn);
		runParameterizedQuery(conn);
		
		// Close() all JDBC objects when done, to release the
		// associated resources on the database server.
		// This is done automatically when the Java objects are
		// garbage-collected, but doing it early is a good idea
		// on a heavily-used database server
		conn.close();
	}

	// List all the sales in February	
	/** 
		Demonstrates how to run a non-parameterized SQL query 
		using java.sql.Statement, and to examine the query results 
		using java.sql.ResultSet.
	 */
	static void runStaticQuery (Connection conn) throws SQLException
	{
		System.out.println("Mr. Frumble made the following sales in February:");
		// Create a Statement object to run SQL code on this connection
		Statement stmt = conn.createStatement();
		
		// Statement.executeQuery() takes a SQL string, which *must*
		// return a result tuple set - otherwise, an exception is thrown!
		// Queries always return a result set, albeit sometimes an empty one; 
		// updates generally don't.  
		ResultSet rs = stmt.executeQuery (
			// Extra space at end of each string - helps prevent accidental run-on
			"SELECT name, discount, price " +
			"FROM hw1_data " +
			"WHERE month = 'feb' "
		);
		
		// Result set "cursor" starts before the first tuple.
		// ResultSet.next() advances the cursor and returns true,
		// or returns false if no more tuples.
		while (rs.next()) {
			// Use ResultSet.getXXX("attr") to get the "attr" attribute 
			// from current tuple as Java type XXX
			String productName = rs.getString("name");
			
			// We can also index the attributes, starting from 1 (not 0!)
			String discount = rs.getString(2);
			int price = rs.getInt(3);
			
			System.out.printf("%s for $%d (discount: %s)", 
				productName, price, discount);
			System.out.println();
		}
		// Remember to close the RecordSet, as well as the Statement 
		// if you don't want to reuse it by calling executeQuery() on 
		// another SQL string.
		rs.close();
		stmt.close();
		System.out.println();
	}
	
	// Change product "gizmo3"'s name to "jane's super gizmo"
	/** 
		Demonstrates how to run a non-parameterized SQL database update,
		and the proper way to ensure that JDBC objects get closed.
	 */
	static void runUpdate (Connection conn) throws SQLException
	{
		// Using the "finally" block, we ensure that the Statement
		// is closed even if an exception interrupts the "try" block.
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			
			// Statement.executeUpdate() returns the number of
			// rows affected, or 0 when there is no meaningful
			// "rows affected" count.  If there is a result tuple set,
			// then executeUpdate() throws an exception!
			int cRowsAffected = stmt.executeUpdate (
				"UPDATE hw1_data " +
				// Use two consecutive single quotes ' 
				// to escape a single one in SQL
				"SET name = 'jane''s super gizmo' " +
				"WHERE name = 'gizmo3' "
			);
			
			System.out.println (cRowsAffected + 
				" rows were updated from \"gizmo3\" to \"jane's super gizmo\"");
			System.out.println();
		} finally {
			if (stmt != null) stmt.close();
		}
	}

	// List all the sales in April, May, and June
	// (separately for each month)
	/**
	   Demonstrates running a SQL query with runtime-determined
	   parameters, by using java.sql.PreparedStatement.
	 */
	static void runParameterizedQuery (Connection conn) throws SQLException
	{
		// A PreparedStatement represents SQL code that is sent to the
		// database server for "compilation" prior to requesting
		// execution.  PreparedStatements can have "parameters"
		// that can be changed for each execution.
		PreparedStatement pstmt = null;
		try {
			// ? in the SQL code marks a parameter
			// prepareStatement() works for both queries and updates;
			// the check and exception throw are done at runtime.
			pstmt = conn.prepareStatement (
				"SELECT name, discount, price " +
				"FROM hw1_data " +
				"WHERE month = ? "
			);
			// Why can't we just concatenate the parameter value
			// to the SQL string each time we run the query?
			//
			// If the parameter value is not controlled by you,
			// blindly concatenating it into your SQL string
			// could cause your program to run SQL code you
			// didn't intend, aka a "SQL injection".
			//
			// Reference: http://xkcd.com/327/
			
			for (String month : new String[] {"apr", "may", "jun"}) { 
				// Use setXXX(N, VAL) to set parameter N (numbered from 1)
				// to VAL of type XXX.
				pstmt.setString(1, month);
				
				// Can call both executeQuery() and executeUpdate() as needed;
				// no SQL string needed
				ResultSet rs = pstmt.executeQuery();
				
				System.out.println("In month " + month +
					", Mr. Frumble made the following sales:");
				while (rs.next()) {
					String productName = rs.getString("name");
					String discount = rs.getString(2);
					int price = rs.getInt(3);
			
					System.out.printf("%s for $%d (discount: %s)", 
						productName, price, discount);
					System.out.println();
				}
				rs.close();
				System.out.println();
			}
		} finally {
			if (pstmt != null) pstmt.close();
		}
	}
}