Logging SQL queries in android

I am using the query functions in order to build the SQL queries for my tables. Is there a way to see the actual query that is run? For instance log it somewhere?

So far the best I could do was to have a look at the cursor's member mQuery using a breakpoint. I'd love to output the queries automatically though. This member is of course not public and does not have a getter.

Just for the record, here is an implementation of the accepted answer.

/**
 * Implement the cursor factory in order to log the queries before returning 
 * the cursor
 * 
 * @author Vincent @ MarvinLabs
 */
public class SQLiteCursorFactory implements CursorFactory {

    private boolean debugQueries = false;

    public SQLiteCursorFactory() {
        this.debugQueries = false;
    }

    public SQLiteCursorFactory(boolean debugQueries) {
        this.debugQuerie开发者_如何学编程s = debugQueries;
    }

    @Override
    public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery, 
                            String editTable, SQLiteQuery query) {
        if (debugQueries) {
            Log.d("SQL", query.toString());
        }
        return new SQLiteCursor(db, masterQuery, editTable, query);
    }
}

adb shell setprop log.tag.SQLiteStatements VERBOSE

Don't forget to restart your app after setting this property.

It is also possible to enable logging of execution time. More details are availabe here: http://androidxref.com/4.2.2_r1/xref/frameworks/base/core/java/android/database/sqlite/SQLiteDebug.java

You can apply your own SQLiteDatabase.CursorFactory to the database. (See the openDatabase parameters.) This will allow you to create your own subclass of Cursor, which keeps the query in an easily accessible field.

edit: In fact, you may not even have to subclass Cursor. Just have your factory's newCursor() method return a standard SQLiteCursor, but log the query before doing so.

adb shell setprop log.tag.SQLiteLog V
adb shell setprop log.tag.SQLiteStatements V
adb shell stop
adb shell start

Using an SQLiteQueryBuilder it's painfully simple. buildQuery() returns a raw sql string, which can then be logged:

SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
qb.setTables(ExampleTable.TABLE_NAME);
String sql = qb.buildQuery(projection, selection, null, null, sortOrder, null);
Log.d("Example", sql);

So far the best I could do was to have a look at the cursor's member mQuery using a breakpoint. This member is of course not public and does not have a getter, hence, no way to output it. Any better suggestion?

If you are using SQLiteDatabase with it's standard methods as insert, update and delete custom CursorFactory will not be working.

I implemented my not very great but working solution based on SQLiteDatabase class. It just repeats logic of insert, update and delete methods but without statements and actually doing the logging of SQL statements.

public class SQLiteStatementsLogger {

    private static final String TAG = SQLiteStatementsLogger.class.getSimpleName();

    private static final String[] CONFLICT_VALUES = new String[]
            {"", " OR ROLLBACK ", " OR ABORT ", " OR FAIL ", " OR IGNORE ", " OR REPLACE "};

    public void logInsert(String table, String nullColumnHack, ContentValues values) {
        logInsertWithOnConflict(table, nullColumnHack, values, 0);
    }

    public static void logInsertWithOnConflict(String table, String nullColumnHack,
                                     ContentValues initialValues, int conflictAlgorithm) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT");
        sql.append(CONFLICT_VALUES[conflictAlgorithm]);
        sql.append(" INTO ");
        sql.append(table);
        sql.append('(');

        Object[] bindArgs = null;
        int size = (initialValues != null && initialValues.size() > 0)
                ? initialValues.size() : 0;
        if (size > 0) {
            bindArgs = new Object[size];
            int i = 0;
            for (String colName : initialValues.keySet()) {
                sql.append((i > 0) ? "," : "");
                sql.append(colName);
                bindArgs[i++] = initialValues.get(colName);
            }
            sql.append(')');
            sql.append(" VALUES (");
            for (i = 0; i < size; i++) {
                sql.append((i > 0) ? ",?" : "?");
            }
        } else {
            sql.append(nullColumnHack + ") VALUES (NULL");
        }
        sql.append(')');
        sql.append(". (");
        for (Object arg : bindArgs) {
            sql.append(String.valueOf(arg)).append(",");
        }
        sql.deleteCharAt(sql.length()-1).append(')');
        Log.d(TAG, sql.toString());
    }

    public static void logUpdate(String table, ContentValues values, String whereClause, String[] whereArgs) {
        logUpdateWithOnConflict(table, values, whereClause, whereArgs, 0);
    }

    public static void logUpdateWithOnConflict(String table, ContentValues values,
                                        String whereClause, String[] whereArgs, int conflictAlgorithm) {

        StringBuilder sql = new StringBuilder(120);
        sql.append("UPDATE ");
        sql.append(CONFLICT_VALUES[conflictAlgorithm]);
        sql.append(table);
        sql.append(" SET ");

        // move all bind args to one array
        int setValuesSize = values.size();
        int bindArgsSize = (whereArgs == null) ? setValuesSize : (setValuesSize + whereArgs.length);
        Object[] bindArgs = new Object[bindArgsSize];
        int i = 0;
        for (String colName : values.keySet()) {
            sql.append((i > 0) ? "," : "");
            sql.append(colName);
            bindArgs[i++] = values.get(colName);
            sql.append("=?");
        }
        if (whereArgs != null) {
            for (i = setValuesSize; i < bindArgsSize; i++) {
                bindArgs[i] = whereArgs[i - setValuesSize];
            }
        }
        if (!TextUtils.isEmpty(whereClause)) {
            sql.append(" WHERE ");
            sql.append(whereClause);
        }
        sql.append(". (");
        for (Object arg : bindArgs) {
            sql.append(String.valueOf(arg)).append(",");
        }
        sql.deleteCharAt(sql.length()-1).append(')');
        Log.d(TAG, sql.toString());
    }

    public static void logDelete(String table, String whereClause, String[] whereArgs) {
        StringBuilder sql = new StringBuilder("DELETE FROM " + table);
        if (!TextUtils.isEmpty(whereClause)) {
            sql.append(" WHERE " + whereClause);
            sql.append(". (");
            for (Object arg : whereArgs) {
                sql.append(String.valueOf(arg)).append(",");
            }
            sql.deleteCharAt(sql.length()-1).append(')');
        }
        Log.d(TAG, sql.toString());
    }
}

Be aware not to use the logger in release versions. It might increase time of queries executing. You can check if the build is in debug mode with this code line:

0 != (getApplicationInfo().flags & ApplicationInfo.FLAG_DEBUGGABLE)

If it is for once off scenario, I would suggest injecting an error (e.g. type in expression like LIEK instead of LIKE!) and watch the Eclipse LogCat for any errors! HTH!

If you are using a ContentProvider to access the DB, this is how I got it logging the queries. Not a perfect solution, but it works for development

@Override
  public boolean onCreate() {
    dbHelper = new MySQLiteHelper(getContext());
    database=dbHelper.getWritableDatabase();

    if(!database.isReadOnly())
      database.execSQL("PRAGMA foreign_keys=ON;");
    return true;
  }            

  SQLiteDatabase.CursorFactory cursorFactory = new SQLiteDatabase.CursorFactory() {      
    @Override
    public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery, String editTable, SQLiteQuery query) {
      Log.d(TAG, "Query: "+query);

      return new SQLiteCursor(db, masterQuery, editTable, query);
    }
  };

  @Override
  public Cursor query(Uri uri, String[] projection, String selection,
      String[] selectionArgs, String sortOrder) {
    String table =getTableName(uri);

    if(Constants.LOG_QUERIES){
      database = SQLiteDatabase.openOrCreateDatabase(database.getPath(), cursorFactory);
    }

    Cursor cursor =database.query(table,  projection, selection, selectionArgs, null, null, sortOrder);
    cursor.moveToFirst();

    return cursor;
  }

It'll throw a DatabaseNotClosed exception, but you'll be able to see the query

Personnally I log text using java.util.Log and the Log.w("MYAPPNAME", "My text...") function. It shows up in the Log view of Eclipse and it can be filtered to output only the logs for "MYAPPNAME".