/*
 * Created by xuehao at Nov 29, 2005
 */
package org.indigo.db;

import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleStatement;
import oracle.sql.CLOB;

import org.indigo.log.FileLoggerError;
import org.indigo.log.FileLoggerInsertDBError;
import org.indigo.util.DBConfig;
/**
 * 和DBConnection功能相似，唯一的不同此類
 * 是提供了對Oracle數(shù)據(jù)庫的操作方法和連接。
 * @author wbz
 *
 */
public class DBOracleConnection
{
    private static DBOracleConnection itsInstance = new DBOracleConnection();
    private String itsJDBCDriver = null;
    private String itsJDBCUrl = null;
    private Connection itsRealConn = null;
    String host = "220.178.27.84";
    String port = "1521";
//    String database = "platform";
    String database = "zxuehao";
    String user = "iim_405";
    String password = "ROAD887ANHUI";
/**
 * 構(gòu)造函數(shù)，獲取數(shù)據(jù)庫配置信息。
 *
 */
    private DBOracleConnection()
    {
        String host,port,database,str;
       
        host = DBConfig.getInstance().getProperty( "host" ).trim();
        port = DBConfig.getInstance().getProperty( "port" ).trim();
        database = DBConfig.getInstance().getProperty( "database" ).trim();
        user = DBConfig.getInstance().getProperty( "user" ).trim();
        password = DBConfig.getInstance().getProperty( "password" ).trim();
        
        itsJDBCUrl = "jdbc:oracle:thin:@" + host + ":" + port + ":" + database;
        itsJDBCDriver = "oracle.jdbc.driver.OracleDriver";
        try
        {
            Class.forName(itsJDBCDriver);
        } catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
    }

   
    public static DBOracleConnection getInstance()
    {
        return itsInstance;
    }
    /**
     * 關(guān)閉數(shù)據(jù)庫連接
     * @return
     */
    public void close()
    {
        if (itsRealConn == null)
            return;
        try
        {
            if (itsRealConn.isClosed())
                return;
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
        try
        {
            itsRealConn.close();
        } catch (SQLException e1)
        {
            e1.printStackTrace();
        } finally
        {
            itsRealConn = null;
        }
    }
    /**
     * 當(dāng)從oracle的clob類型的字段中獲取數(shù)據(jù)時，
     * 需要把clob類型轉(zhuǎn)換為字符串類型。
     * 此方法是把指定的clob字段轉(zhuǎn)化為字符串。
     * @param clob
     * @return
     */
    public static String clobTOstring(Clob clob)
	{
		String s1= "";   
		char   ac[] = new char[200];   
		if(clob == null)   
			return   null;   
		Reader reader;
		try 
		{
			reader = clob.getCharacterStream();		  
			int   i;   
			while((i = reader.read(ac,0,200)) != -1)     
				s1 = s1+ new String(ac, 0, i);  
			reader.close();   
		}   
		catch(Exception   exception1)   
		{   
		}   
		return  s1;
	}
    /**
     * 執(zhí)行查詢操作。
     * @param sql
     * @return
     */
    public ResultSet executeQuery( String sql )
    {
        ResultSet rs=null;

        OracleStatement stmt = null;
		try
		{
			if( itsRealConn==null || itsRealConn.isClosed() )
			{
				itsRealConn = DriverManager.getConnection( itsJDBCUrl,this.user,this.password );
			}
			stmt = (OracleStatement)itsRealConn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE );
			rs = stmt.executeQuery( sql );

		} catch (SQLException e)		
		{
			e.printStackTrace();
		}
/*        
        Statement stmt = null;
        try
        {
            if( itsRealConn==null || itsRealConn.isClosed() )
            {
                itsRealConn = DriverManager.getConnection( itsJDBCUrl );
            }
            stmt = itsRealConn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE );
            rs = stmt.executeQuery( sql );
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
*/
        return rs;
    }
/**
 * 執(zhí)行插入操作。
 * @param sql
 */
    public void executeInsert(String sql)
    {
        OracleStatement stmt = null;
        try
        {
            if (itsRealConn == null || itsRealConn.isClosed())
            {
                itsRealConn = DriverManager.getConnection(itsJDBCUrl,
                        this.user, this.password);
            }
            stmt = (OracleStatement) itsRealConn
                    .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                            ResultSet.CONCUR_UPDATABLE);
            stmt.execute(sql);

        } catch (SQLException e)
        {
            FileLoggerError.getInstance().warning(sql);
            FileLoggerError.getInstance().warning(e.getMessage());
            e.printStackTrace();
        }
        try
        {
            if (stmt != null)
            {
                stmt.close();
                stmt = null;
            }
        } catch (SQLException e1)
        {
            e1.printStackTrace();
        }

    }
/**
 * 執(zhí)行插入操作，
 * 此法是當(dāng)需要對含有clob類型字段的表中插入數(shù)據(jù)時用到的方法。
 * @param sql
 * @param clobStr
 */
    public void executeInsert(String sql, String clobStr)
    {

        OraclePreparedStatement ps = null;
        CLOB clob = null;
        try
        {
            if (itsRealConn == null || itsRealConn.isClosed())
            {
                itsRealConn = DriverManager.getConnection(itsJDBCUrl,
                        this.user, this.password);
//                System.out.println(this.user+":"+this.password);
            }
            ps = (OraclePreparedStatement) itsRealConn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                            ResultSet.CONCUR_UPDATABLE);

            clob = CLOB.createTemporary(itsRealConn, true, CLOB.DURATION_SESSION);
            clob.putString(1, clobStr);
            ps.setCLOB(1, clob);
            ps.execute();
            itsRealConn.commit();

        } catch (SQLException e)
        {
           // System.out.println(sql);
            FileLoggerInsertDBError.getInstance().info(sql+"\n"+ e.getMessage()+"\n");
            e.printStackTrace();

        } finally
        {
            try
            {
                if (clob != null)
                {
                    if (clob.isOpen())
                        clob.close();
                    clob = null;
                }
                if ((ps != null))
                {
                    ps.close();
                    ps = null;
                }

            } catch (SQLException e1)
            {
                e1.printStackTrace();
            }
        }
    }
    public static void main(String args[])
    {
    	DBOracleConnection.getInstance().executeInsert(null, "clobStr");
    }
}