How to use placeholder in spanner

Question

My Query:

Select * from (Select id,name, salary from Emp ORDER BY %s %s) AS AL 
BETWEEN OFFSET :OFFSET AND LIMIT: LIMIT

%s ,%s  Represents the created,ASC

It is not working in Spanner

How to implement this query in spanner from java side?

Answer 1

It seems your query has a syntax error. We are missing a WHERE <column name> before the BETWEEN.

With the fixed query, you could do something like this in the Java client:

try (ResultSet rs = databaseClient
       .singleUse()
       .executeQuery(Statement
           .newBuilder(String.format("SELECT *"
               + " FROM ("
               + "   SELECT id, name, salary FROM Emp ORDER BY %s %s"
               + " ) AS e \n"
               + " WHERE e.id BETWEEN @offset AND @limit",
               "id", "ASC"
            ))
            .bind("offset")
            .to(1L)
            .bind("limit")
            .to(10L)
            .build())) {
      while (rs.next()) {
        System.out.println(rs.getLong("id") + ", " + rs.getString("name") + ", " + rs.getBigDecimal("salary"));
      }
    }