276
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);
}
@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(";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(";username=" + username + ";" + password + ";\n");
}
}
}
另外，sqllite數據庫的db文件可以直接使用工具查看，具體工具是SQLiteSpy
如上代碼和SQLiteSpy.exe查看下載地址為：https://download.csdn.net/detail/catoop/4319241
最後更新：2017-04-02 17:28:39