Java Code Examples for android.database.sqlite.SQLiteStatement#execute()

/**
 * Submits a batch of commands to the database for execution.
 * 
 * @param sql SQL to execute.
 * @param args arguments to bind to the query.
 */
void batchExecute(String sql, String[][] argsPerRow) {
    SQLiteDatabase database = null;
    SQLiteStatement statement = null;
    try {
        database = databaseHelper.getWritableDatabase();
        database.beginTransaction();
        statement = database.compileStatement(sql);
        for (String[] args : argsPerRow) {
            statement.clearBindings();
            SQLiteUtils.bindAllArgsAsStrings(statement, args);
            statement.execute();
        }
        database.setTransactionSuccessful();
    } catch (Exception ex) {
        Log.e(TAG, "Couldn't execute batch " + sql, ex);
    } finally {
        SQLiteUtils.close(statement);
        SQLiteUtils.endTransaction(database);
        SQLiteUtils.close(database);
    }
}
 

protected void updateInsideSynchronized(Object obj, SQLiteStatement sqlitestatement, boolean flag)
{
    bindValues(sqlitestatement, obj);
    int i = 1 + config.allColumns.length;
    Object obj1 = getKey(obj);
    if (obj1 instanceof Long)
    {
        sqlitestatement.bindLong(i, ((Long)obj1).longValue());
    } else
    {
        if (obj1 == null)
        {
            throw new DaoException("Cannot update entity without key - was it inserted before?");
        }
        sqlitestatement.bindString(i, obj1.toString());
    }
    sqlitestatement.execute();
    attachEntity(obj1, obj, flag);
}
 

private void createFiles(SQLiteDatabase db) {
    mFileNames = new String[mFiles];
    byte[] rawData = new byte[mFileSize+mFiles];
    Random random = new Random();
    random.nextBytes(rawData);

    ByteArrayOutputStream[] streams = new ByteArrayOutputStream[mFiles];
    for (int i = 0; i < mFiles; i++) {
        streams[i] = new ByteArrayOutputStream(mFileSize);
        streams[i].write(rawData, i, mFileSize);
        mFileNames[i] = String.valueOf(i);
    }

    SQLiteStatement insert = db.compileStatement("INSERT INTO files (filename, data) VALUES (?, ?)");
    for (int i = 0; i < mFiles; i++) {
        insert.bindString(1, mFileNames[i]);
        insert.bindBlob(2, streams[i].toByteArray());

        insert.execute();
    }
}
 

public void deleteCategories(@NonNull List<Category> categories) {
    if (!openDatabase()) {
        LogUtil.e("Database error: deleteCategories() failed to open database");
        return;
    }

    String query = "DELETE FROM " +TABLE_CATEGORIES+ " WHERE " +KEY_NAME+ " = ?";
    SQLiteStatement statement = mDatabase.get().mSQLiteDatabase.compileStatement(query);
    mDatabase.get().mSQLiteDatabase.beginTransaction();

    for (Category category : categories) {
        statement.clearBindings();
        statement.bindString(1, category.getName());
        statement.execute();
    }

    mDatabase.get().mSQLiteDatabase.setTransactionSuccessful();
    mDatabase.get().mSQLiteDatabase.endTransaction();
}
 

/**
 * Submits a batch of commands to the database for execution.
 * 
 * @param sql SQL to execute.
 * @param statementBinder the BatchSQLiteStatementBinder to set values to a SQLiteStatement.
 */
void batchExecute(String sql, BatchSQLiteStatementBinder statementBinder) {
    SQLiteDatabase database = null;
    SQLiteStatement statement = null;
    try {
        database = databaseHelper.getWritableDatabase();
        database.beginTransaction();
        statement = database.compileStatement(sql);
        for (int i = 0; i < statementBinder.getBatchSize(); i++) {
            statement.clearBindings();
            statementBinder.bindValues(statement, i);
            statement.execute();
        }
        database.setTransactionSuccessful();
    } catch (Exception ex) {
        Log.e(TAG, "Couldn't execute batch [" + sql + "]", ex);
    } finally {
        SQLiteUtils.close(statement);
        SQLiteUtils.endTransaction(database);
        SQLiteUtils.close(database);
    }
}
 

public void putBulkItemValue(JsonArray api_data) {
    SQLiteDatabase db = null;
    SQLiteStatement statement;
    try {
        if (api_data.size() > 0) {
            db = getWritableDatabase();
            db.delete(slotitem_table_name, null, null);
            db.beginTransaction();
            statement = db.compileStatement("INSERT INTO ".concat(slotitem_table_name).concat(" (KEY, KCID, VALUE) values (?, ?, ?)"));

            for (JsonElement item: api_data) {
                int column = 1;
                JsonObject item_data = item.getAsJsonObject();
                String api_id = item_data.get("api_id").getAsString();
                String api_slotitem_id = item_data.get("api_slotitem_id").getAsString();
                statement.bindString(column++, api_id);
                statement.bindString(column++, api_slotitem_id);
                statement.bindString(column++, item_data.toString());
                statement.execute();
            }
            statement.close();
            db.setTransactionSuccessful();
        }
    } catch (RuntimeException e) {
        e.printStackTrace();
    } finally {
        if (db != null) {
            db.endTransaction();
        }
    }
}
 

public void putBulkItemValue(JsonArray api_data) {
    SQLiteDatabase db = null;
    SQLiteStatement statement;
    try {
        if (api_data.size() > 0) {
            db = getWritableDatabase();
            db.delete(slotitem_table_name, null, null);
            db.beginTransaction();
            statement = db.compileStatement("INSERT INTO ".concat(slotitem_table_name).concat(" (KEY, KCID, VALUE) values (?, ?, ?)"));

            for (JsonElement item: api_data) {
                int column = 1;
                JsonObject item_data = item.getAsJsonObject();
                String api_id = item_data.get("api_id").getAsString();
                String api_slotitem_id = item_data.get("api_slotitem_id").getAsString();
                statement.bindString(column++, api_id);
                statement.bindString(column++, api_slotitem_id);
                statement.bindString(column++, item_data.toString());
                statement.execute();
            }
            statement.close();
            db.setTransactionSuccessful();
        }
    } catch (RuntimeException e) {
        e.printStackTrace();
    } finally {
        if (db != null) {
            db.endTransaction();
        }
    }
}
 

public void updateDownloadCurrentBytes(final DownloadInfo di) {
    SQLiteStatement downloadCurrentBytes = getUpdateCurrentBytesStatement();
    downloadCurrentBytes.clearBindings();
    downloadCurrentBytes.bindLong(1, di.mCurrentBytes);
    downloadCurrentBytes.bindLong(2, di.mIndex);
    downloadCurrentBytes.execute();
}
 

@Override
public void execute(String sql, Object... params) throws IOException {
    try {
        SQLiteStatement s = db.compileStatement(sql);
        for (int i = 0; i < params.length; i++) {
            Object p = params[i];
            if(p == null){
                s.bindNull(i + 1);
            }else{
                if(p instanceof String){
                    s.bindString(i + 1, (String)p);                    
                }else if(p instanceof byte[]){
                    s.bindBlob(i + 1, (byte [])p);
                }else if(p instanceof Double){
                    s.bindDouble(i + 1, ((Double)p).doubleValue());
                } else if(p instanceof Long){
                    s.bindLong(i + 1, ((Long)p).longValue());
                } else if(p instanceof Integer){
                    s.bindLong(i + 1, ((Integer)p).intValue());
                } else {
                    if(p != null) {
                        s.bindString(i + 1, p.toString());
                    }
                }
            }
        }
        s.execute();
        s.close();
    } catch (Exception e) {
        e.printStackTrace();
        throw new IOException(e.getMessage());
    }
    
}
 

/**
 * Delete a gps log by its id.
 *
 * @param id the log's id.
 * @throws IOException
 */
public void deleteGpslog(long id) throws IOException {
    SQLiteDatabase sqliteDatabase = GeopaparazziApplication.getInstance().getDatabase();
    sqliteDatabase.beginTransaction();
    try {
        // delete log
        String query = "delete from " + TABLE_GPSLOGS + " where " + GpsLogsTableFields.COLUMN_ID.getFieldName() + " = " + id;
        SQLiteStatement sqlUpdate = sqliteDatabase.compileStatement(query);
        sqlUpdate.execute();
        sqlUpdate.close();

        // delete properties
        query = "delete from " + TABLE_GPSLOG_PROPERTIES + " where " + GpsLogsPropertiesTableFields.COLUMN_LOGID.getFieldName() + " = " + id;
        sqlUpdate = sqliteDatabase.compileStatement(query);
        sqlUpdate.execute();
        sqlUpdate.close();

        // delete data
        query = "delete from " + TABLE_GPSLOG_DATA + " where " + GpsLogsDataTableFields.COLUMN_LOGID.getFieldName() + " = " + id;
        sqlUpdate = sqliteDatabase.compileStatement(query);
        sqlUpdate.execute();
        sqlUpdate.close();

        sqliteDatabase.setTransactionSuccessful();
    } catch (Exception e) {
        GPLog.error("DOAGPSLOG", e.getLocalizedMessage(), e);
        throw new IOException(e.getLocalizedMessage());
    } finally {
        sqliteDatabase.endTransaction();
    }
}
 

/**
 * Move any settings with the given prefixes from the source table to the
 * destination table.
 */
private void movePrefixedSettingsToNewTable(
        SQLiteDatabase db, String sourceTable, String destTable, String[] prefixesToMove) {
    SQLiteStatement insertStmt = null;
    SQLiteStatement deleteStmt = null;

    db.beginTransaction();
    try {
        insertStmt = db.compileStatement("INSERT INTO " + destTable
                + " (name,value) SELECT name,value FROM " + sourceTable
                + " WHERE substr(name,0,?)=?");
        deleteStmt = db.compileStatement(
                "DELETE FROM " + sourceTable + " WHERE substr(name,0,?)=?");

        for (String prefix : prefixesToMove) {
            insertStmt.bindLong(1, prefix.length() + 1);
            insertStmt.bindString(2, prefix);
            insertStmt.execute();

            deleteStmt.bindLong(1, prefix.length() + 1);
            deleteStmt.bindString(2, prefix);
            deleteStmt.execute();
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
        if (insertStmt != null) {
            insertStmt.close();
        }
        if (deleteStmt != null) {
            deleteStmt.close();
        }
    }
}
 

@SuppressLint("ObsoleteSdkInt")
private void executeSafeUpdateOrDelete(SQLiteStatement statement) {
    if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.HONEYCOMB) {
        statement.executeUpdateDelete();
    }
    else {
        statement.execute();
    }
}
 

public void setTrackLengthm(long logid, double lengthm) throws IOException {
    SQLiteDatabase sqliteDatabase = GeopaparazziApplication.getInstance().getDatabase();
    try {
        sqliteDatabase.beginTransaction();

        StringBuilder sb = new StringBuilder();
        sb = new StringBuilder();
        sb.append("UPDATE ");
        sb.append(TABLE_GPSLOGS);
        sb.append(" SET ");
        sb.append(GpsLogsTableFields.COLUMN_LOG_LENGTHM.getFieldName()).append("=").append(lengthm).append(" ");
        sb.append("WHERE ").append(GpsLogsTableFields.COLUMN_ID.getFieldName()).append("=").append(logid);

        String query = sb.toString();
        if (GPLog.LOG_HEAVY)
            GPLog.addLogEntry("DAOGPSLOG", query);
        SQLiteStatement sqlUpdate = sqliteDatabase.compileStatement(query);
        sqlUpdate.execute();
        sqlUpdate.close();

        sqliteDatabase.setTransactionSuccessful();
    } catch (Exception e) {
        GPLog.error("DAOGPSLOG", e.getLocalizedMessage(), e);
        throw new IOException(e.getLocalizedMessage());
    } finally {
        sqliteDatabase.endTransaction();
    }
}
 

public void log(String url, String req, String resp) {
    SQLiteDatabase db = null;
    SQLiteStatement statement;
    long timestamp;
    try {
        db = getWritableDatabase();
        db.beginTransaction();
        statement = db.compileStatement("INSERT INTO ".concat(packetlog_table_name).concat(" (timestamp, url, type, data) values (?, ?, ?, ?)"));

        timestamp = System.currentTimeMillis();
        statement.bindAllArgsAsStrings(new String[]{String.valueOf(timestamp), url, REQUEST, req});
        statement.execute();

        timestamp = System.currentTimeMillis();
        statement.bindAllArgsAsStrings(new String[]{String.valueOf(timestamp), url, RESPONSE, resp});
        statement.execute();

        statement.close();
        db.setTransactionSuccessful();
    } catch (RuntimeException e) {
        e.printStackTrace();
    } finally {
        if (db != null) {
            db.endTransaction();
        }
    }
}
 

private void deleteByKeyInsideSynchronized(Object obj, SQLiteStatement sqlitestatement)
{
    if (obj instanceof Long)
    {
        sqlitestatement.bindLong(1, ((Long)obj).longValue());
    } else
    {
        if (obj == null)
        {
            throw new DaoException("Cannot delete entity, key is null");
        }
        sqlitestatement.bindString(1, obj.toString());
    }
    sqlitestatement.execute();
}
 

private static void migrateConversation(Context context, MasterSecret masterSecret,
                                        SmsMigrationProgressListener listener,
                                        ProgressDescription progress,
                                        long theirThreadId, long ourThreadId)
{
  SmsDatabase ourSmsDatabase = DatabaseFactory.getSmsDatabase(context);
  Cursor cursor              = null;

  try {
    Uri uri = Uri.parse("content://sms/conversations/" + theirThreadId);

    try {
      cursor = context.getContentResolver().query(uri, null, null, null, null);
    } catch (SQLiteException e) {
      /// Work around for weird sony-specific (?) bug: #4309
      Log.w(TAG, e);
      return;
    }

    SQLiteDatabase transaction = ourSmsDatabase.beginTransaction();
    SQLiteStatement statement  = ourSmsDatabase.createInsertStatement(transaction);

    while (cursor != null && cursor.moveToNext()) {
      int typeColumn = cursor.getColumnIndex(SmsDatabase.TYPE);

      if (cursor.isNull(typeColumn) || isAppropriateTypeForMigration(cursor, typeColumn)) {
        getContentValuesForRow(context, masterSecret, cursor, ourThreadId, statement);
        statement.execute();
      }

      listener.progressUpdate(new ProgressDescription(progress, cursor.getCount(), cursor.getPosition()));
    }

    ourSmsDatabase.endTransaction(transaction);
    DatabaseFactory.getThreadDatabase(context).update(ourThreadId, true);
    DatabaseFactory.getThreadDatabase(context).notifyConversationListeners(ourThreadId);

  } finally {
    if (cursor != null)
      cursor.close();
  }
}
 

private void normalizeIcons(SQLiteDatabase db) {
    Log.d(TAG, "normalizing icons");

    db.beginTransaction();
    Cursor c = null;
    SQLiteStatement update = null;
    try {
        boolean logged = false;
        update = db.compileStatement("UPDATE favorites "
                + "SET icon=? WHERE _id=?");

        c = db.rawQuery("SELECT _id, icon FROM favorites WHERE iconType=" +
                Favorites.ICON_TYPE_BITMAP, null);

        final int idIndex = c.getColumnIndexOrThrow(Favorites._ID);
        final int iconIndex = c.getColumnIndexOrThrow(Favorites.ICON);

        while (c.moveToNext()) {
            long id = c.getLong(idIndex);
            byte[] data = c.getBlob(iconIndex);
            try {
                Bitmap bitmap = Utilities.createIconBitmap(
                        BitmapFactory.decodeByteArray(data, 0, data.length),
                        mContext);
                if (bitmap != null) {
                    update.bindLong(1, id);
                    data = ItemInfo.flattenBitmap(bitmap);
                    if (data != null) {
                        update.bindBlob(2, data);
                        update.execute();
                    }
                    bitmap.recycle();
                }
            } catch (Exception e) {
                if (!logged) {
                    Log.e(TAG, "Failed normalizing icon " + id, e);
                } else {
                    Log.e(TAG, "Also failed normalizing icon " + id);
                }
                logged = true;
            }
        }
        db.setTransactionSuccessful();
    } catch (SQLException ex) {
        Log.w(TAG, "Problem while allocating appWidgetIds for existing widgets", ex);
    } finally {
        db.endTransaction();
        if (update != null) {
            update.close();
        }
        if (c != null) {
            c.close();
        }
    }
}
 

private void normalizeIcons(SQLiteDatabase db) {
    Log.d(TAG, "normalizing icons");

    db.beginTransaction();
    Cursor c = null;
    SQLiteStatement update = null;
    try {
        boolean logged = false;
        update = db.compileStatement("UPDATE favorites "
                + "SET icon=? WHERE _id=?");

        c = db.rawQuery("SELECT _id, icon FROM favorites WHERE iconType=" +
                Favorites.ICON_TYPE_BITMAP, null);

        final int idIndex = c.getColumnIndexOrThrow(Favorites._ID);
        final int iconIndex = c.getColumnIndexOrThrow(Favorites.ICON);

        while (c.moveToNext()) {
            long id = c.getLong(idIndex);
            byte[] data = c.getBlob(iconIndex);
            try {
                Bitmap bitmap = Utilities.resampleIconBitmap(
                        BitmapFactory.decodeByteArray(data, 0, data.length),
                        mContext);
                if (bitmap != null) {
                    update.bindLong(1, id);
                    data = ItemInfo.flattenBitmap(bitmap);
                    if (data != null) {
                        update.bindBlob(2, data);
                        update.execute();
                    }
                    bitmap.recycle();
                }
            } catch (Exception e) {
                if (!logged) {
                    Log.e(TAG, "Failed normalizing icon " + id, e);
                } else {
                    Log.e(TAG, "Also failed normalizing icon " + id);
                }
                logged = true;
            }
        }
        db.setTransactionSuccessful();
    } catch (SQLException ex) {
        Log.w(TAG, "Problem while allocating appWidgetIds for existing widgets", ex);
    } finally {
        db.endTransaction();
        if (update != null) {
            update.close();
        }
        if (c != null) {
            c.close();
        }
    }
}
 

/**
 * Update the properties of a log.
 *
 * @param logid   the id of the log.
 * @param color   color
 * @param width   width
 * @param visible whether it is visible.
 * @param name    the name.
 * @throws IOException if something goes wrong.
 */
public static void updateLogProperties(long logid, String color, float width, boolean visible, String name)
        throws IOException {
    SQLiteDatabase sqliteDatabase = GeopaparazziApplication.getInstance().getDatabase();
    sqliteDatabase.beginTransaction();
    try {

        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE ");
        sb.append(TABLE_GPSLOG_PROPERTIES);
        sb.append(" SET ");
        sb.append(GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_COLOR.getFieldName()).append("='").append(color).append("', ");
        sb.append(GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_WIDTH.getFieldName()).append("=").append(width).append(", ");
        sb.append(GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_VISIBLE.getFieldName()).append("=").append(visible ? 1 : 0).append(" ");
        sb.append("WHERE ").append(GpsLogsPropertiesTableFields.COLUMN_LOGID.getFieldName()).append("=").append(logid);

        String query = sb.toString();
        if (GPLog.LOG_HEAVY)
            GPLog.addLogEntry("DAOGPSLOG", query);
        // sqliteDatabase.execSQL(query);
        SQLiteStatement sqlUpdate = sqliteDatabase.compileStatement(query);
        sqlUpdate.execute();
        sqlUpdate.close();

        if (name != null && name.length() > 0) {
            sb = new StringBuilder();
            sb.append("UPDATE ");
            sb.append(TABLE_GPSLOGS);
            sb.append(" SET ");
            sb.append(GpsLogsTableFields.COLUMN_LOG_TEXT.getFieldName()).append("='").append(name).append("' ");
            sb.append("WHERE ").append(GpsLogsTableFields.COLUMN_ID.getFieldName()).append("=").append(logid);

            query = sb.toString();
            if (GPLog.LOG_HEAVY)
                GPLog.addLogEntry("DAOGPSLOG", query);
            sqlUpdate = sqliteDatabase.compileStatement(query);
            sqlUpdate.execute();
            sqlUpdate.close();
        }

        sqliteDatabase.setTransactionSuccessful();
    } catch (Exception e) {
        GPLog.error("DAOGPSLOG", e.getLocalizedMessage(), e);
        throw new IOException(e.getLocalizedMessage());
    } finally {
        sqliteDatabase.endTransaction();
    }
}
 

public boolean processDownloadedHillshade(int x, int y, String filePath, @Nullable ProgressListener progressListener) {
    File mapFile = new File(filePath);
    try {
        logger.error("Importing from {}", mapFile.getName());
        SQLiteDatabase database = SQLiteDatabase.openDatabase(filePath, null, SQLiteDatabase.OPEN_READONLY);

        int total = 0, progress = 0;
        if (progressListener != null) {
            total += DatabaseUtils.queryNumEntries(database, TABLE_TILES);
            progressListener.onProgressStarted(total);
        }

        // copy tiles
        SQLiteStatement statement = mHillshadeDatabase.compileStatement("REPLACE INTO " + TABLE_TILES + " VALUES (?,?,?,?)");
        mHillshadeDatabase.beginTransaction();
        Cursor cursor = database.query(TABLE_TILES, ALL_COLUMNS_TILES, null, null, null, null, null);
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            statement.clearBindings();
            statement.bindLong(1, cursor.getInt(0));
            statement.bindLong(2, cursor.getInt(1));
            statement.bindLong(3, cursor.getInt(2));
            statement.bindBlob(4, cursor.getBlob(3));
            statement.execute();
            if (progressListener != null) {
                progress++;
                progressListener.onProgressChanged(progress);
            }
            cursor.moveToNext();
        }
        cursor.close();
        mHillshadeDatabase.setTransactionSuccessful();
        mHillshadeDatabase.endTransaction();
        logger.error("  imported tiles");

        byte version = 0;
        cursor = database.query(TABLE_INFO, new String[]{COLUMN_INFO_VALUE}, WHERE_INFO_NAME, new String[]{"version"}, null, null, null);
        if (cursor.moveToFirst())
            version = (byte) Integer.valueOf(cursor.getString(0)).intValue();
        cursor.close();
        database.close();
        if (!mHasHillshades) {
            Configuration.setHillshadesEnabled(true);
            mHasHillshades = true;
        }
        setHillshadeDownloaded(x, y, version);
    } catch (SQLiteException e) {
        MapTrek.getApplication().registerException(e);
        logger.error("Import failed", e);
        return false;
    } finally {
        if (mHillshadeDatabase.inTransaction())
            mHillshadeDatabase.endTransaction();
        if (progressListener != null)
            progressListener.onProgressFinished();
        //noinspection ResultOfMethodCallIgnored
        mapFile.delete();
    }
    return true;
}