package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class LookUp {

    public void execute(Connection conn, String sql) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.print(rs.getString(1));// 读取每条记录的第一个字段//
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closePstmt(pstmt);
        }
    }
    
    public void update(Connection conn, String sql) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closePstmt(pstmt);
        }
    }
    
    /**
     * 关闭PreparedStatement
     * 
     * */
    private void closePstmt(PreparedStatement pstmt) {
        try {
            if (pstmt != null && !pstmt.isClosed()) {
                pstmt.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    public static void main(String[] args) {
        String selectSql = "select NAME,BIRTHDAY from TBL_TEST_CLASS1 t ";
        String updateSql = "update TBL_TEST_CLASS2 set NAME='张山' where NAME='张三';";
        String deleteSql = "DELETE FROM TBL_TEST_CLASS1 WHERE NAME = '张山'";
        String insertSql="INSERT INTO TBL_TEST_CLASS1 (USER-CODE,NAME,BIRTHDAY) VALUES('lisi','李四','19880808')";
        LookUp lookUp = new LookUp();
        Connection conn = null;
        
        try {
            String dbUrl = "jdbc:oracle:thin:@Bard0117:1522:ORCL";
            String user = "system";
            String password = "system";

            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(dbUrl, user, password);
            
            //第一次查询
            lookUp.execute(conn, selectSql);
            //把“张三”改成“张山”
            lookUp.update(conn, updateSql);
            //输出修改后的结果
            lookUp.execute(conn, selectSql);
            //删除张山
            lookUp.update(conn, deleteSql);
            //把李四加进去
            lookUp.update(conn, insertSql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                //关闭connection
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}