Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Apache JDBC utils

Reply
Thread Tools

Apache JDBC utils

 
 
markspace
Guest
Posts: n/a
 
      04-30-2012
Hey all,

I'm making a small website as a personal project using only the JDBC
interface. (No ORM, etc.) Well, I did the CRUD for exactly one bean
and found it pretty tedious going. So I started looking around for
something light-weight to help me out. I found the Apache commons
dbutils project:

<http://commons.apache.org/dbutils/>

This makes reading a bean much much easier. It does most of the column
to property matching for you and will read an entity into a bean with
only a few lines of code. Here's a (mostly) complete example from my
little project:

public UserBean getByUsername( String name ) {
QueryRunner run = new QueryRunner( dataSource );
BeanHandler<UserBean> handler = new BeanHandler( UserBean.class );
UserBean user = null;
try {
user=run.query( sqlStatements.getProperty( LOGIN_BY_USERNAME ),
handler, name );
} catch( SQLException ex ) {
Logger.getLogger( UserDataMapper.class.getName() ).
log( Level.SEVERE, null, ex );
}
return user;
}


That's a lot less 'faffing about' reading the fields of a ResultSet into
a simple bean, and a much higher signal-to-noise ratio imo.

The problem is, this only works for reading a simple entity. There
doesn't seem to be any equivalent for update, create, or delete.

So my question is: does any have experience with dbutils and see's
something I'm missing? Would you take a look at the docs even if you
don't have experience with dbutils?

And: is there a better, light-weight non-ORM package that you might
recommend instead? Something a bit more complete.

Anyway, I'm in the middle of adding basic update and create, and it's
actually going well. (It'd be going better if I weren't some clumsy
with SQL syntax.) But I thought I'd ask to see what other ideas the
folks here on this news group might have.

Thanks!

 
Reply With Quote
 
 
 
 
Arved Sandstrom
Guest
Posts: n/a
 
      04-30-2012
On 12-04-30 06:55 PM, markspace wrote:
> Hey all,
>
> I'm making a small website as a personal project using only the JDBC
> interface. (No ORM, etc.) Well, I did the CRUD for exactly one bean
> and found it pretty tedious going. So I started looking around for
> something light-weight to help me out. I found the Apache commons
> dbutils project:
>
> <http://commons.apache.org/dbutils/>
>
> This makes reading a bean much much easier. It does most of the column
> to property matching for you and will read an entity into a bean with
> only a few lines of code. Here's a (mostly) complete example from my
> little project:
>
> public UserBean getByUsername( String name ) {
> QueryRunner run = new QueryRunner( dataSource );
> BeanHandler<UserBean> handler = new BeanHandler( UserBean.class );
> UserBean user = null;
> try {
> user=run.query( sqlStatements.getProperty( LOGIN_BY_USERNAME ),
> handler, name );
> } catch( SQLException ex ) {
> Logger.getLogger( UserDataMapper.class.getName() ).
> log( Level.SEVERE, null, ex );
> }
> return user;
> }
>
>
> That's a lot less 'faffing about' reading the fields of a ResultSet into
> a simple bean, and a much higher signal-to-noise ratio imo.
>
> The problem is, this only works for reading a simple entity. There
> doesn't seem to be any equivalent for update, create, or delete.
>
> So my question is: does any have experience with dbutils and see's
> something I'm missing? Would you take a look at the docs even if you
> don't have experience with dbutils?


I haven't used DBUtils myself, but right in
http://commons.apache.org/dbutils/examples.html I can see examples of
INSERTs and UPDATEs (so presumably DELETEs are fine too :_))

> And: is there a better, light-weight non-ORM package that you might
> recommend instead? Something a bit more complete.


Check out http://www.mybatis.org/java.html.

> Anyway, I'm in the middle of adding basic update and create, and it's
> actually going well. (It'd be going better if I weren't some clumsy
> with SQL syntax.) But I thought I'd ask to see what other ideas the
> folks here on this news group might have.
>
> Thanks!
>

AHS

--
A fly was very close to being called a "land," cause that's what they do
half the time.
-- Mitch Hedberg
 
Reply With Quote
 
 
 
 
markspace
Guest
Posts: n/a
 
      05-01-2012
On 4/30/2012 4:56 PM, Arved Sandstrom wrote:

> I haven't used DBUtils myself, but right in
> http://commons.apache.org/dbutils/examples.html I can see examples of
> INSERTs and UPDATEs (so presumably DELETEs are fine too :_))



I looked at that but didn't strongly consider it, I guess. It seems
(still) a bit more tedious that I'd like. For example, to update some
bean, you have to call "get" on each one of its properties. So, while
the docs show this:

// Now it's time to rise to the occation...
int updates = run.update( "UPDATE Person SET height=? WHERE name=?",
2.05, "John Doe" );

A slightly more realistic example would be something like this:

// Now it's time to rise to the occation...
int updates = run.update( "UPDATE Person SET height=?, weight=?,
id=?, foo=?, bar=?, argleBarble=? WHERE name=?",
aBean.getHeight(), aBean.getWeight(), aBean.getId(),
aBean.getFoo(), aBean.getBar(), aBean.getArgleBarble(), aBean.getName() );

Which is just a tad messier than I want:

SimpleSql.updateBean( dataSource, aBean );

Although I can see advantages with their method. Theirs is a lot more
flexible, and perhaps I'm being too narrow minded in how I expect a bean
to be used in practice.

Or possibly I'm having too much fun faffing about with the reflection
API.

>
>> And: is there a better, light-weight non-ORM package that you might
>> recommend instead? Something a bit more complete.

>
> Check out http://www.mybatis.org/java.html.


Thanks for that, I'll check it out.

 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      05-01-2012
On Monday, April 30, 2012 2:55:51 PM UTC-7, markspace wrote:
> Hey all,
>
> I'm making a small website as a personal project using only the JDBC
> interface. (No ORM, etc.) Well, I did the CRUD for exactly one bean


That's funny. You say, "No ORM", then immediately describe the ORM library you're using.

> and found it pretty tedious going. So I started looking around for


Yes, it is. That's why ORM frameworks are popular.

I've done this exercise myself, repeatedly. I've written a handful of project-specific ORM layers, used a number of off-the-shelf products, and done direct comparisons between JPA and raw JDBC idioms (with custom ORM) with two or more idiomatic approach each for the JPA and JDBC styles.

The idiom that won for me was non-monolithic JPA (as opposed to the monolithic idiom I've seen in most shops and was the root of their complaints about JPA).

It is very light weight, for how I use the term "light weight".

How do you mean the term, precisely?

> something light-weight [sic] to help me out. I found the Apache commons
> dbutils project:
>
> <http://commons.apache.org/dbutils/>
>
> This makes reading a bean much much easier. It does most of the column
> to property matching for you and will read an entity into a bean with
> only a few lines of code. Here's a (mostly) complete example from my
> little project:
>
> public UserBean getByUsername( String name ) {
> QueryRunner run = new QueryRunner( dataSource );
> BeanHandler<UserBean> handler = new BeanHandler( UserBean.class );
> UserBean user = null;
> try {
> user=run.query( sqlStatements.getProperty( LOGIN_BY_USERNAME ),
> handler, name );
> } catch( SQLException ex ) {
> Logger.getLogger( UserDataMapper.class.getName() ).
> log( Level.SEVERE, null, ex );
> }
> return user;
> }
>
>
> That's a lot less 'faffing about' reading the fields of a ResultSet into
> a simple bean, and a much higher signal-to-noise ratio imo.


Yes, that's the advantage of ORMs generally.

I prefer EclipseLink and OpenJPA, myself. They go so far as to abstract away even that pseudo-SQL, for the common case. You write some annotations andBob's your uncle.

> The problem is, this only works for reading a simple entity. There
> doesn't seem to be any equivalent for update, create, or delete.
>
> So my question is: does any have experience with dbutils and see's
> something I'm missing? Would you take a look at the docs even if you
> don't have experience with dbutils?
>
> And: is there a better, light-weight non-ORM package that you might
> recommend instead? Something a bit more complete.


How is the one you're using not ORM?

It maps between objects and relational entities. Object-to-relational mapping. Q.E.D.

> Anyway, I'm in the middle of adding basic update and create, and it's
> actually going well. (It'd be going better if I weren't some clumsy
> with SQL syntax.) But I thought I'd ask to see what other ideas the
> folks here on this news group might have.


JPA.

--
Lew

 
Reply With Quote
 
markspace
Guest
Posts: n/a
 
      05-01-2012
On 4/30/2012 6:03 PM, Lew wrote:
>
> That's funny. You say, "No ORM", then immediately describe the ORM
> library you're using.

....
> How is the one you're using not ORM?



Well, I'll point out the main landing page for the project specifically
says that dbutils is not an ORM.

"DbUtils is not:

An Object/Relational bridge - there are plenty of good O/R tools
already. DbUtils is for developers looking to use JDBC without all the
mundane pieces."

<http://commons.apache.org/dbutils/>

If the authors of the projects say it's not ORM, I'll choose to believe
them.


> The idiom that won for me was non-monolithic JPA (as opposed to the
> monolithic idiom I've seen in most shops and was the root of their
> complaints about JPA).



Could you describe what you mean by "non-monolithic" vs "monolithic?" I
don't think I've heard those terms before and I'd be interested to see
what you are referring too.

As for "I've done this exercise myself, repeatedly" yeah I seem to be
going down the same road. Building small prototypes to see how the
result actually functions. In general I think doing is the best way to
learn, so I don't mind doing some extra work in order to get some learnin'.


> It is very light weight, for how I use the term "light weight".
>
> How do you mean the term, precisely?



I think I mean relatively speaking. Something is lighter weight in
comparison to certain alternates. Lighter weight in terms of options,
configuration required or configuration options, lighter weight in terms
of number features needed to learn, lighter weight in terms of API calls
needed to be conversant with before one can be productive.


> I prefer EclipseLink and OpenJPA, myself. They go so far as to
> abstract away even that pseudo-SQL, for the common case. You write
> some annotations and Bob's your uncle.



I'm somewhat conversant with JPA 2.0. I'm just looking at alternatives
for a comparison. What, if any, advantages do other APIs provide? So
far my personal jury is still out. Although I can see a customer for
example specify specifying something other than JPA for their own
reasons, and it might not (as in very probably not) be my place to
second-guess their business decision. That I think would be the main
reason, imo, to not use a full JPA solution.

 
Reply With Quote
 
Arved Sandstrom
Guest
Posts: n/a
 
      05-01-2012
On 12-04-30 11:27 PM, markspace wrote:
> On 4/30/2012 6:03 PM, Lew wrote:
>>
>> That's funny. You say, "No ORM", then immediately describe the ORM
>> library you're using.

> ...
>> How is the one you're using not ORM?

>
>
> Well, I'll point out the main landing page for the project specifically
> says that dbutils is not an ORM.
>
> "DbUtils is not:
>
> An Object/Relational bridge - there are plenty of good O/R tools
> already. DbUtils is for developers looking to use JDBC without all the
> mundane pieces."
>
> <http://commons.apache.org/dbutils/>
>
> If the authors of the projects say it's not ORM, I'll choose to believe
> them.


To an extent I'm happy to go with what authors say also. More precisely
DBUtils *is* an ORM - just look at the available ResultSetHandler
implementations like BeanListHandler - but it sure isn't an ORM like
Hibernate JPA or EclipseLink are.

I prefer to differentiate between basic OR mappers like DBUtils that do
low-level first-tier stuff, and full-blown JPA implementations. They are
all ORMs but there are massive differences in capability.

>> The idiom that won for me was non-monolithic JPA (as opposed to the
>> monolithic idiom I've seen in most shops and was the root of their
>> complaints about JPA).

>
>
> Could you describe what you mean by "non-monolithic" vs "monolithic?" I
> don't think I've heard those terms before and I'd be interested to see
> what you are referring too.


I am curious myself.

> As for "I've done this exercise myself, repeatedly" yeah I seem to be
> going down the same road. Building small prototypes to see how the
> result actually functions. In general I think doing is the best way to
> learn, so I don't mind doing some extra work in order to get some learnin'.
>
>> It is very light weight, for how I use the term "light weight".
>>
>> How do you mean the term, precisely?

>
> I think I mean relatively speaking. Something is lighter weight in
> comparison to certain alternates. Lighter weight in terms of options,
> configuration required or configuration options, lighter weight in terms
> of number features needed to learn, lighter weight in terms of API calls
> needed to be conversant with before one can be productive.


I tend to agree. For example, DBUtils allows one to create a set of
domain classes that can be rich (see other threads ) so long as they
are Java Beans, and they are unaware of DBUtils. It's extremely simple
to populate one or a list of these beans with ResultSet data.

But as far as I know there is no DBUtils transactions support. There is
certainly no object management. This makes it pretty lightweight to me.

With JPA you can go easy on the annotations, using ORM XML, and
therefore also have domain classes that are JPA unaware, but the
distinction remains that in JPA you had to configure the mappings
somewhere, but in DBUtils the mapping remains conceptual. Less artifacts
with DBUtils, seriously less capability, no object management - I know
what's lighter-weight in this picture.

>> I prefer EclipseLink and OpenJPA, myself. They go so far as to
>> abstract away even that pseudo-SQL, for the common case. You write
>> some annotations and Bob's your uncle.

>
> I'm somewhat conversant with JPA 2.0. I'm just looking at alternatives
> for a comparison. What, if any, advantages do other APIs provide? So
> far my personal jury is still out. Although I can see a customer for
> example specify specifying something other than JPA for their own
> reasons, and it might not (as in very probably not) be my place to
> second-guess their business decision. That I think would be the main
> reason, imo, to not use a full JPA solution.
>

For simple cases and/or relatively inexperienced programmers I actually
prefer that JPA be used. For the large majority of complex cases I
believe that JPA also is the choice. There is only s small percentage of
complex OR mapping situations where I think an experienced programmer
can make a case for simple mapping tools.

AHS
--
A fly was very close to being called a "land," cause that's what they do
half the time.
-- Mitch Hedberg
 
Reply With Quote
 
markspace
Guest
Posts: n/a
 
      05-01-2012
On 5/1/2012 6:29 AM, Arved Sandstrom wrote:

> On 12-04-30 11:27 PM, markspace wrote:
>> If the authors of the projects say it's not ORM, I'll choose to believe
>> them.


> To an extent I'm happy to go with what authors say also. More precisely
> DBUtils *is* an ORM - just look at the available ResultSetHandler



Right-o. I knew what Lew meant, but I still found his statement to be
inaccurate. It was clearly an exaggeration to say that dbutils is an
ORM just like JPA or Hibernate. JPA and dbutils are practically on
different planets. They solve a similar problem, but they take very
different approaches.


> I prefer to differentiate between basic OR mappers like DBUtils that do



This is good, I like the term "mapper" here instead of ORM. Data Mapper
is a design pattern. Specifically it's a lighter-weight one that DAO,
so it's a good fit to what dbutils is trying to accomplish.

<http://martinfowler.com/eaaCatalog/dataMapper.html>
<http://rrees.wordpress.com/2009/07/11/the-dao-anti-patterns/>


 
Reply With Quote
 
Daniel Pitts
Guest
Posts: n/a
 
      05-01-2012
On 4/30/12 2:55 PM, markspace wrote:
> Hey all,
>
> I'm making a small website as a personal project using only the JDBC
> interface. (No ORM, etc.) Well, I did the CRUD for exactly one bean and
> found it pretty tedious going. So I started looking around for something
> light-weight to help me out. I found the Apache commons dbutils project:

[snip].
> That's a lot less 'faffing about' reading the fields of a ResultSet into
> a simple bean, and a much higher signal-to-noise ratio imo.
>
> The problem is, this only works for reading a simple entity. There
> doesn't seem to be any equivalent for update, create, or delete.
>
> So my question is: does any have experience with dbutils and see's
> something I'm missing? Would you take a look at the docs even if you
> don't have experience with dbutils?
>
> And: is there a better, light-weight non-ORM package that you might
> recommend instead? Something a bit more complete.

Why avoid ORM? They do exactly what you're asking, and do it fairly well
for simple situations. If you find something that does what you're
asking without "faffing about", then you've found an ORM. You can always
use introspection to do that work yourself for creates/updates, but at
that point you've created a rudimentary ORM.
>
> Anyway, I'm in the middle of adding basic update and create, and it's
> actually going well. (It'd be going better if I weren't some clumsy with
> SQL syntax.) But I thought I'd ask to see what other ideas the folks
> here on this news group might have.

I know this isn't the answer you're looking for, but look into ORMs.
Unless you have a good reason to keep it low level, but realistically
there are only three reasons to do that, educational exercise or you're
building your own ORM, fear of the unknown.

Good luck with your endeavors,
Daniel.
 
Reply With Quote
 
markspace
Guest
Posts: n/a
 
      05-01-2012
On 5/1/2012 10:32 AM, Daniel Pitts wrote:

> there are only three reasons to do that, educational exercise



This is my intent exactly. What other options exist and to what extent
should I pay attention to them? That's what I'm working out.


> fear of the unknown.



This I've seen in the industry and in potential employers. My intent is
also defensive. Put a bit of something else on the resume, be able to
say I've done. I don't have to recommend it; I'll volunteer to point
out it's not best practice. But if they're paying and they insist it's
what they want, then the buyer gets what the buyer wants.

 
Reply With Quote
 
Daniel Pitts
Guest
Posts: n/a
 
      05-01-2012
On 5/1/12 11:22 AM, markspace wrote:
> On 5/1/2012 10:32 AM, Daniel Pitts wrote:
>
>> there are only three reasons to do that, educational exercise

>
>
> This is my intent exactly. What other options exist and to what extent
> should I pay attention to them? That's what I'm working out.
>
>
>> fear of the unknown.

>
>
> This I've seen in the industry and in potential employers. My intent is
> also defensive. Put a bit of something else on the resume, be able to
> say I've done. I don't have to recommend it; I'll volunteer to point out
> it's not best practice. But if they're paying and they insist it's what
> they want, then the buyer gets what the buyer wants.


Fair enough

Best of luck in that case
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
stand-alone JMS, other JDBC operations, and transactions ( ActiveMQ + JOTM + JDBC operations ) Jesus M. Salvo Jr. Java 2 02-11-2006 06:33 PM
oracle.jdbc.OracleDriver vs oracle.jdbc.driver.OracleDriver Betty Java 1 05-21-2005 05:15 PM
dev-utils gem... How do I require_gem 'dev-utils/debug' ? Eirikur Hallgrimsson Ruby 3 10-10-2004 07:47 AM
Re: jdbc help:sun.jdbc.odbc.JdbcOdbcDriver Keith Wansbrough Java 0 08-16-2004 07:31 PM
in using ADS 1.2, does utils.h = Utils.h for example ... in windows XP arm developer C++ 0 06-03-2004 02:33 AM



Advertisments