Android SQLite使用详解和多线程并发访问

Android中数据持久化技术包括文件存储、SharedPreferences以及数据库存储,对于大量复杂的关系型数据,数据库无疑是最合适的选择。

SQLite是一个轻量级的关系型数据库,运算速度快,占用资源少,适合在移动设备上使用。SQLite不仅支持SQL语法,还遵循数据库的ACID事务,使得本地持久化产生了质的飞跃。

首先我们创建类继承SQliteOpenHelper抽象类,重写onCreate和onUpgrade方法实现数据库的创建和升级。

这里为了方便多线程并发访问数据库,将类设计为单例模式。

package com.sdu.runningsdu.Utils;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Build;
import android.util.Log;

/**
 * Created by FTDsm on 2018/6/4.
 */

public class DatabaseHelper extends SQLiteOpenHelper {

    private static String DB_NAME = "xxx.db";
    private static final int DB_VERSION = 1;
    private static DatabaseHelper databaseHelper;

    public DatabaseHelper(Context context, String name) {
        super(context, name, null, DB_VERSION);
        if(Build.VERSION.SDK_INT >= 11){
            getWritableDatabase().enableWriteAheadLogging();
        }
    }

    /**
     * 单例模式
     * @param context
     * @param name
     * @return DatabaseHelper
     */
    public static synchronized DatabaseHelper getInstance(Context context, String name) {
        if (databaseHelper == null) {
            databaseHelper = new DatabaseHelper(context, name);
        }
        return databaseHelper;
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String userSQL = "create table if not exists user " +
                "(sid varchar(20) primary key, " +
                "name varchar(255), " +
                "password varchar(255), " +
                "imagePath varchar(255), " +
                "image blob)";
        sqLiteDatabase.execSQL(userSQL);
        Log.d("database", "create table user");

        String friendSQL = "create table if not exists friend " +
                "(sid varchar(20) primary key, " +
                "name varchar(255), " +
                "imagePath varchar(255), " +
                "unread integer, " +
                "image blob)";
        sqLiteDatabase.execSQL(friendSQL);
        Log.d("database", "create table friend");

        String groupSQL = "create table if not exists groups " +
                "(gid integer primary key, " +
                "name varchar(255), " +
                "creator varchar(255), " +
                "imagePath varchar(255), " +
                "unread varchar(255), " +
                "image blob)";
        sqLiteDatabase.execSQL(groupSQL);
        Log.d("database", "create table groups");

        String groupMemberSQL = "create table if not exists groupmember " +
                "(gid integer, " +
                "sid varchar(20)," +
                "primary key(gid, sid) )";
        sqLiteDatabase.execSQL(groupMemberSQL);
        Log.d("database", "create table groupmember");

        String friendMessageSQL = "create table if not exists friendmessage " +
                "(mid integer primary key, " +
                "sid varchar(20), " +
                "type integer, " +  /* 0接收 1发送 */
                "content varchar(255), " +
                "time timestamp)";
        sqLiteDatabase.execSQL(friendMessageSQL);
        Log.d("database", "create table friendmessage");

        String groupMessageSQL = "create table if not exists groupmessage " +
                "(mid integer primary key, " +
                "gid integer, " +
                "sid varchar(20), " +
                "type integer, " +
                "content varchar(255), " +
                "time timestamp)";
        sqLiteDatabase.execSQL(groupMessageSQL);
        Log.d("database", "create table groupmessage");

        String requestSQL = "create table if not exists request " +
                "(rid integer primary key, " +
                "receiver varchar(20), " +
                "sender varchar(20), " +
                "message varchar(255), " +
                "time timestamp, " +
                "state integer)";
        sqLiteDatabase.execSQL(requestSQL);
        Log.d("database", "create table request");
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        String userSQL = "drop table if exists user";
        sqLiteDatabase.execSQL(userSQL);
        String friendSQL = "drop table if exists friend";
        sqLiteDatabase.execSQL(friendSQL);
        String groupSQL = "drop table if exists group";
        sqLiteDatabase.execSQL(groupSQL);
        String groupMemberSQL = "drop table if exists groupmember";
        sqLiteDatabase.execSQL(groupMemberSQL);
        String friendMessageSQL = "drop table if exists friendmessage";
        sqLiteDatabase.execSQL(friendMessageSQL);
        String groupMessageSQL = "drop table if exists groupmessage";
        sqLiteDatabase.execSQL(groupMessageSQL);
        String requestSQL = "drop table if exists request";
        sqLiteDatabase.execSQL(requestSQL);
        onCreate(sqLiteDatabase);
    }

    @Override
    public synchronized void close() {
        super.close();
    }
}

然后我们创建Database Access Object类,完成数据增删改查等操作,借助SQLiteOpenHelper的getReadableDatabase()和getWritableDatabase()方法获取对数据库可读或可写操作对象。为了确保数据库可并发访问,有两种解决方案,一是以单例模式实例化SQLiteOpenHelper,每次使用完不执行db.close(),以免另一个操作正在进行时关闭数据库导致操作失败,二是每次实例化新的SQLiteOpenHelper,并在使用结束后调用db.close(),这里推荐第一种,性能更高且节省资源。

这里以user为例,介绍数据库的增删改查操作。

package com.sdu.runningsdu.Utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.sdu.runningsdu.JavaBean.Friend;
import com.sdu.runningsdu.JavaBean.Group;
import com.sdu.runningsdu.JavaBean.Message;
import com.sdu.runningsdu.JavaBean.Request;
import com.sdu.runningsdu.JavaBean.User;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by FTDsm on 2018/6/4.
 */

public class MyDAO {

    private Context context;

    private String name;

    private DatabaseHelper databaseHelper;

    public MyDAO(Context context, String name) {
        this.context = context;
        this.name = name;
        this.databaseHelper = DatabaseHelper.getInstance(context, name);
        Log.d("database", "database name: " + databaseHelper.getDatabaseName());
    }

    /**
     * 查询所有表格
     */
    public void findTable() {
        SQLiteDatabase db = this.databaseHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select name from sqlite_master where type='table' order by name", null);
        Log.d("database", "table:\n");
        while (cursor.moveToNext()) {
            Log.d("database", cursor.getString(0));
        }
        cursor.close();
    }

    /**
     * 添加用户
     * @param user User对象
     */
    public void addUser(User user) {
        SQLiteDatabase db = this.databaseHelper.getWritableDatabase();
        Object[] objects = new Object[3];
        objects[0] = user.getSid();
        objects[1] = user.getName();
        objects[2] = user.getPassword();
        String sql = "insert into user(sid, name, password) values(?,?,?)";
        db.execSQL(sql, objects);
        Log.d("database", "add user: " + user.getName());
    }

    /**
     * 删除用户
     * @param sid 用户id
     */
    public void deleteUser(String sid) {
        SQLiteDatabase db = this.databaseHelper.getWritableDatabase();
        String sql = "delete from user where sid = ?";
        db.execSQL(sql, new Object[]{sid});
        Log.d("database", "delete user: " + sid);
    }

    /**
     * 更新用户信息
     * @param user User对象
     */
    public void updateUser(User user) {
        SQLiteDatabase db = this.databaseHelper.getWritableDatabase();
        Object[] objects = new Object[3];
        objects[0] = user.getName();
        objects[1] = user.getPassword();
        objects[2] = user.getSid();
        String sql = "update user set name=?, password=? where sid=?";
        db.execSQL(sql, objects);
        Log.d("database", "update user: " + user.getName());
    }

    /**
     * 查询是否有用户
     * @return 当前是否存在用户
     */
    public boolean hasUser() {
        SQLiteDatabase db = this.databaseHelper.getReadableDatabase();
        Cursor cursor = db.query("user",
                null, null, null, null, null, null);
        Log.w("has user", ""+cursor.getCount());
        if (cursor.getCount() > 0) {
            return true;
        }
        cursor.close();
        return false;
    }

    /**
     * 查找用户信息
     * @param sid 用户id
     * @return User对象
     */
    public User findUser(String sid) {
        User user = new User();
        SQLiteDatabase db = this.databaseHelper.getReadableDatabase();
        Cursor cursor = db.query("user",
                new String[]{"sid", "name", "password", "imagePath"},
                "sid = ?",
                new String[]{sid},
                null, null, null);
        if (cursor.moveToNext()) {
            user.setSid(cursor.getString(cursor.getColumnIndex("sid")));
            user.setName(cursor.getString(cursor.getColumnIndex("name")));
            user.setPassword(cursor.getString(cursor.getColumnIndex("password")));
            user.setImagePath(cursor.getString(cursor.getColumnIndex("imagePath")));
        }
        Log.d("database", "find user: " + user.toString());
        cursor.close();
        return user;
    }

    /**
     * 查询所有用户
     * @return User列表
     */
    public List<User> findAllUser() {
        List<User> users = new ArrayList<>();
        SQLiteDatabase db = this.databaseHelper.getReadableDatabase();
        Cursor cursor = db.query("user",
                new String[]{"sid", "name", "password", "imagePath"},
                null, null, null, null, null);
        while (cursor.moveToNext()) {
            User user = new User();
            user.setSid(cursor.getString(cursor.getColumnIndex("sid")));
            user.setName(cursor.getString(cursor.getColumnIndex("name")));
            user.setPassword(cursor.getString(cursor.getColumnIndex("password")));
            user.setImagePath(cursor.getString(cursor.getColumnIndex("imagePath")));
            Log.d("database", "find user: " + user.toString());
            users.add(user);
        }
        cursor.close();
        return users;
    }
}

正如groupmember表中所定义,SQLite联合主键采用如下写法:

String sql= "create table if not exists groupmember " +
        "(gid integer, " +
        "sid varchar(20)," +
        "primary key(gid, sid) )";
sqLiteDatabase.execSQL(sql);

SQLite允许存储varchar、char、integer、real、text、blob、date、time等类型,存储图片通常使用blob存储byte数组

/**
 * 更新用户头像
 * @param sid
 * @param image
 */
public void updateUserImage(String sid, String imagePath, byte[] image) {
    SQLiteDatabase db = this.databaseHelper.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put("imagePath", imagePath);
    cv.put("image", image);
    db.update("user", cv, "sid = ?", new String[]{sid});
}

发布了56 篇原创文章 · 获赞 115 · 访问量 22万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 技术黑板 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览