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, Dec 08 '20 at 07:32
@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, Dec 08 '20 at 07:37
None enumeration/limitation/pagination makes sense until definite sorting by the expresssion which provides rows uniqueness. — Akina, Dec 08 '20 at 07:48
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, Dec 08 '20 at 07:59
If so then specify some meaningful ORDER BY during rows enumeration. — Akina, Dec 08 '20 at 08:00
Given you have multiple statements, which is not supported by JDBC, you need to enable the non-standard option allowMultiQueries=true in the connection string. — Mark Rotteveel, Dec 08 '20 at 10:56

score 0 · Answer 1 · answered Dec 08 '20 at 07:29

0

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

answered Dec 08 '20 at 07:29

supernerd

379
4
13

`limit` has low prefomance in deep page, just like `limit 10000, 50`, it will query first 10050 records and then return the last 50 records. – Rollsbean Dec 08 '20 at 07:33
@KDFinal https://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/ would this help you out? – supernerd Dec 08 '20 at 07:40
unfortunately, i'm using MySQL 5.7 not 8+, `row_number() over()` require MySQL 8+, anyway, thanks for your answer. – Rollsbean Dec 08 '20 at 07:44

score 0 · Answer 2 · answered Dec 08 '20 at 13:59

0

Why dont you use OFFSET. I believe this would help you.

SELECT id, name FROM any_table LIMIT 15000 OFFSET 15000

answered Dec 08 '20 at 13:59

Irfan Nasim

1,952
2
19
29

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

2 Answers2