Section 4.1. Setting Up the Database and Schema

4.1. Setting Up the Database and Schema

If you don't already have a relational database, it's time to set one up, define the schema for our application, and tweak our Ant build appropriately. Don't worry. It will go quickly. We'll use MySQL. You can then define some scripts that build the schema, and an Ant task to create the database from a script.

I'm directionally challenged, but kayakers have a tremendous advantage that most hikers don't share. Hikers can get easily lost as they take on more advanced hikes. My rivers don't move. They stay in the river beds, and flow one direction. I never have to worry about getting lost, as long as I can find the put-in and the take-out. Spring is like the river that lends structure and direction to your journey. It doesn't provide the database or transactions or remoting, but makes those services more consistent and easier to use.

4.1.1. How do I do that?

First, you'll get and install MySQL. You can find it at http://mysql.org. Download it and follow the installation directions. Make sure to pay special attention to the instructions for initializing and modifying the user accounts for the installation; the instructions can vary greatly from one version to the next.

Start the MySQL daemon and make sure things are working by creating a database, as in Example 4-1.

Example 4-1. Using mysql client to list databases

mysql> create database bikestore; Query OK, 1 row affected (0.00 sec) mysql> show databases; +------------+ | Database   | +------------+ | bikestore  | | mysql      | | test       | +------------+ 3 rows in set (0.00 sec)

Your application is going to talk to MySQL through a JDBC driver. You've got to tell the JVM how to find it. You'll want to download the latest release version of the Mysql Connector/J library and make it available to your project's classpath. In this case, we've copied it into the /lib folder underneath the main project folder.

Next, you can create the database schema. You could issue commands directly against the database. You'd be able to quickly establish a few tables, and you'd get immediate feedback if you made any syntax errors. But there are also a few downsides to that approach:

• You wouldn't be able to automate the process. If you wanted to create the database on another machine, you couldn't.

• You wouldn't have the DDL (data definition language). The DDL can also help you communicate with others that may want to read your code, or help them set up the environment to test the code.

• Later, when you use an object relational mapper, it's safer to use a script than to point it directly to the database.

Instead, you'll create a file with your database schema and data, and store it in the project's /db folder, which you created earlier. For each table, you'll use an identifier, which makes it easier to build consistent code, and a simple schema, which handles the identifiers consistently. You'll also see a database table column for each major field. For the most part, your design will let you have one class per table, but not exclusively. Some of the relationships will require extra relationship tables (Example 4-2).

Example 4-2. rentabike.sql

drop database bikestore; create database bikestore; use bikestore; create table bikes (    bikeId int(11) not null auto_increment,     manufacturer char(50) not null,    model char(50) not null,    frame int(11) not null default '0',    serialNo char(20) not null,    weight double not null default '0',    `status` char(15) not null,    primary key (bikeId)); create table customers (    custId int(11) not null auto_increment,    firstname char(50) not null,    lastname char(50) not null,    primary key (custId)); create table reservations (    resId int(11) not null auto_increment,    bikeId int(11) not null,    custId int(11) not null,    resDate date not null,    primary key (resId));

After creating the tables, you should assign all permissions to your account so that your code can access the data.

You should try to verify your driver with the simple test shown in Example 4-3, which opens the driver.

Example 4-3. ControllerTest.java

public void testJDBC( ) throws Exception {    try {       System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver");       Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/bikestore");    } catch (Exception ex) {       fail("Failed to open connection:" + ex.getMessage( ));    }    assertTrue(true); }

Example 4-4 creates some sample data. This approach is nice, because once you start testing, it allows you to start with a consistent set of test data for each test case.

Example 4-4. rentabike.sql

insert into bikes values(1, "Shimano", "Roadmaster", 20, "11111", 15, "Fair"); insert into bikes values(2, "Cannondale", "F2000 XTR", 18, "22222",12, "Excellent"); insert into bikes values(3, "Trek","6000", 19, "33333", 12.4, "Fair"); insert into customers values(1, "Justin", "Gehtland"); insert into customers values(2, "Bruce", "Tate"); insert into reservations values(1, 2, 2, '2004-09-15'); insert into reservations values(2, 3, 1, '2004-10-07');

Finally, you'll modify your Ant build script to use the rentabike.sql file to create your database. To do so, you'll have to provide a couple of new properties, as well as a classpath that points to the MySQL driver in your /lib folder.

Example 4-5. build.xml

<property name="database.url" value="jdbc:mysql://localhost/bikestore"/> <property name="database.username" value="bikestore"/> <path >     <pathelement location="${war.dir}/WEB-INF/lib/        mysql-connector-java-3.0.14-production-bin.jar"/> </path> <target name="create.tables">     <sql driver="com.mysql.jdbc.Driver"         url="${database.url}"         user         password="">         <classpath>             <path ref/>         </classpath>         <fileset dir="${db.dir}">             <include name="rentabike.sql"/>         </fileset>     </sql> </target>

4.1.2. What just happened?

You're now set up and ready to put Spring through its paces. You just installed MySQL, the database engine. You started the database service and then created a database, so you know it's working. Since all Java applications will access MySQL through a JDBC driver, you installed one so that our application could find it. Then, you created a new Ant task, and ran the task to create a database and some sample data.

You need to be careful to make sure that both the database and the driver work. These verification steps may seem tedious, but they'll save you a whole lot of time in the long run, because things are much easier to debug before you add additional layers.

4.1.3. What about...

...HyperSQL, Oracle, DB2, or some other database? You used MySQL because it's one of the most widely used open source databases. Spring will make sure that much of what you're going to do will be portable. If you'd like, you can install any database, as long as you make sure to install a working JDBC driver.