package com.zpwd_jdbc.jdbc_practice0328;

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

/**
 * 查询30部门，1985年以前入职的员工姓名，工资，部门名称，工作地点
 * 
 *
 */
public class HomeWork {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String user = "scott";
        String password = "tiger";
        try {
            // 1.加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // 2.利用驱动管理器获取数据库连接 mysql的本机端口为3306！！！
            conn = DriverManager.getConnection(url, user, password);
            // 3.获取SQL语句对象
            StringBuffer sb = new StringBuffer("select e.ename,e.sal,d.dname,d.loc" + " from emp e join dept d"
                    + " on e.deptno = d.deptno" + " where d.deptno = ? and" + " to_char(e.hiredate,'yyyy') < ?");
            ps = conn.prepareStatement(sb.toString());
            // Date d = sdf.parse(date);
            ps.setInt(1, 30);
            ps.setString(2, "1985");
            // 4.执行语句得到结果集
            rs = ps.executeQuery();
            while (rs.next()) {
                String ename = rs.getString(1);
                double sal = rs.getDouble(2);
                String dname = rs.getString(3);
                String loc = rs.getString(4);
                System.out.println(ename + '\t' + sal + '\t' + dname + '\t' + loc);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 5.释放资源
            if (conn != null) {
                conn.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
    }
}