CODEWITHSUNDEEP
javasqlsqlbuilder
Dmitry K
Dmitry K

Reputation: 1312

How to build SELECT query with sqlbuilder?

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?

Upvotes: 5

Views: 5468

Answers (4)

Mohy Eldeen
Mohy Eldeen

Reputation: 1330

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;

}

}

Upvotes: 0

Sergei Podlipaev
Sergei Podlipaev

Reputation: 1421

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();

Upvotes: 0

Dmitry K
Dmitry K

Reputation: 1312

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')

Upvotes: 4

Shaggy
Shaggy

Reputation: 596

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"));

Upvotes: 1

Related Questions