// -------------------------------------------------------------------
//
// Example on how to connect to a SQL Server using JDBC
// 
// 
//
// About JDBC
//  http://java.sun.com/javase/technologies/database/
//  http://en.wikipedia.org/wiki/JDBC
//
// Microsoft SQL Server JDBC Driver
//  http://msdn.microsoft.com/en-us/data/aa937724.aspx
//
// JDBC driver for MySQL
//  http://dev.mysql.com/downloads/connector/ 
//
// Instruction
//  1) Download an appropriate JDBC-driver.
//  2) Download this java test program.
//  3) Compile program to java bytes-code (.class-file).
//     $ javac ExampleJDBC.java
//  4) Run program with a correct classpath.
//     $ java -classpath ".:sqljdbc_2.0/enu/sqljdbc.jar" ExampleJDBC 
//
//

import java.sql.*;

public class ExampleJDBC
{

  // ------------------------------------------------------------------------------
  //
  // Main method
  //
  public static void main(String[] args)
  {
    ExampleJDBC app = new ExampleJDBC();
    
		System.out.println("Main(): Starting example program, get ready.");      

		System.out.println("Main(): Connecting to the database.");      
    app.SetupDatabaseConnectionMSSQLServer();

		System.out.println("Main(): Make a SELECT query.");      
    app.DoSelectQuery();
		System.out.println("Main(): Make a INSERT query.");      
    app.DoInsertQuery("Ford", "99", 109);
		System.out.println("Main(): Make a SELECT query again.");      
    app.DoSelectQuery();
  }

    
  // ------------------------------------------------------------------------------
  //
  // Member variables
  //
  private Connection iConn;

  
  // ------------------------------------------------------------------------------
  //
  // Constructor
  //
  public ExampleJDBC()
  {	
  }

  // ------------------------------------------------------------------------------
  //
  // Set up and connect to MS SQL Server database
  // Review the driver's manual for example on connection strings
  //
  private void SetupDatabaseConnectionMSSQLServer()
  {
    try
    {
 			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
 
			String server   = "jdbc:sqlserver://194.47.135.20:1433;";
      String database = "databaseName=dv1219_09_0;";
      String user     = "user=dv1219_09_0;";
      String password = "password=**********;";
		
			String connectionUrl = server + database + user + password;

      System.out.print("SetupDatabaseConnectionMSSQLServer(): Connecting using: " + connectionUrl + " ...");
      this.iConn = DriverManager.getConnection(connectionUrl);
      System.out.println(" Done.");    
    }
    catch (Exception e)
    {
      System.out.println("Caught exception: " + e);
    } 
  }


  // ------------------------------------------------------------------------------
  //
  // Perform a SELECT query
  //
  private void DoSelectQuery()
  {
    try
    {
      Statement stmt 	= iConn.createStatement();
      String query;
			ResultSet res;
			ResultSetMetaData meta;
			
			query = "SELECT nameCars AS name, modelCars AS model FROM Cars";
      System.out.println("DoSelectQuery(): Performing query: " + query);          
      res = stmt.executeQuery(query);
      meta = res.getMetaData();
      System.out.println("DoSelectQuery(): Found number of columns: " + meta.getColumnCount());      
                         
      System.out.println("DoSelectQuery(): Printing resultset");      
      while (res.next())
      {
        System.out.println(res.getRow() + ": " + res.getString("name") + res.getString("model"));
      }

      res.close();
      stmt.close();
    }
    catch (Exception e)
    {
      System.out.println("Caught exception: " + e);
    } 
  }

  
  // ------------------------------------------------------------------------------
  //
  // Do a INSERT/UPDATE query
  //
  private void DoInsertQuery(String aName, String aModel, int aHours)
  {
    try
    {
      Statement stmt = iConn.createStatement();
      String query;

			query = "INSERT INTO Cars VALUES ('" + aName + "', '" + aModel + "', getdate(), " + aHours + ")";
      System.out.println("DoInsertQuery(): Performing query: " + query);	
      stmt.executeUpdate(query);
      System.out.println("DoInsertQuery(): Rows affected: " + stmt.getUpdateCount());
      
      //
      // Call using a stored procedure
      //
      //stmt.executeUpdate("exec SPInsertProducedCar @aModel='" + aName + "', @aHours=" + aHours + "");

      //
      // Call using a stored procedure which returns a resultset
      //
      //ResultSet res = stmt.executeQuery("exec test");
      //while (res.next())
      //{
      //  System.out.println(res.getRow() + ": " + res.getString("nameCars") + res.getString("modelCars"));
      //}
      //res.close();

      stmt.close();
   }
   catch (Exception e)
   {
      System.out.println("Caught exception: " + e);
   } 
  }

}