[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)
@Documented
public @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"); // REAL
		TYPES.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();
	}

	@Override
	public 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();
	}

	@Override
	public 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;

	@Override
	public 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;
	}
}