Android原生数据库模型

原生数据库模型

作者：Dmytro Danylyk，Google Android开发专家，原文链接https://github.com/dmytrodanylyk/dmytrodanylyk/blob/gh-pages/articles/Raw%20database%20model.md
我想展示Android”原生数据库模型”，基本概念比较简单-使用存储在 res/values/queries.xml的原生语句

基础类包

com.sample.db.model

首先，从基础类开始，这些基础类能够在不同的工程中使用
DatabaseManager-是一个单例，含有字段DatabaseProxy
功能
1. 访问DatabaseProxy对象
2. 提供openDatabase和closeDatabase方法来处理数据库的并发访问问题

   public class DatabaseManager {
        private AtomicInteger mOpenCounter = new AtomicInteger();

        private static DatabaseManager instance;
        private static SQLiteOpenHelper mDatabaseHelper;
        private static Context mContext;

        private DatabaseProxy mDatabaseProxy;

        public static synchronized void initializeInstance(SQLiteOpenHelper helper, Context context) {
            if (instance == null) {
                instance = new DatabaseManager();
                mDatabaseHelper = helper;
                mContext = context.getApplicationContext();
            }
        }

        public static synchronized DatabaseManager getInstance() {
            if (instance == null) {
                throw new IllegalStateException(DatabaseManager.class.getSimpleName() +
                        " is not initialized, call initializeInstance(..) method first.");
            }

            return instance;
        }

        public DatabaseProxy openDatabase() {
            if (mOpenCounter.incrementAndGet() == 1) {
                mDatabaseProxy = new DatabaseProxy(mDatabaseHelper.getWritableDatabase(), mContext);
            }
            L.d("Database open counter: " + mOpenCounter.get());
            return mDatabaseProxy;
        }

        public void closeDatabase() {
            if (mOpenCounter.decrementAndGet() == 0) {
                mDatabaseProxy.close();
            }

            L.d("Database open counter: " + mOpenCounter.get());
        }
    }

DatabaseProxy-是SQLiteDatabase类包装类，具构造器只有包访问权限(缺省)，所以可以防止从外部创建DatabaseProxy类的对象。其实这里就是设计模式中的代理模式-静态代理模式
功能
1. 提供方法执行原生语句，例如：rawQuery,execSQL
2. 提供具有包访问权限的close方法来关闭数据库，同样，包访问权限可以防止从外部关闭数据库

public class DatabaseProxy {

    private SQLiteDatabase mDatabase;
    private Context mContext;

    DatabaseProxy(SQLiteDatabase database, Context context) {
        mDatabase = database;
        mContext = context;
    }

    void close() {
        mDatabase.close();
    }

    public void transactionSuccessful() {
        mDatabase.setTransactionSuccessful();
    }

    public void transactionBegin() {
        mDatabase.beginTransaction();
    }

    public void transactionEnd() {
        mDatabase.endTransaction();
    }

    public Cursor rawQuery(int sql) {
        return rawQuery(sql, null);
    }

    public Cursor rawQuery(int sql, String[] selectionArgs) {
        return mDatabase.rawQuery(mContext.getString(sql), selectionArgs);
    }

    public void execSQL(int sql) {
        mDatabase.execSQL(mContext.getString(sql));
    }

    public void execSQL(int sql, String[] bindArgs) {
        mDatabase.execSQL(mContext.getString(sql), bindArgs);
    }
}

AbstractDAO-不一定要是使用了泛型的抽象类，所有的DAO类都将继承她
功能
1.提供简化了的函数openDatabase和closeDatabase
2.提供辅助函数，例如manageCursor,closeCursor

public abstract class AbstractDAO<T> {

    protected DatabaseProxy openDatabase() {
        return DatabaseManager.getInstance().openDatabase();
    }

    protected void closeDatabase() {
        DatabaseManager.getInstance().closeDatabase();
    }

    protected void closeCursor(@Nullable Cursor cursor) {
        if (cursor != null) {
            cursor.close();
        }
    }

    @NotNull
    protected List<T> manageCursor(Cursor cursor) {
        List<T> dataList = new ArrayList<T>();

        if (cursor != null) {
            cursor.moveToFirst();
            while (!cursor.isAfterLast()) {
                T user = cursorToData(cursor);
                dataList.add(user);
                cursor.moveToNext();
            }
        }
        return dataList;
    }

    protected abstract T cursorToData(Cursor cursor);
}

具体的类

com.sample.db.model.conrete
User-数据对象

public class User {

    private long mId;
    private int mAge;
    private String mName;

    public int getAge() {
        return mAge;
    }

    public void setAge(int age) {
        mAge = age;
    }

    public long getId() {
        return mId;
    }

    public void setId(long id) {
        mId = id;
    }

    public String getName() {
        return mName;
    }

    public void setName(String name) {
        mName = name;
    }
}

UserDAO和queries.xml的第一个版本将提供create和delete语句.
UserDAO(不完整)-访问User对象的对象(User对象的操作类)
功能
1.提供所有关于User对象的数据库操作，例如insert,select,delete

public class UserDAO extends AbstractDAO<User> {

    interface Table {

        String COLUMN_ID = "id";
        String COLUMN_NAME = "name";
        String COLUMN_AGE = "age";
    }

    public static String getCreateTable(Context context) {
        return context.getString(R.string.create_table_user);
    }

    public static String getDropTable(Context context) {
        return context.getString(R.string.drop_table_users);
    }

    @Override
    protected User cursorToData(Cursor cursor) {
        int idIndex = cursor.getColumnIndex(Table.COLUMN_ID);
        int nameIndex = cursor.getColumnIndex(Table.COLUMN_NAME);
        int ageIndex = cursor.getColumnIndex(Table.COLUMN_AGE);

        User user = new User();
        user.setId(cursor.getLong(idIndex));
        user.setAge(cursor.getInt(ageIndex));
        user.setName(cursor.getString(nameIndex));

        return user;
    }
}

queries.xml(不完整)-包含所有语句

<?xml version="1.0" encoding="utf-8"?>
<resources>

    <!--Language=SQLite-->
    <string name="drop_table_users">
        DROP TABLE IF EXISTS users;
    </string>

    <!--Language=SQLite-->
    <string name="create_table_user">
        CREATE TABLE IF NOT EXISTS users (
            id   INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            age  INTEGER
        );
    </string>
</resources>

DatabaseHelper辅助类，数据库的创建管理和数据库的版本管理

public class DatabaseHelper extends SQLiteOpenHelper {


    public static final String DATABASE_NAME = "sample_database";
    public static final int DATABASE_VERSION = 1;
    private Context mContext;

    public DatabaseHelper(@NotNull Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        // create all tables
        sqLiteDatabase.execSQL(UserDAO.getCreateTable(mContext));
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        if (newVersion > oldVersion) {
            // drop all tables
            sqLiteDatabase.execSQL(UserDAO.getDropTable(mContext));
            //re-create all tables
            onCreate(sqLiteDatabase);
        }
    }
}

将这些类联合起来

// 初始化，应该执行一次，放在application类中比较好
DatabaseManager.initializeInstance(new DatabaseHelper(getContext()), getContext());

现在我们需要添加语句到UserDAO和queries.xml中

删除语句

queries.xml

<!--Language=SQLite-->
<string name="delete_all_users">
    DELETE FROM users;
</string>

UserDAO类

public void deleteAll() {
    DatabaseProxy databaseProxy = openDatabase();
    databaseProxy.execSQL(R.string.delete_all_users);
    closeDatabase();
}

用法

UserDAO dao = new UserDAO();
dao.deleteAll();

插入语句

queries.xml

<!--Language=SQLite-->
<string name="insert_user">
    INSERT INTO users (name, age) VALUES (?, ?);
</string>

UserDAO类

public void insert(List<User> userList) {
    DatabaseProxy databaseProxy = openDatabase();

    for (User user : userList) {
        String[] bindArgs = {
                user.getName(),
                String.valueOf(user.getAge())
        };
        databaseProxy.execSQL(R.string.insert_user, bindArgs);
    }

    closeDatabase();
}

public void insert(User user) {
    DatabaseProxy databaseProxy = openDatabase();
    String[] bindArgs = {
            user.getName(),
            String.valueOf(user.getAge())
    };
    databaseProxy.execSQL(R.string.insert_user, bindArgs);
    closeDatabase();
}

用法

/ insert single user
User user = new User();
user.setAge(100);
user.setName("Jon Doe");

UserDAO dao = new UserDAO();
dao.insert(user);

// insert user list
UserDAO dao = new UserDAO();
dao.insert(generateDummyUserList(10));


private List<User> generateDummyUserList(int itemsCount) {
    List<User> userList = new ArrayList<User>();
    for (int i = 0; i < itemsCount; i++) {
        User user = new User();
        user.setAge(i);
        user.setName("Jon Doe");
        userList.add(user);
    }
    return userList;
}

更新语句

queries.xml

<!--Language=SQLite-->
<string name="update_user_name_by_age">
    UPDATE users SET name = ?
    WHERE age = ?;
</string>

UserDAO类

public void updateNameByAge(String name, int age) {
    DatabaseProxy databaseProxy = openDatabase();
    String[] bindArgs = {
            name,
            String.valueOf(age)
    };
    databaseProxy.execSQL(R.string.update_user_name_by_age, bindArgs);
    closeDatabase();
}

用法

User user = new User();
user.setAge(18);
user.setName("Jon Doe");

UserDAO dao = new UserDAO();
dao.insert(user);

dao.updateNameByAge("Will Smith", 18);

查询语句

queries.xml

<!--Language=SQLite-->
<string name="select_users_by_age">
    SELECT
        *
    FROM users
    WHERE age = ?;
</string>

<!--Language=SQLite-->
<string name="select_all_users">
    SELECT
        *
    FROM users;
</string>

UserDAO类

ublic List<User> selectByAge(int age) {
    DatabaseProxy databaseProxy = openDatabase();
    String[] selectionArgs = {
            String.valueOf(age)
    };
    Cursor cursor = databaseProxy.rawQuery(R.string.select_users_by_age, selectionArgs);

    List<User> dataList = manageCursor(cursor);

    closeCursor(cursor);
    closeDatabase();

    return dataList;
}

public List<User> selectAll() {
    DatabaseProxy databaseProxy = openDatabase();
    Cursor cursor = databaseProxy.rawQuery(R.string.select_all_users);

    List<User> dataList = manageCursor(cursor);

    closeCursor(cursor);
    closeDatabase();

    return dataList;
}

用法

// select all users
UserDAO dao = new UserDAO();
List<User> listFromDB = dao.selectAll();

// select all users where age=18
List<User> listFromDB = dao.selectByAge(18);