Tutorial: Spring Framework DB1

(typo fix)
Line 261: Line 261:
[http://en.wikipedia.org/wiki/Singleton_pattern Singleton design pattern].  This way, we can ensure that there is only
[http://en.wikipedia.org/wiki/Singleton_pattern Singleton design pattern].  This way, we can ensure that there is only
every a single object representing the inventory that all the Petstore classes
ever a single object representing the inventory that all the Petstore classes
share.  We do this in Java
share.  We do this in Java
by ensuring that there is at least one constructor and that
by ensuring that there is at least one constructor and that

Revision as of 14:22, 9 June 2008


Tutorial: Database Connectivity 1


Project configuration for this tutorial consists of creating two projects from existing sources in your Eclipse workspace, creating a directory of external library files, then linking the latter into the former. There is an additional step here that we have not performed: linking against external library files. Each of these tutorial projects relies on the MySQL library for connecting to MySQL databases. Rather than having a copy of that library in each project, we will save a copy of the relevant JAR file outside the Eclipse workspace, and then let all the projects link against that single copy of the library.

  1. Download the project sources: Media:Db-tut1.zip and unzip the file.
  2. Copy the two projects (tutorial-jdbc and tutorial-jdbc-spring) into your Eclipse workspace.
  3. Download the library files: Media:DB-tut-libs.zip and unzip the file.
  4. Move the libs directory to the same directory that contains your Eclipse workspace. Do not put the libs directory into your workspace!
  5. Start Eclipse and create the two new projects:
    1. Select "New Project...," the select "Java->Java Project" from the wizard.
    2. Enter tutorial-jdbc for the project name. At this point, Eclipse should indicate that there is already a project in this location and it will attempt to guess appropriate project settings. Click on "Next."
    3. Make sure that the next page shows src with a Source folder icon, not a regular folder icon. If it does not, stop and ask for help.
    4. Select the "Libraries" tab. Click on "Add External JARS...", navigate to the libs folder you created in step 4 and select mysql-connector-java-5.1.6-bin.jar.
    5. Click on "Finish."
    6. Repeat the process for tutorial-jdbc-spring; in Step 4, select mysql-connector-java-5.1.6-bin.jar, spring.jar, and commons-logging.jar.
  6. Create the database for this project.
    1. Point your browser to the Trinity database server and log in with your MySQL user name and password.
    2. Create a new database named username_petstore where username is your user name.
    3. Select the "Import" tab. Under "File to import" browse to the petstore.sql file in the Project Sources zip file from Step 1.
    4. Click on "Go." This will create and populate two tables in the database.
  7. Open a browser window and point it to the Java API documentation.

Once you are finished, the directory structure for the directory containing your Eclipse workspace should look as follows:

    [other project directories]

Java Database Connectivity (JDBC) and pure Java

Java provides a framework called JDBC for interacting with relational databases. JDBC provides facilities for connecting to, querying, and modifying a database. In the first part of this tutorial we will look at a simple plain Java application that uses some of this functionality.

The JDBC interfaces and classes are in the packages java.sql and javax.sql. The only types we will work with in this first part of the tutorial are DriverManager (for establishing a connection), Connection (representing a connection to a database), Statement (for executing queries) and ResultSet (for analyzing the result of a query).

Although queries to a database will typically be formulated in SQL and are independent of the database vendor, the low-level communication is dependent on the vendor. That is, if you are using, for example, an Oracle database, then the protocol by which your Java app sends SQL queries to the database will be different that if your database were MySQL. Thus each vendor must supply classes that manage the low-level details of the connection. The java.sql.DriverManager interface is a framework that hides those details from your Java application. First let's see the code for establishing a connection to the MySQL database called petstore on localhost with (MySQL) username pstore and password pstore_pw (from tutorial-jdbc/src/petstore/CreatePetStore.java; you will need to change this code to point to your own database):

    // Let JDBC know about the Driver provided by MySQL's Connector/J.
    try {
    } catch (ClassNotFoundException e) {

        // Handle errors getting the connector class.

    try {
    // Establish the connection to the database.
    Connection conn = DriverManager.getConnection(
                                    "pstore", "pstore_pw") ;
    } catch (SQLException e) {
        // Handle connection errors.

In this case, we are using the class com.mysql.jdbc.Driver to connect the database; this class is written by the MySQL developers and is provided in the mysql-connector-java-XXX-bin.jar library (where XXX indicates version number). What may be a bit surprising is that we never instantiate this class with new directly! So what is going on?

The point is that when writing your Java application, you may not know what vendor databases will be used on the back-end when your application is deployed (although that is not the case in this simple example, when we are assuming that we are connecting to a MySQL database). You don't want to supply every possible vendor-supplied driver with your application, since this would be useless code-bloat. Furthermore, as a developer you may not have access to all drivers; some may be proprietary, or you may want your application to still be usable even if your client's database vendor does an update and releases new driver code. What we do instead is to identify classes (by the name of the class) that can be used to connect to databases. Since the identification is by a String name, this can be specified as part of the run-time properties of the application and hence set when the application is deployed. To identify such classes, we force the JVM to load the class definition; classes that can manage database connections register with the JDBC framework, essentially saying "I can handle connections to these kinds of databases:...". More precisely, when we load the class definition into the JVM with Class.forName(), part of the static initialization process for the class is to invoke DriverManager.registerDriver() to register itself. In fact, you can even avoid programmatically identifying the classes by instead specifying the jdbc.drivers system property---see the DriverManager API documentation for details.

When we actually want a connection, we get one from DriverManager.getConnection(), specifying the database, user name, and password (the latter two are optional). The database specification in our example starts with jdbc:mysql, indicating that this is to be a JDBC-managed connection to a MySQL database. The DriverManager class then determines whether any of the driver classes that have registered with it can handle this combination. If so, it will return a Connection object representing the connection, and otherwise it fails. One of the advantages to this approach is that if the application needs to connect to several different databases from possibly different vendors, it does not need to know anything about which drivers should be used to connect to which databases. The drivers register themselves with DriverManager, and that class is responsible for looking at the database specification and determining the correct driver to use.

Once we have a connection, executing queries and commands typically consists of getting a Statement object stmnt from the Connection and executing an SQL query on stmnt. This returns a ResultSet, which is a representation of the table created by the query. A ResultSet consists of "rows," and you can iterate through the rows and extract information from each row using the ResultSet methods. Here is an example of getting all the rows of the inventory table from the petstore database and creating InventoryItem objects corresponding to each one (from tutorial-jdbc/src/Inventory.java):

public class Inventory {


    private Inventory(Connection conn) {


        // Typical query to an SQL database:  make a statement,
        // execute the query, get the results (rows) as a
        // ResultSet, then iterate through the ResultSet.
        Statement stmnt = conn.createStatement() ;
        ResultSet rs = stmnt.executeQuery("select * from inventory") ;

        // You would think there would be something like rs.isEmpty().
        if (!rs.isBeforeFirst()) return ;

        // Getting field values is more efficient if the column is
        // specified by an int, but that means knowing a lot about
        // the database structure in the code.
        while (!rs.isLast()) {
            rs.next() ;
            items.add(new InventoryItem(conn, rs.getString("Description"),
                    rs.getInt("Quantity"), rs.getFloat("Price"),
                    rs.getInt("Supplier"))) ;


There are more examples in InventoryItem.java (see especially setQuantity for an example of changing the database) and Supplier.java.

A note about Inventory and Supplier: each implements the Singleton design pattern. This way, we can ensure that there is only ever a single object representing the inventory that all the Petstore classes share. We do this in Java by ensuring that there is at least one constructor and that all constructors are declared private. The only way to get an object is through a static method, which either creates, saves, and returns the object if it has not been created yet, or just returns the saved object if it has.

While all of this code works, it is really sort of ugly. A case in point is how we have to pass around the Connection object all the time so that every class/object can talk to the database. Of course, we could also define a PetstoreDB class that manages the connection and provides static methods for accessing it. This would roughly correspond to implementing the connection with something like the Singleton design pattern. And this is one of the things that Spring does for us already! In fact, Spring also makes it easy to manage JDBC connections (as beans) an implement the Data Access Object design pattern in which the fact that an object is tied to a database is hidden from clients (as it should be).

See also

Database connectivity and Spring

A simple Spring application

The application referred to in this sub-tutorial is the conn.petstore.CreatePetStore application in the tutorial-jdbc-spring project. You can run this code by right-clicking on build.xml, selecting "Run As->Ant build...," and ensuring that the "run" target is selected and the "rundao" is not selected.

An alternative to DriverManager is DataSource. We don't need to know much about the difference, but this is how Spring does things, so it is worth knowing about. In a Spring application, we specify the data source as a bean in the application context file, as in the following XML (tutorial-jdbc-spring/metadata/petsore.xml; ignore the remaining bean definitions in that file for now):

<bean id="dataSource"
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost/petstore"/>
    <property name="username" value="pstore"/>
    <property name="password" value="pstore_pw"/>

This is completely analogous to the programmatic specification that we did in the previous example. The only change in the Java source code is in getting the Connection object (tutorial-jdbc-spring/src/conn/petstore/CreatePetStore.java):

// Get the application context.
ApplicationContext ctx =
    new ClassPathXmlApplicationContext("petstore.xml");

// Get the datasource as a bean.
DataSource ds = (DataSource) ctx.getBean("dataSource");

// Get the connection.
Connection conn = DataSourceUtils.getConnection(ds) ;

Other than this change, the code is the same as before.

A more interesting Spring application

The application discussed in this sub-tutorial is dao.petstore.CreatePetStore in the tutorial-jdbc-spring project.

As we saw above, Inventory and Supplier implement Singleton patterns. And we would also like to hide database access as much as possible. Both can be made a bit easier using Spring. The first step is to add bean definitions for the inventory object, inventory items, and suppliers (tutorial-jdbc-spring/metadata/petstore.xml):

<bean id="inventoryDAO" class="dao.petstore.Inventory" >
  <property name="dataSource" ref="dataSource" />

<bean id="inventoryItemDAO" class="dao.petstore.InventoryItem"
  <property name="dataSource" ref="dataSource" />

<bean id="supplierDAO" class="dao.petstore.Supplier">
  <property name="dataSource" ref="dataSource" />

Notice the singleton="false" attribute for inventoryItemDAO; we do not want a single shared instance of InventoryItem, and this is how we instruct Spring to create one instance per bean request. In each bean definition, we specify that the dataSource is defined by the dataSource bean. When any one of these beans is created, Spring will "inject" the dataSource bean into the instantiated object by invoking setDataSource on that object just after it is created; the argument will be the DataSource object corresponding to the dataSource bean. An advantage to this is that only objects that need to understand the database have any data that tells them about it. For example, look at dao/petstore/CreatePetStore.java; there is no database code whatsoever in the main method. This is the idea behind the Data Access Object (DAO) design pattern. CreatePetStore does not need to know that the inventory is backed by a database; it just needs an Inventory instance so that it can print out the inventory and modify it.

Likewise, look at dao/petstore/Inventory.java. As usual, instead of instantiating an Inventory object, we get it by its bean reference. When the bean is created, Spring will invoke setDataSource with the appropriate DataSource as defined in the application context file. No longer do we have to "carry around" a Connection object and keep passing it around to every class that might need to talk to the database!

For actually sending queries and the like, we will also use the Spring-supplied classes. The relevant classes are JdbcTemplate (similar to Statement) and SqlRowSet (similar to ResultSet). You will notice in the various classes that we do not save the DataSource that we are configured with. Instead, we create a JdbcTemplate based on the DataSource and save that as our instance data. We can then submit SQL queries analogously to how we did with Statement and ResultSet; see the fillInventory method of tutorial-jdbc-spring/src/dao/petstore/Inventory.java for an example.


Add an "add item" function to the Inventory class that adds an item to the inventory.

Still more to come...

We still really aren't using all the power of Spring. Here's a couple more things we need to know:

  • Some clean-up: should every class be implemented as a bean?
  • Object-relational mapping and Hibernate.