Just added Spring (2.0) support back into jDBI (2.0). When I started I looked back at the 1.4 codebase to see what I had done in the previous version... The 2.0 is much smaller, lighter, and has less special stuff. Yea!
This has been my first chance to work with Spring 2.0 and I rather like it. The SPI for playing with the transaction manager is pretty much identical, which is cool. The @Transactional annotation stuff works very nicely. I also learned, along the way, that Derby supports nested transactions. Sweet!
Anyway, jDBI has come a helluva long way in two years. The 2.0 codebase is getting the kinks worked out and I really like it.
Anywho, jDBI 2.0pre13 has been cut. I am trusting the codebase, so hopefully will hit a 2.0 final soon. In prerelease form it trumps 1.4.X right now, but I don't want to rule out API tweaks until I've used it anger a bit more.
0 writebacks [/src/java/jdbi] permanent link
Tue, 28 Nov 2006
Oracle's DML Returning and jDBI 2.0pre7
So I broke down and have started adding some database-specific functionality to jDBI. The first bit is a statement customizer which does the downcasts and whatnot to make it convenient to use oracle's dml returning stuff (like "insert into something (id, name) values (something_id_seq.nextval, ?) returning id into ?").
public void testFoo() throws Exception
{
Handle h = dbi.open();
OracleReturning<Integer> or = new OracleReturning<Integer>(new ResultSetMapper<Integer>() {
public Integer map(int index, ResultSet r) throws SQLException
{
return r.getInt(1);
}
});
or.registerReturnParam(1, OracleTypes.INTEGER);
h.createStatement("insert into something (id, name) values (17, 'Brian') returning id into ?")
.addStatementCustomizer(or)
.execute();
List<Integer> ids = or.getReturnedResults();
assertEquals(1, ids.size());
assertEquals(Integer.valueOf(17), ids.get(0));
h.close();
}
This allows for a nice API, without client code downcasts as well, to make use of results returned from DML against Oracle. When I get a chance I'll add one for Postgres as well :-) The code to do it is pretty straightforward. The nice part was that it required no changes to the core of jDBI 2.0 to do this :-)
Pushed 2.0pre7 as well ;-) Have fun!!
0 writebacks [/src/java/jdbi] permanent link
Thu, 10 Aug 2006
O/R Mapping, SQL, Relation Orientation and Convenience
Ted Neward has broken out his commentary on object-relational mapping again. When he first posted this idea I disagreed -- I felt strongly that everyone tackling it, all the options, and various approaches, signaled a strong and healthy solution space. Over the intervening couple of years I have started to come around to Ted's point of view.
Ted sums up the problems very thoroughly, much more so than I want to take the time to do, so... go read his post.
Now, if you do want to use SQL-centric stuff in a convenient way, there are few options that give you the kind of convenience APIs that the O/RM tools provide. To take a trivial example of a one-parameter query:
RIFE provides a very nice API for accessing relational databases as... relational databases. Hynek recently posted. RIFE uses a builder approach to sql statements, like(to use Hynek's example):
Select select = new Select(datasource)
.from("customers")
.where("city", "=", city);
DbQueryManager manager = new DbQueryManager(datasource);
List<Customer> r = manager.executeFetchAllBeans(select, Customer.class);
This style is really nice when your queries are dynamic, such as the ubiquitous "advanced search" form. The example here chains everything, but you can quite easily wrap the where clauses in if statements to build up a statement programmatically rather than via string concatenation.
If you prefer to work directly with SQL, or have mostly static statements, then there are a couple more really good tools. My favorite is, well, one I wrote called jDBI, which lets you do things like:
Handle handle = DBI.open(datasource);
List<Customer> r = handle.createQuery("select * from customers where city = :city")
.bind("city", city)
.map(Customer.class)
.list();
jDBI is focused very tightly on being a JDBC convenience API, not a general framework, such as RIFE. RIFE, on the other hand, provides some higher level functions enabled by taking control over what exactly gets done, such as handling limit and offset even on databases which don't support them (via query munging and rownums on Oracle, or using scrolling cursors and only fetching the correct rows elsewhere).
The grandaddy of JDBC convenience libraries is Apache DbUtils. With DbUtils you would have:
QueryRunner runner = new QueryRunner(datasource);
List r = (List) runner.query("select * from customers where city = ?",
city,
new BeanListHandler(Customer.class));
DbUtils is a short-and-sweet tool which provides even less high-level functionality, but is extremely well tested and quite flexible. It hasn't been upgraded to take advantage of generics yet, but some people probably prefer that :-)
Spring provides a few convenience API's, mostly centered around the JdbcTemplate. Like much of Spring, it doesn't provide an alternative API for what it helps with, it instead focuses on making the common cases easy and smoothing the sharp edges.
SimpleJdbcTemplate t = new SimpleJdbcTemplate(datasource);
List<Customer> r = t.query("select * from customers where city = ?",
new ParameterizedRowMapper<Customer>() {
Customer mapRow(ResultSet rs, int index) {
Customer c = new Customer();
// map into the bean
return c;
}
}, city);
This uses the upcoming Spring 2.0 stuff :-) Surprisingly they don't seem to have a generic JavaBean mapping row mapper. Weird, but easy to write.
Finally, this doesn't really exist yet, but JDBC 4 (part of JDK 1.6) will include a spiffy looking convenience API that looks like an annotation based SQL-J. You define an annotated interface and the driver provides an implementation which does the queries.
public interface CustomerDao extends BaseQuery {
@Select("select * from customers where city = ?1")
DataSet<Customer> findCustomersFrom(String city);
}
CustomerDao cd = QueryObjectFactory.createQueryObject(CustomerDao.class, datasource);
DataSet<Customer> r = cd.findCustomersFrom(city);
DataSet extends List, so you can treat reasonably! I am quite looking forward to trying this out. There are only a limited set of annotations defined, so you cannot specify things like fetch size, but it is pretty nice looking anyway :-)
Anyway, it hasn't been an in-depth look, but a taste and starting point for folks who want something that tries to embrace the underlying relational database.
update Thu Aug 10 09:01:00 PDT 2006 -- add iBatis
It was kindly pointed out by Steven Grimm that I missed iBatis. D'oh! It definitely should be mentioned. iBatis is very configuration heavy so I don't really have time to post up the trivial example. Will do so later :-)
4 writebacks [/src/java/jdbi] permanent link
Sat, 06 May 2006Pushed jDBI 2.0pre1 last night. This is very much an ongoing development branch -- not many docs, not all the sugary features of 1.X (Spring TX integration, the magic configuration detection, etc), but it has some major features that just aren't doable under the 1.X design, like much more explicit control over things when you need it, while still providing the higher level functions when you don't -- and the ability to mix them pretty willy-nilly.
Rather than C&P lots of code here, I'll reference the test cases to demonstrate stuff =)
More Sophisticated Named Parameter Binding
public void testInsert() throws Exception
{
Handle h = openHandle();
UpdateStatement insert = h.createStatement("insert into something (id, name) values (:id, :name)");
insert.bind("id", 1);
insert.bind("name", "Brian");
int count = insert.execute();
assertEquals(1, count);
}
public void testDemo() throws Exception
{
Handle h = DBI.open(Tools.getDataSource());
h.createStatement("insert into something (id, name) values (:id, :name)")
.bind("id", 1)
.bind("name", "Brian")
.execute();
h.insert("insert into something (id, name) values (?, ?)", 2, "Eric");
h.insert("insert into something (id, name) values (?, ?)", 3, "Erin");
List<Something> r = h.createQuery("select id, name from something " +
"where name like :name " +
"order by id")
.bind("name", "Eri%")
.map(Something.class)
.list();
assertEquals(2, r.size());
assertEquals(2, r.get(0).getId());
assertEquals(3, r.get(1).getId());
h.close();
}
public void testIteratorBehavior3() throws Exception {
h.insert("insert into something (id, name) values (?, ?)", 1, "keith");
h.insert("insert into something (id, name) values (?, ?)", 2, "keith");
int count = 0 ;
for (Something s : h.createQuery("select * from something order by id").map(Something.class))
{
count++;
assertEquals("keith", s.getName());
}
assertEquals(2, count);
}
Much more powerful statement
rewriting capabilites. The rules (and rewriter) from 1.X is
the default, but you can add additional macro capabilities
pretty easily, say something like: select
{org.skife.jdbi.Something} from something which will look
up writeable javabeans properties on Something and
put this in by name. That is not to say jDBI does strong o/r m,
but that it is trivial to add ActiveRecord style
auto-mapping (not its querying stuff, or tight integration with
the stack, am not tring to bring down the wrath of the railzors
(which I probably count as, so, umh, hmm)) in a typed manner
(and without the dreaded select * from something).
The 2.0 branch (trunk really) keeps all the easy to use batching, prepared batching, scripting, externalized statements, etc. Just adds better hooks into the innards, and makes more fine grained stuff easier, which is awfully important to be able to drill into. Once again, jDBI is not a JDBC abstraction library, it is a JDBC convenience library -- optimized for people, not driver writers!
Anyway, have fun! and let us know if things break, could be better, are bass ackwards, or whatnot!
0 writebacks [/src/java/jdbi] permanent link
Thu, 16 Mar 2006
I got an accidental feature in the upcoming 2.0 jDBI when I implemented
forward-only external iteration (via an iterate()
method on Query:
public void testIteratorBehavior2() throws Exception
{
h.insert("insert into something (id, name) values (1, 'keith')");
h.insert("insert into something (id, name) values (2, 'eric')");
ResultIterator<Something> i = h.createQuery("select * from something order by id")
.map(Something.class)
.iterator();
Something first = i.next();
assertEquals("keith", first.getName());
Something second = i.next();
assertEquals(2, second.getId());
assertFalse(i.hasNext());
i.close();
}
I realized that providing the iterate method provided
all that was needed for Iterable, so we also get...
public void testIterable() throws Exception
{
int count = 0;
h.insert("insert into something (id, name) values (1, 'keith')");
h.insert("insert into something (id, name) values (2, 'keith')");
for (Something s : h.createQuery("select * from something").map(Something.class))
{
assertEquals("keith", s.getName());
count++;
}
assertEquals(2, count);
}
Nifty! Looking forward to getting this release out =)
1 writebacks [/src/java/jdbi] permanent link
Sat, 11 Mar 2006Minor release of jDBI to set the target and source versions for compilation explicitely to jdk 1.4. No functionality changes, but folks running jdk 1.4 should be able to use the distributed binaries now =)
0 writebacks [/src/java/jdbi] permanent link
Wed, 22 Feb 2006
Simone Gianni found a vicious little bug in the (lack of) handling
of setNull in jDBI 1.4.0. So... jDBI 1.4.1 has been
released. Thank you JDK 1.4 and
PreparedStatement#getParameterMetaData(). It should
degrade to previous behavior if your driver doesn't support
parameter metadata, if it doesn't on your platform, please let me
know!
Have fun!
2 writebacks [/src/java/jdbi] permanent link
Sat, 18 Feb 2006jDBI has been getting some love here and there, and a recent feature request has lead to a great design change.
The first thing is that the upcoming 2.0 release is going to require JDK 1.5. Why? Because I was beaten with a stick at ApacheCon for not doing something that made total sense to do, but was not very aesthetic in the current codebase. What this really means is that you can do stuff like this in the upcoming release:
public void testDemo() throws Exception
{
Handle h = DBI.open(Tools.getDataSource());
h.createStatement("insert into something (id, name) values (:id, :name)")
.setInteger("id", 1)
.setString("name", "Brian")
.execute();
h.insert("insert into something (id, name) values (?, ?)", 2, "Eric");
h.insert("insert into something (id, name) values (?, ?)", 3, "Erin");
List<Something> r = h.createQuery("select id, name from something " +
"where name like :name " +
"order by id")
.setString("name", "Eri%")
.map(Something.class)
.list();
assertEquals(2, r.size());
assertEquals(2, r.get(0).getId());
assertEquals(3, r.get(1).getId());
h.close();
}
This illustrates a couple of the biggest changes -- there are statement and query abstractions which allow for much more fine tuning of what happens. This opens the door for lazy iteration, setting of isolation levels, etc. All the nice stuff which you need sometimes in JDBC.
The map(Something.class) in there is a fun one -- by
default queries still do lists of maps (of String => Object) but
bean mapping, custom mapping, etc are much more easily done with the
ability to specify a mapper in the query, rather than as an
interceptor on the handle. The API is much nicer there.
FInally, what isn't shown above because I haven't finished it, is the statement rewriting system. It is designed to pretty much support first-class macros, with access to the parameters at the time of macro expansion. This allows for some really interesting dynamic SQL scenarios done very cleanly.
I also thought up a byline for jDBI =) "Because SQL shouldn't be such a pain the ass in Java." Not very catchy, but it certainly sums up my thoughts.
0 writebacks [/src/java/jdbi] permanent link
Sat, 19 Nov 2005Just added a pluggable script locator module to jDBI. The script locator is used when you use the handle to execute scripts, so by default you can do:
handle.script("scripts/create-tables.sql");
handle.script("com/example/classpath-scripts.sql");
handle.script("https://example.com/scripts/remote-script.sql");
Of course, you can also write your own implementation of
ScriptLocator (define one method) which locates script
some other way =)
I pushed two releases with this. The first is a fully backwards
compatible 1.3.3 release. The second is a 1.4.0 release which
exposes the methods for specifying the statement locator and handle
decorator on the IDBI interface (and is a 1.X upgrade
as others may have implemented the interface and this would break
their implementation).
1 writebacks [/src/java/jdbi] permanent link
Fri, 18 Nov 2005Devender demonstrated shelling out to SQL*Plus to run a script, figured I had to trackback with a jDBI example that doesn't have to shell out, just for alternatives (and because this is a common mechanism for me to construct test schema =)
Our script, with various comment styles (this was taken from the regression tests for jDBI, btw):
-- insert 3 lines
insert into something(id, name) values (1, 'one');
# line 2
insert into something (id, name) values (2, 'two');
// line 3
insert into something (id, name) values (3, 'three');
and our code to run the script:
public void testScriptExample() throws Exception
{
Handle h = DBI.open("jdbc:derby:testing");
assertEquals(0, h.query("select * from something").size());
h.script("src/test-etc/insert-script-with-comments.sql");
assertEquals(3, h.query("select * from something").size());
h.close();
}
Shelling out to SQL*Plus is always an option I guess =) This did remind me that I need to change jDBI to use a script locator mechanism analogous to its named statement locator, in case you want to store your scripts on a web server, in the database, in JNDI, or... wherever comes to mind. Guess 1.3.3 may be out soon!
1 writebacks [/src/java/jdbi] permanent link
Wed, 16 Nov 2005Just pushed a minor jDBI release (1.3.2) which adds better error reporting on exceptions (it is much easier to get to the underlying SQLException on malformed SQL now).
Have fun!
2 writebacks [/src/java/jdbi] permanent link
Wed, 29 Jun 2005
jDBI 1.3.1 Released and added a Committer =)
Just released jDBI 1.3.1. It fixes a bug which only manifested in JDK 1.5. Annoying things, those platform version dependent bugs =( Thank you Patrick for finding it and figuring out the fix!
Along the way, I managed to force a commit bit onto him, even better!
This should be a drop-in replacement for anyone running 1.3.0.
0 writebacks [/src/java/jdbi] permanent link
Tue, 26 Apr 2005Released a minor API change version of jDBI last night. Significant features include:
Global params are easy to show examples of:
public void testParamOnBareFirst() throws Exception
{
handle.getGlobalParameters().put("name", "Robert");
handle.execute("insert into something (id, name) values (:id, :name)",
new Something(1, "Robert"));
Map robbie = handle.first("select * from something where name = :name");
assertNotNull(robbie);
assertEquals(new Integer("1"), robbie.get("id"));
}
public void testOnDBI() throws Exception
{
handle.close();
DBI dbi = new DBI(Tools.CONN_STRING);
dbi.getGlobalParameters().put("name", "Robert");
handle = dbi.open();
handle.execute("insert into something (id, name) values (:id, :name)",
Args.with("id", new Integer(1)));
Map robbie = handle.first("select * from something where name = :name");
assertNotNull(robbie);
assertEquals(new Integer("1"), robbie.get("id"));
}
So have fun with it!
0 writebacks [/src/java/jdbi] permanent link
Tue, 15 Mar 2005jDBI is a thin wrapper around JDBC, designed to be convenient for the programmer instead of whatever JDBC was designed to be conveninent for =) It uses the Java collections framework for query results, provides a convenient means of externalizing sql statements, and provides named parameter support for any database being used, hooks into the various transaction systems (local, BMT, CMT, Spring, etc), and is hopefully useful.
All 1.2.5 does is use the batch api for handling scripts. This allows for DDL in scripts, which is kinda handy, as a lot of databases don't allow DDL in prepared statements (which the default jDBI statement execution uses).
Go have fun with it!
Coming soon: using the externalized SQL system for scripts instead of the current specialized script handler.
0 writebacks [/src/java/jdbi] permanent link
Mon, 31 Jan 2005Just pushed jDBI 1.2.3. Much thanks to Patrick and Robert for prodding me make a couple big changes, and for prodding me to not make those changes until I found the right way, respectively (for 1.2.2 (transparently handling different transactional contexts) and 1.2.3).
The biggest change is making externalized SQL pluggable. It was something that had been itching for a while (previously it could only pulled named statements from the classpath, though pretty smartly), and Patrick's need for sticking the SQL in the database prodded me to finally support that explicitely. He's not using it, I don't think, but you'd better bet I will before too long =) Maybe stick them in a JNDI or an LDAP instead, or whatever. It's all good.
Speaking of the classpath approach, what I did on the last new thing I used jdbi for was to take advantage of Java's nice resource loading and bundle all the sql into its own jar, under an sql/ directory, so you'd have:
sql/
find-foo-by-id.sql
find-foo-by-name.sql
...
Map foo = handle.first("sql/find-foo-by-id", Args.with("id", new Long(fooId)));
Map same_foo = handle.first("sql/find-foo-by-name", foo); // gets "name" from map
The hack here is that the named statement is fetched correctly, and if you unzip the sql.jar to tweak the sql, it's in its own dir, not the working dir. This is minor, but I have things that untar (zip|jar) into the working directory =)
Anyway, the release is up on Codehaus. Have fun!
2 writebacks [/src/java/jdbi] permanent link
Wed, 19 Jan 2005
I have been lax in announcing jDBI related stuff -- time to make up for that =) First, jDBI made its 14th and 15th (1.1.2 and 1.2.1 -- seperate branches). Technically 1.2 maintains bytecode compatibility with 1.1 (you can drop the jar in and go) but there is a huge seven character difference. DBIException now extends RuntimeException per a bunch of strong arguments by Patrick Burleson. Seven characters, world of difference. Guess I have taken the dive into optional exception checking. Water still feels chilly.
Aside from API changes, hosting has moved to the Haus. The subversion repo isn't there yet, but the rest is. No pretty confluenze site (yet?). We also have mailing lists! I should probably post them on the site sometime soon... In the mean time, its not hard to figure out my email address, or the lists if you look at how Bob lays out projects ;-)
As the last release I announced was 1.0.X series, some other big things in there include really nice Spring integration (factory bean which makes a couple minor changes to play as expected and hooks into the platform transaction system, and DBIUtils to provide transactionally bound handles/access/etc)
Meeting a few feature requests there is a really nice batch and prepared batch system with usages looking something like:
public void testBatchOfTwo() throws Exception
{
handle.batch()
.add("insert into something (id, name) values (1, 'one')")
.add("insert into something (id, name) values (2, 'two')")
.execute();
final Collection results = handle.query("select id, name from something order by id");
assertEquals(2, results.size());
}
public void testPreparedBatch() throws Exception
{
handle.prepareBatch("insert into something (id, name) values (:id, :name)")
.add(new Something(1, "one"))
.add(new Something(2, "two"))
.add(new Something(3, "three"))
.add(new Something(4, "four"))
.execute();
assertEquals(4, handle.query("select * from something").size());
}
public void testPreparedBatch2() throws Exception
{
final Something[] things = new Something[]
{
new Something(1, "one"),
new Something(2, "two"),
new Something(3, "three"),
new Something(4, "four")
};
handle.prepareBatch("insert into something (id, name) values (:id, :name)")
.addAll(things)
.execute();
assertEquals(4, handle.query("select * from something").size());
}
That one is my favorite. I rarely used batched sql when working with jdbc directly as, well, it is bloody inconvenient. Do it all the time now =)
A particularly fun piece is not actually released yet as I am not satisfied that I have caught all the edge cases -- Henri Yandell asked for in-clause expansion. That is, bind a collection or array to a single formal param for an sql in clause, a la, select id, name from something where id in ?, which could have a List<Long> bound and would expand as required to behave correctly (ideally as a prepared statement still). As I said, I have code which does it, but it is buried in subversion and not exposed yet as I had wanted to avoid fulling parsing and transforming the sql. Still, that is powerful stuff, so it'll probably be the next significant feature.
Anyway, enjoy it and have fun! Remember, SQL isn't teh ev1l, JDBC is just inconvenient! Let the code do the work =)