Hrorm Forums

Construction of compound where clauses

#1

I am thinking about the request to easily create where clauses that look like

where X or Y or Z or ...

I made a small addition to the Where class, a static factory method with this signature:

public static Where or(List<Where> subWheres)

This allows users to write code like this:

    @Test
    public void testStaticOrConstruction() {
        helper.useConnection(connection -> {
            Dao<Columns> dao = daoBuilder().buildDao(connection);

            for(long idx=1; idx<=10; idx++){
                Columns columns = new Columns();
                columns.setIntegerThing(idx);
                dao.insert(columns);
            }
        });
        helper.useConnection(connection -> {
            Dao<Columns> dao = daoBuilder().buildDao(connection);
            List<Where> wheres = new ArrayList<>();
            for(long idx=2; idx<=10; idx+=2){
                wheres.add(new Where("integer_column", EQUALS, idx));
            }
            List<Columns> columns = dao.select(Where.or(wheres));
            AssertHelp.containsAllItems(new Long[]{ 2L, 4L, 6L, 8L, 10L }, columns, c -> c.getIntegerThing());
        });
    }

It’s a pretty small addition, and adding a similar and method is easy enough.

Not sure if this scratches a big enough itch, but there’s little downside. Still thinking about it, to see if a better idea appears.

#2

I pushed the code to github. It is here:

#3

Let me try something with this…

#4

By now you know I’m a fan of the Streams API. This looks alot like a Map/Reduce operation, so I tried to model that.

        List<Columns> columns = LongStream.rangeClosed(2, 10)
                .filter(l -> l%2==0)
                // This really can be any Stream/map.
                .mapToObj(idx -> new Where("integer_column", EQUALS, idx))
                .reduce(Where.or())
                .map(dao::select)
                .orElse(Collections.emptyList());

That’s kind of meh. It gets a little better in Java 1.9x:

        // Java 9 has {Int,Long}Stream.iterate to model for() loops
        List<Columns> columnsSame = LongStream.iterate(2L, idx->idx<=10, idx->idx+2) // Java 1.9+
                .mapToObj(idx -> new Where("integer_column", EQUALS, idx))
                .reduce(Where.or())
                .map(dao::select)
                .orElse(Collections.emptyList());

Just spitballing. Having to explicitly create a List when you’re essentially doing a direct map operation and then a reduce operation (consolidating that into a single where) is kinda Java 1.5ish.

What you have is an improvement, for sure. It seems to fall short somehow, but I can’t quite place my finger on exactly how or why.

How I did .reduce(Where.or()) (there’s also Where.and()):

#5

I was able to even further simplify the reducer. I can’t believe an enum can be used like this…

private enum Reducer implements BinaryOperator<Where> {
    AND(Where::and), OR(Where::or);

    final BinaryOperator<Where> binaryOperator;

    Reducer(BinaryOperator<Where> binaryOperator) {
        this.binaryOperator = binaryOperator;
    }

    @Override
    public Where apply(Where where, Where where2) {
        return binaryOperator.apply(where, where2);
    }
}

I could probably take this further by supporting batching, but then youi’d really be selling out to Stream. The problem is, you can only chain ~ 2000 ORs (depending on the RDBMS) before the driver gets fed up with you. Typically you want to do something like:

SELECT * FROM table where 
(common criteria) // Apples to all "batches" of ORs
AND
(field = 1 OR field = 2 OR field = 3....) // Giant IN clause

So given:

Stream<Where> wheres;
Where commonCriteria;

You’d want to do some magic like:

List<Columns> combinedResults = wheres.reduce(Where.or().batch(
        1000, 
        batch -> dao.select(commonCriteria.and(batch)
      ) /* Returns List<Columns> for each batch */ )
      .flatMap(List::stream) // Combine each batch somewhere
      .collect(Collectors.toList());

That might be a nice feature.

#6

Maybe this is developing in a direction of a too-awesome crutch, when really, hrorm just needs to learn to walk.

I tried a crack at implementing IN clauses. It’s just a trial for now, since it does not have NOT in, and you have to duplicate some type information, but it’s a thing. Now this test passes:

    @Test
    public void testInClauseWithInts() {
        helper.useConnection(connection -> {
            Dao<Columns> dao = daoBuilder().buildDao(connection);

            for(long idx=1; idx<=10; idx++){
                Columns columns = new Columns();
                columns.setIntegerThing(idx);
                dao.insert(columns);
            }
        });

        helper.useConnection(connection -> {
            Dao<Columns> dao = daoBuilder().buildDao(connection);

            List<Long> toFind = Arrays.asList(3L,7L,8L);
            List<Columns> columns = dao.select(new Where("integer_column", GenericColumn.LONG, toFind));
            List<Long> found = columns.stream().map(Columns::getIntegerThing).collect(Collectors.toList());
            AssertHelp.sameContents(toFind, found);
        });
    }

Maybe this is a better direction.

Code is at:

#7

IN will have similar limits in number of valid arguments, its functionally no different than chaining OR in most driver implementations. I’ll take a look at the code.

#8

I just merged the code into master.

The limits are whatever the underlying RDBMS supplies, as you say, but I think just directly supporting IN and NOT IN makes much more sense than fancy ways to construct compound OR statements.

#9

That’s it, in a nutshell.

Great on this getting in, I can now upgrade and eliminate alot of code where I manually was doing this OR assembly myself.