Script units accessing database
From WebRatio WebML 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.
Contents |
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.
#input int categoryOID
import com.webratio.rtx.core.BeanHelper
import org.apache.commons.lang.math.NumberUtils
import com.webratio.rtx.db.DBTransaction
import com.webratio.rtx.db.HibernateService
def count = 0
def dbId = "db1"
def dbTransaction = localContext.get("com.webratio.rtx.TRANSACTION." + dbId + ".hibernate")
try {
/* if the transaction is null or the script unit is the first unit of the transaction */
if(dbTransaction == null){
def hbService = rtx.getService(dbId + ".hibernate", HibernateService.class)
dbTransaction = new DBTransaction(service.id, hbService.getSessionFactory().openSession())
localContext.put("com.webratio.rtx.TRANSACTION." + dbId + ".hibernate", dbTransaction)
}
def session = dbTransaction.getSession()
def query = session.createSQLQuery("SELECT count(*) FROM APP.PRODUCT WHERE CATEGORYOID=:categoryOID")
query.setParameter("categoryOID", categoryOID)
count = query.uniqueResult()
} catch(Throwable e) {
}
if (count != 0) {
count--
}
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:
#input int categoryOID
import com.webratio.rtx.core.BeanHelper
import org.apache.commons.lang.math.NumberUtils
import com.webratio.rtx.db.DBTransaction
import com.webratio.rtx.db.HibernateService
def count = 0
def dbId = "db1"
def session = null
try {
def hbService = rtx.getService(dbId + ".hibernate", HibernateService.class)
session = hbService.getSessionFactory().openSession()
def query = session.createSQLQuery("SELECT count(*) FROM APP.PRODUCT WHERE CATEGORYOID=:categoryOID")
query.setParameter("categoryOID", categoryOID)
count = query.uniqueResult()
} catch(Throwable e) {
} finally{
/* closes quietly the hibernate session */
executeQuietly{session.close()}
}
if (count != 0) {
count--
}
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).
WebRatio 5.1.X Improvements
In WebRatio 5.1 there is a new simple way to access the database using the Script Unit. This section reports the Groovy Script examples shown for WebRatio 5.0 modified according to the new feature. Note that the code you are going to use is the same wherever the Script Unit is placed (in or outside transactions). The previous version of the code still works in this WebRatio version. Here are the improvements available:
- retrieving of the Hibernate session using a single line of code
def dbId = "db1" def hbSession = getDBSession(dbId)
- availability of two methods to perform commit or rollback. These methods allows you to control the result of the Script Unit operations. If the Script Units operates in transaction these methods do not have immediate effects. Moreover they are not mandatory. If you do not use them in your script, the unit decide itself which is the operation to do according to the results of the code executed.
commit(hbSession) rollback(hbSession)
- availability of a variable that states if the unit is working in a transaction or not
inTransaction
The Groovy Script becomes the same in or out of transaction and it is the following:
#input int categoryOID
import com.webratio.rtx.core.BeanHelper
import org.apache.commons.lang.math.NumberUtils
import com.webratio.rtx.db.DBTransaction
import com.webratio.rtx.db.HibernateService
def count = 0
def dbId = "db1"
def session = getDBSession(dbId)
def query = session.createSQLQuery("SELECT count(*) FROM APP.PRODUCT WHERE CATEGORYOID=:categoryOID")
query.setParameter("categoryOID", categoryOID)
count = query.uniqueResult()
if (count != 0) {
count--
}
return count
The main differences from the previous version are:
- the way to retrieve the Hibernate session
- it's possible to remove the try-catch-finally block since the session is managed automatically and you do not need to manage it
Deleting data from the database
Suppose that we want to add to our application the functionality to delete stores, using a Script Unit (as in the model shown below):
To accomplish this we can use the following script:
#input int storeOID
import com.webratio.rtx.core.BeanHelper
import org.apache.commons.lang.math.NumberUtils
/* delete the store */
def dbId = "db1"
def session = getDBSession(dbId)
def query = session.createQuery("delete from Store where OID=:storeOID")
query.setParameter("storeOID", storeOID)
query.executeUpdate()
commit(session)
Adding data to the database
Using Script Unit it's possible to add data to the database. In our example we want to add categories (refer to the following model):
The following script implements the requested functionality:
#input String newCategory import com.webratio.rtx.core.BeanHelper import com.webratio.webapp.Category def dbId = "db1" def session = getDBSession(dbId) Category category = new Category() category.setCategory(newCategory) session.save(category) commit(session)
Note that we don't have to manage the oid explicitly, since it's auto-generated.
In this last example we use the "Category" Java class generated by WebRatio that represents the "Category" entity of the Data Model. The code of the class representing an entity can be reached by right clicking on an entity in the Data Model and choosing "Open Generated Code" -> "Java Class". These classes are stored in the web application folder, in WEB-INF\classes\com\webratio\webapp.
Download the example
Click on the following link to download a sample project containing all the resources to test this article.
Acme - Accessing DB Scripts Project
