Choosing value in SQL query vs (Java) code

Question

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...
}

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
– Thrash Bean, Commented Feb 11, 2014 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
– ArturoTena, Commented Feb 11, 2014 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
– Thrash Bean, Commented Feb 11, 2014 at 18:50

Philip Kelley · Accepted Answer · 2014-02-11 18:43:56Z

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

answered Feb 11, 2014 at 18:43

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ArturoTena Over a year ago

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 '='".

Philip Kelley Over a year ago

Oops -- my SQL comments were based on MS SQL Server. I guess the syntax for dealing with nulls varies by manufacturer.

Thrash Bean · Accepted Answer · 2014-02-11 18:48:52Z

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)

answered Feb 11, 2014 at 18:48

Thrash Bean

6584 silver badges7 bronze badges

1 Comment

ArturoTena Over a year ago

Complexity of the query is as a good metric to take into consideration... agreed.

Collectives™ on Stack Overflow

Choosing value in SQL query vs (Java) code

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related