Android之Sqlite的增删查操作

今天寫了個sqlite CRUD的操作執行個體，這裡做個筆記。android的存儲方式有很多有sharedpreferenced、file、xml、sqlite等。

sqlite的介紹網絡上實在是多，俺還是直接代碼。對使用者的注冊情況進行CRUD操作。

1、首先建立個DBHelper繼承SQLiteOpenHelper

package com.xzw.db.utils;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.database.sqlite.SQLiteOpenHelper;

/**

* 該類是一個資料庫的幫助類，用于建立資料庫

* @author xzw

public class DBHelper extends SQLiteOpenHelper {

private static final String TAG = "DBHelper";

private String dbName;

private String tableName;

private String createTableSql;

/**

* @param context 上下文

* @param dbName 資料庫名

* @param version 資料庫版本号

* @param tableName 表名稱

* @param createTableSql 建表Sql

public DBHelper(Context context, String dbName,int version,String tableName,String createTableSql) {

super(context, dbName, null, version);

this.dbName = dbName;

this.tableName = tableName;

this.createTableSql = createTableSql;

}

@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL(createTableSql);

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

String sql = "DROP TABLE IF EXISTS "+tableName;

db.execSQL(sql);

onCreate(db);

}

2、建立User對象

package com.xzw.db.bean;

public class User {

private Integer userid;

private String userName;

private String password;

private String sex;

private long regTime;

public User() {

public User(String userName, String password, String sex, long regTime) {

this.userName = userName;

this.password = password;

this.sex = sex;

this.regTime = regTime;

public User(Integer userid, String userName, String password, String sex,

long regTime) {

this.userid = userid;

public Integer getUserid() {

return userid;

public void setUserid(Integer userid) {

public String getUserName() {

return userName;

public void setUserName(String userName) {

public String getPassword() {

return password;

public void setPassword(String password) {

public String getSex() {

return sex;

public void setSex(String sex) {

public long getRegTime() {

return regTime;

public void setRegTime(long regTime) {

public String toString() {

// TODO Auto-generated method stub

return "[User]userid = " + userid + " ,userName = " + userName

+ ", password = " + password + ", sex = " + sex + ",regTime = "

+ regTime;

3、建立UserStorage進行crud處理。

import java.util.ArrayList;

import java.util.List;

import com.xzw.db.bean.User;

import android.content.ContentValues;

import android.database.Cursor;

import android.util.Log;

public class UserStorage {

private static final String TAG = "UserStorage";

private static final String DB_NAME = "user_data.db";

public static final String TABLE_NAME = "user_data";

private static final int DB_VERSION = 1;

static final String COLUM_ID = "_id";

static final String COLUM_USER_NAME = "username";

static final String COLUM_PASSWORD = "password";

static final String COLUM_SEX = "sex";

static final String COLUM_REG_TIME = "reg_time";

private String mCreateTableSql = "create table if not exists " + TABLE_NAME

+ "("+COLUM_ID+" integer primary key autoincrement,"

+COLUM_USER_NAME + " text not null,"

+COLUM_PASSWORD + " text not null,"

+COLUM_SEX + " text,"

+COLUM_REG_TIME + " bigint not null"

+")";

private DBHelper dbHelper;

public UserStorage(Context context){

dbHelper = new DBHelper(context, DB_NAME, DB_VERSION, TABLE_NAME, mCreateTableSql);

* 插入資料

* @param cv

* @return

public long insert(ContentValues cv){

//以寫的方式建立或打開資料庫

SQLiteDatabase db = dbHelper.getWritableDatabase();

long result = db.insert(TABLE_NAME, null, cv);

db.close(); //操作完成記得關閉資料庫

if(result > 0 ){

Log.i(TAG, "[Insert] success:" + result);

}else{

Log.e(TAG, "Failed to insert into " + TABLE_NAME);

}

return result;

* 批量插入多條資料

* @param cvs

public int insert(List<ContentValues> cvs){

int count = 0;

for(ContentValues cv : cvs){

long id = insert(cv,db);

count += id>0?1:0;

db.close();

return count;

* @param db

private long insert(ContentValues cv,SQLiteDatabase db){

if(result > 0 ){

}else{

}

/**

* 根據id更新記錄

* @param cv

* @param id

* @return

public int updateUserById(ContentValues cv,String id){

int result = db.update(TABLE_NAME, cv, "_id=?", new String[]{id});

Log.i(TAG, "[Update] success:" + result);

}else{

Log.e(TAG, "Failed to upate " + TABLE_NAME);

}

* 擷取所有列

public String[] getColumns(){

return new String[]{COLUM_ID,COLUM_USER_NAME,COLUM_PASSWORD,COLUM_SEX,COLUM_REG_TIME};

public List<User> getAllUser(){

List<User> users = new ArrayList<User>();

SQLiteDatabase db = dbHelper.getReadableDatabase();

Cursor cursor = db.query(TABLE_NAME, getColumns(), null, null, null, null, COLUM_ID);

while(cursor.moveToNext()){

User user = fillUser(cursor);

//添加user到集合中

users.add(user);

cursor.close(); //遊标使用完成後記得關閉

return users;

private User fillUser(Cursor cursor) {

User user = new User();

user.setUserid(cursor.getInt(cursor.getColumnIndex(COLUM_ID)));

user.setUserName(getValueByColumnName(cursor,COLUM_USER_NAME));

user.setPassword(getValueByColumnName(cursor,COLUM_PASSWORD));

user.setSex(getValueByColumnName(cursor,COLUM_SEX));

user.setRegTime(cursor.getLong(cursor.getColumnIndex(COLUM_REG_TIME)));

return user;

* 根據ID擷取資料

* @param id

public User getUserById(String id){

String where = "_id=?";

String[] selectionArgs = new String[]{id};

Cursor cursor = db.query(TABLE_NAME, getColumns(), where, selectionArgs, null, null, null);

User user = null;

if(cursor.moveToFirst()){

user = fillUser(cursor);

* 根據id删除記錄

public int deleteById(String id){

int result = db.delete(TABLE_NAME, "_id=?", new String[]{id});

Log.i(TAG, "[Delete] success:" + result);

Log.e(TAG, "Failed to delete from " + TABLE_NAME);

//擷取字段值

private String getValueByColumnName(Cursor cursor,String columnName){

return cursor.getString(cursor.getColumnIndex(columnName));

* 清空表資料

public void clear(){

db.delete(TABLE_NAME, null, null);

4、編寫UserService進行業務處理。

import android.os.storage.StorageManager;

public class UserService {

private UserStorage userStorage;

public UserService(Context context){

userStorage = new UserStorage(context);

* 儲存使用者

* @param user

public long saveUser(User user){

ContentValues cv = fillContentValues(user);

long count = userStorage.insert(cv);

* 批量儲存使用者

* @param users

public long saveUser(List<User> users){

List<ContentValues> contentValues = new ArrayList<ContentValues>();

for(User user:users){

ContentValues cv = fillContentValues(user);

contentValues.add(cv);

long count = userStorage.insert(contentValues);

* 根據id修改資料

public int updateUserById(User user){

ContentValues contentValues = fillContentValues(user);

return userStorage.updateUserById(contentValues, user.getUserid().toString());

* 擷取所有使用者資訊

return userStorage.getAllUser();

* 根據id擷取使用者資訊

return userStorage.getUserById(id);

* 根據id删除資料

return userStorage.deleteById(id);

}

* 删除所有資料

public void deleteAll(){

userStorage.clear();

* 填充資料

private ContentValues fillContentValues(User user) {

//以下代碼比較多地方會用帶，故将其抽象成為方法。也是一種優化手段

ContentValues cv = new ContentValues();

cv.put(UserStorage.COLUM_USER_NAME, user.getUserName());

cv.put(UserStorage.COLUM_PASSWORD, user.getPassword());

cv.put(UserStorage.COLUM_SEX, user.getSex());

cv.put(UserStorage.COLUM_REG_TIME, user.getRegTime());

return cv;

以上是整個業務流程了。現在使用AndroidTestCase進行測試

建立TestUser繼承AndroidTestCase

package com.xzw.db;

import com.xzw.db.utils.UserService;

import com.xzw.db.utils.UserStorage;

import android.test.AndroidTestCase;

public class TestUser extends AndroidTestCase {

private static final String TAG = "TestUser";

* 測試添加使用者

public void testSave(){

UserService userService = new UserService(this.getContext());

User user = new User("xzw", "123456", "男", System.currentTimeMillis());

userService.saveUser(user);

* 測試批量添加使用者

public void testBatchSave(){

for(int i=0;i<10;i++){

User user = new User("hh-"+i, "123456", (i%2==0)?"男":"女", System.currentTimeMillis());

userService.saveUser(users);

public void testGetAllUser(){

List<User> users =userService.getAllUser();

for(User user : users){

Log.i(TAG, user.toString());

public void testGetUserById(){

User user =userService.getUserById("2");

Log.i(TAG, user.toString());

public void testUpdateUser(){

User user = userService.getUserById("2");

user.setUserName("測試使用者");

userService.updateUserById(user);

public void testDeleteById(){

userService.deleteById("1");

代碼全部編寫完畢，運作前需要在AndroidManifest.xml檔案添加

<manifest xmlns:android="http://schemas.android.com/apk/res/android"

package="com.xzw.db"

android:versionCode="1"

android:versionName="1.0" >

<uses-sdk

android:minSdkVersion="8"

android:targetSdkVersion="15" />

<application

android:icon="@drawable/ic_launcher"

android:label="@string/app_name"

android:theme="@style/AppTheme" >

<uses-library android:name="android.test.runner"/>

<activity

android:name=".MainActivity"

android:label="@string/title_activity_main" >

<intent-filter>

</intent-filter>

</activity>

</application>

<instrumentation android:name="android.test.InstrumentationTestRunner"

android:targetPackage="com.xzw.db" android:label="Tests for My App"/>

</manifest>

以上就是整個sqlite crud的操作了。

查詢所有使用者資訊：

根據id進行查詢。

update後查詢的結果。

以上就是完整的過程了。

本文轉自xuzw13 51CTO部落格，原文連結：http://blog.51cto.com/xuzhiwei/1054965，如需轉載請自行聯系原作者

Android之Sqlite的增删查操作

繼續閱讀

cs231n斯坦福基于卷積神經網絡的CV學習筆記（一）KNN和線性分類器/分類器損失/反向傳播一，KNN圖像分類算法二，線性分類器三，線性分類器損失四，反向傳播五，神經網絡

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

Android電視機（機頂盒）初次開發的一些經驗分享

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述