I am the founder and CEO of Data Geekery GmbH, located in Zurich, Switzerland. With our company, we have been selling database products and services around Java and SQL since 2013. Ever since my Master's studies at EPFL in 2006, I have been fascinated by the interaction of Java and SQL. Most of this experience I have obtained in the Swiss E-Banking field through various variants (JDBC, Hibernate, mostly with Oracle). I am happy to share this knowledge at various conferences, JUGs, in-house presentations and on our blog. Lukas is a DZone MVB and is not an employee of DZone and has posted 255 posts at DZone. You can read more from them at their website. View Full User Profile

2011. A Great Year for Stored Procedures!

01.13.2011
| 11738 views |
  • submit to reddit

When I first started coding Oracle PL/SQL for my employer, I realised that I found myself back in medieval ages. A syntax reminding me of Ada and Pascal, ultra-strong typing, compilers that find compilation errors about 100'000 lines later, etc etc. I was young (OK I still am), full of visions and ideas, so this was about as hard as reality could get. Especially since I have friends working over at the Scala labs in Lausanne, Switzerland... developing for the "future", not the "past". I guess, today I would have joined this facebook group.

But I haven't. Actually, I have started to completely change my mind about those archaic pieces of logic deep down in the database. They're very much alive! In our company, we're using stored procedures as a means of interfacing various systems with our huge database (several billions of records in some tables). Actually, the database has become a middleware between our own software components. On one server (or from a script, etc) we call a stored procedure with a UDT message parameter. The procedure updates some tables and puts the UDT in an Oracle AQ. The AQ is consumed by another server. While this is not a "J2EE standard" architecture, it works very well and efficiently.

Stored procedure evolution

Check out this hilariously angry article from 2004. People have furiously hated stored procedures for a long time and with a lot of passion, and they still do today. But the stored procedures aren't gone. Au contraire, they're even more important today, as databases tend to hold more and more data that cannot be handled on the business tier as far as many operations are concerned. Check out "newer" databases such as MySQL, Postgres, H2, HSQLDB, etc. They all ship with their own stored procedure languages. So it's not just about the "archaic", "evil" monoliths provided by Oracle, IBM (DB2), or Microsoft.

So, where is this going?

In the IT-business, we live in a fast-paced world. None of us actually knows where we're going in the next 10 years. I'm not here to discuss that overly religious topic. Maybe I'm wrong about the stored procedure business. But I love them for their performance and closeness to the data they manipulate. And I think I share this love with many of you out there. The same applies for SQL. I'm not here to discuss the object-relational impedance mismatch either and whose fault it is etc... It's a boring and over-rated discussion. After all, we don't know where these things are heading either.

jOOQ

Instead I'm focusing on something else. I recently published an article on dzone and on the server side about jOOQ. I had lots of interesting, constructive, and motivating feedback, and a first committer! In the meantime, we have established a sound support for stored procedures (and UDT's) that integrate well into jOOQ's DSL. Because one of the most tiresome tasks when using stored procedures, is to call and map them from a higher-level language such as Java and JDBC.

Generate PROCEDURES as methods, PACKAGES as packages, UDTs as classes

Like many other persistence tools, jOOQ ships with code generation. It will also generate classes for your stored procedures and for your UDT's. Check out this PL/SQL example package:

CREATE OR REPLACE PACKAGE library AS

-- A procedure checking the existence of an author and
-- providing the result as an OUT parameter
PROCEDURE p_author_exists (author_name VARCHAR2, result OUT NUMBER);

-- A function checking the existence of an author and
-- providing the result as a RETURN value
FUNCTION f_author_exists (author_name VARCHAR2) RETURN NUMBER;
END library;

And its generated (simplified) Java representation:

public final class Library {

// Invoke the stored procedure on a connection. The return value
// represents the OUT parameter.
public static BigDecimal pAuthorExists(
Connection connection,
String authorName)
throws SQLException { ... }

// Invoke the stored function on a connection
public static BigDecimal fAuthorExists(
Connection connection,
String authorName)
throws SQLException { ... }

// Use the stored function as a Field in jOOQ's query DSL
public static Field<BigDecimal> fAuthorExists(String authorName) { ... }

// Use the stored function as a Field taking another field as parameter
public static Field<BigDecimal> fAuthorExists(Field<String> authorName) { ... }
}

Apart from calling stored functions and procedures directly from your Java application, you can also embed them in a jOOQ query like this:

// Let's say PERSONS is a table holding FIRST_NAME and LAST_NAME fields
// Then the following code will use those fields as generated Java objects
Factory create = new Factory(connection);
Field<BigDecimal> isAuthor = Library.fAuthorExists(LAST_NAME).as("is_author");

// Find persons born in 1981 and check whether they are authors
Result<?> result = create
.select(FIRST_NAME, LAST_NAME, isAuthor)
.from(PERSONS)
.where(YEAR_OF_BIRTH.equal(1981)).fetch();

for (Record record : result) {
System.out.println(
record.getValue(LAST_NAME) + " is an author : " +
record.getValue(isAuthor));
}

You can embed the stored function wherever you can in SQL, i.e. in SELECT clauses, in ORDER BY clauses, in GROUP BY clauses, etc. For example, you can filter the authors directly in the database, not in your Java code:

Result<?> result = create
.select(FIRST_NAME, LAST_NAME)
.from(PERSONS)
.where(YEAR_OF_BIRTH.equal(1981))
.and(Library.fAuthorExists(LAST_NAME).equal(1))
.fetch();

Now if you have UDT's in your stored procedure (which is a lot more common than having UDT's in tables), then you can have jOOQ map them easily to the Java world for you as well. Let's say you have this UDT:

CREATE TYPE u_address_type AS OBJECT (
street u_street_type, -- A nested UDT!
zip VARCHAR2(50),
city VARCHAR2(50),
country VARCHAR2(50)
)

And this UDT is used in the following standalone (i.e. not in a package) stored procedure:

CREATE OR REPLACE PROCEDURE p_check_address
(address IN OUT u_address_type);

Then, these pieces of Java code (simplified for the example) are generated for you:

public class UAddressTypeRecord extends UDTRecordImpl<UAddressTypeRecord> {

// The nested UDT is referenced
public UStreetTypeRecord getStreet();
public String getZip();
public String getCity();
public String getCountry();
}

And you will be able to communicate with your database using code similar to the following one:

// Create the nested UDT structure
UAddressTypeRecord address = new UAddressTypeRecord();
UStreetTypeRecord street = new UStreetTypeRecord();
street.setStreet("Bahnhofstrasse");
street.setNo("1");
address.setStreet(street);
address.setZip("8001");
address.setCity("Zurich");
address.setCountry("Switzerland");

// Pass the UDT as a parameter to the database. Note how the OUT parameter
// is mapped as a return value to the method. If you have several OUT parameters
// A value object containing all qualified parameters is generated and returned
UAddressTypeRecord resultingAddress = Procedures.pCheckAddress(connection, address);

Conclusion

In the early days of jOOQ, I thought I was creating an alternative to Hibernate or JPA, which are excellent tools, but they are OR-mappers (see the introduction about religious persistence beliefs). Since I'm in love with SQL, I don't want any mapping to the OO-world. (I'm also in love with OO, that's why it's called j-OO-Q).

But in the mean time, I have realised that jOOQ is something entirely different. It can go side-by-side with Hibernate, JPA, or other tools, providing much ease of access to your beloved (or hated) database functionality in the way you're used to in Java. With code generation, access to vendor-specific constructs becomes as easy as it can be. You can call your stored procedures as if they were regular Java methods, without knowing the details about JDBC's advanced features. You can make your database an integral part of your application. You can make stored procedures fun! Heck, you can even change your mind like I did! :-)

Check back on jOOQ for future versions, with more features and databases to come. And maybe you'll commit the odd patch to integrate that beloved database-feature of yours in jOOQ!

Published at DZone with permission of Lukas Eder, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Jan Gaspar replied on Thu, 2011/01/13 - 4:25am

Indeed. Stored procedures are alive, OR-mapping never worked well. Our company policy is: if you want to access a database then call a stored procedures if there is one or ask your DB team to write one for you; plain CRUD statements are not allowed. The advantage - apart from the performace gain - is also you don't have to worry about the database structure too much.

Lukas Eder replied on Thu, 2011/01/13 - 4:41am in response to: Jan Gaspar

Hi Jan

That's a very interesting -almost extreme- approach. I would like to see how such an architecture performs in every day tasks. I imagine the separation of data and business logic can be kept quite strict, this way. What technologies (databases, programming languages, etc) are you using?

Cheers,
Lukas

Artur Biesiadowski replied on Thu, 2011/01/13 - 5:43am

@Jan

How does it work in practice? I'm a developer and I would like to do a quick proof-of-concept requiring a new field in database (on development system of course). I will need to modify a table and few stored procedures which are touching that area. In your environment, will I be blocked till somebody from DB team can spend time to do my correction on my private database, or I'm allowed to modify the stuff myself?

If latter, then what DB guys are doing, as probably most of the changes will be coming from developers in first place? If former, isn't it standing in way of fast prototyping ?

I find stored procs particular pain when doing branching/prototyping/etc. There is plently of tools which are helping with all those things on source level side, but suddenly, if you try to keep things in sync on database, you are often doing manual/error prone work. And to be honest, 50% of stored procs I have seen are just doing selects from tables, something which is a lot better handled by views (a lot more self-descriptive from metadata point of view).

Jean-Baptiste Nizet replied on Thu, 2011/01/13 - 6:41am

I'm very far from being an expert in stored procedures, but if the PL/SQL syntax is so annoying, why don't you develop them in Java directly?

I know that Oracle supports stored procs written in Java. PostgreSQL supports Perl, Python and other languages. Why not use them? Are they slower? Or is it impossible to perform some operations in those languages?

JB.

Lukas Eder replied on Thu, 2011/01/13 - 6:58am in response to: Jean-Baptiste Nizet

@Jean-Baptiste: That's a good question and I am not an expert myself. But there are many reasons for that, I guess. I find this explanation on stackoverflow quite interesting. It compares

  • stored procedures vs business logic
  • stored procedures in Java vs stored procedures in a language like PL/SQL or pgplsql
For me personally, I like the fact that PL/SQL (or pgplsql, or the HSQLDB routine language, etc) integrate so well with SQL. As I am writing data logic (and not business logic) inside a stored procedure, I accept the quite different syntax. On the other hand, imagine opening, closing PreparedStatements, CallableStatements etc. inside of Oracle. Don't you think PL/SQL is better suited for stored procedures?

Jan Gaspar replied on Thu, 2011/01/13 - 8:09am in response to: Lukas Eder

We use Oracle 10. The programming language is Java. One of the reasons we went this way is that some tasks cannot be very effectively implemented in Java. For example we have got an order management sytem which routes orders to exchanges ... the route is calculated by a stored procedure based on the type of the order, stock, client and plenty of other parameters ... the logic to find a route is quite complicated and involves accessing multible tables. (Implementing this via OR-mapping framework would be nearly impossible.) Another point is that Java developers are not always the best SQL developers ... that means the DB code is maintaned by DB team which can usually write more effective SQL statements. These statements are implemented as stored proceures which are then called by Java code.

Lukas Eder replied on Thu, 2011/01/13 - 8:26am in response to: Jan Gaspar

@Jan: That's a great show-case for stored procedures. We do similar stuff for our backend integration with SMS, stock exchange systems, payment systems etc. Except that we're the same people developing Java AND PL/SQL :-)

How do you interface Java with your PL/SQL procedures? Would jOOQ be an option to you?

Jan Gaspar replied on Thu, 2011/01/13 - 11:22am in response to: Lukas Eder

We use JDBC and calling pocedures using either CallableStatement or Spring's StoredProcedure. jOOQ looks promissing. Does it support OUT parameter which is a cursor?

Lukas Eder replied on Thu, 2011/01/13 - 12:24pm in response to: Jan Gaspar

@Jan: Not yet. The difficulty in jOOQ is to find a solution that will work across all supported databases. While "cursors" in Postgres really are like tables (select * from my_function()), they aren't in Oracle. Also ARRAY types are not supported yet. But both are a very high priority in early 2011.

Oliver Plohmann replied on Fri, 2011/01/14 - 4:28am

When I first started coding Oracle PL/SQL for my employer, I realised that I found myself back in medieval ages.
Yes, exactly. And when you don't have Oracle PL/SQL on your CV you won't get invited to a single job interview ...

Lukas Eder replied on Fri, 2011/01/14 - 4:50am in response to: Oliver Plohmann

Yes, exactly. And when you don't have Oracle PL/SQL on your CV you won't get invited to a single job interview ...
@Olli: I wouldn't go that far, but I have experienced the fact that it was a serious plus, also on the pay check

Nicolas Bousquet replied on Fri, 2011/01/14 - 12:15pm

In fact i tend to say that NoSQL approach can work great. NoSQL tends to go with a "querry centric" model that perform great on reading.

I also like solution with fat databases, with a nice normalized schema (Data centric), or even some denormalised data as needed. Using stored procedure give you a clean interface to the outside and encapsulate the actual data model. The gain here is that there is no networks load to perform the internal computation. All is done on the server and it can save a lot of time and bring astuning performance.

 The model that fail for me is using an ORM. Well you can just use an ORM to map the result set of your stored procedure or views... Basically then you ORM bring you just an added layer of complexity in exchange for loading yours results directly into POJO, and if the column name change, you juste change you configuration file. Basically that's fine.

But if you start to repeat your database model in you code JAVA/.NET code by modelising the relations between tables or worse if you start to repeat your OO model in the database by modelising inheritences or weird things last that, problems start to appear :

 - no separation of concerns. The database model is visible from the outside. If database model change, everybody is impacted.

- bad performance : because processing is done outside the database, network traffic increase a lot, altogether with increased response time.

 - impedence mismatch : if you try to make you database appear like an object store instead of a rows store it not optimized for reading or writing anymore : it is optimized to fit your OO model. So optimized to please you (or your architect)... And excepted from you or your architect, nobody will care of what your architecture is ... if it fast enought.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.