Built my first PreparedStatement with parameters yesterday. I was delighted to see that I just use question marks in the SQL to indicate parameters, as I'm used to from DBI. And then I was startled when execute() didn't seem to work when called with those parameters.
Poor, poor JDBC. Being hosted in a language such as Java, execute can't seem to handle a varying parameter list. There's a Java provision for variable-length parameter lists, but not really a decent way to handle parameters that might vary in type as well. Everything could be wrapped up in an object, but that would just be messy.
So I have to bind the variables to the statement before I execute it. And then came the part that really made me laugh mockingly: you don't just say set(pos, variable). You have to say setType(pos, variable): there's setInt(), setLong(), setString(), etc. Even setBlob(), setNull(), and setBigDecimal(). How sad.
However, I must say that I am impressed by setDate(). I always wanted to do that in DBI. Of course, I didn't want to have to bind each parameter independently or worse, use a different method for each type of variable, to do it. I wanted DBI to give me Time::Piece objects, or DateTime objects, or whatever I preferred at the moment. (I probably would've been happy even if it had given me a date object of a type I didn't prefer.)
On another JDBC note, the Statement class makes no sense to me. Statement objects do not seem to actually represent SQL statements unless they are one of its subclasses, yet Statement is not an abstract class. The documentation for Connection.createStatement() even refers to them as "a Statement object for sending SQL statements to the database." Statement has a method to call SQL statements, which is strange.
One of the better lightweight database libraries is jDBI. It doesn't seem to have the varargs method corresponding to DBI's execute(), but it does have generic 'bind()' methods as well as the ability to pass in a Map which uses named params.
Similar to the JDBC support in Spring, It's also got simple and very useful output mapping so you can get a list of objects back rather than raw resultsets.
Re:libraries, libraries
jdavidb on 2008-02-14T13:49:54
Awesome! I didn't know such stuff existed!
Unfortunately I probably can't use it at work at this time.
:) But I've been encouraged at times to seek better ways of doing things that would radically alter our code base, so they might be open to it in the future, if I can prove it's a good idea. I think one of the big problems in the Java world is that "official" ways to do things have often been blessed into standards status by Sun, discouraging further innovation. EJB is a great example, and I've seen book after book that says so. If there had been no body making things the standard, then best practices would have developed, and the free market exchange of ideas and encouragement for innovation would've produced far better de facto standards.
Thankfully I'm seeing more and more of these projects: Spring, JODA, jDBI, etc. And it's obvious the standards process is beginning to be influenced by the competition.
Re:libraries, libraries
lachoy on 2008-02-14T14:16:58
Actually, there's a TON of opensource stuff in the Java world. There's just no CPAN. A lot of it is in response to overdesigned standards (like EJB), crappy built-in libraries (like Joda Time improving datetime handling), or just building better functionality over basic work (like jDBI or iBatis over JDBC).
One thing that's become more frequent is a widely-used open source project becoming part of the JDK. All the concurrency improvements in Java5 came pretty much verbatim from Doug Lea's concurrency library; improving datetime handling is now a JSR that's headed by one of the Joda Time guys. There's hope!
