Looking for an embeddable SQL beautifier or reformatter

I am looking for a Java open source beautifier or reformatter for SQL that I can use to clean up DDL statements that I am generating with openArchitectureWare.

Nothing in the answer to "Online Code Beautifier And Formatter" is of use to me and I have not been able to get Simple SQL Formatter to work for me.

Answers


UPDATE 2:

org.hibernate.jdbc.util.BasicFormatterImpl got moved in release 4.0. It is now located at: org.hibernate.engine.jdbc.internal.BasicFormatterImpl.

UPDATE 1:

Technology marches on. As noted by Alex, org.hibernate.pretty.Formatter no longer exists as of version 3.3.2.GA. The replacement is org.hibernate.jdbc.util.BasicFormatterImpl:

String formattedSQL = new BasicFormatterImpl().format(sql);

ORIGINAL ANSWER:

If you're using Hibernate, they've got one built-in: org.hibernate.pretty.Formatter

String formattedSQL = new Formatter(sql).format();

Part of the eclipse Data Tools Platform is the SQL Development Tools Project.

The page describing how to use the SQL Query Parser has an extremely brief use of SQLQuerySourceFormat which provides these options:

  • preserveSourceFormat = the option to preserve the input source formating when SQL source text is generated
  • statementTerminator = the character separating multiple SQL statements
  • hostVariablePrefix = the character that preceedes a host language variable
  • parameterMarker = the character that identifies a host language parameter
  • delimitedIdentifierQuote* = the character that encloses delimited identifiers whose writing in case will be preserved
  • omitSchema = the current schema (omitted in SQL source, implicit to unqualified table references)
  • qualifyIdentifiers = the flag describing how identifiers in the SQL source will be qualified
  • preserveComments = the option to preserve comments in the parsed SQL source or/and the generated SQL source
  • generateCommentsForStatementOnly = the option to generate comments for the SQL source only in the context of the complete statement, or if set to false, for single SQL Query objects outside the context of a statement as well

With Hibernate v3.3.2.GA, org.hibernate.pretty.Formatter doesn't exist anymore. You can use its replacement : org.hibernate.jdbc.util.BasicFormatterImpl

Formatter f = new BasicFormatterImpl();
String formatted_sql_code = f.format(ugly_sql_code);

Have you considered:

http://www.sqlinform.com

They provide both an API version and a command line version (as well as an online version).

No knowledge of costs though.


You could just use a SQL grammar and build the AST with antlr. Then you can output the tree in any format you like.


Perhaps jsqlparser will work for you.

Not as easy to find as you might think, as there are a fair few defunct projects out there. In fact i failed to find it so ended up doing my own thing (based on the h2 parser - you can contact me if all else fails). As a consequence i do not know if it has a beautifier, but writing one on top should be straight forward enough.

Its based on a grammar and JavaCC, so probably a better option than reinventing this wheel with antlr in any event. You may find if you need to support various dialects of sql in complex statements that any approach based on a grammar will fail you.


Would this work - SQL Formatter.


So this is definitely what you are looking for: A SQL formatter library that support Oracle, SQL Server, DB2, MySQL, Teradata and PostgreSQL.


Need Your Help

What's the easiest, most practical way to toggle several lightbulbs with a PC?

continuous-integration usb serial-port electronics

This question is mainly electronics related, but it also has a programming aspect.