A lot of developers are struggling with check functions stored in their database. This post will show a very easy and declarative way to reuse your existing database functions.
Lets assume we have a check function that returns a simple character as Y oder N based on it's validation outcome.
This function will guarantee that the parameter string will start with a Sir. This way we can create a validation that will only allow noble employees in our company ;)
The example is based on the HR schema and has only a EO for the Employees table, a VO based on this EO which has one instance in our EmployeesService AM.
To create our validation we need to get our hands on this DB function. Of course we can start to write our own prepared statement in EmployeesEOImpl.class. But we want to work more declarative and move more pixels. Easiest way to get the return value of our function is to create a SQL query with calls it.
Don't forget to add the named parameter at the query section of our VO.
This new VO can be access by our EO with an simple ViewAccessor. We don't fill the parameter of our checkNameVO within the accessor. This way the accessor can be reused by other attributes.
Now our setup is complete and we can create our groovy script based attribute validation. First we fill the parameter of our where clause and execute the query. We know that our statement will definitely return at most one row, but JDeveloper doesn't. So we have to get the Result attribute of the first element of our rowset.
This is a very easy way to reuse existing database functions for validating our entity object attributes. Be careful with this type of validation. This way we create a logical dependency between the result of an VO and an EO attribute. So you should consider where to place this type of VOs within your application structure.