4

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...
}
  • 1
    If it is null final int id = rs.getInt("Id"); will throw an exception... – assylias Feb 11 '14 at 18:36
  • @assylias I thought it would, but according to JavaDoc: "if the value is SQL NULL, the value returned is 0". That's why I needed to use ResultSet#wasNull(). – ArturoTena Feb 11 '14 at 18:40
  • There's no objective 'better'. Use whichever you think is more maintainable in your code base. I'd typically use approach 1 to keep the code a little less cluttered. – GrandmasterB Feb 11 '14 at 18:49
  • 2
    What you do will depend on what significance the values have. Are the values 1, 2, and 3 represent anything meaningful in the database layer, or do they only have meaning for the code? – Dan Lyons Feb 11 '14 at 18:52
  • @GrandmasterB Maintainability is indeed a good metric. Thank you! – ArturoTena Feb 11 '14 at 18:54
1

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.

  • "Always code as if the person who ends up maintaining your code is a violent psychopath who knows where you live." -comment on C2, cited by Jeff Atwood. Got it. :) On a more serious note, Consistency, avoiding the data travelling, modularization/reuse and knowledge of the language seems good metrics. Thank you! // On an unrelated note, I've made a mess with my Stack Exchange accounts, so I can't upvote your answer to my own question :,( – ArturoTena Feb 11 '14 at 20:11
1

The answer to your question is another question. Why are you encoding these results as arbitrary integers 1, 2, 3? Is it for some external Java API, or an internal API that you cannot modify? If so, then do the translation in some Java wrapper around that interface. That will prevent repeating this translation in multiple queries.

== Addendum ==

According to OP, these are magic numbers that stand in for an event type. In that case, I would go ahead and do the translation in the query. If I created another query that needed to repeat that translation, I would factor the translation into a view. But I wouldn't call the resulting column "ID", which tells me nothing. I would call it "event_type".

  • They are constants. In the business language there are only three possible "events". It's a legacy project, which use ints are constant as it where made years before enums arrived. They are used all along thousands of places, so refactoring would not be easy. What do you mean to wrapping the interface on Java? – ArturoTena Feb 12 '14 at 16:21
0

In layman's words:

First option is better, because:

  1. The part of SQL used for writing queries is declarative, not procedural, so intrinsically it's less complex than java, thus first option has less cyclomatic complexity.
  2. Understanding SQL is a very common skill, so maintenance of SQL code is cheaper. A programmer may not know Java but there's a big chance he/she knows SQL.

This answer of mine (here) to a similar question elaborates on that.

This other answer to another similar question also supports that.

  • incorrect in so many ways... – jwenting Feb 12 '14 at 14:11
  • @jwenting Please explain so I can learn from my mistakes. – Tulains Córdova Feb 12 '14 at 15:16
  • 1
    SQL is a programming language. It's NOT universally true that SQL is a good option. Far from everyone knows SQL, let alone the specific dialect for a specific database engine. – jwenting Feb 12 '14 at 15:23
  • @jwenting I edited my answer. Changed the "programming language" part, as well as the "everyone knows" part. But note that both options use SQL, so my approach was never a SQL-only option. – Tulains Córdova Feb 12 '14 at 15:44
  • @user61852 From your links, I'll take "what SQL is made to do" & SQL [should be] used for writing [declarative, not procedural queries]. Thank you! – ArturoTena Feb 12 '14 at 16:28
-1

As industry best practices, we need create a stored procedure to handle that logic. Java code's work is to execute the stored procedure(SP) and get the output.

It will be easy while changing the logic in future. If it is SP, we just need to modify the logic by altering the stored procedure.

If you are writing in java. we need to build and deploy the whole project again. As a developer we do make mistakes by checking in or out the wrong version of code from or to production. It will cause issue after the deployment.

So. stored procedure is always best way.

  • 3
    It's simply not true that stored procedures are always the best way. You mentioned already why: Because we as developers make mistakes. Why is it more likely to check out a wrong version than not paying attention to the version of the sp? What if somehow the sp has a bug and you spend hours searching it in your applications code? I don't say you shouldn't use stored procedures, but to use them always is dangerous, because they add another layer of logic. – mhr Feb 12 '14 at 7:01
  • @mri SPs can replace often the entire application business logic, leaving only display logic outside the database. In that case, it's a potentially great solution as it prevents data corruption by applications modifying data outside the proper business logic components. I know it's an outmoded idea in this newfangled world of JPA everything, but it still has validity. – jwenting Feb 12 '14 at 14:13
  • Stored procedures offer the added benefit of using the logic outside of a specific application, but most devs try to avoid that whenever possible. – JeffO Feb 12 '14 at 15:33
  • @keerthi (also @ jwenting @ JeffO) My boss will love your answers :D I'm not so sure, but it's tempting. Thank you! – ArturoTena Feb 12 '14 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.