Monday, May 16, 2011

Oracle SCA Spring Bean With Own Persistence

My last blogs are around the Oracle SCA Spring Bean basics illustrated with a calculator example. We already implemented the four basic forms of calculation by own Spring Bean implementations which are wired on the Calculator Bean (bottom-up blog, top-down blog). Additionally we called an EJB for the square and square root operation (blog).
Composite

But good calculators have the most familiar trigonometric functions sine, cosine, and tangent on them. So we extend your Calculator Service example with these new mathematical operations.

But instead of using again the Math Java functions we utilize the Oracle Database for these functions. The implementation of mathematical function by using an Oracle Database is a kind of artificial, but I want to show the direct access of Spring Beans on the database.

This is a practice-relevant topic because Oracle SOA applications sometimes have to ignore the DB Adapter because of complex query creation, special result set handling or just because of performance reasons. But the “normal” way of accessing a database should use the matured DB Adapter (also easy usable from a Spring Bean).

Oracle SCA Spring Beans are running inside of the SCA Container on top of the WebLogic application server (we ignore IBM WebSphere on this example). The data source configuration with appropriate connection pooling happens on the WebLogic Application Server Console. Our Spring Bean should use the existing data source configuration instead of doing own data source configuration on the context configuration file.

The Spring Framework offers an easy way to do a JNDI lookup. The <jee:jndi-lookup> can be used to retrieve and expose JNDI objects. In our case we reuse the existing SOA/MDS data source configuration (jdbc/SOALocalTxDataSource). Real world use cases will do a lookup on own data source or multi data source configurations. The data source will be injected on the Calculator Bean (setter DI).
clip_image004

Inside of the bean implementation the easiest way to do JDBC calls is the usage of the JDBCTemplate (out-of-the-box implementation of the Template-Pattern). Without the abstraction of JdbcTemplate the usage of JDBC would be much more complex and error-prone. Therefore we create a JdbcTemplate instance on the data source setter method and passing the data source.
clip_image006

The JdbcTemplate offers several methods to execute SQL Statements. The methods execute(), query(), update() and batchUpdate() are available for all kind of DML commands. Our use case needs to do execute a select command therefore we use the query() method. If the query returns only one value from the database the queryForInt() or queryForLong() method could be used. Unfortunately Spring don’t offer a queryForDouble() method, so we have to use the more generic queryForObject() method which takes the expected return type as parameter.
clip_image008

In case that the select returns several values the queryForMap() has to be used. The map collection assigns to each column name a value. Finally when we retrieve several rows from the database the queryForList() method has to be used. The list collection contains for each row a map collection.
Finally test the new Calculator Service operations sine, cosine, and tangent (I’m using SOAPUI).
clip_image010

The source code is available as 7-zip here.

That’s it. We created the access on the database inside of a Spring Bean on the Oracle SCA Container. This blog only touches the Spring JDBC topic very briefly. For more details take a look at the Spring JDBC documentation.