sqlite 入门

1.将数据库文件存放在attets下的schema中

 

2.写一个DBHelper类继承SQLiteOpenHelper

 

继承该类会重写

public void onCreate(SQLiteDatabase db);

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion);

 

在onCreate方法中,一般会写一个executeSchema(db, "schema.sql");

 

 

private void executeSchema(SQLiteDatabase db, String schemaName)
	{
		BufferedReader in = null;
		try {
			
			in = new BufferedReader(new InputStreamReader(mContext.getAssets()
								.open(Configuration.DBSchemaRoot+"/"+schemaName)));
			String line;
			String  buffer = "";
			while ((line = in.readLine()) != null) {
				buffer += line;
				if (line.trim().endsWith(";")) {
					db.execSQL(buffer.replace(";", ""));
					buffer = "";
				}
			}
		} catch (IOException e) {
			
		} finally {
			try {
				if (in != null)
					in.close(); 
			} catch (IOException e) {}
		}
	}

这个方法重要用于读取数据库文件里的内容

 

onUpgrade:

 

@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if(newVersion <= oldVersion)
		{
			return ;
		}
		
		int changeCnt = newVersion - oldVersion;
		for(int i = 0; i < changeCnt; i++)
		{
			String schemaName = "update"+(oldVersion+i)+"_"+(oldVersion+i+1)+".sql";
			executeSchema(db, schemaName);
		}
		
	}

3 写一个AbstractDao类

public class AbstractDao{

	private Context mContext;
	private SQLiteDatabase db;
	private static final int DATABASE_VERSION = 6;
	
	public static boolean mTransaction = false;
	
	public AbstractDao(Context context) {
		this.mContext = context;
		db = new DBHelper(mContext, Configuration.DatabaseFile, null, 
				DATABASE_VERSION).getWritableDatabase();
	}
	
	protected void execute(String sql) throws SQLException {
		db.execSQL(sql);
	}
	
	protected int delete(String table, String whereClause, String[] whereArgs) {
		int result = db.delete(table, whereClause, whereArgs);
		return result;
	}
	
	protected long insert(String table, String nullColumnHack, ContentValues values) throws SQLException {
		long result = db.insertOrThrow(table, nullColumnHack, values);
		return result;
	}
	
	protected int update(String table, ContentValues values, String whereClause, String[] whereArgs) {
		int result = db.update(table, values, whereClause, whereArgs);
		return result;
	}
	
	protected Cursor query(String sql, String[] selectionArgs) {
		return db.rawQuery(sql, selectionArgs);
	}
	
	protected Cursor query(String table, String[] columns, String selection, String[] selectionArgs)
	{
		return db.query(table, columns, selection, selectionArgs, null, null, null);
	}
	
	protected int getScalarValue(String sql, String[] selectionArgs) {
		int ret = 0;
		Cursor c = query(sql, selectionArgs);
		
		if(c != null)
		{
			if (c.getCount() > 0) 
			{
				c.moveToFirst();
				ret = c.getInt(0);
			}
			
			c.close();
		}
		return ret;
	}
	
	/**
	 * This database to determine whether there
	 * @param tableName
	 * @return
	 */
	public int isCreateTable(String tableName)
	{
		int count = 0;
		String sql = "Select Count(*) From sqlite_master Where type='table' And name='"+tableName+"'";
		count = this.getScalarValue(sql,null);
		return count;
	}
	

	
	
	protected void beginTransaction() {
		db.beginTransaction();
	}
	
	protected void commit() {
		db.setTransactionSuccessful();
		db.endTransaction();
	}
	
	protected void rollback() {
		db.endTransaction();
	}
	
	public void dispose() {
		synchronized(db)
		{
			if (db != null && db.isOpen())
				db.close();
		}
	}

}

4其他的类继承 AbstractDao类.

 

 

注意:在调用完dao方法后 一定要记得dispose();