alvinalexander.com | career | drupal | java | mac | mysql | perl | scala | uml | unix  

Groovy example source code file (SqlTest.groovy)

This example Groovy source code file (SqlTest.groovy) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" TM.

Java - Groovy tags/keywords

food, food, found, found, groovytestcase, hello, james, jdbc, mcwhirter, person, person, sql, sql, sqlsubclass, sqlsubclass, strachan

The Groovy SqlTest.groovy source code

/*
 * Copyright 2003-2010 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package groovy.sql

import java.sql.Connection

/**
 * This is more of a sample program than a unit test and is here as an easy
 * to read demo of GDO. The actual full unit test case is in SqlCompleteTest
 */
class SqlTest extends GroovyTestCase {

    private sql

    void setUp() {
        sql = createSql()
    }

    void testSqlQuery() {
        sql.eachRow("select * from PERSON") { println("Hello ${it.firstname} ${it.lastname}") }
    }

    void testQueryUsingColumnIndex() {
        def answer = null
        sql.eachRow("select count(*) from PERSON") { answer = it[0] }
        println "Found the count of ${answer}"
        assert answer == 3
    }

    void testQueryUsingNegativeColumnIndex() {
        def first = null
        def last = null
        sql.eachRow("select firstname, lastname from PERSON where firstname='James'") { row ->
            first = row[-2]
            last = row[-1]
        }
        println "Found name ${first} ${last}"
        assert first == "James"
        assert last == "Strachan"
    }

    void testSqlQueryWithWhereClause() {
        def foo = "drink"
        sql.eachRow("select * from FOOD where type=${foo}") { println("Drink ${it.name}") }
    }

    void testEachRowWithWhereClauseWith2Arguments() {
        def foo = "cheese"
        def bar = "edam"
        sql.eachRow("select * from FOOD where type=${foo} and name != ${bar}") { println("Found cheese ${it.name}") }
    }

    void testFirstRowWithWhereClauseWith2Arguments() {
        def foo = "cheese"
        def bar = "edam"
        def result = sql.firstRow("select * from FOOD where type=${foo} and name != ${bar}")
        assert result.name == 'brie'
    }

    void testSqlQueryWithIncorrectlyQuotedDynamicExpressions() {
        def foo = "cheese"
        def bar = "edam"
        sql.eachRow("select * from FOOD where type='${foo}' and name != '${bar}'") { println("Found cheese ${it.name}") }
    }

    void testDataSet() {
        def people = sql.dataSet("PERSON")
        people.each { println("Hey ${it.firstname}") }
    }

    void testDataSetWithClosurePredicate() {
        def food = sql.dataSet("FOOD")
        food.findAll { it.type == "cheese" }.each { println("Cheese ${it.name}") }
    }

    void testExecuteUpdate() {
        def foo = 'food-drink'
        def bar = 'guinness'
        def nRows = sql.executeUpdate("update FOOD set type=? where name=?", [foo, bar]);
        if (nRows == 0) {
            sql.executeUpdate("insert into FOOD (type,name) values (${foo},${bar})");
        }
    }

    void testExecuteInsert() {
        def foo = 'food-drink'
        def bar = 'guiness'
        if (sql.dataSource.connection.metaData.supportsGetGeneratedKeys()) {
            def keys = sql.executeInsert('insert into FOOD (type,name) values (?,?)', [foo, bar])
            assert 1 == keys.size()
        } else {
            def count = sql.executeUpdate('insert into FOOD (type,name) values (?,?)', [foo, bar])
            assert 1 == count
        }
    }

    void testMetaData() {
        sql.eachRow('select * from PERSON') {
            assert it[0] != null
            assert it.getMetaData() != null
        }
    }

    void testSubClass() {
        def sub = new SqlSubclass(sql)
        def res = null
        def data = []
        sql.eachRow('select firstname from PERSON') {
            data << it.firstname
        }
        try {
            res = sub.rowsCursor('select * from PERSON')
            while (res.next()) {
                assert data.remove(res.firstname)
            }
            assert data.isEmpty()
        } finally {
            if (res)
                res.close()
        }
        res = sub.rows('select * from PERSON') { metaData ->
            assert sub.connection && !sub.connection.isClosed()
            data = (1..metaData.columnCount).collect {
                metaData.getColumnName(it).toLowerCase()
            }
        }
        assert data.size == 2 && !(data - ['firstname', 'lastname'])
    }

    private createSql() {
        def ds = new org.hsqldb.jdbc.jdbcDataSource()
        ds.database = "jdbc:hsqldb:mem:foo" + getMethodName()
        ds.user = 'sa'
        ds.password = ''
        def sql = new Sql(ds)

        sql.execute("create table PERSON ( firstname varchar, lastname varchar )")
        sql.execute("create table FOOD ( type varchar, name varchar)")

        // now let's populate the datasets
        def people = sql.dataSet("PERSON")
        people.add(firstname: "James", lastname: "Strachan")
        people.add(firstname: "Bob", lastname: "Mcwhirter")
        people.add(firstname: "Sam", lastname: "Pullara")

        def food = sql.dataSet("FOOD")
        food.add(type: "cheese", name: "edam")
        food.add(type: "cheese", name: "brie")
        food.add(type: "cheese", name: "cheddar")
        food.add(type: "drink", name: "beer")
        food.add(type: "drink", name: "coffee")

        return sql
    }
}

class SqlSubclass extends Sql {
    def connection

    SqlSubclass(Sql base) {
        super(base)
    }

    def rowsCursor(String sql) {
        def rs = executeQuery(sql)
        return new GroovyResultSetProxy(rs).getImpl()
    }

    @Override
    void setInternalConnection(Connection conn) {
        connection = conn
    }
}

Other Groovy examples (source code examples)

Here is a short list of links related to this Groovy SqlTest.groovy source code file:

... this post is sponsored by my books ...

#1 New Release!

FP Best Seller

 

new blog posts

 

Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.

A percentage of advertising revenue from
pages under the /java/jwarehouse URI on this website is
paid back to open source projects.