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();
}
}