Spring Framework DB1

Tutorial: Database Connectivity 1

Setup

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.

  Download the project sources: [[Media:Db-tut1.zip]] and unzip the file.   Copy the two projects (tutorial-jdbc and tutorial-jdbc-spring) into your Eclipse workspace.   Download the library files: [[Media:DB-tut-libs.zip]] and unzip the file.   Move the libs directory to the same directory that contains your Eclipse workspace. Do not put the libs directory into your workspace! </li>  Start Eclipse and create the two new projects: <ol>  Select "New Project...," the select "Java->Java Project" from the wizard. </li>  Enter tutorial-jdbc</tt> 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." </li>  Make sure that the next page shows src</tt> with a Source folder icon, not a regular folder icon. If it does not, stop and ask for help. </li>  Select the "Libraries" tab. Click on "Add External JARS...", navigate to the libs</tt> folder you created in step 4 and select mysql-connector-java-5.1.6-bin.jar</tt>. </li>  Click on "Finish." </li>  Repeat the process for tutorial-jdbc-spring</tt>; in Step 4, select mysql-connector-java-5.1.6-bin.jar</tt>, spring.jar</tt>, and commons-logging.jar</tt>. </li> </ol> </li>  Create the database for this project. <ol> <li> Point your browser to the Trinity database server and log in with your MySQL user name and password. </li> <li> Create a new database named <tt>username_petstore</tt> where <tt>username</tt> is your user name. </li> <li> Select the "Import" tab. Under "File to import" browse to the <tt>petstore.sql</tt> file in the Project Sources zip file from Step 1. </li> <li> Click on "Go." This will create and populate two tables in the database. </li> </ol> </li> <li> Open a browser window and point it to the Java API documentation. </li> </ol> Once you are finished, the directory structure for the directory containing your Eclipse workspace should look as follows: workspace/ tutorial-jdbc/ tutorial-jdbc-spring/ [other project directories] lib/ commons-logging.jar mysql-connector-java-5.1.6-bin.jar spring.jar

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 <tt>java.sql</tt> and <tt>javax.sql</tt>. The only types we will work with in this first part of the tutorial are <tt>DriverManager</tt> (for establishing a connection), <tt>Connection</tt> (representing a connection to a database), <tt>Statement</tt> (for executing queries) and <tt>ResultSet</tt> (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 <tt>java.sql.DriverManager</tt> 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 <tt>petstore</tt> on <tt>localhost</tt> with (MySQL) username <tt>pstore</tt> and password <tt>pstore_pw</tt> (from <tt>tutorial-jdbc/src/petstore/CreatePetStore.java</tt>; 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 { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) {

// Handle errors getting the connector class. e.printStackTrace; System.exit(1); }

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

In this case, we are using the class <tt>com.mysql.jdbc.Driver</tt> to connect the database; this class is written by the MySQL developers and is provided in the <tt>mysql-connector-java-XXX-bin.jar</tt> library (where <tt>XXX</tt> indicates version number). What may be a bit surprising is that we never instantiate this class with <tt>new</tt> 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 <tt>String</tt> 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 <tt>Class.forName</tt>, part of the static initialization process for the class is to invoke <tt>DriverManager.registerDriver</tt> to register itself. In fact, you can even avoid programmatically identifying the classes by instead specifying the <tt>jdbc.drivers</tt> system property---see the <tt>DriverManager</tt> API documentation for details.

When we actually want a connection, we get one from <tt>DriverManager.getConnection</tt>, specifying the database, user name, and password (the latter two are optional). The database specification in our example starts with <tt>jdbc:mysql</tt>, indicating that this is to be a JDBC-managed connection to a MySQL database. The <tt>DriverManager</tt> class then determines whether any of the driver classes that have registered with it can handle this combination. If so, it will return a <tt>Connection</tt> 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 <tt>DriverManager</tt>, 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 <tt>Statement</tt> object <tt>stmnt</tt> from the <tt>Connection</tt> and executing an SQL query on <tt>stmnt</tt>. This returns a <tt>ResultSet</tt>, which is a representation of the table created by the query. A <tt>ResultSet</tt> consists of "rows," and you can iterate through the rows and extract information from each row using the <tt>ResultSet</tt> methods. Here is an example of getting all the rows of the <tt>inventory</tt> table from the <tt>petstore</tt> database and creating <tt>InventoryItem</tt> objects corresponding to each one (from <tt>tutorial-jdbc/src/Inventory.java</tt>): 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 <tt>InventoryItem.java</tt> (see especially <tt>setQuantity</tt> for an example of changing the database) and <tt>Supplier.java</tt>.

A note about <tt>Inventory</tt> and <tt>Supplier</tt>: 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 <tt>private</tt>. 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 <tt>Connection</tt> object all the time so that every class/object can talk to the database. Of course, we could also define a <tt>PetstoreDB</tt> 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 <ul> <li> The Java Tutorial, JDBC Trail. </li> </ul>

Database connectivity and Spring

A simple Spring application

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

An alternative to <tt>DriverManager</tt> is <tt>DataSource</tt>. 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 (<tt>tutorial-jdbc-spring/metadata/petsore.xml</tt>; ignore the remaining bean definitions in that file for now): <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <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 <tt>Connection</tt> object (<tt>tutorial-jdbc-spring/src/conn/petstore/CreatePetStore.java</tt>): // 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 <tt>dao.petstore.CreatePetStore</tt> in the <tt>tutorial-jdbc-spring</tt> project.

As we saw above, <tt>Inventory</tt> and <tt>Supplier</tt> 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 (<tt>tutorial-jdbc-spring/metadata/petstore.xml</tt>): <bean id="inventoryDAO" class="dao.petstore.Inventory" > <property name="dataSource" ref="dataSource" />

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

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

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

Likewise, look at <tt>dao/petstore/Inventory.java</tt>. As usual, instead of instantiating an <tt>Inventory</tt> object, we get it by its bean reference. When the bean is created, Spring will invoke <tt>setDataSource</tt> with the appropriate <tt>DataSource</tt> as defined in the application context file. No longer do we have to "carry around" a <tt>Connection</tt> 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 <tt>JdbcTemplate</tt> (similar to <tt>Statement</tt>) and <tt>SqlRowSet</tt> (similar to <tt>ResultSet</tt>). You will notice in the various classes that we do not save the <tt>DataSource</tt> that we are configured with. Instead, we create a <tt>JdbcTemplate</tt> based on the <tt>DataSource</tt> and save that as our instance data. We can then submit SQL queries analogously to how we did with <tt>Statement</tt> and <tt>ResultSet</tt>; see the <tt>fillInventory</tt> method of <tt>tutorial-jdbc-spring/src/dao/petstore/Inventory.java</tt> for an example.

Exercises Add an "add item" function to the <tt>Inventory</tt> 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: <ul> <li>Some clean-up: should every class be implemented as a bean?</li> <li>Object-relational mapping and Hibernate.</li> </ul>