groovy-sql doco: other metadata handling doco
Project: http://git-wip-us.apache.org/repos/asf/incubator-groovy/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-groovy/commit/65df5e2e Tree: http://git-wip-us.apache.org/repos/asf/incubator-groovy/tree/65df5e2e Diff: http://git-wip-us.apache.org/repos/asf/incubator-groovy/diff/65df5e2e Branch: refs/heads/groovy_2_4_X Commit: 65df5e2e82f788a7d4ce1d95c347a72dae69e897 Parents: 95760b2 Author: Paul King <pa...@asert.com.au> Authored: Fri Apr 17 21:45:31 2015 +1000 Committer: Paul King <pa...@asert.com.au> Committed: Sat Apr 18 09:09:32 2015 +1000 ---------------------------------------------------------------------- .../groovy-sql/src/spec/doc/sql-userguide.adoc | 35 ++++++++++++++++--- .../groovy-sql/src/spec/test/SqlTest.groovy | 36 ++++++++++++++++---- 2 files changed, 61 insertions(+), 10 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/65df5e2e/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc ---------------------------------------------------------------------- diff --git a/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc b/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc index f679e6a..5d06fd8 100644 --- a/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc +++ b/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc @@ -236,17 +236,44 @@ include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[ta === Fetching metadata -JDBC metadata can be retrieved in numerous ways. Perhaps the most basic approach is to extract the metadata from any row as shown here: +JDBC metadata can be retrieved in numerous ways. Perhaps the most basic approach is to extract the +metadata from any row as shown in the following example which examines the tablename, column names and column type names: [source,groovy] .Using row metadata ---- -include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_basic_metadata,indent=0] +include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_basic_rs_metadata,indent=0] ---- -Advanced options for metadata handling using closures are also possible. +And another slight variant to the previous example, this time also looking at the column label: -(TBD) +[source,groovy] +.Also using row metadata +---- +include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_basic_rs_metadata2,indent=0] +---- + +Accessing metadata is quite common, so Groovy also provides variants to many of its methods that let you +supply a closure that will be called once with the row metadata in addition to the normal row closure +which is called for each row. The following example illustrates the two closure variant for `eachRow`: + +[source,groovy] +.Using row and metadata closures +---- +include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_basic_rs_metadata3,indent=0] +---- + +Note that our SQL query will only return one row, so we could have equally used `firstRow` for the previous example. + +Finally, JDBC also provides metadata per connection (not just for rows). You can also access such metadata from Groovy as shown in this example: + +[source,groovy] +.Using connection metadata +---- +include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_basic_table_metadata,indent=0] +---- + +Consult the JavaDoc for your driver to find out what metadata information is available for you to access. === Named and named-ordinal parameters http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/65df5e2e/subprojects/groovy-sql/src/spec/test/SqlTest.groovy ---------------------------------------------------------------------- diff --git a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy index bfa5daf..eb249ce 100644 --- a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy +++ b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy @@ -310,14 +310,38 @@ class SqlTest extends GroovyTestCase { """ sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')" - // tag::sql_basic_metadata[] - sql.eachRow("SELECT * FROM Author where firstname = 'Dierk'") { row -> + // tag::sql_basic_rs_metadata[] + sql.eachRow("SELECT * FROM Author WHERE firstname = 'Dierk'") { row -> def md = row.getMetaData() - assert md.getTableName(1).toLowerCase() == 'author' - assert (1..md.columnCount).collect{ md.getColumnTypeName(it).toLowerCase() } == ['integer', 'varchar', 'varchar'] - assert (1..md.columnCount).collect{ md.getColumnLabel(it).toLowerCase() } == ['id', 'firstname', 'lastname'] + assert md.getTableName(1) == 'AUTHOR' + assert (1..md.columnCount).collect{ md.getColumnName(it) } == ['ID', 'FIRSTNAME', 'LASTNAME'] + assert (1..md.columnCount).collect{ md.getColumnTypeName(it) } == ['INTEGER', 'VARCHAR', 'VARCHAR'] } - // end::sql_basic_metadata[] + // end::sql_basic_rs_metadata[] + // tag::sql_basic_rs_metadata2[] + sql.eachRow("SELECT firstname AS first FROM Author WHERE firstname = 'Dierk'") { row -> + def md = row.getMetaData() + assert md.getColumnName(1) == 'FIRSTNAME' + assert md.getColumnLabel(1) == 'FIRST' + } + // end::sql_basic_rs_metadata2[] + // tag::sql_basic_rs_metadata3[] + def metaClosure = { meta -> assert meta.getColumnName(1) == 'FIRSTNAME' } + def rowClosure = { row -> assert row.FIRSTNAME == 'Dierk' } + sql.eachRow("SELECT firstname FROM Author WHERE firstname = 'Dierk'", metaClosure, rowClosure) + // end::sql_basic_rs_metadata3[] + // build currently uses the jdk5 classifier version of hsqldb.jar which is JDBC 3 + // TODO remove this classifier since we now require jdk6+ + // tag::sql_basic_table_metadata[] + def md = sql.connection.metaData + assert md.driverName == 'HSQL Database Engine Driver' + assert md.databaseProductVersion == '2.3.2' + assert ['JDBCMajorVersion', 'JDBCMinorVersion'].collect{ md[it] } == [3, 0] + assert md.stringFunctions.tokenize(',').contains('CONCAT') + def rs = md.getTables(null, null, 'AUTH%', null) + assert rs.next() + assert rs.getString('TABLE_NAME') == 'AUTHOR' + // end::sql_basic_table_metadata[] } ''' }
