Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #16168

Re: JDBC, PreparedStatement and named parameters

Show all headers | View raw

On 7/20/2012 3:35 PM, Andreas Leitgeb wrote:
>
> Here's a slight simplification of my problem at hand:
> (I'm *NOT* asking for a solution nor even for help to this


Yeah but it's an interesting problem. ;)


>   problem - Unlike the Jdbc-question which I originally posted,
>   this one's not even related to Java.)
>
> I have a somewhat convoluted sql-problem at hand.  Two tables are joined,
> just not by a single join-column, but by overlapping intervals.
>
> Table1:   from1, to1
> Table2:   from2, to2
> Params:   from3, to3


I think I did something similar once, although there was no join 
involved.  Intersection isn't really hard, but if you have a lot of 
intervals to search it might be useful to look hard for an efficient 
solution.

Not posting "solutions" because you asked that none be posted....


>
> I have to identify the intersections of the table's intervals
> within the bounds of a third interval given as parameter.
> I figured it would take me quite a couple of tries to find which
> variant of conjunctive inequalities would actually work best,


I'm going to have to look those words up.


> and the params from3 and to3 would not only change their relative
> position often between consecutive trials, but will likely turn
> out to be used multiple times, each, in the query.
>
> With named parameters I could concentrate on the ordering of all
> the single "fromX < toY"-terms.
>
> As it is in jdbc, I'm instead facing a lot of "fromX < ?" and
> "toY < ?" terms as well as a hard time trying to memorize which
> of all the "?" was really meant to mean which of the params.


In a pinch, string substitution might work.

long start = ...
long end = ...
String sql = "Select * from SomeTable where from >= ::start:: & to <= 
::end::";
sql = sql.replaceAll( "::start::", Long.toString( start ) );
sql = sql.replaceAll( "::end::", Long.toString( end ) );

Cheesy, but it's "clear" what is being done.


Hmm, final thoughts... you have two tables joined on an *interval*? 
That doesn't seem right.  Time, or length, is continuous.  Normally you 
wouldn't expect the *exact* same values to appear in two places.  Are 
you sure this spec is correct?

Back to comp.lang.java.programmer | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

JDBC, PreparedStatement and named parameters Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2012-07-20 09:39 +0000
  Re: JDBC, PreparedStatement and named parameters markspace <-@.> - 2012-07-20 06:31 -0700
    Re: JDBC, PreparedStatement and named parameters Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2012-07-20 14:52 +0000
      Re: JDBC, PreparedStatement and named parameters markspace <-@.> - 2012-07-20 08:15 -0700
        Re: JDBC, PreparedStatement and named parameters Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2012-07-20 16:26 +0000
          Re: JDBC, PreparedStatement and named parameters markspace <-@.> - 2012-07-20 09:37 -0700
            Re: JDBC, PreparedStatement and named parameters Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2012-07-20 21:04 +0000
              Re: JDBC, PreparedStatement and named parameters markspace <-@.> - 2012-07-20 14:12 -0700
                Re: JDBC, PreparedStatement and named parameters Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2012-07-20 22:35 +0000
                Re: JDBC, PreparedStatement and named parameters markspace <-@.> - 2012-07-20 16:08 -0700
                Re: JDBC, PreparedStatement and named parameters Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2012-07-21 14:32 +0000
                Re: JDBC, PreparedStatement and named parameters Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2012-07-24 16:18 +0000
            Re: JDBC, PreparedStatement and named parameters Arne Vajhøj <arne@vajhoej.dk> - 2012-07-20 22:13 -0400
  Re: JDBC, PreparedStatement and named parameters Daniel Pitts <newsgroup.nospam@virtualinfinity.net> - 2012-07-20 10:22 -0700
    Re: JDBC, PreparedStatement and named parameters Lew <lewbloch@gmail.com> - 2012-07-20 11:00 -0700
  Re: JDBC, PreparedStatement and named parameters Arne Vajhøj <arne@vajhoej.dk> - 2012-07-20 22:11 -0400
    Re: JDBC, PreparedStatement and named parameters Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2012-07-21 14:47 +0000
      Re: JDBC, PreparedStatement and named parameters jebblue <n@n.nnn> - 2012-07-23 22:14 -0500
        Re: JDBC, PreparedStatement and named parameters jebblue <n@n.nnn> - 2012-07-23 22:19 -0500
  Re: JDBC, PreparedStatement and named parameters Roedy Green <see_website@mindprod.com.invalid> - 2012-07-21 15:00 -0700
    Re: JDBC, PreparedStatement and named parameters Arne Vajhøj <arne@vajhoej.dk> - 2012-07-21 21:53 -0400

csiph-web