>>Android数据库操作类，超实用！

Chen Zhidong @ 2011 年 12 月 21 日

Android代码分享第三弹：超实用的数据库操作类！在书里看到的，整理出来了，在原来的基础上又加了几个函数，并且新建数据库的那边也做了更改，判断所在目录有没有。最初使用时候经常发生错误，不能在程序私有空间建立数据库文件，后来才发现是路径问题，改了一下，从此有权限的地方都能建库！so easy!在查询那边加了一个用sql语句进行查询的函数，原来的那个函数不会用，还是直接sql方便。加了个复制数据库的函数。准备好数据库放在raw目录下，到时候用R.raw访问。

不多说，看代码：

package czd.android.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class SQLiteUtil {

	public static final String SQLite_MASTER_TABLE = "sqlite_master";
	private static SQLiteUtil mInstance = new SQLiteUtil();

	/**
	 *
	 */
	private SQLiteUtil() {
	}

	/**
	 * @return instance of SQLiteUtil
	 */
	public static SQLiteUtil getInstance() {
		return SQLiteUtil.mInstance;
	}

	/**
	 * open or create a database with the given dbName:
	 * /data/data/packagename/databases/databasefilename
	 * /sdcard/databasefilename
	 *
	 * @param dbName
	 * @return SQLiteDatabase
	 */
	private SQLiteDatabase openDB(String dbName) {
		File file = new File(dbName);
		if (file.exists() == true) {
			return SQLiteDatabase.openDatabase(dbName, null, SQLiteDatabase.OPEN_READWRITE);
		}else {
			File datafiledir = file.getParentFile();
			if(!datafiledir.exists()){
				datafiledir.mkdirs();
			}
			return SQLiteDatabase.openOrCreateDatabase(dbName, null);
		}
	}

	/**
	 * close database
	 *
	 * @param db
	 */
	private void closeDB(SQLiteDatabase db) {
		db.close();
	}

	/**
	 * delete database
	 *
	 * @param dbName
	 * @return boolean
	 */
	public boolean deleteDB(String dbName) {
		File file = new File(dbName);
		if (file.exists() == true) {
			return file.delete();
		}
		return true;
	}

	/**
	 * execute sql command
	 *
	 * @param dbName
	 * @param sql
	 */
	public void execQuery(String dbName, String sql) {
		SQLiteDatabase db = openDB(dbName);
		db.execSQL(sql);
		closeDB(db);
	}

	/**
	 * execute sql command and return Cursor
	 *
	 * @param dbName
	 * @param tableName
	 * @param condStr
	 * @return Cursor
	 */
	public Cursor openQuery(String dbName, String sql) {
		SQLiteDatabase db = openDB(dbName);
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		closeDB(db);
		return cursor;
	}

	public Cursor openQuery(String dbName, String tableName, String condStr) {
		SQLiteDatabase db = openDB(dbName);
		Cursor cursor = db.query(tableName, null, condStr, null, null, null, null);
		cursor.moveToFirst();
		closeDB(db);
		return (cursor);
	}

	public int getRowsCount(Cursor cursor) {
		return cursor.getCount();
	}

	public int getColumnsCount(Cursor cursor) {
		return cursor.getColumnCount();
	}

	public String getColumnNameBy(Cursor cursor, int index) {
		return cursor.getColumnName(index);
	}

	public boolean isBOF(Cursor cursor) {
		return cursor.isBeforeFirst();
	}

	public boolean isEOF(Cursor cursor) {
		return cursor.isAfterLast();
	}

	public boolean moveNext(Cursor cursor) {
		return cursor.moveToNext();
	}

	public String getField(Cursor cursor, int index) {
		return cursor.getString(index);
	}

	public void closeQuery(Cursor cursor) {
		cursor.close();
	}

	public boolean isTableExists(String dbName, String tableName) {
		Cursor cursor = openQuery(dbName, SQLite_MASTER_TABLE, "(tbl_name='" + tableName + "')");
		int recordCount = cursor.getCount();
		cursor.close();
		return (recordCount > 0);
	}

	public boolean isDatabaseExists(String dbName){
		return new File(dbName).exists();
	}

	public static void copyDatabase(Context context, String dbName, int resourceId) {
		try {
			if (!(new File(dbName)).exists()) {
				InputStream is = context.getResources().openRawResource(resourceId);
				FileOutputStream fos = new FileOutputStream(dbName);
				byte[] buffer = new byte[1024];
				int count = 0;
				while ((count = is.read(buffer)) > 0) {
					fos.write(buffer, 0, count);
				}
				fos.close();
				is.close();
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
};