AndroidDatabase

Code related to Android SQLite Database

ArrayUtils.java

public class ArrayUtils {

    public static String[] build(Object... values) {
        String[] arr = new String[values.length];
        for (int i = 0; i < values.length; i++) {
            arr[i] = String.valueOf(values[i]);
        }
        return arr;
    }
}

CursorParser.java

public class CursorParser {

    private int mIndex;
    private Cursor mCursor;

    public CursorParser(Cursor cursor) {
        mCursor = cursor;
        mIndex = -1;
    }

    public float readFloat() {
        mIndex++;
        return mCursor.getFloat(mIndex);
    }

    public double readDouble() {
        mIndex++;
        return mCursor.getDouble(mIndex);
    }

    public long readLong() {
        mIndex++;
        return mCursor.getLong(mIndex);
    }

    public String readString() {
        mIndex++;
        return mCursor.getString(mIndex);
    }

    public boolean readBoolean() {
        mIndex++;
        return mCursor.getInt(mIndex) != 0;
    }
}

DatabaseConnection.java

public class DatabaseConnection {

    private int mOpenCounter;

    private static DatabaseConnection sInstance;
    private SQLiteOpenHelper mDatabaseHelper;
    private SQLiteDatabase mDatabase;

    private DatabaseConnection(SQLiteOpenHelper helper) {
        mDatabaseHelper = helper;
    }

    public static synchronized void initializeInstance(SQLiteOpenHelper helper) {
        if (sInstance == null) {
            sInstance = new DatabaseConnection(helper);
        }
    }

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

        return sInstance;
    }

    synchronized SQLiteDatabase open() {
        if (mOpenCounter == 0) {
            // Opening new database
            mDatabase = mDatabaseHelper.getWritableDatabase();
        }
        mOpenCounter++;
        L.d("Database open counter: " + mOpenCounter);
        return mDatabase;
    }

    synchronized void close() {
        mOpenCounter--;
        if (mOpenCounter == 0) {
            // Closing database
            mDatabaseHelper.close();

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

}

DatabaseHelper.java

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

DatabaseTest.java

public class DatabaseTest extends AndroidTestCase {

    @Override
    protected void setUp() throws Exception {
        super.setUp();

        L.LOG_TAG = "database";
        DatabaseConnection.initializeInstance(new DatabaseHelper(getContext()));

        L.v("Deleting all users");
        DatabaseConnection connection = DatabaseConnection.instance();
        SQLiteDatabase database = connection.open();
        UserDAO dao = new UserDAO(database, getContext());
        dao.deleteAll();
        connection.close();
    }

    public void testInsertUserList() {
        L.v("testInsertUserList");

        DatabaseConnection connection = DatabaseConnection.instance();
        SQLiteDatabase database = connection.open();

        UserDAO dao = new UserDAO(database, getContext());
        dao.insert(generateDummyUserList(10));

        List<User> listFromDB = dao.selectAll();
        Assert.assertTrue("User list is empty", !listFromDB.isEmpty());
        Assert.assertTrue("User list size is wrong", listFromDB.size() == 10);

        connection.close();
    }

    public void testInsertUser() {
        L.v("testInsertUser");

        DatabaseConnection connection = DatabaseConnection.instance();
        SQLiteDatabase database = connection.open();

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

        UserDAO dao = new UserDAO(database, getContext());
        dao.insert(user);

        List<User> listFromDB = dao.selectAll();
        Assert.assertTrue("User list is empty", !listFromDB.isEmpty());
        Assert.assertTrue("User list size is wrong", listFromDB.size() == 1);

        User userFromDB = listFromDB.get(0);

        Assert.assertTrue("Incorrect data",
                user.getName().contentEquals(userFromDB.getName()));
        Assert.assertTrue("Incorrect data", user.getAge() == userFromDB.getAge());

        connection.close();
    }

    public void testUpdateUser() {
        L.v("testUpdateUser");

        DatabaseConnection connection = DatabaseConnection.instance();
        SQLiteDatabase database = connection.open();

        UserDAO dao = new UserDAO(database, getContext());
        User user = new User();
        user.setAge(18);
        user.setName("Jon Doe");

        dao.insert(user);

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

        List<User> listFromDB = dao.selectByAge(18);
        Assert.assertTrue("User list is empty", !listFromDB.isEmpty());

        User userFromDB = listFromDB.get(0);

        Assert.assertTrue("User is null", userFromDB != null);
        Assert.assertTrue("User age is wrong", userFromDB.getAge() == 18);
        Assert.assertTrue("User name is wrong", userFromDB.getName().equals("Will Smith"));

        connection.close();
    }

    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;
    }

    private int totalTasks = 100;
    private AtomicInteger tasksAlive = new AtomicInteger(totalTasks);

    public void testConcurrentAccess() {
        L.v("testConcurrentAccess");

        CountDownLatch signal = new CountDownLatch(1);

        for (int i = 0; i < totalTasks; i++) {
            spamNewThread(signal);
        }

        try {
            signal.await();
        } catch (InterruptedException e) {
            L.d(e.getMessage(), e);
        }
    }

    private void spamNewThread(final CountDownLatch signal) {
        new Thread(new Runnable() {
            @Override
            public void run() {
                DatabaseConnection connection = DatabaseConnection.instance();
                SQLiteDatabase database = connection.open();

                UserDAO dao = new UserDAO(database, getContext());
                int usersCount = 10;
                dao.insert(generateDummyUserList(usersCount));

                L.v("Task #" + tasksAlive.get() + " is finished");

                boolean allTasksFinished = tasksAlive.decrementAndGet() == 0;
                if (allTasksFinished) {
                    int totalUsers = usersCount * totalTasks;
                    List<User> listFromDB = dao.selectAll();
                    Assert.assertTrue("User list is empty", !listFromDB.isEmpty());
                    Assert.assertTrue("User list size is wrong", listFromDB.size() == totalUsers);
                    signal.countDown();
                }

                connection.close();
            }
        }).start();
    }

}

queries.xml

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

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

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

    <!--language=SQLite-->
    <string name="select_users_by_age">
        SELECT
            id,
            age,
            name
        FROM users
        WHERE age = ?;
    </string>

    <!--language=SQLite-->
    <string name="select_all_users">
        SELECT
            id,
            age,
            name
        FROM users;
    </string>

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

    <!--language=SQLite-->
    <string name="delete_all_users">
        DELETE FROM 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>

User.java

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.java

public class UserDAO {

    private SQLiteDatabase mDatabase;
    private Context mContext;

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

    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);
    }

    public void deleteAll() {
        mDatabase.execSQL(mContext.getString(R.string.delete_all_users));
    }

    public void insert(List<User> userList) {
        String sql = mContext.getString(R.string.insert_user);
        String[] bindArgs;
        for (User user : userList) {
            bindArgs = ArrayUtils.build(user.getName(), user.getAge());
            mDatabase.execSQL(sql, bindArgs);
        }
    }

    public void insert(User user) {
        String[] bindArgs = ArrayUtils.build(user.getName(), user.getAge());
        mDatabase.execSQL(mContext.getString(R.string.insert_user), bindArgs);
    }

    public void updateNameByAge(String name, int age) {
        String[] bindArgs = ArrayUtils.build(name, age);
        mDatabase.execSQL(mContext.getString(R.string.update_user_name_by_age), bindArgs);
    }

    public List<User> selectByAge(int age) {
        String[] selectionArgs = ArrayUtils.build(age);
        String query = mContext.getString(R.string.select_users_by_age);
        Cursor cursor = mDatabase.rawQuery(query, selectionArgs);

        List<User> dataList = manageCursor(cursor);

        closeCursor(cursor);

        return dataList;
    }

    public List<User> selectAll() {
        Cursor cursor = mDatabase.rawQuery(mContext.getString(R.string.select_all_users), null);
        List<User> dataList = manageCursor(cursor);
        closeCursor(cursor);

        return dataList;
    }

    protected User cursorToData(Cursor cursor) {
        CursorParser parser = new CursorParser(cursor);

        User user = new User();
        user.setId(parser.readLong());
        user.setAge(parser.readInt());
        user.setName(parser.readString());

        return user;
    }

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

    protected List<User> manageCursor(Cursor cursor) {
        List<User> dataList = new ArrayList<User>();

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