/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */


/**
 *
 * @author aic
 */
// You need to import the java.sql package to use JDBC
import java.sql.*;

// We import java.io to be able to read from the command line

import java.io.*;
import java.util.Scanner;
public class JDBCORACLE {

    public static void main(String[] args) {
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver"); //Load the Oracle JDBC driver
            String url="jdbc:oracle:thin:@fedora1.uscupstate.edu:1521:xe";
        Connection con = DriverManager.getConnection(url,"testa","t123456");//connect oracle

            executeTransaction(con);

            con.close();
        }
        catch (Exception e)
        {
            System.out.println(e);
        }
    }
    public static void executeTransaction(Connection con) {
        try {
           Scanner scan = new Scanner(System.in);
           //Switch to manual transaction mode by setting
           //autocommit to false. Note that this starts the first
           //manual transaction.
           con.setAutoCommit(false);
           Statement stmt = con.createStatement();
           System.out.println("Please enter the bank account number with which you wish to "
                + "transfer funds from");
           int account1 = scan.nextInt();
           System.out.println("Please enter the bank account number with which you wish to "
                + "transfer funds to");
           int account2 = scan.nextInt();
           System.out.println("Please enter the amount of funds you wish to transfer");
           int transferAmount = scan.nextInt();
           String returnAmount1 = "SELECT balance FROM bankaccount WHERE accnum='" + account1 +"';";
           String returnAmount2 = "SELECT balance FROM bankaccount WHERE accnum='" + account2 +"';";
           stmt.executeQuery(returnAmount1);
           stmt.executeQuery(returnAmount2);
           int finalAmount = Integer.valueOf(returnAmount1) - transferAmount;
           int finalAmount2 = Integer.valueOf(returnAmount2) + transferAmount;
           stmt.executeUpdate("insert into bank values('"+account1+"','"+ finalAmount + "')");
           stmt.executeUpdate("insert into bank values('"+account2+"','"+ finalAmount2 + "')");
           //con.rollback();
           con.commit(); //This commits the transaction and starts a new one.
           stmt.close(); //This turns off the transaction.
           System.out.println("Transaction succeeded. Both records were written to the database.");
        }
        catch (SQLException ex) {
           ex.printStackTrace();
           try {
              System.out.println("Transaction failed.");
              con.rollback();
           }
           catch (SQLException se) {
              se.printStackTrace();
           }
        }
     }
}