CODEWITHSUNDEEP
javaregex
jagamot
jagamot

Reputation: 5464

Need help with parsing sql in java

I have the below code that splits the sql statement and give its indices for the columns. 

String sql = "INSERT INTO Table1(SSN, EMPID) VALUES (?,?)";

    public static List<Object[]> indices(String stmt) {
        List<Object[]> list = new ArrayList<Object[]>();
        String variables[] = null;
        try {
            variables = stmt.substring(stmt.indexOf('(')+1, 
                stmt.indexOf(')', stmt.indexOf('(')+1)).split("\\,");
        } catch (Exception e) {}

        for (int i=0; i < variables.length; ++i ) {
            Object [] str =  new Object [2] ;
            str[0] = variables[i].trim() ;
            str[1] = ((Integer)(i+1)) ;
            list.add(str) ;
        }
        return list;
    }



Result - 

list[0] >>

array[0] = SSN
array[1] = 1

list [1] >>
array[0] = EMPID
array[1] = 2

Can some one point me with appropriate regular expression to split the following sql instead -

sql = "if not exists (select * from Table1 where SSN = ? and EMPID =?)  
      INSERT INTO Table1(SSN, EMPID) VALUES (?,?)"

I guess the output would be something like -

list[0] >>
array[0] = SSN
array[1] = 1

list [1] >>
array[0] = EMPID
array[1] = 2

list[2] >>
array[0] = SSN
array[1] = 1

list [3] >>
array[0] = EMPID
array[1] = 2

Thank You

Upvotes: 2

Views: 1062

Answers (3)

chetan
chetan

Reputation: 11

ZQL parser is great.

It can parse almost all queries except CREATE

Features:

  • list colums,
  • list tables,
  • FOR clause,
  • WHERE clause
  • etc

Upvotes: 0

Andreas Dolk
Andreas Dolk

Reputation: 114817

What about this one as an alternative: Zql, a SQL parser. Could make it much easier to access the 'elements' of any SQL statement.

EDIT

Easiest approach for the second SQL:

just do a sql.split("INSERT INTO") and apply your existing code to the second array entry (should be " Table1(SSN, EMPID) VALUES (?,?)"). As far as I understand your code, it should give the correct result.

Yes, I missed your point. Use a parser.

Upvotes: 1

Stephen C
Stephen C

Reputation: 719426

Attempting to parse non-trivial languages like SQL using regexes or other low-level string bashing is a BAD IDEA. You end up with unreadable code and a fragile "parser" that breaks when supplied with input that is invalid or valid-but-different.

You either need to implement a proper SQL parser (or use an existing one), or change your code so that you are not parsing SQL but assembling it from something else.

I have an application depending on a framework and don't want to introduce new libraries!

That is a bad reason for not doing proper parsing. What is wrong with not using another library???

Upvotes: 4

Related Questions