My Android app is reading and writing to a local SQLite DB from a few different Activities and a Service. Pretty standard. But I'm not happy with the way I've got all the DB details stored as constants that I then use anywhere I access the DB. I've been advised to wrap the DB in a ContentProvider. Sounds good to me. While I'm refactoring my code, I figured I'd ask: <ul> <li>What are your best practices for local DB data storage in Android?</li> <li>Where and how do you store "CREATE TABLE" statements, column names, other SQL?</li> <li>Would you mind sharing a list of the classes you instantiate and what goes into each (ContentProvider, DatabaseProvider, DatabaseHelper...)?</li> <li>How do you coordinate the structure of your local Android DB with a server-side DB available through a REST interface?</li> </ul> Yeah, I realize I'm getting at the perennial "where's the Android object-relation-mapping framework?" question. For now, I'm mainly curious to hear how you structure your Android apps with what's available in the standard SDK. As always, thanks for the pointers!

We have been tuning ORMLite on Android for a while now and it is working well. ORMLite supports Android with native database calls and also supports other databases via JDBC. You annotate your classes/fields and use base DAO classes to persist to SQLite. <ul> <li>CREATE TABLE statements are handled my ORMLite's utility classes. Most SQL is taken care of by the DAO classes.</li> <li>The Android section of the online documentation explains the class hierarchy. You implement a <code>DatabaseHelper</code> which helps create an update your database. Your activities extend <code>OrmLiteBaseActivity</code> (or service or tab) which gives access to helper and DAOs.</li> <li>ORMLite does not provide a solution for merging with remote REST servers.</li> </ul> Hope this is somewhat helpful.

Best practices for SQLite DB and ContentProvider

My Android app is reading and writing to a local SQLite DB from a few different Activities and a Service. Pretty standard. But I'm not happy with the way I've got all the DB details stored as constants that I then use anywhere I access the DB. I've been advised to wrap the DB in a ContentProvider. Sounds good to me. While I'm refactoring my code, I figured I'd ask:

What are your best practices for local DB data storage in Android?
Where and how do you store "CREATE TABLE" statements, column names, other SQL?
Would you mind sharing a list of the classes you instantiate and what goes into each (ContentProvider, DatabaseProvider, DatabaseHelper...)?
How do you coordinate the structure of your local Android DB with a server-side DB available through a REST interface?

Yeah, I realize I'm getting at the perennial "where's the Android object-relation-mapping framework?" question. For now, I'm mainly curious to hear how you structure your Android apps with what's available in the standard SDK.

As always, thanks for the pointers!

What is the best GUI for SQLite?

If you just want to work with SQLite, DB Browser for SQLite and SQLiteStudio are good choices. Both tools offer similar features for the casual user and do not require any preparation to access your database. The more you want to do, the more I suggest you use SQLiteStudio.

Why you should not use SQLite?

High write volumes: SQLite allows only one write operation to take place at any given time, which significantly limits its throughput. If your application requires lots of write operations or multiple concurrent writers, SQLite may not be adequate for your needs.

Can SQLite be used over network?

SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows).

Is SQLite good enough for production?

SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite.

We have been tuning ORMLite on Android for a while now and it is working well. ORMLite supports Android with native database calls and also supports other databases via JDBC. You annotate your classes/fields and use base DAO classes to persist to SQLite.

CREATE TABLE statements are handled my ORMLite's utility classes. Most SQL is taken care of by the DAO classes.
The Android section of the online documentation explains the class hierarchy. You implement a DatabaseHelper which helps create an update your database. Your activities extend OrmLiteBaseActivity (or service or tab) which gives access to helper and DAOs.
ORMLite does not provide a solution for merging with remote REST servers.

Hope this is somewhat helpful.

For now, I'm mainly curious to hear how you structure your Android apps with what's available in the standard SDK.

I'm not a real fan of SQL and the way that it's handled in android, so I use the object database NeoDatis. It basically just lets you store / retrieve Java objects into a flat file stored on the device very easily. db40 is also another alternative Object Database that will work on android.

Haven't had any issues using this approach, you may want to note that including the NeoDatis library will increase your APK size by ~700kb.

Best practices for SQLite DB and ContentProvider

Tags:

android

orm

Drew Dara-Abrams

People also ask

2 Answers

Gray

Duane

Recent Activity

Donate For Us

Best practices for SQLite DB and ContentProvider

Tags:

android

orm

Drew Dara-Abrams

People also ask

2 Answers

Gray

Duane

Related questions

Recent Activity

Donate For Us