How to fetch Data from BDM using a Specific Criteria
Hello Community
I am trying to Implement a scenario . I have stored user name, emails and Password expiry date in my Database.
Now Bonita process will Search the Database and for those where the Expiry date is 15 days from current date, the Application will send an email to User notifying about the Expiry date.
Please help in implementing this.
1) Could we use a JPQL query to return a List of Dates ? But we cannot assign this output to Process variable. Nor can we assign the output to BDM Object variable.
2) Can we use a groovy script to fetch this Data.? If Yes, please suggest some sample script to perform the same.
Any suggestions will be helpful
Thanks
Hi Dibyajit.Roy,
I do not see why you could not define a custom query in your business object that would return precisely all the entries with a password expiring before a given date.
Consider the following business object, for example:
You can define a custom query that will do precisely that:
Then, in your process/client, you may just call the custom query "findPwdToExpire" to retrieve the list of UserAccountObject instances with a password expiring before a given date.
- For example, using the REST API:
../API/bdm/businessData/com.company.model.UserAccountObject?q=findPwdToExpire&p=0&c=10&f=expirationDate={{expirationDate}}
where {{expirationDate}} is a ISO 8601 formatted String, e.g.: 2019-02-24T15:05:06.896Z
- Or using the DAO of the business object:
List<UserAccountObject> userAccountsAboutToExpire = userAccountObjectDAO.findPwdToExpire((LocalDate) expirationDate, startIndex, maxResults);
I hope this helps.
Regards,
Unai
Comments
Thank you Unai.
I have considered using a custom query . Its just that I am not able to format it in a way to get desired output.
I am not sure about Rest Api as this will be a complete service task Process. There will be no Human Intervention.
Calling Rest Api in Connector or Groovy Script is a challenge for me.
I will reconsider the Custom Query Idea. Its seems the easy way to Proceed.
Alternately, I did create a custom connector for Oracle 11g Database.
For H2 , it works fine. I am able to get the desired output. But my Database in Production is Oracle 11g. I cannot test my connector or Connection String. I have to manually update the details and check the Process directly in production and I dont see any Output. Hence the Roadblock.
I will try both approach. If I find a solution, I will add it here .
P.S - I cannot see the attached image. Perhaps there is a way to see them in a different page or Portal like for image share sites.
Regards
Hi Unai
I was able to create a JPQL which compares Expiry Date with Current timestamp and returns data items.
If I run the query as REST API, then I get the Desired output.
But If I use the DAO object , then it returns com.company.model.TableObject and not data items as the REST API.
Could you suggest how I can get the output in DAO.
Thanks
Hi Dibyajit,
I don't quite understand what you mean by:
The query from the DAO object should return a list of objects complying with the search criteria.
If what you want is to have the result as JSON data, then you simply need to convert your list of objects into JSON. (See for example: Jackson 2 – Convert Java Object to / from JSON, Converting Java objects to JSON with Jackson, ...)
Does this answer your question?
Regards,
Unai
Hi Unai
The REST API is http://localhost:8080/bonita/API/bdm/businessData/com.vodafone.qa.model....
and the output is
[{"persistenceId":1,"persistenceId_string":"1","persistenceVersion":0,"persistenceVersion_string":"0","Name":"walter.bates","requestid":0,"requestid_string":"0","processid":0,"processid_string":"0","emailid":"walter.bates","expirydate":1550637000000},{"persistenceId":2,"persistenceId_string":"2","persistenceVersion":0,"persistenceVersion_string":"0","Name":"helen kelly","requestid":0,"requestid_string":"0","processid":0,"processid_string":"0","emailid":"helen.kelly","expirydate":1550341800000}The DAO script is
List datavar = rFNotificationDAO.query1(0, 10);
return datavar ;
The output is
com.vodafone.qa.model.RFNotification@29e01cc9, com.vodafone.qa.model.RFNotification@e4141577, com.vodafone.qa.model.RFNotification@5088829c]
Just to add, my custom query is
SELECT r
FROM RFNotification r
WHERE r.expirydate < CURRENT_TIMESTAMP
I am not able to use column name as its giving an error saying output type not compatible with string type.
Hence I have to use SELECT r.
My plan was to try and use something like SELECT r.emailId
OK, I got it to work. Here is the small script change.
def myUserList = []
def emailsIds;
//myUserList.add({currentPCPTable1Input ->
def dataVar = rFNotificationDAO.findByFormat("20022019", 0, 10);
for(item in dataVar)
{
myUserList.add(item.emailid);
}
return myUserList;
Hi Dibyajit,
Excuse me for getting back to you so late. I am glad to see you got your script working.
Indeed, as I was telling you in my last comment, you custom query method in your object's DAO returns a list of that object:
Then, if you just wish to retrieve the list of the email ids, you simply do as you ended up doing:
Regards,
Unai
Thank you Unai