package com.allanlxf.jdbc.core;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import com.allanlxf.jdbc.util.ConnectionFactory;
import com.allanlxf.jdbc.util.JdbcUtil;

public class AccountDaoPS
{
    public void insert(Account account)
    {
        Connection con = null;
        PreparedStatement ps = null;
        
        try
        {
            con = ConnectionFactory.getConnection();
            String sql = "insert into sd0703_account(id, no, pwd,owner, balance, cdate)";
            sql += " values(?,?,?,?,?,?)";
            
            ps = con.prepareStatement(sql);
            
            int index = 1;
            ps.setInt(index++, account.getId());
            ps.setString(index++, account.getNo());
            ps.setString(index++, account.getPassword());
            ps.setString(index++, account.getOwner());
            ps.setDouble(index++, account.getBalance());
            ps.setDate(index++, account.getCreateDate());
            
            ps.executeUpdate();
        }catch(SQLException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }finally
        {
            JdbcUtil.close(ps, con);
        }        
    }
    
    public void insert(Collection accounts)
    {
        Connection con = null;
        PreparedStatement ps = null;
        
        try
        {
            con = ConnectionFactory.getConnection();
            String sql = "insert into sd0703_account(id, no, pwd,owner, balance, cdate)";
            sql += " values(?,?,?,?,?,?)";
            
            ps = con.prepareStatement(sql);
            
            Iterator iter = accounts.iterator();
            while(iter.hasNext())
            {
                Account account = (Account)iter.next();
                int index = 1;
                ps.setInt(index++, account.getId());
                ps.setString(index++, account.getNo());
                ps.setString(index++, account.getPassword());
                ps.setString(index++, account.getOwner());
                ps.setDouble(index++, account.getBalance());
                ps.setDate(index++, account.getCreateDate());
                
                ps.executeUpdate();
            }            
        }catch(SQLException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }finally
        {
            JdbcUtil.close(ps, con);
        }
    }
    
    public void update(Account account)
    {
        Connection con = null;
        PreparedStatement ps = null;
        
        try
        {
            con = ConnectionFactory.getConnection();
            String sql = "update sd0703_account set pwd=?,owner=?, balance=?";
            sql += " where id = ?";
            
            ps = con.prepareStatement(sql);
            
            int index = 1;
            ps.setString(index++, account.getPassword());
            ps.setString(index++, account.getOwner());
            ps.setDouble(index++, account.getBalance());
            ps.setInt(index++, account.getId());
            
            ps.executeUpdate();
        }catch(SQLException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }finally
        {
            JdbcUtil.close(ps, con);
        }        
    }
    
    public Account selectById(int id)
    {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Account account = null;
        
        try
        {
            con = ConnectionFactory.getConnection();
            String sql = "select id,no,owner,pwd,cdate,balance from sd0703_account where id = ?";
            ps = con.prepareStatement(sql);
            
            ps.setInt(1, id);
            
            rs = ps.executeQuery();
            
            if(rs.next())
            {
                account = populate(rs);
            }
        }catch(SQLException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
            
        }finally
        {
            JdbcUtil.close(rs, ps, con);
        }       
        
        return account; 
    }
    
    public Collection select()
    {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Collection accounts = new ArrayList();
        
        try
        {
            con = ConnectionFactory.getConnection();
            String sql = "select id,no,owner,pwd,cdate,balance from sd0703_account order by id";
            ps = con.prepareStatement(sql);
            
            rs = ps.executeQuery();

            while(rs.next())
            {
                accounts.add(populate(rs));
            }
        }catch(SQLException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
            
        }finally
        {
            JdbcUtil.close(rs, ps, con);
        }       
        
        return accounts.isEmpty() ? null : accounts; 
    }
    
    public void delete(int id)
    {
        Connection con = null;
        PreparedStatement ps = null;
        
        try
        {
            con = ConnectionFactory.getConnection();
            String sql = "delete from sd0703_account where id = ?";
            
            ps = con.prepareStatement(sql);
            
            ps.setInt(1, id);
            
            ps.executeUpdate();
        }catch(SQLException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }finally
        {
            JdbcUtil.close(ps, con);
        }
    }
    
    private Account populate(ResultSet rs) throws SQLException
    {
        Account account = new Account();
        account.setId(rs.getInt(1));
        account.setNo(rs.getString(2));
        account.setOwner(rs.getString(3));
        account.setPassword(rs.getString(4));
        account.setCreateDate(rs.getDate(5));
        account.setBalance(rs.getDouble(6));
        return account;      
    }
}