What sqlite statement can give the result for specifying duplicate records? Android
For my application, I am trying to add records without a duplicate record, and if there is a duplicate, notify the user and ask them to try again. Using SQLite, which I know very little about, I have tried these two methods and I want to know which one is better, more efficient, or better to develop it?
First way:
db.execSQL("INSERT INTO " + DatabaseHelper.DATABASE_TABLE +
"(LATITUDE, LONGITUDE, RATING) SELECT " + latitude + ", " + longitude + ", " + rating +
" WHERE NOT EXISTS (SELECT 1 FROM " + DatabaseHelper.DATABASE_TABLE +
" WHERE LATITUDE = " + latitude + " AND LONGITUDE = " + longitude + ")");
Second way:
long id = -1;
try {
id = db.compileStatement(
"SELECT COUNT(*) FROM " + DatabaseHelper.DATABASE_TABLE
+ " WHERE LATITUDE = " + latitude
+ " AND LONGITUDE = " + longitude)
.simpleQueryForLong();
} catch (NullPointerException e) {
return -1;
}
return id;
The first way is to either insert or ignore the values, I check this by storing the number of lines of strings in a variable before the call, call this function and then check its results. If the results are no higher than before the call, the insert was ignored, prompt the user for the message "values exist". (Very casually, I know, but desperate times call for desperate measures)
The second method returns the actual number of rows that correspond to the numbers I want to store if the number returned is greater than 1 prompts the user for a "values exist" message.
I've been going back and forth trying in different ways, but I don't know how to tune SQLite to have UNIQUE pairs that I was told would be the easiest. If anyone can fix any of these ways and / or comment on them, it will be very helpful.
thank
source to share
I decided to go with my second approach (see above)
The reason being that creation UNIQUE INDEX
doesn't seem to work for me and has proven to be a difficult task for me whether it was the result of my end or SQLite end.
The goal is not trivial, the approach may be trivial, but it does what I need it to do.
Hope people facing a similar situation can be affected by the answers left here and wish they had better luck than me.
source to share
This is where I understand what you want: have a Database_Table (not the most descriptive name, so to speak) that never allows you to enter one latitude and longitude pair for two rows.
If that's correct, you want to declare either PRIMARY or UNIQUE KEY, which includes the latitude and longitude of the columns. If you do this, any attempt to INSERT the same pair will throw an exception, which you can catch and thereby notify the user. Using this method, you don't need to use the WHERE NOT EXISTS (...) clause in your query, just do a simple INSERT and let SQLite alert you to a violation of your UNIQUEness constraint.
If your table already exists, the key can be easily added using the SQL CREATE INDEX command . Since you can only have one PRIMARY KEY in any table, if you have a PRIMARY KEY on the table already, you will need UNIQUE KEY for this purpose.
The simplest form of the CREATE INDEX statement that you use is:
CREATE UNIQUE INDEX lat_long_unique ON Database_Table(latitude, longitude)
source to share
It seems to me that using raw lat / long is a great way to check for duplicates. Every possible lat / long combination using Android GeoPoint 1E6 format is less than five square inches. This size is, of course, different depending on where on Earth you stand, but it makes for a pretty good rule of thumb. So you should at least round your lat / long to the nearest ten, one hundred or thousand depending on the size of the thing you want to measure.
Before storing it in the database:
lat = (lat/1000)*1000;
lon = (lon/1000)*1000;
Here's a good tool for calculating distances between lat / long points on Earth:
source to share