Java - regex on sql query

Question

I need to apply a java regex on sql query string to calculate the count of it. I have to get what is between the "first select" and "from" of the principal query. This is my example :

Query :
select name,(select age from subtable), adress from table where name in (select name from subtable1)

Result :
select count(*) from table where name in (select name from subtable1)

I was using replaceFirst("^(.*?)from", "select count(*) from") but it is not working because there is an sql query in the attribute.

Please anyone can help ?

Are you sure that is the correct approach? I feel an XY problem here. — Thomas
– Thomas, Commented Apr 28, 2017 at 9:06
@Thomas, I need to get the count of the query without executing the original one, it is just a performance problem. If you have another approach, I'm all ears, thanks. — Youssef El Rhailani
– Youssef El Rhailani, Commented Apr 28, 2017 at 9:13
What kind of performance problems do you want to solve? In most cases really executing the query should provide the best information especially since you could do the count on the results. Any alteration of the query is likely to falsify the information anyways. So if you're after a general indicator on query performance wouldn't a simple EXPLAIN <query> be sufficient? And since you've also added the hibernate tag: AFAIK Hibernate can collect a bunch of statistics that might help as well. — Thomas
– Thomas, Commented Apr 28, 2017 at 9:20
@Thomas, I need to get only the count information which will be used for pagination. Executing the whole query (20 attributes or more) is really expensive. All I want is to get the count directly by altering the query. — Youssef El Rhailani
– Youssef El Rhailani, Commented Apr 28, 2017 at 9:26
If it's for pagination it might be safer to dynamically build 2 queries with the same from clause (we're using a similar approach). Alternatively, some databases support returning the count of the query even if you just load a page (via offset and limit) thus it could be done in one query. Altering queries using regular expressions is very risky imo. — Thomas
– Thomas, Commented Apr 28, 2017 at 10:37

Youcef LAIDANI · Accepted Answer · 2017-04-28 09:38:24Z

1

You can solve your problem using this regex ^(.*?)from(?![^(]*\\)):

str = str.replaceFirst("^(.*?)from(?![^(]*\\))", "select count(*) from");

Output

select count(*) from table where name in (select name from subtable1)

The idea is match from that is inside () parenthesis.

Demo

answered Apr 28, 2017 at 9:38

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Java - regex on sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related