public long[] getSize() {
long[] result = new long[] {0, 0};
Cursor c =
dbHelper
.getReadableDatabase()
.query(TABLE_NAME, null, COL_FILESIZE + " = -1", null, null, null, null);
if (c != null) {
if (c.moveToFirst()) {
c.close();
resetDB();
return result;
}
c.close();
}
c =
dbHelper
.getReadableDatabase()
.rawQuery("SELECT SUM(" + COL_FILESIZE + ") FROM " + TABLE_NAME, null);
if (c != null) {
if (c.moveToFirst()) result[0] = c.getInt(0);
c.close();
}
c =
dbHelper
.getReadableDatabase()
.rawQuery(
"SELECT SUM("
+ COL_FILESIZE
+ ") FROM "
+ TABLE_NAME
+ " WHERE "
+ COL_FILENAME
+ " LIKE '"
+ PREFIX_PAGES
+ "%' OR "
+ COL_FILENAME
+ " LIKE '"
+ PREFIX_DRAFTS
+ "%'",
null);
if (c != null) {
if (c.moveToFirst()) result[1] = c.getInt(0);
c.close();
}
return result;
}
@Override
public Cursor query(
Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
SQLiteDatabase db;
try {
db = dbOpenHelper.getWritableDatabase();
} catch (SQLiteException e) {
db = dbOpenHelper.getReadableDatabase();
}
String groupBy = null;
String having = null;
// creating a database query
SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
switch (uriMatcher.match(uri)) {
case SINGLE_ROW:
String rowID = uri.getPathSegments().get(1);
queryBuilder.appendWhere(DBHelper.NOTE_ID + "=" + rowID);
default:
break;
}
queryBuilder.setTables(DBHelper.TABLE_NAME);
Cursor cursor =
queryBuilder.query(db, projection, selection, selectionArgs, groupBy, having, sortOrder);
return cursor;
}
/**
* 查找数据库记录
*
* @return
* @throws SQLException
*/
public PasswordInfo find() throws SQLException {
PasswordInfo passwordInfo = null;
synchronized (dbHelper) {
SQLiteDatabase db = dbHelper.getReadableDatabase();
String querySql = "select * from password";
// 由于该表的特殊性，password最多只有一条记录
Cursor cursor = null;
try {
cursor = db.rawQuery(querySql, null);
if (cursor.moveToNext()) {
int _id = cursor.getInt(cursor.getColumnIndex("_id"));
String password = cursor.getString(cursor.getColumnIndex("password"));
String question = cursor.getString(cursor.getColumnIndex("question"));
String answer = cursor.getString(cursor.getColumnIndex("answer"));
int flag = cursor.getInt(cursor.getColumnIndex("flag"));
passwordInfo = new PasswordInfo(_id, password, question, answer, flag);
} else {
passwordInfo = null;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
cursor.close();
db.close();
}
}
return passwordInfo;
}
public ArrayList<Local> getLocals(int id_user, Context context) {
ArrayList<Local> locals = new ArrayList<Local>();
SQLiteDatabase sqliteDB = dbHelper.getReadableDatabase();
ArrayList<Integer> listlocalsId = new ArrayList<Integer>();
// listlocalsId = new DBUserLocalAdapter(context)
// .getListUserLocal(id_user);
//
// for (int i = 0; i < listlocalsId.size(); i++) {
// String s = "SELECT * FROM " + TABLE + " WHERE (" + _ID + "="
// + listlocalsId.get(i) + ")"; // OR
// // (" + ID_LOCALTYPE + "=2);";
//
// Cursor crsr2 = sqliteDB.rawQuery(s, null);
// crsr2.moveToFirst();
// for (int j = 0; j < crsr2.getCount(); j++) {
// locals.add(new Local(crsr2.getDouble(1), crsr2.getDouble(2),
// crsr2.getString(3)));
// crsr2.moveToNext();
// }
// }
if (listlocalsId.size() == 0) {
String s = "SELECT * FROM " + TABLE;
// + " WHERE " + ID_LOCALTYPE + "=2;";
Cursor crsr2 = sqliteDB.rawQuery(s, null);
crsr2.moveToFirst();
for (int j = 0; j < crsr2.getCount(); j++) {
locals.add(new Local(crsr2.getDouble(1), crsr2.getDouble(2), crsr2.getString(3)));
crsr2.moveToNext();
}
}
return locals;
}
// �鿴����
public Cursor select(int id) {
SQLiteDatabase db = helper.getReadableDatabase();
String selection = ID + " = ?";
String[] selectionArgs = {String.valueOf(id)};
Cursor cursor = db.query(TABLE_NAME, null, selection, selectionArgs, null, null, null);
return cursor;
}
public Student getStudentById(int Id) {
SQLiteDatabase db = dbHelper.getReadableDatabase();
String selectQuery =
"SELECT "
+ Student.KEY_ID
+ ","
+ Student.KEY_name
+ ","
+ " FROM "
+ Student.TABLE
+ " WHERE "
+ Student.KEY_ID
+ "=?"; // It's a good practice to use parameter ?, instead of concatenate string
int iCount = 0;
Student student = new Student();
Cursor cursor = db.rawQuery(selectQuery, new String[] {String.valueOf(Id)});
if (cursor.moveToFirst()) {
do {
student.student_ID = cursor.getInt(cursor.getColumnIndex(Student.KEY_ID));
student.name = cursor.getString(cursor.getColumnIndex(Student.KEY_name));
} while (cursor.moveToNext());
}
cursor.close();
db.endTransaction();
db.close();
return student;
}
/**
* 查找某一栏目下的所有博客,这里只会存上第一页的博客，因为在加载的时候，并没有存库
*
* @param blogType
* @return
*/
public synchronized List<BlogItem> loadBlog(int blogType) {
List<BlogItem> blogs = new ArrayList<>();
BlogItem item;
db = helper.getReadableDatabase();
Cursor cursor =
db.query(
DBInfo.Table.BLOG_TABLE_NAME,
null,
BlogItem.BLOGTYPE + "= ?",
new String[] {blogType + ""},
null,
null,
null);
while (cursor.moveToNext()) { // 第一步就直接转向第一条记录，为空则退出
item = new BlogItem();
item.setTitle(cursor.getString(cursor.getColumnIndex(BlogItem.TITLE)));
item.setContent(cursor.getString(cursor.getColumnIndex(BlogItem.CONTENT)));
item.setDate(cursor.getString(cursor.getColumnIndex(BlogItem.DATE)));
item.setImgLink(cursor.getString(cursor.getColumnIndex(BlogItem.IMG)));
item.setLink(cursor.getString(cursor.getColumnIndex(BlogItem.LINK)));
item.setBlogType(blogType);
blogs.add(item);
}
cursor.close();
db.close();
return blogs;
}
public User getUserForToken(String token) {
DBHelper dbHelper = new DBHelper(context);
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor =
db.query(
DBConfig.TABLE_USER,
null,
DBConfig.KEY_TOKEN + "=?",
new String[] {String.valueOf(token)},
null,
null,
null,
null);
if (cursor != null) {
cursor.moveToFirst();
}
User item = new User();
if (cursor.getCount() == 0) {
db.close();
cursor.close();
return item;
}
item.setIdMaster(cursor.getString(0));
item.setLogin(cursor.getString(1));
item.setToken(cursor.getString(3));
item.setName(cursor.getString(4));
item.setBalance(cursor.getString(5));
item.setOnLine(cursor.getInt(6));
item.setTextInfo(cursor.getString(8));
cursor.close();
db.close();
return item;
}
public ArrayList<HashMap<String, String>> getStudentList() {
// Open connection to read only
SQLiteDatabase db = dbHelper.getReadableDatabase();
String selectQuery =
"SELECT " + Student.KEY_ID + "," + Student.KEY_name + " FROM " + Student.TABLE;
// Student student = new Student();
ArrayList<HashMap<String, String>> studentList = new ArrayList<HashMap<String, String>>();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
HashMap<String, String> student = new HashMap<String, String>();
student.put("id", cursor.getString(cursor.getColumnIndex(Student.KEY_ID)));
student.put("name", cursor.getString(cursor.getColumnIndex(Student.KEY_name)));
studentList.add(student);
} while (cursor.moveToNext());
}
cursor.close();
db.endTransaction();
db.close();
return studentList;
}
private ArrayList<Match> getMatchListFromDataBase() {
SQLiteDatabase mDB = mDBHelper.getReadableDatabase();
Cursor mCursor = mDB.query(mDBHelper.TABLE_NAME_MATCH_LIST, null, null, null, null, null, null);
ArrayList<Match> matchList = new ArrayList<>();
for (int i = 0; i < mCursor.getCount(); i++) {
mCursor.moveToPosition(i);
int[] blueTeams = {
mCursor.getInt(
mCursor.getColumnIndex(DatabaseContract.MatchListEntry.COLUMN_NAME_BLUE_ONE)),
mCursor.getInt(
mCursor.getColumnIndex(DatabaseContract.MatchListEntry.COLUMN_NAME_BLUE_TWO)),
mCursor.getInt(
mCursor.getColumnIndex(DatabaseContract.MatchListEntry.COLUMN_NAME_BLUE_THREE))
};
int[] redTeams = {
mCursor.getInt(mCursor.getColumnIndex(DatabaseContract.MatchListEntry.COLUMN_NAME_RED_ONE)),
mCursor.getInt(mCursor.getColumnIndex(DatabaseContract.MatchListEntry.COLUMN_NAME_RED_TWO)),
mCursor.getInt(
mCursor.getColumnIndex(DatabaseContract.MatchListEntry.COLUMN_NAME_RED_THREE))
};
int matchId =
mCursor.getInt(
mCursor.getColumnIndex(DatabaseContract.MatchListEntry.COLUMN_NAME_MATCH_NUMBER));
Match match = new Match(matchId, blueTeams, redTeams);
matchList.add(match);
}
mCursor.close();
return matchList;
}
private void displayListView() {
mDataBaseHelper = new DBHelper(this);
db = mDataBaseHelper.getReadableDatabase();
String selection = mDataBaseHelper.text_question + " LIKE '%?%'";
Cursor cursor =
db.query(mDataBaseHelper.table_name_tree, null, selection, null, null, null, null);
ArrayList<Question> questionList = new ArrayList<Question>();
while (cursor.moveToNext()) {
String name = cursor.getString(cursor.getColumnIndex(mDataBaseHelper.text_question));
Question q = new Question(name, false);
questionList.add(q);
}
cursor.close();
// create an ArrayAdaptar from the String Array
dataAdapter_q = new MyCustomAdapter(this, R.layout.list_with_checkbox, questionList);
ListView listView = (ListView) findViewById(R.id.listView1);
// Assign adapter to ListView
listView.setAdapter(dataAdapter_q);
listView.setOnItemClickListener(
new AdapterView.OnItemClickListener() {
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
// When clicked, show a toast with the TextView text
Question country = (Question) parent.getItemAtPosition(position);
/*Toast.makeText(getApplicationContext(),
"Clicked on Row: " + country.getName(),
Toast.LENGTH_LONG).show();*/
}
});
}
public ScoreBoard getPersonalScoreBoard(String Name) {
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor =
db.query(
SCOREBOARD_TABLE,
new String[] {
SCOREBOARD_ID,
SCOREBOARD_GROUPNAME,
SCOREBOARD_CLASSNAME,
SCOREBOARD_SCORE,
SCOREBOARD_TIME
},
SCOREBOARD_GROUPNAME + "=?",
new String[] {String.valueOf(Name)},
null,
null,
null,
null);
if (cursor != null) cursor.moveToFirst();
ScoreBoard scoreBoard =
new ScoreBoard(
Integer.parseInt(cursor.getString(0)),
cursor.getString(1),
cursor.getString(2),
Integer.parseInt(cursor.getString(3)),
cursor.getString(4));
return scoreBoard;
}
public Questions getQuestion(int id) {
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor =
db.query(
QUESTIONS_TABLE,
new String[] {
QUESTIONS_ID,
QUESTIONS_NAME,
QUESTIONS_QUESTION,
QUESTIONS_ANSWERS,
QUESTIONS_RIGHTANSWER,
QUESTIONS_FINISHED
},
QUESTIONS_ID + "=?",
new String[] {String.valueOf(id)},
null,
null,
null,
null);
if (cursor != null) cursor.moveToFirst();
Questions question =
new Questions(
Integer.parseInt(cursor.getString(0)),
cursor.getString(1),
cursor.getString(2),
cursor.getString(3),
cursor.getString(4),
cursor.getString(5));
return question;
}
public String select(String parentId) {
SQLiteDatabase db = helper.getReadableDatabase();
String selection = TITLE + " = ?";
String[] selectionArgs = {String.valueOf(parentId)};
Cursor cursor = db.query(TABLE_NAME, null, selection, selectionArgs, null, null, null);
return cursor.getString(0);
}
@Override
public boolean openReadOnlyDB() {
try {
database = helper.getReadableDatabase();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
public User selectInfo(int id) {
User u = new User();
SQLiteDatabase db = helper.getReadableDatabase();
Cursor c = db.rawQuery("select * from user where id=?", new String[] {id + ""});
if (c.moveToFirst()) {
u.setImg(c.getInt(c.getColumnIndex("img")));
u.setName(c.getString(c.getColumnIndex("name")));
}
return u;
}
public int updateServerID(Local local, String server_id) {
SQLiteDatabase sqliteDB = dbHelper.getReadableDatabase();
ContentValues values = new ContentValues();
values.put(SERVER_ID, server_id);
// updating row
return sqliteDB.update(
TABLE, values, _ID + " = ?", new String[] {String.valueOf(local.getId())});
}
@Override
public boolean isExists(String url, int thread_id) {
SQLiteDatabase db = mHelper.getReadableDatabase();
Cursor cursor =
db.rawQuery(
"select * from thread_info where url = ? and thread_id = ?",
new String[] {url, thread_id + ""});
boolean exists = cursor.moveToNext();
cursor.close();
db.close();
return exists;
}
/**
* Open database for writing
*
* @return true if db is open and writable, false otherwise
* @exception ex caught SQLiteException if failure to open writable database, will open readable
* if fails
*/
public boolean open() {
try {
db = dbhelper.getWritableDatabase();
} catch (SQLiteException ex) {
if (LOG_ON) Log.e("Open database exception caught", ex.getMessage());
db = dbhelper.getReadableDatabase();
return false;
}
// enforce referential integrity
db.execSQL("PRAGMA foreign_keys=ON;");
return true;
}
public String select4(String name) {
SQLiteDatabase db = helper.getReadableDatabase();
String b = "";
String ID = "";
Cursor cursor = db.rawQuery("select * from knowledges", null);
while (cursor.moveToNext()) {
b = cursor.getString(2);
if (b.equals(name)) {
ID = cursor.getString(0); // ��ȡ��yi�е�ֵ
}
}
return ID;
}
public int getLocalID(String local_server_id) {
SQLiteDatabase sqliteDB = dbHelper.getReadableDatabase();
String s = "SELECT * FROM " + TABLE + " WHERE " + SERVER_ID + "='" + local_server_id + "';";
Cursor crsr = sqliteDB.rawQuery(s, null);
crsr.moveToFirst();
int id = crsr.getInt(0);
crsr.close();
return id;
}
public Local getLocal(Event event) {
SQLiteDatabase sqliteDB = dbHelper.getReadableDatabase();
String s = "SELECT * FROM " + TABLE + " WHERE " + _ID + "=" + event.getId_local();
Cursor crsr = sqliteDB.rawQuery(s, null);
crsr.moveToFirst();
Local local = new Local(crsr.getDouble(1), crsr.getDouble(2), crsr.getString(3));
local.setId(crsr.getInt(0));
crsr.close();
return local;
}
public String[] select3() {
SQLiteDatabase db = helper.getReadableDatabase();
String[] a = new String[6];
String b = "";
int i = 0;
Cursor cursor = db.rawQuery("select * from knowledges", null);
while (cursor.moveToNext()) {
b = cursor.getString(1);
if (b.equals("5")) {
a[i] = cursor.getString(2);
i++;
}
}
return a;
}
public String getLocalServerID(String name) {
SQLiteDatabase sqliteDB = dbHelper.getReadableDatabase();
String s = "SELECT * FROM " + TABLE + " WHERE " + NAME + "='" + name + "';";
Cursor crsr2 = sqliteDB.rawQuery(s, null);
if (crsr2.getCount() > 0) {
crsr2.moveToFirst();
String server_id = crsr2.getString(4);
crsr2.close();
return server_id;
}
return "";
}
public boolean isExists(String filename) {
Cursor c =
dbHelper
.getReadableDatabase()
.query(
TABLE_NAME,
null,
COL_FILENAME + " = ?",
new String[] {filename},
null,
null,
null);
boolean result = false;
if (c != null && c.moveToFirst()) result = true;
if (c != null) c.close();
return result;
}
public int GetLocalDataCount() {
// String databaseFilename = DATABASE_PATH + "/" + DATABASE_FILENAME;
// SQLiteDatabase sdb=SQLiteDatabase.openOrCreateDatabase(databaseFilename, null);
int LocalDataCount = 0;
SQLiteDatabase sdb = dbHelper.getReadableDatabase();
String sql = "select count(*) as count from DataDictionary";
Cursor cursor = sdb.rawQuery(sql, null);
while (cursor.moveToNext()) {
LocalDataCount = Integer.parseInt(cursor.getString(cursor.getColumnIndex("count")));
}
cursor.close();
return LocalDataCount;
// Looper.prepare();
// String strdata=String.format("个数:%1$d", iRowCount);
// Toast.makeText(getApplicationContext(), strdata, Toast.LENGTH_LONG).show();
// Looper.loop();
}
@Override
protected void onCreate(Bundle savedInstanceState) {
requestWindowFeature(Window.FEATURE_NO_TITLE);
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_lexicon);
text = (TextView) findViewById(R.id.lexicon_text);
DBHelper dbHelper = new DBHelper(Lexicon.this, "dic.db", null, 1);
final SQLiteDatabase db = dbHelper.getReadableDatabase();
final Cursor cursor =
db.query("dicInfo", new String[] {"word,explain"}, null, null, null, null, null);
if (cursor.moveToFirst()) {
cursor.move(i);
String word = cursor.getString(0);
String explain = cursor.getString(1);
String result = word + "\n" + explain;
text.setText(result);
} else {
}
mLastButton = (Button) findViewById(R.id.last_button);
mLastButton.setOnClickListener(
new View.OnClickListener() {
@Override
public void onClick(View v) {
query("last", cursor);
}
});
mNextButton = (Button) findViewById(R.id.next_button);
mNextButton.setOnClickListener(
new View.OnClickListener() {
@Override
public void onClick(View v) {
query("next", cursor);
}
});
RelativeLayout lexion = (RelativeLayout) findViewById(R.id.lexicon);
lexion.setOnTouchListener(this);
}
// creates a local copy of db file from the working path in assets
private final boolean setupLocalDB(String dbFileName) {
// private class for help creating database file in setupLocalDB()
class DBHelper extends SQLiteOpenHelper {
public DBHelper(Context context, String name) {
super(context, name, null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}
};
try {
// Open then close a SQLite database to prepare for copying
DBHelper dbHelper = new DBHelper(assetsContext, dbFileName);
SQLiteDatabase db = dbHelper.getReadableDatabase();
localDbPath = db.getPath();
db.close();
dbHelper.close();
// Open the empty db as the output stream
OutputStream localDb = new FileOutputStream(localDbPath);
// Open your local db as the input stream
InputStream dbAsset = assetsContext.getAssets().open(workingPath + dbFileName);
// transfer bytes from dbAsset to localDb
byte[] buffer = new byte[1024];
int length;
while ((length = dbAsset.read(buffer)) > 0) localDb.write(buffer, 0, length);
// Close the streams
dbAsset.close();
localDb.flush();
localDb.close();
} catch (Exception e) {
e.printStackTrace();
localDbPath = null; // reset to null if failed
return false;
}
return true;
}
public Local getLocalByName(String name) {
SQLiteDatabase sqliteDB = dbHelper.getReadableDatabase();
String s = "SELECT * FROM " + TABLE + " WHERE " + NAME + "='" + name + "'";
Cursor crsr2 = sqliteDB.rawQuery(s, null);
if (crsr2.getCount() > 0) {
crsr2.moveToFirst();
Local local = new Local(crsr2.getDouble(1), crsr2.getDouble(2), crsr2.getString(3));
local.setId(crsr2.getInt(0));
crsr2.close();
return local;
}
return null;
}
private int find_id_by_name_instruction(String name) {
int id_instruction = 0;
mDataBaseHelper = new DBHelper(this);
db = mDataBaseHelper.getReadableDatabase();
String selection = "NAME = '" + name + "'";
Cursor c =
db.query(mDataBaseHelper.table_name_instruction, null, selection, null, null, null, null);
if (c != null) {
if (c.moveToFirst()) {
do {
String idDB = c.getString(c.getColumnIndex(mDataBaseHelper.UID));
id_instruction = Integer.valueOf(idDB);
} while (c.moveToNext());
}
c.close();
}
return id_instruction;
}
