Android中SQLite的意义及用法

1、示例演示SQLite数据库创建、建表、增删改查等操作

1) MainActivity.java

/**
 * 有root权限才能查看data/data/package/下的目录，包括cache,files,lib,shared_prefs,databases等。
 * <p>清数据会将这些全清掉，清缓存只清掉cache。卸载APP也会清掉全部，释放内存相当于杀死进程不会清掉沙盒数据。</p>
 */
public class MainActivity extends Activity{
	private static final String TAG = MainActivity.class.getSimpleName();
	MyDB myDB;
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		Log.d(TAG, "onCreate");
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		
		//将新版本号放进去
		myDB = new MyDB(getApplication(), MyDB.DATABASE_NAME, null, MyDB.DATABASE_VERSION);
		
		SharedPreferences preferences = getPreferences(Context.MODE_PRIVATE);
		preferences.edit().putBoolean("Are you ready?", true).apply();//API level 9
		
		//操作该目录不需读写权限
		try {
			File file = new File(getCacheDir(),"romantic letter.txt");
			file.createNewFile();
			OutputStream os = new FileOutputStream(file);
			os.write("Please trust me, my love!".getBytes());
			os.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {
			OutputStream os = openFileOutput("info.txt", Context.MODE_PRIVATE);
			os.write("Happy birthday to you!".getBytes());
			os.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public void onClick(View v){
		SQLiteDatabase database;
		switch(v.getId()){
		case R.id.btn_insert:
			ContentValues contentValues = new ContentValues();
			contentValues.put(MyDB.KEY_GOLD_HOARD_NAME_COLUMN, "gold mountain");
			contentValues.put(MyDB.KEY_GOLD_HOARDED_COLUMN, 5.2f);
			contentValues.put(MyDB.KEY_GOLD_HOARD_ACCESSIBLE_COLUMN, 1);
			database = myDB.getWritableDatabase();
			database.insert(MyDB.DATABASE_TABLE, null, contentValues);
			database.releaseReference();//equivalent to Closable.close()
			break;
		case R.id.btn_query:
			String[] result_columns = {MyDB.KEY_ID,MyDB.KEY_GOLD_HOARD_NAME_COLUMN,MyDB.KEY_GOLD_HOARDED_COLUMN,
					MyDB.KEY_GOLD_HOARD_ACCESSIBLE_COLUMN};
			String where = MyDB.KEY_GOLD_HOARD_ACCESSIBLE_COLUMN + "=" +1;
			database = myDB.getWritableDatabase();
			Cursor cursor = database.query(MyDB.DATABASE_TABLE, result_columns, where, null, null, null, null, null);
			StringBuilder sb = new StringBuilder();
			while(cursor.moveToNext()){
				sb.append(cursor.getInt(cursor.getColumnIndexOrThrow(MyDB.KEY_ID))).append(',')
					.append(cursor.getString(cursor.getColumnIndexOrThrow(MyDB.KEY_GOLD_HOARD_NAME_COLUMN))).append(',')
					.append(cursor.getFloat(cursor.getColumnIndexOrThrow(MyDB.KEY_GOLD_HOARDED_COLUMN))).append(',')
					.append(cursor.getInt(cursor.getColumnIndexOrThrow(MyDB.KEY_GOLD_HOARD_ACCESSIBLE_COLUMN))).append('\n');
			}
			cursor.close();
			database.releaseReference();
			if(sb.length()>0){
				sb.deleteCharAt(sb.length()-1);
				Toast.makeText(this, sb, Toast.LENGTH_SHORT).show();
			}
			break;
		case R.id.btn_update:
			ContentValues newValues = new ContentValues();
			newValues.put(MyDB.KEY_GOLD_HOARD_NAME_COLUMN, "silver");
			newValues.put(MyDB.KEY_GOLD_HOARDED_COLUMN, 3.1f);
			String whereU = MyDB.KEY_ID + "=" +1;
			database = myDB.getWritableDatabase();
			//更新指定行指定列数据
			database.update(MyDB.DATABASE_TABLE, newValues, whereU, null);
			database.close();
			break;
		case R.id.btn_delete:
			String whereR = MyDB.KEY_ID + "=" + 1;
			database = myDB.getWritableDatabase();
			//删除指定行数据
			database.delete(MyDB.DATABASE_TABLE, whereR, null);
			database.close();
			break;
		case R.id.btn_drop:
			database = myDB.getWritableDatabase();
			//清空表中数据
			database.delete(MyDB.DATABASE_TABLE, null, null);
			database.close();
		}
	}
}

2) activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    xmlns:android="http://schemas.android.com/apk/res/android">
    <Button 
        android:id="@+id/btn_insert"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="insert"
        android:onClick="onClick"/>
    <Button 
        android:id="@+id/btn_query"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="query"
        android:onClick="onClick"/>
    <Button 
        android:id="@+id/btn_update"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="update"
        android:onClick="onClick"/>
    <Button 
        android:id="@+id/btn_delete"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="delete"
        android:onClick="onClick"/>
    <Button 
        android:id="@+id/btn_drop"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="drop"
        android:onClick="onClick"/>
</LinearLayout>

3) MyDB.java

public class MyDB extends SQLiteOpenHelper {
	private static final String TAG = MyDB.class.getSimpleName();

	public static final String KEY_ID = "_id";
	public static final String KEY_GOLD_HOARD_NAME_COLUMN = "GOLD_HOARD_NAME_COLUMN";
	public static final String KEY_GOLD_HOARD_ACCESSIBLE_COLUMN = "GOLD_HOARD_ACCESSIBLE_COLUMN";
	public static final String KEY_GOLD_HOARDED_COLUMN = "GOLD_HOARDED_COLUMN";
	
	public static final String DATABASE_NAME = "me.db";
	public static final String DATABASE_TABLE = "GoldHoards";
	public static final int DATABASE_VERSION = 1;
	private static final String DATABASE_CREATE = "create table " + DATABASE_TABLE + " ("
			+ KEY_ID + " integer primary key autoincrement, "
			+ KEY_GOLD_HOARD_NAME_COLUMN + " text not null, "
			+ KEY_GOLD_HOARDED_COLUMN + " float, "
			+ KEY_GOLD_HOARD_ACCESSIBLE_COLUMN + " integer);";
	
	public MyDB(Context context, String name, CursorFactory factory, int version){
		super(context, name, factory, version);
	}
	
	@Override
	public void onConfigure(SQLiteDatabase db) {
		Log.d(TAG, "onConfigure");
		//API Level 16
//		super.onConfigure(db);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.d(TAG, "onCreate");
		//建表
		db.execSQL(DATABASE_CREATE);
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		Log.d(TAG, "onOpen");
		super.onOpen(db);
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.d(TAG, "onUpgrade");
		//删除旧表重建新表
		db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE);
		onCreate(db);
	}

	@Override
	public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.d(TAG, "onDowngrade");
		//API Level 11
//		super.onDowngrade(db, oldVersion, newVersion);
		db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE);
		onCreate(db);
	}
}