Category: SQL Server

Whenever you create a more involved project, you often think of how to break down the application into persistance layer, management or application layer, presentation layer. A major part of this decomposition is the decision of where to maintain your business logic with two possible routes to follow:

The application layer
You maintain the rules in your Java, C#, Perl or god forbid PHP, and use the database to retain and manage the data.
Advantages:

1. A vocabulary as wide as the eye can see for the development of the application – you are not limited by the pl/sql or T-SQLs of the world.
2. No need to maintain an additional code base in the database’s programming language or even learn that language
3. Rich development environments for the programming language
4. You are not married to the database – and this is a big consideration. Databases are often very expensive; programming languages are cheap or altogether free.

Distadvantages

1. Object-relational mapping needs to exist – whether you do it yourself or use something like Hibernate; you may need to learn how to use Hibernate
2. Programming languages are often slower in execution
3. Not portable between languages – you are married to the language (as opposed to the database…)

The persistance layer
Business logic is maintained in the database layer through stored procedures and user-defined database functions.

Advantages

1. Speed – databases are normally faster and more optimized than the code you wrote
2. Lightweight application code with simple object-relational mapping
3. Application-layer portable – you can port your code in relatively simple fashion to other technologies

Disadvantages

1. As mentioned above – database licensing can be very expensive
2. Developing and testing stored procedures and functions can be hairy as the development environments are not as robust and often consist of glorified text editors
3. Separate code-base to maintain, often more difficult to maintain in revision control
4. Requires knowledge of the database-specific programming language. Some databases can compile high level language code, though (Java, C#, etc.)

I found the latter solution more logical but then, I spent a lot of time learning T-SQL. Still, most of the front end code I started out with was in Cold Fusion, which forces you to move as much as possible into the database. PHP, is just as unappealing and T-SQL (or any other database language) appears refreshing compared to it. Nonetheless, this approach allowed me a relatively quick porting to Java and JSP.

It is very much a chicken and egg consideration. Whatever works for you will do, but in a world where you can get the database for free, a great IDE for free and application servers and language compilers for free, we are in a happy place where we can decide and not spend a fortune.

This article discusses this database-centric approach.

After executing a JDBC query, you often want to know if anything was returned and if so how many rows exist in the ResultSet. To do that, use this snippet of code:


ResultSet rs = st.exeuteQuery();
rs.last();
int rsRowCount = rs.getRow();
rs.beforeFirst();


The rsRowCount variable will have the answer.
Make sure that your Statement or PreparedStatenent are created using the three parameter version of the Connection object methods createStatement() and PrepareStatement() like:
Statement statement =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
OR

PreparedStatement ps = conn.prepareStatement([SQL EXPRESSION],
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

Look at
the JavaDoc entry for this method.