Query in ContentValues

Question

I have to insert a lot of rows in my SQLite Database and for some tables a specific value of each row has to be converted into an other using the value of an other table. Actually, I have this function which is working well:

public void myFunction( String tableName, String attrName, ContentValues currentVal ) { 

    SQLiteDatabase database = this.getReadableDatabase();
    Cursor c = database.rawQuery("SELECT colName FROM "+tableName+" WHERE "+tableName+".otherColName = " + currentVal.getAsString(attrName), null);

    Long realValue = null;
    if( c.moveToFirst() ) { 
        realValue = c.getLong(0);
    }
    if( c != null ) c.close();

    currentVal.put( attrName, realValue );

}

But, this is really time consumimg because of this part:

if( c.moveToFirst() ) { 
    realValue = c.getLong(0);
}

So I wanted to know if there's a way to set the query directly in the value of the ContantValue like this:

currentVal.put( attrName, "SELECT colName FROM "+tableName+" WHERE "+tableName+".otherColName = " + currentVal.getAsString(attrName));

and the query will be executed at the same time as the insert query in order to replace the value.

Thank you beforehand!

Please, some help would be appreciated. – Nicolas Cortell Jan 14 '16 at 13:11 — Nicolas Cortell, Jan 14 '16 at 13:11

score 1 · Answer 1 · answered Jan 14 '16 at 10:33

1

No, ContentValues is just a wrapper of a hash map for the data. You can try to use index to accelerate the query.

answered Jan 14 '16 at 10:33

xizzhu

895
6
9

I already use indexes. But if you know an other way to solve my problem ... The problem is in the `c.moveToFirst()` which is really time consuming even if I use indexes, foreign keys ... – Nicolas Cortell Jan 14 '16 at 10:35
@NicolasCortell How many rows do you have, and how long does it take to do the query? – xizzhu Jan 14 '16 at 10:36
For the largest table I have more than 1 000 000 rows. Sometimes, I have to insert 10 000 to 20 000 rows at once and for each rows I have to replace the specific value before inserting the row. It can take about 20 minutes to achieve the insert of the 20 000 rows. – Nicolas Cortell Jan 14 '16 at 10:41
@NicolasCortell Have you used transactions for the operation? https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#beginTransaction() – xizzhu Jan 14 '16 at 10:44
Yes, I already did but it still taking too much time. Maybe I should write my own `Cursor` class. Really don't know. – Nicolas Cortell Jan 14 '16 at 10:49

Query in ContentValues

1 Answers1