Android数据库(SqlLite)操作和db文件查看

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