Conditional SQL in Java

[Ken]

I just looked at hibernate... but have never used it.

Can it or any other main stream java tools query databases with an
optional WHERE clause?

What I mean is I have a number of cases where people fill in forms
with quite a few criteria and then I need to build a query. I've been
concatenating strings recently to make this work (although in my
defense I've been programing in IBM RPG, yes that language that
started on punch cards...) but I want to know how to do this
effectively in Java.

I know it is probably possible to build all the needed statements in
SQL conditional logic but I think it will make the statements far more
complicated.

I'd imagine there is a nice API for which you simply create a
statement object and then specify it's type (such as SELECT) and then
add constraints via the API and don't need to mess around doing
painful string concatenations.

I think this can be done with SQL PL? But I don't have access to SQL
PL in my environment but am hoping for some tool that can add this
functionality.

Something like:
Stmt stmt = new stmt();
Select select = new select(stmt);
select.addField("name");
select.from("people");
if (somecondition != null)
select.addWhere("x > " + somecondition);
endif;

Or perhaps there is something that even makes the above look like work
=)

 

[Arne Vajhøj]

I just looked at hibernate... but have never used it.

Can it or any other main stream java tools query databases with an
optional WHERE clause?

What I mean is I have a number of cases where people fill in forms
with quite a few criteria and then I need to build a query. I've been
concatenating strings recently to make this work (although in my
defense I've been programing in IBM RPG, yes that language that
started on punch cards...) but I want to know how to do this
effectively in Java.

I know it is probably possible to build all the needed statements in
SQL conditional logic but I think it will make the statements far more
complicated.

I'd imagine there is a nice API for which you simply create a
statement object and then specify it's type (such as SELECT) and then
add constraints via the API and don't need to mess around doing
painful string concatenations.

I think this can be done with SQL PL? But I don't have access to SQL
PL in my environment but am hoping for some tool that can add this
functionality.

Something like:
Stmt stmt = new stmt();
Select select = new select(stmt);
select.addField("name");
select.from("people");
if (somecondition != null)
select.addWhere("x> " + somecondition);
endif;

Or perhaps there is something that even makes the above look like work
=)

You can build criterias in Hibernate.

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html

Arne

 

[Ken]

You can build criterias in Hibernate.

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycrite...

Arne

Yes this is exactly what I was thinking! I think...

 

[Arne Vajhøj]

Yes this is exactly what I was thinking! I think...

Thinking is good !

Arne

 

[Lew]

Thinking is good !

Use the JPA packages, which Hibernate supports.

 

[Arne Vajhøj]

Use the JPA packages, which Hibernate supports.

JPA 2.0 has this:

http://openjpa.apache.org/builds/latest/docs/manual/jpa_overview_criteria.html

Arne

 

[Arved Sandstrom]

JPA 2.0 has this:

http://openjpa.apache.org/builds/latest/docs/manual/jpa_overview_criteria.html

Arne

The JPA 2.0 Criteria API is absolutely the answer if you've got a JPA
2.0 implementation like EclipseLink 2.x. E.g.
http://wiki.eclipse.org/EclipseLink/Examples/JPA/2.0/Criteria.

Right now if you're using JPA 1.0 then it's down to Java conditional
code and/or using the native APIs of Hibernate/Toplink/EclipseLink.

I can't comment on Hibernate, but EclipseLink (and I would guess
ToplinkEssentials) allow relatively painless transition between the
native and JPA levels. For example, you can start with a ReadAllQuery,
and after adding the reference class then execute your "criteria" logic
(often handy as part of a JSF "criteria" class that encapsulates the
"backing bean" portion of a managed bean), and then convert to a JPA
Query with

javax.persistence.Query jpaQuery =
((JpaEntityManager)em.getDelegate()).createQuery(readAllQuery);

This can then be used to add pagination, if necessary, with straight
JPA, and execute the actual query also in JPA.

AHS