package testsql;

import java.sql.*;

/**
 * @author Linglom
 */

public class testsql {
    
    private static Statement stmt = null;
    private static ResultSet rs = null;
    private static Connection con = null;
    
    public static void main(String[] args) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            
            /* Connection string to connect to a remote server.
             * Change the information to match your environment(hostname:port, username and password).
             */
            String connectionUrl = "jdbc:sqlserver://bkkpdc01:1433;" +
                                    "databaseName=Northwind;user=sa;password=password";
            
            con = DriverManager.getConnection(connectionUrl);
            stmt = con.createStatement();
            
            retriveData("SELECT * FROM Products");
            
            int rowsEffected = 0;
            
            // Example INSERT new record
            rowsEffected = updateData("INSERT INTO Products (ProductName,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder," +
                            "ReOrderLevel,Discontinued) VALUES ('MyProduct','10 Kg.',1234.0000,100,50,30,0)");
            System.out.println(rowsEffected + " rows effected");
            System.out.print("Insert new record : ");
            retriveData("SELECT * FROM Products WHERE ProductName = 'MyProduct'");
            
            // Example UPDATE the record
            rowsEffected = updateData("UPDATE Products SET UnitPrice = 900, UnitsInStock = 55, UnitsOnOrder = 5" +
                            "WHERE ProductName = 'MyProduct'");
            System.out.println(rowsEffected + " rows effected");
            System.out.print("Update the record : ");
            retriveData("SELECT * FROM Products WHERE ProductName = 'MyProduct'");
            
            // Example DELETE the record
            rowsEffected = updateData("DELETE FROM Products WHERE ProductName = 'MyProduct'");
            System.out.println(rowsEffected + " rows effected");
            System.out.print("Delete the record");
            retriveData("SELECT * FROM Products WHERE ProductName = 'MyProduct'");
            
        } catch (SQLException sqlEx) {
            System.out.println("SQL Exception: "+ sqlEx.toString());
        } catch (ClassNotFoundException classEx) {
            System.out.println("Class Not Found Exception: "+ classEx.toString());
        } catch (Exception Ex) {
            System.out.println("Exception: "+ Ex.toString());
        }
    }
    
    /* Update data on the database
     *@param SQL an update commandstring (INSERT, DELETE, UPDATE)
     */
    public static int updateData(String SQL) throws Exception {
        return stmt.executeUpdate(SQL);
    }
    
    /* Show result on output window
     @param SQL a retrive data command string (SELECT)
     */
    public static void retriveData(String SQL) throws Exception {
        rs = stmt.executeQuery(SQL);

        while (rs.next()) {
            System.out.println(rs.getString("ProductName") + " : " + rs.getString("QuantityPerUnit") + " : " + 
                rs.getString("UnitPrice") + " : " + rs.getString("UnitsInStock") + " : " + 
                rs.getString("UnitsOnOrder") + " : " + rs.getString("ReOrderLevel") + " : " + rs.getString("Discontinued"));
            }
    }  
}