package com.cb;

public class SMSInfo 
{
	public static String ITEMINDEX = "sms_index";
	public static String ITEMTO = "sms_to";
	public static String ITEMFROM = "sms_from";
	public static String ITEMMSG = "sms_msg";
	public static String ITEMMSG2 = "sms_msg_binary";
	
	int mIndex;
	private String mTo;
	private String mFrom;
	private String mMsg;
	private byte[] mMsg2;

	public SMSInfo(int index, String to, String from, String msg, byte[] msg2)
	{
		mIndex = index;
		mTo = to;
		mFrom = from;
		mMsg = msg;
		mMsg2 = msg2;
	}
	public int index()
	{
		return mIndex;
	}
	public String to()
	{
		return mTo;
	}
	public String from()
	{
		return mFrom;
	}
	public String msg()
	{
		return mMsg;
	}
	public byte[] msg2()
	{
		return mMsg2;
	}
}

package com.cb;

import java.sql.*;
import java.util.Vector;

import com.cb.CBLogger.Logger;

public class OracleAdaptor
{
	private static String TAG = "OracleAdaptor";
	private static Connection mConnection = null;
	private static Statement mStatement = null;
	private static PreparedStatement mPreparedStatement = null;
	private static ResultSet mResultSet = null;
	private static String TABLENAME = "sms_to_submit";
	private static String VARCHAR32 = "varchar(32)";
	private static String VARCHAR512 = "varchar(512)";
	private static String INTEGER = "INTEGER";
	private static String BLOB = "BLOB";
	static 
	{
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}
		catch(Exception e)
		{
			Logger.w(TAG, "Initialize driver throw " + e.toString());
		}
	}
	boolean getConnection()
	{
		String msg = "Connecting to ";
		String url = "jdbc:oracle:" + "thin:@192.168.1.106:1521:mybase"; //ORCL
		msg += url;
		String user = "C##JACKY";
		String password = "1234";
		Logger.d(TAG, msg);
		try
		{
			try
			{
				if (mConnection != null)
				{
					mConnection.close();
					mConnection = null;
				}
			}
			catch(SQLException e)
			{
				Logger.w(TAG, e.getMessage());
			}
			mConnection = DriverManager.getConnection(url, user, password);
			Logger.d(TAG, "Connected.");
			return true;
		}
		catch(SQLException e)
		{
			mConnection = null;
			Logger.w(TAG, "getConnection throw " + e.getMessage());
			
		}
		return false;
	}
	private void clearResource()
	{
		try
		{
			if(mResultSet != null)
			{
				mResultSet.close();
				mResultSet = null;
			}
		}
		catch(Exception e)
		{
			
		}
		try
		{
			if(mStatement != null)
			{
				mStatement.close();
				mStatement = null;
			}
		}
		catch(Exception e)
		{
			
		}
		try
		{
			if(mPreparedStatement != null)
			{
				mPreparedStatement.close();
				mPreparedStatement = null;
			}
		}
		catch(Exception e)
		{
			
		}
		try
		{
			if(mConnection != null)
			{
				mConnection.close();
				mConnection = null;
			}
		}
		catch(Exception e)
		{
			
		}
	}
	public boolean createTable()
	{
		String createTableSql = "create table ";
		createTableSql = createTableSql + TABLENAME + "(" + SMSInfo.ITEMINDEX + " " + INTEGER + ","+  SMSInfo.ITEMTO + " " + VARCHAR32  + ","
			+ SMSInfo.ITEMFROM + " " + VARCHAR32  + ","+ SMSInfo.ITEMMSG + " " + VARCHAR512   + ","+ SMSInfo.ITEMMSG2 + " " + BLOB + ") ";
		try 
		{
			if(getConnection())
			{
				mStatement = mConnection.createStatement();
				mStatement.executeUpdate(createTableSql);
			}
		} 
		catch(SQLException ex) 
		{
			Logger.w(TAG, "createTable throw " + ex.getMessage());
			return false;
		}
		finally
		{
			clearResource();
		}
		return true;
	}
	public boolean dropTable()
	{
		String dropTableSql = "DROP TABLE " + TABLENAME;
		try 
		{
			if(getConnection())
			{
				mStatement = mConnection.createStatement();
				mStatement.executeUpdate(dropTableSql);
			}
		} 
		catch(SQLException ex) 
		{
			Logger.w(TAG, "dropTable throw " + ex.getMessage());
			return false;
		}
		finally
		{
			clearResource();
		}
		return true;
	}
	public boolean insert(Vector<SMSInfo> smss, Vector<SMSInfo> submitedSMSs)
	{
		String insertSql = "INSERT INTO " + TABLENAME + "(" + SMSInfo.ITEMINDEX + ", " + SMSInfo.ITEMTO + "," + SMSInfo.ITEMFROM + ","
				+ SMSInfo.ITEMMSG + "," + SMSInfo.ITEMMSG2 + ")" + " VALUES (?, ?, ?, ?, ?)";
		
		try 
		{
			if(!getConnection())
			{
				return false;
			}
			for(int i = 0; i < smss.size(); i++)
			{
				SMSInfo sms = smss.get(i);
				mPreparedStatement = mConnection.prepareStatement(insertSql);
				mPreparedStatement.setInt(1, sms.index());
				mPreparedStatement.setString(2, sms.to());
				mPreparedStatement.setString(3, sms.from());
				mPreparedStatement.setString(4, sms.msg());
			    mPreparedStatement.setBytes(5, JSONAdaptor.mDefaultMsg2);
				mPreparedStatement.executeUpdate();
				mPreparedStatement.close();
				mPreparedStatement = null;
				submitedSMSs.add(sms);
			}
		} 
		catch (SQLException ex)
		{
			Logger.w(TAG, "insert throw " + ex.getMessage());
		}
		finally
		{
			clearResource();
		}	
		return true;
	}
	public boolean query(Vector<SMSInfo> smss)
	{
		String selectSql = "SELECT * FROM " + TABLENAME;
		try 
		{
			if(!getConnection())
			{
				return false;
			}
			mStatement = mConnection.createStatement();
		    mResultSet = mStatement.executeQuery(selectSql);
		    while(mResultSet.next())
		    {
				 //Retrieve by column name
		    	int index = mResultSet.getInt(SMSInfo.ITEMINDEX);
				String to = mResultSet.getString(SMSInfo.ITEMTO);
				String from = mResultSet.getString(SMSInfo.ITEMFROM);
				String msg = mResultSet.getString(SMSInfo.ITEMMSG);
				byte[] msg2 = mResultSet.getBytes(SMSInfo.ITEMMSG2);
				SMSInfo sms = new SMSInfo(index, to, from, msg, msg2);
				smss.add(sms);
		    }
		} 
		catch (SQLException ex)
		{
			Logger.w(TAG, "query throw " + ex.getMessage());
		}
		finally
		{
			clearResource();
		}	
		return true;
		
	}
	public boolean delete(Vector<Integer> indexs)
	{
		String deleteSql = "DELETE FROM  " + TABLENAME + " WHERE ";
		for(int i = 0; i < indexs.size(); i++)
		{
			if(i != 0)
			{
				deleteSql += " or ";
			}
			deleteSql += (SMSInfo.ITEMINDEX + " = '" + indexs.get(i) + "'" ) ;
		}
		
		try 
		{
			if(!getConnection())
			{
				return false;
			}
			mStatement = mConnection.createStatement();
		    mResultSet = mStatement.executeQuery(deleteSql);
		    
	      	mStatement.executeUpdate(deleteSql);
		} 
		catch (SQLException ex)
		{
			Logger.w(TAG, "delete throw " + ex.getMessage());
		}
		finally
		{
			clearResource();
		}	
		return true;
	}
	public void accessOracle()
	{
		dropTable();
		createTable();
		Vector<SMSInfo> toSubmit = new Vector<SMSInfo>();
		Vector<SMSInfo> submited = new Vector<SMSInfo>();
		for(int i = 0; i < 100; i++)
		{
			int index = i;
			String to = "1860000" + index;
			String from = "1880000" + index;
			String msg = "这个短信发给Alex，   谢谢。 ";
			toSubmit.add(new SMSInfo(index, to, from, msg, JSONAdaptor.mDefaultMsg2));
		}
		insert(toSubmit, submited);
		
		Vector<SMSInfo> quried = new Vector<SMSInfo>();
		query(quried);
		
		Vector<Integer> indexs = new Vector<Integer>();
		for(int i = 0; i < quried.size(); i++)
		{
			indexs.add(new Integer(quried.get(i).index()));
		}
		
		delete(indexs);
		
		quried.clear();
		query(quried);
	}
	public static void main(String[] args) 
	{
		OracleAdaptor dbTest = new OracleAdaptor();
	    dbTest.accessOracle();
	}
}