0

I have a SQL query and I want to know how many rows will that SQL query return. Now the problem is that I want to know the number of results beforehand which means before running the SQL query.

I would have done this easily by ResultSet.getRow() to get the total number of rows from resultset. But as per the requirement, I can get the resultset only after knowing the number of rows to be returned by that query.

I tried the below Java Regex to solve the issue:

String orgQuery = "select * from emp where id<1210 and salary>55000;" 
Pattern p= Pattern.compile("(?:)from\\s+(.*)*" , Pattern.CASE_INSENSITIVE);
 Matcher m= p.matcher(orgQuery);
    if (m.find()) {
                   countQuery = "SELECT COUNT(*) as total "+ m.group(1);
                   System.out.println(countQuery);
                  }

This work perfectly file and I get the "countQuery" as:

SELECT COUNT(*) as total from emp where id<1210 and salary>55000

By this I can easily know the number of rows to be returned beforehand but the problem occurs when my query become more complex like these two:-- even more complex in case of nested queries i.e. #query2.

#query1: select * from emp where id<1210 and salary>55000 order by dept, salary desc;

#query2: select name from emp where id IN (select id from emp where id < 1210 group by salary , id  order by id  ASC limit 10) order by id DESC  limit 10

I think the main issue is with "Order By" clause. I can remove the "Order By" clause too by below regex:

Pattern.compile("(?:)from\\s+(.*)*" , Pattern.CASE_INSENSITIVE);

But it becomes more complex in case of Nested queries.

Can any Java Regex expert help????? I am using postgres as DB.

Jahar tyagi
  • 91
  • 13
  • I think you will be better off using an sql parser. try googling for one or see this question http://stackoverflow.com/questions/660609/sql-parser-library-for-java – Sharon Ben Asher Jun 09 '16 at 12:06
  • I dont know if it is gonna help me. In my case the "Select coun(*)" query is being generated at runtime on the basis of original query. – Jahar tyagi Jun 09 '16 at 12:20

1 Answers1

2

Wrap your existing query like so:

select count(*) from (<existing query>)

With your given example:

String orgQuery = "select * from emp where id<1210 and salary>55000";
String countQuery = "select count (*) from (" + orgQuery + ')';

I know this works with Oracle. I have not used postgres, so I am not certain if there would be anything preventing this approach from working there.

I will caution on this idea of getting a count first, however, that it might be possible for the data to change between your execution of the count and the actual query.

Brett Okken
  • 6,210
  • 1
  • 19
  • 25
  • Thanks Brett. But do you think it will have additional overhead if I have many records in original table.Actually after getting the count I am limiting the original query by rowcount/10. – Jahar tyagi Jun 09 '16 at 12:31
  • @Jahar tagi: the performance impact depends on where your original statement is spending most of its time. Worst case (and imho the most likely) the `count(*)` will take about the same time as the original select. – piet.t Jun 09 '16 at 12:55
  • @Piet. Thanks for your response Piet. So if I have these 2 queries , will there not be much performance difference? 'select count(*) from emp where id<9000 and salary>50000;' 'select count(*) from (select * from emp where id<9000 and salary>50000 order by dept, salary desc) as d;' I tried running on db server there was a difference of 2 or 3 ms only. – Jahar tyagi Jun 09 '16 at 13:34
  • 1
    Yes, most of the time will be spent identifying the rows that match the where-criteria - and this has to be done whether you intend to fetch the data as well or not. So running both queries might double the time you need, although the second query might benefit from a higher buffer-hit-ratio. – piet.t Jun 09 '16 at 13:55