相信很懂同学都接触过很多开源的数据库框架，具体的就不多说了，反正给我的感觉就是，用起来总是束手束脚的，哪怕说的再好也并没有自己做出来的那么趁手。另外，安卓本身对数据库的封装已经做得很不错了，我觉得自己封一个易用的并且好用的工具也并没有那么难，那么下面进入正题。
首先说说SQLiteOpenHelper这个工具类吧，其实这个工具类网上的教程资料很多，具体使用就不多说，主要说一下我是怎么写的吧。先自己写一个DatabaseHelper类继承SQLiteOpenHelper,这里为了更灵活我写了两个构造函数。代码如下：
public class DatabaseHelper extends SQLiteOpenHelper {
private static final String name = "ZPDB"; //数据库名称
private static final int version = 1;
public DatabaseHelper(Context context) {
this(context, name);
}
public DatabaseHelper(Context context, String dbname){
super(context, dbname, null, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS person (personid integer primary key autoincrement," +
" name varchar(20), password varchar(20), age INTEGER)");
db.execSQL("create table if not exists story (storyid integer primary key autoincrement," +
"title varchar(30), content varchar(1000), date varchar(20))");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}public class DatabaseContext extends ContextWrapper {
private String path;
public DatabaseContext(Context base, String path){
super(base);
this.path = path;
}
@Override
public File getDatabasePath(String name) {
//判断是否存在sd卡
boolean sdExist = android.os.Environment.MEDIA_MOUNTED.equals(android.os.Environment.getExternalStorageState());
if(!sdExist){
return null;
}
else{//如果存在
//获取sd卡路径
String dbDir=android.os.Environment.getExternalStorageDirectory().toString()+"/"+path;
String dbPath = dbDir+"/"+name;//数据库路径
//判断目录是否存在，不存在则创建该目录
File dirFile = new File(dbDir);
if(!dirFile.exists())
dirFile.mkdirs();
//数据库文件是否创建成功
boolean isFileCreateSuccess = false;
//判断文件是否存在，不存在则创建该文件
File dbFile = new File(dbPath);
if(!dbFile.exists())
{
try {
isFileCreateSuccess = dbFile.createNewFile();//创建文件
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
else {
isFileCreateSuccess = true;
}
//返回数据库文件对象
if(isFileCreateSuccess)
return dbFile;
else
return null;
}
}
@Override
public SQLiteDatabase openOrCreateDatabase(String name, int mode,
SQLiteDatabase.CursorFactory factory) {
SQLiteDatabase result = SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name), null);
return result;
}
@Override
public SQLiteDatabase openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory,
DatabaseErrorHandler errorHandler) {
SQLiteDatabase result = SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name), null);
return result;
}
}
SQLiteOpenHelper sqlHelper = new DatabaseHelper(new DatabaseContext (context, path)); 或者
SQLiteOpenHelper sqlHelper = new DatabaseHelper(new DatabaseContext (context, path)); 或者
SQLiteOpenHelper sqlHelper = new DatabaseHelper(new DatabaseContext (context, path), dbname);
这样你的数据库打开位置就是在path下面了，由于使用了SQLiteOpenHelper 这个帮助类，所以如果不存在的话就会自动帮你新建一个。
好了，数据库帮助类以及数据库打开位置搞定了，下面我们还要思考一个问题，那就是对数据的操作问题，数据库无外乎增删查改，如果每次都要重写依次，既浪费时间也不便于扩展和调整。我们应该把这些操作进行一下统一，这样如果有什么大的变动我们就只需要修改一个地方就可以了，而不用考虑其它的因素和可能影响到的地方。数据库操作工具类如下：
public class DatabaseManager2 {
private SQLiteOpenHelper mDatabaseHelper;
private SQLiteDatabase db;
public DatabaseManager2(SQLiteOpenHelper sqLiteOpenHelper){
this.mDatabaseHelper = sqLiteOpenHelper;
}
/**
for example
ContentValues values = new ContentValues();
values1.put("name", "张龙");
values1.put("address", "beijing");
values1.put("sex", "male");
insert("user", values);
* @param table
* @param values
* @return
*/
public boolean insert(String table, ContentValues values){
db = mDatabaseHelper.getWritableDatabase();
boolean flag = false;
long id = -1;
try{
id = db.insert(table, null, values);
flag = id != -1;
}catch (Exception e){
e.printStackTrace();
}
close();
return flag;
}
/**
for example
delete("user", " id =? ", new String[]{"1"});
* @param table
* @param whereClause
* @param whereArgs
* @return
*/
public boolean delete(String table, String whereClause, String[] whereArgs){
db = mDatabaseHelper.getWritableDatabase();
boolean flag = false;
int count;
try {
count = db.delete(table, whereClause, whereArgs);
flag = count > 0;
} catch (Exception e) {
e.printStackTrace();
}
close();
return flag;
}
/**
for example
ContentValues values = new ContentValues();
values.put("name", "张三");
values.put("address", "上海");
values.put("sex", "男");
update("user", values, " id=? ", new String[]{"2"});
* @param table
* @param values
* @param whereClause
* @param whereArgs
* @return
*/
public boolean update(String table, ContentValues values, String whereClause,String[] whereArgs) {
db = mDatabaseHelper.getWritableDatabase();
boolean flag = false;
int count;
try {
count = db.update(table, values, whereClause, whereArgs);
flag = count > 0;
} catch (Exception e) {
e.printStackTrace();
}
close();
return flag;
}
/**
for example
view("user", " id=? ", new String[]{"2"});
* @param table
* @param selection
* @param selectionArgs
* @return
*/
public Map<String, String> view(String table, String selection, String[] selectionArgs) {
db = mDatabaseHelper.getReadableDatabase();
Cursor cursor = null;
Map<String, String> map = new HashMap<String, String>();
try {
cursor = db.query(true, table, null, selection,selectionArgs, null, null, null, null);
int cols_len = cursor.getColumnCount();
while (cursor.moveToNext()) {
for (int i = 0; i < cols_len; i++) {
String cols_key = cursor.getColumnName(i);
String cols_value = cursor.getString(cursor.getColumnIndex(cols_key));
if (cols_value == null) {
cols_value = "";
}
map.put(cols_key, cols_value);
}
}
} catch (Exception e) {
e.printStackTrace();
}
if(cursor != null){
cursor.close();
}
close();
return map;
}
/**
for example
listPersonMaps("user", null, null);
* @param table
* @param selection
* @param selectionArgs
* @return
*/
public List<Map<String, String>> listPersonMaps(String table, String selection, String[] selectionArgs, String orderBy) {
db = mDatabaseHelper.getReadableDatabase();
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
Cursor cursor = null;
try {
cursor = db.query(false, table, null, selection,selectionArgs, null, null, orderBy, null);
int cols_len = cursor.getColumnCount();
while (cursor.moveToNext()) {
Map<String, String> map = new HashMap<String, String>();
for (int i = 0; i < cols_len; i++) {
String cols_key = cursor.getColumnName(i);
String cols_value = cursor.getString(cursor.getColumnIndex(cols_key));
if (cols_value == null) {
cols_value = "";
}
map.put(cols_key, cols_value);
}
list.add(map);
}
} catch (Exception e) {
e.printStackTrace();
}
if(cursor != null){
cursor.close();
}
close();
return list;
}
/**
* 批量插入
* @param sTableName
* @param ListOfMaps
*/
public void InsertMaps(String sTableName, List<Map<String, Object>> ListOfMaps)
{
db = mDatabaseHelper.getWritableDatabase();
try
{
boolean bFirst=true;
if(ListOfMaps==null || ListOfMaps.size()<1)
{
return;
}
Log.e("insertbegin", "begin");
db.beginTransaction();
for(int i=0;i<ListOfMaps.size();i++)
{
bFirst= true;
StringBuilder sb=new StringBuilder();
StringBuilder columns=new StringBuilder();
StringBuilder values=new StringBuilder();
sb = sb.append("INSERT INTO [" + sTableName + "] (");
ArrayList<Object>listOfValues = new ArrayList<Object>();
Map<String, Object> map = ListOfMaps.get(i);
Iterator<Map.Entry<String, Object>> iterator =map.entrySet().iterator();
while(iterator.hasNext())
{
Map.Entry<String, Object> next = iterator.next();
if(!bFirst)
{
columns = columns.append(", ");
values = values.append(", ");
}
columns.append("[").append(next.getKey()).append("]");
values.append("?");
listOfValues.add(next.getValue());
bFirst = false;
}
sb = sb.append(columns.toString()).append(") VALUES (").append(values.toString()).append(")");
ExecNonQuery2(sb.toString(), listOfValues);
}
db.setTransactionSuccessful();
db.endTransaction();
Log.e("insertend", "end");
}
catch (Exception e)
{
e.printStackTrace();
}
close();
}
public void ExecNonQuery2(String Statement, List<Object> Args)
{
SQLiteStatement s = this.db.compileStatement(Statement);
try
{
int numArgs = 0;
if (Args != null)
{
numArgs = Args.size();
}
for (int i = 0; i < numArgs; i++)
{
DatabaseUtils.bindObjectToProgram(s, i + 1, Args.get(i));
}
s.execute();
} finally
{
s.close();
}
}
public void close(){
if(isOpen()){
db.close();
}
}
public boolean isOpen(){
if(db != null){
return db.isOpen();
}
return false;
}
}
具体就不多说，使用方法也是基于系统给的方法进行的简单封装，主要注意一下关于事务的操作就差不多了。
最后是关于如何使用的问题，在这里我的建议是针对每个表自己建一个类，对涉及到的增删查改进行具体操作，因为每次增删改查的具体字段都不相同，而且考虑数据库位置及名称等原因，这样更便于管理和修改，降低耦合度。示范代码如下：
public class TestDemo{
public final String TABLE = "story";
DatabaseManager2 databaseManager2;
public TestDemo(Context context){
this(context, null);
}
public TestDemo(Context context, String path){
databaseManager2 = new DatabaseManager2(new DatabaseHelper(!TextUtils.isEmpty(path)?new DatabaseContext(context, path):context));
}
public void add(Story story) {
ContentValues contentValues = new ContentValues();
contentValues.put("title", story.getTitle());
contentValues.put("content", story.getContent());
contentValues.put("date", story.getDate());
databaseManager2.insert(TABLE, contentValues);
}
}public class DatabaseManager {
private AtomicInteger mOpenCounter = new AtomicInteger();
private static DatabaseManager instance;
private static SQLiteOpenHelper mDatabaseHelper;
private SQLiteDatabase db;
public static synchronized void initializeInstance(SQLiteOpenHelper helper) {
if (instance == null) {
instance = new DatabaseManager();
mDatabaseHelper = helper;
}
}
public static synchronized DatabaseManager getInstance() {
if (instance == null) {
throw new IllegalStateException(DatabaseManager.class.getSimpleName() +
" is not initialized, call initializeInstance(..) method first.");
}
return instance;
}
public synchronized SQLiteDatabase openDatabase() {
if(mOpenCounter.incrementAndGet() == 1) {
// Opening new database
db = mDatabaseHelper.getWritableDatabase();
}
return db;
}
public synchronized void closeDatabase() {
if(mOpenCounter.decrementAndGet() == 0) {
// Closing database
db.close();
}
}
/**
for example
ContentValues values = new ContentValues();
values1.put("name", "张龙");
values1.put("address", "beijing");
values1.put("sex", "male");
insert("user", values);
* @param table
* @param values
* @return
*/
public boolean insert(String table, ContentValues values){
openDatabase();
boolean flag = false;
long id = -1;
try{
id = db.insert(table, null, values);
flag = id != -1;
}catch (Exception e){
e.printStackTrace();
}
closeDatabase();
return flag;
}
/**
for example
delete("user", " id =? ", new String[]{"1"});
* @param table
* @param whereClause
* @param whereArgs
* @return
*/
public boolean delete(String table, String whereClause, String[] whereArgs){
openDatabase();
boolean flag = false;
int count;
try {
count = db.delete(table, whereClause, whereArgs);
flag = count > 0;
} catch (Exception e) {
e.printStackTrace();
}
closeDatabase();
return flag;
}
/**
for example
ContentValues values = new ContentValues();
values.put("name", "张三");
values.put("address", "上海");
values.put("sex", "男");
update("user", values, " id=? ", new String[]{"2"});
* @param table
* @param values
* @param whereClause
* @param whereArgs
* @return
*/
public boolean update(String table, ContentValues values, String whereClause,String[] whereArgs) {
openDatabase();
boolean flag = false;
int count;
try {
count = db.update(table, values, whereClause, whereArgs);
flag = count > 0;
} catch (Exception e) {
e.printStackTrace();
}
closeDatabase();
return flag;
}
/**
for example
view("user", " id=? ", new String[]{"2"});
* @param table
* @param selection
* @param selectionArgs
* @return
*/
public Map<String, String> view(String table, String selection, String[] selectionArgs) {
openDatabase();
Cursor cursor = null;
Map<String, String> map = new HashMap<String, String>();
try {
cursor = db.query(true, table, null, selection,selectionArgs, null, null, null, null);
int cols_len = cursor.getColumnCount();
while (cursor.moveToNext()) {
for (int i = 0; i < cols_len; i++) {
String cols_key = cursor.getColumnName(i);
String cols_value = cursor.getString(cursor.getColumnIndex(cols_key));
if (cols_value == null) {
cols_value = "";
}
map.put(cols_key, cols_value);
}
}
} catch (Exception e) {
e.printStackTrace();
}
if(cursor != null){
cursor.close();
}
closeDatabase();
return map;
}
/**
for example
listPersonMaps("user", null, null);
* @param table
* @param selection
* @param selectionArgs
* @return
*/
public List<Map<String, String>> listPersonMaps(String table, String selection, String[] selectionArgs, String orderBy) {
openDatabase();
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
Cursor cursor = null;
try {
cursor = db.query(false, table, null, selection,selectionArgs, null, null, orderBy, null);
int cols_len = cursor.getColumnCount();
while (cursor.moveToNext()) {
Map<String, String> map = new HashMap<String, String>();
for (int i = 0; i < cols_len; i++) {
String cols_key = cursor.getColumnName(i);
String cols_value = cursor.getString(cursor.getColumnIndex(cols_key));
if (cols_value == null) {
cols_value = "";
}
map.put(cols_key, cols_value);
}
list.add(map);
}
} catch (Exception e) {
e.printStackTrace();
}
if(cursor != null){
cursor.close();
}
closeDatabase();
return list;
}
/**
* 批量插入
* @param sTableName
* @param ListOfMaps
*/
public void InsertMaps(String sTableName, List<Map<String, Object>> ListOfMaps)
{
openDatabase();
try
{
boolean bFirst=true;
if(ListOfMaps==null || ListOfMaps.size()<1)
{
return;
}
Log.e("insertbegin", "begin");
db.beginTransaction();
for(int i=0;i<ListOfMaps.size();i++)
{
bFirst= true;
StringBuilder sb=new StringBuilder();
StringBuilder columns=new StringBuilder();
StringBuilder values=new StringBuilder();
sb = sb.append("INSERT INTO [" + sTableName + "] (");
ArrayList<Object>listOfValues = new ArrayList<Object>();
Map<String, Object> map = ListOfMaps.get(i);
Iterator<Map.Entry<String, Object>> iterator =map.entrySet().iterator();
while(iterator.hasNext())
{
Map.Entry<String, Object> next = iterator.next();
if(!bFirst)
{
columns = columns.append(", ");
values = values.append(", ");
}
columns.append("[").append(next.getKey()).append("]");
values.append("?");
listOfValues.add(next.getValue());
bFirst = false;
}
sb = sb.append(columns.toString()).append(") VALUES (").append(values.toString()).append(")");
ExecNonQuery2(sb.toString(), listOfValues);
}
db.setTransactionSuccessful();
db.endTransaction();
Log.e("insertend", "end");
}
catch (Exception e)
{
e.printStackTrace();
}
closeDatabase();
}
public void ExecNonQuery2(String Statement, List<Object> Args)
{
SQLiteStatement s = this.db.compileStatement(Statement);
try
{
int numArgs = 0;
if (Args != null)
{
numArgs = Args.size();
}
for (int i = 0; i < numArgs; i++)
{
DatabaseUtils.bindObjectToProgram(s, i + 1, Args.get(i));
}
s.execute();
} finally
{
s.close();
}
}
public boolean isOpen(){
if(db != null){
return db.isOpen();
}
return false;
}
}
730
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
