Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Conditional SQL in Java

Reply
Thread Tools

Conditional SQL in Java

 
 
Ken
Guest
Posts: n/a
 
      03-31-2010
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
=)
 
Reply With Quote
 
 
 
 
Arne Vajhj
Guest
Posts: n/a
 
      03-31-2010
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

 
Reply With Quote
 
 
 
 
Ken
Guest
Posts: n/a
 
      03-31-2010
On Mar 31, 5:30*pm, Arne Vajhj <(E-Mail Removed)> 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...
 
Reply With Quote
 
Arne Vajhj
Guest
Posts: n/a
 
      04-01-2010
On 31-03-2010 19:57, Ken wrote:
> On Mar 31, 5:30 pm, Arne Vajhj<(E-Mail Removed)> 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

 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      04-01-2010
Arne Vajhøj wrote:
> On 31-03-2010 19:57, Ken wrote:
>> On Mar 31, 5:30 pm, Arne Vajhøj<(E-Mail Removed)> 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
 
Reply With Quote
 
Arne Vajhøj
Guest
Posts: n/a
 
      04-01-2010
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<(E-Mail Removed)> 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

 
Reply With Quote
 
Arved Sandstrom
Guest
Posts: n/a
 
      04-01-2010
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<(E-Mail Removed)> 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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
compile directive for conditional compile for Java 1.4 versus Java 5 timjowers Java 7 02-02-2011 12:08 AM
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
? ELSE Conditional Comment / Using Conditional Comments Inside Other Tags To Comment Out Attributes Alec S. HTML 10 04-16-2005 02:21 AM
ASP / SQL Query - Conditional SELECT Statement Guy Hocking ASP General 7 01-21-2004 08:41 PM
conditional sql statements Bill ASP General 1 08-29-2003 07:21 PM



Advertisments