/**
 * DB.java for MySQL
 * Connection to MySQLDatabase Method;
 * and normal sqlAction,only pass 
 * all the sqlSentence you need
 * to sqlAction in this Package;
 * Created on 2006年8月17日, 下午14:13
 *  @author tangsj
 * 	@version 2.0
 */

package com.glf.reportIE.commons;

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


public class DB {
        private String CLASSFORNAME,SERVANDDB,USER,PWD;
        private Connection con=null;
        private Statement stm;
        private PreparedStatement pstm;
        
    public DB() {}
    
    /**
     * 執行搜索語句
     * @param String sql_select 傳入查找的sql語句
     * @return ResultSet 返回查找結果
     */
    public ResultSet doSelect(String sql_select) throws Exception{
    	if(con==null){
    		DataBaseConnection();
    	}
         return stm.executeQuery(sql_select);
    }
    
    /**
     * 預處理查找語句
     * @param sql_select
     * @return PreparedStatement 
     * @throws Exception
     */
    public PreparedStatement setSelect(String sql_select) throws Exception{
    	if(con==null){
    		DataBaseConnection();
    	}
    	return con.prepareStatement(sql_select);
    }
    public ResultSet exeSelect(PreparedStatement pstm) throws Exception{
    	if(con==null){
    		DataBaseConnection();
    	}
    	return pstm.executeQuery();
    }
    
    /**
     * 執行更新語句
     * @param String sql_update 傳入更新的sql語句
     * @return void
     */
    public void doUpdate(String sql_update) throws Exception{
    	if(con==null){
    		DataBaseConnection();
    	}
        stm.executeUpdate(sql_update);
    }
    
    /**
     * 預處理update語句
     * @param sql_update
     * @return
     * @throws Exception
     */
    public PreparedStatement setUpdate(String sql_update) throws Exception{
    	if(con==null){
    		DataBaseConnection();
    	}
    	return con.prepareStatement(sql_update);
    }
    /**
     * 執行update
     * @param pstm
     * @return
     * @throws Exception
     */
    public int exeUpdate(PreparedStatement pstm) throws Exception{
    	if(con==null){
    		DataBaseConnection();
    	}
    	return pstm.executeUpdate();
    }
    /**
     * 初始化插入語句
     * 例如insert into DatabaseName values('',?,default);
     * @param String sql_insert 
     * @return PreparedStatement
     */
    public PreparedStatement setInsert(String sql_insert) throws Exception{
    	if(con==null){
    		DataBaseConnection();
    	}
        return con.prepareStatement(sql_insert);
    }
    /**
     * 完成插入語句;通過初始化返回的pstm
     * 如pstm.setString(1,form.getName());
     * 后傳入執行真正插入工作。
     * @param PreparedStatement stm_insert
     * @return int
     */
    public int doInsert(PreparedStatement stm_insert) throws Exception{
        return stm_insert.executeUpdate();
    }
    
    /**
     * 直接執行插入操作，傳入插入的sql語句
     * 比較適合插入較少內容時的用法
     * @param String sql_insert
     * @return void
     */
    public void excuteInsert(String sql_insert) throws Exception{
    	if(con==null){
    		DataBaseConnection();
    	}
        pstm=con.prepareStatement(sql_insert);
        pstm.executeUpdate();
//        System.out.println(num);
    }
    
    /**
     * 執行刪除操作,傳入刪除的sql語句
     * @param String sql_delete
     * @return void
     */
    public void doDelete(String sql_delete) throws Exception{
    	if(con==null){
    		DataBaseConnection();
    	}
        pstm=con.prepareStatement(sql_delete);
        pstm.executeUpdate();
    }
    
    /**
     * 預處理刪除sql語句
     * @param sql_delete
     * @return
     * @throws Exception
     */
    public PreparedStatement setDelete(String sql_delete) throws Exception{
    	if(con==null){
    		DataBaseConnection();
    	}
    	return con.prepareStatement(sql_delete);
    }
    
    /**
     * 執行刪除語句
     * @param pstm
     * @return
     * @throws Exception
     */
    public int exeDelete(PreparedStatement pstm) throws Exception{
    	if(con==null){
    		DataBaseConnection();
    	}
    	return pstm.executeUpdate();
    }
    
    /**
     * 數據庫連接關閉
     * @return void
     */
    public void closeDB() throws Exception{
    	if(stm!=null){
    		stm.close();
    	}
		if(pstm!=null){
			pstm.close();
		}
		if(con!=null){
			con.close();
		}
    }
    
    /**
     * 數據庫接連
     * @return void
     */
    private void DataBaseConnection(){
    	CLASSFORNAME="com.mysql.jdbc.Driver";
    	SERVANDDB= "jdbc:mysql://localhost:3306/thizcaiwu?useUnicode=true&characterEncoding=utf8";
    	USER="root";
    	PWD="123456";
    	try{
    		Class.forName(CLASSFORNAME);
    		con=DriverManager.getConnection(SERVANDDB,USER,PWD);
    		stm=con.createStatement();
    	}
    	catch(Exception e){
    		System.out.println("Connection Error!!");
    	}
    }
    
//    public HashMap selectFromDB(){
//    	HashMap map = new HashMap();
//    	map.get("username");
//    	map.get("heigh");
//    	return null;
//    }
}