Thread: Non-ORM layers over JDBC
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
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
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
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
makethe 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
Objectsto a DB.
Regards,
Stephen Denne.
Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any
attachmentsis confidential and may 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.
__________________________________________________________________
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 Objectsto 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
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
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.
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
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/
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/03/19/the-dearth-of-oracle-rdbms-and-contracting/ On the other hand... "... data is the only thing with value..." http://oracledoug.com/serendipity/index.php?/archives/1394-Theres-Hope-for-us-all.html -- PA. http://alt.textdrive.com/nanoki/