[Android] 資料實體的自動存取–SQLite篇

本例代碼以SQLite為資料存取載體。

在"SharedPreferences篇"中，已知資料的自動儲存原理是使用Java反射的方法擷取資料實體類中的Field進行的儲存的。
當以SQLite為資料存取載體時，需要解決一個問題是：如何標明類中的某個Field是primary key(主鍵)呢。

為解決此問題，此處引入並使用了Java Annotation(內注)。Annotation可以保留一些自訂的注釋資訊並且這些可以在被編譯後仍保留著甚至被JVM運行時擷取。相應文章請查看：[Java] Annotation(內注)執行個體一則

本範例程式碼實現了一個Mark Annotation(標記內注)，用於修飾Field標明其為主鍵。

在對實體類的屬性進行命名時，應避免"order"、"group"等SQL語句中的關鍵字。否則易出現如下異常(屬性名稱出現'order'關鍵字)：

android.database.sqlite.SQLiteException: near "order": syntax error (code 1): , while compiling: CREATE TABLE IF NOT EXISTS EmoPackage (_id INTEGER PRIMARY KEY AUTOINCREMENT,epId INTEGER UNIQUE,name TEXT,order INTEGER,price
INTEGER)

被修飾的主鍵將影響自動產生的sql執行語句。如下代碼：

/** 根據類結構構造表。 */private String getTableBuildingSQL(Class<?> clazz) {StringBuilder strBuilder = new StringBuilder("create table if not exists ");strBuilder.append(clazz.getSimpleName());strBuilder.append("(");// getDeclaredFields():只擷取該類檔案中聲明的欄位// getFields():擷取該類檔案、其父類、介面的聲明欄位Field[] arrField = clazz.getFields();for (int i = arrField.length - 1; i >= 0; i--) {Field f = arrField[i];String type = TYPES.get(f.getType());if (type == null) {continue;} else {strBuilder.append(f.getName() + " " + type);if (f.isAnnotationPresent(primary.class)) {strBuilder.append(" PRIMARY KEY");}if (i > 0) {strBuilder.append(",");}}}strBuilder.append(")");return strBuilder.toString();}

其餘的代碼是普通的SQLite操作及與“SharedPreferences篇”中的處理手法一致。不再冗述。

本文為Sodino所有，轉載請註明出處：http://blog.csdn.net/sodino/article/details/7996088

上代碼：lab.sodino.autosave.annotation.primary

package lab.sodino.autosave.annotation;import java.lang.annotation.Documented;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface primary {}

lab.sodino.autosave.db.DBHelper

package lab.sodino.autosave.db;import java.io.File;import java.lang.reflect.Field;import java.util.HashMap;import java.util.Map;import lab.sodino.autosave.GoodsBean;import lab.sodino.autosave.LogOut;import lab.sodino.autosave.annotation.primary;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class DBHelper extends SQLiteOpenHelper {private SQLiteDatabase sqlDb;public static final int VERSION = 1;public static final Map<Class<?>, String> TYPES;static {TYPES = new HashMap<Class<?>, String>();TYPES.put(byte.class, "BYTE");TYPES.put(boolean.class, "INTEGER");TYPES.put(short.class, "SHORT");TYPES.put(int.class, "INTEGER");TYPES.put(long.class, "LONG");TYPES.put(String.class, "TEXT");TYPES.put(byte[].class, "BLOB");TYPES.put(float.class, "FLOAT"); // REALTYPES.put(double.class, "DOUBLE"); // REAL}public DBHelper(Context context) {super(context, context.getPackageName(), null, VERSION);File dbFile = context.getDatabasePath(context.getPackageName());if (dbFile.exists() == false) {LogOut.out(this, "DBFile does not exist.");// 去調用onCreate()和onUpgrade()建表getWritableDatabase();// initAllDBItem();close();LogOut.out(this, "InitDB finished!!!");} else {LogOut.out(this, "DBFile does exist.");}}public void openDBHelper() {sqlDb = getWritableDatabase();}public void close() {if (sqlDb != null) {sqlDb.close();}super.close();}@Overridepublic void onCreate(SQLiteDatabase db) {String sqlTableBuilding = getTableBuildingSQL(GoodsBean.class);LogOut.out(this, "sql[" + sqlTableBuilding + "]");db.execSQL(sqlTableBuilding);}/** 根據類結構構造表。 */private String getTableBuildingSQL(Class<?> clazz) {StringBuilder strBuilder = new StringBuilder("create table if not exists ");strBuilder.append(clazz.getSimpleName());strBuilder.append("(");// getDeclaredFields():只擷取該類檔案中聲明的欄位// getFields():擷取該類檔案、其父類、介面的聲明欄位Field[] arrField = clazz.getFields();for (int i = arrField.length - 1; i >= 0; i--) {Field f = arrField[i];String type = TYPES.get(f.getType());if (type == null) {continue;} else {strBuilder.append(f.getName() + " " + type);if (f.isAnnotationPresent(primary.class)) {strBuilder.append(" PRIMARY KEY");}if (i > 0) {strBuilder.append(",");}}}strBuilder.append(")");return strBuilder.toString();}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {LogOut.out(this, "onUpgrade");String sql = "drop table if exists" + GoodsBean.class.getSimpleName();db.execSQL(sql);}public int insert(GoodsBean bean) {int row = -1;row = (int) sqlDb.insert(GoodsBean.class.getSimpleName(), null, GoodsBean.translate2ContentValues(bean));LogOut.out(this, "row=" + row);return row;}public GoodsBean query() {Cursor cursor = sqlDb.rawQuery("select * from " + GoodsBean.class.getSimpleName(), null);GoodsBean bean = GoodsBean.cursor2GoodsBean(cursor);cursor.close();return bean;}}

lab.sodino.autosave.ActAutoSave

package lab.sodino.autosave;import lab.sodino.autosave.db.DBHelper;import lab.sodino.autosave_sqlite.R;import android.app.Activity;import android.os.Bundle;import android.view.View;import android.view.View.OnClickListener;import android.widget.Button;import android.widget.TextView;public class ActAutoSave extends Activity implements OnClickListener {private DBHelper dbHelper;private Button btnAction;private GoodsBean goodsBean;private TextView txtDetail, txtRead;@Overridepublic void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.act_auto_save);LogOut.out(this, "onCreate");dbHelper = new DBHelper(this);btnAction = (Button) findViewById(R.id.btnAction);btnAction.setOnClickListener(this);txtDetail = (TextView) findViewById(R.id.txtDetail);txtRead = (TextView) findViewById(R.id.txtRead);goodsBean = GoodsBean.newInstance();txtDetail.setText(goodsBean.toString());}public void onClick(View v) {if (v == btnAction) {doSave(goodsBean);GoodsBean beanRead = doRead();showReadBean(beanRead);}}private void showReadBean(GoodsBean bean) {txtRead.setText(bean.toString());}private GoodsBean doRead() {dbHelper.openDBHelper();GoodsBean bean = dbHelper.query();return bean;}private void doSave(GoodsBean bean) {dbHelper.openDBHelper();dbHelper.insert(bean);dbHelper.close();}}

lab.sodino.autosave.GoodsBean

package lab.sodino.autosave;import java.lang.reflect.Field;import android.content.ContentValues;import android.database.Cursor;import lab.sodino.autosave.annotation.primary;public class GoodsBean {@primary/** 標明了是主鍵*/public long _id;public String name;public int price;public boolean isPaid;/** 測試用。 */public byte testByte;public byte[] arrByte;/** 測試用。 */public short testShort;public float cicle;public double testDouble;public String toString() {StringBuffer strBuffer = new StringBuffer();strBuffer.append("_id[" + _id + "]\n");strBuffer.append("name[" + name + "]\n");strBuffer.append("price[" + price + "]\n");strBuffer.append("isPaid[" + isPaid + "]\n");strBuffer.append("cicle[" + cicle + "]\n");strBuffer.append("testByte[" + testByte + "]\n");strBuffer.append("arrByte.len[" + (arrByte == null ? "N/A" : arrByte.length) + "]\n");strBuffer.append("testShort[" + testShort + "]\n");strBuffer.append("testDouble[" + testDouble + "]\n");return strBuffer.toString();}public static GoodsBean newInstance() {GoodsBean bean = new GoodsBean();bean._id = 128l;bean.name = "AutoSave";bean.price = 1024;bean.isPaid = true;bean.cicle = 2.356f;bean.arrByte = new String("SodinoArrBytes").getBytes();bean.testByte = 8;bean.testShort = 128;bean.testDouble = 9856.2145d;return bean;}public static ContentValues translate2ContentValues(GoodsBean bean) {ContentValues cv = new ContentValues();Field[] arrField = GoodsBean.class.getFields();try {for (Field f : arrField) {if (f.isAccessible() == false) {f.setAccessible(true);}String name = f.getName();Object value = f.get(bean);LogOut.out(GoodsBean.class.getName(), "name:" + name + " " + String.valueOf(value));if (value instanceof Byte) {cv.put(name, (Byte) value);} else if (value instanceof Short) {cv.put(name, (Short) value);} else if (value instanceof Integer) {cv.put(name, (Integer) value);} else if (value instanceof Long) {cv.put(name, (Long) value);} else if (value instanceof String) {cv.put(name, (String) value);} else if (value instanceof byte[]) {cv.put(name, (byte[]) value);} else if (value instanceof Boolean) {cv.put(name, (Boolean) value);} else if (value instanceof Float) {cv.put(name, (Float) value);} else if (value instanceof Double) {cv.put(name, (Double) value);}}} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}return cv;}public static GoodsBean cursor2GoodsBean(Cursor cursor) {GoodsBean bean = new GoodsBean();if (cursor.isBeforeFirst()) {cursor.moveToFirst();}Field[] arrField = GoodsBean.class.getFields();try {for (Field f : arrField) {String columnName = f.getName();int columnIdx = cursor.getColumnIndex(columnName);if (columnIdx != -1) {if (f.isAccessible()) {f.setAccessible(true);}Class<?> type = f.getType();if (type == byte.class) {f.set(bean, (byte) cursor.getShort(columnIdx));} else if (type == short.class) {f.set(bean, cursor.getShort(columnIdx));} else if (type == int.class) {f.set(bean, cursor.getInt(columnIdx));} else if (type == long.class) {f.set(bean, cursor.getLong(columnIdx));} else if (type == String.class) {f.set(bean, cursor.getString(columnIdx));} else if (type == byte[].class) {f.set(bean, cursor.getBlob(columnIdx));} else if (type == boolean.class) {f.set(bean, cursor.getInt(columnIdx) == 1);} else if (type == float.class) {f.set(bean, cursor.getFloat(columnIdx));} else if (type == double.class) {f.set(bean, cursor.getDouble(columnIdx));}}}} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}return bean;}}