Geek asks and answers

Java regex to remove SQL comments from a string

Hope someone can help me with this!

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 created a web application that initializes the mysql database at startup using 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 Internet. The author, please forgive me for not saying your name, I lost it!)

It works fine if there are no SQL comment blocks.

A function copyToString()basically does what it says. Now I need to create a regular expression that will remove the block comments from the string. I have only block comments /* */in the file, no --.

What I have tried so far:

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

Unfortunately, I am not very good at regex ...

" /* comment */ real statement /* another comment*/" .....

+3
4

Try

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

( ?, " lazy" ).

EDIT. , :

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

, .

+7

:

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("/\\*.*?\\*/", ""));
    }
}
+2

:

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

:

 select * from XYZ;

.*? " " ( , .* , .. greedy = > , ? .*?).

+2

100%

comments can be part of a valid string specified in SQL, in which case they need to be saved ...

I'm just researching a solution ... seems complicated

So far I:

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

but it matches all, for now I only need to combine the comment ... and just found that it can fail when it is preceded by a comment on one line ... damn

0
source

More articles:


All Articles