SQLite数据库常用操作
- 创建一个DBHleper继承SQLiteOpenHelper
- 在构造方法里传入数据库名称,和版本名称
private DBHelper(Context context) {
super(context, DB_NAME, null, VERSION);
} - 重写父类方法onCrate()创建表
public void onCreate(SQLiteDatabase db) {
db.execSQL(SQL_CREATE);
} - 重写父类方法Update方法更新表
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
db.execSQL(SQL_DROP);
db.execSQL(SQL_CREATE);
}
数据库增删改查
public class ThreadDaoImpl implements ThreadDao {
private DBHelper dbHelper = null;
public ThreadDaoImpl(Context context) {
dbHelper = DBHelper.getInstance(context);
}
@Override
public synchronized void insertThread(ThreadInfo threadInfo) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.execSQL("insert into thread_info(thread_id,url,icon,name,start,end,finished,file_length) values(?,?,?,?,?,?,?,?)",
new Object[]{threadInfo.getId(), threadInfo.getUrl(),threadInfo.getIcon(),threadInfo.getName(), threadInfo.getStart(), threadInfo.getEnd(), threadInfo.getFinished(),threadInfo.getLength()});
db.close();
}
@Override
public synchronized void deleteThread(String url) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.execSQL("delete from thread_info where url=?",
new Object[]{url});
db.close();
}
@Override
public synchronized void updateThread(String url, int thread_id, long finished) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.execSQL("update thread_info set finished =? where url=? and thread_id=?",
new Object[]{finished, url, thread_id});
// db.close();
}
@Override
public synchronized List<ThreadInfo> getThreads(String url) {
SQLiteDatabase db = dbHelper.getReadableDatabase();
List<ThreadInfo> threadInfos = new ArrayList<>();
Cursor cursor = db.rawQuery("select * from thread_info where url=?", new String[]{url});
while (cursor.moveToNext()){
ThreadInfo threadInfo = new ThreadInfo();
threadInfo.setId(cursor.getInt(cursor.getColumnIndex("thread_id")));
threadInfo.setUrl(cursor.getString(cursor.getColumnIndex("url")));
threadInfo.setStart(cursor.getInt(cursor.getColumnIndex("start")));
threadInfo.setEnd(cursor.getInt(cursor.getColumnIndex("end")));
threadInfo.setFinished(cursor.getInt(cursor.getColumnIndex("finished")));
threadInfo.setLength(cursor.getInt(cursor.getColumnIndex("file_length")));
threadInfos.add(threadInfo);
}
cursor.close();
db.close();
return threadInfos;
}
@Override
public List<ThreadInfo> getAllThreads() {
SQLiteDatabase db = dbHelper.getReadableDatabase();
List<ThreadInfo> threadInfos = new ArrayList<>();
Cursor cursor = db.rawQuery("select * from thread_info where thread_id=?", new String[]{"0"});
while (cursor.moveToNext()){
ThreadInfo threadInfo = new ThreadInfo();
threadInfo.setId(cursor.getInt(cursor.getColumnIndex("thread_id")));
threadInfo.setUrl(cursor.getString(cursor.getColumnIndex("url")));
threadInfo.setIcon(cursor.getString(cursor.getColumnIndex("icon")));
threadInfo.setName(cursor.getString(cursor.getColumnIndex("name")));
threadInfo.setStart(cursor.getInt(cursor.getColumnIndex("start")));
threadInfo.setEnd(cursor.getInt(cursor.getColumnIndex("end")));
threadInfo.setFinished(cursor.getInt(cursor.getColumnIndex("finished")));
threadInfo.setLength(cursor.getInt(cursor.getColumnIndex("file_length")));
threadInfos.add(threadInfo);
}
cursor.close();
db.close();
return threadInfos;
}
@Override
public synchronized boolean isExists(String url, int thread_id) {
SQLiteDatabase db = dbHelper.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;
}
}