«

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
=)

On 31-03-2010 19:27, Ken wrote:
> 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...ycriteria.html

Arne

On Mar 31, 5:30*pm, Arne Vajhøj <a...@vajhoej.dk> wrote:
> On 31-03-2010 19:27, Ken wrote:
>
>
>
> > 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...tml/querycrite...
>
> Arne

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

On 31-03-2010 19:57, Ken wrote:
> On Mar 31, 5:30 pm, Arne Vajhøj<a...@vajhoej.dk> wrote:
>> On 31-03-2010 19:27, Ken wrote:
>>> 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?
....
>> You can build criterias in Hibernate.
>>
>> http://docs.jboss.org/hibernate/core...tml/querycrite...
>
> Yes this is exactly what I was thinking! I think...

Thinking is good !

Arne

Arne Vajhøj wrote:
> On 31-03-2010 19:57, Ken wrote:
>> On Mar 31, 5:30 pm, Arne Vajhøj<a...@vajhoej.dk> wrote:
>>> On 31-03-2010 19:27, Ken wrote:
>>>> 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?
> ...
>>> You can build criterias in Hibernate.
>>>
>>> http://docs.jboss.org/hibernate/core...tml/querycrite...
>>
>> Yes this is exactly what I was thinking! I think...
>
> Thinking is good !

Use the JPA packages, which Hibernate supports.

--
Lew

On 31-03-2010 21:07, Lew wrote:
> Arne Vajhøj wrote:
>> On 31-03-2010 19:57, Ken wrote:
>>> On Mar 31, 5:30 pm, Arne Vajhøj<a...@vajhoej.dk> wrote:
>>>> On 31-03-2010 19:27, Ken wrote:
>>>>> 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?
>> ...
>>>> You can build criterias in Hibernate.
>>>>
>>>> http://docs.jboss.org/hibernate/core...tml/querycrite...
>>>>
>>>
>>> Yes this is exactly what I was thinking! I think...
>>
>> Thinking is good !
>
> Use the JPA packages, which Hibernate supports.

JPA 2.0 has this:

http://openjpa.apache.org/builds/lat..._criteria.html

Arne

Arne Vajhøj wrote:
> On 31-03-2010 21:07, Lew wrote:
>> Arne Vajhøj wrote:
>>> On 31-03-2010 19:57, Ken wrote:
>>>> On Mar 31, 5:30 pm, Arne Vajhøj<a...@vajhoej.dk> wrote:
>>>>> On 31-03-2010 19:27, Ken wrote:
>>>>>> 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?
>>> ...
>>>>> You can build criterias in Hibernate.
>>>>>
>>>>> http://docs.jboss.org/hibernate/core...tml/querycrite...
>>>>>
>>>>>
>>>>
>>>> Yes this is exactly what I was thinking! I think...
>>>
>>> Thinking is good !
>>
>> Use the JPA packages, which Hibernate supports.
>
> JPA 2.0 has this:
>
> http://openjpa.apache.org/builds/lat..._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/...A/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(r eadAllQuery);

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

AHS