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);
	}
	
	@Override
	public 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);
	}
	//数据库版本升级调用
	@Override
	public 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();
			}
		}
	}
}