Suppose you have a task to write some JDBC code and you need to do so in the cheapest way possible. Having your people grow should not be considered here, just the rate.
Person A
Charges 3X per hour and can write working code on the first shot, test it and complete it within an hour
Person B
Charges X per hour, but needs to multiple cycles to fix once the code is typed. (This includes the time to identify what the problem is for each, build cycles and fix time.) For the SQL quiz , see how many errors you can find in the following code. I’ll post the answers as a comment. See if you can spot any I didn’t insert on purpose.
PreparedStatement stmt = null;
ResultSet rs = null;
try {
String sql = "select count(*) from table" + "where column = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, "test");
rs = stmt.executeQuery();
System.out.println(rs.getInt(1));
sql = "select count(*) from table2" + "where column = ?";
stmt = conn.prepareStatement(sql2);
stmt.setString(1, "test");
rs = stmt.executeQuery();
System.out.println(rs.getInt(1));
} finally {
stmt.close();
rs.close();
}
Which is better
In today’s economy, driving down costs is a hot topic. One phrase service providers consider is “how can we lower the rate.” This is the wrong question. The question should be how to lower costs overall. Person A is going to be cheaper overall even though the hourly rate is three times as high. We’ve all heard the comment that a good developer is many times more productive than an average one.
Granted, my example is extreme. It shows the difference between an experienced person and an someone new to JDBC. The point is to use the extreme to emphasize that it’s not all about the hourly rate. In the real world, training the entry level person has value too of course.
Another view
This reminds me of the methodologies that ask you to estimate how many hours/days/weeks a task will take without saying who will do the work. Well, if I do it, the work will take a week. If an entry level person is doing it, it may take four. If someone experienced is doing it who doesn’t know the system, it may take two. How do we balance the differences in people when estimating?
SQL quiz answers:
1) The sql statements are missing a space before the word “where”. SQL doesn’t know we’ve ended the table name without that space.
2) Missing rs.next() before calling rs.getInt()
3) The first statement is never closed. Waiting for the garbage collector is risky because we could run out or resources first.
4) If the code fails before the statement and/or resultset are created, the finally block will fail with a null pointer – obscuring the real error.
5) The stmt and rs close calls are in the wrong order.
1. “from tablewhere” is not going to work
2. The first statement/resultset is not closed
3. finally might raise a NullPointerException in some cases where the first prepareStatement failed.
“table” and “column” are reserved SQL keywords and should either be quoted when used as identifiers or will not work at all, depending on the database system used.
Hauke: Good ones! I didn’t think about that when I obscured away the real table names.
The person truly new to JDBC would not have used PreparedStatement, but put the query together with string catenation — probably having even more errors with spaces and quotes and such!
EFH: True. Let’s assume that the person was told to use a PreparedStatement either by a corporate coding standard or by copy/paste reuse :).
Switching around stmt.close() and rs.close() doesn’t provide anything functionally, though. Closing the Statement also closes the underlying ResultSet, and trying to close an already closed ResultSet doesn’t harm. So maybe having “rs.close()” in there at all is wrong (although not an error).
You’re right! I was all set to “prove” it throws “resultset already closed” exception. But it doesn’t.
If you want to use this test in other situations (e.g. interview) you could split the SQL string into different strings for the select clause, the from clause and so on, e.g. String selectClause =”select count(*)”, fromClause = “from table2”; String sql = selectClause + fromClause;
This is not uncommon (as long as building SQL queries with strings within the code is common), does not look wrong at the first glance and it obscures the missing space problem nicely – the unneeded string concatenation draws attention.
I don’t know that I’d use an interview question that I posted the answer to on the internet, but good idea. Or maybe it would show research skills :).