Search code examples
javamysqlsqloracle-databaserowid

Oracle's ROWID / ROWNUM in MySQL

I'm trying to get the equivalent for this code on MySQL

try(Connection c = DriverManager.getConnection("jdbc:oracle:thin:@myhost:1521:"+ vardbserver, vardbuser, vardbpassword);
      PreparedStatement stmts = c.prepareStatement("SELECT * FROM "+ vardbname +" where ROWNUM > ? and ROWNUM < ? ");
      PreparedStatement max = c.prepareStatement("select max(ROWNUM) as txid from "+ vardbname)
      )

I've looking for the sample but i got a dead end.

Solution 
  • SELECT * FROM tablename where ROWNUM > ? and ROWNUM < ?

    ROWNUM is used to select a subset of rows, i.e. rows between the two values (exclusive). First row of the query is numbered 1, second row is 2, and so on.

    MySQL supports the LIMIT syntax, where you instead specify how many rows you want, and which row to get first:

    SELECT * FROM tablename LIMIT offset, row_count

    or

    SELECT * FROM tablename LIMIT row_count OFFSET offset

    An offset of 0 returns the first row.

    So, you can calculate the two values for MySQL from the two values you had in Oracle. I'll leave that calculation up to you.

    select max(ROWNUM) as txid from tablename

    Well, I've never seen this one before, but if the query returned 10 rows, the max ROWNUM would be 10, so it is essentially the same as:

    select count(*) as txid from tablename