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 findAllUser() {        List 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});}

更多相关文章

随机推荐