This discussion is archived
12 Replies Latest reply: Nov 23, 2009 2:54 PM by 807580 RSS

regex issue - dynamic insert

807580 Newbie
Currently Being Moderated
Hi, everybody

I have HQL queries strings looking like that:
String query = 
     "SELECT DISTINCT obj1 " +
     "FROM POJO_Object1 obj1 " + 
     "LEFT OUTER JOIN obj1.collectionOfObj2 " +
     "WHERE obj1.id = ?";
String query = 
     "SELECT DISTINCT obj2 " +
     "FROM POJO_Object2 obj2 " + 
     "JOIN obj2.obj3 " +
     "LEFT OUTER JOIN obj2.collectionOfObj4";
and so on...

I would like to be able to alter dynamically the queries and insert systematically the same string inside it, like in the examples above:

Example1
String query = 
     "SELECT DISTINCT obj1 " +
     "FROM POJO_Object1 obj1 " + 
     "LEFT OUTER JOIN obj1.collectionOfObj2 " +
     "WHERE obj1.id = ?";
would give:
String query = 
     "SELECT DISTINCT obj1 " +
     "FROM POJO_Object1 obj1 " + 
     "LEFT OUTER JOIN obj1.collectionOfObj2 " +
        "LEFT OUTER JOIN obj1.x " +
        "LEFT OUTER JOIN obj1.x.y " +
     "WHERE obj1.id = ?";
Example2
String query = 
     "SELECT DISTINCT obj2 " +
     "FROM POJO_Object2 obj2 " + 
     "JOIN obj2.obj3 " +
     "LEFT OUTER JOIN obj2.collectionOfObj4";
would give
String query = 
     "SELECT DISTINCT obj2 " +
     "FROM POJO_Object2 obj2 " + 
     "JOIN obj2.obj3 " +
     "LEFT OUTER JOIN obj2.collectionOfObj4" +
        "LEFT OUTER JOIN obj2.x " +
        "LEFT OUTER JOIN obj2.x.y";
The process is always the same: parsing the word between "SELECT DISTINCT" and "FROM", and adding the two lines using this word inside it.

Of course I could do it using simple parsing and concatenations, but i thought maybe there was a more efficient and smarter way using regex (with backreferences and things like that). I don't really know how to achieve it, though, as i'm not a regex specialist.

Any help will be greatly appreciated!
  • 1. Re: regex issue - dynamic insert
    807580 Newbie
    Currently Being Moderated
    <kinobi action="handwave"> I'm not the regex guru you're looking for </kinobi>

    Just as a possible alternative, isn't this the sort of thing the Criteria API was designed for?
  • 2. Re: regex issue - dynamic insert
    807580 Newbie
    Currently Being Moderated
    thanks for your answer, georgemc

    using the Criteria API would make things easier, for sure, but unfortunately, I cannot use it (too many queries to re-code + there is an issue with pagination in the Criteria API which prevents me from using it)
  • 3. Re: regex issue - dynamic insert
    807580 Newbie
    Currently Being Moderated
    calvino_ind wrote:
    thanks for your answer, georgemc

    using the Criteria API would make things easier, for sure, but unfortunately, I cannot use it (too many queries to re-code + there is an issue with pagination in the Criteria API which prevents me from using it)
    Oh right! Well, best wait for one of the resident regex gurus to turn up then. I make no claim to that title, unfortunately.
  • 4. Re: regex issue - dynamic insert
    YoungWinston Expert
    Currently Being Moderated
    calvino_ind wrote:
    The process is always the same: parsing the word between "SELECT DISTINCT" and "FROM", and adding the two lines using this word inside it.
    Is it? Seems that there might be a need for more than just that. Maybe not now, but what about in the future?
    Of course I could do it using simple parsing and concatenations, but i thought maybe there was a more efficient and smarter way using regex (with backreferences and things like that). I don't really know how to achieve it, though, as i'm not a regex specialist.
    Unlikely to be more efficient; and there's usually a tradeoff with regexes as to how generic they are and how easy they are to use.
    Do you want your maintenance people to have PhD's in regex parsing in order to look after your code?

    Seems to me that some sort of HQLBuilder class that deals with each component (the "SELECT", the "FROM", the "JOIN"s and the "WHERE") might be easier to follow; and likely more flexible too.

    Winston
  • 5. Re: regex issue - dynamic insert
    807580 Newbie
    Currently Being Moderated
    Thanks for your hint, Winston, your idea seems to me like a good idea, I will follow that approach if nobody can give me the "magic regex" that would fit my needs :)
  • 6. Re: regex issue - dynamic insert
    807799 Newbie
    Currently Being Moderated
    If you want to experiment with the regex approach...
    String query =
        "SELECT DISTINCT obj1 " +
        "FROM POJO_Object1 obj1 " +
        "LEFT OUTER JOIN obj1.collectionOfObj2 " +
        "WHERE obj1.id = ?";
    
    Matcher m = Pattern.compile(
        "(?i)^select\\s+distinct\\s+(\\w+)(.+?)(\\s+where.+)?$").
        matcher(query);
    
    if (m.matches())
        System.out.println(
            "SELECT DISTINCT " + m.group(1) + m.group(2) +
            " LEFT OUTER JOIN " + m.group(1) +
            ".x LEFT OUTER JOIN " + m.group(1) +
            ".x.y" + (m.group(3) != null ? m.group(3) : ""));
    else
        System.err.println("Oops, need to revise my regex");
  • 7. Re: regex issue - dynamic insert
    807799 Newbie
    Currently Being Moderated
    or if you like...
    String query =
        "SELECT DISTINCT obj1 " +
        "FROM POJO_Object1 obj1 " +
        "LEFT OUTER JOIN obj1.collectionOfObj2 " +
        "WHERE obj1.id = ?";
    
    Matcher m = Pattern.compile(
        "(?i)^(select\\s+distinct\\s+(\\w+).+?)(\\s+where.+)?$").
        matcher(query);
    
    System.out.println(m.replaceFirst(
        "$1 LEFT OUTER JOIN $2.x LEFT OUTER JOIN $2.x.y$3"));
  • 8. Re: regex issue - dynamic insert
    807580 Newbie
    Currently Being Moderated
    thank for your input, lindsten, i did something quite similar, here is the code in case someone ever needs it:
    public class HQLSelectAssistant {
    
         private static final String QUERY_PATTERN = 
              "(SELECT\\s+(?:DISTINCT\\s+)?\\w+\\s+)" +
              "(FROM\\s+\\w+\\s+\\w+\\s+)" +
              "((?:(?:LEFT\\s+OUTER\\s+)?JOIN\\s+(?:FETCH\\s+)?[a-zA-Z_0-9\\.]+\\s+)*)" +
              "((?:WHERE.*)?)" +
              "((?:ORDER BY.*)?)";
         
         private static final String SELECT_TOKEN_PATTERN = 
              "SELECT\\s+(?:DISTINCT\\s+)?(\\w+)\\s+";
    
         private String initialQuery;
         
         private String selectToken;
         private String fromToken;
         private String joinToken;
         private String whereToken;
         private String orderToken;
         
         public HQLSelectAssistant(String query) {
              this.initialQuery = query;
              this.parseQuery();
         }
         
         private void parseQuery() {
              Matcher m = Pattern.compile(QUERY_PATTERN).matcher(this.initialQuery);
              m.find();
              this.selectToken = m.group(1);
              this.fromToken = m.group(2);
              this.joinToken = m.group(3);
              this.whereToken = m.group(4);
              this.orderToken = m.group(5);          
         }
         
         public String getInitialQuery() {
              return this.initialQuery;
         }
         
         public String getQuery() {
              return 
                   this.selectToken + " " +
                   this.fromToken + " " +
                   this.joinToken + " " +
                   this.whereToken + " " + 
                   this.orderToken;
         }
         
         public String getTarget() {
              Matcher m = Pattern.compile(SELECT_TOKEN_PATTERN).matcher(this.selectToken);
              m.find();
              return m.group(1);
         }
         
         public HQLSelectAssistant addJoin(String join) {
              this.joinToken += join + " ";
              return this;
         }
         
         public String toString() {
              return 
                   this.selectToken + "\n" +
                   this.fromToken + "\n" +
                   this.joinToken + "\n" +
                   this.whereToken + "\n" + 
                   this.orderToken;
         }
              
    }
    test was successful with the following main:
         public static void main(String[] args) {
              String alterMe = 
                   "SELECT foo " +
                   "FROM POJO_Foo foo " + 
                   "LEFT OUTER JOIN foo.collectionOfBar " +
                   "WHERE foo.id = ?";
              
              HQLSelectAssistant hqlsa = new HQLSelectAssistant(alterMe);
              
              String firstJoin = "LEFT OUTER JOIN FETCH " + hqlsa.getTarget() + ".x";
              String secondJoin = "LEFT OUTER JOIN FETCH " + hqlsa.getTarget() + ".x.y";
              
              System.out.println(hqlsa.addJoin(firstJoin).addJoin(secondJoin));
              
         }
    I think this should do the trick, thanks to everyone :)
  • 9. Re: regex issue - dynamic insert
    807580 Newbie
    Currently Being Moderated
    note: the regex I made isn't fully functional (one of the main issue is that it will grab both the "where" and "order" token in the same token (the where token) because of the ".*" used in the where token. It might be a problem in case someone needs to create an "addWhere" method. I guess i ll keep the regex that way since it fits my needs atm...

    btw, I tried to google for a generic HQL query like the one I intend to make but didn't find much about it. if anybody ever found something like this, you re welcome to share it!
  • 10. Re: regex issue - dynamic insert
    807799 Newbie
    Currently Being Moderated
    calvino_ind wrote:
    one of the main issue is that it will grab both the "where" and "order" token in the same token (the where token) because of the ".&#42;" used in the where token.
    You can use a lazy quantifier (&#42;?) instead, that way the engine will try to match as few items as possible.


    (how do I write an asterisk without getting bold text?)

    Edited by: lindsten on 2009-nov-23 21:10

    (there, fixed the asterisks.. thanks uncle_alice)

    Edited by: lindsten on 2009-nov-24 08:20
  • 11. Re: regex issue - dynamic insert
    807580 Newbie
    Currently Being Moderated
    Do you really need to match the whole query string? This does what you want with the sample queries you provided:
    return query.replaceFirst("(\\s+LEFT\\s+OUTER\\s+JOIN\\s+\\w+)\.(\\w+)",
                              "$1.$2$1.x$1.x.y");
    If you want a more general query-transformation solution, I suggest you forget about regexes. They're very handy, but there are many kinds of text they can't handle very well. HTML is the most common example, but SQL is even harder to process. HTML at least has special symbols like angle brackets you can use to anchor your matches.

     

    @lindsten: use &#38;#42; (and &#38;#43; for the plus sign). Or put the text in a &#91;code] block.
  • 12. Re: regex issue - dynamic insert
    807580 Newbie
    Currently Being Moderated
    thanks for your advice, uncle_alice, I ll try that :)