JDBC mysql does not support placeholder of LIMIT in PreparedStatement?

Question

I used mysql-connector-java-5.1.38 to operate mysql-community-5.7.10.0 on Windows 10 64-bit.

I try to bind value in limit for pagination

"SELECT * FROM employee LIMIT ?, ?"

However the result shows:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.Util.getInstance(Util.java:387)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2505)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1370)
    at SqlTest.main(SqlTest.java:65)

However, I tried sql in navicat directly but could get the correct answer:

INSERT INTO employee VALUES (1, 'Zara');
INSERT INTO employee VALUES (2, 'Zara');
INSERT INTO employee VALUES (3, 'Zara');
INSERT INTO employee VALUES (4, 'Zara');
SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM employee LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

Here is my entire code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SqlTest {
  // JDBC driver name and database URL
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost:3306/employee?useServerPrepStmts=false";
  // Database credentials
  static final String USER = "root";
  static final String PASS = "whaty123";

  static final int PAGESIZE = 10;

  public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    PreparedStatement pStmt = null;

    // STEP 2: Register JDBC driver
    try {
      Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    }

    // STEP 3: Open a connection
    System.out.println("Connecting to database...");
    try {
      conn = DriverManager.getConnection(DB_URL, USER, PASS);
    } catch (SQLException e) {
      e.printStackTrace();
    }

    String insertPreparedSql = "INSERT INTO employee " + "VALUES (?, 'Zara')";

    try {
      pStmt = conn.prepareStatement(insertPreparedSql);
    } catch (SQLException e) {
      e.printStackTrace();
    }

    for (int i = 0; i < 100; i++) {
      try {
        pStmt.setInt(1, i);
        pStmt.execute();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    String selectLimitSql = "SELECT * FROM employee LIMIT ?, ?";
    // select with limit
    try {
      pStmt = conn.prepareStatement(selectLimitSql);
      pStmt.setFetchSize(PAGESIZE);
      pStmt.setMaxRows(PAGESIZE);
      pStmt.setFetchDirection(ResultSet.FETCH_FORWARD);
      int pageNo = 0;
      pStmt.setInt(1, pageNo * PAGESIZE);
      pStmt.setInt(2, PAGESIZE);
      ResultSet rs = pStmt.executeQuery(selectLimitSql);
      while (!rs.wasNull()) {
        while(rs.next()) {
          System.out.println("id: " + String.valueOf(rs.getInt(1)) + " name: " + rs.getString(2));
        }
        pageNo = pageNo + 1;
        pStmt.setInt(1, pageNo * PAGESIZE);
        pStmt.setInt(2, PAGESIZE);
        pStmt.executeQuery(selectLimitSql);
      }
      rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

}

Answer 1

Your problem is not about the syntax or MySQL support for LIMIT since it's supported. The problem is about the way you are executing the PreparedStatement.

When using PreparedStatement you may not use the executeQuery(String sql), because you've prepared the SQL string formerly for the execution, and no need to pass it again in the executeQuery() method. So do this

ResultSet rs = pStmt.executeQuery();

instead of

ResultSet rs = pStmt.executeQuery(selectLimitSql);

With passing again the selectLimitSql (like above line), you are ignoring the following lines:

pStmt.setInt(1, pageNo * PAGESIZE);
pStmt.setInt(2, PAGESIZE);

and it is like executing your primitive pure sql which contains '?, ?' place holders and you get that exception.

Answer 2

You do not need to pass the query string. Do this

 ResultSet rs = pStmt.executeQuery();

instead of

ResultSet rs = pStmt.executeQuery(selectLimitSql);

Also, remove the following lines as pagination is taken care of limit in query itself.

pStmt.setFetchSize(PAGESIZE);
pStmt.setMaxRows(PAGESIZE);
pStmt.setFetchDirection(ResultSet.FETCH_FORWARD);

Following code works:

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;

    public class SqlTest {
      // JDBC driver name and database URL
      static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
      static final String DB_URL = "jdbc:mysql://localhost:3306/company?useServerPrepStmts=false";
      // Database credentials
      static final String USER = "root";
      static final String PASS = "rohan";

      static final int PAGESIZE = 10;

      public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        PreparedStatement pStmt = null;

        // STEP 2: Register JDBC driver
        try {
          Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
          e.printStackTrace();
        }

        // STEP 3: Open a connection
        System.out.println("Connecting to database...");
        try {
          conn = DriverManager.getConnection(DB_URL, USER, PASS);
        } catch (SQLException e) {
          e.printStackTrace();
        }

       String insertPreparedSql = "INSERT INTO employee " + "VALUES (?, 'Zara', 'Zara','Zara')";

        try {
          pStmt = conn.prepareStatement(insertPreparedSql);
        } catch (SQLException e) {
          e.printStackTrace();
        }

        for (int i = 0; i < 100; i++) {
          try {
            pStmt.setInt(1, i*10);
            pStmt.execute();
          } catch (SQLException e) {
            e.printStackTrace();
          }
        }

        String selectLimitSql = "SELECT * FROM employee limit ?, ?";
        // select with limit
        try {
          pStmt = conn.prepareStatement(selectLimitSql);
          int pageNo = 0;
          pStmt.setInt(1, pageNo * PAGESIZE);
          pStmt.setInt(2, PAGESIZE);
          ResultSet rs = pStmt.executeQuery();
          while (!rs.wasNull()) {
            while(rs.next()) {
              System.out.println("id: " + String.valueOf(rs.getInt(1)) + " name: " + rs.getString(2));
            }

            pageNo = pageNo + 1;
            pStmt.setInt(1, pageNo * PAGESIZE);
            pStmt.setInt(2, PAGESIZE);
            rs = pStmt.executeQuery();
          }
          rs.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }

    }