SAP Tutorial: Complete CAP Java Part 10
CQN Analyzers, extracting values from CQN statements, saving to the database, and returning responses
Contents
- Previous: Handler Setup, Event Lifecycle Phases, and Context Objects
- Current: CQN Analyzers, extracting values from CQN statements, saving to the database, and returning responses
- Next: Adding a custom action to a Fiori Elements page, problems with the sample app’s implementation
You’ve made it to Part 10 of The Complete CAP Java tutorial series, where I’m showing you step by step how to rebuild the SAP’s CAP Java sample application. We’re currently working a page where users can browse the books of a bookshop, and specifically we’re now implementing a function to allow the users to add a rating for a book. The code for this app can be found here. So far in implementing this action we have defined it in CDS and set up a basic event handler to carry out the logic. Be sure to check out the earlier posts in this series to catch up on what we’ve been doing. For now, let’s jump into the code and finish up our event handler.
Step 1: Setting Up a CQN Analyzer
Last time we extracted the data from the context object and placed it in a new instance of the Review interface. However, we can’t simply save it yet because we don’t have the ID of the parent book, and without that we can’t associate the review to that book. However, we can extract that value from the context object’s CQN.
CQN or CDS Query Notation is a CAP’s way of abstracting data queries away from individual dialects of SQL (best support is for SQLite and HANA, but Postgres is also supported with some limitations) into a common notation that allows a single query without having to worry about the underlying database. This is useful because you can switch from one database to another (SQLite for development, HANA for production) without having to change the code; however, it means everyone involved in developments needs to learn yet another query notation. So anyway, let’s learn a little about it together.
First, let’s extract the CQN using the context object’s getCqn method and print the result to the console so we can get a good look at it.
As you can see, this CQN resembles the syntax of a SQL statement, but the different parts have been separated out in arrays and objects so they can be handled easily by the framework; CQN can also be written as a string like regular SQL and then parsed into this form. For more info on CQN, check this documentation.
In any case, inside of this CQN we can clearly see the book ID. But how do we actually extract the value? If we check VS Code to find the output type of the getCqn method, we can see that it’s CqnSelect. We can go to javadoc.io to examine the details about this type and find quite a few built-in methods that seem promising, but try as you might actually getting the value this way is not possible. Rather, we have to use CqnAnalyzer to extract the ID.
There are several ways to set up a CqnAnalyzer, but an ideal way is to initialize an instance of it in the Event Handler class’ constructor, though we can in fact initialize it directly in the function if we wanted to. However, since we will reuse this analyzer repeatedly throughout this handler, rather than instantiate it every time the method is called we’ll just instantiate it when the event handler itself is instantiated when the app is started. This is achieved in the following way:
To explain the above, first we import CqnAnalyzer and CdsModel. Next we declare the analyzer type and variable name. Finally we set up the constructor function. When instantiating the handler, CAP will pass in the metadata about the model for this service, so we specify an input called model of type CdsModel. We then create an instance of CqnAnalyzer and assign it to the variable we declared above. Notice that we pass in the model to CqnAnalyzer.create(), which provides the analyzer with the necessary metadata it needs to properly analyze the CQN generated by and for this service. Now that we have it we can start using it to analyze the CQN that we extracted above. Let’s do that now.
Step 2: Extracting the ID from the CQN Using the CqnAnalyzer
Next let’s explore the analyzer a little bit to learn how we can use it to extract information from the CQN. Let’s take our extracted CQN and put in in a variable (it’s type is CqnSelect), then let’s use the analyzer’s main method, analyze and pass in the CQN as an input, then let’s print that to the console to see what we get.
It looks the same printed as it did before, but now the analyzer has provided us with a whole new list of methods to work with that we didn’t have access to when it was simply a CqnSelect. You can check out the full list of them here. For now, what we care about is the targetKeys method.
Look at that! It’s our ID. And since it’s a Map we know we can easily extract the value we’re looking for by just using the get method while passing ‘ID’ in as a String, but let’s not forget all the generated files that CAP provided for us. We’re expecting a book’s ID so we can extract the value as follows:
That’s our book ID. Now we can finally assign it to our new review. Let’s do that.
And there you have it! We’re ready to actually insert our new review into the database. We’ll talk about that in the next section. For now, let’s just sit back and enjoy the fact that we now know how to extract values from a CQN statement. There are many cases where we might need to do this, and not just to extract IDs, so I encourage you to experiment with the CqnAnalyzer more and see if you can figure out how to extract the other values from our CQN. But for now, let’s move on to the database.
Step 3: Saving the Review to the Database
In an earlier post in this series I mentioned that a service handler like what we are creating now is known as an application service in CAP, and that there are several other types of services as well (refer to this documentation to learn about the others). When it comes to interacting with the application’s database, we need to interact with a persistence service, which are services that handle interaction with databases. Since CAP comes preconfigured to work with SQLite (which CAP sets up by default and which we are currently using) and HANA, we don’t have to worry about actually making persistence services— we can use them just as they are.
Let’s instantiate our persistence service so we can use it to insert our new book. This is done just like we did for the CQN analyzer — CAP will inject the persistence service automatically.
Next we have to provide the insert command to the service. As usual, CAP has it’s own syntax for this so let’s take a moment to break it down.
To execute any kind of operation on the database, we use the persistence service method run. The input to run is a CQN query generated by one of several of CAP’s query builders, not surprisingly named Delete, Insert, Select, Upsert, and Update. We use Insert here because of course we are inserting a new entry into the database.
Each of these query builders then features a method to specify the entity that we’re operating on. The name of the method is different depending on the query builder in order to make the code read like English. As we can see above Insert uses into but Delete would use from.
Next we need to provide the name of the entity. By now you must be familiar with this pattern, but we can get the name using the CDS_NAME property from the generated Reviews_ interface.
Next we choose either the entry method to indicate inserting a single entity or the entries method to insert an iterable of multiple entities. Here we use entry.
What these query builders actually do is generate CQN just like the CQN that we extracted from the context object above. We can actually print what it generates to the console to confirm that. Let’s do that.
We do have a problem, though: we’re still getting an error in the end and what’s more, we aren’t getting the Review object back, but typically we’d want to return that back to the frontend. In the next section we’ll do just that.
Step 4: Sending a Response Back to the Frontend
You might have noticed in the last section that after we ran our code, we still got an error, and what’s more if you tried to read all the reviews in the database to find the one we created, it wouldn’t be there. This is because if any error happens during the processing of a request — even after the insert statement has been run on the database — CAP will undo the changes. The request will only be committed in the database if nothing goes wrong. But what went wrong in our case? Let’s look at the message.
Exception marked the ChangeSet 5 as cancelled: Cannot invoke "com.sap.cds.Result.first()" because "result" is nullWhat this is telling us is that there is something called result and we’re calling a method called “first” on it and it’s result is null. We didn’t write anything like that so it’s a little confusing, but basically the context object contains a value called result which is an instance of Result, which is an iterable. Since our method is expected to return a single entity rather than an array, CAP will call Result.first() to extract that single entity so it can send it back to the front. (Recall that we specified in CDS two parts back in this series that we would be returning an instance of Reviews.) Since CAP is expecting that and it got nothing, it threw the error. Let’s fix that now.
First, we have to save the result of the database query in a variable. You can do that like this (you’ll need to import Result from com.sap.cds.Result):
Next we need to convert our Result to a Review or else we’ll get a type error since the return type is Reviews. Luckily Result provides with some methods that will easily convert our result to any type. The three I want to introduce now are list, first, and single. List will return our results as a list of the given class that we provide as an input. first and single will both return the first element from the Result as the given type, but single will throw an error if there is more than one item in the Result. It’s therefore the best one to use when you only expect one item to be returned from the database — you can catch some possible bugs this way.
Finally, instead of finishing the method with context.setCompleted(), which is used to tell CAP that the processing is done without providing any result (which will throw an error in our case because we specified one in the CDS), we’ll instead use the context object’s setResult method to pass the result back out.
Finally, we can test our code using REST Client and get the Review back with no errors:
We can also use the ID returned to us to confirm that the Review exists in the database.
### Get the new ReviewGET http://admin:admin@localhost:8080/api/browse/Reviews(<add your ID here>) HTTP/1.1
If you run that request, you’ll get the same result.
Now, before we finish up here, let’s clean up our code a little bit with some method-chaining:
That’s all for this week!
Conclusion
We’ve made it to the end of Part 10. Here we learned how to setup and use a CqnAnalyzer to extract values from a CQN statement, how to setup an instance of a persistenceService so we can interact with the database, how to insert a new entity into the database using that persistenceService, and finally how to return that result back to the caller. In the next section we’re going to finally implement the action as a button in our Fiori Elements app so that we can actually create a Review just with the click of a button! Stay tuned!
Was anything unclear in this tutorial? Leave a question below and I’ll get back to you as soon as possible. Was anything incorrect? Please leave a comment below and let me know (a source for the correction would be most helpful). Thanks for your comments!
Support
Did you like this blog? Want to make sure I can keep creating them? Then consider subscribing on Patreon!