Java regex to remove SQL comments from a string

Question

Hope someone can help me out with this one !

I have a sql file that looks like this:

CREATE TABLE IF NOT EXISTS users(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,

    PRIMARY KEY (id),
    CONSTRAINT UNIQUE (firstname,lastname)
)
ENGINE=InnoDB
;

INSERT IGNORE INTO users (firstname,lastname) VALUES ('x','y');
/*
INSERT IGNORE INTO users (firstname,lastname) VALUES ('a','b');
*/

I have buit a web application that initializes a mysql database at startup with this function:

public static void initDatabase(ConnectionPool pool, File sqlFile){
    Connection con = null;
    Statement st = null;
    String mySb=null;
    try{
        con = pool.getConnection();
        mySb=IOUtils.copyToString(sqlFile);

        // We use ";" as a delimiter for each request then we are sure to have well formed statements
        String[] inst = mySb.split(";");

        st = con.createStatement();

        for(int i = 0; i<inst.length; i++){
            // we ensure that there is no spaces before or after the request string
            // in order not to execute empty statements
            if(!inst[i].trim().isEmpty()){
                st.executeUpdate(inst[i]);
            }
        }
        st.close();
    }catch(IOException e){
        throw new RuntimeException(e);
    }catch(SQLException e){
        throw new RuntimeException(e);
    }finally{
        SQLUtils.safeClose(st);
        pool.close(con);
    }
}

(This function was found on the web. Author, please forgive me for not citing your name, I lost it !!)

It works perfectly as long as there is not SQL comment blocks.

The copyToString() function basically does what it says. What I would like now is build a regex that will remove block comments from the string. I only have block comments /* */ in the file, no --.

What I have tried so far:

mySb = mySb.replaceAll("/\\*.*\\*/", "");

Unfortunatly, I'm not very good at regex...

I get all the troubles of "The matched string look something like /* comment */ real statement /* another comment*/ " and so on...

You need lazy operator ? in your regex

Gabber
– Gabber

2012-04-19 10:37:37 +00:00
Commented Apr 19, 2012 at 10:37 — Gabber
– Gabber, Commented Apr 19, 2012 at 10:37

Alexander Pavlov · Accepted Answer · 2012-04-19 11:36:06Z

10

Try

mySb = mySb.replaceAll("/\\*.*?\\*/", "");

(notice the ? which stands for "lazy").

EDIT: To cover multiline comments, use this approach:

Pattern commentPattern = Pattern.compile("/\\*.*?\\*/", Pattern.DOTALL);
mySb = commentPattern.matcher(mySb).replaceAll("");

Hope this works for you.

edited Apr 19, 2012 at 11:36

answered Apr 19, 2012 at 10:19

Alexander Pavlov

32.4k5 gold badges71 silver badges95 bronze badges

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

3 Comments

Eric C. Over a year ago

It seems to work for block comment that is on one line, but not for blocks which stand on multiple line !! Does the .*? also matches carriage return?

Alexander Pavlov Over a year ago

Ouch, you need to use a full-fledged Pattern instance using the Pattern.DOTALL flag. I will modify the answer shortly.

Eric C. Over a year ago

After having read the docs for Pattern class, it works, thanks a lot !!

Guillaume Polet · Accepted Answer · 2012-04-19 10:19:49Z

2

You need to use a reluctant qualifier like this:

public class Main {

    public static void main(String[] args) {
        String s = "The matched string look something like /* comment */ real statement /* another comment*/";
        System.err.println(s.replaceAll("/\\*.*?\\*/", ""));
    }
}

answered Apr 19, 2012 at 10:19

Guillaume Polet

47.7k5 gold badges86 silver badges118 bronze badges

Comments

JiP · Accepted Answer · 2012-04-19 10:26:02Z

2

Try the following approach:

String s = "/* comment */ select * from XYZ; /* comment */";
System.out.println(s.replaceAll("/\\*.*?\\*/", ""));

Outputs:

 select * from XYZ;

The .*? stands for use Laziness Instead of Greediness (that means the .* matches the largest string possible by default, i.e. is greedy => you have to configure it to be non-greedy using the ? in the .*? expression).

answered Apr 19, 2012 at 10:26

JiP

3,2781 gold badge22 silver badges15 bronze badges

1 Comment

Eric C. Over a year ago

thanks for the explanation of laziness and greediness. I at least learned something. But see my comment on Alexander Pavlov post please.

user2433984 · Accepted Answer · 2013-08-14 15:11:58Z

1

it won't work 100%

the comments can be a part of a valid string specified in the SQL and in that case they need to be kept...

I am just researching a solution... seems to be complicated

so far I have:

\G(?:[^']*?|'(?:[^']|'')*?'(?!'))*?\/\*.*?\*\/

but it matches all while I need to match the comment only... and just found out it could fail when preceded by a single-line comment... damn

edited Aug 14, 2013 at 15:11

answered Aug 14, 2013 at 15:03

user2433984

331 silver badge5 bronze badges

1 Comment

user2433984 Over a year ago

ok, so now comments in strings won't be a problem anymore, but for the single-line comments before multi-line comments... I think a parser will really be easier: (?:[^']*?|'(?:[^']|'')*?'(?!'))*?\K\/\*.*?\*\/

Collectives™ on Stack Overflow

Java regex to remove SQL comments from a string

4 Answers 4

3 Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related