如何使用sqlbuilder构建SELECT查询

Question

我正在使用Java和SQLBuilder从http://openhms.sourceforge.net/sqlbuilder/和我试图建立SQL SELECT查询动态:

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

但是，它创建了这样的字符串:

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

由于错误的引号('column1')，它不能正常工作。我想它期望一些列对象在.like()方法。是否有任何方法来创建查询与适当的引号?

Answer 1

我找到了解决办法。我必须创建扩展CustomSql的新类Column，并将我的列名作为参数传递:

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

然后:

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

或者不创建自己的类:

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

它创建了以下SQL查询，运行良好:

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

Answer 2

BinaryCondition.like()接受的对象是Column Object，然后在内部使用Converter.toColumnSqlObject(Object)将其转换为SqlObject。在Class DbTable和Class DbSchema中分别有一个名为findColumn(String columnName)和findSchema(String tableName)的方法，您可以在其中传递一个简单的字符串对象。试试这个，它会解决你的问题:

 DbTable table1= schema.findSchema("table1");
 DbColumn column1 = table1.findColumn("column1");
 SelectQuery sql = new SelectQuery();
 sql.addAllColumns().addCustomFromTable(table1);
 sql.addCondition(BinaryCondition.like(column1, "A"));

Answer 3

请查看工作示例并重构您自己的查询

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

Answer 4

看看这个库JDSQL(它需要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;
}

}