Android: example of working with SQLite database from /assets folder

DaoMaster.java

public abstract class DaoMaster<T extends Entity> {

    protected SQLiteDatabase mSQLiteDatabase;

    protected Context mContext;

    public DaoMaster(final Context context, final SQLiteDatabase sqLiteDatabase) {
        mSQLiteDatabase = sqLiteDatabase;
        mContext = context;
    }

    /**
     * Finds row in the table by corresponding "_id" value.
     *
     * @param id id value
     * @return entity that corresponds to passed id
     */
    public T findById(final Long id) {
        Cursor c = null;

        try {
            c = mSQLiteDatabase.query(getTableName(), null, "_id = ? ", new String[]{String.valueOf(id)}, null, null, null);

            return bind(c);
        } finally {
            if (c != null) {
                c.close();
            }
        }
    }

    /**
     * Finds all rows in the table.
     *
     * @return list of all entities
     */
    public List<T> findAll() {
        Cursor c = null;

        try {
            c = mSQLiteDatabase.query(getTableName(), null, "_id = ? ", null, null, null, null);

            return bindToList(c);
        } finally {
            if (c != null) {
                c.close();
            }
        }
    }

    /**
     * Update an entity on DB using his id as selection.
     *
     * @param entity Entity to update
     * @return true, if a row has been update
     */
    public boolean update(final T entity) {
        boolean updated = false;
        try {
            mSQLiteDatabase.beginTransaction();
            final int count = mSQLiteDatabase.update(getTableName(), bind(entity), "_id = ?", new String[]{ String.valueOf(entity.getId()) });
            updated = count > 0;
            mSQLiteDatabase.setTransactionSuccessful();
        } finally {
            mSQLiteDatabase.endTransaction();
        }
        return updated;
    }

    /**
     * Inserts an entity on the DB and adds inserted id to the entity.
     *
     * @param entity entity to insert
     * @return generated id for the entity, if insert is successful, -1 otherwise
     */
    public Long insert(final T entity) {
        long insertedId;
        try {
            mSQLiteDatabase.beginTransaction();
            insertedId = mSQLiteDatabase.insert(getTableName(), null, bind(entity));
            if (insertedId != -1) {
                mSQLiteDatabase.setTransactionSuccessful();
                entity.setId(insertedId);
            }
        } finally {
            mSQLiteDatabase.endTransaction();
        }

        return insertedId;
    }

    /**
     * Deletes all entities from the list of entities.
     *
     * @param entityList list of entities
     * @return true if all of them were deleted successfully, false otherwise
     */
    public boolean delete(final List<T> entityList) {
        boolean allDeleted = true;

        for (final T entity : entityList) {
            if (!delete(entity)) {
                allDeleted = false;
            }
        }

        return allDeleted;
    }

    /**
     * Delete an entity
     *
     * @param entity entity to delete
     * @return true, if the entity was in the DB
     */
    public boolean delete(final T entity) {
        return delete(entity.getId());
    }

    /**
     * Deletes the row from database by id.
     *
     * @param id _id field of the row to delete.
     * @return the number of rows affected if a whereClause is passed in, 0
     *         otherwise. To remove all rows and get a count pass "1" as the
     *         whereClause.
     */
    public boolean delete(final Long id) {
        boolean deleted = false;
        try {
            mSQLiteDatabase.beginTransaction();
            final int count = mSQLiteDatabase.delete(getTableName(), "_id = ?", new String[]{ String.valueOf(id) });
            deleted = count > 0;
            mSQLiteDatabase.setTransactionSuccessful();
        } finally {
            mSQLiteDatabase.endTransaction();
        }

        return deleted;
    }

    protected abstract String getTableName();

    protected abstract ContentValues bind(final T entity);

    protected abstract T bind(final Cursor c);

    protected List<T> bindToList(final Cursor c) {
        final List<T> entityList = new ArrayList<T>();

        while (c.moveToNext()) {
            entityList.add(bind(c));
        }

        return entityList;
    }
}

DbHelper.java

/**
 * SQLiteAssetsHelper class extension for accessing database from assets.
 */
public class DbHelper extends SQLiteAssetsHelper {

    private static DbHelper sInstance;

    private DbHelper(final Context context, final String dbName, final int dbVersion) {
        super(context, dbName, dbVersion);
    }

    @Override
    public void onCreate(final SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {
    }

    /**
     * Constructor that uses application context.
     */
    public static DbHelper getInstance() {
        if (sInstance == null) {
            sInstance = new DbHelper(Model.getInstance().getApplication(),
                    "MyDatabaseName", 1);
        }

        return sInstance;
    }
}

DbManager.java

public class DbManager {

    private static DbManager sInstance;

    private DbHelper mDatabaseHelper;

    private SQLiteDatabase mDatabase;

	private DbManager(final DbHelper helper) {
		mDatabaseHelper = helper;
	}

    public static synchronized void initialize(final DbHelper helper) {
        if (sInstance == null) {
            sInstance = new DbManager(helper);
        }
    }

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

        return sInstance;
    }
    
    public synchronized SQLiteDatabase getDatabase() throws SQLException, IOException {
	    if (mDatabase == null) {
	        mDatabaseHelper.createDatabase();
	        mDatabase = mDatabaseHelper.openDataBase();
	    }
    
    	return mDatabase;
    }
}

Entity.java

public interface Entity extends Serializable {

    public Long getId();

    public void setId(final Long id);
}

EntityAdapter.java

public abstract class EntityAdapter<T extends Entity> extends ArrayAdapter<T> {

    private LayoutInflater mInflater;

    private int mLayout;

    public EntityAdapter(final Context context, final int resource) {
        super(context, resource);
        mLayout = resource;
        mInflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
    }

    @Override
    public View getView(final int position, final View convertView, final ViewGroup parent) {
        final View view;

        if (convertView == null) {
            view = mInflater.inflate(getLayout(), parent, false);
        } else {
            view = convertView;
        }

        return view;
    }

    @Override
    public long getItemId(final int position) {
        final T item = getItem(position);

        return item.getId();
    }

    public int getLayout() {
        return mLayout;
    }

    public void setData(List<T> dataList) {
        clear();

        if (dataList != null && !dataList.isEmpty()) {
            for (final T data : dataList) {
                super.add(data);
            }
        }
    }

    public List<T> getData() {
        final List<T> items = new ArrayList<T>();

        for(int i = 0; i < getCount(); i++){
            items.add(getItem(i));
        }

        return items;
    }
}

NamedEntity.java

public interface NamedEntity extends Entity {

    public void setName(final String name);

    public String getName();
}

NamedEntityAdapter.java

public class NamedEntityAdapter extends EntityAdapter<NamedEntity> {

    public NamedEntityAdapter(final Context context) {
        super(context, android.R.layout.simple_list_item_1);
    }

    @Override
    public View getView(final int position, final View convertView, final ViewGroup parent) {
        final View view = super.getView(position, convertView, parent);

        final NamedEntity item = getItem(position);

        ((TextView) view.findViewById(android.R.id.text1)).setText(item.getName());

        return view;
    }

    @Override
    public View getDropDownView(final int position, final View convertView, final ViewGroup parent) {
        return getView(position, convertView, parent);
    }
}

SQLiteAssetsHelper.java

public abstract class SQLiteAssetsHelper extends SQLiteOpenHelper {

    protected final String mDbFullPath, mDbName;

    protected final Context mDbContext;

    protected final int mDbVersion;

    protected SQLiteAssetsHelper(final Context context, final String dbName, final int dbVersion) {
        super(context, dbName, null, dbVersion);
        mDbContext = context;
        mDbName = dbName;
        mDbVersion = dbVersion;
        mDbFullPath = mDbContext.getDatabasePath(mDbName).getPath();
    }

    public String getDbFullPath() {
        return mDbFullPath;
    }

    public String getDbName() {
        return mDbName;
    }

    public int getDbVersion() {
        return mDbVersion;
    }

    public Context getDbContext() {
        return mDbContext;
    }

    /**
     * Creates database if it does not exists.
     *
     * @throws IOException exception if copying database failed
     */
    public void createDatabase() throws IOException {
        final boolean dbExist = checkDataBase();

        if (!dbExist) {
            this.getWritableDatabase();
            this.close();

            copyDataBase();
        }
    }

    /**
     * Opens database connection for reads and writes.
     *
     * @return opened database
     * @throws SQLException if opening failed
     */
    public SQLiteDatabase openDataBase() throws SQLException {
        return SQLiteDatabase.openDatabase(mDbFullPath, null,
                SQLiteDatabase.CREATE_IF_NECESSARY);
    }

    /**
     * Checks if database exist.
     *
     * @return true if exist, false otherwise
     */
    private boolean checkDataBase() {
        final File dbFile = new File(mDbFullPath);

        return dbFile.exists();
    }

    /**
     * Copy database in output stream.
     *
     * @throws IOException exception if copying database failed
     */
    private void copyDataBase() throws IOException {
        // Open your local db as the input stream
        final InputStream myInput = mDbContext.getAssets().open(mDbName);
        // Path to the just created empty db
        final String outFileName = mDbFullPath;
        // Open the empty db as the output stream
        final OutputStream myOutput = new FileOutputStream(outFileName);

        // transfer bytes from the inputfile to the outputfile
        final byte[] buffer = new byte[1024];

        int length;
        // while the file is reading (have unreaded bytes)
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }

        // Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();
    }
}

Usage.java

try {
    final SQLiteDatabase sqliteDatabase = DbManager.getInstance().getDatabase();
    final SomeDao someDao = new SomeDao(sqliteDatabase);
    final Some some = someDao.findById(id);

    return some;
} catch (final IOException e) {
    return null;
}

https://github.com/mavstuff/sqlite_android