2

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...
}
ArturoTena
  • 713
  • 5
  • 15
  • 1
    I'd go for SQL if the query is not *very* complex (mean executes in a reasonable time). If the query is 10min, better try java. But I always would prefere sql approach if the DB can do the job for me. – Thrash Bean Feb 11 '14 at 18:39
  • @ThrashBean I don't mean to be rude, but someone could argue that *a lot* of the job on system could be done in the database. I use to troll my boss saying he wish to develop *all* the web apps using just stored procedures :) But I take your comment about complexity of the query as a good metric to take into consideration. Would you mind to post it as an answer so I could upvote it? – ArturoTena Feb 11 '14 at 18:45
  • Here you are. Don't forget to check the execution time, just put some currentTimeMillis before and after the queries. – Thrash Bean Feb 11 '14 at 18:50
  • @ThrashBean Good advice. I will. – ArturoTena Feb 11 '14 at 18:53

2 Answers2

1

I’d say have SQL do the work. It’s fairly trivial and won’t soak up CPU time, and SQL will have to load the pertinent info in memory anyway so it’s already there for processing. Doing it on the app side, to a certain extent it seems like you have to “re-stage” the data for analysis, and (imho) the java code seems more difficult to read through and understand.

Note that there’s a minor flaw in your SQL code, you can’t use WHEN NULL that way in a case statement. You’d want something like

...case
     when min(Id) is null then 2
     when min(Id) = -1 then 1
     else 3
   end
Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • Thank you. So, CPU time and where is the information already loaded could be good metrics to take into consideration. About the SQL code, I'm using Sybase ASE, and it doesn't show any error on my tests. However, using `when min(Id)` shows "Incorrect syntax near '='". – ArturoTena Feb 11 '14 at 18:51
  • Oops -- my SQL comments were based on MS SQL Server. I guess the syntax for dealing with nulls varies by manufacturer. – Philip Kelley Feb 11 '14 at 21:13
1

I'd go for SQL if the query is not very complex (mean executes in a reasonable time). If the query is 10min, better try java. But I always would prefere sql approach if the DB can do the job for me.

(just copy of my comment)

Thrash Bean
  • 658
  • 4
  • 7