package com.allanlxf.jdbc.core;

import java.sql.DriverManager;
import java.sql.Driver;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.allanlxf.jdbc.util.ResultSetUtil;

public class AccountDaoBad
{
    public void insert()
    {
        Connection con = null;
        Statement st = null;
        
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.23:1521:tarena", "sd0703", "sd0703");
            st = con.createStatement();
            System.out.println(st.getFetchSize());
            String sql = "insert into sd0703_account(id, no, pwd,owner, balance, cdate)";
            sql += " values(1, 'account001', 'pwd001', 'user001', 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());
            
        }catch(ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new RuntimeException("can not found class:oracle.jdbc.driver.OracleDriver");
            
        }finally
        {
            try
            {
                st.close();
            }catch(Exception e)
            {
            }
            
            try
            {
                con.close();
            }catch(Exception e)
            {
            }
        }        
    }
    
    public void update()
    {
        Connection con = null;
        Statement st = null;
        
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.23:1521:tarena", "sd0703", "sd0703");
            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());
            
        }catch(ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new RuntimeException("can not found class:oracle.jdbc.driver.OracleDriver");
            
        }finally
        {
            try
            {
                st.close();
            }catch(Exception e)
            {
            }
            
            try
            {
                con.close();
            }catch(Exception e)
            {
            }
        }        
    }
    
    public void selectById(int id)
    {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.23:1521:tarena", "sd0703", "sd0703");
            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());
            
        }catch(ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new RuntimeException("can not found class:oracle.jdbc.driver.OracleDriver");
            
        }finally
        {
            try
            {
                rs.close();
            }catch(Exception e)
            {
            }
            
            try
            {
                st.close();
            }catch(Exception e)
            {
            }
            
            try
            {
                con.close();
            }catch(Exception e)
            {
            }
        }        
    }
    
    public void select()
    {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        
        try
        {
//            Driver d = new oracle.jdbc.driver.OracleDriver();
//            DriverManager.registerDriver(d);
            Class.forName("oracle.jdbc.driver.OracleDriver");
            /** static init block in OracleDriver*/
            con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.23:1521:tarena", "sd0703", "sd0703");
            st = con.createStatement();
            System.out.println(st.getFetchSize());
            String sql = "select id,no,owner,pwd,cdate,balance from sd0703_account";
            rs = st.executeQuery(sql);
            ResultSetUtil.printResult(rs);
        }catch(SQLException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
            
        }catch(ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new RuntimeException("can not found class:oracle.jdbc.driver.OracleDriver");
            
        }finally
        {
            try
            {
                rs.close();
            }catch(Exception e)
            {
            }
            
            try
            {
                st.close();
            }catch(Exception e)
            {
            }
            
            try
            {
                con.close();
            }catch(Exception e)
            {
            }
        }        
    }
    
    public void delete(int id)
    {
        Connection con = null;
        Statement st = null;
        
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.23:1521:tarena", "sd0703", "sd0703");
            st = con.createStatement();
            System.out.println(st.getFetchSize());
            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());
            
        }catch(ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new RuntimeException("can not found class:oracle.jdbc.driver.OracleDriver");
            
        }finally
        {
            try
            {
                st.close();
            }catch(Exception e)
            {
            }
            
            try
            {
                con.close();
            }catch(Exception e)
            {
            }
        }        
    }
    
    public static void main(String[] args)
    {
        AccountDaoBad accountDao = new AccountDaoBad();
        
        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]));
        }
    }
}