0

Possible Duplicate:
SQL parser library for Java

I would like to use ready solution for parsing SQL file. File can contain comments, SQL statements, DDL. I need to get comments, all SQL statements. Doesn anyone know ready solution? I wouldn't like to ivent one more square wheel..

P.S. Please pay atttention that I don't need to ignore comments, I need to get text from comments in file

Community
  • 1
  • 1
Capacytron
  • 3,425
  • 6
  • 47
  • 80
  • 3
    It has already been asked http://stackoverflow.com/questions/660609/sql-parser-library-for-java – Arnon Rotem-Gal-Oz Dec 16 '11 at 07:55
  • http://stackoverflow.com/q/660609/108341 – Michaël Dec 16 '11 at 07:56
  • http://stackoverflow.com/questions/660609/sql-parser-library-for-java – narek.gevorgyan Dec 16 '11 at 07:56
  • I would like to satisfy your curiosity. I did you search. zql, jsqlparser don't give a chance to access comments, General SQL parser is not free. ANTLR is a tool to solve the problem, but not the solution for the problem. – Capacytron Dec 16 '11 at 08:30
  • Also please see my question and answers you've provided: try to find there a requirement: get *comments*. I don't need to ignore comments. – Capacytron Dec 16 '11 at 08:33
  • 1
    I'll be surprised if you find a ready-made solution for your exact problem (comments and all). I think it's more likely that you'll find a tool (such as ANTLR) that makes the job relatively easy. – NPE Dec 16 '11 at 08:45
  • Ok aix, I was afraid of it. One more SQL Parser is not my task. There are some ready grammar parsers for ANSI SQL on antlr site, I'll look at them. – Capacytron Dec 16 '11 at 09:05
  • Just in case anyone needs a more feature rich parser that is open source, supports multiple dialects, AST / visitors, checkout Alibaba's Druid: https://github.com/alibaba/druid/tree/master/src/main/java/com/alibaba/druid/sql – btiernay Mar 19 '17 at 22:06

1 Answers1

1

For what it's worth, I'd recommend that you look at iBatis, which IMHO, makes handwritten DAOs and DTOs with JDBC completely obsolete.

Before iBatis I use to keep my SQL in a separate SQL file and retrieved the correct SQL when I needed it. I don't use it anymore... since this is only marginally better than SQL in the actual Java code.

The syntax of the SQL file is as follows:

-- I added comments like this...
[id] {
  any SQL statement... (quite frankly any text).
} 

[id2] {
  ...
}

...

Then, a simple Parser class of the following:

public class SQLParser {
    private static Logger logger = LoggerUtility.getLogger(SQLParser.class);

    private String raw;
    private Map<String, String> sql;

    public SQLParser(InputStream is) {
        sql = new HashMap<String, String>();
        try {
            read(is);
            parse();
        } catch (IOException e) {
            logger.debug(LoggerCodes.TRACE, "Input Stream could not be successfull read!");
        }
    }

    private void read(InputStream is) throws IOException {
        StringBuilder builder = new StringBuilder();
        BufferedReader in = new BufferedReader(new InputStreamReader(is));
        String line = in.readLine();
        while (line != null) {
            builder.append(line + "\n");
            line = in.readLine();
        }
        raw = builder.toString();
    }

    public String getSql(String sqlId) {
        return sql.get(sqlId);
    }

    public PreparedStatement getSql(Connection connection, String sqlId) throws SQLException {
        String statement = sql.get(sqlId);
        return connection.prepareStatement(statement);
    }

    private void parse() {
        if (raw == null) {
            logger.debug(LoggerCodes.TRACE, "No String to parse for SQL statements!");
            return;
        }

        String regex = "\\.*\\[(\\S*)\\]\\s*\\{\\s*([^\\}]*)\\s*\\}";
        Pattern p = Pattern.compile(regex);
        Matcher m = p.matcher(raw);
        while (m.find()) {
            String key = m.group(1);
            String body = m.group(2);
            logger.debug("Adding " + key + " to SQL map.");
            sql.put(key, body.trim());
        }
    }
}

Then simply create a new instance of the above and call sqlParser.getSql(id) to obtain the correct SQL.

bezmax
  • 25,562
  • 10
  • 53
  • 84
Jaco Van Niekerk
  • 4,180
  • 2
  • 21
  • 48
  • Ok, granted... this solution will ignore the comments, but I am sure you can adapt it accordingly. – Jaco Van Niekerk Dec 16 '11 at 08:45
  • I would like to describe my task: I do have existing files with different sql scripts: DDL create table/views statements, selects, e.t.c. I would like to add macroses into these files. Macroses will be inside comments. So sql files will stay valid and my tool can read them, extract macroses from comments and execute additional operations. The idea is not to break existing SQL code stored in files and add more functionality. Thanks Jaco I use the same approach for sql queiries in my java projects. I hate to inline SQL code into Java code. It's ugly. :( – Capacytron Dec 16 '11 at 09:01