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

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

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

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

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

操作步骤很简单，首先导入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); } @Override public void onCreate(SQLiteDatabase db) { } @Override public 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(); //相当于map values.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")); } } }