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

private void testAndroidSQLiteWrite(Statistics statistics) {
    Trace trace = new Trace("Android Write");
    SQLiteDatabase db = platformSQLite.getReadableDatabase();
    SQLiteStatement statement = db.compileStatement(
        String.format("insert into %s (%s, %s) values (?,?)",
            Record.TABLE_NAME,
            Record.COLUMN_CONTENT,
            Record.COLUMN_CREATED_TIME));
    try {
        db.beginTransaction();
        for (int i = 0; i < COUNT; i++) {
            Record record = Record.create(i);
            statement.bindString(1, record.getContent());
            statement.bindDouble(2, record.getCreatedTime());
            long id = statement.executeInsert();
            record.setId(id);
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
    statistics.write( trace.exit() );
}
 

public void insertFiles(File[] files) {
    SQLiteDatabase database = dbHelper.getWritableDatabase();
    SQLiteStatement statement = database.compileStatement("INSERT INTO " + TABLE_NAME +
            " (" + COL_FILENAME + ", " + COL_FILESIZE + ", " + COL_TIMESTAMP + ") VALUES (?, ?, ?)");
    database.beginTransaction();
    try {
        for (File file : files) {
            statement.bindString(1, file.getName());
            statement.bindLong(2, file.length());
            statement.bindLong(3, file.lastModified());
            statement.executeInsert();
        }
        database.setTransactionSuccessful();
    } finally {
        database.endTransaction();
    }
}
 

@Override
public int bulkInsert(Uri uri, @NonNull ContentValues[] values) {
    SQLiteDatabase db = helper.getWritableDatabase();
    db.beginTransaction();
    int count = 0;
    try {
        SQLiteStatement insertStmt = db.compileStatement(
                "INSERT INTO " + BufferScheme.TABLE_NAME + " (" +
                        BufferScheme.COLUMN_CLASS + ", " +
                        BufferScheme.COLUMN_BODY + ") VALUES (?, ?);");
        for (ContentValues value : values) {
            insertStmt.bindString(1, value.getAsString("name"));
            long id = insertStmt.executeInsert();
            if (id != -1) {
                count++;
            }
        }
        db.setTransactionSuccessful();
    } finally {
        Utils.endTransaction(db);
    }
    return count;
}
 

/**
 * {@inheritDoc}
 */
@Override
public long insertOrReplace(JobHolder jobHolder) {
    if (jobHolder.getId() == null) {
        return insert(jobHolder);
    }
    jobHolder.setRunningSessionId(JobManager.NOT_RUNNING_SESSION_ID);
    SQLiteStatement stmt = sqlHelper.getInsertOrReplaceStatement();
    long id;
    synchronized (stmt) {
        stmt.clearBindings();
        bindValues(stmt, jobHolder);
        id = stmt.executeInsert();
    }
    jobHolder.setId(id);
    return id;
}
 

@Override
public int insert(String statement, Object[] args, FieldType[] argFieldTypes, GeneratedKeyHolder keyHolder)
		throws SQLException {
	SQLiteStatement stmt = null;
	try {
		stmt = db.compileStatement(statement);
		bindArgs(stmt, args, argFieldTypes);
		long rowId = stmt.executeInsert();
		if (keyHolder != null) {
			keyHolder.addKey(rowId);
		}
		/*
		 * I've decided to not do the CHANGES() statement here like we do down below in UPDATE because we know that
		 * it worked (since it didn't throw) so we know that 1 is right.
		 */
		int result = 1;
		logger.trace("{}: insert statement is compiled and executed, changed {}: {}", this, result, statement);
		return result;
	} catch (android.database.SQLException e) {
		throw SqlExceptionUtil.create("inserting to database failed: " + statement, e);
	} finally {
		closeQuietly(stmt);
	}
}
 

public void putDateInDatabase(long now) {
    SQLiteDatabase db = getWritableDatabase();
    SQLiteStatement insert = makeInsertStatement(db);

    insert.bindLong(1, now);
    insert.executeInsert();
    insert.clearBindings();
}
 

private <T> T executeInsert(
    SQLiteDatabase database,
    String query,
    ExecuteResultHandler<T> handler) {
  SQLiteStatement statement = database.compileStatement(query);
  long count = statement.executeInsert();
  return handler.handleInsert(count);
}
 

public void deletePreference(String className) {
    final SQLiteDatabase db = getWritableDatabase();
    final SQLiteStatement statement = db.compileStatement("DELETE FROM "
            + TABLE_NAME + " WHERE " + KEY_CLASSNAME + "=?");
    statement.bindString(1, className);
    statement.executeInsert();
    statement.close();
    db.close();

}
 

@Override
public long executeInsert(String sql, Object[] bindArgs) {
    SQLiteStatement statement = null;
    try {
        statement = db.compileStatement(sql);
        SquidCursorFactory.bindArgumentsToProgram(statement, bindArgs);
        return statement.executeInsert();
    } finally {
        if (statement != null) {
            statement.close();
        }
    }
}
 

/**
 * {@inheritDoc}
 */
@Override
public long insert(JobHolder jobHolder) {
    SQLiteStatement stmt = sqlHelper.getInsertStatement();
    long id;
    synchronized (stmt) {
        stmt.clearBindings();
        bindValues(stmt, jobHolder);
        id = stmt.executeInsert();
    }
    jobHolder.setId(id);
    return id;
}
 

private void executeInsert(SQLiteDatabase database, String query, DatabaseResult result) {
    SQLiteStatement statement = database.compileStatement(query);
    long count = statement.executeInsert();
    result.transformInsert(count);
}
 

private void upGradeToVersion3(SQLiteDatabase db) {
    Log.d(TAG, "upGradeToVersion3(): Entry");

    // We are changing our key field to a new text field that contains a hash of
    // of the ID and type. In addition, we are dealing with a Lint complaint about
    // using a string field where we ought to be using a text field.

    db.execSQL("BEGIN TRANSACTION;");
    db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_SAMPLES + "_new (" +
            COL_HASH + " TEXT PRIMARY KEY, " +
            COL_RFID + " TEXT, " +
            COL_TYPE + " TEXT, " +
            COL_TRUST + " INTEGER, " +
            COL_LAT + " REAL, " +
            COL_LON + " REAL, " +
            COL_RAD_NS + " REAL, " +
            COL_RAD_EW + " REAL, " +
            COL_NOTE + " TEXT);");

    SQLiteStatement insert = db.compileStatement("INSERT INTO " +
            TABLE_SAMPLES + "_new("+
            COL_HASH + ", " +
            COL_RFID + ", " +
            COL_TYPE + ", " +
            COL_TRUST + ", " +
            COL_LAT + ", " +
            COL_LON + ", " +
            COL_RAD_NS + ", " +
            COL_RAD_EW + ", " +
            COL_NOTE + ") " +
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);");

    String query = "SELECT " +
            COL_RFID+","+COL_TYPE+","+COL_TRUST+","+COL_LAT+","+COL_LON+","+COL_RAD_NS+","+COL_RAD_EW+","+COL_NOTE+" "+
            "FROM " + TABLE_SAMPLES + ";";

    Cursor cursor = db.rawQuery(query, null);
    try {
        if (cursor.moveToFirst()) {
            do {
                String rfId = cursor.getString(0);
                String rftype = cursor.getString(1);
                if (rftype.equals("WLAN"))
                    rftype = RfEmitter.EmitterType.WLAN_24GHZ.toString();
                RfIdentification rfid = new RfIdentification(rfId, RfEmitter.typeOf(rftype));
                String hash = rfid.getUniqueId();

                // Log.d(TAG,"upGradeToVersion2(): Updating '"+rfId.toString()+"'");

                insert.bindString(1, hash);
                insert.bindString(2, rfId);
                insert.bindString(3, rftype);
                insert.bindString(4, cursor.getString(2));
                insert.bindString(5, cursor.getString(3));
                insert.bindString(6, cursor.getString(4));
                insert.bindString(7, cursor.getString(5));
                insert.bindString(8, cursor.getString(6));
                insert.bindString(9, cursor.getString(7));

                insert.executeInsert();
                insert.clearBindings();
            } while (cursor.moveToNext());
        }
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
    db.execSQL("DROP TABLE " + TABLE_SAMPLES + ";");
    db.execSQL("ALTER TABLE " + TABLE_SAMPLES + "_new RENAME TO " + TABLE_SAMPLES + ";");
    db.execSQL("COMMIT;");
}
 

@Override
public Metrics runCase() {
    mDbHelper = new DbHelper(App.getInstance(), getClass().getName());
    Metrics result = new Metrics(getClass().getSimpleName()+" ("+mInsertions+" insertions)", mTestSizeIndex);
    SQLiteDatabase db = mDbHelper.getWritableDatabase();

    Charset ascii = Charset.forName("US-ASCII");

    byte[] titleByteArry = new byte[50];
    byte[] urlByteArray = new byte[100];
    byte[] lyricsByteArray = new byte[2000];
    byte[] aboutByteArray = new byte[2000];

    result.started();
    db.beginTransaction();
    SQLiteStatement stmt = db.compileStatement("INSERT INTO tracks (id, title, band_id, duration, url, lyrics, about, release_date, mod_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
    for (int i = 0; i < mInsertions; i++) {
        mRandom.nextBytes(titleByteArry);
        mRandom.nextBytes(urlByteArray);
        mRandom.nextBytes(lyricsByteArray);
        mRandom.nextBytes(aboutByteArray);

        stmt.bindLong(1, i);
        stmt.bindString(2, new String(titleByteArry, ascii));
        stmt.bindLong(3, mRandom.nextInt());
        stmt.bindDouble(4, mRandom.nextDouble());
        stmt.bindString(5, new String(urlByteArray, ascii));
        stmt.bindString(6, new String(lyricsByteArray, ascii));
        stmt.bindString(7, new String(aboutByteArray, ascii));
        stmt.bindLong(8, mRandom.nextLong());
        stmt.bindLong(9, mRandom.nextLong());

        stmt.executeInsert();
        stmt.clearBindings();
    }
    db.setTransactionSuccessful();
    db.endTransaction();
    result.finished();

    return result;
}
 

public void create(final FileEntry entity) {
    if (entity == null) {
        return;
    }
    SQLiteDatabase db = null;
    SQLiteStatement statement = null;
    try {
        DbHelper helper = new DbHelper(getContext());
        db = helper.getWritableDatabase();
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO file_entries (");
        sb.append(" name, ");
        sb.append(" url, ");
        sb.append(" basic_auth_user, ");
        sb.append(" basic_auth_password, ");
        sb.append(" created_at, ");
        sb.append(" updated_at");
        sb.append(") VALUES (");
        sb.append(" ?, "); // name
        sb.append(" ?, "); // url
        if (entity.basicAuthUser != null) {
            sb.append(" ?, "); // basic_auth_user
        }
        if (entity.basicAuthPassword != null) {
            sb.append(" ?, "); // basic_auth_password
        }
        sb.append("DATETIME('now', 'localtime'), "); // created_at
        sb.append("DATETIME('now', 'localtime')"); // updated_at
        sb.append(");");
        statement = db.compileStatement(sb.toString());
        int index = 1;
        statement.bindString(index++, entity.name);
        statement.bindString(index++, entity.url);
        if (entity.basicAuthUser != null) {
            statement.bindString(index++, entity.basicAuthUser);
        }
        if (entity.basicAuthPassword != null) {
            statement.bindString(index++, entity.basicAuthPassword);
        }
        statement.executeInsert();
    } finally {
        if (statement != null) {
            statement.close();
        }
        if (db != null) {
            db.close();
        }
    }
}
 

/**
 * Add a new Observations in an ArrayList
 * 
 * @return
 */
public boolean addObservationArrayList(ArrayList<CbWeather> weather, CbApiCall api) {
	mDB.beginTransaction();

	String insertSQL = "INSERT INTO "
			+ API_LIST_TABLE
			+ " ("
			+ KEY_LATITUDE
			+ ", "
			+ KEY_LONGITUDE
			+ ", "
			+ KEY_ALTITUDE
			+ ", "
			+ KEY_TIME
			+ ", " 
			+ KEY_OBSERVATION_VALUE
			+ " "
			+ ") values (?, ?, ?, ?, ?)";
	try {
		SQLiteStatement insert = mDB.compileStatement(insertSQL);
		
		for (CbWeather weatherItem : weather) {
			
			CbObservation ob = (CbObservation) weatherItem;
			double latitude = ob.getLocation().getLatitude(); 
			double longitude = ob.getLocation().getLongitude();
			double altitude = ob.getLocation().getAltitude();
			insert.bindDouble(1, latitude);
			insert.bindDouble(2, longitude); 
			insert.bindDouble(3, altitude);
			insert.bindLong(4, ob.getTime());
			insert.bindDouble(5, ob.getObservationValue());
			insert.executeInsert();
		}

		mDB.setTransactionSuccessful();
		
	} catch (SQLException sqle) {
		sqle.printStackTrace();
	} finally {
		mDB.endTransaction();
	}
	return true;
}
 

public boolean addTemperatureForecastArrayList(ArrayList<TemperatureForecast> tempForecasts) {
	
	try {
		mDB.beginTransaction();
	} catch(SQLiteDatabaseLockedException sqldble) {
		// This try/catch block is a bad hack. Refactor db usaage to use only one lock
		// regardless of the thread
		
	}
	
	String insertSQL = "INSERT INTO "
			+ TEMPERATURES
			+ " ("
			+ KEY_FORECAST_LOCATION_ID
			+ ", "
			+ KEY_TEMP_FORECAST_START_TIME
			+ ", "
			+ KEY_TEMP_FORECAST_HOUR
			+ ", "
			+ KEY_TEMP_FORECAST_SCALE
			+ ", "
			+ KEY_TEMP_FORECAST_VALUE
			+ ") values (?, ?, ?, ?, ?)";

	try {
		SQLiteStatement insert = mDB.compileStatement(insertSQL);
		for (TemperatureForecast temp : tempForecasts) {
			insert.bindString(1, temp.getLocationID());
			insert.bindString(2, temp.getStartTime());
			insert.bindLong(3, temp.getForecastHour());
			insert.bindLong(4, temp.getScale());
			insert.bindDouble(5, temp.getTemperatureValue());
			insert.executeInsert();
		}

		mDB.setTransactionSuccessful();
	} catch (SQLException sqle) {
		sqle.printStackTrace();
	} finally {
		mDB.endTransaction();
	}

	return true;
}
 

public boolean addCurrentConditionArrayList(ArrayList<CbWeather> weather) {

		
		try {
			mDB.beginTransaction();
		} catch(SQLiteDatabaseLockedException sqldble) {
			// This try/catch block is a bad hack. Refactor db usaage to use only one lock
			// regardless of the thread
			
		}
		
		String insertSQL = "INSERT INTO "
				+ CURRENT_CONDITIONS_TABLE
				+ " ("
				+ KEY_LATITUDE
				+ ", "
				+ KEY_LONGITUDE
				+ ", "
				+ KEY_ALTITUDE
				+ ", "
				+ KEY_ACCURACY
				+ ", "
				+ KEY_PROVIDER
				+ ", "
				+ KEY_SHARING
				+ ", "
				+ KEY_TIME
				+ ", "
				+ KEY_TIMEZONE
				+ ", "
				+ KEY_USERID
				+ ", "
				+ KEY_GENERAL_CONDITION
				+ ", "
				+ KEY_WINDY
				+ ", "
				+ KEY_FOGGY
				+ ", "
				+ KEY_CLOUD_TYPE
				+ ", "
				+ KEY_PRECIPITATION_TYPE
				+ ", "
				+ KEY_PRECIPITATION_AMOUNT
				+ ", "
				+ KEY_PRECIPITATION_UNIT
				+ ", "
				+ KEY_THUNDERSTORM_INTENSITY
				+ ", "
				+ KEY_USER_COMMENT
				+ ") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		try {
			SQLiteStatement insert = mDB.compileStatement(insertSQL);
			for (CbWeather weatherItem : weather) {
				CbCurrentCondition condition = (CbCurrentCondition) weatherItem;
				insert.bindDouble(1, condition.getLocation().getLatitude());
				insert.bindDouble(2, condition.getLocation().getLongitude());
				insert.bindDouble(3, condition.getLocation().getAltitude());
				insert.bindDouble(4, condition.getLocation().getAccuracy());
				insert.bindString(5, condition.getLocation().getProvider());
				insert.bindString(6, condition.getSharing_policy());
				insert.bindLong(7, condition.getTime());
				insert.bindLong(8, condition.getTzoffset());
				insert.bindString(9, condition.getUser_id());
				insert.bindString(10, condition.getGeneral_condition());
				insert.bindString(11, condition.getWindy());
				insert.bindString(12, condition.getFog_thickness());
				insert.bindString(13, condition.getCloud_type());
				insert.bindString(14, condition.getPrecipitation_type());
				insert.bindDouble(15, condition.getPrecipitation_amount());
				insert.bindString(16, condition.getPrecipitation_unit());
				insert.bindString(17, condition.getThunderstorm_intensity());
				insert.bindString(18, condition.getUser_comment());
				insert.executeInsert();
			}

			mDB.setTransactionSuccessful();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} finally {
			mDB.endTransaction();
		}

		return true;
	}
 

public long bulkInsert(String table, List<String[]> values)
{
    Log.d("TIMOSENSOR", "BULK INSERT " + values.size());

    long result = -1;

    if(values.size() == 0) {
        return result;
    }

    String sql = "INSERT OR IGNORE INTO " + table + " (";
    String qMarks = "";
    for(String name : values.get(0)) {
        sql += name + ",";
        qMarks += "?,";
    }

    sql = sql.substring(0, sql.length() - 1);
    sql += ") values (" + qMarks.substring(0, qMarks.length() - 1) + ");";

    SQLiteDatabase database = databaseHelper.getWritableDatabase();
    database.beginTransaction();
    SQLiteStatement insert = database.compileStatement(sql);
    for(int i = 1; i < values.size(); i++) {
        String[] value = values.get(i);

        for(int j = 1; j <= value.length; j++) {
            insert.bindString(j, value[j - 1]);
        }
        result = insert.executeInsert();
    }
    database.setTransactionSuccessful();
    database.endTransaction();

    Log.d("TIMOSENSOR", "BULK INSERT RESULT " + result);

    List<String[]> abc = query("SELECT * FROM " + table + " LIMIT " + Settings.WEARTRANSFERSIZE, null, true);

    Log.d("TIMOSENSOR", "BULK INSERT TEST " + abc.size());

    return result;
}
 

/**
 * Add multiple leq records
 * @param leqBatches List of Leq to add
 */
public void addLeqBatches(List<LeqBatch> leqBatches) {
    SQLiteDatabase database = storage.getWritableDatabase();
    try {
        database.beginTransaction();
        SQLiteStatement leqStatement = database.compileStatement(
                "INSERT INTO " + Storage.Leq.TABLE_NAME + "(" +
                        Storage.Leq.COLUMN_RECORD_ID + "," +
                        Storage.Leq.COLUMN_LEQ_UTC + "," +
                        Storage.Leq.COLUMN_LATITUDE  + "," +
                        Storage.Leq.COLUMN_LONGITUDE  + "," +
                        Storage.Leq.COLUMN_ALTITUDE  + "," +
                        Storage.Leq.COLUMN_ACCURACY  + "," +
                        Storage.Leq.COLUMN_LOCATION_UTC + "," +
                        Storage.Leq.COLUMN_SPEED + "," +
                        Storage.Leq.COLUMN_BEARING +
                        ") VALUES (?, ?,?,?,?,?,?,?,?)");
        SQLiteStatement leqValueStatement = database.compileStatement("INSERT INTO " +
                Storage.LeqValue.TABLE_NAME + " VALUES (?,?,?)");
        for(LeqBatch leqBatch : leqBatches) {
            Storage.Leq leq = leqBatch.getLeq();
            leqStatement.clearBindings();
            leqStatement.bindLong(1, leq.getRecordId());
            leqStatement.bindLong(2, leq.getLeqUtc());
            leqStatement.bindDouble(3, leq.getLatitude());
            leqStatement.bindDouble(4, leq.getLongitude());
            if(leq.getAltitude() != null) {
                leqStatement.bindDouble(5, leq.getAltitude());
            } else {
                leqStatement.bindNull(5);
            }
            leqStatement.bindDouble(6, leq.getAccuracy());
            leqStatement.bindDouble(7, leq.getLocationUTC());
            if(leq.getSpeed() != null) {
                leqStatement.bindDouble(8, leq.getSpeed());
            } else {
                leqStatement.bindNull(8);
            }
            if(leq.getBearing() != null) {
                leqStatement.bindDouble(9, leq.getBearing());
            } else {
                leqStatement.bindNull(9);
            }
            long leqId = leqStatement.executeInsert();
            for(Storage.LeqValue leqValue : leqBatch.getLeqValues()) {
                leqValueStatement.clearBindings();
                leqValueStatement.bindLong(1, leqId);
                leqValueStatement.bindLong(2, leqValue.getFrequency());
                leqValueStatement.bindDouble(3, leqValue.getSpl());
                leqValueStatement.execute();
            }
        }
        database.setTransactionSuccessful();
        database.endTransaction();
    } finally {
        database.close();
    }
}
 

public void updateRecordUserInput(int recordId, String description, Short pleasantness,
                                  String[] tags, Uri photo_uri, String noisePartyTag) {

    SQLiteDatabase database = storage.getWritableDatabase();
    try {
        database.beginTransaction();
        SQLiteStatement recordStatement = database.compileStatement(
                "UPDATE " + Storage.Record.TABLE_NAME +
                        " SET "+ Storage.Record.COLUMN_DESCRIPTION + " = ?, " +
                        Storage.Record.COLUMN_PLEASANTNESS + " = ?, " +
                        Storage.Record.COLUMN_PHOTO_URI + " = ?, " +
                        Storage.Record.COLUMN_NOISEPARTY_TAG + " = ?" +
                        " WHERE " + Storage.Record.COLUMN_ID + " = ?");
        recordStatement.clearBindings();
        recordStatement.bindString(1, description);
        if(pleasantness != null) {
            recordStatement.bindLong(2, pleasantness);
        } else {
            recordStatement.bindNull(2);
        }
        recordStatement.bindString(3, photo_uri != null ? photo_uri.toString() : "");
        recordStatement.bindString(4, noisePartyTag);
        recordStatement.bindLong(5, recordId);
        recordStatement.executeUpdateDelete();
        database.delete(Storage.RecordTag.TABLE_NAME,
                Storage.RecordTag.COLUMN_RECORD_ID + " = ?" +
                        "", new String[] {String.valueOf(recordId)});
        SQLiteStatement tagStatement = database.compileStatement(
                "INSERT INTO " + Storage.RecordTag.TABLE_NAME +
                        "(" + Storage.RecordTag.COLUMN_RECORD_ID + ", " +
                        Storage.RecordTag.COLUMN_TAG_SYSTEM_NAME + ") " +
                        " VALUES (?, ?)");
        for(String sysTag : tags) {
            tagStatement.clearBindings();
            tagStatement.bindLong(1, recordId);
            tagStatement.bindString(2, sysTag);
            tagStatement.executeInsert();
        }
        database.setTransactionSuccessful();
        database.endTransaction();
    } finally {
        database.close();
    }
}