How to build SELECT query with sqlbuilder?

Question

I am using Java and SQLBuilder from http://openhms.sourceforge.net/sqlbuilder/ and am trying to build SQL SELECT query dynamicly:

SelectQuery sql = new SelectQuery();
sql.addAllColumns().addCustomFromTable("table1");
sql.addCondition(BinaryCondition.like("column1", "A"));

However, it creates string like this:

SELECT * FROM table1 WHERE ('column1' LIKE 'A')

Because of wrong quotes ('column1') it doesn't work properly. I suppose it expects some Column object in .like() method. Is there any way to create query with proper quotes?

You should use column object type instead of "column1" String — Sergei Podlipaev, Sep 26 '16 at 10:16

score 4 · Accepted Answer · answered Sep 26 '16 at 10:11

I've found a solution. I had to create new class Column that extends CustomSql and pass my column name as parameter:

public class Column extends CustomSql {
   public Column(String str) {
      super(str);
   }
}

And then:

SelectQuery sql = new SelectQuery();
sql.addAllColumns().addCustomFromTable("table1");
sql.addCondition(BinaryCondition.like(new Column("column1"), "A"));

Or without creating own class:

SelectQuery sql = new SelectQuery();
sql.addAllColumns().addCustomFromTable("table1");
sql.addCondition(BinaryCondition.like(new CustomSql("column1"), "A"));

It creates following SQL query, which works fine:

SELECT * FROM table1 WHERE (column1 LIKE 'A')

Shaggy · Answer 2 · 2016-09-25T16:53:02.947

1

BinaryCondition.like() takes Object which is a Column Object and then it is converted to SqlObject using Converter.toColumnSqlObject(Object) internally . There is a method named findColumn(String columnName) and findSchema(String tableName) in Class DbTable and Class DbSchemarespectively where you can pass a simple String Object. Try this it would solve your problem:

 DbTable table1= schema.findSchema("table1");
 DbColumn column1 = table1.findColumn("column1");

 SelectQuery sql = new SelectQuery();
 sql.addAllColumns().addCustomFromTable(table1);
 sql.addCondition(BinaryCondition.like(column1, "A"));

edited Sep 25 '16 at 16:53

answered Sep 25 '16 at 16:07

Shaggy

596
1
6
15

I didn't use `DbSchema` for creating my Database. I need to build just one query. Is it possible? – Dmitry K Sep 25 '16 at 16:37

score 0 · Answer 3 · answered Sep 26 '16 at 10:19

Please, check the working example and refactor your own query

String query3 =
      new SelectQuery()
      .addCustomColumns(
          custNameCol,
          FunctionCall.sum().addColumnParams(orderTotalCol))
      .addJoins(SelectQuery.JoinType.INNER, custOrderJoin)
      .addCondition(BinaryCondition.like(custNameCol, "%bob%"))
      .addCondition(BinaryCondition.greaterThan(
                        orderDateCol,
                        JdbcEscape.date(new Date(108, 0, 1)), true))
      .addGroupings(custNameCol)
      .addHaving(BinaryCondition.greaterThan(
                     FunctionCall.sum().addColumnParams(orderTotalCol),
                     100, false))
      .validate().toString();

This example assumes that I've created Database with schema objects, but I need to build just one query. See my answer — Dmitry K, Sep 26 '16 at 10:51

score 0 · Answer 4 · answered Sep 29 '16 at 16:57

Look at this library JDSQL (It requires Java 8):

JQuery jquery = new JQuery();
    Collection<Map<String, Object>> result = jquery.select("tbl1::column1", "tbl2::column2") //Select column list
                                                .from("Table1" , "TB1") // Specifiy main table entry, and you can add alias
                                                .join("Table2::tb2") // Provide your join table, and another way to provide alias name
                                                .on("tbl1.key1", "tbl2.key1") // your on statement will be based on the passed 2 values equaliy 
                                                .join("Table3", "tbl3", true) // Join another table with a flag to enable/disable the join (Lazy Joining)
                                                .on("tbl2.key2", "tbl3.key1", (st-> {st.and("tbl3.condition = true"); return st;}))
                                                .where("tbl1.condition", true, "!=") // Start your where statment and it also support enable/disable flags 
                                                .and("tbl2.condition = true", (st-> {st.or("tbl.cond2", 9000, "="); return st;})) // And statment that is grouping an or inside parentheses to group conditions  
                                                .and("tbl3.cond3=5", false) // And statment with a flag to enable/disable the condition 
                                                .get((String sql, Map<String, Object> parameters)-> getData(sql, parameters)); // Passing the hybrid getter. 
                                                                //You can also assign the getter at the jqueryobject itself by calling setGetter.
}

private static Collection<Map<String, Object>> getData(String sql, Map<String, Object> parameters){



    return null;

}

}

Where can this JDSQL library be found and how is it different from e.g. https://www.jooq.org? — Lukas Eder, Nov 22 '18 at 14:16
Its an open source lib I started few years ago, but have not have time to publish to maven or ivy. Its free joog is? I am not sure. DJSQL also not complete yet, so it still need some more work. Happy to take pull requests or help if you like to help publish it to mvn https://github.com/mohyeid/DJSQL — Mohy Eldeen, Nov 23 '18 at 19:16
Very cool, thanks for the link. jOOQ is free for open source databases. I probably won't send pull requests, but publishing to Maven central is not that complicated. There's a nice guide by sonatype: https://central.sonatype.org/pages/ossrh-guide.html — Lukas Eder, Nov 24 '18 at 14:15

How to build SELECT query with sqlbuilder?

4 Answers4