Changeset 642 for trunk


Ignore:
Timestamp:
Sep 5, 2010, 8:06:53 PM (14 years ago)
Author:
gav
Message:

HqlBuilder and tests, draftB.

Location:
trunk
Files:
2 edited

Legend:

Unmodified
Added
Removed

  • trunk/src/groovy/HqlBuilder.groovy

    r641 r642  
    1616/**
    1717 * Provides a DSL for building and managing HQL strings.
    18  * For usage examples see the HqlBuilderTests.
     18 * For more usage examples see the HqlBuilderTests.
    1919 * HQL reference see http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/queryhql.html
     20 *
     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.
    2024 *
    2125 * Primary goals:
    … …  
    2529 * 4. Easy to change a clause and execute again.
    2630 *
     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 *
    2745 * @author Gavin Kromhout
    28  * @version DraftA
     46 * @version DraftB
    2947 *
    3048 */
    3149class HqlBuilder {
    3250
    33     // Query clauses.
    34     // Each clause is a key with a list of terms.
     51    // HQL clauses.
     52    // Each clause is a map key with a list of terms.
    3553    def clauses = [:]
    3654
    … …  
    4260    def paginateParams = [max: 1000, offset: 0]
    4361
    44     // It is easier and more flexible to simply add order as a clause, e.g: order 'by id asc'
    45     //def sort = "" // e.g. instanceName.id
    46     //def order = "" // e.g. asc or desc
    47 
    48     def HqlBuilder(debug = false) {
     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 = []
     66
     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        }
    4996        if(!super.metaClass.hasMetaProperty('log'))
    50             mockLogging(debug)
     97            mockLogging(args.debug)
    5198        log.debug "HqlBuilder()"
    5299    }
    53100
     101    /**
     102     * Call with no args.
     103     *  Has no real use other than to prevent obscure errors.
     104     */
    54105    def call() {
    55106        log.debug "call()"
    … …  
    69120     *  Has no real use other than to prevent obscure errors
    70121     * when user makes a call to query() and Groovy calls query(Closure cl)
     122     *
    71123     * @returns This object.
     124     *
    72125     */
    73126    def query() {
    … …  
    78131    /**
    79132     * Domain specific build method.
    80      * A typically used build call, e.g: def q = new HqlBuilder().query { }
    81      *
    82      * @param cl The supplied Closure.
     133     * The recommended build call, e.g: def q = new HqlBuilder().query { }
     134     *
     135     * @param cl The closure that will be used to build the query.
    83136     * @returns This object.
    84137     *
    … …  
    92145    /**
    93146     * InvokeMethod resolves all undefined methods.
    94      * Which include the clause methods, e.g select 'book' is equivalent to select('book').
     147     * Which include the clause methods, e.g: select 'book' is equivalent to select('book').
    95148     * Note that defined methods will be called directly since this class does not implement GroovyInterceptable.
    96149     * If class was "HqlBuilder implements GroovyInterceptable" then even println would be intercepted and
    97150     * several exlusions might be needed. e.g: if(methodName != 'call' && methodName != 'println')
    98151     */
    99     def invokeMethod(String name, args) {
     152    def invokeMethod(String methodName, args) {
    100153
    101154        log.debug "invokeMethod(${methodName}, ${args})"
    102155
    103         if(!this.clauses[name])
    104             this.clauses[name] = []
     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        }
     161
     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        }
    105171
    106172        for(arg in args) {
    107             if(arg instanceof String)
    108                 this.clauses[name] << arg
    109         }
    110 
    111         if(args[-1] instanceof Closure)
    112             handleClosure(args[-1])
     173            if(arg instanceof String || arg instanceof GString)
     174                clauses[methodName] << arg
     175        }
    113176
    114177    } // invokeMethod()
    … …  
    119182     * and clauses to be removed, e.g: q.order = null
    120183     */
    121     def propertyMissing(String name, value) {
     184    def propertyMissing(String propertyName, value) {
    122185        log.debug "propertyMissing(${propertyName}, ${value})"
     186
    123187        if(value == null) {
    124             clauses.remove(name)
     188            removeClause(propertyName)
     189            if(propertyName.toLowerCase() == 'where')
     190                whereClauseTerms.clear()
    125191            return
    126192        }
    127193
    128         if(value instanceof String)
    129             clauses[name] = [value]
    130     }
     194        if(!clauses.containsKey(propertyName))
     195            clauses[propertyName] = []
     196
     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)
    131207
    132208    /**
    … …  
    135211     * Since clauses is a Map null is simply returned for a non-existant clause.
    136212     */
    137     def propertyMissing(String name) {
    138         log.debug "propertyMissing(${name})"
    139         clauses[name]
     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
     232    }
     233
     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
     246    /**
     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
    140364    }
    141365
    … …  
    144368     * Setting delegate and DELEGATE_FIRST allows closure to access this object's properties first.
    145369     */
    146     private handleClosure(Closure cl) {
     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)
    147377        cl.delegate = this
    148378        cl.resolveStrategy = Closure.DELEGATE_FIRST
    149379        cl.call()
     380        //log.debug "nestingStack: $nestingStack"
     381        nestingStack.pop()
     382        if(isWhereClauseBuilder(methodName)) {
     383            logicalIndexStack.pop()
     384        }
    150385    }
    151386
    … …  
    166401
    167402    /**
     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    /**
    168423     * GetQuery.
    169424     * Assemble and return the query in a format that can be directly executed.
    … …  
    171426     */
    172427    def getQuery() {
    173         this.clauses.collect {
    174             it.key + ' ' + it.value.join(' ')
     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            }
    175448        }.join(' ')
    176     }
     449    } // getQuery()
    177450
    178451    /**
    … …  
    182455     */
    183456    def getPrintFormattedQuery() {
    184         this.clauses.collect {
    185             it.key + ' ' + it.value.join(' \n')
     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            }
    186477        }.join(' \n')
    187     }
     478    } // getPrintFormattedQuery()
    188479
    189480} // end class

  • trunk/test/unit/HqlBuilderTests.groovy

    r641 r642  
    2121 *
    2222 * @author Gavin Kromhout
    23  * @version DraftA
     23 * @version DraftB
    2424 *
    2525 */
    … …  
    2727
    2828    def n = '\n'
     29    def t = '\t'
    2930    def savedMetaClass
    3031
    … …  
    3334        savedMetaClass = HqlBuilder.metaClass
    3435        def emc = new ExpandoMetaClass(HqlBuilder, true, true)
    35         //emc.log = LogFactory.getLog(getClass())
     36        emc.log = LogFactory.getLog(getClass())
    3637        emc.initialize()
    3738        GroovySystem.metaClassRegistry.setMetaClass(HqlBuilder, emc)
    … …  
    4445    }
    4546
    46     void testSelectBasic() {
     47    void testBasicUsage() {
    4748
    4849        def q = new HqlBuilder().query {
    … …  
    5859        assert q.printFormattedQuery == 'select distinct book \nfrom Book as book \nwhere book.id > 100'
    5960
    60     } // testSelectBasic()
    61 
    62     void testSelectAdditional() {
    63 
    64         def q = new HqlBuilder().query {
    65             select 'distinct book'
    66             from 'Book as book'
    67             left 'join book.group as group',
     61    } // testBasicUsage()
     62
     63    void testBasicUsageAlternateForm() {
     64
     65        def q = new HqlBuilder()
     66
     67        q {
     68            select 'distinct book'
     69            from 'Book as book'
     70            where 'book.id > 100'
     71        }
     72
     73        assert q.query == 'select distinct book from Book as book where book.id > 100'
     74
     75    } // testBasicUsageAlternateForm()
     76
     77    void testPaginateParams() {
     78
     79        def q = new HqlBuilder(max: 99, offset: 11).query {
     80            select 'distinct book'
     81            from 'Book as book'
     82            where 'book.id > 100'
     83        }
     84
     85        assert q.max == 99
     86        assert q.offset == 11
     87
     88    } // testPaginateParams()
     89
     90    void testPaginateParamsAlternateForm() {
     91
     92        def q = new HqlBuilder().query {
     93            max = 99
     94            offset = 11
     95            select 'distinct book'
     96            from 'Book as book'
     97            where 'book.id > 100'
     98        }
     99
     100        assert q.max == 99
     101        assert q.offset == 11
     102
     103    } // testPaginateParamsAlternateForm()
     104
     105    void testNamedParams() {
     106        def startId = 13
     107        def endId = 23
     108
     109        def q = new HqlBuilder().query {
     110            namedParams.startId = startId
     111            select 'distinct book'
     112            from 'Book as book'
     113            where 'book.id > :startId'
     114                and 'book.id < :endId'
     115        }
     116
     117        q.namedParams.endId = endId
     118
     119        assert q.namedParams.startId == startId
     120        assert q.namedParams.endId == endId
     121
     122    } // testNamedParams()
     123
     124    void testMultipleTerms() {
     125
     126        def q = new HqlBuilder().query {
     127            select 'book.name',
     128                        'type.name'
     129            from 'Book as book',
     130                    'left join book.group as group',
    68131                    'left join group.type as type'
    69             where 'book.id > 100',
    70                         'and group = :group'
    71         }
    72 
    73         assert q.query == 'select distinct book from Book as book left join book.group as group left join group.type as type where book.id > 100 and group = :group'
    74 
    75     } // testSelectAdditional()
    76 
    77     void testSelectAlternate() {
    78 
    79         def q = new HqlBuilder()
    80 
    81         q {
    82             select 'distinct book'
    83             from 'Book as book'
    84             where(/book.name like '%Ned%'/) // Slashy string literals have to be protected when calling a function.
    85             where 'and book.description like "Head"'
    86         }
    87 
    88         assert q.query == /select distinct book from Book as book where book.name like '%Ned%' and book.description like "Head"/
    89         assert q.printFormattedQuery == /select distinct book ${n}from Book as book ${n}where book.name like '%Ned%' ${n}and book.description like "Head"/
    90 
    91     } // testSelectAlternate()
    92 
    93     void testSelectWithPlaceHolder() {
    94 
    95         def q = new HqlBuilder().query {
    96             select 'distinct book'
    97             from 'Book as book'
    98             where '' // Place holder.
     132            where "book.name like '%Ned%'",
     133                        'group = :group'
     134        }
     135
     136        def expectedQuery = /select book.name, type.name from Book as book left join book.group as group left join group.type as type/
     137        expectedQuery +=  / where book.name like '%Ned%' and group = :group/
     138        assert q.query == expectedQuery
     139
     140        def expectedPrintFormat = /select book.name, ${n}${t}type.name/
     141        expectedPrintFormat += / ${n}from Book as book ${n}${t}left join book.group as group ${n}${t}left join group.type as type/
     142        expectedPrintFormat += / ${n}where book.name like '%Ned%' ${n}${t}and group = :group/
     143        assert q.printFormattedQuery == expectedPrintFormat
     144
     145    } // testMultipleTerms()
     146
     147    void testMultipleTermsAlternateForm() {
     148
     149        def q = new HqlBuilder().query {
     150            select 'book.name' // Create clause and append arg to clause's term list.
     151            select 'type.name' // Method arg is appended to existing clause's term list.
     152            from 'Book as book'
     153            left 'join book.group as group'
     154            left 'left join group.type as type' // 'left join' has to be repeated since left is an existing clause.
     155            where(/book.name like '%Ned%'/) // Slashy string literals have to be protected when calling a method.
     156            where 'group = :group'
     157        }
     158
     159        def expectedQuery = /select book.name, type.name from Book as book left join book.group as group left join group.type as type/
     160        expectedQuery +=  / where book.name like '%Ned%' and group = :group/
     161        assert q.query == expectedQuery
     162
     163        def expectedPrintFormat = /select book.name, ${n}${t}type.name/
     164        expectedPrintFormat += / ${n}from Book as book ${n}left join book.group as group ${n}${t}left join group.type as type/
     165        expectedPrintFormat += / ${n}where book.name like '%Ned%' ${n}${t}and group = :group/
     166        assert q.printFormattedQuery == expectedPrintFormat
     167
     168    } // testMultipleTermsAlternateForm()
     169
     170    void testPlaceHolder() {
     171
     172        def q = new HqlBuilder().query {
     173            select 'distinct book'
     174            from 'Book as book'
     175            where  // Place holder as propertyMissing call.
    99176            order 'by book.name asc'
    100177        }
    101178
    102         // Insert to place holder which is in the middle of query string.
    103         q.where = /book.name like '%Ned%'/  // Slashy string literals don't need protecting when assigning.
     179        // Assign to place holder which is in the middle of the query string.
     180        q.where = /book.name like '%Ned%'/
    104181
    105182        assert q.query == /select distinct book from Book as book where book.name like '%Ned%' order by book.name asc/
    106183
    107     } // testSelectWithPlaceHolder()
    108 
    109     void testSelectWithClauseRemoval() {
     184    } // testPlaceHolder()
     185
     186    void testPlaceHolderAlternateForm() {
     187
     188        def q = new HqlBuilder().query {
     189            select 'distinct book'
     190            from 'Book as book'
     191            where '' // Place holder as method call, tests for nulls when also using append method call bellow.
     192            order 'by book.name asc'
     193        }
     194
     195        // Append to place holder which is in the middle of the query string.
     196        q.where(/book.name like '%Ned%'/)
     197
     198        assert q.query == /select distinct book from Book as book where book.name like '%Ned%' order by book.name asc/
     199
     200    } // testPlaceHolderAlternateForm()
     201
     202    void testClauseRemoval() {
    110203
    111204        def q = new HqlBuilder().query {
    … …  
    116209        }
    117210
    118         q.order = null // Remove clause, since order by makes no sense when selecting a count ;-)
     211        q.order = null // Remove clause.
    119212        assert q.query == /select count(distinct book) from Book as book where book.name like '%Ned%'/
    120213
    121     } // testSelectWithClauseRemoval()
     214    } // testClauseRemoval()
     215
     216    void testClauseRemovalAlternateForm() {
     217
     218        def q = new HqlBuilder().query {
     219            select 'count(distinct book)'
     220            from 'Book as book'
     221            where = /book.name like '%Ned%'/  // Slashy string literals don't need protecting when assigning.
     222            order 'by book.name asc'
     223        }
     224
     225        q.removeClause('order') // Remove clause, alternate form.
     226        assert q.query == /select count(distinct book) from Book as book where book.name like '%Ned%'/
     227
     228    } // testClauseRemovalAlternateForm()
     229
     230    void testLogicalBuilder() {
     231
     232        def q = new HqlBuilder().query {
     233            from 'Book as book'
     234            where "book.name like '%Ned%'"
     235                or "book.onSpecial = true"
     236        }
     237
     238        assert q.query == /from Book as book where book.name like '%Ned%' or book.onSpecial = true/
     239
     240    } // testLogicalBuilder()
     241
     242    void testLogicalBuilderNesting() {
     243
     244        def q = new HqlBuilder().query {
     245            from 'Book as book'
     246            where "book.name like '%Ned%'"
     247                or {
     248                    where "book.onSpecial = true"
     249                    and 'book.inStock = true'
     250                }
     251        }
     252
     253        assert q.query == /from Book as book where book.name like '%Ned%' or ( book.onSpecial = true and book.inStock = true )/
     254
     255    } // testLogicalBuilderNesting()
     256
     257    void testLogicalBuilderNestingLoop() {
     258        def range = 1..2
     259
     260        def q = new HqlBuilder().query {
     261            from 'Book as book'
     262            where 'book.inStock = true'
     263                and {
     264                    range.each {
     265                        or "book.id = $it"
     266                    }
     267                }
     268        }
     269
     270        assert q.query == /from Book as book where book.inStock = true and ( book.id = 1 or book.id = 2 )/
     271
     272    } // testLogicalBuilderNestingLoop()
     273
     274    void testWhereClosure() {
     275
     276        def q = new HqlBuilder().query {
     277            from 'Book as book'
     278            where {
     279                and 'book.id = 1'
     280            }
     281        }
     282
     283        // Only 1 expression so no brackets.
     284        assert q.query == /from Book as book where book.id = 1/
     285
     286    } // testWhereClosure()
     287
     288    void testWhereClosureAlternate() {
     289
     290        def q = new HqlBuilder().query {
     291            from 'Book as book'
     292        }
     293
     294        q.where {
     295            and 'book.id = 1',
     296                    'book.inStock = true'
     297        }
     298
     299        // More than 1 expression so brackets are included.
     300        assert q.query == /from Book as book where ( book.id = 1 and book.inStock = true )/
     301
     302    } // testWhereClosureAlternate()
     303
     304// This is very likely to be a common usage error as it may seem like a natural way to write the where clause.
     305// Is it possible to intercept the String & GString constructors just inside the closure and call where 'book.id = 1'?
     306// Perhaps by internally creating a new Closure and using something like this:
     307// http://groovy.codehaus.org/JN3515-Interception ???
     308// Or is it possible to examine each statment of a closure?
     309    void testWhereClosureWithNewString() {
     310
     311        def q = new HqlBuilder().query {
     312            from 'Book as book'
     313            where {
     314                'book.id = 1' // This statement is missing a method call and hence will simply be excluded.
     315                and 'book.inStock = true'
     316            }
     317        }
     318
     319        // Would be nice if the first case was true.
     320        assertFalse q.query == /from Book as book where ( book.id = 1 and book.inStock = true )/
     321        assert q.query == /from Book as book where book.inStock = true/
     322
     323    } // testSelectWhereClosureWithNewString()
     324
     325    void testWithConditionals() {
     326        def y = true
     327        def n = false
     328
     329        def q = new HqlBuilder().query {
     330            select 'distinct book'
     331            from 'Book as book'
     332            if(y)
     333                where(/book.name like '%Ned%'/)
     334            if(n)
     335                order ''
     336            else
     337                order 'by book.name asc'
     338        }
     339
     340        assert q.query == /select distinct book from Book as book where book.name like '%Ned%' order by book.name asc/
     341
     342    } // testWithConditionals()
     343
     344    void testSelectWithLooping() {
     345        def selections = ['id', 'name', 'description']
     346
     347        def q = new HqlBuilder().query {
     348            for(s in selections) {
     349                select "book.${s}"
     350            }
     351            from 'Book as book'
     352        }
     353
     354        assert q.query == /select book.id, book.name, book.description from Book as book/
     355
     356    } // testSelectWithLooping()
     357
     358    void testWhereWithLooping() {
     359        def range = 1..3
     360
     361        def q = new HqlBuilder().query {
     362            from 'Book as book'
     363            where 'book.inStock = true'
     364                range.each {
     365                    or "book.id = $it"
     366                }
     367        }
     368
     369        assert q.query == /from Book as book where book.inStock = true or book.id = 1 or book.id = 2 or book.id = 3/
     370
     371    } // testWhereWithLooping()
     372
     373    void testWhereDirectlyWithLoops() {
     374        def range = 1..3
     375
     376        def q = new HqlBuilder().query {
     377            from 'Book as book'
     378            where
     379                range.each {
     380                    or "book.id = $it"
     381                }
     382        }
     383
     384        assert q.query == /from Book as book where book.id = 1 or book.id = 2 or book.id = 3/
     385
     386    } // testWhereDirectlyWithLoops()
     387
     388    void testWhereNodeWithLoops() {
     389        def range = 1..3
     390
     391        def q = new HqlBuilder().query {
     392            from 'Book as book'
     393            where {
     394                range.each {
     395                    or "book.id = $it"
     396                }
     397            }
     398        }
     399
     400        assert q.query == /from Book as book where ( book.id = 1 or book.id = 2 or book.id = 3 )/
     401
     402    } // testWhereNodeWithLoops()
     403
     404    void testOrderByMultipleTerms() {
     405
     406        def q = new HqlBuilder().query {
     407            from 'Book as book'
     408            where 'book.id > 100'
     409            order 'by book.name asc',
     410                        'book.id desc'
     411        }
     412
     413        assert q.query == 'from Book as book where book.id > 100 order by book.name asc, book.id desc'
     414
     415        assert q.printFormattedQuery == 'from Book as book \nwhere book.id > 100 \norder by book.name asc, \n\tbook.id desc'
     416
     417    } // testOrderByMultipleTerms()
     418
     419    void testGroupByMultipleTerms() {
     420
     421        def q = new HqlBuilder().query {
     422            from 'Book as book'
     423            where 'book.id > 100'
     424            group 'by book.name asc',
     425                        'book.id desc'
     426        }
     427
     428        assert q.query == 'from Book as book where book.id > 100 group by book.name asc, book.id desc'
     429
     430        assert q.printFormattedQuery == 'from Book as book \nwhere book.id > 100 \ngroup by book.name asc, \n\tbook.id desc'
     431
     432    } // testGroupByMultipleTerms()
     433
     434    void testUpdate() {
     435        def q = new HqlBuilder().query {
     436            update 'Book b'
     437            set 'b.name = :newName',
     438                'b.inStock = true'
     439            where 'b.name = :oldName'
     440        }
     441
     442        assert q.query == 'update Book b set b.name = :newName, b.inStock = true where b.name = :oldName'
     443
     444        assert q.printFormattedQuery == 'update Book b \nset b.name = :newName, \n\tb.inStock = true \nwhere b.name = :oldName'
     445
     446    } // testUpdate()
     447
     448    void testDelete() {
     449        def q = new HqlBuilder().query {
     450            delete 'Book b'
     451            where 'b.name = :oldName'
     452        }
     453
     454        assert q.query == 'delete Book b where b.name = :oldName'
     455
     456        assert q.printFormattedQuery == 'delete Book b \nwhere b.name = :oldName'
     457
     458    } // testDelete()
     459
     460    void testInsertInto() {
     461        def q = new HqlBuilder(debug:true).query {
     462            insert 'into ArchiveBook (id, name)'
     463            select 'b.id',
     464                        'b.name'
     465            from 'Book b'
     466            where 'b.name = :oldName'
     467        }
     468
     469        assert q.query == 'insert into ArchiveBook (id, name) select b.id, b.name from Book b where b.name = :oldName'
     470
     471        assert q.printFormattedQuery == 'insert into ArchiveBook (id, name) \nselect b.id, \n\tb.name \nfrom Book b \nwhere b.name = :oldName'
     472
     473    } // testInsertInto()
    122474
    123475} // end class
Note: See TracChangeset for help on using the changeset viewer.