@Override
public boolean checkNImage(String nImage) {
boolean isExists = false;
Cursor cur = null;
String sql =
"select count("
+ BaseColumns._ID
+ ") from "
+ FY2Columns.TABLE_NAME
+ " where "
+ FY2Columns.NIMAGE
+ " = ?";
String[] selectionArgs = {nImage};
try {
cur = getSQLiteDatabase().rawQuery(sql, selectionArgs);
while (cur.moveToNext()) {
int count = cur.getInt(0);
if (count > 0) {
isExists = true;
}
break;
}
} catch (SQLException ex) {
Log.e(TAG, "checkNImage failed! - " + ex.getMessage());
} finally {
if (cur != null) cur.close();
}
return isExists;
}
void createTable() {
SQLiteDatabase db = Obtainer.AcquireDB();
db.beginTransaction();
boolean createComplete = false;
try {
Obtainer.getDB().execSQL(CREATE);
createComplete = true;
} catch (SQLException ex) {
String msg = null;
if (ex != null) {
msg = ex.getMessage();
}
if (msg != null && msg.contains("already exists")) {
createComplete = true;
} else {
createComplete = false;
}
}
if (createComplete) {
db.setTransactionSuccessful();
}
db.endTransaction();
Obtainer.ReleaseDB();
}
private void saveAccelerometerDevice(Sensor acc) {
Cursor accelInfo =
getContentResolver().query(Accelerometer_Sensor.CONTENT_URI, null, null, null, null);
if (accelInfo == null || !accelInfo.moveToFirst()) {
ContentValues rowData = new ContentValues();
rowData.put(
Accelerometer_Sensor.DEVICE_ID,
Aware.getSetting(getApplicationContext(), Aware_Preferences.DEVICE_ID));
rowData.put(Accelerometer_Sensor.TIMESTAMP, System.currentTimeMillis());
rowData.put(Accelerometer_Sensor.MAXIMUM_RANGE, acc.getMaximumRange());
rowData.put(Accelerometer_Sensor.MINIMUM_DELAY, acc.getMinDelay());
rowData.put(Accelerometer_Sensor.NAME, acc.getName());
rowData.put(Accelerometer_Sensor.POWER_MA, acc.getPower());
rowData.put(Accelerometer_Sensor.RESOLUTION, acc.getResolution());
rowData.put(Accelerometer_Sensor.TYPE, acc.getType());
rowData.put(Accelerometer_Sensor.VENDOR, acc.getVendor());
rowData.put(Accelerometer_Sensor.VERSION, acc.getVersion());
try {
getContentResolver().insert(Accelerometer_Sensor.CONTENT_URI, rowData);
Intent accel_dev = new Intent(ACTION_AWARE_ACCELEROMETER);
accel_dev.putExtra(EXTRA_SENSOR, rowData);
sendBroadcast(accel_dev);
if (Aware.DEBUG) Log.d(TAG, "Accelerometer device:" + rowData.toString());
} catch (SQLiteException e) {
if (Aware.DEBUG) Log.d(TAG, e.getMessage());
} catch (SQLException e) {
if (Aware.DEBUG) Log.d(TAG, e.getMessage());
}
} else accelInfo.close();
}
public void insert(ChannelWithTvGuide c) {
if (db == null) {
db = getDatabase().getWritableDatabase();
db.beginTransaction();
}
ContentValues values = new ContentValues();
values.putNull(SQLiteProvider.CHANNEL_TBL_COLUMN_ID);
values.put(SQLiteProvider.CHANNEL_TBL_COLUMN_KEY, c.getKey());
values.put(SQLiteProvider.CHANNEL_TBL_COLUMN_TITLE, c.getDescription());
try {
long id = db.insert(SQLiteProvider.CHANNEL_TBL_NAME, null, values);
Collection<TvShow> shows = c.getSortedListing();
Log.d(TAG, "Channel '" + c.getKey() + "' has " + shows.size() + " shows.");
for (TvShow show : shows) {
insert(id, show);
}
} catch (SQLException se) {
Log.d(TAG, se.getMessage());
}
}
public boolean deleteAll() {
boolean flag = false;
String deleteHeadSql = "DELETE FROM ProductGroupHead";
String deleteDetailSql = "DELETE FROM ProductGroupDetail";
SQLiteDatabase db = AssetsDatabaseManager.getManager().getDatabase();
try {
// 开启事务
db.beginTransaction();
SQLiteStatement detail_stat = db.compileStatement(deleteDetailSql);
detail_stat.executeUpdateDelete();
SQLiteStatement head_stat = db.compileStatement(deleteHeadSql);
head_stat.executeUpdateDelete();
// 数据插入成功，设置事物成功标志
db.setTransactionSuccessful();
// 保存数据
db.endTransaction();
flag = true;
} catch (SQLException e) {
// 结束事物，在这里没有设置成功标志，结束后不保存
ZillionLog.e(this.getClass().getName(), e.getMessage(), e);
db.endTransaction();
e.printStackTrace();
}
return flag;
}
public void addFavorite(int id, String name) {
try {
db.execSQL(
"INSERT INTO " + TABLE_FAVORITES + " (id, name) VALUES (" + id + ", '" + name + "');");
} catch (SQLException e) {
Log.d("DB", e.getMessage());
}
}
/**
* This test exercises the same code as {@link CsvTableWriter#dumpToCsv}. When upgrading the db
* version, any new table will throw this exception. The test ensures that the exception will get
* caught and is mainly here for future-proofing.
*/
public void testBackupMissingTableThrows() {
try {
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor c = db.query("bogus_table", null, null, null, null, null, null);
fail();
} catch (SQLException e) {
assertTrue(e.getMessage().contains("no such table"));
}
}
// Table: Exec SQL
public boolean execSQL(String SQL) {
try {
database.execSQL(SQL);
return true;
} catch (SQLException ex) {
setError(ex.getMessage());
return false;
}
}
/**
* 批量增加产品组合主表记录数据
*
* @param list
*/
public boolean batchAddProductGroupHead(List<ProductGroupHeadData> list) {
boolean flag = false;
String headSql =
"insert into ProductGroupHead(PG1_ID,PG1_M02_ID,PG1_CU1_ID,PG1_CODE,PG1_Name,PG1_CreateUser,PG1_CreateTime,PG1_ModifyUser,PG1_ModifyTime,PG1_RowVersion)"
+ "values(?,?,?,?,?,?,?,?,?,?)";
String detailSql =
"insert into ProductGroupDetail(PG2_ID,PG2_M02_ID,PG2_PG1_ID,PG2_PD1_ID,PG2_GroupQty,PG2_CreateUser,PG2_CreateTime,PG2_ModifyUser,PG2_ModifyTime,PG2_RowVersion)"
+ "values(?,?,?,?,?,?,?,?,?,?)";
SQLiteDatabase db = AssetsDatabaseManager.getManager().getDatabase();
try {
// 开启事务
db.beginTransaction();
for (ProductGroupHeadData productGroupHead : list) {
SQLiteStatement stat = db.compileStatement(headSql);
stat.bindString(1, productGroupHead.getPg1Id());
stat.bindString(2, productGroupHead.getPg1M02Id());
stat.bindString(3, productGroupHead.getPg1Cu1Id());
stat.bindString(4, productGroupHead.getPg1Code());
stat.bindString(5, productGroupHead.getPg1Name());
stat.bindString(6, productGroupHead.getPg1CreateUser());
stat.bindString(7, productGroupHead.getPg1CreateTime());
stat.bindString(8, productGroupHead.getPg1ModifyUser());
stat.bindString(9, productGroupHead.getPg1ModifyTime());
stat.bindString(10, productGroupHead.getPg1RowVersion());
stat.executeInsert();
List<ProductGroupDetailData> children = productGroupHead.getChildren();
if (children != null) {
for (ProductGroupDetailData productGroupDetail : children) {
SQLiteStatement detail_stat = db.compileStatement(detailSql);
detail_stat.bindString(1, productGroupDetail.getPg2Id());
detail_stat.bindString(2, productGroupDetail.getPg2M02Id());
detail_stat.bindString(3, productGroupDetail.getPg2Pg1Id());
detail_stat.bindString(4, productGroupDetail.getPg2Pd1Id());
detail_stat.bindLong(5, productGroupDetail.getPg2GroupQty());
detail_stat.bindString(6, productGroupDetail.getPg2CreateUser());
detail_stat.bindString(7, productGroupDetail.getPg2CreateTime());
detail_stat.bindString(8, productGroupDetail.getPg2ModifyUser());
detail_stat.bindString(9, productGroupDetail.getPg2ModifyTime());
detail_stat.bindString(10, productGroupDetail.getPg2RowVersion());
detail_stat.executeInsert();
}
}
}
// 数据插入成功，设置事物成功标志
db.setTransactionSuccessful();
// 保存数据
db.endTransaction();
flag = true;
} catch (SQLException e) {
// 结束事物，在这里没有设置成功标志，结束后不保存
ZillionLog.e(this.getClass().getName(), e.getMessage(), e);
db.endTransaction();
e.printStackTrace();
}
return flag;
}
public void read(SQLiteDatabase db, String fromJid) {
try {
db.execSQL(
"UPDATE " + TABLE_CHAT + " set STATE = ? where FROMJID = ? and STATE = ?",
new Object[] {Message.READ, Message.getUserId(fromJid), Message.UNREAD});
} catch (SQLException e) {
Log.e(TAG, "Error adding new message:" + e.getMessage(), e);
}
}
@Override
public void onCreate(SQLiteDatabase db) {
try {
db.execSQL(DATABASE_CREATE_TAB_GOMEE);
} catch (SQLException ex) {
Log.i("onCreate DB", ex.getMessage());
}
}
public List<Message> getThreads(
SQLiteDatabase db, int myUserId, boolean both, int limit, int offset) {
List<Message> list = new ArrayList<Message>();
String myId = String.valueOf(myUserId);
try {
String sql =
"(select max(id) as latest_id from "
+ TABLE_CHAT
+ " group by max(FROMJID, TOJID), "
+ "min(FROMJID, TOJID) having (FROMJID = '"
+ myId
+ "' or TOJID = '"
+ myId
+ "' "
+ ")order by latest_id desc limit "
+ limit
+ " offset "
+ offset
+ ") AS threads";
sql =
"select * from "
+ TABLE_CHAT
+ ", "
+ sql
+ " where "
+ TABLE_CHAT
+ ".id = threads.latest_id;";
Cursor cursor = db.rawQuery(sql, null);
int count = cursor.getCount();
if (count > 0) {
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
// "(ID INTEGER PRIMARY KEY,FROM TEXT,TO TEXT,TIME LONG,TEXT TEXT, ATTACHID TEXT,
// ATTACHTYPE INTEGER, STATE INTEGER);";
Message msg = new Message(cursor.getString(2), Message.MSG_TYPE_CHAT);
msg.setFrom(cursor.getString(1));
msg.setBody(cursor.getString(4));
msg.setTimestamp(new Date(cursor.getLong(3)));
msg.setState(cursor.getInt(7));
msg.setExtraType(cursor.getString(8));
msg.setExtraJson(cursor.getString(9));
msg.setSubject(cursor.getString(10));
msg.setThread(msg.getFromUserId().equals(myId) ? msg.getToUserId() : msg.getFromUserId());
list.add(msg);
cursor.moveToNext();
}
cursor.close();
return list;
} else {
cursor.close();
}
} catch (SQLException e) {
Log.e(TAG, "Error getRecords" + e.getMessage());
}
return list;
}
public boolean delDespesa(int id) {
try {
String query = "DELETE FROM Despesas WHERE rowid=" + id;
this.bancoDeDados.execSQL(query);
return true;
} catch (SQLException e) {
Log.e("TripLogDB", e.getMessage());
return false;
}
}
public void execSQL(SQLiteDatabase db, String sql, Object[] params) {
db.beginTransaction();
try {
db.execSQL(sql, params);
db.setTransactionSuccessful();
} catch (SQLException e) {
Log.e(TAG, "Error execSQL " + sql + ";ErrorMessage:" + e.getMessage());
} finally {
db.endTransaction();
}
}
public RepeticionesCRUD(Context context) {
this.mContext = context;
mDbHelper = new Helper(context);
// open the database
try {
open();
} catch (SQLException e) {
Log.e(TAG, "SQLException on openning database " + e.getMessage());
e.printStackTrace();
}
}
public boolean delDespesas(int idViagem) {
try {
Log.i(null, "APAGANDO despesas " + idViagem);
String query = "DELETE FROM Despesas WHERE " + BancoDeDados.COL_ID_VIAGEM + "=" + idViagem;
this.bancoDeDados.execSQL(query);
return true;
} catch (SQLException e) {
Log.e("TripLogDB", e.getMessage());
return false;
}
}
public boolean delDespesasPagasCom(int carteiraId) {
try {
String query =
"DELETE FROM Despesas WHERE " + BancoDeDados.DESPESA_COL_PAGOCOM + "=" + carteiraId;
this.bancoDeDados.execSQL(query);
return true;
} catch (SQLException e) {
Log.e("TripLogDB", e.getMessage());
return false;
}
}
@Override
public void addClient(String mac, String ip) {
ContentValues cv = new ContentValues(2);
cv.put(DatabaseHelper.DATABASE_MAC, mac);
cv.put(DatabaseHelper.DATABASE_IP, ip);
SQLiteDatabase db = mDatabaseH.getWritableDatabase();
try {
db.replaceOrThrow(DatabaseHelper.DATABASE_TABLE, null, cv);
} catch (SQLException e) {
Log.e(AperiMainActivity.TAG, "Could not add/replace [" + mac + "]:\n" + e.getMessage());
}
}
/**
* 用于执行数据的增删改操作
*
* @param sql 以insert | update |delete打头的SQL语句
*/
public void execSQL(String sql) {
db = helper.getWritableDatabase();
try {
db.execSQL(sql);
// CommonUtil.Log("sqy", "DBHelper", "execSQL:sql=" + sql, 'i');
} catch (SQLException sqlEx) {
System.out.println(sqlEx.getMessage());
} finally {
db.close();
}
}
/**
* 用于执行数据的查询操作
*
* @param sql以select打头的SQL语句
* @return
*/
public Cursor rawQuery(String sql) {
db = helper.getReadableDatabase();
Cursor cursor = null;
try {
cursor = db.rawQuery(sql, null);
// CommonUtil.Log("sqy", "DBHelper", "rawQuery：sql=" + sql, 'i');
return cursor;
} catch (SQLException e) {
System.out.println(e.getMessage());
return null;
}
}
// load/reload the active profile
private void loadProfileDetails() {
UserDBAdapter dbUser = new UserDBAdapter(this);
try {
dbUser.open();
Cursor c = dbUser.getProfile(username);
dbUser.close();
EditText et = (EditText) findViewById(R.id.editTextName);
et.setText(c.getString(0));
setEditableOff(et);
et = (EditText) findViewById(R.id.editTextDOB);
et.setText(c.getString(1));
setEditableOff(et);
et = (EditText) findViewById(R.id.editTextGender);
et.setText(c.getString(2));
setEditableOff(et);
et = (EditText) findViewById(R.id.editTextLocation);
et.setText(c.getString(3));
setEditableOff(et);
et = (EditText) findViewById(R.id.editTextInfo);
et.setText(c.getString(4));
setEditableOff(et);
et = (EditText) findViewById(R.id.editTextEmail);
et.setText(c.getString(5));
setEditableOff(et);
if (!c.getString(6).equals("empty")) {
File imgFile = new File(c.getString(6));
if (imgFile.exists()) {
Bitmap myBitmap = BitmapFactory.decodeFile(imgFile.getAbsolutePath());
// Add image to the image view
ImageView myImage = (ImageView) findViewById(R.id.profileImage);
myImage.setImageBitmap(myBitmap);
} else {
ImageView myImage = (ImageView) findViewById(R.id.profileImage);
myImage.setImageResource(R.drawable.ic_launcher);
}
} else {
ImageView myImage = (ImageView) findViewById(R.id.profileImage);
myImage.setImageResource(R.drawable.ic_launcher);
}
} catch (SQLException ex) {
Toast.makeText(
getApplicationContext(),
"getProfile sqlexception:" + ex.getMessage(),
Toast.LENGTH_LONG)
.show();
}
}
void Initalize() {
Cursor cur = null;
try {
cur = getCursor();
} catch (SQLException ex) {
if (ex != null) {
if (ex.getMessage().startsWith("no such table")) {
createTable();
}
}
} finally {
closeCursor(cur);
}
}
public void deleteRecordsOlderThanDays(String purgeDay) {
if (purgeDay != null) {
try {
int num =
getWritableDatabase()
.delete(
"earthquakes",
" (strftime('%J','now','UTC') - strftime('%J',datetime, 'UTC')) > " + purgeDay,
null);
Log.i("Cleaning up old records in database", num + " records deleted successfully");
} catch (SQLException e) {
Log.e("Tried Cleaning up old data from database", e.getMessage());
}
}
}
@Override
public void remover(ClienteTipo clienteTipo) throws RepositorioException {
String _id = String.valueOf(clienteTipo.getId());
String where = ClienteTipoColunas.ID + "=?";
String[] whereArgs = new String[] {_id};
try {
super.getDb().delete(clienteTipo.getNomeTabela(), where, whereArgs);
} catch (SQLException sqe) {
Log.e(ConstantesSistema.LOG_TAG, sqe.getMessage() + " " + sqe.getCause());
sqe.printStackTrace();
throw new RepositorioException(context.getResources().getString(R.string.db_error));
}
}
@Override
public long inserir(ClienteTipo clienteTipo) throws RepositorioException {
ContentValues values = new ContentValues();
values = clienteTipo.carregarValues();
try {
return super.getDb().insert(clienteTipo.getNomeTabela(), "", values);
} catch (SQLException sqe) {
Log.e(ConstantesSistema.LOG_TAG, sqe.getMessage() + " " + sqe.getCause());
sqe.printStackTrace();
throw new RepositorioException(
context.getResources().getString(R.string.db_error_insert_record));
}
}
public List<Message> getMessages(
SQLiteDatabase db, String from, String to, boolean both, int limit, int offset) {
List<Message> list = new ArrayList<Message>();
try {
String fromJid = from.split("@")[0];
String toJid = to.split("@")[0];
String sql =
"SELECT * FROM "
+ TABLE_CHAT
+ " where (FROMJID = '"
+ fromJid
+ "' and TOJID = '"
+ toJid
+ "')";
if (both) {
sql += " OR (FROMJID = '" + toJid + "' and TOJID = '" + fromJid + "')";
}
sql += " order by TIME DESC limit " + limit + " offset " + offset + ";";
Cursor cursor = db.rawQuery(sql, null);
int count = cursor.getCount();
if (count > 0) {
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
// "(ID INTEGER PRIMARY KEY,FROM TEXT,TO TEXT,TIME LONG,TEXT TEXT, ATTACHID TEXT,
// ATTACHTYPE INTEGER, STATE INTEGER);";
Message msg = new Message(cursor.getString(2), Message.MSG_TYPE_CHAT);
msg.setId(cursor.getInt(0));
msg.setFrom(cursor.getString(1));
msg.setBody(cursor.getString(4));
msg.setTimestamp(new Date(cursor.getLong(3)));
msg.setState(cursor.getInt(7));
msg.setExtraType(cursor.getString(8));
msg.setExtraJson(cursor.getString(9));
msg.setSubject(cursor.getString(10));
list.add(msg);
cursor.moveToNext();
}
cursor.close();
return list;
} else {
cursor.close();
}
} catch (SQLException e) {
Log.e(TAG, "Error getRecords" + e.getMessage());
}
return list;
}
public void createAppInfo(ApplicationData appData) {
SQLiteDatabase db = getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_APPLICATIONNAME, "\"" + appData.get_applicationName() + "\"");
values.put(KEY_PACKAGENAME, "\"" + appData.get_packageName() + "\"");
values.put(KEY_CPUUSAGE, "\"" + appData.get_averageCPU() + "\"");
values.put(KEY_PRIVATEMEMORYUSAGE, "\"" + appData.get_averagePrivateMemoryUsage() + "\"");
values.put(KEY_SHAREDMEMORYUSAGE, "\"" + appData.get_averageSharedMemoryUsage() + "\"");
values.put(KEY_RECEIVEDDATA, "\"" + appData.get_averagereceivedData() + "\"");
values.put(KEY_SENTDATA, "\"" + appData.get_averageSentData() + "\"");
values.put(KEY_TIMESTAMP, "\"" + appData.get_timestamp() + "\"");
values.put(KEY_PID, "\"" + appData.get_pid() + "\"");
String queryString =
"insert into appInfo (applicationName,packageName,cpuUsage,privateMemoryUsage,sharedMemoryUsage,sentData,receiveddata,timestamp,pid)"
+ "values (\""
+ appData.get_applicationName()
+ "\",\""
+ appData.get_packageName()
+ "\",\""
+ appData.get_averageCPU()
+ "\",\""
+ appData.get_averagePrivateMemoryUsage()
+ "\","
+ "\""
+ appData.get_averageSharedMemoryUsage()
+ "\",\""
+ appData.get_averagereceivedData()
+ "\",\""
+ appData.get_averageSentData()
+ "\",\""
+ appData.get_timestamp()
+ "\",\""
+ appData.get_pid()
+ "\") ";
try {
db.execSQL(queryString);
} catch (SQLException ex) {
ex.getMessage();
}
// long rowInserted = db.insert(TABLE_APPINFO, null, values);
db.close();
}
public int getUnreadCount(SQLiteDatabase db) {
int unread = 0;
try {
Cursor cursor =
db.rawQuery(
"SELECT count(ID) FROM " + TABLE_CHAT + " where STATE = ?",
new String[] {String.valueOf(Message.UNREAD)});
int count = cursor.getCount();
if (count > 0 && cursor.moveToFirst()) {
unread = cursor.getInt(0);
}
cursor.close();
} catch (SQLException e) {
Log.e(TAG, "Error adding new message:" + e.getMessage(), e);
}
return unread;
}
@Override
public void atualizar(SetorComercial setorComercial) throws RepositorioException {
ContentValues values = setorComercial.carregarValues();
String _id = String.valueOf(setorComercial.getId());
String where = SetorComercialColunas.ID + "=?";
String[] whereArgs = new String[] {_id};
try {
super.getDb().update(setorComercial.getNomeTabela(), values, where, whereArgs);
} catch (SQLException sqe) {
Log.e(ConstantesSistema.LOG_TAG, sqe.getMessage() + " " + sqe.getCause());
sqe.printStackTrace();
throw new RepositorioException(context.getResources().getString(R.string.db_error));
}
}
protected void create(EarthQuake eachEarthQuake) {
if (eachEarthQuake != null) {
try {
ContentValues dbValues = new ContentValues();
dbValues.put("identifier", eachEarthQuake.getIdentifier());
dbValues.put("intensity", eachEarthQuake.getIntensity());
dbValues.put("location", eachEarthQuake.getLocation());
dbValues.put("longitude", eachEarthQuake.getLongitude());
dbValues.put("latitude", eachEarthQuake.getLatitude());
dbValues.put("datetime", eachEarthQuake.getDate());
dbValues.put("href", eachEarthQuake.getDetailLink());
getWritableDatabase().insertOrThrow("earthquakes", "datetime", dbValues);
} catch (SQLException e) {
Log.e("Creating new earthquake", e.getMessage());
}
}
}
