import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.LogManager;

import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;

import org.junit.Test;
import org.junit.Assert;
import org.junit.Ignore;

import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;;

import java.sql.SQLException;
import java.sql.BatchUpdateException;

import java.util.Properties;
import java.util.Scanner;
import java.util.List;
import java.util.ArrayList;

import java.lang.reflect.Method;
import java.lang.reflect.Field;

import java.io.InputStream;
import java.io.IOException;

@SuppressWarnings("unchecked")
public class JDBCEx05
{
	private static final Logger LOGGER = LogManager.getLogger(JDBCEx05.class);  

	public ResultSet getResultSet(String sql)
	{

		Statement stat = null;
		ResultSet rs = null;
		
		try
		{
			// NO.1 反射 加载Oracle的JDBC驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");

			// NO.2 获取连接
			DBHelper dbhelper = DBHelper.getInstance();
			Connection con = dbhelper.getConnection();
			
			// NO.3 创建声明
			stat = con.createStatement();

			// NO.4 执行SQL,获得结果集
			rs = stat.executeQuery(sql);
		}
		catch(ClassNotFoundException e)
		{
			LOGGER.catching(e);
		}
		catch(SQLException e)
		{
			LOGGER.catching(e);
		}
		
		// 不可以关闭 否则报异常 java.sql.SQLRecoverableException: 关闭的 Resultset
		return rs;
	}

	public List<Emp> getEmpList()
	{
		ResultSet rs = null;
		List<Emp> empList = new ArrayList<>();
		try
		{
			rs = getResultSet("SELECT * FROM emp JOIN dept USING (deptno)");			
			Emp emp = null;
			Dept dept = null;
			while(rs.next())
			{
				int empno = rs.getInt("empno");
				String name = rs.getString("ename");
				String job = rs.getString("job");
				int mgr = rs.getInt("mgr");
				String hiredate = rs.getString("hiredate");
				double sal = rs.getDouble("sal");
				double comm = rs.getDouble("comm");
				int deptno = rs.getInt("deptno");
				String dname = rs.getString("dname");
				String loc = rs.getString("loc");
				dept = new Dept(deptno, dname, loc);
				empList.add(new Emp(empno, name, job, mgr, hiredate, sal, comm, dept));
			}	
		}
		catch(SQLException e)
		{
			LOGGER.catching(e);
		}
		finally
		{
			if (rs != null)
			{
				try
				{
					rs.close();
				}
				catch(SQLException e)
				{
					LOGGER.catching(e);
				}
			}		
		}	
		return empList;
	}	

	// 获取emp对象
	public Emp findEmp(int empno)
	{
		
		List<Emp> empList = getEmpList();
		for(Emp e : empList)
		{
			if (empno == e.getEmpno())
			{
				return e;
			}
		}	
		return null;
	}

	// 删除
	public int delete(Emp emp)
	{	
		if(null != findEmp(emp.getEmpno()))
		{
			ResultSet rs = null;	
			String fsql = "DELETE FROM emp WHERE empno = %s";
			String sql = String.format(fsql, emp.getEmpno());
			
			rs = getResultSet(sql);

			return 1;
		}
		else
		{
			return 0;
		}
	}

	// 修改员工信息
	public int update(Emp emp)
	{
		int i = 0;
		int empno = emp.getEmpno();
		if (null != findEmp(empno))
		{
			ResultSet rs = null;	
			while(true)
			{
				i++;
				Scanner sc = new Scanner(System.in);
				System.out.print("要更改员工的信息(列名)");
				String str = sc.next();

				System.out.print("更改信息为");
				String strV = sc.next();

				String fsql = "UPDATE emp SET %s = %s WHERE empno = %s";
				String sql = String.format(fsql, str, strV, empno);
				
				rs = getResultSet(sql);
				
				System.out.print("是否继续修改(y/n)");
				if("n".equals(sc.next()))
				{
					return i;
				}
			}
		}
		else
		{
			return i;
		}
	}

	@Test
	public void test2()
	{
		List<Emp> empList = getEmpList();

		// 得到的emp集合中每个emp对象必须可以访问到他的部门信息。
		ArrayList<Emp> emps = (ArrayList)empList;
		Dept dept = emps.get(1).getDept();
		LOGGER.info(dept);

		// 选择要修改信息的员工通过empno控制
		Scanner sc = new Scanner(System.in);
		System.out.print("请输入要更改员工的员工编号:");
		int num = sc.nextInt();
	
		Emp emp = findEmp(num);
		LOGGER.info(emp.getDept().toString());

		update(emp);
		emp = findEmp(num);
		LOGGER.info(emp.toString());
		delete(emp);
	}
}


class Emp
{
	private Integer empno;
	private String name;
	private String job;
	private Integer mgr;
	private String hiredate;
	private double sal;
	private double comm;
	private Dept dept;

	public Emp(Integer empno, String name, String job, Integer mgr, String hiredate, double sal, double comm, Dept dept)
	{
		this.empno = empno;
		this.name = name;
		this.job = job;
		this.mgr = mgr;
		this.hiredate = hiredate;
		this.sal = sal;
		this.comm = comm;
		this.dept = dept;
	}

	public Integer getEmpno()
	{
		return empno;
	}

	public String getName()
	{
		return name;
	}

	public String getJob()
	{
		return job;
	}

	public Integer getMgr()
	{
		return mgr;
	}

	public String getHiredate()
	{
		return hiredate;
	}

	public double getSal()
	{
		return sal;
	}

	public double getComm()
	{
		return comm;
	}

	public Dept getDept()
	{
		return dept;
	}
    
	@Override
	public String toString()
	{
		return ToStringBuilder.reflectionToString(this, ToStringStyle.JSON_STYLE);
	}	      
}

class Dept
{
	private int deptno;
	private String dname;
	private String loc;

	public Dept(int deptno, String dname, String loc)
	{
		this.deptno = deptno;
		this.dname = dname;
		this.loc = loc;
	}

	public int getDeptno()
	{
		return deptno;
	}

	public String getDname()
	{
		return dname;
	}

	public String getLoc()
	{
		return loc;
	}

	@Override
	public String toString()
	{
		return ToStringBuilder.reflectionToString(this, ToStringStyle.JSON_STYLE);
	}	
}

// 提供数据库连接服务
class DBHelper
{
	private static final Logger LOGGER = LogManager.getLogger(DBHelper.class);  

	// 单例模式在整个应用中有且仅有一个实例
	// 饿汉式单例
	private static final DBHelper INSTANCE = new DBHelper();

	// 构造函数私有化
	private DBHelper(){}

	// 提供一个获取实例的方法
	public static DBHelper getInstance()
	{
		return INSTANCE;
	}

	public Connection getConnection()
	{
		String url = null;
		String username = null;
		String password = null;

		InputStream in = null;
		try
		{
			LOGGER.info("开始读取配置文件!");
			// 读取当前路径下config文件夹下的db.properties文件
			in = DBHelper.class.getResourceAsStream("/config/db.properties");

			// 创建一个properties对象
			Properties props = new Properties();

			// Properties加载流
			props.load(in);

			url = props.getProperty("url");
			username = props.getProperty("username");
			password = props.getProperty("password");

			LOGGER.info("url={}", url);
			LOGGER.info("username={}", username);
			LOGGER.info("password={}", password);
		}
		catch(IOException e)
		{
			LOGGER.info("未找到config/db.properties文件");
			LOGGER.catching(e);
		}
		finally
		{
			if(in != null)
			{
				try
				{
					in.close();
				}
				catch(IOException e)
				{
					LOGGER.catching(e);
				}
			}
		}
		Connection con = null;
		
		try
		{
			LOGGER.info("加载数据库驱动...");
			//反射 加载Oracle的JDBC驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");

			LOGGER.info("开始建立数据库连接...");
			//获取连接
			con = DriverManager.getConnection(url, username, password);
			LOGGER.info("数据库连接完成...");
		}
		catch(ClassNotFoundException e)
		{
			LOGGER.warn("未找到驱动");
			LOGGER.catching(e);
		}
		catch(SQLException e)
		{
			LOGGER.catching(e);
		}

		return  con;
	}

	public static void closeConnection(Connection con)
	{
		if(con != null)
		{
			try
			{
				con.close();
				LOGGER.info("关闭数据库");
			}
			catch(SQLException e)
			{
				LOGGER.catching(e);
			}
		}
	}
}