Thread: JPA and desktop apps

Hi all

This is mildly OT, so please ignore unless you're interested in and work
with PostgreSQL via JPA/Hibernate/EclipseLink/etc in a desktop app setting.

Ages ago, I asked here if others were using JDBC directly to access Pg
from their java apps, or if they were using an ORM. I sought advice.
Many suggested that an ORM was a strongly preferable option, and partly
as a result of that advice I ended up using the JPA interface (with
Hibernate powering it) for my database access in my Swing app.

I've now concluded that, while it seems great on the surface, that
approach is absolutely awful, because of issues with detatched entities,
lazy property fetching, and the need to run database work on a non-EDT
thread to avoid UI stalls/freezes and repaint delays.

More details here:

http://soapyfrogs.blogspot.com/2010/07/jpa-and-hibernateeclipselinkopenjpaetc.html

Anyway, since folks here advised me to go for an ORM like Hibernate, I'm
curious: How do you handle the need to avoid blocking on database access
in your Java GUI apps, while lazily fetching properties of entities to
avoid downloading half the database whenever you query it for anything?

--
Craig Ringe

There's no one good answer to your question, although you've gone a
long way to answering it with this: "while lazily fetching properties
of entities to avoid downloading half the database whenever you query
it for anything."  Quite simply, you cannot allow the UI to access
lazily fetched properties if you cannot tolerate the UI blocking while
it loads.  I don't use JPA, but I do use hibernate, and removing the
JPA layer gives me a much richer API for interacting with hibernate.
I can write an HQL query which loads the properties that I know will
be utilized in a particular context, avoiding lazy loading, even
though my mappings support it (look for "join fetch" in the hibernate
HQL docs).  I can disable lazy loading entirely in my mappings, or
control it via my mappings and ensure that things are fetched in
chunks larger than my UI displays them.  When it comes to web services
type apps, which often serialize objects out to a remote client, I use
something like dozer to map my hibernate objects over to plain beans
in my controller via a mapping that only copies the properties I care
about, otherwise every request tends to get a copy of most of the db.
Then the view deals only with the non-hibernate versions of your
objects.  There are a variety of approaches you can use, but which one
works best will inevitably be application and schema dependent. I will
say that I've had no luck using any of the automatic marshaling
frameworks precisely because they wind up iterating over the entire db
when you serialize a single object.  I always wind up mapping to
non-hibernate objects and then marshaling those, which adds latency
and workload, though this is very unlikely to be noticeable on a
single-user desktop app.

--sam


On Wed, Jul 21, 2010 at 8:07 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> Hi all
>
> This is mildly OT, so please ignore unless you're interested in and work
> with PostgreSQL via JPA/Hibernate/EclipseLink/etc in a desktop app setting.
>
> Ages ago, I asked here if others were using JDBC directly to access Pg
> from their java apps, or if they were using an ORM. I sought advice.
> Many suggested that an ORM was a strongly preferable option, and partly
> as a result of that advice I ended up using the JPA interface (with
> Hibernate powering it) for my database access in my Swing app.
>
> I've now concluded that, while it seems great on the surface, that
> approach is absolutely awful, because of issues with detatched entities,
> lazy property fetching, and the need to run database work on a non-EDT
> thread to avoid UI stalls/freezes and repaint delays.
>
> More details here:
>
> http://soapyfrogs.blogspot.com/2010/07/jpa-and-hibernateeclipselinkopenjpaetc.html
>
> Anyway, since folks here advised me to go for an ORM like Hibernate, I'm
> curious: How do you handle the need to avoid blocking on database access
> in your Java GUI apps, while lazily fetching properties of entities to
> avoid downloading half the database whenever you query it for anything?
>
> --
> Craig Ringe
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>

JPA isn't wonder solution, it's works very good if you know it exactly and
know implementation of JPA you will use. Then when you create your
business
logic and database structure it can be optimized for JPA.

If you would like to go from legacy databases it can be sometimes
impossible (there can be problems with composite primary keys, working as
parts of FKs), but I saw one interesting way - create entities without
references. This means if A references B, then don't make reference, but
just put in A property bId, and then on demand load by
findById(,a.getBId()). Generally JPA isn't good for desktop applications,
this is due to transaction context (it can work without transactions,
ofc),
and on demand loading.

I use following way to speed up application.
1. First I create services for store/load.
2. For load I create search objects, that contains, if in need,
configuration parameters, from point of view of usage of searched objects.
Eg. UserSearch {nameLike ='%', wantGroups = true}
3. In this way service class can optimize read operations. When I don't
use eager fetching, I commonly use something like this or LEFT JOIN FETCH,
depends on configuration and provider
for (user : users)
 if (user.groups.size > 0) users.groups.get(0);
this will pre-cache entities.
4. You need always refer to JPA provider implementations, for special
configuration parameters, query parameters, or additional annotations
(this
will make application less portable, but difference between JPA providers
are sometimes such big and on such basic things, that portability is
fiction, unless your queries will be "SELECT a FROM A a").
5. Use in queries LEFT JOIN FETCH, don't use eager loading unless objects
are in composition.
6. Build queries in dynamic way. This will allow you to change query
depending on search specification given in 2.
7. Treat JPA much more like read / write mapping to database, instead of
"make everything" utility.

Generally make service layer in your application and build specialized
facades in this layer for complicated methods.

Regards,
Radek

P.S.
I works long with JPA through Hibernate and Postgresql, I still getting
bugs and "surprises".

On 22/07/10 14:31, Samuel Gendler wrote:
> There's no one good answer to your question, although you've gone a
> long way to answering it with this: "while lazily fetching properties
> of entities to avoid downloading half the database whenever you query
> it for anything."  Quite simply, you cannot allow the UI to access
> lazily fetched properties if you cannot tolerate the UI blocking while
> it loads.

Yeah, about what I feared.

It increasingly seems you can use JPA in desktop apps so long as you
don't want to provide a decent "experience" for the user, or don't mind
writing  reams of boilerplate proxies that JPA is supposed to remove the
need for.

>  I don't use JPA, but I do use hibernate, and removing the
> JPA layer gives me a much richer API for interacting with hibernate.
> I can write an HQL query which loads the properties that I know will
> be utilized in a particular context, avoiding lazy loading, even
> though my mappings support it (look for "join fetch" in the hibernate
> HQL docs).

The same is possible via JPA. The JPA query language is basically just HQL.

It still requires your app to know, in advance, what parts of what
objects will be required by what parts of the app. This becomes more of
a problem when you don't know what the user is going to do and how
they're going to interact with the objects, and you're using reusable
components that aren't aware of the nature of the objects they're
working with.

JPA (and ORM in general, including Hibernate etc) is "sold" on the
ability to use plain old java objects that map to database entities,
including using them as generic beans with any component or group of
components that works with beans. Yet, in reality, that just doesn't
work in any case where you need to pass in a large collection of
objects, only some of which will ever be examined in detail. You have to
load *all* the data for *all* the objects, just in case, or modify the
component to add JPA awareness that it's not supposed to need.

If you're going to do all that, and have to fight JPA's issues with
replacing whole object graphs in the process, using plain 'ol JDBC with
directly database-aware components starts to seem like a good idea. I
guess I'm just confused about why people seem to recommend using these
ORMs in Swing-based desktop apps, when they *REALLY* don't fit the Swing
event-driven non-blocking single-threaded programming model.

> I can disable lazy loading entirely in my mappings, or
> control it via my mappings and ensure that things are fetched in
> chunks larger than my UI displays them.  When it comes to web services
> type apps, which often serialize objects out to a remote client, I use
> something like dozer to map my hibernate objects over to plain beans
> in my controller via a mapping that only copies the properties I care
> about, otherwise every request tends to get a copy of most of the db.
> Then the view deals only with the non-hibernate versions of your
> objects.

Ugh, so you work around the limitation with yet another layer of
abstraction / proxying?

I get the feeling I'd spend so long building layer after layer to work
around limitations that I might as well just write the code that does
just what I need in the first place. How do you find your approach as
compared to just using JDBC directly?

I'm particularly amazed that the NetBeans platform doesn't seem to cover
issues with UI blocking. There are tutorials on JPA use with NBP
everywhere, but they all just sweep the blocking/reliability issues
under the "it's just a tutorial" rug. This gives the poor reader the
idea that this might actually be a good idea, leading them straight into
a world of pain and suffering.

As it is, I'm considering tossing the whole swing/JPA app I have out the
window and prototyping something in grails that reuses my existing
mappings. It's that or build yet more infrastructure, possibly down to
cglib proxies, just to make JPA play well with Swing. Or build a
controller layer to proxy for my proxy entities. It's so Enterprise-y,
maybe I should do that!

*sigh*

Enterprise architecture: keeping programmers employed by doing as much
work as possible to achieve as little as possible by hiding everything
within ten or more pointless layers to work around the artificial
limitations of the previous layers.

( I actually have a working system in place for handling lazy loading,
dispatching reloads to the database while displaying a busy uI to the
user, etc. It's just ugly and very annoying. )

> I always wind up mapping to
> non-hibernate objects and then marshaling those, which adds latency
> and workload, though this is very unlikely to be noticeable on a
> single-user desktop app.

Ah, sorry, I may've been unclear. It's very definitely not single user,
it has many users interacting with a database over the network. Each
instance of the app is single-user, but they all share one database.

--
Craig Ringer

Craig Ringer wrote:
> Ages ago, I asked here if others were using JDBC directly to access Pg
> from their java apps, or if they were using an ORM. I sought advice.
> Many suggested that an ORM was a strongly preferable option, and partly
> as a result of that advice I ended up using the JPA interface (with
> Hibernate powering it) for my database access in my Swing app.
>
> I've now concluded that, while it seems great on the surface, that
> approach is absolutely awful, because of issues with detatched entities,
> lazy property fetching, and the need to run database work on a non-EDT
> thread to avoid UI stalls/freezes and repaint delays.

I don't find JPA a problem at all - it's a huge boost.  Naturally you have to
deal with properly setting it up, just like anything else.

If lazy fetching is bad for you, use eager fetching.  Simple.

You need to run DB work on a non-GUI thread if you don't use an ORM.  How is
that different?

Detached entities are part of a solution, not part of a problem.

> Anyway, since folks here advised me to go for an ORM like Hibernate, I'm
> curious: How do you handle the need to avoid blocking on database access
> in your Java GUI apps, while lazily fetching properties of entities to
> avoid downloading half the database whenever you query it for anything?

Proper design of entities to fit with a proper object model.

Proper separation of view from model, just like if you don't use an ORM.

Methinks you blame the hammer for the carpenter.

--
Lew

On 23/07/10 20:48, Lew wrote:
> I don't find JPA a problem at all - it's a huge boost.  Naturally you
> have to deal with properly setting it up, just like anything else.
>
> If lazy fetching is bad for you, use eager fetching.  Simple.

Over potentially limited bandwidth connections where you want queries to
run in a reasonable amount of time? You're kidding, right? Not everyone
is using their app on the local gigabit LAN and doesn't have to consider
the possibility of the user's wifi/cellular connection dropping out.

There's a *huge* difference in bandwidth terms between "fetch customer
id and name" and "fetch all the data in the Customer relation, including
all its child objects like addresses and contact records."

Sure, some things make sense to eagerly fetch, because you expect
they'll get used in most parts of the app. But do you want all their
addressing and contact details every time you fetch them? Even if these
are (as in my case) separate relations accessed by reference, lazy
fetching is still an issue.

JPA offers few if any facilities to hook into lazy fetching to permit

> You need to run DB work on a non-GUI thread if you don't use an ORM.
> How is that different?

It's not. My point isn't that JPA is particularly bad, just that I see
very little advantage for its use in desktop apps in the *real* world.
I'm frustrated by all the currently-fashionable-framework based examples
that show it to be so "easy" and paper over the fact that actually,
making it useful requires a completely different approach.

>> Anyway, since folks here advised me to go for an ORM like Hibernate, I'm
>> curious: How do you handle the need to avoid blocking on database access
>> in your Java GUI apps, while lazily fetching properties of entities to
>> avoid downloading half the database whenever you query it for anything?
>
> Proper design of entities to fit with a proper object model.

Like, say, mapping entities from the database into native Java objects
managed by an ORM?

Isn't that the point?

> Proper separation of view from model, just like if you don't use an ORM.

Sure, that sounds nice. Now, we have this nice pre-made model/view
system in Swing, where all the Swing controls have suitable model
interfaces and are well suited to plugging in your own models.

Let's just use JPA with that existing infrastructure rather than rolling
our own new one. Most of them provide useful mechanisms to drive them
with POJOs, lists of POJOs etc via simple adapters, so they seem an
ideal fit for use with an ORM. Oh, wait, the Swing models aren't
callback/event driven, so you can't lazily load without blocking the UI.
We have to abstract the Swing models, building a model to drive the
model to drive the view.

This is what I'm talking about. Of course you can do it, it just sucks
in terms of productivity. JPA, Hibernate etc can be used on the desktop
in client apps, they're just (IMO) highly overrated. Most people who try
to use them only seem to discover how many problems they leave to the
programmer rather late in the process.

Consider:

http://blog.schauderhaft.de/2008/09/28/hibernate-sessions-in-two-tier-rich-client-applications/

> Methinks you blame the hammer for the carpenter.

Perhaps. Rather, I think this new JPA hammer is just a different kind of
hammer, better for some problems and worse for others. It is rather
overrated as the magic solution to all your database access problems as
it's often touted to be.

In this case, I think it hides and obscures more problems than it
solves. I didn't know that when I started, and want to help others be
more aware of the booby traps in this particular app design, caused
partly by conflicts between JPA blocking-call lazy loading and Swing's
non-blocking event-driven approach to GUIs. It's not that you can't make
it work, it's just way, way more fuss and boilerplate than you'd hope or
expect.

--
Craig Ringer

I'd like to add my 2p worth (2c for those using $).
The company I work for (cisx.com) wrote an entire suit of desktop
applications using Swing, JDBC and PostgreSQL.
We embarked on this project back in 2001 and looked at using more modern
architecture however with the likes of EJB2 on offer at that time we decided
that it wasn't mature enough and chose the above "old-school" two-tier
approach.
We wrote everything from scratch. An entire object based library that models
a flat table view of a SQL database. That is we modelled columns, rows and
tables. We created an API into the database via SQL VIEWS, so that the
client never has to perform joins, everything on the client remains as a
simple flat table. All of the business logic is embedded on the database,
however to make the client more user friendly you do have to replicate some
of this logic in the client code.
It works well as long as everything fits into the SQL table model. In the
real world this is not always the case and it becomes hard work trying to
fit "round pegs into square wholes" (the usual complaints).
We chose PostgreSQL purely on its LISTEN/NOTIFY capabilities such that we
have implemented an event driven model so that table views update
automatically  (we modified the JDBC driver to get non-polling asynchronous
events from the server).

General complaints:
- Maintenance is hard work when you have 100+ tables.
- The system requires a lot of naming conventions to be followed.
- Swing is badly written and I would avoid it at all costs if we were to
start again. We had to modify JTable to get virtual views however the model
still requires in advance the number of rows in the table. COUNT is an
expensive operation on tables that have millions of records in them.
The number of "hoop jumping" and "hacking" you have to do to get everything
working nicely with the AWT event queue is ridiculous.
I could go on an on about the problems with Swing. The main thing to note is
that you don't have to be using JPA to be having problems with it.
- Remote client connections via firewalls / proxy servers is problematic.
Again we modified the JDBC driver so that we could use tunnelling
techniques. This required us to redirect port 443 on our incoming firewalls
to port 5432 on the database server, meaning we cannot host a secure
web-server on the same IP address. That aside, all works reasonably well,
however  a lot of clients use "single sign on" for connecting to their proxy
servers and our application is non-compliant here because the amount of work
to modify the JDBC driver for this is beyond our available resources and
knowledge base. End result: a lot of clients have to have special proxy
settings just for our application, which they complain about endlessly...
- Upgrading remote clients means re-installing software on their computers
which is again not nice for these clients.
- Finally, there are many other areas that I have not mentioned, which
provide us with headaches - I'm only mentioning the main points here as I
have already rambled on beyond 2p worth...

Positive notes:
- High availability is simple with a two-tier approach, especially with
PostgreSQL's WAL system.

We are currently embarking on moving to a three-tier system using the thin
client model (web-server). The two technologies that we are considering are
GWT and Wicket.

There are so many things that the three-tier model architecture solves and
we believe these technologies are mature now. I would reconsider the
two-tier approach because no matter which way you go, JDBC or JPA, you are
going to have many problems to solve. IMO JPA is not designed for the
two-tier approach and you have pointed out many problems already and you
have only just started with the idea.
I guess you have to evaluate how complex the system you are building is and
whether it is worthy of a two-tier or three-tier architecture.

What ever you choose good luck!
Regards
Donald Fraser

----- Original Message -----
Subject: Re: [JDBC] JPA and desktop apps


> On 23/07/10 20:48, Lew wrote:
>> I don't find JPA a problem at all - it's a huge boost.  Naturally you
>> have to deal with properly setting it up, just like anything else.
>>
>> If lazy fetching is bad for you, use eager fetching.  Simple.
>
> Over potentially limited bandwidth connections where you want queries to
> run in a reasonable amount of time? You're kidding, right? Not everyone
> is using their app on the local gigabit LAN and doesn't have to consider
> the possibility of the user's wifi/cellular connection dropping out.
>
> There's a *huge* difference in bandwidth terms between "fetch customer
> id and name" and "fetch all the data in the Customer relation, including
> all its child objects like addresses and contact records."
>
> Sure, some things make sense to eagerly fetch, because you expect
> they'll get used in most parts of the app. But do you want all their
> addressing and contact details every time you fetch them? Even if these
> are (as in my case) separate relations accessed by reference, lazy
> fetching is still an issue.
>
> JPA offers few if any facilities to hook into lazy fetching to permit
>
>> You need to run DB work on a non-GUI thread if you don't use an ORM.
>> How is that different?
>
> It's not. My point isn't that JPA is particularly bad, just that I see
> very little advantage for its use in desktop apps in the *real* world.
> I'm frustrated by all the currently-fashionable-framework based examples
> that show it to be so "easy" and paper over the fact that actually,
> making it useful requires a completely different approach.
>
>>> Anyway, since folks here advised me to go for an ORM like Hibernate, I'm
>>> curious: How do you handle the need to avoid blocking on database access
>>> in your Java GUI apps, while lazily fetching properties of entities to
>>> avoid downloading half the database whenever you query it for anything?
>>
>> Proper design of entities to fit with a proper object model.
>
> Like, say, mapping entities from the database into native Java objects
> managed by an ORM?
>
> Isn't that the point?
>
>> Proper separation of view from model, just like if you don't use an ORM.
>
> Sure, that sounds nice. Now, we have this nice pre-made model/view
> system in Swing, where all the Swing controls have suitable model
> interfaces and are well suited to plugging in your own models.
>
> Let's just use JPA with that existing infrastructure rather than rolling
> our own new one. Most of them provide useful mechanisms to drive them
> with POJOs, lists of POJOs etc via simple adapters, so they seem an
> ideal fit for use with an ORM. Oh, wait, the Swing models aren't
> callback/event driven, so you can't lazily load without blocking the UI.
> We have to abstract the Swing models, building a model to drive the
> model to drive the view.
>
> This is what I'm talking about. Of course you can do it, it just sucks
> in terms of productivity. JPA, Hibernate etc can be used on the desktop
> in client apps, they're just (IMO) highly overrated. Most people who try
> to use them only seem to discover how many problems they leave to the
> programmer rather late in the process.
>
> Consider:
>
> http://blog.schauderhaft.de/2008/09/28/hibernate-sessions-in-two-tier-rich-client-applications/
>
>> Methinks you blame the hammer for the carpenter.
>
> Perhaps. Rather, I think this new JPA hammer is just a different kind of
> hammer, better for some problems and worse for others. It is rather
> overrated as the magic solution to all your database access problems as
> it's often touted to be.
>
> In this case, I think it hides and obscures more problems than it
> solves. I didn't know that when I started, and want to help others be
> more aware of the booby traps in this particular app design, caused
> partly by conflicts between JPA blocking-call lazy loading and Swing's
> non-blocking event-driven approach to GUIs. It's not that you can't make
> it work, it's just way, way more fuss and boilerplate than you'd hope or
> expect.
>
> --
> 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 27/07/10 18:39, Donald Fraser wrote:

> The company I work for (cisx.com) wrote an entire suit of desktop
> applications using Swing, JDBC and PostgreSQL.

The design you chose is similar to what I ended up with, except I was
using JPA instead of using JDBC directly. I, like you, have most
business logic directly in the database, though some has
non-authoritative duplicate checks in the client where it permits me to
offer earlier or better error reporting for the user.

I'm using a lot of stored procs as well as using views and HQL joins, so
I don't have the same square peg issues you've encountered. I sure do
have the Swing issues, though...

> We are currently embarking on moving to a three-tier system using the
> thin client model (web-server). The two technologies that we are
> considering are GWT and Wicket.

Out of interest, why not JSF2?

I'm seriously considering dropping my own app completely in favour of a
server-side rewrite, as I'm increasingly coming to see that while JPA
makes some Swing issues worse, and it has some big problems in the
desktop app context no matter what toolkit is used, a fair chunk of my
problem is with Swing not JPA.

( Session lifetime issues are one of the fun all-JPA problems not
contributed to by Swing or inherent to blocking database access. )

Anyway, GWT and Wicket are two on my short list. I considered testing
with Grails too, but I'm smelling a lot of hype, a lot of yelling about
how anything Grails doesn't play well with is "legacy", and other
warning signs that it's perhaps not as flexible as I might need. I also
have JSF2 on the shortlist and am wondering why you've ruled it out.

Note that I'm completely, 100% green on server-side Java, to the point
where I'm still wondering if Java is the right choice - perhaps I should
throw out my JPA/Hibernate mappings, validators, etc and re-plan the
whole affair entirely. So perhaps it's obvious why JSF2 is a bad choice,
and I'm just too green to know it.

Perhaps I should've just written the whole thing in PHP :S

> We chose PostgreSQL purely on its LISTEN/NOTIFY capabilities such that
> we have implemented an event driven model so that table views update
> automatically  (we modified the JDBC driver to get non-polling
> asynchronous events from the server).

Listen/notify is a significant part of why I chose my current structure.
Little did I know that Hibernate and JPA want to get in the way of using
it every way they can, to the point where I'd have to write my own
simplistic single-connection pool and Hibernate pool provider so I could
keep an eye on notifications without having to maintain a dedicated JDBC
connection for the purpose.

> - Maintenance is hard work when you have 100+ tables.

Using JPA certainly does help there, at least a bit. Pity Java doesn't
have true properties, so you still have to write and maintain legions of
accessors, which is OK first time (code generation) but unpleasant to
maintain as types and field names change.

> - Swing is badly written and I would avoid it at all costs if we were to
> start again. We had to modify JTable to get virtual views however the
> model still requires in advance the number of rows in the table. COUNT
> is an expensive operation on tables that have millions of records in them.
> The number of "hoop jumping" and "hacking" you have to do to get
> everything working nicely with the AWT event queue is ridiculous.
> I could go on an on about the problems with Swing. The main thing to
> note is that you don't have to be using JPA to be having problems with it.

While true, I think JPA is a really good way to highlight its issues and
limitations. The lazy loading stuff in particular really shows how
painful Swing can be to use when you have to deal with an API that may
block at unpredictable points in time.

> - Remote client connections via firewalls / proxy servers is
> problematic. Again we modified the JDBC driver so that we could use
> tunnelling techniques. This required us to redirect port 443 on our
> incoming firewalls to port 5432 on the database server, meaning we
> cannot host a secure web-server on the same IP address. That aside, all
> works reasonably well, however  a lot of clients use "single sign on"
> for connecting to their proxy servers and our application is
> non-compliant here because the amount of work to modify the JDBC driver
> for this is beyond our available resources and knowledge base. End
> result: a lot of clients have to have special proxy settings just for
> our application, which they complain about endlessly...

Interesting issue. Thanks for bringing it up. I never really considered
over-paranoid outbound packet filters as a likely issue when using
PgJDBC, but given my intended use case involves a lot of roaming users I
can see it would've potentially become a support concern.

> I guess you have to evaluate how complex the system you are building is
> and whether it is worthy of a two-tier or three-tier architecture.
>
> What ever you choose good luck!

Thanks for your time and comments. Good luck to you too with your redesign.

I'd be really interested in sharing experiences down the track on the
web framework side of things, as it sounds like we have similar needs
and I'm about to start writing a test app in each candidate framework
(!!) to see which sucks the least for my purposes.

--
Craig Ringer

> I'm using a lot of stored procs as well as using views and HQL joins, so
> I don't have the same square peg issues you've encountered. I sure do
> have the Swing issues, though...

We have 565 stored procedures and if the square peg issues were our only
problems then we wouldn't be considering moving away from this architecture.
The square peg issue is a minor inconvenience compared to the rest of the
problems.

>> We are currently embarking on moving to a three-tier system using the
>> thin client model (web-server). The two technologies that we are
>> considering are GWT and Wicket.
>
> Out of interest, why not JSF2?

There are so many flavours of enterprise architecture that claim to solve
all your problems that you have to look at what you think are your most
difficult problems and do they solve them.
As far as I can tell there are still a lot of naming conventions required
for JSF2 and boiler plate code to map things.
I'm reading a lot of forums to see what issues people have and what they
think of these technologies.
People that were once JSF2 ambassadors that have moved to Wicket claim it to
be so much better, hence why its not in my short list. I might be wrong but
you have to start somewhere and have some system of reducing your short list
before one waists a lot of time in writing test apps to really find what the
issues are. In short we are looking at key areas that we think are the
problem areas and investigating these issues. Namingly we would like a pure
message based system which requires ajax technology to make it work on the
thin client. So we are focusing on the ajax problem areas to see whether
these technologies cut it or not...

> I'm seriously considering dropping my own app completely in favour of a
> server-side rewrite, as I'm increasingly coming to see that while JPA
> makes some Swing issues worse, and it has some big problems in the
> desktop app context no matter what toolkit is used, a fair chunk of my
> problem is with Swing not JPA.
Here here!

> Note that I'm completely, 100% green on server-side Java, to the point
> where I'm still wondering if Java is the right choice - perhaps I should
> throw out my JPA/Hibernate mappings, validators, etc and re-plan the
> whole affair entirely. So perhaps it's obvious why JSF2 is a bad choice,
> and I'm just too green to know it.

We're pretty green on the server side Java too having spent the last 10
years working entirely on our two-tier systems.
We do have server side code for automation, however this is pretty simple
code that only uses the Java SE VM.
So we were pretty taken back when we loaded up GlassFish and started reading
up on what it has to offer. We could spend the next 6 months just learning
the in and outs of this JEE platform and is this the right JEE platform to
use? Yet another decision to make.

> Perhaps I should've just written the whole thing in PHP :S
Yes, our web-server code is PHP and we recently reworked the entire site so
that it is completely OO.
Its crossed my mind several times to say lets just write it all in PHP but I
have faith that these new technologies will work for you if you use them how
they were intended to be used. If only I had more time....

>> - Remote client connections via firewalls / proxy servers is
>> problematic. Again we modified the JDBC driver so that we could use
>> tunnelling techniques. This required us to redirect port 443 on our
>> incoming firewalls to port 5432 on the database server, meaning we
>> cannot host a secure web-server on the same IP address. That aside, all
>> works reasonably well, however  a lot of clients use "single sign on"
>> for connecting to their proxy servers and our application is
>> non-compliant here because the amount of work to modify the JDBC driver
>> for this is beyond our available resources and knowledge base. End
>> result: a lot of clients have to have special proxy settings just for
>> our application, which they complain about endlessly...
>
> Interesting issue. Thanks for bringing it up. I never really considered
> over-paranoid outbound packet filters as a likely issue when using
> PgJDBC, but given my intended use case involves a lot of roaming users I
> can see it would've potentially become a support concern.

On top of the firewall/proxy server issues you have the session connection
management problems.
We limit each individual user to only one connection and because we are
using a normal TCP socket connection, most of the time, things work OK.
However when a connection is dropped over the internet the server will keep
the connection open until it knows otherwise. This causes problems when the
user tries to re-establish a connection. Unless you have session management
in place you don't know which connections need cleaning up... All of this
code had to be written in C, as a plug-in to the PostgreSQL server.
If I was to do this part of our app again I wouldn't use JDBC directly. I
would use a message bus to deliver the SQL requests / results sets. Your
users then scale better. Most message buss implementations have the option
to use the HTTP protocols as a delivery mechanism which makes tunnelling out
of firewall/proxy servers easy. Single sign on - not a problem, Sun
implemented this with the HTTP connection classes (If only the source code
to this was available...)

> I'd be really interested in sharing experiences down the track on the
> web framework side of things, as it sounds like we have similar needs
> and I'm about to start writing a test app in each candidate framework
> (!!) to see which sucks the least for my purposes.

It does seem that we have a lot in common and have come to many of the same
conclusions.
I too would be interested in sharing experiences. What pit-holes did we
uncover etc...

I'll keep you posted on any significant findings.
Regards
Donald

On 7/27/2010 2:56 AM, Craig Ringer wrote:

> This is what I'm talking about. Of course you can do it, it just sucks
> in terms of productivity. JPA, Hibernate etc can be used on the desktop
> in client apps, they're just (IMO) highly overrated. Most people who try
> to use them only seem to discover how many problems they leave to the
> programmer rather late in the process.

Agreed.  We used JPA on one good-sized (but not large) internal
application.  The amount of hand-holding it needed and the amount of
bulk it added to that app prompted us look at other solutions for a
rewrite of our customer portal.

Our team is proficient in SQL and JDBC, so we wanted something that
would take care of all the routine housekeeping but would not be as
bloated as JPA or Hibernate.  We settled on MyBatis
(http://www.mybatis.org/) formerly iBATIS.  This is a very light
framework; you write your own SQL using either an external XML file or
annotations, and it maps results to your Java objects and does all the
routine housekeeping like opening and closing connections, statements
and resultsets.    It has an optional Java class generator for producing
classes from database tables.

Given our skill set, MyBatis is a much more livable solution than JPA.
Given the huge amount of bloat in JPA, it doesn't seem to make sense for
individual applications.  If you have the luxury of starting from
scratch and building an enterprise model, then perhaps JPA would be a
good fit.

--
Guy Rouillier

On 7/27/2010 8:03 AM, Craig Ringer wrote:
> Out of interest, why not JSF2?
...
> Anyway, GWT and Wicket are two on my short list.

You seem to be traveling down the same road we did several months ago.
We are a Java shop, so that option is predetermined.  We used GWT to
implement the front end for the same app we used JPA for (replied in a
different email.)

After dealing with the many issues with GWT (and to be fair, the same
issues probably apply to any solution heavily slanted to the
client-side), we elected to try JSF for our customer portal.  That
turned out to be an excellent choice.  JSF (specifically facelets, which
I'd highly recommend; for JSF2, facelets is the default implementation)
provides a much more responsive environment (from the user's
perspective) that earlier JSP.  Using a server-side solution is *much*
easier to work with, as you are not having to constantly shuffle data
back and forth to the client.

So, unless you have very compelling requirements that require
significant client interaction without returning to the server, I'd
highly recommend JSF/facelets.  Obviously, if you are not committed to
Java, then perhaps other options are more appropriate to you environment
and/or skill set.

--
Guy Rouillier

On 28/07/10 04:35, Guy Rouillier wrote:

> Our team is proficient in SQL and JDBC, so we wanted something that
> would take care of all the routine housekeeping but would not be as
> bloated as JPA or Hibernate.  We settled on MyBatis
> (http://www.mybatis.org/) formerly iBATIS.

How did you find that it played with Pg features like:

- Custom and domain types
- User/role security
- column privileges

?

I had to fiddle a bit to make Hibernate play well with per-user logins.
It wants a single all-powerful database login and security implemented
in the app. I prefer someone else's already-tested security system to
inventing my own, so I was using user rights in the database. I had
issues making Hibernate log in with a dynamic username/password, though
they weren't overly difficult to overcome. It also took some fiddling to
make Hibernate only list changed columns in UPDATE and INSERT queries so
it'd play well with column privileges.

iBatis / MyBatis looks *much* more like what I'm after in most ways, but
I'm a bit concerned about managing column privs. If simple INSERT/UPDATE
queries are hand-coded then it's going to take some interesting fiddling
to avoid trying to touch columns a user doesn't have the rights to
update. I'm concerned I'd have to write too many very similar queries.
In this particular area, Hibernate is a real boon - well, once I
convinced it to generate dynamic queries.

The security model isn't so complex that I can't just have a few
different UPDATE queries for different rights levels, but I'm hoping to
avoid it.

Have you dealt with this? Or do you not use column privs?

I'm still cursing the design of column privs in SQL. Requiring that cols
not even be listed in an UPDATE if you don't have UPDATE rights to them
- argh! It should be allowed to UPDATE a column to an identical value,
ie UPDATE x SET a = 'old-value-of-a' so long as you have at least SELECT
rights on that column. I'm sure there are good reasons it's how it is,
but it really, really stinks from an application dev point of view, and
SQL didn't need any more clumsy dynamic query construction crap than we
already have to deal with.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

On 7/28/2010 10:21 PM, Craig Ringer wrote:
> On 28/07/10 04:35, Guy Rouillier wrote:
>
>> Our team is proficient in SQL and JDBC, so we wanted something that
>> would take care of all the routine housekeeping but would not be as
>> bloated as JPA or Hibernate.  We settled on MyBatis
>> (http://www.mybatis.org/) formerly iBATIS.
>
> How did you find that it played with Pg features like:
>
> - Custom and domain types
> - User/role security
> - column privileges

Custom types: you can implement your own TypeHandler and simply tell
MyBatis to use that for specific columns.  I do that, for example, to
map columns containing foreign key values into Java enums.  The mapping
works in both directions, so once you've updated the value of a Java
enum in an object and want to update the DB, MyBatis will map the enum
back into the proper column value using your TypeHandler.  Of course,
all the default TypeHandlers are built in.

Per-user logins: I haven't done that in over 10 years, i.e., long before
I started using MyBatis.  Just about everything I've been working on
uses web-based GUIs with server-based database interactions.  MyBatis
comes packaged with 3 DataSource options that all use a single set of
credentials.  You can provide your own DataSource implementation.

Column privileges: from the way you describe this, it sounds like you
have some way of identifying which user logons have update rights on
which columns.  If that is the case, then MyBatis provides minimalist
conditional expressions.  Here is a ready example from some production code:

    where
       user_id = #{aUserId, javaType=String, jdbcType=VARCHAR}
       <if test="dtStartDate != null">
       and trunc(timestamp) <![CDATA[ >= ]]> trunc(#{dtStartDate,
javaType=java.util.Date, jdbcType=DATE})
       </if>
       <if test="aiEventType != null">
       and type_id in
          <foreach collection="aiEventType" item="item" open="("
separator="," close=")">
          #{item}
          </foreach>
       </if>

The code is for a where clause, but you can do the same thing in the set
clause of an update.

I'm not a MyBatis developer, just a happy end user.  It works well for
what it attempts to do.  But it certainly doesn't attempt to do anywhere
near what JPA or Hibernate does.

--
Guy Rouillier

On 28/07/10 04:57, Guy Rouillier wrote:

> So, unless you have very compelling requirements that require
> significant client interaction without returning to the server, I'd
> highly recommend JSF/facelets.  Obviously, if you are not committed to
> Java, then perhaps other options are more appropriate to you environment
> and/or skill set.

From initial reading, that looks like a really good option. Thanks for
the pointer; the 'net is so full of negativity about JSF1 that it's hard
to get a feel for how JSF2 is working out.

So far I'm impressed.

BTW, anyone else who's looking at moving a (presumably Pg-based) project
server-side and who is new to server-side Java should start reading here:

http://www.coreservlets.com/JSF-Tutorial/jsf2/

... as it's an AMAZING resource.

--
Craig Ringer