Android SQLite insert unique

Question

Simple enough question, but I cant find ant half-decent documentation on it. Right so I've created my SQLite db but it has two problems or one depending how you look at it! The db creates fine and I can show the data in a basic listview, however on changing the orientation of the phone/emulator the insert statements run again and duplicate all the records. Im guessing this has something to do with all the code being placed within the OnCreate() method. My main question however is how to do INSERT UNIQUE or query the db to see if the record exists? I just could find any good documentation on SQLite DB parameters on the Android platform.

Any help is much obliged !

Cheers

Answer 1

SQLite has an INSERT OR REPLACE command (REPLACE for short, or use SQLiteDatabase.replace()) that can do what you want.

However I'd suggest that your application shouldn't be re-inserting into the database every time your Activity is created unless it really needs to, so you might want to look into changing the design.

Answer 2

You should make use of the DatabaseUtils.stringForQuery() static method that is already in Android SDK to easily retrieve a value, this example is for String bot there is also method for Long

stringForQuery(SQLiteDatabase db, String query, String[] selectionArgs)

Utility method to run the query on the db and return the value in the first column of the first row.

Something like

String myString=DatabaseUtils.stringForQuery(getDB(),query,selectionArgs);

This example was to get a string value, but you can easily adapt to get a Long value, and your query should be something like

select count(*) from mytable where field1='stringtocheckagainst'

if this returns a number greater the zero, then the field already exists in the database.

Also look into code for handling orientation changes.

---

UPDATE by @nutone:

On API 11 and higher, you can also use the DatabaseUtils.queryNumEntries functions to get the number of (filtered) rows directly.

long count = DatabaseUtils.queryNumEntries(getDB(), table, query, selectionArgs);