glennnnnnnn

//package databasedemo;
import java.sql.*;

/**
 *
 * @author tha
 */
public class DataBaseDemo {

    private static Connection Connection;

    public static void main(String[] args) /*throws Exception*/ {
       try {
           doConnect();


           /*x = stmt.executeUpdate("CREATE TABLE Customer(custID INTEGER, custName VARCHAR, custValue NUMBER, custLastName VARCHAR, Primary Key (custID));");
           System.out.println(x);

           x = stmt.executeUpdate("INSERT INTO Customer VALUES(1,'Søren', 1,'Hansen');");
           System.out.println(x);

           x = stmt.executeUpdate("DROP TABLE Customer;");
           System.out.println(x);*/


           //Create Car Table

          /*x = stmt.executeUpdate("CREATE TABLE  Car (reg_no VARCHAR, color VARCHAR, year INTEGER, price INTEGER);");
           System.out.println("create:" + x);*/


           /*for (int i = 0; i<3;i++) {
               x = stmt.executeUpdate("INSERT INTO Car VALUES('VG51538975','silver',2000,215430)");
               System.out.println(x);

               x = stmt.executeUpdate("INSERT INTO Car VALUES('CR48543214','bordeux',1997,9000)");
               System.out.println(x);
           }*/

           /*x = stmt.executeUpdate("UPDATE Car SET color = 'NoneOfTheAbove' WHERE color='silver'");
           System.out.println(x);*/

           /*x = stmt.executeUpdate("DELETE * FROM Car WHERE color = 'NoneOfTheAbove'");
           System.out.println(x);*/


           //Get first column data for all Cars
           /*ResultSet rs = stmt.executeQuery("SELECT * from Car");
           rs.next();
           System.out.print(rs.getString(2));*/

           /*ResultSet rs = stmt.executeQuery("SELECT * from Car");
           ResultSetMetaData rsmd = rs.getMetaData();
           while (rs.next()){
               for (int i = 1; i<=rsmd.getColumnCount(); i++){
                   System.out.print(rs.getString(i)+"\t");
               }
               System.out.print("\n");
           }*/

           //x = stmt.executeUpdate("DROP CAR");


           /*String query = "INSERT INTO Car VALUES(?,?,?,?)"; //string, string, int, int
           long first = System.currentTimeMillis();
           for (int i = 0; i< 9000; i++ ) {
               PreparedStatement ps = con.prepareStatement(query);
               ps.setString(1, "Hulika");
               ps.setString(2, "buja");
               ps.setInt(3, 34);
               ps.setInt(4, 1900);
               ps.executeUpdate();
           }
           con.commit();
           long second = System.currentTimeMillis();
           System.out.println("PS " + (first-second));

           con.setAutoCommit(true);
           long firstt = System.currentTimeMillis();
           for (int i = 0; i< 9000; i++ ) {
               x = stmt.executeUpdate("INSERT INTO Car VALUES('Hulika','buja',34,1900)");
           }
           long secondd = System.currentTimeMillis();
           System.out.println("Normal " + (firstt-secondd));*/


           /*String glennsquery = "SELECT City.city, City.zip, Customer.customerName FROM City INNER JOIN Customer ON City.zip = Customer.zip;";

           PreparedStatement ps = con.prepareStatement(glennsquery);
           /*ps.setString(1, "City");
           ps.setString(2, "Customer");
           ps.setString(3, "City.zip");
           ps.setString(4, "Customer.zip");*/
           /*ResultSet rs = ps.executeQuery();

           rs.next();

           ResultSetMetaData rsmd = rs.getMetaData();
           while (rs.next()){
               for (int i = 1; i<=rsmd.getColumnCount(); i++){
                   System.out.print(rs.getString(i)+"\t");
               }
               System.out.print("\n");
           }*/



           /*
           String catalog = null;
           String schemaPattern = null;
           String tableNamePattern = null;
           String []  tableTypes = {"TABLE"};
           String tableNamePattern2 = null;
           String tableNamePattern3 = null;

           System.out.println("Major jdbc driver version: "+dbmd.getDriverMajorVersion());
           System.out.println("Minor jdbc driver version: "+dbmd.getDriverMinorVersion());
           System.out.println(dbmd.getDriverName());

           ResultSet rs = dbmd.getTables(catalog,schemaPattern,tableNamePattern,tableTypes);


           // (4) List columnNames
           // ResultSet rs = dbmd.getColumns(catalog,schemaPattern,tableNamePattern,columnNamePattern);
           // String columnNamePattern = null; + tableName

           while(rs.next()) {
               System.out.println(rs.getString(3));  // Why 3 ???
               System.out.println(rs.getString(4));
               System.out.println(rs.getString(6));
           }
           */


            /*
           System.out.println("ColumnName " +rsmd.getColumnName(1));
           System.out.println("ColumnLabel " +rsmd.getColumnLabel(1));
           System.out.println("CatalogName " +rsmd.getCatalogName(1));
           System.out.println("ColumnType " +rsmd.getColumnType(1));
           System.out.println("ColumnTypeName " +rsmd.getColumnTypeName(1));
           System.out.println("TableName " +rsmd.getTableName(1));
           */

            BactchJob();



           closeConnection();
       }catch (Exception e){
            System.out.println("2 bad bro, you dun guuf'd");
       }
    }

    private static void BactchJob(){
        try{
            Statement stmt = Connection.createStatement();

            String sqlDropAll = "DROP TABLE Orderr, Customer, Product, City;";

            System.out.println(stmt.executeUpdate(sqlDropAll));

            String sqlCreateCity = "CREATE TABLE City (zip INT, city VARCHAR(20), PRIMARY KEY (zip) );";

            String sqlCreateProduct = "CREATE TABLE Product (productID INT, description VARCHAR, PRIMARY KEY (productID));";

            String sqlCreateCustomer = "CREATE TABLE Customer (customerID INT, customerName VARCHAR, zip INT REFERENCES City(zip), PRIMARY KEY (customerID));";

            String sqlCreateOrder = "CREATE TABLE Orderr (orderID INT, customerID INT REFERENCES Customer(customerID), productID INT REFERENCES Product(productID), PRIMARY KEY (orderID));";

            String sqlInsertPleb = "INSERT INTO City VALUES(6400, 'Sønderborg')";

            String sqlInsertPlebNavn = "INSERT INTO Customer VALUES(1, 'Glenn', 6400);";

            String sqlInsertPlebProduct = "INSERT INTO Product VALUES(1, 'Glenns Dildo');";

            String sqlInsertPlebOrder = "INSERT INTO Orderr VALUES(1, 1, 1);";

            stmt.addBatch(sqlCreateCity);
            stmt.addBatch(sqlCreateProduct);
            stmt.addBatch(sqlCreateCustomer);
            stmt.addBatch(sqlCreateOrder);
            stmt.addBatch(sqlInsertPleb);
            stmt.addBatch(sqlInsertPlebNavn);
            stmt.addBatch(sqlInsertPlebProduct);
            stmt.addBatch(sqlInsertPlebOrder);
            int [] status = stmt.executeBatch();

            for(int i = 0; i < status.length; i++){
                System.out.println("status of batch no "+i+ " : "+ status[i]);
            }
        }catch (Exception e){
            System.out.println("2 bad bro, you dun guuf'd");
        }
    }

    private static void PrepResultSet(){
        try {
            String selectSQL = "SELECT * FROM Car WHERE Colour = ?";
            PreparedStatement preparedStatement = Connection.prepareStatement(selectSQL);
            preparedStatement.setString(1, "green");
            ResultSet rs = preparedStatement.executeQuery(selectSQL);
            while (rs.next()) {
                String userid = rs.getString("Make");
                String username = rs.getString("Model");
            }
        }catch (Exception e){
            System.out.println("2 bad bro, you dun guuf'd");
        }
    }

    private static void GlennsMethod(){
        try{
        Statement stmt = Connection.createStatement();
        String selectStatement = "UPDATE Car SET Price=Price+? WHERE Colour=?";
        String selectminusStatement = "UPDATE Car SET Price=Price-? WHERE Colour=?";
        PreparedStatement ps = Connection.prepareStatement(selectStatement);
        PreparedStatement ps2 = Connection.prepareStatement(selectminusStatement);
        //ps.setString(1,"5");
        //ResultSet rs = ps.executeQuery(selectStatement);

        ps.setDouble(1,6000.0);
        ps.setString(2, "yellow");
        ps.executeUpdate();

        ps2.setDouble(1, 5000.0);
        ps2.setString(2,"green");
        ps2.executeUpdate();
        Connection.commit();
        }
        catch (SQLException e){
            try {
                Connection.rollback();
            }catch (Exception k){

            }
        }
    }

    private static void doConnect(){
        try{
            // Load Driver
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            System.out.println("Driver Loaded");

            // Create Connection
            //FoxeyJr
            //String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:/Users/FoxeyJr/Dropbox/Datamatiker/2. Semester/Datamatiker 2. Semester/SKO/Tommy/Database1.accdb"; //Type of databse, path to database
            String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:/Users/Simon/Dropbox/Datamatiker/2. Semester/Datamatiker 2. Semester/SKO/Tommy/Database1.accdb"; //Type of databse, path to database
            Connection = DriverManager.getConnection(url);
            Connection.setAutoCommit(false);
            System.out.println("Connected to database\n");}
        catch(Exception e){
            System.out.println("Couldn't connect to Database");
        }
    }

    private static void closeConnection(){
        try {
            Connection.setAutoCommit(true);
            Connection.close();
        }catch (Exception e){
            System.out.println("Couldn't close connection\n");
        }
    }


}