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++驱动概述