[1/4] incubator-groovy git commit: groovy-sql doco: initial JDBC metadata handling doco

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[1/4] incubator-groovy git commit: groovy-sql doco: initial JDBC metadata handling doco

paulk
Repository: incubator-groovy
Updated Branches:
  refs/heads/groovy_2_4_X [created] 004e03293


groovy-sql doco: initial JDBC 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/95760b21
Tree: http://git-wip-us.apache.org/repos/asf/incubator-groovy/tree/95760b21
Diff: http://git-wip-us.apache.org/repos/asf/incubator-groovy/diff/95760b21

Branch: refs/heads/groovy_2_4_X
Commit: 95760b2197960d3f240ece94537f3bbf256f81ce
Parents: 4c2b43e
Author: Paul King <[hidden email]>
Authored: Fri Apr 17 12:59:32 2015 +1000
Committer: Paul King <[hidden email]>
Committed: Sat Apr 18 09:09:31 2015 +1000

----------------------------------------------------------------------
 .../groovy-sql/src/spec/doc/sql-userguide.adoc  | 10 ++++++
 .../groovy-sql/src/spec/test/SqlTest.groovy     | 33 ++++++++++++++++++++
 2 files changed, 43 insertions(+)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/95760b21/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 2c0202d..f679e6a 100644
--- a/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc
+++ b/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc
@@ -236,6 +236,16 @@ 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:
+
+[source,groovy]
+.Using row metadata
+----
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_basic_metadata,indent=0]
+----
+
+Advanced options for metadata handling using closures are also possible.
+
 (TBD)
 
 === Named and named-ordinal parameters

http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/95760b21/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 3e09362..bfa5daf 100644
--- a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
+++ b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
@@ -289,6 +289,39 @@ class SqlTest extends GroovyTestCase {
         '''
     }
 
+    void testMetadata() {
+        assertScript '''
+            import groovy.sql.Sql
+
+            def url = 'jdbc:hsqldb:mem:yourDB'
+            def user = 'sa'
+            def password = ''
+            def driver = 'org.hsqldb.jdbcDriver'
+            Sql.withInstance(url, user, password, driver) { sql ->
+              sql.execute """
+              DROP TABLE Author IF EXISTS
+              """
+              sql.execute """
+              CREATE TABLE Author (
+                id          INTEGER GENERATED BY DEFAULT AS IDENTITY,
+                firstname   VARCHAR(64),
+                lastname    VARCHAR(64)
+              )
+              """
+              sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')"
+
+              // tag::sql_basic_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']
+              }
+              // end::sql_basic_metadata[]
+            }
+        '''
+    }
+
     void testDeletingRows() {
         assertScript '''
         import groovy.sql.Sql


Reply | Threaded
Open this post in threaded view
|

[2/4] incubator-groovy git commit: groovy-sql doco: other metadata handling doco

paulk
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 <[hidden email]>
Authored: Fri Apr 17 21:45:31 2015 +1000
Committer: Paul King <[hidden email]>
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[]
             }
         '''
     }


Reply | Threaded
Open this post in threaded view
|

[3/4] incubator-groovy git commit: groovy-sql: update test dependency (no longer need jdk5 artifact)

paulk
In reply to this post by paulk
groovy-sql: update test dependency (no longer need jdk5 artifact)


Project: http://git-wip-us.apache.org/repos/asf/incubator-groovy/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-groovy/commit/d6cd40c3
Tree: http://git-wip-us.apache.org/repos/asf/incubator-groovy/tree/d6cd40c3
Diff: http://git-wip-us.apache.org/repos/asf/incubator-groovy/diff/d6cd40c3

Branch: refs/heads/groovy_2_4_X
Commit: d6cd40c3fb677bd4f5c5e211efddf4be8f92658c
Parents: 65df5e2
Author: Paul King <[hidden email]>
Authored: Sat Apr 18 08:55:56 2015 +1000
Committer: Paul King <[hidden email]>
Committed: Sat Apr 18 09:09:32 2015 +1000

----------------------------------------------------------------------
 subprojects/groovy-sql/build.gradle                 | 6 ++++--
 subprojects/groovy-sql/src/spec/test/SqlTest.groovy | 2 +-
 2 files changed, 5 insertions(+), 3 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/d6cd40c3/subprojects/groovy-sql/build.gradle
----------------------------------------------------------------------
diff --git a/subprojects/groovy-sql/build.gradle b/subprojects/groovy-sql/build.gradle
index 563a5d1..4e82dc4 100644
--- a/subprojects/groovy-sql/build.gradle
+++ b/subprojects/groovy-sql/build.gradle
@@ -18,9 +18,11 @@
  */
 dependencies {
     compile rootProject
-//    testCompile 'hsqldb:hsqldb:1.8.0.10'
-    testCompile group: 'org.hsqldb', name: 'hsqldb', version: '2.3.2', classifier: 'jdk5'
+    testCompile group: 'org.hsqldb', name: 'hsqldb', version: '2.3.2'
+// uncomment to test with other databases (requires changes elsewhere too)
+//    testCompile group: 'org.hsqldb', name: 'hsqldb', version: '2.3.2', classifier: 'jdk5'
 //    testCompile 'com.h2database:h2:1.3.164'
+//    testCompile 'hsqldb:hsqldb:1.8.0.10'
     testCompile project(':groovy-test')
 }
 

http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/d6cd40c3/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 eb249ce..40e237f 100644
--- a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
+++ b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
@@ -336,7 +336,7 @@ class SqlTest extends GroovyTestCase {
               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 ['JDBCMajorVersion', 'JDBCMinorVersion'].collect{ md[it] } == [4, 0]
               assert md.stringFunctions.tokenize(',').contains('CONCAT')
               def rs = md.getTables(null, null, 'AUTH%', null)
               assert rs.next()


Reply | Threaded
Open this post in threaded view
|

[4/4] incubator-groovy git commit: groovy-sql: update test dependency (no longer need jdk5 artifact)

paulk
In reply to this post by paulk
groovy-sql: update test dependency (no longer need jdk5 artifact)


Project: http://git-wip-us.apache.org/repos/asf/incubator-groovy/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-groovy/commit/004e0329
Tree: http://git-wip-us.apache.org/repos/asf/incubator-groovy/tree/004e0329
Diff: http://git-wip-us.apache.org/repos/asf/incubator-groovy/diff/004e0329

Branch: refs/heads/groovy_2_4_X
Commit: 004e0329346afe35d7885773f384b3da39d43170
Parents: d6cd40c
Author: Paul King <[hidden email]>
Authored: Sat Apr 18 08:56:23 2015 +1000
Committer: Paul King <[hidden email]>
Committed: Sat Apr 18 09:09:33 2015 +1000

----------------------------------------------------------------------
 subprojects/groovy-sql/src/spec/test/SqlTest.groovy | 2 --
 1 file changed, 2 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/004e0329/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 40e237f..22d087f 100644
--- a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
+++ b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
@@ -330,8 +330,6 @@ class SqlTest extends GroovyTestCase {
               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'