Android数据库sqlite封装心得

怎样稍微对Android的SQLiteDatabase进行相应的封装呢？

SQLite是Android里面轻量实现的数据库， Android提供了SQLiteDatabase类用来创建和访问数据库对象。

 

当要打开和创建数据库对象时，会执行SQLiteDatabase .openOrCreateDatabase()方法；

当要执行sql语句的时候,我们会执行SQLiteDatabase .execSQL() 方法。

android里面也提供了SQLiteOpenHelper类，顾名思义，这个类是SQLiteDatabase的一个辅助类。通过这个类不仅可以生成数据库，并对数据库的版本进行管理。

当在程序当中调用SQLiteOpenHelper的getWritableDatabase()或者getReadableDatabase()方法的时候，如果当前还没有数据，Android系统就会自动生成一个数据库。

 

 

package com.example.faithhopelove;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.faithhopelove.db.Group;

/**
 * 数据库访问辅助类
 */
public class DBHelper {

	private static DBHelper dbhelper = null;
	
	private DatabaseHelper databasehelper = null;
	
	private DBHelper(Context context){
		this.databasehelper = new DatabaseHelper(context );
	}
	 
	
	public synchronized static DBHelper getInstance(Context context){   //单例模型
	      if(dbhelper == null){
	    	  dbhelper = new DBHelper(context);
	      }	 return dbhelper;
	}
	
	public SQLiteDatabase getWritableDB(){
		return databasehelper.getWritableDatabase();
	}
	
	
	public SQLiteDatabase getReadableDB(){
		return databasehelper.getReadableDatabase();
	}
	
	public void closeDB(){
		databasehelper.close();
	}
	
	private class DatabaseHelper extends SQLiteOpenHelper{
		
		private static final String DATABASE_NAME = "hzbg.db";  //数据库名称
		private static final int DB_VERSION = 10;
		
		public DatabaseHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}
		
		public DatabaseHelper(Context context){
			this(context, DATABASE_NAME, null, DB_VERSION);
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
//			Log.d("TAG","onCreate");
			db.execSQL(PersonColumns.CREAT_TABLE(PersonColumns.TABLE_NAME()));
		    db.execSQL(GroupColumns.CREAT_TABLE(GroupColumns.TABLE_NAME()));
		    initGroup(db, GroupColumns.TABLE_NAME());
		    initPerosn(db,PersonColumns.TABLE_NAME());
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//			Log.d("TAG","onUpgrade");
			db.execSQL(PersonColumns.CREAT_TABLE(PersonColumns.TABLE_NAME()));
		    db.execSQL(GroupColumns.CREAT_TABLE(GroupColumns.TABLE_NAME()));
		}
		
	}

 
	public void initGroup(SQLiteDatabase db,String tableName) {
		String[] names = { Group.GROUP1, Group.GROUP2, Group.GROUP3 };
		int len = names.length;
		db.beginTransaction();
		try {
			for (int i = 0; i < len; i++) {
				ContentValues values = new ContentValues();
				values.put( GroupColumns.ID, i);
				values.put(GroupColumns.GROUPNAME, names[i]);
 				db.insert(tableName, null, values);
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
		} finally {
			db.endTransaction();
		}
	}


	public void initPerosn(SQLiteDatabase db, String table_NAME) {
		db.beginTransaction();
		try {
				ContentValues values = new ContentValues();
				values.put(PersonColumns.ID, "3");
				values.put(PersonColumns.USERNAME, "zhangkang");
				values.put(PersonColumns.BIRTH, "25");
				values.put(PersonColumns.ISMARRY, true);
				values.put(PersonColumns.SEX, "man");
				values.put(PersonColumns.BAPTISM, true);
				values.put(PersonColumns.ADDRESS, "china");
				values.put(PersonColumns.JOB, "programer");
				values.put(PersonColumns.PHONE, "110");
				values.put(PersonColumns.GROUPID, 1);
				values.put(PersonColumns.GROUPNAME, "信实组");
 				db.insert(table_NAME, null, values);
 
 				
 				db.setTransactionSuccessful();
		} catch (Exception e) {
		} finally {
			db.endTransaction();
		}
	}
		 
	
}

 

SQLiteOpenHelper在执行OnCreate的时候需要传入一个SQLiteDatabase 对象，SQLiteOpenHelper和 SQLiteDatabase是组合的关系。

这里的逻辑是将对于同一个数据库中的表的操作，都用一个类来进行管理。对于每张表中的逻辑封装到一个BeanColumns类当中，BeanColumns的作用是由它去跟SQLiteOpenHelper完成相应的业务表的创建和查询等等，这样下次如果有新的业务表的话，就只用再新建一个bean对象，新建一个BeanColumns对象，这样拓展性就比较好。
 

package com.example.faithhopelove;

public class PersonColumns {

	public static  String TABLE_NAME(){
		return "persons";
	} 
	public static final String ID = "id";  //ID
	
	public static final String USERNAME = "name";  //姓名
	
	public static final String PHONE = "phone";  //电话号码
	
	public static final String  SEX = "sex";  //性别
	
	public static final String BIRTH = "birth";  //生日
	
	public static final String JOB = "job";   //工作
	
	public static final String ISMARRY = "ismarried";   //是否结婚
	
	public static final String ADDRESS = "address";   //地址
	
	public static final String BAPTISM = "baptism";
	
	public static final String GROUPID = "groupid";   //组id
	
	public static final String GROUPNAME = "groupname";  //组名
	
	public static final String[] COLUMN_ARRAY = {
		ID,
		USERNAME,
		PHONE,
		SEX,
		BIRTH,
		JOB,
		ISMARRY,
		ADDRESS,
		BAPTISM,
		GROUPID,
		GROUPNAME
	};
	
	
	public static String CREAT_TABLE(String tableName){
		return new StringBuffer().
				append("CREATE TABLE IF NOT EXISTS ").append(tableName).
				append("(").
				append(ID).append(" TEXT PRIMARY KEY NOT NULL,").
				append(USERNAME).append(" TEXT,").
				append(PHONE).append(" TEXT,").
				append(SEX).append(" TEXT,").
				append(BIRTH).append(" TEXT,").
				append(JOB).append(" TEXT,").
				append(ISMARRY).append(" INTEGER DEFAULT 1,").
				append(ADDRESS).append(" INTEGER DEFAULT 0,").
				append(BAPTISM).append(" TEXT ,").
				append(GROUPID).append(" INTEGER DEFAULT 0, ").
				append(GROUPNAME).append(" TEXT  ").
				append(");").toString();
	}
	
	private static String DROP_TABLE(){
		return "DROP TABLE IF EXISTS " +  TABLE_NAME();
	}
	
}
 

 

     附录：github完整项目地址

    https://github.com/preqel/FaithHopeLove