DEV Community

AWS Community Builders profile image Vadym Kazulkin
Vadym Kazulkin for AWS Community Builders

Posted on • Edited on

Data API for Amazon Aurora Serverless v2 with AWS SDK for Java - Part 2 Executing SQL statements

#aws #serverless #java #database

Introduction

In part 1, we set up the sample application, which has API Gateway in front of Lambda functions that communicate with Aurora Serverless v2 PostgreSQL database via Data API to create the products and retrieve them (by id). In this part, we'll dive deeper into the new Data API for Aurora Serverless v2 itself and its capabilities, like executing SQL Statements, and we will use AWS DK for Java (of course) for it. Here you can find the sample project.

Data API for Aurora Serverless v2 executing SQL Statements

To use the new Data API, we need to include the following dependency in the pom.xml

<dependency>
   <groupId>software.amazon.awssdk</groupId>
   <artifactId>rdsdata</artifactId>
</dependency>
Enter fullscreen mode Exit fullscreen mode

In this example, we retrieve the product by its id, see the definition of the Lambda function GetProductByIdViaAuroraServerlessV2DataApi
Here is the complete source code of the Lambda function GetProductByIdViaAuroraServerlessV2DataApiHandler.

As the first step, we need to build RdsDataClient like this:

RdsDataClient rdsDataClient = RdsDataClient.builder().build();
Enter fullscreen mode Exit fullscreen mode

Then we need to build ExecuteStatementRequest, for example, like this:

final String id = event.getPathParameters().get("id");
final String dbClusterArn=System.getenv("DB_CLUSTER_ARN");
final String dbSecretStoreArn=System.getenv("DB_CRED_SECRETS_STORE_ARN");
final String sql="select id, name, price from products where id="+id;

final ExecuteStatementRequest request= ExecuteStatementRequest.builder().database("").
                        resourceArn(dbClusterArn).
                        secretArn(dbSecretStoreArn).
                        sql(sql).
                        build();
Enter fullscreen mode Exit fullscreen mode

We pass Aurora DB Cluster ARN and Secret Manager ARN, which we received through the environment variables via the SAM template, and we first don't use any prepared statement.

To execute the statement, we need to invoke

final ExecuteStatementResponse response= rdsDataClient.executeStatement(request);
Enter fullscreen mode Exit fullscreen mode

Then we need to work through the response records. Each of them, in case the execute statement response isn't empty, consists of a list of fields, from which we get the individual typed (String, Integer) values by index, which matches the field position in the SQL query, starting from 0. In our example, as we select a product by id we know that records can have maximal 1 element.

final List<List<Field>> records=response.records();
if (records.isEmpty()) return Optional.empty();

final List<Field> fields= records.get(0);
final String name= fields.get(1).stringValue(); 
final BigDecimal price= new BigDecimal(fields.get(2).stringValue());

final Product product = new Product(id, name, price);
return Optional.of(product);
Enter fullscreen mode Exit fullscreen mode

If we construct ExecuteStatementRequest with additionally formatRecordsAs(RecordsFormatType.JSON)

final ExecuteStatementRequest request= ExecuteStatementRequest.builder().database("").
                        resourceArn(dbClusterArn).
                        secretArn(dbSecretStoreArn).
                        sql(sql).
   formatRecordsAs(RecordsFormatType.JSON).
                        build();
Enter fullscreen mode Exit fullscreen mode

and then execute the statement, and then invoke response.formattedRecords() we get a string value that represents the result set of a SELECT statement in JSON format.

final ExecuteStatementResponse response= rdsDataClient.executeStatement(request);
Final String responseAsJson=response.formattedRecords();
Enter fullscreen mode Exit fullscreen mode

We can then easily convert this string value into an array of Products using libraries like Jackson.

Now let's look into how to construct the Prepared Statement using the Data API to be protected against SQL injection attacks. For this, we need to adjust the ExecuteStatementRequest and use the SqlParameter abstraction.

final String sql="select id, name, price from products where id=:id";

final SqlParameter sqlParam= SqlParameter.builder().name("id").value(Field.builder().longValue(Long.valueOf(id)).build()).build();

final ExecuteStatementRequest request= ExecuteStatementRequest.builder().database("").
                        resourceArn(dbClusterArn).
                        secretArn(dbSecretStoreArn).
                        sql(sql).
                        parameters(sqlParam).
                        build();
Enter fullscreen mode Exit fullscreen mode

In the WHERE section of the SQL Statement, we define our parameter where id=:id and build a Sql Parameter object with the field with the name id (which matches the parameter name in the SQL statement) of the type Long, passing its value dynamically like this :

SqlParameter.builder().name("id").value(Field.builder().
longValue(Long.valueOf(id)).build()).build();
Enter fullscreen mode Exit fullscreen mode

Finally, we pass the created SqlParameter object (or the collection of SqlParameters) to the ExecuteStatementRequest builder by invoking the parameter method like this .parameters(sqlParam). Dealing with the ExecuteStatementResponse remains the same.

Conclusion

In this article, we introduced the new Data API for Aurora Serverless v2 itself and its capabilities using normal and prepared SQL statements and getting responses in different formats, such as a record of fields or a JSON array. Of course, Data API offers more, like running a batch SQL statement over an array of data for bulk update and insert operations. A batch SQL statement can provide a significant performance improvement over individual insert and update statements. We'll look at this capability of the Data API in the next part of the series.

Top comments (0)