玩转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"));
		}
	}
}