CODEWITHSUNDEEP
androidsqlite
Sami Eltamawy
Sami Eltamawy

Reputation: 10019

Best practice for Sqlite query building in Android

I used to build my Sqlite complex queries with input parameters by concatenating a string value with my parameters and return this string with the final executable query as following:-

public static String getExampleQuery(String id) {
    String query = "SELECT * " + "FROM " + Constants.TABLE_NAME
            + " as table " + "INNER JOIN " + Constants.TABLE_NAME_TWO
            + " as table2 " + "ON table." + Constants.COL_ID
            + " = table2." + Constants.COL_ID
                            + " and table2." + Constants.COL_ID + " = '"
                            + para1 + "'"
            + " order by table." + Constants.COL_ID + " desc ";
    return query;
}

Then, I pass the query string to be executed. BUT, most of the times I face a lot of problems with this way by forgetting spaces or adding extra single quot ' before and after the outer parameter. It always be hard to figure out the problem from the logcat. Besides, the queries are not READABLE at all.

What is the best way to build Sqlite queries that make the query Readable and less fault-able and to avoid the previous listed problems! Thanks in advance

Upvotes: 0

Views: 128

Answers (2)

Justin Morris
Justin Morris

Reputation: 7329

I can't read SQLiteQueryBuilder code at a glance and I'm very use to reading SQL so I write my queries close to the way you do it. I try to put my returns at a logical place for readability. I also use the rawQuery method so that I can separate out any variables and not have to worry about quoting text or not quoting integers. And I add extra spaces in my queries.. it just keeps me from making common mistakes:

String[] whereVars = new String[]{id};
String query = " SELECT * "
             + " FROM " + Constants.TABLE_NAME + " as t " 
             + " INNER JOIN " + Constants.TABLE_NAME_TWO + " as t2 " 
             + " ON t." + Constants.COL_ID + " = t2." + Constants.COL_ID
             + " WHERE t2." + Constants.COL_ID + " = ? "
             + " ORDER BY t." + Constants.COL_ID + " DESC ";
db.rawQuery(query, whereVars)

Upvotes: 1

marcinj
marcinj

Reputation: 50046

Look into: SQLiteQueryBuilder, it makes writing queries less low level. You will find some examples how to start using it here:

http://www.vogella.com/tutorials/AndroidSQLite/article.html

Upvotes: 1

Related Questions