package com.tarena.bank.persistent;

import java.sql.*;
import com.tarena.bank.biz.entity.Account;
import com.tarena.bank.util.JdbcUtil;
import com.tarena.bank.util.ConnectionFactory;

public class AccountDAOJdbcImpl implements IAccountDAO{
	public void insert(Account acct){
		Connection con=null;
		PreparedStatement ps=null;
		String sql="insert into bank values(?,?,?)";
		try{
			con = ConnectionFactory.getConnection();
			con.setAutoCommit(false);
			ps = con.prepareStatement(sql);
			ps.setString(1, acct.getUserName());
			ps.setString(2, acct.getPassword());
			ps.setDouble(3, acct.getBalance());
			ps.executeUpdate();
			con.commit();
		}catch(SQLException e){
			e.printStackTrace();
			if(con!=null){
				try{
					con.rollback();
				}catch(SQLException e1){
					e1.printStackTrace();	
				}	
			}
		}finally{
			JdbcUtil.release(null, ps, con);
		}		
	}
  	public void delete(String userName){
  		Connection con=null;
  		PreparedStatement ps=null;
  		String sql="delete from bank where userName=?";
  		try{
			con = ConnectionFactory.getConnection();
			con.setAutoCommit(false);
			ps=con.prepareStatement(sql);
			ps.setString(1,userName);
			ps.executeUpdate();
			con.commit();  		
  		}catch(SQLException e){
  			e.printStackTrace();
  			if(con!=null){
  				try{
					con.rollback();  				
  				}catch(SQLException e1){
  					e1.printStackTrace();
  				}	
  			}
  		}finally{
  			JdbcUtil.release(null,ps,con);
  		}
  	}
  	public void update(Account acct){
  		Connection con = null;
		PreparedStatement ps = null;
		String sql = "update bank set userName=?,password=?,balance=? where userName=?";
		try {
			con = ConnectionFactory.getConnection();
			con.setAutoCommit(false);
			ps = con.prepareStatement(sql);
			ps.setString(1, acct.getUserName());
			ps.setString(2, acct.getPassword());
			ps.setDouble(3, acct.getBalance());
			ps.setString(4, acct.getUserName());
			ps.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			if (con != null) {
				try {
					con.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		}finally{
			JdbcUtil.release(null, ps, con);
		}
	
  	}
  	public Account findByUserName(String userName){
  		Account acct=null;
  		Connection con=null;
  		PreparedStatement ps=null;
  		ResultSet rs=null;
  		String sql="select * from bank where userName=?";
  		try{
	  		con = ConnectionFactory.getConnection();
	  		con.setAutoCommit(false);
	  		ps=con.prepareStatement(sql);
	  		ps.setString(1, userName);
	  		rs=ps.executeQuery();
	  		if(rs!=null){
	  			rs.next();
	  			acct=new Account(rs.getString(1),rs.getString(2),rs.getDouble(3));	
	  		} 
	  		con.commit(); 		
  		}catch(SQLException e){
  			e.printStackTrace();
  			if(con!=null){
  				try{
  					con.rollback();
  				}catch(SQLException e1){
  					e1.printStackTrace();	
  				}	
  			}
  		}finally{
  			JdbcUtil.release(rs,ps,con);
  		}
  		return acct;	
  	}
}