Getting started with database access
zero.data provides an easy-to-use data access interface to get to your
data. zero.data is an API that is flexible enough to handle complex
situations without making simple tasks cumbersome.
In the spirit of making the common things easy and the hard things possible, the
zero.data.Manager and zero.data.groovy.Manager interfaces provide an API
that solves many common use cases while still allowing for very flexible,
pluggable customizations.
Supported Database Vendors and Versions
The zero.data module is a thin layer on top of the IBM® pureQuery
Runtime and provides convenience APIs for Java, Groovy, and PHP. However,
because the zero.data modules contains the pureQuery Runtime, you can also
use the pureQuery Runtime APIs directly as shown in its documentation.
The pureQuery Runtime is tested against, and therefore zero.data supports,
the following databases:
Adding zero.data to your application
To begin using zero.data you first add it as a dependency in your
application. IBM® WebSphere® sMash's dependency manager will find the
necessary jars and add them to your application's classpath.
After creating your application, modify the config/ivy.xml file in your
application by adding the following line in the dependencies section:
<dependency org="zero" name="zero.data" rev="[1.0.0.0, 2.0.0.0[" />
After adding the dependency, issue the resolve command using the CLI. For instance, from the command line:
zero resolve
Getting database drivers
zero.data is simply a layer on top of Java's standard JDBC interfaces. Thus,
in order to use zero.data, you must first obtain the appropriate driver for
the database you wish to connect.
The distribution of drivers from the database vendors are quite different. The
open source database vendors make their drivers available in public repositories
that the dependency manager can find. Other vendors do not. For more
details, consult the list of driver distribution and configuration information
for the supported zero.data databases.
Configure the connection
In order to connect to your database, you need to provide some basic connection
properties to zero.data in your application's config/zero.config file.
The following example shows basic configuration for an embedded Derby database:
/config/db/sampleDb = {
"class" : "org.apache.derby.jdbc.EmbeddedDataSource",
"databaseName" : "db/review"
}
Other databases require several more properties, the server name, port, user, and password, for instance, are common. You can find templates for other databases in the configuration documentations.
Obtaining a Manager
The configuration above follows a simple zero.data convention in the
config zone of the Global Context. The pattern /config/db/{dbKey}
defines a "dbKey" that is used to obtain an instance of a Manager as
shown in the following example:
// Groovy code:
import zero.data.groovy.Manager
...
def data = zero.data.Manager.create('sampleDb')
def results = data.queryList('SELECT * FROM table')
In this example, sampleDb is the "dbKey" and corresponds to the
configuration example from above. zero.data will match the "dbKey" and use
the configuration properties from the Global Context.
Use your own DataSource or Connection
If your application has database connection needs that are not supported by the
Manager.create(String) convenience method, you can obtain a instance of
Manager by passing either a javax.sql.DataSource or
java.sql.Connection instance:
def connection = // obtained through your application code def dataConnection = new zero.data.Manager(connection) def dataSource = // obtained through your application code def dataDataSource = new zero.data.Manager(dataSource)
If you pass in a Connection, your application code is responsible for
opening and closing the database connection. The Manager will not close the
connection.
If you pass in a DataSource the Manager will call getConnection()
on the DataSource instance before and close() on the connection after
every method called on the Manager instance that requires a connection.
Most connection needs are fulfilled between these three ways of providing a
Connection to the Manager. For instance, you can implement your own
DataSource with specific behavior that wraps a DataSource provided by
your database vendor.
Executing queries
Once you have an instance of a Manager you can call on a variety of data
access methods that fit your application's needs. Raw JDBC access uses the
ResultSet interface to access the results of database queries. zero.data
APIs allow for wrapping database results into more usable and friendly data
structures.
Iterate over results using eachRow
The zero.data.groovy.Manager.eachRow() function provides a quick and easy
way to perform logic against each row. For example, the following code creates
XML quite easily:
def data = zero.data.groovy.Manager.create('mydb')
def xml = new groovy.xml.MarkupBuilder(request.writer)
xml.records() {
data.eachRow('SELECT * FROM table') { row ->
item(id: row['id'], name: row.name)
}
}
Given a few rows in the query results, this simple example might create the following XML:
<records>
<item id="10" name="Robert" />
<item id="11" name="Bill" />
<item id="15" name="Aaron" />
</records>
Next steps
The above example is only a simple Groovy example. Your next step is to learn
the options zero.data provides to access your data.
| [*] | SQL Server is either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. |
| [**] | MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries. |
| [***] | Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. |
