sqlite数据库总结

     在工作中,有很多时候会遇到一些数据的存储,我们就会考虑使用sqlite把数据存储起来,这样就很方便我们对数据进行操作.

    首先我们先写个DBOpenHelper类继承SQLiteOpenHelper类,在onCreate方法中创建表.代码如下:

import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class DBOpenHelper extends SQLiteOpenHelper {/** * 构造函数 * @param context * piano.db 数据库名 * 1 版本号 */public DBOpenHelper(Context context) {super(context, "piano.db", null, 1);}@Overridepublic void onCreate(SQLiteDatabase db) {//创建第一张表createRecordDatabase(db);//创建第二张表createRecordEventDatabase(db);}//第二张表private void createRecordEventDatabase(SQLiteDatabase db) {String sql = "create table if not exists recode_event_tbl ("+ "[_id] integer primary key autoincrement, "+ "[r_id] integer not null, "+ "[type] integer not null, "+ "[position] integer not null, "+ "[interval] integer not null"+ ")";db.execSQL(sql);}//第一张表private void createRecordDatabase(SQLiteDatabase db) {String sql = "create table if not exists recode_tbl ("+ "[_id] integer primary key autoincrement, "+ "[name] text unique not null, "+ "[duration] integer default 0"+ ")";db.execSQL(sql);}//数据库版本升级调用@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}}

现在数据库表我们已经创建好了,接下了我们写个类来管理数据库.代码如下:

import java.util.ArrayList;import java.util.List;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log;import com.example.piano_app.util.Record;import com.example.piano_app.util.RecordEvent;public class DBManager {private static DBManager instance;private DBOpenHelper mHelper;//单例模式public static DBManager getInstance() {if (instance == null) {instance = new DBManager();}return instance;}private DBManager() {}public void init(Context context) {mHelper = new DBOpenHelper(context);}//查找recode_tbl 表中所有的数据public List<Record> queryAllRecode() {List<Record> list = new ArrayList<Record>();SQLiteDatabase db = mHelper.getReadableDatabase();Cursor c = null;try {c = db.rawQuery("select * from recode_tbl", null);while (c.moveToNext()) {Record r = new Record();r.setId(c.getInt(0));r.setName(c.getString(1));r.setDuration(c.getLong(2));list.add(r);}} catch (Exception e) {e.printStackTrace();} finally {close(c, db);}return list;}//删除recode_tbl,recode_event_tbl where表中的一条数据public void deleteRecode(int id) {SQLiteDatabase db = mHelper.getWritableDatabase();try {String sid = String.valueOf(id);db.execSQL("delete from recode_tbl where [_id] = ?",new String[] { sid });db.execSQL("delete from recode_event_tbl where [r_id] = ?",new String[] { sid });} catch (Exception e) {e.printStackTrace();} finally {close(null, db);}}//更改对应id中的一个字段public void updateRecode(int id, String name) {SQLiteDatabase db = mHelper.getWritableDatabase();try {String sid = String.valueOf(id);ContentValues values = new ContentValues();values.put("name", name);db.update("recode_tbl", values, "[_id]=?", new String[] { sid });} catch (Exception e) {e.printStackTrace();} finally {close(null, db);}}//判断是否同名public boolean isExists(String name) {SQLiteDatabase db = mHelper.getReadableDatabase();Cursor c = null;try {c = db.rawQuery("select [_id] from recode_tbl where [name] = ?",new String[] { name });return c.getCount() > 0;} catch (Exception e) {e.printStackTrace();} finally {close(c, db);}return false;}//查找对应id的数据public List<RecordEvent> queryRecodeEventById(int id) {List<RecordEvent> list = new ArrayList<RecordEvent>();SQLiteDatabase db = mHelper.getReadableDatabase();Cursor c = null;try {c = db.rawQuery("select * from recode_event_tbl where [r_id] = ?",new String[] { String.valueOf(id) });while (c.moveToNext()) {RecordEvent e = new RecordEvent();e.setId(c.getInt(0));e.setType(c.getInt(2));e.setPosition(c.getInt(3));e.setIntervalTime(c.getLong(4));list.add(e);}} catch (Exception e) {e.printStackTrace();} finally {close(c, db);}Log.e("DBmanager", "id:"+id +"  "+list.toString());return list;}//插入数据public boolean insert(String name, List<RecordEvent> list) {int id = insertRecord(name, list);if (id < 0) {return false;}return insertEvents(id, list);}//插入数据到recode_event_tbl表中private boolean insertEvents(int id, List<RecordEvent> list) {Log.e("DBManager", "id:"+id+"  insertEvents:"+list.size());SQLiteDatabase db = mHelper.getWritableDatabase();try {db.beginTransaction();String sid = String.valueOf(id);for (RecordEvent event : list) {db.execSQL("insert into recode_event_tbl ([r_id], [type], [position], [interval]) values (?, ?, ?, ?)",new String[] { sid, String.valueOf(event.getType()),String.valueOf(event.getPosition()),String.valueOf(event.getIntervalTime()) });}db.setTransactionSuccessful();return true;} catch (Exception e) {e.printStackTrace();return false;} finally {close(null, db);}}//插入数据到recode_tbl表中private int insertRecord(String name, List<RecordEvent> list) {SQLiteDatabase db = mHelper.getWritableDatabase();Cursor c = null;try {ContentValues values = new ContentValues();values.put("name", name);values.put("duration", getDuration(list));if (db.insert("recode_tbl", null, values) < 0) {return -1;}c = db.rawQuery("select [_id] from recode_tbl where [name] = ?",new String[] { name });if (!c.moveToNext()) {return -1;}return c.getInt(0);} catch (Exception e) {e.printStackTrace();return -1;} finally {close(c, db);}}private long getDuration(List<RecordEvent> list) {long duration = 0;for (int i = 0; i < list.size(); i++) {duration += list.get(i).getIntervalTime();}return duration;}//关闭游标,关闭数据库private void close(Cursor c, SQLiteDatabase db) {if (c != null && !c.isClosed()) {c.close();}if (db != null) {if (db.inTransaction()) {db.endTransaction();}if (db.isOpen()) {db.close();}}}}