Monday, March 26, 2012

JDBC Transactions

Most of the times transactions are important when we deal with JDBC. When we perform database operations if we don't realize the importance of transactions it will end up with a tragedy. I have created a scenario to demonstrate the importance of a transaction.
Scenario :
Lets assume a scenario where a transaction credit 500 rs from user1 and debit that 500 rs to user2 .

initial state of accounts

after executed non transaction method
after executed a proper transaction

Simply what we do in a transaction is do commitment of the transaction if all the operation of the transaction is completed successfully. for example just assume a transaction T that have operations called A,B,C. T is considered to be completed transaction if all operations A,B,C are completed successfully. if any operation fail to complete transaction T is considered as a incompleted transaction.

Important : Though JDBC supports transactions , all the DBMS es may not support transactions by default. for example I have used MySQL  as the DBMS in this scenario. But by default MySQL has enabled MyISAM storage engine on its tables. In order to get support for transactions in MySQL we have to use storage engine InnoDB for our tables. We can specify it when creating tables. (see the code)

SQL query for table creation:
CREATE TABLE `account` (
 `id` INT(11) NOT NULL DEFAULT '0',
 `name` VARCHAR(25) NULL DEFAULT NULL,
 `balance` FLOAT NULL DEFAULT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB;

Java Code for Invalid Transaction and proper Transaction
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package transactiondemo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;

/**
 *
 * @author Kanishka
 */
public class TransactionPerform {

    private final String DB_DRIVER = "com.mysql.jdbc.Driver";
    private final String DB_USER = "root";
    private final String DB_PASS = "";
    private final String HOST = "localhost";
    private final String DATABASE = "test";
    private final String DB_CONNECTION_URL = "jdbc:mysql://" + HOST + ":3306/" + DATABASE;

    public TransactionPerform() {
    }

    public boolean doIncorrectTransaction() throws SQLException {
        boolean isDone = false;
        Connection dbConn = null;
        PreparedStatement preps_credit = null;
        PreparedStatement preps_debit = null;

        String creditQuery = "update account ac set ac.balance=ac.balance - ? where ac.id= ? ";
        String debitQuery = "update account ac set ac.balance=ac.balance + ? where ac.id= ? ";
        try {
            dbConn = getConnection();

            //debit 
            //operatin 1
            preps_credit = dbConn.prepareStatement(creditQuery);
            preps_credit.setFloat(1, 500.0f);
            preps_credit.setInt(2, 1);
            preps_credit.executeUpdate();

            //operation 2
            preps_debit = dbConn.prepareStatement(debitQuery);
            preps_debit.setFloat(1, 500);
            preps_debit.setInt(2, 2);
            //make an artificial interrupt before commiting the 2nd operation
            if (1 > 0) {
                throw new Exception("Unknow exception occured before commit the transaction");
            }
            preps_debit.executeUpdate();
            isDone = true;
            System.out.println("===Transaction Completed Successfully!===");

        } catch (Exception e) {
            System.err.println("Error occured " + e.getMessage());
        } finally {
            if (preps_credit != null) {
                preps_credit.close();
            }
            if (preps_debit != null) {
                preps_debit.close();
            }
            if (dbConn != null) {
                dbConn.close();
            }
        }
        return isDone;
    }

    public boolean doCorrectTransaction() throws SQLException {
        boolean isDone = false;
        Connection dbConn = null;
        PreparedStatement preps_credit = null;
        PreparedStatement preps_debit = null;

        String creditQuery = "update account ac set ac.balance=ac.balance - ? where ac.id= ? ";
        String debitQuery = "update account ac set ac.balance=ac.balance + ? where ac.id= ? ";
        try {
            dbConn = getConnection();
            dbConn.setAutoCommit(false);//begining of the transaction

            //debit 
            //operatin 1
            preps_credit = dbConn.prepareStatement(creditQuery);
            preps_credit.setFloat(1, 500.0f);
            preps_credit.setInt(2, 1);
            preps_credit.executeUpdate();

            //operation 2
            preps_debit = dbConn.prepareStatement(debitQuery);
            preps_debit.setFloat(1, 500.0f);
            preps_debit.setInt(2, 2);
            //make an artificial interrupt before commiting the 2nd operation
            if (1 > 0) {
                //throw new  Exception("Unknown exception occured before commit the transaction");
            }
            preps_debit.executeUpdate();
            dbConn.commit();    //end of the transaction
            isDone = true;
            System.out.println("===Transaction Completed Successfully!===");

        } catch (Exception e) {
            System.err.println("Error occured :" + e.getMessage());
            dbConn.rollback();  //rollback the transaction
            System.out.println("Operation rollbacked!");
        } finally {
            if (preps_credit != null) {
                preps_credit.close();
            }
            if (preps_debit != null) {
                preps_debit.close();
            }
            if (dbConn != null) {
                dbConn.close();
            }
        }
        return isDone;
    }

    private Connection getConnection() {
        Connection dbConn = null;
        try {
            Class.forName(DB_DRIVER);
            dbConn = DriverManager.getConnection(DB_CONNECTION_URL, DB_USER, DB_PASS);
            return dbConn;
        } catch (ClassNotFoundException e) {
            System.err.println("DB Driver class not found! : " + e.getMessage());
        } catch (SQLException e) {
            System.err.println("Canno't create a connection to the Database! : " + e.getMessage());
        }
        return dbConn;
    }
}

Thursday, March 22, 2012

Java Excel Manipulation

We can use jxl library to deal with excel docs


import java.io.File;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;
import java.util.Random;

import jxl.CellView;
import jxl.Workbook;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class MyDemo {

 public MyDemo() {

 }

 public void writeToExcel() throws IOException, RowsExceededException,
   WriteException {
  // create a workbook
  WritableWorkbook workbook = Workbook.createWorkbook(new File(
    "C:/Users/Kanishka/Desktop/myDemoExcel.xls"));

  // create a new sheet inside the workbook
  WritableSheet sheet = workbook.createSheet("Sheet 01", 0);

  // instantiate a new label object
  Label cellLabel = new Label(0, 1, "This is a cell");
  // now add the cell object to sheet
  sheet.addCell(cellLabel);

  // instantiate some number objects
  Number cellNumber1 = new Number(0, 2, 3.145636454);
  Number cellNumber2 = new Number(0, 3, 4500000000.0);

  sheet.addCell(cellNumber1);
  sheet.addCell(cellNumber2);

  workbook.write();
  workbook.close();

  System.out.println("Excel sheet created successfully!");
 }

 public void writeToExcelFormatted() throws IOException,
   RowsExceededException, WriteException {
  WritableWorkbook workbook = Workbook.createWorkbook(new File(
    "C:/Users/Kanishka/Desktop/Formatted DemoExcel.xls"));

  WritableSheet sheet = workbook.createSheet("Sheet 01", 0);

  WritableFont arial14Font = new WritableFont(WritableFont.ARIAL, 14);

  WritableCellFormat writableCellFormat = new WritableCellFormat(
    arial14Font);
  writableCellFormat.setWrap(true);

  Label cellLabel1 = new Label(0, 1, "This is a formatted cell",
    writableCellFormat);

  Label cellLabel2 = new Label(1, 2, "This is an another formatted cell",
    writableCellFormat);

  sheet.addCell(cellLabel1);
  sheet.addCell(cellLabel2);

  // specify the format of the number
  NumberFormat myNumberFormat = new NumberFormat("#.####");
  // specify Font+number format object
  WritableCellFormat cellFormatForNumbers = new WritableCellFormat(
    arial14Font, myNumberFormat);

  // initiate number
  Number num1 = new Number(2, 1, 3.12456346534634, cellFormatForNumbers);
  Number num2 = new Number(2, 2, 343.3454823452318, cellFormatForNumbers);

  // add numbers to sheet
  sheet.addCell(num1);
  sheet.addCell(num2);

  // set auto size for column 0
  CellView cellView = sheet.getColumnView(0);
  cellView.setAutosize(true);
  sheet.setColumnView(0, cellView);

  workbook.write();
  workbook.close();

  System.out.println("Excel sheet created successfully!");
 }

 public void createExcelDocumentPattern() throws IOException,
   RowsExceededException, WriteException {
  WritableWorkbook workbook = Workbook.createWorkbook(new File(
    "C:/Users/Kanishka/Desktop/Formatted DemoExcel Pattern.xls"));

  WritableSheet sheet = workbook.createSheet("Sheet 01", 0);

  WritableFont arial14Font = new WritableFont(WritableFont.TIMES, 10);
  WritableFont arial12FontBold = new WritableFont(WritableFont.ARIAL, 12,
    WritableFont.BOLD, false);

  WritableCellFormat writableCellFormat = new WritableCellFormat(
    arial14Font);
  WritableCellFormat writableCellFormatHeader = new WritableCellFormat(
    arial12FontBold);

  Label cellHeaderStringCol = new Label(0, 0, "String Column",
    writableCellFormatHeader);
  Label cellHeaderNumCol1 = new Label(1, 0, "Number Column 1",
    writableCellFormatHeader);
  Label cellHeaderNumCol2 = new Label(2, 0, "Number Column 2",
    writableCellFormatHeader);
  Label cellHeaderNumCol3 = new Label(3, 0, "Number Column 3",
    writableCellFormatHeader);
  Label cellHeaderNumCol4 = new Label(4, 0, "Number Column 4",
    writableCellFormatHeader);
  Label cellHeaderNumCol5 = new Label(5, 0, "Date Column",
    writableCellFormatHeader);

  sheet.addCell(cellHeaderStringCol);
  sheet.addCell(cellHeaderNumCol1);
  sheet.addCell(cellHeaderNumCol2);
  sheet.addCell(cellHeaderNumCol3);
  sheet.addCell(cellHeaderNumCol4);
  sheet.addCell(cellHeaderNumCol5);

  NumberFormat numberFormat5DecPnts = new NumberFormat("#.#####");
  WritableCellFormat numberCellFormat = new WritableCellFormat(
    arial14Font, numberFormat5DecPnts);
  WritableCellFormat numberCellFormatIntegers = new WritableCellFormat(
    NumberFormats.INTEGER);

  for (int i = 1; i < 100; i++) {
   String rowString = "This is row " + i;
   double randNum1 = new Random(i).nextDouble() + 1;
   double randNum2 = new Random(i).nextDouble() + 10;
   double randNum3 = new Random(i).nextDouble() + 2;
   int randNum4 = new Random(i).nextInt();

   Label stringLbl = new Label(0, i, rowString, writableCellFormat);
   Number num1 = new Number(1, i, randNum1, numberCellFormat);
   Number num2 = new Number(2, i, randNum2, numberCellFormat);
   Number num3 = new Number(3, i, randNum3, numberCellFormat);
   Number num4 = new Number(4, i, randNum4, numberCellFormatIntegers);

   // sample dates
   Date now = Calendar.getInstance().getTime();
   DateFormat customDateFormat = new DateFormat("dd MMM yyyy hh:mm:ss");
   WritableCellFormat dateFormat = new WritableCellFormat(
     customDateFormat);
   DateTime dateCell = new DateTime(5, i, now, dateFormat);
   sheet.addCell(dateCell);

   sheet.addCell(stringLbl);
   sheet.addCell(num1);
   sheet.addCell(num2);
   sheet.addCell(num3);
   sheet.addCell(num4);

  }

  // set autosize for columns
  for (int i = 0; i < 6; i++) {
   CellView cv = sheet.getColumnView(i);
   cv.setAutosize(true);
   sheet.setColumnView(i, cv);
  }

  workbook.write();
  workbook.close();

  System.out.println("Excel document generated successfully!");

 }

}
 
© Copyright 2035 kani.stack.notez
Theme by Yusuf Fikri