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 userUser user = new User();user.setAge(100);user.setName("Jon Doe");UserDAO dao = new UserDAO();dao.insert(user);// insert user listUserDAO 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 usersUserDAO dao = new UserDAO();List<User> listFromDB = dao.selectAll();// select all users where age=18List<User> listFromDB = dao.selectByAge(18);