Script units accessing database
From Wiki
The typical usage of the script unit is for transforming a set of input parameters into a set of output parameters, but it can be used also to access database and work with its instances.
We have to consider two cases:
- The Script Unit is part of a transaction
- The Script Unit isn't part of a transaction
This article wants to give you an example on how to retrieve the Hibernate session and execute queries in the unit script.
Script unit placed in transactions
If the Script Unit is in a transaction, it is possible to retrieve the started transaction by the local context.
For instance consider the Acme project. Suppose we want to know how many products belong to the category of the selected product, in addition to the selected one. This operation can be simply done with a derived attribute, but in this case suppose that you can't modify the database structure. Your site view can appear as in the image below.
1: //inputs=categoryOIDs
2:
3: import com.webratio.rtx.core.BeanHelper
4: import org.apache.commons.lang.math.NumberUtils
5: import com.webratio.rtx.db.DBTransaction
6: import com.webratio.rtx.db.HibernateService
7:
8: def count = 0
9:
10: /* transforms the input array in the integer categoryOID */
11: def category = BeanHelper.asString(categoryOIDs)
12: def categoryOID = NumberUtils.toInt(category)
13:
14: def dbId = "db1"
15: def dbTransaction = localContext.get("com.webratio.rtx.TRANSACTION." + dbId + ".hibernate")
16: try {
17:
18: /* if the transaction is null or the script unit is the first unit of the transaction */
19: if(dbTransaction == null){
20: def hbService = rtx.getService(dbId + ".hibernate", HibernateService.class)
21: dbTransaction = new DBTransaction(service.id, hbService.getSessionFactory().openSession())
22: localContext.put("com.webratio.rtx.TRANSACTION." + dbId + ".hibernate", dbTransaction)
23: }
24: def session = dbTransaction.getSession()
25: def query = session.createSQLQuery("SELECT count(*) FROM APP.PRODUCT WHERE CATEGORYOID=:categoryOID")
26: query.setParameter("categoryOID", categoryOID)
27: count = query.uniqueResult()
28: } catch(Throwable e) {
29:
30: }
31: if (count != 0) {
32: count--
33: }
34: return count
- Defines the inputs of the script (line 1).
- The output of a Selector Unit is an array of objects. Therefore we have to extract the string value of the category OID and transform it into an int value (line 11-12).
- The localContext is a map of name-to-object bindings, preloaded with values of parameters. It is an implicit variable (line 15).
- The rtx is the shared runtime manager. It is an implicit variable (line 20).
- If the Script Unit is the first unit of the transaction, it must start the transaction and open the session. Moreover it is necessary to put the transaction in the localContext map, so that following units can retrieve it from the local context (lines 19-23).
- Obtain the opened session from the transaction (line 24).
- Query the database with the SQL query and obtain the count result (lines 25-27).
- Return the result (line 34).
Script units not in transactions
Suppose that your script unit is placed in the page and it is used to show the result. Your site view can appear as follows:
As in the previous case we want to know how many products the category contains in addition to the selected one. The script can be:
1: //inputs=categoryOIDs
2:
3: import com.webratio.rtx.core.BeanHelper
4: import org.apache.commons.lang.math.NumberUtils
5: import com.webratio.rtx.db.DBTransaction
6: import com.webratio.rtx.db.HibernateService
7:
8: def count = 0
9:
10: /* transforms the input array in the integer categoryOID */
11: def category = BeanHelper.asString(categoryOIDs)
12: def categoryOID = NumberUtils.toInt(category)
13: def dbId = "db1"
14: def session = null
15: try {
16: def hbService = rtx.getService(dbId + ".hibernate", HibernateService.class)
17: session = hbService.getSessionFactory().openSession()
18: def query = session.createSQLQuery("SELECT count(*) FROM APP.PRODUCT WHERE CATEGORYOID=:categoryOID")
19: query.setParameter("categoryOID", categoryOID)
20: count = query.uniqueResult()
21: } catch(Throwable e) {
22:
23: } finally{
24:
25: /* closes quietly the hibernate session */
26: executeQuietly{session.close()}
27: }
28: if (count != 0) {
29: count--
30: }
31: return count
- Defines the inputs of the script (line 1).
- The output of a Selector Unit is an array of objects. Therefore we have to extract the string value of the category OID and transform it into an int value (line 11-12).
- The rtx is the shared runtime manager. It is an implicit variable (line 16).
- Obtain a new hibernate session (line 17).
- Query the database with the SQL query and obtain the count result (lines 18-20).
- Finally close the session (line 26).
- Return the result (line 31).
Download the example
Click on the following link to download a sample project containing all the resources to test this article.
