SQL vs Java Code – Choosing Values in Queries

clean codejavasql

I need to choose one of three values of an integer using the value of a column on a nullable column of a table.

There are at least two approaches: 1) use SQL to do all the work: test null values, and choose between the other values, or 2) read the value and use code -in this case Java- to choose.

Which one is "better", ie. easier to understand & more maintainable? Do you have any other metric use to decide?

As an example, I have the following code:

// If id is equal to:
//   -1, then make v = 1
//   null, then make v = 2
//   in any other case, make v = 3

// Option 1:
int v;
String query = "SELECT CASE min(Id) WHEN NULL THEN 2 WHEN -1 THEN 1 ELSE 3 END AS Id"
        + "FROM TableA WHERE SomeField IN (SELECT ...blah blah...)";
ResultSet rs = // execute query
if (rs.next()) {
    v = rs.getInt("Id");
} else {
    // TODO something went *very* wrong...
}

// Option 2:
int v;
String query = "SELECT CASE min(Id) Id"
        + "FROM TableA WHERE SomeField IN (SELECT ...blah blah...)";
ResultSet rs = // execute query
if (rs.next()) {
    final int id = rs.getInt("Id");
    if (rs.wasNull()) {
        v = 2;
    } else if (id == -1) {
        v = 1;
    } else {
        v = 3;
    }
} else {
    // TODO something went *very* wrong...
}

Best Answer

In the example you give, I don't think it makes a difference.

One important thing is to be consistent with where you put your logic, so you'll know where to look for it later. When querying databases, it's best to only pull the data needed. In your example, it doesn't seem to make a difference (same number of records and amount of data).

What are you going to do when your logic gets more involved? You may find you're better off handling it in Java or some other procedural language of choice. This is probably a personal preference, but could be influenced by which language you're more skilled.

If you wanted to have some sort of code library to reuse this logic, I wouldn't recommend having it in your database code. Not all data come from the same database. What if you want to perform this logic on a different table or different columns? You won't be able to reuse this code.

These recommendations aren't based on trying to pre-optimize or to go out of your way to extend things you don't need. You were probably able to build both solutions with the same amount of effort. If you think any of these potential pit-falls could occur, just go in that direction and be consistent. The next coder will appreciate it even if it is you.

EDIT: Also, I write a lot of SQL code in my job (as database objects), but can't stand it when I have to write them in strings in other languages. Simple selects are fine. The shorter the better.

IDE - the debugger in Java here will probably be more useful. Of course you can test your sql somewhere else.

Magic Numbers - it would be easier in the Java code to represent your numbers (1,2,3) as descriptive variables/constants.

Related Topic