1 //$Id: Formatter.java 7815 2005-08-10 18:44:32Z oneovthafew $
2 package org.hibernate.pretty;
3
4 import java.util.HashSet;
5 import java.util.LinkedList;
6 import java.util.Set;
7 import java.util.StringTokenizer;
8
9 import org.hibernate.util.StringHelper;
10
11 public class Formatter {
12
13 private static final Set BEGIN_CLAUSES = new HashSet();
14 private static final Set END_CLAUSES = new HashSet();
15 private static final Set LOGICAL = new HashSet();
16 private static final Set QUANTIFIERS = new HashSet();
17 private static final Set DML = new HashSet();
18 private static final Set MISC = new HashSet();
19 static {
20
21 BEGIN_CLAUSES.add("left");
22 BEGIN_CLAUSES.add("right");
23 BEGIN_CLAUSES.add("inner");
24 BEGIN_CLAUSES.add("outer");
25 BEGIN_CLAUSES.add("group");
26 BEGIN_CLAUSES.add("order");
27
28 END_CLAUSES.add("where");
29 END_CLAUSES.add("set");
30 END_CLAUSES.add("having");
31 END_CLAUSES.add("join");
32 END_CLAUSES.add("from");
33 END_CLAUSES.add("by");
34 END_CLAUSES.add("join");
35 END_CLAUSES.add("into");
36 END_CLAUSES.add("union");
37
38 LOGICAL.add("and");
39 LOGICAL.add("or");
40 LOGICAL.add("when");
41 LOGICAL.add("else");
42 LOGICAL.add("end");
43
44 QUANTIFIERS.add("in");
45 QUANTIFIERS.add("all");
46 QUANTIFIERS.add("exists");
47 QUANTIFIERS.add("some");
48 QUANTIFIERS.add("any");
49
50 DML.add("insert");
51 DML.add("update");
52 DML.add("delete");
53
54 MISC.add("select");
55 MISC.add("on");
56 //MISC.add("values");
57
58 }
59
60 String indentString = " ";
61 String initial = "\n ";
62
63 boolean beginLine = true;
64 boolean afterBeginBeforeEnd = false;
65 boolean afterByOrSetOrFromOrSelect = false;
66 boolean afterValues = false;
67 boolean afterOn = false;
68 boolean afterBetween = false;
69 boolean afterInsert = false;
70 int inFunction = 0;
71 int parensSinceSelect = 0;
72 private LinkedList parenCounts = new LinkedList();
73 private LinkedList afterByOrFromOrSelects = new LinkedList();
74
75 int indent = 1;
76
77 StringBuffer result = new StringBuffer();
78 StringTokenizer tokens;
79 String lastToken;
80 String token;
81 String lcToken;
82
83 public Formatter(String sql) {
84 tokens = new StringTokenizer(
85 sql,
86 "()+*/-=<>'`\"[]," + StringHelper.WHITESPACE,
87 true
88 );
89 }
90
91 public Formatter setInitialString(String initial) {
92 this.initial = initial;
93 return this;
94 }
95
96 public Formatter setIndentString(String indent) {
97 this.indentString = indent;
98 return this;
99 }
100
101 public String format() {
102
103 result.append(initial);
104
105 while ( tokens.hasMoreTokens() ) {
106 token = tokens.nextToken();
107 lcToken = token.toLowerCase();
108
109 if ( "'".equals(token) ) {
110 String t;
111 do {
112 t = tokens.nextToken();
113 token += t;
114 }
115 while ( !"'".equals(t) );
116 }
117 else if ( "\"".equals(token) ) {
118 String t;
119 do {
120 t = tokens.nextToken();
121 token += t;
122 }
123 while ( !"\"".equals(t) );
124 }
125
126 if ( afterByOrSetOrFromOrSelect && ",".equals(token) ) {
127 commaAfterByOrFromOrSelect();
128 }
129 else if ( afterOn && ",".equals(token) ) {
130 commaAfterOn();
131 }
132
133 else if ( "(".equals(token) ) {
134 openParen();
135 }
136 else if ( ")".equals(token) ) {
137 closeParen();
138 }
139
140 else if ( BEGIN_CLAUSES.contains(lcToken) ) {
141 beginNewClause();
142 }
143
144 else if ( END_CLAUSES.contains(lcToken) ) {
145 endNewClause();
146 }
147
148 else if ( "select".equals(lcToken) ) {
149 select();
150 }
151
152 else if ( DML.contains(lcToken) ) {
153 updateOrInsertOrDelete();
154 }
155
156 else if ( "values".equals(lcToken) ) {
157 values();
158 }
159
160 else if ( "on".equals(lcToken) ) {
161 on();
162 }
163
164 else if ( afterBetween && lcToken.equals("and") ) {
165 misc();
166 afterBetween = false;
167 }
168
169 else if ( LOGICAL.contains(lcToken) ) {
170 logical();
171 }
172
173 else if ( isWhitespace(token) ) {
174 white();
175 }
176
177 else {
178 misc();
179 }
180
181 if ( !isWhitespace( token ) ) lastToken = lcToken;
182
183 }
184 return result.toString();
185 }
186
187 private void commaAfterOn() {
188 out();
189 indent--;
190 newline();
191 afterOn = false;
192 afterByOrSetOrFromOrSelect = true;
193 }
194
195 private void commaAfterByOrFromOrSelect() {
196 out();
197 newline();
198 }
199
200 private void logical() {
201 if ( "end".equals(lcToken) ) indent--;
202 newline();
203 out();
204 beginLine = false;
205 }
206
207 private void on() {
208 indent++;
209 afterOn = true;
210 newline();
211 out();
212 beginLine = false;
213 }
214
215 private void misc() {
216 out();
217 if ( "between".equals(lcToken) ) {
218 afterBetween = true;
219 }
220 if (afterInsert) {
221 newline();
222 afterInsert = false;
223 }
224 else {
225 beginLine = false;
226 if ( "case".equals(lcToken) ) {
227 indent++;
228 }
229 }
230 }
231
232 private void white() {
233 if ( !beginLine ) {
234 result.append(" ");
235 }
236 }
237
238 private void updateOrInsertOrDelete() {
239 out();
240 indent++;
241 beginLine = false;
242 if ( "update".equals(lcToken) ) newline();
243 if ( "insert".equals(lcToken) ) afterInsert = true;
244 }
245
246 private void select() {
247 out();
248 indent++;
249 newline();
250 parenCounts.addLast( new Integer(parensSinceSelect) );
251 afterByOrFromOrSelects.addLast( new Boolean(afterByOrSetOrFromOrSelect) );
252 parensSinceSelect = 0;
253 afterByOrSetOrFromOrSelect = true;
254 }
255
256 private void out() {
257 result.append(token);
258 }
259
260 private void endNewClause() {
261 if (!afterBeginBeforeEnd) {
262 indent--;
263 if (afterOn) {
264 indent--;
265 afterOn=false;
266 }
267 newline();
268 }
269 out();
270 if ( !"union".equals(lcToken) ) indent++;
271 newline();
272 afterBeginBeforeEnd = false;
273 afterByOrSetOrFromOrSelect = "by".equals(lcToken)
274 || "set".equals(lcToken)
275 || "from".equals(lcToken);
276 }
277
278 private void beginNewClause() {
279 if (!afterBeginBeforeEnd) {
280 if (afterOn) {
281 indent--;
282 afterOn=false;
283 }
284 indent--;
285 newline();
286 }
287 out();
288 beginLine = false;
289 afterBeginBeforeEnd = true;
290 }
291
292 private void values() {
293 indent--;
294 newline();
295 out();
296 indent++;
297 newline();
298 afterValues = true;
299 }
300
301 private void closeParen() {
302 parensSinceSelect--;
303 if (parensSinceSelect<0) {
304 indent--;
305 parensSinceSelect = ( (Integer) parenCounts.removeLast() ).intValue();
306 afterByOrSetOrFromOrSelect = ( (Boolean) afterByOrFromOrSelects.removeLast() ).booleanValue();
307 }
308 if ( inFunction>0 ) {
309 inFunction--;
310 out();
311 }
312 else {
313 if (!afterByOrSetOrFromOrSelect) {
314 indent--;
315 newline();
316 }
317 out();
318 }
319 beginLine = false;
320 }
321
322 private void openParen() {
323 if ( isFunctionName( lastToken ) || inFunction>0 ) {
324 inFunction++;
325 }
326 beginLine = false;
327 if ( inFunction>0 ) {
328 out();
329 }
330 else {
331 out();
332 if (!afterByOrSetOrFromOrSelect) {
333 indent++;
334 newline();
335 beginLine = true;
336 }
337 }
338 parensSinceSelect++;
339 }
340
341 private static boolean isFunctionName(String tok) {
342 final char begin = tok.charAt(0);
343 final boolean isIdentifier = Character.isJavaIdentifierStart( begin ) || '"'==begin;
344 return isIdentifier &&
345 !LOGICAL.contains(tok) &&
346 !END_CLAUSES.contains(tok) &&
347 !QUANTIFIERS.contains(tok) &&
348 !DML.contains(tok) &&
349 !MISC.contains(tok);
350 }
351
352 private static boolean isWhitespace(String token) {
353 return StringHelper.WHITESPACE.indexOf(token)>=0;
354 }
355
356 private void newline() {
357 result.append("\n");
358 for ( int i=0; i<indent; i++ ) {
359 result.append(indentString);
360 }
361 beginLine = true;
362 }
363
364 public static void main(String[] args) {
365 if ( args.length>0 ) System.out.println(
366 new Formatter( StringHelper.join(" ", args) ).format()
367 );
368 System.out.println(
369 new Formatter("insert into Address (city, state, zip, \"from\") values (?, ?, ?, 'insert value')").format()
370 );
371 System.out.println(
372 new Formatter("delete from Address where id = ? and version = ?").format()
373 );
374 System.out.println(
375 new Formatter("update Address set city = ?, state=?, zip=?, version = ? where id = ? and version = ?").format()
376 );
377 System.out.println(
378 new Formatter("update Address set city = ?, state=?, zip=?, version = ? where id in (select aid from Person)").format()
379 );
380 System.out.println(
381 new Formatter("select p.name, a.zipCode, count(*) from Person p left outer join Employee e on e.id = p.id and p.type = 'E' and (e.effective>? or e.effective<?) join Address a on a.pid = p.id where upper(p.name) like 'G%' and p.age > 100 and (p.sex = 'M' or p.sex = 'F') and coalesce( trim(a.street), a.city, (a.zip) ) is not null order by p.name asc, a.zipCode asc").format()
382 );
383 System.out.println(
384 new Formatter("select ( (m.age - p.age) * 12 ), trim(upper(p.name)) from Person p, Person m where p.mother = m.id and ( p.age = (select max(p0.age) from Person p0 where (p0.mother=m.id)) and p.name like ? )").format()
385 );
386 System.out.println(
387 new Formatter("select * from Address a join Person p on a.pid = p.id, Person m join Address b on b.pid = m.id where p.mother = m.id and p.name like ?").format()
388 );
389 System.out.println(
390 new Formatter("select case when p.age > 50 then 'old' when p.age > 18 then 'adult' else 'child' end from Person p where ( case when p.age > 50 then 'old' when p.age > 18 then 'adult' else 'child' end ) like ?").format()
391 );
392 }
393
394 }
Save This Page