Bank Account DAO

package dataaccess;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Vector;

import java.sql.Connection;

import java.util.Calendar;

import model.BankAccount;

import java.sql.ResultSet;

public class BankAccountDAO {

private String driver = "com.mysql.jdbc.Driver";

private String url="jdbc:mysql://localhost:3306/ibanking";

private String user="root";

private String password="password";

private Connection conn;

public void openConnection() throws

ClassNotFoundException,SQLException

{

Class.forName(driver);

conn=(Connection) DriverManager.getConnection(url,user,password);

}

public boolean updatebalance(String custId, Integer fromaccId,Integer toaccId,Float tranamount, String accounttype)

throws Exception{

boolean b;

Calendar cal=Calendar.getInstance();

int dates=cal.get(Calendar.DATE);

int months=cal.get(Calendar.MONTH)+1;

int years=cal.get(Calendar.YEAR);

String str= years+"/" + months +"/"+ dates;

Statement st=conn.createStatement();

//String sql4="Select RoleID from user where UserID='"+id+"'";

//ResultSet rs=st.executeQuery(sql4);

//custId=rs.getString("RoleID");

String sql5="Select CurrentBalance From bankaccount where CustomerId='"+custId+"'and AccountNo='"+fromaccId+"'";

ResultSet rs1=st.executeQuery(sql5);

float CurrentBalance = 0;

while (rs1.next())

{

CurrentBalance=rs1.getFloat(1);

}

if (CurrentBalance>0.0 && tranamount<=CurrentBalance)

{

String sql1="update bankaccount set CurrentBalance=CurrentBalance-"+tranamount+ "where CustomerId='"+custId+"'and AccountNo='"+fromaccId+"'";

String sql3="update bankaccount set CurrentBalance=CurrentBalance+"+tranamount+ "where AccountNo='"+toaccId+"'";

//String transferToAccId=null;

//String sql2="INSERT INTO transaction(TransactionID,TransactionCode,myAccountNo,myAccountType,TransferAccountNo,TransferAccountType,TransferAmount,TransferDate)"+"VALUES('"+fromAccId+"'"+","+"'"+transferToAccId+"'"+","+"'"+tranamount+"'"+","+"'"+acctypeId+"')";

String sql2="INSERT INTO transaction(TransactionCode,myAccountNo,myAccountType,TransferAccountNo,TransferAccountType,TransferAmount,TransferDate)VALUES('ITD','"+fromaccId+"','"+accounttype+"','"+toaccId+"','"+accounttype+"','"+tranamount+"',str_to_date('"+str+"','%Y/%m/%d'))";

st.executeUpdate(sql1);

st.executeUpdate(sql2);

st.executeUpdate(sql3);

}

else

{

System.out.println("Transaction is not available");

conn.close();

}

b=true;

st.close();

conn.close();

return b;

}

@SuppressWarnings("unchecked")

public BankAccount[] retrieveAccType(String id) throws Exception{

Statement stmt=conn.createStatement();

String sql="Select AccountNo From bankaccount where CustomerID='"+id+"'";

Vector v=new Vector();

try{

ResultSet rs=stmt.executeQuery(sql);

while(rs.next())

{

BankAccount bact=new BankAccount();

String s=rs.getString("accountno");

bact.setAccountno(Integer.parseInt(s));

//String s1=rs.getString("accounttypeid");

//bact.setAccounttypeid(Integer.parseInt(s1));

v.add(bact);

}

stmt.close();

rs.close();

}catch (Exception e){

//throw new Exception();

System.out.print(e);

}finally

{

try{

System.out.println("Closing connection");

conn.close();

}catch(Exception e){}

}

BankAccount[] result=new BankAccount[v.size()];

result=(BankAccount[])v.toArray(result);

return(result);

}

public void closeConnection()throws ClassNotFoundException, SQLException,InstantiationException,IllegalAccessException {

if (conn!=null)

conn.close();

}

private BankAccount[] FindAccounts(String sql) throws Exception {

Vector v=new Vector();

try{

this.openConnection();

System.out.println("Created connection");

Statement statement=(Statement) conn.createStatement();

System.out.println("Execute query:"+sql);

java.sql.ResultSet rs= (java.sql.ResultSet) statement.executeQuery(sql);

System.out.println("Executed query");

if(rs!=null){

while(rs.next()){

BankAccount obj=new BankAccount();

obj.setAccountno(Integer.parseInt(rs.getString("accountno")));

obj.setPin(rs.getString("pin"));

obj.setAccounttypeid(Integer.parseInt(rs.getString("accounttypeid")));

obj.setCardno(Integer.parseInt(rs.getString("cardno")));

obj.setCurrentbalance(Double.parseDouble(rs.getString("currentbalance")));

obj.setCustomerid(rs.getString("customerid"));

obj.setOpeingdate(rs.getDate("opeingdate"));

v.add(obj);

}

}

else

throw new SQLException();

}

catch(Exception e){

throw e;

}

BankAccount[] result=new BankAccount[v.size()];

result=(BankAccount[])v.toArray(result);

return result;

}

public int validateaccount (int accountno) throws Exception{

Statement stmt=conn.createStatement();

BankAccount ba = new BankAccount();

int result = 0;

String sql="Select AccountNo From bankaccount where accountno = " + accountno + " ";

try{

ResultSet rs=stmt.executeQuery(sql);

while(rs.next()){

result = 1;

}

stmt.close();

}catch (Exception e){

//throw new Exception();

System.out.print(e);

}finally

{

try{

System.out.println("Closing connection");

conn.close();

}catch(Exception e){}

}

return result;

}

public static void main(String[] args){

BankAccountDAO dao=new BankAccountDAO();

try {

dao.openConnection();

// User u = dao.getUser("123");

// System.out.println(u.getRole()); // show customer

int x = dao.validateaccount(222222222);

System.out.println("--------x is ="+ x);

} catch (Exception e) {

System.out.println(e.getMessage());

}

}

}