asgas

package pl.edu.pw.elka.bd.lab6;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
//import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//import java.util.Scanner;

import oracle.jdbc.pool.OracleDataSource;

/**
 * BD.A lab 6 JDBC Showcase.
 *
 * @author B.Twardowski <B.Twardowski@ii.pw.edu.pl>
 *
 */
public class JDBCShowcase {

    // const's
    private static String JDBC_URL = "jdbc:oracle:thin:kromasev/kromasev@ora3.elka.pw.edu.pl:1521:ora3inf";

    private OracleDataSource ods;
    private Connection conn;
    //private Scanner inputScanner = new Scanner(System.in);

    /**
     * Initializing DB connection.
     *
     * @throws SQLException
     */
    private void init() throws SQLException {
        ods = new OracleDataSource();
        ods.setURL(JDBC_URL);
        conn = ods.getConnection();
        DatabaseMetaData meta = conn.getMetaData();
        System.out.println("Successfully connected to DB. JDBC driver version is " + meta.getDriverVersion());

    }

    /**
     * Closing DB connection.
     *
     * @throws SQLException
     */
    private void close() throws SQLException {
        conn.close();
        System.out.println();
        System.out.println("Database connection successfully closed.");
    }

    /**
     * Showcase.
     *
     * @throws SQLException
     */
    public void doShowcase() throws SQLException {
        init();
        insert();
        delete();
        transaction1();
        transaction2();
        select();
        close();
    }

    /**
     * Simple select statement.
     *
     * @throws SQLException
     */
    private void select() throws SQLException {

        // Create a statement
        Statement stmt = conn.createStatement();

        // Execute SQL
        ResultSet rset = stmt.executeQuery("Select nazwa,cena from zamowienie where cena < 17");

        System.out.println();
        System.out.println("Select nazwa,cena from zamowienie: ");

        int i = 1;
        while (rset.next()) {
            System.out.println("[" + i + "]:" + rset.getString(1) + " " + rset.getString(2));
            i++;
        }

        rset = stmt.executeQuery("Select nazwisko,imie from uzytkownik where nazwisko = 'Kruk'");

        System.out.println();
        System.out.println("Select nazwisko,imie from uzytkownik where nazwisko = 'Kruk': ");

        i = 1;
        while (rset.next()) {
            System.out.println("[" + i + "]:" + rset.getString(1) + " " + rset.getString(2));
            i++;
        }

        // close the result set, the statement and connect
        rset.close();
        stmt.close();
    }

    /**
     * Simple select statement.
     *
     * @throws SQLException
     */
    private void insert() throws SQLException {

        // Create a statement
        Statement stmt = conn.createStatement();

        // insert the data
        stmt.executeUpdate("INSERT INTO skladnik " + "VALUES (11, 'Pomarancze', 'Biedronka', 4, 0)");

        System.out.println();
        System.out.println("Inserted into skladnik (11, Pomarancze, Biedronka, 4, 0)");

        stmt.close();
    }

    private void delete() throws SQLException {

        // Create a statement
        Statement stmt = conn.createStatement();

        // delete the data
        stmt.executeUpdate("DELETE FROM skladnik WHERE id = 10");

        System.out.println();
        System.out.println("Deleted from skladnik where ID = 10 ");

        stmt.close();
    }



    /**
     * Transaction showcase.
     *
     * @throws SQLException
     */
    private void transaction1() throws SQLException {

        System.out.println();
        System.out.println("Transaction 1 ");

        conn.setAutoCommit(false);
        Statement stmt = conn.createStatement();

        String SQL = "Update uzytkownik SET Nazwisko = 'Kruk' WHERE Nazwisko = 'Cook'";
        stmt.executeUpdate(SQL);

        SQL = "Update uzytkownik SET Nazwisko = 'Bialy' WHERE Nazwisko = 'White'";
        stmt.executeUpdate(SQL);

        SQL = "Update uzytkownik SET Nazwisko = 'Mlody' WHERE Nazwisko = 'Young'";
        stmt.executeUpdate(SQL);

        conn.commit();
        System.out.println("Zmieniono nazwiska: Cook->Kruk, White->Bialy, Young->Mlody ");
        stmt.close();
    }

    private void transaction2() throws SQLException {

        System.out.println();
        System.out.println("Transaction 2 ");

        conn.setAutoCommit(false);
        Statement stmt = conn.createStatement();

        String SQL = "UPDATE Skladnik SET Dostepnosc = 1 WHERE Cena < 7";
        stmt.executeUpdate(SQL);

        SQL = "UPDATE Skladnik SET Dostepnosc = 1 WHERE Producent = 'Twimm'";
        stmt.executeUpdate(SQL);

        conn.commit();
        System.out.println("Zmieniono Dostepnosc skladnikow gdzie producent to Twimm i cena < 7");
        stmt.close();
    }

    /**
     * Run showcase.
     *
     * @param args
     */
    public static void main(String[] args) throws SQLException {
        JDBCShowcase showcase = new JDBCShowcase();
        showcase.doShowcase();
    }

}