CODEWITHSUNDEEP
androiddatabasecompare
Scamparelli
Scamparelli

Reputation: 744

How to check if data already saved in Android database

I am trying to check if the text the user inputs is already in my database but unsure how this should be done.

So I can fetch the data from my database by calling the following in my database helper class:

public Cursor fetchDamagedComponentsForLocation(long damagedComponentId) {
    Cursor mCursor =
            rmDb.query(true, DAMAGED_COMPONENTS_TABLE, new String[] {
                    COMPONENT_ID, LOCATION_LINK, RUN_LINK, AREA_LINK, INSPECTION_LINK, LOCATION_REF, RACKING_SYSTEM, COMPONENT, POSITION, RISK, ACTION_REQUIRED, NOTES_GENERAL, MANUFACTURER, TEXT1, TEXT2, TEXT3, TEXT4, NOTES_SPEC}, 
                    LOCATION_LINK + "=" + damagedComponentId, null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
}

For each of the database entries I need to check if two user inputs match two of the database fields (in this case COMPONENT and POSITION) which I get from my Spinners when the user clicks on the save button:

    String component = ((Cursor)componentSpinner.getSelectedItem()).getString(2).toString();
    String position = ((Cursor)positionSpinner.getSelectedItem()).getString(1).toString();

This is basically to make sure there is not already that component saved.

I'm sure this is quite simple, but I can't work out how to do it..

edit - following Sams advice, I have added the following class to my databased helper class:

 public boolean checkIfComponentAlreadySaved(long locationId, String component, String position) {
     Cursor mCursor = rmDb.query(DAMAGED_COMPONENTS_TABLE, new String[] {"1"}, 
             LOCATION_LINK + " = " + locationId + " AND " + COMPONENT + " = " + component + " AND " + POSITION + " = " + position, null, null, null, null, null);
     boolean result = mCursor.moveToFirst();
     mCursor.close();
     return result;
 }

However, I am getting the following error:

android.database.sqlite.SQLiteException: near "pin": syntax error: , while compiling: SELECT 1 FROM damaged_components_table WHERE location_link = 3 AND component = Locking pin AND position = Not Applicable

I'm guessing this is because I am comparing Strings, but any suggestions?

Upvotes: 0

Views: 1364

Answers (2)

Sam
Sam

Reputation: 86948

moveToFirst() returns true if one or more rows exist, false if the Cursor is empty. So create a new method like this:

public boolean isComponentDamagedForLocation(long damagedComponentId) {
    Cursor mCursor =
            rmDb.query(DAMAGED_COMPONENTS_TABLE, new String[] {"1"}, 
                    LOCATION_LINK + "=" + damagedComponentId, null,
                    null, null, null, null);
    result = mCursor.moveToFirst();
    mCursor.close();
    return result;
}

The exact columns don't matter so you can pass anything that you want, the WHERE clause is the heart of this query. Use this Java method, to test if the data exists. If isComponentDamagedForLocation) is false, then insert the data.

Addition
Since you are using Strings in your query you should use the replacement character (?) to prevent the error you are seeing now and protect yourself from SQL injection attacks:

public boolean checkIfComponentAlreadySaved(long locationId, String component, String position) {
    Cursor mCursor = rmDb.query(DAMAGED_COMPONENTS_TABLE, new String[] {"1"}, 
            LOCATION_LINK + " = " + locationId + " AND " + COMPONENT + " = ? AND " + POSITION + " = ?", 
            new String[] {component, position}, null, null, null, null);
    // etc

Upvotes: 1

Joe Malin
Joe Malin

Reputation: 8641

I'm confused. You're retrieving data from the database in a Cursor, but you're also getting Cursor IDs from the Spinner? What's the source of the spinner data? getSelectedItem is a method of AdapterView, which assumes that each entry in the View is bound to a row in the backing data. getSelectedItem returns an "id" value in the backing data of the currently selected bound View.

Step back and think about the problem logically. What data is in the database? What data is the user entering? How do you make a record of what the user selected, and look for that selection in data you have in the database? Don't try to do fancy shortcuts, do it step-by-step. How would you search the database if the user had to type in the data?

Upvotes: 0

Related Questions