Section 4.8. Accessing the Database Using Ant

4.8. Accessing the Database Using Ant

Now that we've created the JBossAtWorkDB database instance, we need to create and populate the Car table. Ant has a <sql> task that is ideal for this sort of thing. Keeping these commands in a script allows you to rebuild your database easily and often during the development phase.

The same rules for scripting the deployment of your EAR to a production server apply here as well: Just Say NO! If you create a script that points to a production database, you are only asking for it to be run inadvertently with disastrous results. With great power comes great responsibilityuse it wisely.

That said, let's look at the build.xml file in the new SQL subproject, shown in Example 4-9. This project doesn't contain any compiled code. It is just a convenient storage location for these SQL scripts.

Example 4-9. SQL subproject build.xml

 <?xml version="1.0"?> <project name="sql" default="init" basedir=".">     <!-- Initialization variables -->     <property name="database.driver.dir"               value="${env.JBOSS_HOME}/server/default/lib/"/>     <property name="database.driver.jar" value="hsqldb.jar"/>     <path >         <fileset dir="${database.driver.dir}">             <include name="${database.driver.jar}"/>         </fileset>     </path>     <!-- ====================================== -->     <target name="init"             description="Creates test data in the database.">         <sql driver="org.hsqldb.jdbcDriver"              url="jdbc:hsqldb:hsql://localhost:1701"              user              password=""              print="yes"              classpathref="sql.classpath">             DROP TABLE IF EXISTS CAR;             CREATE TABLE CAR (                 ID BIGINT identity,                 MAKE VARCHAR(50),                 MODEL VARCHAR(50),                 MODEL_YEAR VARCHAR(50)              );              INSERT INTO CAR (ID, MAKE, MODEL, MODEL_YEAR)              VALUES (99, 'Toyota', 'Camry', '2005');              INSERT INTO CAR (ID, MAKE, MODEL, MODEL_YEAR)              VALUES (100, 'Toyota', 'Corolla', '1999');              INSERT INTO CAR (ID, MAKE, MODEL, MODEL_YEAR)              VALUES (101, 'Ford', 'Explorer', '2005');              SELECT * FROM CAR;          </sql>      </target> </project> 

We provide a classpath to your database driver and set up the connection string. From there, it is straight SQL. Run the script: you should see something like Example 4-10 for console output.

Example 4-10. Ant SQL output

 Buildfile: build.xml init:       [sql] Executing commands       [sql] 0 rows affected       [sql] 0 rows affected       [sql] 1 rows affected       [sql] 1 rows affected       [sql] 1 rows affected       [sql] ID,MAKE,MODEL,MODEL_YEAR       [sql] 99,Toyota,Camry,2005       [sql] 100,Toyota,Corolla,1999       [sql] 101,Ford,Explorer,2005       [sql] 0 rows affected       [sql] 6 of 6 SQL statements executed successfully BUILD SUCCESSFUL Total time: 3 seconds 

If you really want to prove to yourself that this worked, look in $JBOSS_HOME/server/default/data/hypersonic one more time. If you open the jaw-db.log file in a text editor, you should see the following:

 /*C1*/CONNECT USER SA SET AUTOCOMMIT FALSE  DROP TABLE IF EXISTS CAR   CREATE TABLE CAR ( ID BIGINT, MAKE VARCHAR(50), MODEL VARCHAR(50),          MODEL_YEAR VARCHAR(50) ) INSERT INTO CAR VALUES(99,'Toyota','Camry','2005') INSERT INTO CAR VALUES(100,'Toyota','Corolla','1999') INSERT INTO CAR VALUES(101,'Ford','Explorer','2005') COMMIT DISCONNECT 

At this point, the stage is set. We have a database table with data in it. All we need to do now is create a new DAO object that will read the information out of the table.