Sql2o keep returning a same set of data although query is different

Question

I am new to using SQL2O with MySQL, but I am having a weird problem, where different queries return same results. Is SQL2O returning me cached results?

My code looks like this:

String sql = "SELECT * " +
             "FROM report_A" +
             "ORDER BY :order :sequence "+
             "LIMIT :from, :limit";
    int limit = 5;
    int startIndex = (page-1)*limit;
    String sequence = "DESC";
    try(Connection con = sql2o.open()) {

        if(order.contains("-")){
            order = order.replace("-", "");
            sequence= " ASC";
        }
        Query query= con.createQuery(sql)
                .addParameter("from", startIndex)
                .addParameter("limit", limit)
                .addParameter("order", order) 
                .addParameter("sequence", sequence); 

        List<ReportA> result = query.executeAndFetch(ReportA.class);
        con.close();

The 4 parameters always change, but the output remains the same. I have verified the queries in mysql workbench, the data is different, but SQL2O returns me the same set of data. Am I missing something?

If you use try with resource you don't need to explicitly close the connection. It will be done automatically. — Philippe, Aug 01 '17 at 12:22

score 0 · Answer 1 · answered Jun 05 '18 at 13:29

Your query is invalid. It wont compile and throw an Sql2oException on execution.

The problem is, basically, that you can use parameters only for values, not for table names, column names or other keywords like "ASC". Changing those would change the structure of the query.

It's possible to construct queries with variable structure by good old string concatenation, i.e.

String sql = "SELECT * " +
         "FROM report_A" +
         "ORDER BY " + order " " + SEQUENCE +
         "LIMIT :from, :limit";

and then

query(sql)
    .addParameter("from", from)
    .addParameter("limit", limit)
    .executeAndFetch(...)

Sql2o keep returning a same set of data although query is different

1 Answers1