package com.huang.common.DB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

public class DBtool {
	private static Connection connection = null;
	private static PreparedStatement ps = null;
	
	public static boolean updateData(String sql) {
		System.out.println(sql);
		try {
			connection = DBpool.getConnection();
			ps = connection.prepareStatement(sql);
			ps.execute();
			return true;
		}catch(Exception e) {
			e.printStackTrace();
			return false;
		}finally {
			try {
				ps.close();
				DBpool.returnTOConnection(connection);
				connection = null;
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
	public static ArrayList getDataList(String tablename,String[] cols) {
		return getDataList(tablename,cols,"");
	}
	public static ArrayList getDataList(String tablename,String[] cols,String condition){
		String sql = "select ";
		ArrayList list = new ArrayList();
		ResultSet rs = null;
		
		for(int i = 0; i < cols.length; i++) {
			if(i == cols.length-1) {
				sql = sql + cols[i] + " ";
			}else {
				sql = sql + cols[i] + ",";
			}
			
		}
		if(condition.equals("")) {
			sql = sql + " from " + tablename;
		}else {
			sql = sql + " from " +tablename+ " where "+condition;
		}
		System.out.println(sql);
		connection = DBpool.getConnection();
		if(connection != null) {
			try {
				ps = connection.prepareStatement(sql);

				rs = ps.executeQuery();

				while(rs.next()) {
					HashMap hm = new HashMap();
					for(int i = 0; i < cols.length; i++) {
						
						hm.put(cols[i], rs.getObject(cols[i]));
					}
					list.add(hm);

				}	
			} catch(Exception e) {
				e.printStackTrace();
				return null;
			} finally {
				try {
					rs.close();
					ps.close();
					DBpool.returnTOConnection(connection);
					connection = null;
				} catch(Exception e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
	
	//根據sql語句返回數字
	public static int getOneInt(String sql) {
		ResultSet rs = null;
		int value = 0;
		System.out.println(sql);
		connection = DBpool.getConnection();
		if(connection != null) {
			try {
				ps = connection.prepareStatement(sql);
				rs = ps.executeQuery();	
				while(rs.next()) {
					
					value = rs.getInt(1);
				}
			} catch(Exception e) {
				e.printStackTrace();
				return 0;
			} finally {
				try {
					rs.close();
					ps.close();
					DBpool.returnTOConnection(connection);
					connection = null;
				} catch(Exception e) {
					e.printStackTrace();
				}
			}
		}
		return value;
	}
	//根據sql語句返回字符串
	public static String getOneString(String sql) {
		ResultSet rs = null;
		String value = null;
		System.out.println(sql);
		connection = DBpool.getConnection();
		if(connection != null) {
			try {
				ps = connection.prepareStatement(sql);
				rs = ps.executeQuery();	
				while(rs.next()) {
					
					value = rs.getString(1);
				}
			} catch(Exception e) {
				e.printStackTrace();
				return null;
			} finally {
				try {
					rs.close();
					ps.close();
					DBpool.returnTOConnection(connection);
					connection = null;
				} catch(Exception e) {
					e.printStackTrace();
				}
			}
		}
		return value;
	}

}