Android SQLite insert unique

Simple enough question, but I cant find ant ha开发者_StackOverflow社区lf-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

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.

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

One thing is you need to handle your orientation change issue, to access the database on every orientation change consumes a lot.

Second thing, regarding the check duplicate record problem, you can define the column as unique like

CREATE TABLE table_name(column_definition, unique(column_names) ON CONFLICT replace);` hen 

You can perform insert as usual, the db would ensure no duplicates exist.