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