package zwh.db2.six;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

import javax.swing.JOptionPane;

public class Tool {
	Statement stmt;
	Connection con;
	private ResultSet rs;
	private PreparedStatement pps;
	private static String driver = "COM.ibm.db2.jdbc.app.DB2Driver";
	
	static{
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			errorMessage(e.getMessage());//任务7.1
		}
	}
	
	public Tool(){
		String url = "jdbc:db2:sample";
		try {
			con = DriverManager.getConnection(url,"db2admin","db2admin");
			stmt = con.createStatement();
		}catch(SQLException e){
			e.printStackTrace();
			errorMessage(e.getMessage());//任务7.2
		}
	}
	
	public int update(String sql,Object[] param){//增删改，预处理
		int count = 0;
		try {
			if(param==null){
				stmt.executeUpdate(sql);
			}else{
				pps = con.prepareStatement(sql);
				for(int i = 0;i<param.length;i++){
					pps.setObject(i+1, param[i]);
				}
				count = pps.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			errorMessage(e.getMessage());//任务7.3
		}
		return count;
	}

	public ArrayList<HashMap<String, Object>> query(String sql){//从数据库查询
		try {
			rs = stmt.executeQuery(sql);
			ResultSetMetaData mt = rs.getMetaData();// 获取结果集结构对象，结果集里有字段，类型，长度
			int count = mt.getColumnCount();//列数目
			String[] columns = new String[count];// 根据列的数目创建数组
			for(int i = 0;i<columns.length;i++){// 把字段名存入数组
				columns[i] = mt.getColumnName(i+1);
			}

			//把结果集里的数据保存到链表
			ArrayList<HashMap<String,Object>> list  = new ArrayList<HashMap<String,Object>>();
			while(rs.next()){
				HashMap<String,Object> map = new HashMap<String,Object>();
				for(int i = 0;i<columns.length;i++){
					map.put(columns[i],rs.getString(columns[i]));
				}
				list.add(map);
			}
			rs.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			errorMessage(e.getMessage());//任务7.4
		}
		return null;
	}
	
//	关闭结果集
	public void close(){
		try{
			stmt.close();
		}catch(SQLException e ){
			e.printStackTrace();
			errorMessage(e.getMessage());//任务7.5
		}
		try{
			con.close();
		}catch(SQLException e ){
			e.printStackTrace();
			errorMessage(e.getMessage());//任务7.6
		}
	}
	
	public String[][] show(){//显示全部数据
		String sql = "SELECT * FROM employee";
		ArrayList<HashMap<String, Object>> list = query(sql);
		
		String [][] a = new String[list.size()][list.get(0).size()];
		for(int i=0;i<list.size();i++){
			//任务6后加1：
			a[i][0]=ifNull(list.get(i).get("EMPNO"),0).toString();
			a[i][1]=ifNull(list.get(i).get("FIRSTNME"),0).toString();
			a[i][2]=ifNull(list.get(i).get("MIDINIT"),0).toString();
			a[i][3]=ifNull(list.get(i).get("LASTNAME"),0).toString();
			a[i][4]=ifNull(list.get(i).get("EDLEVEL"),0).toString();
		}
		return a;
	}
	
	public void insertLine(String[] datas){//单行添加
		String sql = "insert into employee (EMPNO,FIRSTNME,MIDINIT,LASTNAME,EDLEVEL) values(?,?,?,?,?)";
		update(sql, datas);
	}
	
	public void insertLines(String[][] datas){//多行添加
		String sql = "insert into employee (EMPNO,FIRSTNME,MIDINIT,LASTNAME,EDLEVEL) values(?,?,?,?,?)";
		for(int i=0;i<datas.length;i++){
			update(sql, datas[i]);
		}
	}
	
	public void workFive_query(){//子查询添加
		String sql = "insert into employee select * from like_employee_zwh where EMPNO not in(select EMPNO from employee)";
		update(sql, null);
	}
	
	public void delete(Object value){//删除行
		String sql = "delete from employee where EMPNO=?";
		update(sql, new Object[]{value});
	}
	
	public void updateAllTable(String[] value){//修改整表
		String sql = "update employee set FIRSTNME=?,MIDINIT=?,LASTNAME=?,EDLEVEL=? where EMPNO=?";
		update(sql, value);
	}
	
	public Object ifNull(Object value,int type){//显示置空
		if(type==0){
			if(value.equals(" ")){value="空";}
			if(value.equals("")){value="空";}			
		}
		if(type==1){
			if(value.equals("空")){value=" ";}
		}
		return value;
	}
	
	public static void errorMessage(String Message){//错误信息
		JOptionPane.showMessageDialog(null,Message,"Error",JOptionPane.INFORMATION_MESSAGE);
	}
}