package com.fgkj.dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.fgkj.db.DBUtil;
import com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException;

/**
 * 
 * 数据库操作相关的封装类,封装了所有的增删改以及查询的操作
 * @author Administrator
 *
 */

public class DAOHelper {
	public static SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	public static SimpleDateFormat sdfwithOut=new SimpleDateFormat("yyyy-MM-dd");
	/**
	 * 封装所有更新的操作(添加,删除,修改)
	 * @param conn
	 * @param sql
	 * @param values
	 * @return
	 */
	public static boolean executeUpdate(Connection conn,String sql,Object[] values){
		//System.out.println("释放之前************");
		//DBUtil.getConnections();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			if(values != null){
				for (int i = 0; i < values.length; i++) {
					ps.setObject(i+1, values[i]);
				}
			}
			int flag =  ps.executeUpdate();
			if(flag>0){
				return true;
			}
		}catch (MySQLSyntaxErrorException e) {
			e.printStackTrace();
			//System.out.println("权限不足");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(null, ps, conn);
			//System.out.println("释放之后++++++++++++");
			//DBUtil.getConnections();
		}
		
		return false;
	}
	
	/**
	 * 根据指定sql语句执行相关查询
	 * @param sql		需要执行的sql查询语句
	 * @param values	执行sql查询时需要的参数值
	 * @param call		回调函数对象:将获取结果的任务以回调的方式交给调用者处理
	 * @return
	 */
	public static List executeQuery(String sql,Connection conn,Object[] values,CallBack call){
		//System.out.println("释放之前************");
		//DBUtil.getConnections();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setQueryTimeout(600);
			//当查询条件不为空时,设置查询条件对应的值
			if(values != null){
				for (int i = 0; i < values.length; i++) {
					ps.setObject(i+1, values[i]);
				}
			}
			rs=ps.executeQuery();
			return call.getResults(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, ps, conn);
			//System.out.println("释放之后++++++++++++");
			//DBUtil.getConnections();
		}
		return null;
	}
	
	//对图片的更新操作(添加,删除,修改)
	public static boolean executeUpdatePicture(Connection conn,String sql,List list){
		PreparedStatement ps = null;
		InputStream in = null;
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				Object obj=list.get(i);
				if(obj.getClass().getName().equals("java.io.File")){
					File file=(File) obj;
					try {
						in = new FileInputStream(file);
						//System.out.println(in);
						//生成被插入文件的节点流
			            //设置Blob
			             ps.setBlob(i+1, in);
					} catch (FileNotFoundException e) {
						conn.rollback();
						e.printStackTrace();
					} catch (IOException e) {
						conn.rollback();
						e.printStackTrace();
					}
				}else{
					ps.setObject(i+1, obj);
				}
			}
			int rs =  ps.executeUpdate();
			if(rs>0){
				conn.commit();
				return true;
			}
		}catch (MySQLSyntaxErrorException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(null, ps, conn);
			if(in != null){
				try {
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return false;
	}
	//对图片的查询操作
	public static List executeQueryPicture(String sql,Connection conn,Object[] values,CallBack call){
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setQueryTimeout(600);
			//当查询条件不为空时,设置查询条件对应的值
			if(values != null){
				for (int i = 0; i < values.length; i++) {
					ps.setObject(i+1, values[i]);
				}
			}
			rs=ps.executeQuery();
			return call.getResults(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, ps, conn);
		}
		return null;
	}
	//用于分页的查询
	public static List executeQueryLimit(String sql,Connection conn,Object[] values,CallBack call){
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			//当查询条件不为空时,设置查询条件对应的值
			if(values != null){
				for (int i = 0; i < values.length; i++) {
					ps.setObject(i+1, values[i]);
				}
			}
			rs=ps.executeQuery();
			return call.getResults(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, ps, null);
		}
		return null;
	}
	//用于修改回滚
	public static boolean executeUpdateLimit(Connection conn,String sql,Object[] values){
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			if(values != null){
				for (int i = 0; i < values.length; i++) {
					ps.setObject(i+1, values[i]);
				}
			}
			int flag =  ps.executeUpdate();
			conn.commit();
			if(flag>0){
				return true;
			}
		}catch (MySQLSyntaxErrorException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(null, ps, null);
		}
		
		return false;
	}
	/**
	 * 
	 * @param regex		SimpleDateFormat 的形式
	 * @return	根据传入的regex获取的SimpleDateFormat对象
	 */
	public static SimpleDateFormat getSdf(String regex){
		if(regex!=null){
			return new SimpleDateFormat(regex);
		}else{
			return  new SimpleDateFormat();
		}
	}
	
	public static double toFixid(Double a,long index){
		StringBuffer ms=new StringBuffer();
		if(a!=null && index>=0){
			if(index==0){
				ms=ms.append("#");
			}else{
				ms=ms.append("#.");
				for(int i=0;i<index;i++){
					ms.append("0");
				}
				
			}
			//System.out.println(new DecimalFormat(ms.toString()).format(a));
			return Double.parseDouble(new DecimalFormat(ms.toString()).format(a));
		}else{
			return a;
		}
	}
	//执行sql语句
	public static boolean executeCreateTable(Connection mysql_con,String sql_str)
	{  
		PreparedStatement ps=null;
	
		boolean rest = true;
		try
    	{
			//System.out.println(sql_str);
		    ps = mysql_con.prepareStatement(sql_str);
			ps.setQueryTimeout(600);
			ps.executeUpdate();
    	}
    	catch(SQLException ex)
		{
    		//System.out.println("##########################");
    		ex.printStackTrace();
    		rest = false;
    		DBUtil.close(null, ps, mysql_con);   
    		
		}
		return rest;
	}
	//执行sql语句with no colse
	public static boolean executeCreateTableNoclose(Connection mysql_con,String sql_str)
	{  
		PreparedStatement ps=null;
	
		boolean rest = true;
		try
    	{
		    ps = mysql_con.prepareStatement(sql_str);
		   
			ps.setQueryTimeout(600);
			ps.execute();
			
    	}
    	catch(SQLException ex)
		{
    		ex.printStackTrace();
    		rest = false;
    		
		}
		return rest;
	}
	
	public static boolean makeManualCommit(Connection mysql_con,ArrayList<String> al_sql_strs) 
	{
	    //System.out.println("释放之前************");
		//DBUtil.getConnections();
		boolean exe_res = true;
		try {
			mysql_con.setAutoCommit(false);
			for(int n=0; n<al_sql_strs.size(); n++) {
				if(true == exe_res) {
					exe_res = executeCreateTableNoclose(mysql_con,al_sql_strs.get(n));
				} else {
					break;
				}
			}
			if(true == exe_res) {
				mysql_con.commit();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			exe_res = false;
		} finally {
			try {
				if(false == exe_res) {
					mysql_con.rollback();
				}
				mysql_con.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			} finally{
				DBUtil.close(null, null, mysql_con);
				//System.out.println("释放之后++++++++++++");
				//DBUtil.getConnections();
			}
		}
		
		return exe_res;
	}
	
	public static void main(String[] args) {
		//System.out.println(getSdf(null));
		float f=1.2546f;
		System.out.println(toFixid(new Double(f), 9));;
	}
}