//The source of this sample code is
//http://www.tutorialspoint.com/jdbc/jdbc-sample-code.htm

//STEP 1. Import required packages
import java.sql.*;
import java.util.Scanner;

public class FirstExample {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/school";

    //  Database credentials
    static final String USER = "root";
    static final String PASS = "password";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try{
            //STEP 2: Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            //STEP 3: Open a connection
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);

            //STEP 4: Execute a query
            System.out.println("Creating statement...");
            stmt = conn.createStatement();

            String sql;
            sql = "CREATE TABLE Students(firstName VARCHAR(20),lastName VARCHAR(20), ssn VARCHAR(9),major VARCHAR(20))";
            stmt.executeUpdate(sql);

            Scanner in = new Scanner(System.in);

            for(int i=0;i<4;i++)
            {
                String first="";
                String last="";
                String ssn= "";
                String major="";

                System.out.println("Enter a first name");
                first = in.next();
                System.out.println("Enter a last name");
                last = in.next();
                System.out.println("Enter the 9 digit SSN");
                ssn = in.next().substring(0, 9);
                System.out.println("Enter a major");
                major = in.next();

                sql = "INSERT INTO Students VALUES('"+first+"','"+last+"','"+ssn+"','"+major+"');";

                stmt.executeUpdate(sql);
            }

            sql = "SELECT * FROM Students";

            ResultSet rs = stmt.executeQuery(sql);

            //STEP 5: Extract data from result set
            while(rs.next()){
                //Retrieve by column name
                String first    = rs.getString("firstName");
                String last     = rs.getString("lastName");
                String ssn      = rs.getString("ssn");
                String major    = rs.getString("major");
                //Display values
                System.out.println("Student:"+first+" "+last+ " :"+ssn+" : "+major);
            }
            //STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
        }catch(SQLException se){
            //Handle errors for JDBC
            se.printStackTrace();
        }catch(Exception e){
            //Handle errors for Class.forName
            e.printStackTrace();
        }finally{
            //finally block used to close resources
            try{
                if(stmt!=null)
                    stmt.close();
            }catch(SQLException se2){
            }// nothing we can do
            try{
                if(conn!=null)
                    conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }//end finally try
        }//end try
        System.out.println("Goodbye!");
    }//end main
}//end FirstExample