package com.allanlxf.jdbc.core;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

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

public class AccountDao
{
    public void insert()
    {
        Connection con = null;
        Statement st = null;
        
        try
        {
            con = ConnectionFactory.getConnection();
            st = con.createStatement();
            System.out.println(st.getFetchSize());
            String sql = "insert into sd0703_account(id, no, pwd,owner, balance, cdate)";
            sql += " values(1876, 'account001', 'pwd001', 'user''001', 0, to_date('2007-07-17', 'yyyy-mm-dd'))";
            System.out.println(sql);
            st.executeUpdate(sql);
        }catch(SQLException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }finally
        {
            JdbcUtil.close(st, con);
        }        
    }
    
    public void update()
    {
        Connection con = null;
        Statement st = null;
        
        try
        {
            con = ConnectionFactory.getConnection();
            st = con.createStatement();
            System.out.println(st.getFetchSize());
            String sql = "update sd0703_account set balance = balance + 13800";
            System.out.println(sql);
            st.executeUpdate(sql);
        }catch(SQLException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
            
        }finally
        {
            JdbcUtil.close(st, con);
        }        
    }
    
    public void selectById(int id)
    {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        
        try
        {
            con = ConnectionFactory.getConnection();
            st = con.createStatement();
            System.out.println(st.getFetchSize());
            String sql = "select id,no,owner,pwd,cdate,balance from sd0703_account where id = " + id;
            rs = st.executeQuery(sql);
            if(rs.next())
            {
                System.out.print("id=" + rs.getInt(1));
                System.out.print(",no=" + rs.getString(2));
                System.out.print(",owner=" + rs.getString(3));
                System.out.print(",pwd=" + rs.getString(4));
                System.out.print(",cdate=" + rs.getDate(5));
                System.out.println(",balance=" + rs.getDouble(6));
            }
        }catch(SQLException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
            
        }finally
        {
            JdbcUtil.close(rs, st, con);
        }        
    }
    
    public void select()
    {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        
        try
        {
            con = ConnectionFactory.getConnection();
            st = con.createStatement();
            System.out.println(st.getFetchSize());
            String sql = "select id,no,owner,pwd,cdate,balance from sd0703_account";
            rs = st.executeQuery(sql);
            while(rs.next())
            {
                System.out.print("id=" + rs.getInt(1));
                System.out.print(",no=" + rs.getString(2));
                System.out.print(",owner=" + rs.getString(3));
                System.out.print(",pwd=" + rs.getString(4));
                System.out.print(",cdate=" + rs.getDate(5));
                System.out.println(",balance=" + rs.getDouble(6));
            }
        }catch(SQLException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
            
        }finally
        {
            JdbcUtil.close(rs, st, con);
        }        
    }
    
    public void delete(int id)
    {
        Connection con = null;
        Statement st = null;
        
        try
        {
            con = ConnectionFactory.getConnection();
            st = con.createStatement();
            String sql = "delete from sd0703_account where id = " + id;
            System.out.println(sql);
            st.executeUpdate(sql);
        }catch(SQLException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
            
        }finally
        {
            JdbcUtil.close(st, con);
        }        
    }
    
    public static void main(String[] args)
    {
        AccountDao accountDao = new AccountDao();
        
        if(args[0].equalsIgnoreCase("select"))
        {
            accountDao.select();
        }else if(args[0].equalsIgnoreCase("insert"))
        {
            accountDao.insert();
        }else if(args[0].equalsIgnoreCase("update"))
        {
            accountDao.update();
        }else if(args[0].equalsIgnoreCase("delete"))
        {
            accountDao.delete(Integer.parseInt(args[1]));
        }else if(args[0].equalsIgnoreCase("selectbyid"))
        {
            accountDao.selectById(Integer.parseInt(args[1]));
        }
    }
}