/**
* 单个数据查询 2
*
* @param sql 传入sql语句直接查询
* @return 单个数据指针
*/
public Cursor get(String sql) {
Cursor mCursor = db.rawQuery(sql, null);
// 如果指针存在，就把指针移到第一个条目上
if (mCursor != null) mCursor.moveToFirst();
return mCursor;
}
public void CarregaLista(String searched) {
SQLiteDatabase myDB = null;
try {
myDB = this.openOrCreateDatabase("skyzone", MODE_PRIVATE, null);
Cursor mCursor =
myDB.rawQuery("SELECT * FROM Zones WHERE Name LIKE '" + searched + "%';", null);
startManagingCursor(mCursor);
ListAdapter adapter =
new SimpleCursorAdapter(
this,
R.layout.list,
mCursor,
new String[] {"Icon", "Name", "Country", "State", "City", "Phone"},
new int[] {R.id.Icon, R.id.Name, R.id.Country, R.id.State, R.id.City, R.id.Phone});
this.setListAdapter(adapter);
this.getListView().setTextFilterEnabled(true);
} finally {
if (myDB != null) {
myDB.close();
}
}
}
// 获取某个数据库的表的名称
public String getTablesList() {
Cursor c = db.rawQuery("select name from sqlite_master where type='table' order by name", null);
String str = "";
while (c.moveToNext()) {
str += c.getString(0) + "\n";
}
return "表的名称为:\n" + str;
}
private static Cursor getMesesDiferentes() throws SQLException {
SQLiteDatabase conn = null;
Cursor rs = null;
String consulta = " Select distinct month from session ";
try {
conn = BaseDatos.getConn();
rs = conn.rawQuery(consulta, null);
} catch (SQLException e) {
throw (e);
}
return rs;
}
/**
* 列表查询用 2
*
* @param sql 传入sql语句直接查询
* @return 数据指针
*/
public Cursor getAll(String sql) {
return db.rawQuery(sql, null);
}
/**
* Executes a batch request and sends the results via sendJavascriptCB().
*
* @param dbname The name of the database.
* @param queryarr Array of query strings
* @param jsonparams Array of JSON query parameters
* @param queryIDs Array of query ids
* @param cbc Callback context from Cordova API
*/
private void executeSqlBatch(
String dbname,
String[] queryarr,
JSONArray[] jsonparams,
String[] queryIDs,
CallbackContext cbc) {
SQLiteDatabase mydb = this.getDatabase(dbname);
if (mydb == null) return;
String query = "";
String query_id = "";
int len = queryarr.length;
JSONArray batchResults = new JSONArray();
for (int i = 0; i < len; i++) {
query_id = queryIDs[i];
JSONObject queryResult = null;
String errorMessage = "unknown";
try {
boolean needRawQuery = true;
query = queryarr[i];
// UPDATE or DELETE:
// NOTE: this code should be safe to RUN with old Android SDK.
// To BUILD with old Android SDK remove lines from HERE: {{
if (android.os.Build.VERSION.SDK_INT >= 11
&& (query.toLowerCase().startsWith("update")
|| query.toLowerCase().startsWith("delete"))) {
SQLiteStatement myStatement = mydb.compileStatement(query);
if (jsonparams != null) {
for (int j = 0; j < jsonparams[i].length(); j++) {
if (jsonparams[i].get(j) instanceof Float || jsonparams[i].get(j) instanceof Double) {
myStatement.bindDouble(j + 1, jsonparams[i].getDouble(j));
} else if (jsonparams[i].get(j) instanceof Number) {
myStatement.bindLong(j + 1, jsonparams[i].getLong(j));
} else if (jsonparams[i].isNull(j)) {
myStatement.bindNull(j + 1);
} else {
myStatement.bindString(j + 1, jsonparams[i].getString(j));
}
}
}
int rowsAffected = -1; // (assuming invalid)
// Use try & catch just in case android.os.Build.VERSION.SDK_INT >= 11 is lying:
try {
rowsAffected = myStatement.executeUpdateDelete();
// Indicate valid results:
needRawQuery = false;
} catch (SQLiteException ex) {
// Indicate problem & stop this query:
ex.printStackTrace();
errorMessage = ex.getMessage();
Log.v(
"executeSqlBatch", "SQLiteStatement.executeUpdateDelete(): Error=" + errorMessage);
needRawQuery = false;
} catch (Exception ex) {
// Assuming SDK_INT was lying & method not found:
// do nothing here & try again with raw query.
}
if (rowsAffected != -1) {
queryResult = new JSONObject();
queryResult.put("rowsAffected", rowsAffected);
}
} // to HERE. }}
// INSERT:
if (query.toLowerCase().startsWith("insert") && jsonparams != null) {
needRawQuery = false;
SQLiteStatement myStatement = mydb.compileStatement(query);
for (int j = 0; j < jsonparams[i].length(); j++) {
if (jsonparams[i].get(j) instanceof Float || jsonparams[i].get(j) instanceof Double) {
myStatement.bindDouble(j + 1, jsonparams[i].getDouble(j));
} else if (jsonparams[i].get(j) instanceof Number) {
myStatement.bindLong(j + 1, jsonparams[i].getLong(j));
} else if (jsonparams[i].isNull(j)) {
myStatement.bindNull(j + 1);
} else {
myStatement.bindString(j + 1, jsonparams[i].getString(j));
}
}
long insertId = -1; // (invalid)
try {
insertId = myStatement.executeInsert();
} catch (SQLiteException ex) {
ex.printStackTrace();
errorMessage = ex.getMessage();
Log.v("executeSqlBatch", "SQLiteDatabase.executeInsert(): Error=" + errorMessage);
}
if (insertId != -1) {
queryResult = new JSONObject();
queryResult.put("insertId", insertId);
queryResult.put("rowsAffected", 1);
}
}
if (query.toLowerCase().startsWith("begin")) {
needRawQuery = false;
try {
mydb.beginTransaction();
queryResult = new JSONObject();
queryResult.put("rowsAffected", 0);
} catch (SQLiteException ex) {
ex.printStackTrace();
errorMessage = ex.getMessage();
Log.v("executeSqlBatch", "SQLiteDatabase.beginTransaction(): Error=" + errorMessage);
}
}
if (query.toLowerCase().startsWith("commit")) {
needRawQuery = false;
try {
mydb.setTransactionSuccessful();
mydb.endTransaction();
queryResult = new JSONObject();
queryResult.put("rowsAffected", 0);
} catch (SQLiteException ex) {
ex.printStackTrace();
errorMessage = ex.getMessage();
Log.v(
"executeSqlBatch",
"SQLiteDatabase.setTransactionSuccessful/endTransaction(): Error=" + errorMessage);
}
}
if (query.toLowerCase().startsWith("rollback")) {
needRawQuery = false;
try {
mydb.endTransaction();
queryResult = new JSONObject();
queryResult.put("rowsAffected", 0);
} catch (SQLiteException ex) {
ex.printStackTrace();
errorMessage = ex.getMessage();
Log.v("executeSqlBatch", "SQLiteDatabase.endTransaction(): Error=" + errorMessage);
}
}
// raw query for other statements:
if (needRawQuery) {
String[] params = null;
if (jsonparams != null) {
params = new String[jsonparams[i].length()];
for (int j = 0; j < jsonparams[i].length(); j++) {
if (jsonparams[i].isNull(j)) params[j] = "";
else params[j] = jsonparams[i].getString(j);
}
}
Cursor myCursor = mydb.rawQuery(query, params);
if (query_id.length() > 0) {
queryResult = this.getRowsResultFromQuery(myCursor);
}
myCursor.close();
}
} catch (Exception ex) {
ex.printStackTrace();
errorMessage = ex.getMessage();
Log.v("executeSqlBatch", "SQLitePlugin.executeSql[Batch](): Error=" + errorMessage);
}
try {
if (queryResult != null) {
JSONObject r = new JSONObject();
r.put("qid", query_id);
r.put("type", "success");
r.put("result", queryResult);
batchResults.put(r);
} else {
JSONObject r = new JSONObject();
r.put("qid", query_id);
r.put("type", "error");
JSONObject er = new JSONObject();
er.put("message", errorMessage);
r.put("result", er);
batchResults.put(r);
}
} catch (JSONException ex) {
ex.printStackTrace();
Log.v("executeSqlBatch", "SQLitePlugin.executeSql[Batch](): Error=" + ex.getMessage());
// TODO what to do?
}
}
cbc.success(batchResults);
}