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...
}
8
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.
1
In layman’s words:
First option is better, because:
- 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.
- 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.
5
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”.
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.
4