How to execute MySQL sql 'begin... end;' using JDBC

Question

I'm using Mysql 5.7 and JDBC, I need to run below query and get result data using JDBC:

set @rownum := 0;
select *
from (select *, @rownum := @rownum + 1 as rownum from test) t
where t.rownum >= 1
  and t.rownum <= 10;

table structure and init sql:

create table test(id int, name varchar(16));
insert into test(id, name) VALUES (1,'rollsbean');

I tried executeQuery() and conn.prepareCall() and run failed.

My test code:

public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://172.16.100.115:3306/valid?user=drpeco&password=DT@Stack#123&serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false");
            String plsql = "set @rownum := 0;\n" +
                    "select *\n" +
                    "from (select *, @rownum := @rownum + 1 as rownum from test) t\n" +
                    "where t.rownum >= 1\n" +
                    "  and t.rownum <= 10;";
            CallableStatement cstmt = conn.prepareCall(plsql1);
            cstmt.execute();
            Object object = cstmt.getObject(1);
//            testExecute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

stmt = conn.createStatement();
            String plsql = "set @rownum := 0;\n" +
                    "select *\n" +
                    "from (select *, @rownum := @rownum + 1 as rownum from test) t\n" +
                    "where t.rownum >= 1\n" +
                    "  and t.rownum <= 10;";
            rs = stmt.executeQuery(plsql);
            while (rs.next()) {
                Object object = rs.getObject(1);
            }

What i want: I want to do paging query using MySQL custom variable, mysql 5.7 don't support row_number() over ().

Question: how to execute these query and get resulte data using JDBC.

Additional: the target table i searched don't have specific columns and index, if i use limit to do paging query. the efficiency will very low in deep pages, so i want to try row_number, if anyone have better solution to query, please share with me, i will very happy for your answer.

My Requirement: as i said in the Additional, my app will run sql jobs and generate many template table with data, the data is depend on the sql, and we can download all the data from one specific template table without any conditions, in this step, i will do paging query to read
data.

I need to run below query This query makes no sense. By fact it selects some 10 rows from the table. — Akina
@Akina, See my requirement, i just want to query all the data without any condition, so just need to do paging query, what i did is use @rownum instead of limit 0, 10, does that make sense? — Rollsbean
None enumeration/limitation/pagination makes sense until definite sorting by the expresssion which provides rows uniqueness. — Akina
Yes, but the template table doesn't have unique key and if there are hundreds of thousands of rows, i can't query all the data in one time. — Rollsbean
If so then specify some meaningful ORDER BY during rows enumeration. — Akina

supernerd supernerd · Accepted Answer · 2020-12-08T07:29:01

Use LIMIT with two parameters. For example, to return results 11-60 (where result 1 is the first row), use:

SELECT * FROM foo LIMIT 10, 50

For more details look here

How to execute MySQL sql 'begin... end;' using JDBC

2 Answers