25

I'm not sure if this even exists or not, so I figured I would tap the wisdom of others..

I was wondering if there are any Java libraries out there that can be used to validate a SQL query's syntax. I know that there are many deviations from common SQL spec, so it would probably only work against something like SQL:2006, but that would certainly suffice.

My goal is to use this for unit-testing purposes without needing to attempt the execution against the DB. I know it's of limited use, but it would still be useful.

Thanks!

Improve this question
1
  • Do you need to validate SQL of specific vendor or standard ANSI SQL? All major SQL DBMS vendors significantly departed from standard by extending and underimplementing it at the same time. Commented Sep 26, 2008 at 20:17

7 Answers 7

Reset to default
11

Perhaps you can use Antlr, it has a number of SQL grammars and a Java library, as well as plugins for various Java IDEs.

Or as advised, use the parser of open source SQL utilities like SQuirreL SQL Client.

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

Comments

6

General SQL Parser can do offline SQL syntax check. Supported databases: Oracle, DB2, MySQL, SQL Server, Teradata and PostgreSQL.

Improve this answer

1 Comment

Nice tool but not free
5

Try JSQL parser.

In addition to validating, you obtain a meaningful representation of the query.
This allows you, for example, to only accept "certain" commands; manipulate
the query, "prettify" it, etc.

Improve this answer

2 Comments

I think this one moved to github: github.com/JSQLParser/JSqlParser
Validation examples: github.com/JSQLParser/JSqlParser/wiki/…
4

I don't think there are such libraries. The SQL syntax has too many derivatives.

A possible solution would be to use parts of an open source pure Java DBMS like SmallSQL. In this project you can create an instance of the SQLParser. The needed references to the connection can be removed very easily.

Improve this answer

5 Comments

Are you by any chance a SmallSQL developer?
Yes, I am one of the developers.
@Horcrux7: I downloaded SmallSQL just now. It didn't look so easy to remove the SSConnection reference. It seems like the SQLParser validates the SQL syntax by checking against an actual database schema. (e.g. SQLParser.from() method tries to connect to database).
If you can not remove the reverence then you can replace it with own placebo implementation. The syntax that you want parse must be have also a name schema.
Apache Calcite now exists (started in 2014) and it does exactly what OP asked (in 2008).
1

You might be able to extract the parsing code out from HSQL, which is java and open source.

Improve this answer

Comments

1

Apache Derby is an open source SQL database implemented entirely in Java and available under the Apache License, Version 2.0. It was formerly known as IBM Cloudscape.

You may try to reuse it's parsing code from org.apache.derby.impl.sql.

Improve this answer

Comments

0

With JOOQ you will never make a mistake in SQL. Java compiler will take care of that.

Improve this answer

4 Comments

If you're using JOOQ only as a query builder - are there any Jooq mechanisms to validate the generated SQL (as for example in a Junit test)?
@hovanessyan There is no need to validate them, I believe, because JOOQ emits valid SQL.
JOOQ allows you to write SQL statements yourself, so if you miss a comma or brackets or smth the SQL will be invalid. JOOQ emits valid SQL if you're using the generated model classes - I am only using it as SQL Builder and not relying on the model generation - so it's possible to generate invalid SQL.
@hovanessyan True. I think you can try to prepare the query to test it's validity. It requires active database connection, but I think if query is prepared ok, you can be 99,9% be sure that it's syntactically correct.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.