public void runInternal() throws DatabaseUnavailableException {
SQLiteDatabase database = HubApplication._().getDatabaseHandle();
Cursor c = database.query("SyncableUser", null, null, null, null, null, null);
try {
SyncableUser actionable = null;
for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
SyncableUser u = SyncableUser.fromDb(c);
if (u.getStatus() != SyncableUser.UserStatus.ModelFetched && u.getStatus() != SyncableUser.UserStatus.AuthError) {
//Only grab the first user to act on, we don't wanna be greedy in this thread
actionable = u;
break;
}
}
if (actionable == null) {
return;
}
if (actionable.getStatus() == SyncableUser.UserStatus.Requested) {
fetchKeyForUser(actionable, database);
} else if(actionable.getStatus() == SyncableUser.UserStatus.KeysFetched) {
try {
fetchModelForUser(actionable, database);
} catch(IOException ioe) {
ServicesMonitor.reportMessage("Error fetching user model for " + actionable.getUsername()+ " - " + ioe.getMessage());
}
}
} finally {
c.close();
}
}
@Override
public void onCreate(SQLiteDatabase db) {
// creating required tables
db.execSQL(AnswerAccess.CREATE_TABLE);
db.execSQL(AnswerTypeAccess.CREATE_TABLE);
db.execSQL(QuestionAccess.CREATE_TABLE);
db.execSQL(SurveyResponseAccess.CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// create new tables
db.execSQL(DROP_TABLE_PRE_FIX + " " + AnswerAccess.TABLE_NAME);
db.execSQL(DROP_TABLE_PRE_FIX + " " + AnswerTypeAccess.TABLE_NAME);
db.execSQL(DROP_TABLE_PRE_FIX + " " + QuestionAccess.TABLE_NAME);
db.execSQL(DROP_TABLE_PRE_FIX + " " + SurveyResponseAccess.TABLE_NAME);
onCreate(db);
}
public static int executeSqlStatementsInTx(SQLiteDatabase db, String[] statements) {
db.beginTransaction();
try {
int count = executeSqlStatements(db, statements);
db.setTransactionSuccessful();
return count;
} finally {
db.endTransaction();
}
}
@Override
protected JSONArray doInBackground(String... params) {
JSONArray json = null;
SQLiteDatabase db = mDBHelper.getReadableDatabase();
try {
json = constructResponse(db.rawQuery(generateQuery(params[0]), new String[] {}), params[0]);
} catch (JSONException e) {
e.printStackTrace();
}
return json;
}
public List<TimelineEvent> allFor(String caseId) {
SQLiteDatabase database = masterRepository.getReadableDatabase();
Cursor cursor =
database.query(
TIMELINEEVENT_TABLE_NAME,
TIMELINEEVENT_TABLE_COLUMNS,
CASEID_COLUMN + " = ?",
new String[] {caseId},
null,
null,
null);
return readAllTimelineEvents(cursor);
}
@Override
public boolean execute(SQLiteDatabase database) {
database.execSQL("create table t1(a INTEGER, b INTEGER);");
database.execSQL("insert into t1(a,b) values(123, 456);");
Cursor cursor = database.rawQuery("select * from t1;", new String[] {});
if (cursor != null) {
cursor.moveToFirst();
cursor.copyStringToBuffer(1, charArrayBuffer);
String actualValue = new String(charArrayBuffer.data, 0, charArrayBuffer.sizeCopied);
return "456".equals(actualValue);
}
return false;
}
public synchronized void save(SQLiteDatabase database) {
ContentValues values = Gen_getValues();
values.remove(GEN_FIELD__ID);
// Never save the SSH password and passphrase.
values.put(GEN_FIELD_SSHPASSWORD, "");
values.put(GEN_FIELD_SSHPASSPHRASE, "");
if (!getKeepPassword()) {
values.put(GEN_FIELD_PASSWORD, "");
}
if (isNew()) {
set_Id(database.insert(GEN_TABLE_NAME, null, values));
} else {
database.update(
GEN_TABLE_NAME, values, GEN_FIELD__ID + " = ?", new String[] {Long.toString(get_Id())});
}
}
/**
* Utility method to run the query on the db and return the value in the first column of the first
* row.
*/
public static String stringForQuery(SQLiteDatabase db, String query, String[] selectionArgs) {
SQLiteStatement prog = db.compileStatement(query);
try {
return stringForQuery(prog, selectionArgs);
} finally {
prog.close();
}
}
private SQLiteStatement getStatement(boolean allowReplace) throws SQLException {
if (allowReplace) {
if (mReplaceStatement == null) {
if (mInsertSQL == null) buildSQL();
// chop "INSERT" off the front and prepend "INSERT OR REPLACE" instead.
String replaceSQL = "INSERT OR REPLACE" + mInsertSQL.substring(6);
mReplaceStatement = mDb.compileStatement(replaceSQL);
}
return mReplaceStatement;
} else {
if (mInsertStatement == null) {
if (mInsertSQL == null) buildSQL();
mInsertStatement = mDb.compileStatement(mInsertSQL);
}
return mInsertStatement;
}
}
public void saveAndWriteRecent(boolean saveEmpty) {
Database database = new Database(c);
// We need server address or SSH server to be filled out to save. Otherwise,
// we keep adding empty connections.
// However, if there is partial data from a URI, we can present the edit screen.
// Alternately, perhaps we could process some extra data
if ((getConnectionType() == Constants.CONN_TYPE_SSH && getSshServer().equals("")
|| getAddress().equals(""))
&& !saveEmpty) {
return;
}
SQLiteDatabase db = database.getWritableDatabase();
db.beginTransaction();
try {
save(db);
MostRecentBean mostRecent = getMostRecent(db);
if (mostRecent == null) {
mostRecent = new MostRecentBean();
mostRecent.setConnectionId(get_Id());
mostRecent.Gen_insert(db);
} else {
mostRecent.setConnectionId(get_Id());
mostRecent.Gen_update(db);
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
if (db.isOpen()) {
db.close();
}
}
@Override
public void onCreate(SQLiteDatabase database) {
try {
database.beginTransaction();
TableBuilder builder = new TableBuilder(ApplicationRecord.class);
database.execSQL(builder.getTableCreateString());
builder = new TableBuilder(AndroidSharedKeyRecord.class);
database.execSQL(builder.getTableCreateString());
builder = new TableBuilder(AndroidLogEntry.STORAGE_KEY);
builder.addData(new AndroidLogEntry());
database.execSQL(builder.getTableCreateString());
DbUtil.createNumbersTable(database);
database.setVersion(GLOBAL_DB_VERSION);
database.setTransactionSuccessful();
} finally {
database.endTransaction();
}
}
/**
* Query the table for the number of rows in the table.
*
* @param db the database the table is in
* @param table the name of the table to query
* @return the number of rows in the table
*/
public static long queryNumEntries(SQLiteDatabase db, String table) {
Cursor cursor = db.query(table, countProjection, null, null, null, null, null);
try {
cursor.moveToFirst();
return cursor.getLong(0);
} finally {
cursor.close();
}
}
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.sqlcipher);
ButterKnife.bind(this);
SQLiteDatabase.loadLibs(this);
MyDataBaseOpenHelper helper = new MyDataBaseOpenHelper(this, "demo.db", null, 1);
database = helper.getWritableDatabase("secret_key");
}
public static void logTableDump(SQLiteDatabase db, String tablename) {
Cursor cursor = db.query(tablename, null, null, null, null, null, null);
try {
String dump = DatabaseUtils.dumpCursorToString(cursor);
DaoLog.d(dump);
} finally {
cursor.close();
}
}
public JSONArray getObservationsFor(JSONObject params) throws JSONException {
String patientUuid = params.getString("patientUuid");
JSONArray conceptNamesArray = params.getJSONArray("conceptNames");
String conceptNames = conceptNamesArray.toString();
JSONArray visitUuidsArray = params.getJSONArray("visitUuids");
String visitUuids = visitUuidsArray.toString();
SQLiteDatabase db = mDBHelper.getReadableDatabase();
JSONArray observations = new JSONArray();
String inClauseConceptNameList =
conceptNamesArray.length() > 0
? conceptNames.substring(2, conceptNames.length() - 2)
: conceptNames;
String inClauseVisitUuidsList =
visitUuidsArray.length() > 0
? visitUuids.substring(2, visitUuids.length() - 2)
: visitUuids;
Cursor c =
db.rawQuery(
"SELECT observationJson from observation"
+ " WHERE patientUuid = '"
+ patientUuid
+ "'"
+ " AND conceptName in (\""
+ inClauseConceptNameList
+ "\") "
+ " AND ( visitUuid in (\""
+ inClauseVisitUuidsList
+ "\") OR visitUuid is NULL )",
new String[] {});
if (c.getCount() < 1) {
c.close();
return new JSONArray();
}
c.moveToFirst();
for (int index = 0; index < c.getCount(); index++) {
JSONObject observationJson = new JSONObject();
observationJson.put(
"observation", new JSONObject(c.getString(c.getColumnIndex("observationJson"))));
observations.put(index, observationJson);
c.moveToNext();
}
c.close();
return observations;
}
public static String singleValueFromQuery(SQLiteDatabase database, String query) {
Cursor cursor = database.rawQuery(query, new String[] {});
String value = "";
if (cursor != null) {
cursor.moveToFirst();
value = cursor.getString(0);
cursor.close();
}
return value;
}
public static int executeSqlStatements(SQLiteDatabase db, String[] statements) {
int count = 0;
for (String line : statements) {
line = line.trim();
if (line.length() > 0) {
db.execSQL(line);
count++;
}
}
return count;
}
public JSONArray insertObservationData(
String patientUuid, String visitUuid, JSONArray observationData) throws JSONException {
SQLiteDatabase db = mDBHelper.getWritableDatabase();
ContentValues values = new ContentValues();
for (int index = 0; index < observationData.length(); index++) {
JSONObject observation = observationData.getJSONObject(index);
String observationUuid = observation.getString("uuid");
if (observation.getJSONArray("groupMembers").length() > 0) {
values.put("uuid", observationUuid);
values.put("patientUuid", patientUuid);
values.put("visitUuid", visitUuid);
values.put("conceptName", observation.getJSONObject("concept").getString("name"));
values.put("encounterUuid", observation.getString("encounterUuid"));
values.put("observationJson", String.valueOf(observation));
db.insertWithOnConflict("observation", null, values, SQLiteDatabase.CONFLICT_REPLACE);
} else {
deleteObservationByUuid(db, observationUuid, patientUuid);
}
}
db.close();
return observationData;
}
@Override
public boolean isEmpty(SQLiteDatabase database) {
Cursor cursor =
database.query(getTableName(), new String[] {COLUMN_ID}, null, null, null, null, null, "1");
try {
if (cursor.moveToFirst()) {
return false;
}
} finally {
cursor.close();
}
return true;
}
@OnClick({R.id.add_data, R.id.query_data})
public void onClick(View view) {
switch (view.getId()) {
case R.id.add_data:
ContentValues values = new ContentValues();
values.put("name", "GOT");
values.put("pages", 566);
database.insert("Book", null, values);
break;
case R.id.query_data:
Cursor cursor = database.query("Book", null, null, null, null, null, null);
if (cursor != null) {
while (cursor.moveToNext()) {
String name = cursor.getString(cursor.getColumnIndex("name"));
int pages = cursor.getInt(cursor.getColumnIndex("pages"));
Log.d("TAG", "book names is " + name);
Log.d("TAG", "book pages is " + pages);
}
}
cursor.close();
break;
}
}
public JSONArray getObservationsForVisit(String visitUuid) throws JSONException {
SQLiteDatabase db = mDBHelper.getReadableDatabase();
Cursor c =
db.rawQuery(
"SELECT observationJson FROM observation WHERE visitUuid = '" + visitUuid + "'",
new String[] {});
JSONArray observations = new JSONArray();
if (c.getCount() < 1) {
c.close();
return observations;
}
c.moveToFirst();
for (int index = 0; index < c.getCount(); index++) {
JSONObject observationJson = new JSONObject();
observationJson.put(
"observation", new JSONObject(c.getString(c.getColumnIndex("observationJson"))));
observations.put(index, observationJson);
c.moveToNext();
}
return observations;
}
private void buildSQL() throws SQLException {
StringBuilder sb = new StringBuilder(128);
sb.append("INSERT INTO ");
sb.append(mTableName);
sb.append(" (");
StringBuilder sbv = new StringBuilder(128);
sbv.append("VALUES (");
int i = 1;
Cursor cur = null;
try {
cur = mDb.rawQuery("PRAGMA table_info(" + mTableName + ")", null);
mColumns = new HashMap<String, Integer>(cur.getCount());
while (cur.moveToNext()) {
String columnName = cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX);
String defaultValue = cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX);
mColumns.put(columnName, i);
sb.append("'");
sb.append(columnName);
sb.append("'");
if (defaultValue == null) {
sbv.append("?");
} else {
sbv.append("COALESCE(?, ");
sbv.append(defaultValue);
sbv.append(")");
}
sb.append(i == cur.getCount() ? ") " : ", ");
sbv.append(i == cur.getCount() ? ");" : ", ");
++i;
}
} finally {
if (cur != null) cur.close();
}
sb.append(sbv);
mInsertSQL = sb.toString();
if (LOCAL_LOGV) Log.v(TAG, "insert statement is " + mInsertSQL);
}
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
SQLiteDatabase.loadLibs(this);
mDBHelper = OpenHelperManager.getHelper(this, DBHelper.class);
mTemp = mDBHelper.getRuntimeExceptionDao(Temp.class);
// 添加数据库数据库源
findViewById(R.id.add_data)
.setOnClickListener(
new OnClickListener() {
@Override
public void onClick(View v) {
Temp mtep = new Temp();
mtep.name = "aaaaa";
mtep.pwd = "bbbb";
mTemp.create(mtep);
}
});
// 查询数据
findViewById(R.id.query_data)
.setOnClickListener(
new OnClickListener() {
@Override
public void onClick(View v) {
try {
List<Temp> list = mTemp.queryForAll();
for (int i = 0; i < list.size(); i++) {
System.out.println("temp->" + list.get(i));
}
} catch (Exception e) {
}
}
});
}
/** Executes a DELETE query. */
public int delete(SQLiteDatabase db) {
return db.delete(mTable, mCondition, mArgs);
}
/** Executes an UPDATE query. */
public int update(SQLiteDatabase db, ContentValues values) {
return db.update(mTable, values, mCondition, mArgs);
}
/** Executes a SELECT query. */
public Cursor select(SQLiteDatabase db, String... columns) {
return db.query(mTable, columns, mCondition, mArgs, mGroupBy, null, mOrderBy, null);
}
/** @param context */
private OpenHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
SQLiteDatabase.loadLibs(context);
this.generateEncryptKey(context);
}
private void initializeSQLCipher() {
SQLiteDatabase.loadLibs(this);
}
@Override
public void onCreate(SQLiteDatabase db) {
String sql =
"create table " + TABLE + "( " + ALIAS + " text not null, " + KEY_DATA + " blob not null);";
db.execSQL(sql);
}