Read and extract the multiple SQL queries in a String and store them in a list in Java

Question

I need to extract only the SQL queries(all of them) and store them in a list to pass an argument in Java.

This is the sample input string

section Section1; shared Data_first = let Source = Sourcename.Database("abc.net", [HierarchicalNavigation=true , Query=" SELECT Company_ , Corporate_ , Corporate_Name , Group_Name , Division_Name , Market_Name FROM Entity_table WHERE CODE = '12345' "]), in #"Renamed Columns"; shared Data_first = let Source = Sourcename.Database("abc.net", [HierarchicalNavigation=true , Query=" SELECT column1, column2 FROM table1, table2 WHERE column2='value'; "]), in #"Renamed Columns"; shared Data_first = let Source = Sourcename.Database("abc.net", [HierarchicalNavigation=true , Query="SELECT Company_ , Corporate_ , Corporate_Name , Group_Name , Division_Name , Market_Name FROM Entity_table_three WHERE CODE = '78901'"]), in #"Renamed Columns";

Here is the code that I tried, but it only fetches the first query. I need all the queries present in the String. I am not familiar with regex so have not tried it.

public static String ReadBigStringIn(BufferedReader buffIn, String st) throws IOException {
    StringBuilder everything = new StringBuilder();
       
    StringBuilder lines = null ;
    while( (st = buffIn.readLine()) != null) {
        lines = everything.append(st);
    }
    String myQuery = lines.toString().substring(lines.toString().indexOf("Query"));
    System.out.println("myQuery: \n"+ myQuery);
    String query= substringBetween(myQuery, "Query=\"", "\"])");
    System.out.println("myQuery2: \n"+ query);
    return query;

Thanks in advance!

Hulya · Accepted Answer · 2021-01-09 09:13:40Z

You can use positive lookahead and positive lookbehind to capture the substring between the texts Query=" and the next " with this regex: (?<=Query=\\\")\\s*(.*?)(?=\\\") :

Pattern pattern = Pattern.compile("((?<=Query=\\\").*?(?=\\\"))");
Matcher matcher = pattern.matcher(lines);
while(matcher.find()) {
    System.out.println(matcher.group(1));
}

(?<=Query=\") : a positive lookbehind, matches if a string is preceded by Query="
\\s* : zero-or-more whitespace, to get rid of spaces after the "
(.*?) : match anything, ? non-greedily (till the next ")
(?=\"): a positive lookahead, matches if a string is followed by "

Output:

SELECT Company_ , Corporate_ , Corporate_Name , Group_Name , Division_Name , Market_Name FROM Entity_table WHERE CODE = '12345' 
SELECT column1, column2 FROM table1, table2 WHERE column2='value'; 
SELECT Company_ , Corporate_ , Corporate_Name , Group_Name , Division_Name , Market_Name FROM Entity_table_three WHERE CODE = '78901'

Tim Biegeleisen · Accepted Answer · 2021-01-09 08:57:36Z

1

I would use a Java regex matcher with the following pattern:

"\s*(SELECT.*?)\s*"

Demo

Your updated code:

public static List<String> getAllQueries (BufferedReader in) throws IOException {
    String line;
    StringBuffer lines = new StringBuilder();
    List<String> queries = new ArrayList<>();

    while ((line = in.readLine()) != null) {
        lines.append(st).append("\n");
    }

    String pattern = "\"\s*(SELECT.*?)\s*\"";
    Pattern p = Pattern.compile(pattern);
    Matcher m = r.matcher(lines.toString());
    while (m.find()) {
        queries.add(m.group(1));
    }

    return queries;
}

answered Jan 9, 2021 at 8:57

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

Read and extract the multiple SQL queries in a String and store them in a list in Java

2 Answers 2

Comments

Demo

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related