source: trunk/src/groovy/net/kromhouts/HqlBuilder.groovy @ 897

Last change on this file since 897 was 643, checked in by gav, 14 years ago

SVN move HqlBuilder to net.kromhouts package.
File size: 15.7 KB
Line 
1/* Copyright 2010 the original author or authors.
2 *
3 * Licensed under the Apache License, Version 2.0 (the "License");
4 * you may not use this file except in compliance with the License.
5 * You may obtain a copy of the License at
6 *
7 *      http://www.apache.org/licenses/LICENSE-2.0
8 *
9 * Unless required by applicable law or agreed to in writing, software
10 * distributed under the License is distributed on an "AS IS" BASIS,
11 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12 * See the License for the specific language governing permissions and
13 * limitations under the License.
14 */
15
16package net.kromhouts
17
18/**
19 * Provides a DSL for building and managing HQL strings.
20 * For more usage examples see the HqlBuilderTests.
21 * HQL reference see http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/queryhql.html
22 *
23 * DML reference see http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/batch.html#batch-direct
24 * DML-style clauses: "update, delete, insert into" may not be well tested, the "set" clause has basic implementation.
25 * So double check the q.query output for these cases.
26 *
27 * Primary goals:
28 * 1. Easy to read and understand in code.
29 * 2. Easy to read and understand when printed (e.g when displayed in a report).
30 * 3. Easy to execute with correct paginateParams and namedParams.
31 * 4. Easy to change a clause and execute again.
32 *
33 * Basic usage:
34 *    def q = new HqlBuilder().query {
35 *        select 'count(distinct book)'
36 *        from 'Book as book'
37 *        where 'book.id > 100'
38 *            and 'book.inStock = true'
39 *    }
40 *    def totalCount = Book.executeQuery(q.query, q.namedParams, q.paginateParams)[0].toInteger()
41 *    q.select = 'distinct book'
42 *    def list = Book.executeQuery(q.query, q.namedParams, q.paginateParams)
43 *
44 *    def bookList = new PagedResultList(list, totalCount)
45 *    log.debug '\n' + q.printFormattedQuery
46 *
47 * @author Gavin Kromhout
48 * @version DraftB
49 *
50 */
51class HqlBuilder {
52
53    // HQL clauses.
54    // Each clause is a map key with a list of terms.
55    def clauses = [:]
56
57    // HQL namedParams.
58    // HQL requires the namedParams to match exactly with the clause expressions.
59    def namedParams = [:]
60
61    // HQL paginateParams.
62    def paginateParams = [max: 1000, offset: 0]
63
64    // The where clause terms are handled separately from other clauses
65    // and are a list of logicalTerms.
66    // The where clause is built by buildWhereClause.
67    def whereClauseTerms = []
68
69    // LogicalIndexStack holds indexes of the current whereClauseTerm nesting.
70    def logicalIndexStack = []
71
72   def logicalBuilders = [AND: 'and',
73                                        OR: 'or']
74
75    def nestingStack = []
76
77    // Sort and Order.
78    // It is easier and more flexible to simply add order as a clause, e.g: order 'by name desc, id asc'
79    // def sort = "" // e.g. instanceName.id
80    // def order = "" // e.g. asc or desc
81
82    /**
83     * Constructor.
84     * Any property that exists (or responds) in the class may be supplied as an argument.
85     * E.g: max:20, offset:10, debug:true
86     * The debug property does not really exist, but if true and no external log property
87     * has been setup then the internal mockLogger will be configured in debug mode.
88     *
89     * @param args A map of arguments, defaults to an empty map.
90     *
91     */
92    def HqlBuilder(Map args = [:]) {
93        args.each { arg ->
94            def argKey = arg.key.toLowerCase()
95            if(super.hasProperty(argKey))
96                this[argKey] = arg.value
97        }
98        if(!super.metaClass.hasMetaProperty('log'))
99            mockLogging(args.debug)
100        log.debug "HqlBuilder()"
101    }
102
103    /**
104     * Call with no args.
105     *  Has no real use other than to prevent obscure errors.
106     */
107    def call() {
108        log.debug "call()"
109    }
110
111    /**
112     * Call with closure as last arg.
113     * A typically used build call, e.g: q { } is equivalent to q.call() { }
114     */
115    def call(Closure cl) {
116        log.debug "call(Closure cl)"
117        handleClosure(cl)
118    }
119
120    /**
121     * Domain specific build method.
122     *  Has no real use other than to prevent obscure errors
123     * when user makes a call to query() and Groovy calls query(Closure cl)
124     *
125     * @returns This object.
126     *
127     */
128    def query() {
129        log.debug "query()"
130        return this // Must return this object to q.
131    }
132
133    /**
134     * Domain specific build method.
135     * The recommended build call, e.g: def q = new HqlBuilder().query { }
136     *
137     * @param cl The closure that will be used to build the query.
138     * @returns This object.
139     *
140     */
141    def query(Closure cl) {
142        log.debug "query(Closure cl)"
143        handleClosure(cl)
144        return this // Must return this object to q.
145    }
146
147    /**
148     * InvokeMethod resolves all undefined methods.
149     * Which include the clause methods, e.g: select 'book' is equivalent to select('book').
150     * Note that defined methods will be called directly since this class does not implement GroovyInterceptable.
151     * If class was "HqlBuilder implements GroovyInterceptable" then even println would be intercepted and
152     * several exlusions might be needed. e.g: if(methodName != 'call' && methodName != 'println')
153     */
154    def invokeMethod(String methodName, args) {
155
156        log.debug "invokeMethod(${methodName}, ${args})"
157
158        // Call any closures first, that way the nesting is handled and we just keep a reference.
159        if(args[-1] instanceof Closure) {
160            handleClosure(args[-1], methodName)
161            args = args.minus(args[-1])
162        }
163
164        if(!clauses.containsKey(methodName) && !isLogicalBuilder(methodName))
165            clauses[methodName] = []
166
167        if(args) {
168            if(isWhereClauseBuilder(methodName)) {
169                logicalBuilder(methodName, args)
170                return
171            }
172        }
173
174        for(arg in args) {
175            if(arg instanceof String || arg instanceof GString)
176                clauses[methodName] << arg
177        }
178
179    } // invokeMethod()
180
181    /**
182     * PropertyMissing.
183     * Allows clauses to be added after build, e.g: q.order = 'by book.name asc'
184     * and clauses to be removed, e.g: q.order = null
185     */
186    def propertyMissing(String propertyName, value) {
187        log.debug "propertyMissing(${propertyName}, ${value})"
188
189        if(value == null) {
190            removeClause(propertyName)
191            if(propertyName.toLowerCase() == 'where')
192                whereClauseTerms.clear()
193            return
194        }
195
196        if(!clauses.containsKey(propertyName))
197            clauses[propertyName] = []
198
199        // Occurs when user assigns to where clause, e.g: q.where = 'book.id > 100'
200        if(propertyName.toLowerCase() == 'where') {
201            whereClauseTerms.clear()
202            logicalBuilder(propertyName, [value])
203            return
204        }
205
206        if(value instanceof String || value instanceof GString)
207            clauses[propertyName] = [value]
208    } // propertyMissing(String propertyName, value)
209
210    /**
211     * PropertyMissing.
212     * Allow clauses to be accessed directly by name, e.g: println q.order.
213     * Since clauses is a Map null is simply returned for a non-existant clause.
214     */
215    def propertyMissing(String propertyName) {
216        log.debug "propertyMissing(${propertyName})"
217
218        if(!clauses.containsKey(propertyName))
219            clauses[propertyName] = []
220
221        // Occurs when user performs an operation on where clause.
222        // E.g: q.where << "book.id = 100" which is actually NOT a supported operation since
223        // calling the method provides the correct function e.g: q.where "book.id > 100".
224        // Also allows `println q.where` to be short hand for `println q.whereClauseTerms`
225        if(propertyName.toLowerCase() == 'where') {
226            return whereClauseTerms
227        }
228
229        clauses[propertyName]
230    } // propertyMissing(String propertyName)
231
232    def setMax(Integer value) {
233        paginateParams.max = value
234    }
235
236    def getMax() {
237        paginateParams.max
238    }
239
240    def setOffset(Integer value) {
241        paginateParams.offset = value
242    }
243
244    def getOffset() {
245        paginateParams.offset
246    }
247
248    /**
249     * RemoveClause.
250     * Allows clauses to be removed, e.g: q.removeClause('order')
251     *
252     * @param clauseName The clause to remove.
253     *
254     */
255    def removeClause(String clauseName) {
256            clauses.remove(clauseName)
257    }
258
259    /**
260     * BuildWhereClause.
261     * Build the where clause from whereClauseTerms.
262     */
263    def buildWhereClause(printFormat = false) {
264        //log.debug "buildWhereClause()"
265
266        if(!whereClauseTerms)
267            return ''
268
269        def whereClause = 'where '
270
271        def buildExpression // declared separately to allow recurrsion.
272        buildExpression = { term ->
273            def result = ''
274            def termCount = term.expressions.size()
275            if(termCount > 1) {
276                term.expressions.eachWithIndex { t, index ->
277                    if(index == 0)
278                        result += buildExpression(t)
279                    else if(printFormat)
280                        result += " \n\t${t.logic} ${buildExpression(t)}"
281                    else
282                        result += " ${t.logic} ${buildExpression(t)}"
283
284                }
285                result = "( "+result+" )"
286            }
287            else {
288                if(term.expressions[0] instanceof Map)
289                    result += "${term.expressions[0].expressions[0]}"
290                else
291                    result += "${term.expressions[0]}"
292            }
293            return result
294        }
295
296        whereClauseTerms.eachWithIndex { tm, index ->
297            if(index == 0)
298                whereClause += buildExpression(tm)
299            else if(printFormat)
300                whereClause += " \n\t${tm.logic} ${buildExpression(tm)}"
301            else
302                whereClause += " ${tm.logic} ${buildExpression(tm)}"
303        }
304
305        return whereClause
306    } // buildWhereClause(printFormat = false)
307
308    /**
309     * LogicalBuilder.
310     * Build the whereClauseTerms
311     * by appending logicalTerms to the appropriate expressions.
312     */
313    def logicalBuilder(logicalName, args) {
314        log.debug "logicalBuilder(${logicalName}, ${args})"
315        log.debug "logicalIndexStack: ${logicalIndexStack}"
316
317        def logic = getLogicalString(logicalName)
318
319        for(arg in args) {
320            if(arg instanceof String || arg instanceof GString) {
321                arg = arg.trim()
322                if(arg) { // prevent empty strings being added.
323                    if(logicalIndexStack.size() > 0) {
324                        // Append to current index position.
325                        whereClauseTerms[logicalIndexStack[-1]].expressions << logicalTerm(logic, arg)
326                    }
327                    else {
328                        // Append to 'root'.
329                        whereClauseTerms << logicalTerm(logic, null) // empty expression logicalTerm.
330                        whereClauseTerms[-1].expressions << logicalTerm(logic, arg) // append logicalTerm to expressions
331                    }
332                } // if(arg)
333            } // if(arg instanceof)
334        } // for
335
336    } // logicalBuilder(logicalName, args)
337
338    /**
339     * LogicalTerm.
340     * A logicalTerm is a map object that holds the logic and list of expressions of a whereClauseTerm.
341     */
342    def logicalTerm = { logic, expression ->
343        expression = expression ? [expression] : []
344        ['logic': getLogicalString(logic), 'expressions': expression]
345    }
346
347    /**
348     * GetLogicalString.
349     *
350     * @param logicalName The name to get the matching logicalBuilder string for.
351     */
352    private getLogicalString(logicalName) {
353
354        switch(logicalName.toLowerCase()) {
355            case 'where':
356                logicalBuilders.AND
357                break
358            case logicalBuilders.AND:
359                logicalBuilders.AND
360                break
361            case logicalBuilders.OR:
362                logicalBuilders.OR
363                break
364        }
365
366    }
367
368    /**
369     * HandleClosure.
370     * Setting delegate and DELEGATE_FIRST allows closure to access this object's properties first.
371     */
372    private handleClosure(Closure cl, String methodName = 'root') {
373        log.debug "handleClosure(${cl.toString()}, ${methodName})"
374        if(isWhereClauseBuilder(methodName)) {
375            whereClauseTerms << logicalTerm(getLogicalString(methodName), null)
376            logicalIndexStack << whereClauseTerms.size()-1
377        }
378        nestingStack.push(methodName)
379        cl.delegate = this
380        cl.resolveStrategy = Closure.DELEGATE_FIRST
381        cl.call()
382        //log.debug "nestingStack: $nestingStack"
383        nestingStack.pop()
384        if(isWhereClauseBuilder(methodName)) {
385            logicalIndexStack.pop()
386        }
387    }
388
389    /**
390     * MockLogging.
391     * This class has super cow powers and can mock out it's own debug logging.
392     */
393    private mockLogging(debug = false) {
394        def mockLogger = {}
395        if(debug) {
396            mockLogger = {msg ->
397                    println "${super.getClass()} - DEBUG: $msg"
398            }
399        }
400        super.metaClass.log = [debug: mockLogger]
401        log.debug "Internal mockLogger configured."
402    }
403
404    /**
405     * IsLogicalBuilder.
406     * Determine if a method is a logicalBuilder.
407     */
408    private isLogicalBuilder(String methodName) {
409        logicalBuilders.find{ it.value == methodName.toLowerCase()} ? true:false
410    }
411
412    /**
413     * IsWhereClauseBuilder.
414     * Determine if a method is a where clause builder.
415     */
416    private isWhereClauseBuilder(String methodName) {
417        methodName = methodName.toLowerCase()
418        if(methodName == 'where' || isLogicalBuilder(methodName))
419            return true
420        else
421            return false
422    }
423
424    /**
425     * GetQuery.
426     * Assemble and return the query in a format that can be directly executed.
427     * E.g: executeQuery(q.query, q.namedParams, q.paginateParams).
428     */
429    def getQuery() {
430        clauses.collect { clause ->
431            switch (clause.key.toLowerCase()) {
432                case 'select':
433                    clause.key + ' ' + clause.value.join(', ')
434                    break
435                case 'set':
436                    clause.key + ' ' + clause.value.join(', ')
437                    break
438                case 'where':
439                    buildWhereClause()
440                    break
441                case 'order':
442                    clause.key + ' ' + clause.value.join(', ')
443                    break
444                case 'group':
445                    clause.key + ' ' + clause.value.join(', ')
446                    break
447                default:
448                    clause.key + ' ' + clause.value.join(' ')
449            }
450        }.join(' ')
451    } // getQuery()
452
453    /**
454     * GetPrintFormattedQuery.
455     * Assemble and return the query in a format that can be more easily printed and read by a person.
456     * E.g: println q.printFormattedQuery or when displayed in a report.
457     */
458    def getPrintFormattedQuery() {
459        clauses.collect { clause ->
460            switch (clause.key.toLowerCase()) {
461                case 'select':
462                    clause.key + ' ' + clause.value.join(', \n\t')
463                    break
464                case 'set':
465                    clause.key + ' ' + clause.value.join(', \n\t')
466                    break
467                case 'where':
468                    buildWhereClause(true)
469                    break
470                case 'order':
471                    clause.key + ' ' + clause.value.join(', \n\t')
472                    break
473                case 'group':
474                    clause.key + ' ' + clause.value.join(', \n\t')
475                    break
476                default:
477                    clause.key + ' ' + clause.value.join(' \n\t')
478            }
479        }.join(' \n')
480    } // getPrintFormattedQuery()
481
482} // end class
Note: See TracBrowser for help on using the repository browser.