【转载】Android数据库(SqlLite)操作和db文件查看
原文地址：http://blog.csdn.net/catoop/article/details/7589204
操作步骤很简单，首先导入sqlLite 的DB文件(即File Explorer /data /data/)，然后进行各种sql操作。
下面是我的代码：
package com.xiaoshan.udp.client.db; 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; /** * 数据库常用操作的封装类 * * @author 单红宇 * */ public class DBHelper { private static DatabaseHelper mDbHelper; private static SQLiteDatabase mDb; private static final String DATABASE_NAME = "shanhy.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; } /** * 关闭数据源 * * @author SHANHY */ public void closeConnection() { if (mDb != null && mDb.isOpen()) mDb.close(); if (mDbHelper != null) mDbHelper.close(); } /** * 插入数据 参数 * * @param tableName * 表名 * @param initialValues * 要插入的列对应值 * @return * @author SHANHY */ public long insert(String tableName, ContentValues initialValues) { return mDb.insert(tableName, null, initialValues); } /** * 删除数据 * * @param tableName * 表名 * @param deleteCondition * 条件 * @param deleteArgs * 条件对应的值（如果deleteCondition中有“？”号，将用此数组中的值替换，一一对应） * @return * @author SHANHY */ public boolean delete(String tableName, String deleteCondition, String[] deleteArgs) { return mDb.delete(tableName, deleteCondition, deleteArgs) > 0; } /** * 更新数据 * * @param tableName * 表名 * @param initialValues * 要更新的列 * @param selection * 更新的条件 * @param selectArgs * 更新条件中的“？”对应的值 * @return * @author SHANHY */ public boolean update(String tableName, ContentValues initialValues, String selection, String[] selectArgs) { return mDb.update(tableName, initialValues, selection, selectArgs) > 0; } /** * 取得一个列表 * * @param distinct * 是否去重复 * @param tableName * 表名 * @param columns * 要返回的列 * @param selection * 条件 * @param selectionArgs * 条件中“？”的参数值 * @param groupBy * 分组 * @param having * 分组过滤条件 * @param orderBy * 排序 * @return * @author SHANHY */ public Cursor findList(boolean distinct, String tableName, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) { return mDb.query(distinct, tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit); } /** * 取得单行记录 * * @param tableName * 表名 * @param columns * 获取的列数组 * @param selection * 条件 * @param selectionArgs * 条件中“？”对应的值 * @param groupBy * 分组 * @param having * 分组条件 * @param orderBy * 排序 * @param limit * 数据区间 * @param distinct * 是否去重复 * @return * @throws SQLException * @author SHANHY */ public Cursor findOne(boolean distinct,String tableName, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) throws SQLException { Cursor mCursor = findList(distinct, tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit); if (mCursor != null) { mCursor.moveToFirst(); } return mCursor; } /** * 执行SQL(带参数) * * @param sql * @param args * SQL中“？”参数值 * @author SHANHY */ public void execSQL(String sql, Object[] args) { mDb.execSQL(sql, args); } /** * 执行SQL * * @param sql * @author SHANHY */ 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) { } return result; } /** * 判断某张表中是否存在某字段(注，该方法无法判断表是否存在，因此应与isTableExist一起使用) * * @param tabName * 表名 * @param columnName * 列名 * @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) { } return result; } }
测试类的代码：
package com.xiaoshan.udp.client.db; import android.content.ContentValues; import android.database.Cursor; import android.test.AndroidTestCase; /** * 单元测试操作sqlLite的各种sql * * @author 单红宇 */ 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.closeConnection(); } /** * 插入数据 * * @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.closeConnection(); } /** * 更新数据 * * @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 = ?", new String[] { "1" }); dbHelper.closeConnection(); } /** * 删除数据 * * @throws Exception */ public void delete() throws Exception { DBHelper dbHelper = new DBHelper(this.getContext()); dbHelper.open(); dbHelper.delete("user", "id =?'", new String[] { "1" }); dbHelper.closeConnection(); } /** * 增加字段 * * @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); dbHelper.closeConnection(); } /** * 查询列表 * * @throws Exception */ public void selectList() throws Exception { DBHelper dbHelper = new DBHelper(this.getContext()); dbHelper.open(); Cursor returnCursor = dbHelper.findList(false, "user", new String[] { "id", "username", "password" }, "username?", new String[] { "test" }, null, null, "id desc", null); while (returnCursor.moveToNext()) { String id = returnCursor.getString(returnCursor.getColumnIndexOrThrow("id")); String username = returnCursor.getString(returnCursor.getColumnIndexOrThrow("username")); String password = returnCursor.getString(returnCursor.getColumnIndexOrThrow("password")); System.out.println("id=" + id + ";username=" + username + ";" + password + ";\n"); } dbHelper.closeConnection(); } /** * 某一条信息 * * @throws Exception */ public void selectInfo() throws Exception { DBHelper dbHelper = new DBHelper(this.getContext()); dbHelper.open(); Cursor returnCursor = dbHelper.findOne(false,"user", new String[] { "id", "username", "password" }, "id = '1'", null, null, null, "id desc",null); if (returnCursor != null) { String id = returnCursor.getString(returnCursor.getColumnIndexOrThrow("id")); String username = returnCursor.getString(returnCursor.getColumnIndexOrThrow("username")); String password = returnCursor.getString(returnCursor.getColumnIndexOrThrow("password")); System.out.println("id=" + id + ";username=" + username + ";" + password + ";\n"); } } }
另外，sqllite数据库的db文件可以直接使用工具查看，具体工具是SQLiteSpy
如上代码和SQLiteSpy.exe查看下载地址为：http://download.csdn.net/detail/catoop/4319241
