玩转android sqlLite---（附android DB的图行工具）

捣鼓android一年多了，总结一下sqlLite的开发经验。

sqlLite就像个迷你数据库，配套的操作工具还不完善，操作起来不直观。不像oracle、mysql那样有图形化的操作工作。

偶然在网上发现一款操作sqlLite的图形化工具 ---- SQLiteSpy（后附上链接）。如下图：

玩转android sqlLite---（附android DB的图行工具）_第1张图片

怎么样！嘿嘿，虽然功能还显简单，但对开发者来说，起码说看到比较直观的界面。

操作步骤很简单，首先导入sqlLite 的DB文件(即File Explorer /data /data/ )，然后进行各种sql操作。

顺便写一下，我常用到的sqlLite操作类，对增删查改进行了简单的封装。

import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.SQLException;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class DBHelper {static private DatabaseHelper mDbHelper;static private SQLiteDatabase mDb;private static final String DATABASE_NAME = "zhyy.db";private static final int DATABASE_VERSION = 1;private final Context mCtx;private static class DatabaseHelper extends SQLiteOpenHelper {DatabaseHelper(Context context) {super(context, DATABASE_NAME, null, DATABASE_VERSION);}@Overridepublic void onCreate(SQLiteDatabase db) {}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}}public DBHelper(Context ctx) {this.mCtx = ctx;}public DBHelper open() throws SQLException {mDbHelper = new DatabaseHelper(mCtx);mDb = mDbHelper.getWritableDatabase();return this;}public void closeclose() {mDb.close();mDbHelper.close();}/** * 插入数据 * 参数：tableName 表名 * initialValues 要插入的列对应值 *   */public long insert(String tableName,ContentValues initialValues) {return mDb.insert(tableName, null, initialValues);}/** * 删除数据 * 参数：tableName 表名 * deleteCondition 删除的条件 * deleteArgs 如果deleteCondition中有“？”号，将用此数组中的值替换 *   */public boolean delete(String tableName,String deleteCondition,String[] deleteArgs) {return mDb.delete(tableName, deleteCondition, deleteArgs) > 0;}/** * 更新数据 * 参数：tableName 表名 * initialValues 要更新的列 * selection 更新的条件 * selectArgs 如果selection中有“？”号，将用此数组中的值替换 *   */public boolean update(String tableName,ContentValues initialValues,String selection,String[] selectArgs) {int returnValue = mDb.update(tableName, initialValues, selection, selectArgs);return  returnValue > 0; }/** * 取得一个列表 * 参数：tableName 表名 * columns 返回的列 * selection 查询条件 * selectArgs 如果selection中有“？”号，将用此数组中的值替换 *   */public Cursor findList(String tableName,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy) {return mDb.query(tableName, columns, selection, selectionArgs, groupBy, having, orderBy);}/** * 取得单行记录 * 参数：tableName 表名 * columns 返回的列 * selection 查询条件 * selectArgs 如果selection中有“？”号，将用此数组中的值替换 *   */public Cursor findInfo(String tableName,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy,String limit,boolean distinct) throws SQLException {Cursor mCursor = mDb.query(distinct, tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit);if (mCursor != null) {mCursor.moveToFirst();}return mCursor;}/** * 执行sql * 参数：sql 要执行的sql  *   */public void execSQL(String sql){mDb.execSQL(sql);}/**    * 判断某张表是否存在    * @param tabName 表名    * @return    */public boolean isTableExist(String tableName){           boolean result = false;           if(tableName == null){                   return false;           }                     try {           Cursor cursor = null;                   String sql = "select count(1) as c from sqlite_master where type ='table' and name ='"+tableName.trim()+"' ";                   cursor = mDb.rawQuery(sql, null);                   if(cursor.moveToNext()){                           int count = cursor.getInt(0);                           if(count>0){                                   result = true;                           }                   }                                                         cursor.close();           } catch (Exception e) {                   // TODO: handle exception           }                           return result;   } /**    * 判断某张表中是否存在某字段(注，该方法无法判断表是否存在，因此应与isTableExist一起使用)    *     * @param tabName 表名    * @return    */ public boolean isColumnExist(String tableName,String columnName){           boolean result = false;           if(tableName == null){                   return false;           }                              try {           Cursor cursor = null;                   String sql = "select count(1) as c from sqlite_master where type ='table' and name ='"+tableName.trim()+"' and sql like '%"+ columnName.trim() +"%'" ;                   cursor = mDb.rawQuery(sql, null);                   if(cursor.moveToNext()){                           int count = cursor.getInt(0);                           if(count>0){                                   result = true;                           }                   }                                                         cursor.close();           } catch (Exception e) {                   // TODO: handle exception           }                           return result;   }   }

好吧，也顺便写一下各种增删查改的sql。

package com.android.mission.test;import com.android.mission.util.DBHelper;import android.content.ContentValues;import android.database.Cursor;import android.test.AndroidTestCase;import android.util.Log;/** * 单元测试操作sqlLite的各种sql */public class testSqlLite extends AndroidTestCase{/** * 创建表 * @throws Exception */public void createTable() throws Exception{DBHelper dbHelper = new DBHelper(this.getContext());dbHelper.open();String deleteSql = "drop table if exists user ";   dbHelper.execSQL(deleteSql); //id是自动增长的主键，username和 password为字段名， text为字段的类型String sql = "CREATE TABLE user (id integer primary key autoincrement, username text, password text)";  dbHelper.execSQL(sql);dbHelper.closeclose();}/** * 插入数据 * @throws Exception */public void insert() throws Exception{DBHelper dbHelper = new DBHelper(this.getContext());dbHelper.open();ContentValues values =  new ContentValues();  //相当于mapvalues.put("username", "test");values.put("password", "123456");dbHelper.insert("user", values);dbHelper.closeclose();}/** * 更新数据 * @throws Exception */public void update() throws Exception{DBHelper dbHelper = new DBHelper(this.getContext());dbHelper.open();ContentValues initialValues = new ContentValues();initialValues.put("username", "changename");  //更新的字段和值dbHelper.update("user", initialValues, "id = '1'", null);   //第三个参数为 条件语句dbHelper.closeclose();}/** * 删除数据 * @throws Exception */public void delete() throws Exception{DBHelper dbHelper = new DBHelper(this.getContext());dbHelper.open();String testId = "1";dbHelper.delete("user", "id = '"+ testId +"'", null);dbHelper.closeclose();}/** * 增加字段 * @throws Exception */public void addColumn() throws Exception{DBHelper dbHelper = new DBHelper(this.getContext());dbHelper.open();String updateSql = "alter table user add company text";dbHelper.execSQL(updateSql);}/** * 查询列表 * @throws Exception */public void selectList()throws Exception{DBHelper dbHelper = new DBHelper(this.getContext());dbHelper.open();Cursor returnCursor = dbHelper.findList("user",new String[] {"id","username", "password"}, "username = 'test'", null,null, null, "id desc");while(returnCursor.moveToNext()){String id = returnCursor.getString(returnCursor.getColumnIndexOrThrow("id"));String username = returnCursor.getString(returnCursor.getColumnIndexOrThrow("username"));String password = returnCursor.getString(returnCursor.getColumnIndexOrThrow("password"));}}/** * 某条信息 * @throws Exception */public void selectInfo()throws Exception{DBHelper dbHelper = new DBHelper(this.getContext());dbHelper.open();Cursor returnCursor = dbHelper.findList("user",new String[] {"id","username", "password"}, "id = '1'", null,null, null, "id desc");if (returnCursor.getCount() > 0) {returnCursor.moveToFirst();String id = returnCursor.getString(returnCursor.getColumnIndexOrThrow("id"));String username = returnCursor.getString(returnCursor.getColumnIndexOrThrow("username"));String password = returnCursor.getString(returnCursor.getColumnIndexOrThrow("password"));}}}

作者：baolong47 发表于2011-12-16 12:25:02 原文链接阅读：13 评论：0 查看评论

更多相关文章

随机推荐