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

最新推荐文章于 2022-03-10 17:32:18 发布

fengkuanghun

最新推荐文章于 2022-03-10 17:32:18 发布

阅读量1.1k

点赞数

分类专栏： Android

Android 专栏收录该内容

166 篇文章

订阅专栏

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

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

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

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

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

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

      
   001import  android.content.ContentValues; 
 
   002import  android.content.Context; 
 
   003import  android.database.Cursor; 
 
   004import  android.database.SQLException; 
 
   005import  android.database.sqlite.SQLiteDatabase; 
 
   006import  android.database.sqlite.SQLiteOpenHelper; 
 
   007
 
   008public  class DBHelper { 
 
   009static private DatabaseHelper mDbHelper; 
 
   010static private SQLiteDatabase mDb; 
 
   011
 
   012private static final  String DATABASE_NAME = "zhyy.db"; 
 
   013
 
   014private static final  int DATABASE_VERSION = 1; 
 
   015
 
   016private final Context mCtx;  
 
   017
 
   018private static class  DatabaseHelper extends  SQLiteOpenHelper { 
 
   019
 
   020DatabaseHelper(Context context) { 
 
   021super(context, DATABASE_NAME, null, DATABASE_VERSION); 
 
   022}  
 
   023
 
   024@Override
 
   025public void onCreate(SQLiteDatabase db) { 
 
   026
 
   027}  
 
   028@Override
 
   029public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
 
   030
 
   031}  
 
   032}  
 
   033
 
   034public DBHelper(Context ctx) { 
 
   035this.mCtx = ctx; 
 
   036}  
 
   037
 
   038public DBHelper open() throws SQLException { 
 
   039mDbHelper = new DatabaseHelper(mCtx); 
 
   040mDb = mDbHelper.getWritableDatabase(); 
 
   041return this; 
 
   042}  
 
   043
 
   044public void closeclose() {  
 
   045
 
   046mDb.close(); 
 
   047mDbHelper.close(); 
 
   048}  
 
   049
 
   050/** 
 
   051* 插入数据 
 
   052* 参数：tableName 表名 
 
   053* initialValues 要插入的列对应值 
 
   054* */
 
   055public long insert(String tableName,ContentValues initialValues) { 
 
   056
 
   057return mDb.insert(tableName, null, initialValues); 
 
   058}  
 
   059
 
   060
 
   061/** 
 
   062* 删除数据 
 
   063* 参数：tableName 表名 
 
   064* deleteCondition 删除的条件 
 
   065* deleteArgs 如果deleteCondition中有“？”号，将用此数组中的值替换 
 
   066* */
 
   067public boolean delete(String tableName,String deleteCondition,String[] deleteArgs) { 
 
   068
 
   069return mDb.delete(tableName, deleteCondition, deleteArgs) >  0; 
 
   070}  
 
   071
 
   072
 
   073/** 
 
   074* 更新数据 
 
   075* 参数：tableName 表名 
 
   076* initialValues 要更新的列 
 
   077* selection 更新的条件 
 
   078* selectArgs 如果selection中有“？”号，将用此数组中的值替换 
 
   079* */
 
   080public boolean update(String tableName,ContentValues initialValues,String selection,String[] selectArgs) { 
 
   081int returnValue = mDb.update(tableName, initialValues, selection, selectArgs); 
 
   082
 
   083return returnValue > 0; 
 
   084}  
 
   085
 
   086/** 
 
   087* 取得一个列表 
 
   088* 参数：tableName 表名 
 
   089* columns 返回的列 
 
   090* selection 查询条件 
 
   091* selectArgs 如果selection中有“？”号，将用此数组中的值替换 
 
   092* */
 
   093public Cursor findList(String tableName,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy) { 
 
   094
 
   095return mDb.query(tableName, columns, selection, selectionArgs, groupBy, having, orderBy); 
 
   096}  
 
   097
 
   098/** 
 
   099* 取得单行记录 
 
   100* 参数：tableName 表名 
 
   101* columns 返回的列 
 
   102* selection 查询条件 
 
   103* selectArgs 如果selection中有“？”号，将用此数组中的值替换 
 
   104* */
 
   105public Cursor findInfo(String tableName,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy,String limit,boolean distinct) throws SQLException { 
 
   106
 
   107Cursor mCursor = mDb.query(distinct, tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit); 
 
   108
 
   109
 
   110if (mCursor != null) { 
 
   111mCursor.moveToFirst(); 
 
   112}  
 
   113return mCursor; 
 
   114
 
   115}  
 
   116
 
   117/** 
 
   118* 执行sql 
 
   119* 参数：sql 要执行的sql 
 
   120
 
   121* */
 
   122public void execSQL(String sql){ 
 
   123mDb.execSQL(sql); 
 
   124
 
   125}  
 
   126
 
   127/** 
 
   128* 判断某张表是否存在 
 
   129* @param tabName 表名 
 
   130* @return 
 
   131*/
 
   132public boolean isTableExist(String tableName){ 
 
   133boolean result = false; 
 
   134if(tableName == null){ 
 
   135return false; 
 
   136}  
 
   137
 
   138try { 
 
   139Cursor cursor = null; 
 
   140String sql = "select count(1) as c from sqlite_master where type ='table' and name ='"+tableName.trim()+"' "; 
 
   141cursor = mDb.rawQuery(sql, null); 
 
   142if(cursor.moveToNext()){ 
 
   143int count = cursor.getInt(0); 
 
   144if(count>0){ 
 
   145result = true; 
 
   146}  
 
   147}  
 
   148
 
   149
 
   150cursor.close(); 
 
   151}  catch (Exception e) {  
 
   152// TODO: handle exception 
 
   153}  
 
   154return result; 
 
   155}  
 
   156
 
   157
 
   158/** 
 
   159* 判断某张表中是否存在某字段(注，该方法无法判断表是否存在，因此应与isTableExist一起使用) 
 
   160* 
 
   161* @param tabName 表名 
 
   162* @return 
 
   163*/
 
   164public boolean isColumnExist(String tableName,String columnName){ 
 
   165boolean result = false; 
 
   166if(tableName == null){ 
 
   167return false; 
 
   168}  
 
   169
 
   170
 
   171try { 
 
   172Cursor cursor = null; 
 
   173String sql = "select count(1) as c from sqlite_master where type ='table' and name ='"+tableName.trim()+"' and sql like '%"+ columnName.trim() +"%'" ;  
 
   174cursor = mDb.rawQuery(sql, null); 
 
   175if(cursor.moveToNext()){ 
 
   176int count = cursor.getInt(0); 
 
   177if(count>0){ 
 
   178result = true; 
 
   179}  
 
   180}  
 
   181
 
   182
 
   183cursor.close(); 
 
   184}  catch (Exception e) {  
 
   185// TODO: handle exception 
 
   186}  
 
   187return result; 
 
   188}  
 
   189
 
   190
 
   191
 
   192
 
   193
 
   194
 
   195}

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

      
   001package  com.android.mission.test; 
 
   002
 
   003import  com.android.mission.util.DBHelper; 
 
   004
 
   005import  android.content.ContentValues; 
 
   006import  android.database.Cursor; 
 
   007import  android.test.AndroidTestCase; 
 
   008import  android.util.Log; 
 
   009/** 
 
   010* 单元测试操作sqlLite的各种sql 
 
   011*/
 
   012public  class testSqlLite extends AndroidTestCase{ 
 
   013
 
   014/** 
 
   015* 创建表 
 
   016* @throws Exception 
 
   017*/
 
   018public void createTable()  throws Exception{  
 
   019DBHelper dbHelper = new DBHelper(this.getContext()); 
 
   020dbHelper.open(); 
 
   021
 
   022String deleteSql = "drop table if exists user "; 
 
   023dbHelper.execSQL(deleteSql); 
 
   024
 
   025//id是自动增长的主键，username和 password为字段名， text为字段的类型 
 
   026String sql = "CREATE TABLE user (id integer primary key autoincrement, username text, password text)"; 
 
   027dbHelper.execSQL(sql); 
 
   028dbHelper.closeclose(); 
 
   029}  
 
   030
 
   031/** 
 
   032* 插入数据 
 
   033* @throws Exception 
 
   034*/
 
   035public void insert() throws Exception{ 
 
   036DBHelper dbHelper = new DBHelper(this.getContext()); 
 
   037dbHelper.open(); 
 
   038
 
   039ContentValues values = new ContentValues(); //相当于map 
 
   040
 
   041values.put("username", "test");  
 
   042values.put("password", "123456");  
 
   043
 
   044dbHelper.insert("user", values); 
 
   045
 
   046dbHelper.closeclose(); 
 
   047}  
 
   048
 
   049/** 
 
   050* 更新数据 
 
   051* @throws Exception 
 
   052*/
 
   053public void update() throws Exception{ 
 
   054DBHelper dbHelper = new DBHelper(this.getContext()); 
 
   055dbHelper.open(); 
 
   056ContentValues initialValues = new ContentValues(); 
 
   057initialValues.put("username", "changename");  //更新的字段和值 
 
   058dbHelper.update("user", initialValues, "id = '1'",  null); //第三个参数为 条件语句 
 
   059
 
   060dbHelper.closeclose(); 
 
   061}  
 
   062
 
   063
 
   064/** 
 
   065* 删除数据 
 
   066* @throws Exception 
 
   067*/
 
   068public void delete() throws Exception{ 
 
   069DBHelper dbHelper = new DBHelper(this.getContext()); 
 
   070dbHelper.open(); 
 
   071
 
   072String testId = "1"; 
 
   073dbHelper.delete("user", "id = '"+ testId +"'", null); 
 
   074
 
   075dbHelper.closeclose(); 
 
   076}  
 
   077
 
   078
 
   079/** 
 
   080* 增加字段 
 
   081* @throws Exception 
 
   082*/
 
   083public void addColumn()  throws Exception{  
 
   084DBHelper dbHelper = new DBHelper(this.getContext()); 
 
   085dbHelper.open(); 
 
   086
 
   087String updateSql = "alter table user add company text"; 
 
   088dbHelper.execSQL(updateSql); 
 
   089}  
 
   090
 
   091/** 
 
   092* 查询列表 
 
   093* @throws Exception 
 
   094*/
 
   095public void selectList()throws Exception{ 
 
   096DBHelper dbHelper = new DBHelper(this.getContext()); 
 
   097dbHelper.open(); 
 
   098Cursor returnCursor = dbHelper.findList("user",new String[] {"id","username", "password"},  "username = 'test'",  null,null, null, "id desc"); 
 
   099while(returnCursor.moveToNext()){ 
 
   100String id = returnCursor.getString(returnCursor.getColumnIndexOrThrow("id")); 
 
   101String username = returnCursor.getString(returnCursor.getColumnIndexOrThrow("username")); 
 
   102String password = returnCursor.getString(returnCursor.getColumnIndexOrThrow("password")); 
 
   103}  
 
   104}  
 
   105
 
   106/** 
 
   107* 某条信息 
 
   108* @throws Exception 
 
   109*/
 
   110public void selectInfo()throws Exception{ 
 
   111DBHelper dbHelper = new DBHelper(this.getContext()); 
 
   112dbHelper.open(); 
 
   113Cursor returnCursor = dbHelper.findList("user",new String[] {"id","username", "password"},  "id = '1'", null,null, null, "id desc"); 
 
   114if (returnCursor.getCount() > 0) { 
 
   115returnCursor.moveToFirst(); 
 
   116String id = returnCursor.getString(returnCursor.getColumnIndexOrThrow("id")); 
 
   117String username = returnCursor.getString(returnCursor.getColumnIndexOrThrow("username")); 
 
   118String password = returnCursor.getString(returnCursor.getColumnIndexOrThrow("password")); 
 
   119}  
 
   120}  
 
   121}