Looking for an embeddable SQL beautifier or reformatter
Solution 1
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();
Solution 2
With Hibernate v3.3.2.GA, org.hibernate.pretty.Formatter doesn't exist anymore. You can use its replacement : org.hibernate.jdbc.util.BasicFormatterImpl
Use org.hibernate.engine.jdbc.internal.BasicFormatterImpl for Hibernate 4.0+.
Formatter f = new BasicFormatterImpl();
String formatted_sql_code = f.format(ugly_sql_code);
Solution 3
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
Solution 4
Have you considered:
They provide both an API version and a command line version (as well as an online version).
No knowledge of costs though.
Solution 5
Would this work - SQL Formatter.
![Brian Matthews](https://i.stack.imgur.com/ls8Uy.jpg?s=256&g=1)
Comments
-
Brian Matthews about 4 years
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.
-
Brian Matthews over 15 yearsThanks. That will be my plan of last resort. Fortunately I am too busy with a new job to look at this now and hopefully I'll get an answer before I have to roll up my sleeves.
-
Stephen Denne over 15 yearsOr DDLFormatter, which will a) Insert newline after each comma; b) Indent three spaces after each inserted newline;
-
Brian Matthews over 15 yearsI need to be able to do this off-line as after code generation step in my build process. So SQL Formatter is not an option for me.
-
Brian Matthews over 15 yearsThis would appear to be exactly what I am looking for. Hibernate is already a core component of my solution so its not introducing a new dependency.
-
Brian Matthews over 15 yearsThis solution would definitely work for me but I prefer the simplicity of the Hibernate solution. A pity I could only give you a +1
-
Karl over 10 yearsFYI, BasicFormatterImpl does not handle comments '--' and DDLFormatter is even more primitive see dil.univ-mrs.fr/~massat/docs/hibernate-3.1/api/org/hibernate/…
-
Guido almost 10 yearsSQLinForm at www.sqlinform.com is handling comments too and has many formatting options.
-
Kohei Nozaki over 8 yearsI created a standalone SQL beautifulier tool which uses
BasicFormatterImpl
andDDLFormatterImpl
. github.com/lbtc-xxx/sql-beautifulier -
Choppy The Lumberjack almost 5 yearsCheck out SQL Formatter by Yohei Yyamana. It's Java, open source, zero-dependencies, and already in maven central! github.com/vertical-blank/sql-formatter