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