Urgent : Need help regarding update records by using where condition in Aerospike database

[vikas8910]

how to update specific records by using where condition in aerospike as we do in RDBMS database?

i am using version of Spring Data Aerospike is 2.4.0.RELEASE and crud repository.

Below is the update query which we use for updating record based on certain conditions in RDBMS database.

update T1
set locked = 1,
set instance_id = 'xyz'
where maturity <= now()

[roimenashe]

@vikas8910
There are few ways to perform an “update where” operation in Aerospike:

Spring Data Repository way:
Add a “findByMaturityLessThanEqual” query to your repository without any implementation (for more information about Spring Data Queries: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods),
Get the desired records using the query you have just defined, update the requested fields (locked and instance id) and use save() on the new modified objects, something like:

repository.findByMaturityLessThanEqual(now()).stream()
        .forEach(x -> {
            x.setLocked(1);
            x.setInstanceId("xyz");
            save(x);
        });

Note: in order to use findByMaturityLessThanEqual you will need a secondary index on maturity bin or to enable scans.

Aerospike Java Client way (Without Spring):
You can use Aerospike Query with Filter Expressions to scan for the requested records something like:

Statement stmt = new Statement();
stmt.setNamespace(args.namespace);
stmt.setSetName(setName);

QueryPolicy policy = new QueryPolicy();
policy.filterExp = Exp.build(
			Exp.le(Exp.intBin(“maturity”), Exp.val(now())));

RecordSet rs = client.query(policy, stmt);

try {
	while (rs.next()) {
		// update locked and instance
		// save()
	}
}
finally {
	rs.close();
}

You can also utilize Aerospike UDFs:
https://docs.aerospike.com/client/java/usage/query/query_udf

Btw, you're using 2.4.0.RELEASE which was released on July 2020, we recommend upgrading to the latest version of Spring Data Aerospike 3.3.1 if possible.

[vikas8910]

@vikas8910 There are few ways to perform an “update where” operation in Aerospike:

Spring Data Repository way: Add a “findByMaturityLessThanEqual” query to your repository without any implementation (for more information about Spring Data Queries: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods), Get the desired records using the query you have just defined, update the requested fields (locked and instance id) and use save() on the new modified objects, something like:

repository.findByMaturityLessThanEqual(now()).stream()
        .forEach(x -> {
            x.setLocked(1);
            x.setInstanceId("xyz");
            save(x);
        });

Note: in order to use findByMaturityLessThanEqual you will need a secondary index on maturity bin or to enable scans.

Aerospike Java Client way (Without Spring): You can use Aerospike Query with Filter Expressions to scan for the requested records something like:

Statement stmt = new Statement();
stmt.setNamespace(args.namespace);
stmt.setSetName(setName);

QueryPolicy policy = new QueryPolicy();
policy.filterExp = Exp.build(
			Exp.le(Exp.intBin(“maturity”), Exp.val(now())));

RecordSet rs = client.query(policy, stmt);

try {
	while (rs.next()) {
		// update locked and instance
		// save()
	}
}
finally {
	rs.close();
}

You can also utilize Aerospike UDFs: https://docs.aerospike.com/client/java/usage/query/query_udf

Btw, you're using 2.4.0.RELEASE which was released on July 2020, we recommend upgrading to the latest version of Spring Data Aerospike 3.3.1 if possible.

**Thanks for help!!!

is it possible to update records for below RDBMS query by using Spring Data Repository way;**

update T1
set locked = 1,
instance_id = 'xyz',
timestamp= sysdate
where
(maturity <= sysdate() and locked = 0 or
maturity < sysdate() and locked = 1 and TIMEDIFF(sysdate,timestamp) >= 60)

[roimenashe]

@vikas8910 You're welcome!
For complex queries I would suggest to use the Aerospike Java Client with Filter Expressions or to use the Query Engine of Spring Data Aerospike directly (once you configure Spring Data Aerospike you have the client and query engine beans created).

A good example for Query Engine complex query usage can be found here:
https://github.com/aerospike-community/spring-data-aerospike/blob/f701aeb8c82b4c096e5ee1a839c6ce8249b7f661/src/test/java/org/springframework/data/aerospike/query/QualifierTests.java#L610

[vikas8910]

@vikas8910 You're welcome! For complex queries I would suggest to use the Aerospike Java Client with Filter Expressions or to use the Query Engine of Spring Data Aerospike directly (once you configure Spring Data Aerospike you have the client and query engine beans created).

A good example for Query Engine complex query usage can be found here:

https://github.com/aerospike-community/spring-data-aerospike/blob/f701aeb8c82b4c096e5ee1a839c6ce8249b7f661/src/test/java/org/springframework/data/aerospike/query/QualifierTests.java#L610

Can i do update operation using Query Engine? is it only for select clause? (selectWithBetweenAndOrQualifiers)

[roimenashe]

@vikas8910
You can only select using Query Engine/Scan with Filter Expressions, you get the requested records and then you can update each of them.
"batch/bulk updates" is not yet supported in Aerospike.

[vikas8910]

Okay....thanks for your help :) 👍