Conditional SQL in Java

Discussion in 'Java' started by Ken, Apr 1, 2010.

  1. Ken

    Ken Guest

    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
    =)
     
    Ken, Apr 1, 2010
    #1
    1. Advertising

  2. Ken

    Arne Vajhøj Guest

    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/3.3/reference/en/html/querycriteria.html

    Arne
     
    Arne Vajhøj, Apr 1, 2010
    #2
    1. Advertising

  3. Ken

    Ken Guest

    On Mar 31, 5:30 pm, Arne Vajhøj <> 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/3.3/reference/en/html/querycrite...
    >
    > Arne


    Yes this is exactly what I was thinking! I think...
     
    Ken, Apr 1, 2010
    #3
  4. Ken

    Arne Vajhøj Guest

    On 31-03-2010 19:57, Ken wrote:
    > On Mar 31, 5:30 pm, Arne Vajhøj<> 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/3.3/reference/en/html/querycrite...

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


    Thinking is good !

    Arne
     
    Arne Vajhøj, Apr 1, 2010
    #4
  5. Ken

    Lew Guest

    Arne Vajhøj wrote:
    > On 31-03-2010 19:57, Ken wrote:
    >> On Mar 31, 5:30 pm, Arne Vajhøj<> 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/3.3/reference/en/html/querycrite...

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

    >
    > Thinking is good !


    Use the JPA packages, which Hibernate supports.

    --
    Lew
     
    Lew, Apr 1, 2010
    #5
  6. 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<> 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/3.3/reference/en/html/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/latest/docs/manual/jpa_overview_criteria.html

    Arne
     
    Arne Vajhøj, Apr 1, 2010
    #6
  7. 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<> 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/3.3/reference/en/html/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/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
     
    Arved Sandstrom, Apr 1, 2010
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alec S.
    Replies:
    10
    Views:
    10,231
    Alec S.
    Apr 16, 2005
  2. timjowers
    Replies:
    7
    Views:
    4,375
    heyjude
    Feb 2, 2011
  3. ecoolone
    Replies:
    0
    Views:
    783
    ecoolone
    Jan 3, 2008
  4. Bill

    conditional sql statements

    Bill, Aug 29, 2003, in forum: ASP General
    Replies:
    1
    Views:
    111
    Ray at
    Aug 29, 2003
  5. Guy Hocking

    ASP / SQL Query - Conditional SELECT Statement

    Guy Hocking, Jan 21, 2004, in forum: ASP General
    Replies:
    7
    Views:
    207
    Bob Barrows
    Jan 21, 2004
Loading...

Share This Page