SEO

Hi all

I'm fairly new to Java (sorry!) and struggling with the database access
area. My PostgreSQL database is fairy complex and significant parts of
its user interface are through stored procedures etc. I've been looking
into Hibernate / Hibernate EntityManager and other EJB3-compatible
tools, but they all seem to be oriented toward database-independence and
using the DB as a dumb storage engine. They even implement their own
outer joins (!) and other core DB functionality. Currently I'm trying to
figure out why a "SELECT c FROM customer c;" (Hibernate-style) is
introducing a WHERE clause for on the customer's bank_id when executed
.... so I'm not impressed so far.

However, I've also looked at raw JDBC code, and it seems to be very
verbose with a lot of manual data conversion and little language
integration.

I'm somewhat spoiled by having come from C++ with TrollTech's Qt, a
toolkit so good that it makes C++ not only worth using but even nice. It
has SQL-enabled widgets with a lot of programmer control, and a very
nice and well integrated SQL interface that doesn't impose too many
weird limits or force you to work around your SQL access layer to do
simple things. It makes good use of variant types and conversion
operators to minimise the amount of manual type wrangling the programmer
has to do.

I've done a fair bit of searching but haven't found any sort of JDBC
over-layer that integrates database access into the Java language a bit
better but without all the ORM database-independence stuff.

What do people here use? Just use the JDBC directly? Use an ORM layer
even though you just target PostgreSQL and work around its limitations
and quirks? Use a different ORM layer without weird limitations and
quirks? Some other tool I haven't been able to find out about?

I'm writing a pretty ordinary desktop app, but I was really hoping to
avoid writing vast amounts of database interface code even for the
simpler parts of the interface, as appears to be necessary with direct
JDBC use.

Comments on what you're using or would like to have used would be very
much appreciated.

--
Craig Ringer

-
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

If you want JDBC access that is more closely integrated into the language I
would suggest using Groovy. It REALLY simplifies JDBC access because of
Groovy's dynamic typing, which is basically the same thing as using variant
data types in C++, at least syntactically. Groovy's way of executing JDBC's
statements is also much easier to use. Groovy compiles to Java class files
and the JVM doesn't know the difference. The groovy runtime/library is just
a jar file that you stick on your classpath.

ORM for me works really well in OLTP situations. If I am doing pure OLTP I
rarely need to go outside of my ORM access layer, which is Hibernate.
Hibernate's query language (HQL) has lots of features to make writing SQL
queries easier and lots of features to minimize performance problems. If you
are used to SQL, it make take a little getting used to because HQL is more
abstract than SQL. It's like making a jump from C to Java, more abstraction,
less code, less raw power.

If you have lots of screens where users are basically building up sql queries,
using forms, then Hibernate's query by criteria makes this easy because you
are not longer manually building up SQL (or HQL) queries by hand (which is
really error prone). All of my complicated search screens use this feature
of Hibernate.

ORM falls down badly for two things: 1) OLAP style database work and 2) Batch
processing. OLAP depends way too much on specific database facilities to
make things fast, which Hibernate can't take advantage of. Batch processing
chokes because Hibernate will cache too much because it is trying to optimize
OLTP style interactions.

David Clark


On Monday 24 March 2008, Craig Ringer wrote:
> Hi all
>
> I'm fairly new to Java (sorry!) and struggling with the database access
> area. My PostgreSQL database is fairy complex and significant parts of
> its user interface are through stored procedures etc. I've been looking
> into Hibernate / Hibernate EntityManager and other EJB3-compatible
> tools, but they all seem to be oriented toward database-independence and
> using the DB as a dumb storage engine. They even implement their own
> outer joins (!) and other core DB functionality. Currently I'm trying to
> figure out why a "SELECT c FROM customer c;" (Hibernate-style) is
> introducing a WHERE clause for on the customer's bank_id when executed
> ... so I'm not impressed so far.
>
> However, I've also looked at raw JDBC code, and it seems to be very
> verbose with a lot of manual data conversion and little language
> integration.
>
> I'm somewhat spoiled by having come from C++ with TrollTech's Qt, a
> toolkit so good that it makes C++ not only worth using but even nice. It
> has SQL-enabled widgets with a lot of programmer control, and a very
> nice and well integrated SQL interface that doesn't impose too many
> weird limits or force you to work around your SQL access layer to do
> simple things. It makes good use of variant types and conversion
> operators to minimise the amount of manual type wrangling the programmer
> has to do.
>
> I've done a fair bit of searching but haven't found any sort of JDBC
> over-layer that integrates database access into the Java language a bit
> better but without all the ORM database-independence stuff.
>
> What do people here use? Just use the JDBC directly? Use an ORM layer
> even though you just target PostgreSQL and work around its limitations
> and quirks? Use a different ORM layer without weird limitations and
> quirks? Some other tool I haven't been able to find out about?
>
> I'm writing a pretty ordinary desktop app, but I was really hoping to
> avoid writing vast amounts of database interface code even for the
> simpler parts of the interface, as appears to be necessary with direct
> JDBC use.
>
> Comments on what you're using or would like to have used would be very
> much appreciated.
>
> --
> Craig Ringer
>
> -
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc



-
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

David Clark wrote:
> If you want JDBC access that is more closely integrated into the language I
> would suggest using Groovy. It REALLY simplifies JDBC access because of
> Groovy's dynamic typing, which is basically the same thing as using variant
> data types in C++, at least syntactically. Groovy's way of executing JDBC's
> statements is also much easier to use. Groovy compiles to Java class files
> and the JVM doesn't know the difference. The groovy runtime/library is just
> a jar file that you stick on your classpath.
>
This app needs to be maintainable by others and rather future-proof, so
I'm a little leery of Groovy as something that's still a bit "out there"
in Java land. I suspect that the best option here is plain old JDBC.
Thankfully the app won't need too much SQL with highly variable WHERE
clauses etc, so it shouldn't be too bad.

I've just spent some time looking over the website for iBatis, though,
and I'd be interested in hearing if anybody has any experience with /
comments about it. It looks closer to what I'm after than hibernate and
friends do.

http://ibatis.apache.org/overview.html
> ORM for me works really well in OLTP situations. If I am doing pure OLTP I
> rarely need to go outside of my ORM access layer, which is Hibernate.
> Hibernate's query language (HQL) has lots of features to make writing SQL
> queries easier and lots of features to minimize performance problems.
Yep, it seems pretty attractive for that sort of role - but not much use
for a 2 tier database app.

I'm trying to keep middleware application servers etc out of the picture
to reduce the number of required components - simplify maintainance and
administration, reduce dependencies, etc.

If I wasn't taking that approach I'd probably just build it in C++ with Qt .
> If you have lots of screens where users are basically building up sql queries,
> using forms, then Hibernate's query by criteria makes this easy because you
> are not longer manually building up SQL (or HQL) queries by hand (which is
> really error prone). All of my complicated search screens use this feature
> of Hibernate.
>
Yep, I truly detest the "Hmm, do I need an AND here" comma-and-keyword
juggling sort of low level syntax fiddling that's required for building
up SQL queries programatically. The query languages offered by things
like Hibernate look nice for that, but Hibernate its self seems to be
designed for use with middleware servers to the detriment of most other
things. I haven't found much good said about its use in "direct"
database apps, though it's clearly great for use in an appserver.

I've also found it more difficult than I thought to plug Hibernate
queries directly into Swing for use as a data model, providing efficient
database fetching etc. Then again, my Java is rather limited as is my
Swing experience, so I might just be doing it wrong.

--
Craig Ringer

-
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Craig Ringer wrote
> I'm fairly new to Java (sorry!) and struggling with the
> database access
> area. My PostgreSQL database is fairy complex and significant
> parts of
> its user interface are through stored procedures etc. I've
> been looking
> into Hibernate / Hibernate EntityManager and other EJB3-compatible
> tools, but they all seem to be oriented toward
> database-independence and
> using the DB as a dumb storage engine. They even implement their own
> outer joins (!) and other core DB functionality. Currently
> I'm trying to
> figure out why a "SELECT c FROM customer c;" (Hibernate-style) is
> introducing a WHERE clause for on the customer's bank_id when
> executed
> ... so I'm not impressed so far.

A lot of ORMs are designed for ease of persisting objects, not ease of realising databases.

Though I haven't used it, so can't guess whether it would be a good match for your needs or not... one tool I think may make the database to object mapping easier is ActiveObjects.

> However, I've also looked at raw JDBC code, and it seems to be very
> verbose with a lot of manual data conversion and little language
> integration.

I use and quite like Spring-JDBC. Among other things, it makes JDBC use much less verbose.

In Java, a lot of projects use some form of data-binding between their GUI and Objects, then an ORM to persist those Objects to a DB.

Regards,
Stephen Denne.

Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential andmay be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way.
__________________________________________________ ________________
This email has been scanned by the DMZGlobal Business Quality
Electronic Messaging Suite.
Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
__________________________________________________ ________________



-
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Stephen Denne wrote:

> I use and quite like Spring-JDBC. Among other things, it makes JDBC use much less verbose.

Yes, that looks very interesting, and much closer to what I'm after than
Hibernate, Toplink, etc.

Spring looks a bit like the Boost of the Java world, and it's a good
thing to know about in general.

> In Java, a lot of projects use some form of data-binding between their GUI and Objects, then an ORM to persist those Objects to a DB.

Yep, I got that impression. Most of them seem to be coming from the
opposite side to me, though - "I have a Java application that needs data
persistence" as opposed to "I have a database I need to provide a user
interface to". The former tends to lead to the desire for database
portability and a Java layer hiding the database; the latter makes
direct SQL database access with usable language interfaces more desirable.

The difference results in rather different design choices and needs,
making most of the ORM stuff fairly unattractive to me. In particular,
don't want the database specifics hidden. I'm already utterly tied to
PostgreSQL (but not to Java) and DB portability is just a limiting
annoyance. Perhaps if I'd started with a 3-tier design with a Java
application server it'd be different, but in the design as it stands the
database is very much the core with much of the "business logic" and
reporting smarts in PL/PgSQL. I'm not a fan of introducing an
essentially spurious middle layer or dumbing down my database use to
database-portable levels, so the ORM tools aren't too attractive.

Unfortunately the vast majority of the information out there approaches
database with Java from the java-centric rather than database-centric
angle, making it hard to find out about the sort of tools I'm after.

Thanks so much for the tip about Spring-JDBC . It looks pretty good.

--
Craig Ringer


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

On 24-Mar-08, at 1:40 PM, Craig Ringer wrote:

> David Clark wrote:
>> If you want JDBC access that is more closely integrated into the
>> language I would suggest using Groovy. It REALLY simplifies JDBC
>> access because of Groovy's dynamic typing, which is basically the
>> same thing as using variant data types in C++, at least
>> syntactically. Groovy's way of executing JDBC's statements is also
>> much easier to use. Groovy compiles to Java class files and the
>> JVM doesn't know the difference. The groovy runtime/library is
>> just a jar file that you stick on your classpath.
>>
> This app needs to be maintainable by others and rather future-proof,
> so I'm a little leery of Groovy as something that's still a bit "out
> there" in Java land. I suspect that the best option here is plain
> old JDBC. Thankfully the app won't need too much SQL with highly
> variable WHERE clauses etc, so it shouldn't be too bad.
>
I think groovy is going to have considerable staying power.
> I've just spent some time looking over the website for iBatis,
> though, and I'd be interested in hearing if anybody has any
> experience with / comments about it. It looks closer to what I'm
> after than hibernate and friends do.
>
> http://ibatis.apache.org/overview.html
>> ORM for me works really well in OLTP situations. If I am doing
>> pure OLTP I rarely need to go outside of my ORM access layer, which
>> is Hibernate. Hibernate's query language (HQL) has lots of
>> features to make writing SQL queries easier and lots of features to
>> minimize performance problems.
> Yep, it seems pretty attractive for that sort of role - but not much
> use for a 2 tier database app.
>
> I'm trying to keep middleware application servers etc out of the
> picture to reduce the number of required components - simplify
> maintainance and administration, reduce dependencies, etc.
>
> If I wasn't taking that approach I'd probably just build it in C++
> with Qt .
>> If you have lots of screens where users are basically building up
>> sql queries, using forms, then Hibernate's query by criteria makes
>> this easy because you are not longer manually building up SQL (or
>> HQL) queries by hand (which is really error prone). All of my
>> complicated search screens use this feature of Hibernate.
>>
> Yep, I truly detest the "Hmm, do I need an AND here" comma-and-
> keyword juggling sort of low level syntax fiddling that's required
> for building up SQL queries programatically. The query languages
> offered by things like Hibernate look nice for that, but Hibernate
> its self seems to be designed for use with middleware servers to the
> detriment of most other things. I haven't found much good said about
> its use in "direct" database apps, though it's clearly great for use
> in an appserver.
>
> I've also found it more difficult than I thought to plug Hibernate
> queries directly into Swing for use as a data model, providing
> efficient database fetching etc. Then again, my Java is rather
> limited as is my Swing experience, so I might just be doing it wrong.
>
> --
> Craig Ringer
>
> -
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Hello.

On Mar 24, 2008, at 6:45 , Craig Ringer wrote:
> I'm fairly new to Java (sorry!) and struggling with the database
> access area.

A newbie should not question if he should use a ORM, but instead focus
on which ORM to use. Please spend some time finding a good ORM that
you feel comfortable with.

Why you should use an ORM:
* Less boring code to write when bringing over data from PostgreSQL
to your java application and back.
* Simpler refactoring. What if you want to change the primary key,
add a relationship or whatever.
* Easier to support several databases. Thanks to Apache Cayenne, my
app work with PostgreSQL, MS SQL Server and Derby and others.
* And lots of other good reasons you will discover.

When to not use an ORM:
* Some specially tuned reporting queries

> What do people here use? Just use the JDBC directly? Use an ORM
> layer even though you just target PostgreSQL and work around its
> limitations and quirks? Use a different ORM layer without weird
> limitations and quirks? Some other tool I haven't been able to find
> out about?

I am using Apache Cayenne. It is a simple and elegant ORM with a swing
modeler tool that helps you create the mapping.

http://cayenne.apache.org/

Regards,
- Tore.



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

On Wed, 2008-03-26 at 11:21 +0100, Tore Halset wrote:
> Hello.
>
> On Mar 24, 2008, at 6:45 , Craig Ringer wrote:
> > I'm fairly new to Java (sorry!) and struggling with the database
> > access area.
>
> A newbie should not question if he should use a ORM, but instead focus
> on which ORM to use. Please spend some time finding a good ORM that
> you feel comfortable with.
>
> Why you should use an ORM:
> * Less boring code to write when bringing over data from PostgreSQL
> to your java application and back.
> * Simpler refactoring. What if you want to change the primary key,
> add a relationship or whatever.
> * Easier to support several databases. Thanks to Apache Cayenne, my
> app work with PostgreSQL, MS SQL Server and Derby and others.
> * And lots of other good reasons you will discover.
>
> When to not use an ORM:
> * Some specially tuned reporting queries

I'm the architect for a few large products that make heavy use of a
database, and I've worked fairly significantly both with raw JDBC and
with Hibernate. I'll be the first to admit that I don't know
everything, and my ORM experience is limited just to Hibernate, but
here's our experience for what it's worth.

If you're starting a new project (new DB, new code), then I would
definitely go with something like Hibernate or another ORM tool. Even
if you're used to writing straight SQL, it takes much of the tedium out
of working with the database and can be made to perform reasonably well
as long as you think before you type. You will need to do things the
"Hibernate way", but as long as you know that going in, it really
doesn't get in the way.

But if you are dealing with either existing code or an existing database
schema, my experience has been very different. Trying to adapt existing
object-oriented code to use Hibernate has proven to be extremely painful
because even though Hibernate is reputed to be an extremely configurable
ORM, many common object-oriented techniques just don't map very well and
you end up rewriting big chunks of code that you didn't expect to need
to. Like I mentioned above, code written without an ORM in mind just
won't map cleanly.

Trying to adapt Hibernate to an existing database schema is less
painful, but still seems more like an obstacle than a help, because a
design that makes sense in a relational world does not always (or even
usually) map cleanly onto an object-oriented world. You end up
generating somewhat exotic mappings and/or changing your database
structure just so your mapping works. It's rarely worth the effort.

Especially in the OP's case, where he already has an existing PG
database with large portions of the business logic in stored procedures,
I'd steer clear of ORM solutions.

For examples of using a database with a Swing GUI without an ORM layer,
you might want to look at the google results for "ResultSet TableModel"
or "Rowset TableModel" (note that I haven't used Rowsets in PG before,
they look promising and easier to use than ResultSets, but I have no
experience with how well they work in practice).

-- Mark

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

On Mar 25, 2008, at 2:01 AM, Craig Ringer wrote:

> Unfortunately the vast majority of the information out there
> approaches
> database with Java from the java-centric rather than database-centric
> angle, making it hard to find out about the sort of tools I'm after.

One approach that works rather well in a database-centric environment
is to entirely abstract the database away as a RPC service (also known
as stored procedure).

Using DatabaseMetaData to automatically process the relevant
CallableStatement make the use of stored procedure very transparent to
the client as the interaction with the database boil down to the
equivalent of a regular method call.

Something along the lines of a lightweight Remote Method Invocation
(RMI), but natively supported by your database (perhaps Remote
Database Invocation aka RDI

--
PA.
http://alt.textdrive.com/nanoki/


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

On Mar 26, 2008, at 11:21 AM, Tore Halset wrote:

> A newbie should not question if he should use a ORM, but instead
> focus on which ORM to use.

Oh, my...

"... the war has been lost and there are only a few pockets of
resistance left now, resistance that will sooner or later be squashed.
The religious war regarding sensible, about pragmatic use of databases
and database code, about doing work related to data in the database,
about data quality being enforced in the database, etc versus the
database should be a bit bucket camp..."

http://orastory.wordpress.com/2008/0...d-contracting/

On the other hand...

"... data is the only thing with value..."

http://oracledoug.com/serendipity/in...or-us-all.html

--
PA.
http://alt.textdrive.com/nanoki/

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc