Database Tutorial Data Access Object - Revision history

Kyle: /* Properly closing the Database connection */

2009-12-03T22:20:42Z

‎Properly closing the Database connection

Kyle: /* Query the database */

2009-12-03T22:19:08Z

‎Query the database

Kyle: /* Extensions */

2009-12-03T21:54:04Z

‎Extensions

Kyle at 21:38, 3 December 2009

2009-12-03T21:38:01Z

Kyle: New page: A Data Access Object is an object-oriented pattern that is often used in conjunction with databases to hide the details of interfacing with a database. In our case, it allows us to encapsu...

2009-12-03T21:37:17Z

New page: A Data Access Object is an object-oriented pattern that is often used in conjunction with databases to hide the details of interfacing with a database. In our case, it allows us to encapsu...

New page

A Data Access Object is an object-oriented pattern that is often used in conjunction with databases to hide the details of interfacing with a database. In our case, it allows us to encapsulate all the work of talking with JDBC so that none of that logic is included in the DBApp class.
=The DAO interface=
The first step is to create an interface class that the DBApp can use to add new RFIDEvent objects to the database. For now, we will keep it very simple:
<pre>
/**
* This interface is implemented by Data Access Objects (DAO) that have the
* ability to write to our data source.
*
* @author Kyle Neumeier - kyle@pramari.com
*
*/
public interface RFID_DAO {

/***
* Write the supplied event to the data source
*
* @param event
*/
public void addRow(RFIDEvent event);

}
</pre>
=The DAO Implementation=
The next step is to create an object that implements the <tt>RFID_DAO</tt> interface and can use JDBC to write to a database.
==Define the table and column names==
To make sure that we use the same table and column names throughout the application, let's define some static final members:
<pre>
/** The name of the table to write to */
private static final String TABLE = "rfidevents";
/** The name of the ID column in the table */
private static final String COL_ID = "id";
/** The name of the reader column in the table */
private static final String COL_READER = "reader";
/** The name of the antenna column in the table */
private static final String COL_ANT = "antenna";
/** The name of the time column in the table */
private static final String COL_TIME = "time";
</pre>
==Dependency Injection==
Like the DBApp class, the DAO should have its dependencies injected by spring. In this case, we only have one dependency:
;SimpleDriverDataSource
:This object contains the information about how to connect to a database: the JDBC driver to use, the URL of the database, and the username and pass.
<pre>
/**
* Method called by spring to inject the datasource. This method also opens
* up the connection to the datasource.
*
* @param ds
*/
public void setDataSource(SimpleDriverDataSource ds) {
this.ds = ds;
template = new SimpleJdbcTemplate(ds);

// first thing to do is to drop the table if it exists already.
try {
template.update("drop table " + TABLE, new Object[] {});
logger.debug("RFIDEVENTS table dropped");
} catch (DataAccessException dae) {
}

// next create the table
template.update("create table " + TABLE + "(" + COL_ID
+ " VARCHAR(24), " + COL_READER + " VARCHAR(50)," + COL_ANT
+ " INTEGER, " + COL_TIME + " TIMESTAMP)", new Object[] {});
logger.debug("New RFIDEVENTS table created");
insertRFIDEvent = new SimpleJdbcInsert(ds).withTableName(TABLE);
}
</pre>
This setter method is a bit more complicated than the one in the DBApp class. Whenever spring hands the DAO a new datasource, it needs to do some setup.
First, it needs to create the <tt>SimpleJdbsTemplate</tt> which is a helper class provided by spring that simplifies JDBC database access. Next it attempts to drop the table if it has already been created. Then it tries to create the table. There are definitely more sophisticated ways to ensure that a table exists and that there are no rows in it, but this is simple enough. Finally, the setter method creates another spring helper object called SimpleJdbcInsert which (as the name implies) simplifies the process of writing a new row in the database.
==Add Rows==
The last piece of functionality that the DAO needs is to implement the addRow method from its interface.
<pre>
/*
* (non-Javadoc)
*
* @see
* org.rifidi.edge.app.db.dao.RFIDDAO#addRow(org.rifidi.edge.app.db.domain
* .RFIDEvent)
*/
@Override
public void addRow(RFIDEvent event) {
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put(COL_ID, event.getId());
parameters.put(COL_READER, event.getReader());
parameters.put(COL_ANT, event.getAntenna());
parameters.put(COL_TIME, new Timestamp(event.getTimestamp()));
insertRFIDEvent.execute(parameters);

}
</pre>
This method is self-evident. Whenever it is called with an RFID event object, it creates a map containing the information to be inserted and the columns to insert that data into. Then it inserts a new row into the database.
=Extensions=
==Query the database==

← Older revision		Revision as of 22:20, 3 December 2009
Line 101:		Line 101:

	==Properly closing the Database connection==		==Properly closing the Database connection==
		+	This DAO does not properly shut down the JDBC connection. You could possibly add a method called <tt>close()</tt> to the DAO interface and then let the DBApp object call this method when its stop method is called.

@@ Line 94: / Line 94: @@
 Here are a few ideas for extra functionality that might be useful for this DAO to have
 ==Query the database==
 ==Properly closing the Database connection==

← Older revision		Revision as of 21:38, 3 December 2009
Line 93:		Line 93:
	=Extensions=		=Extensions=
	==Query the database==		==Query the database==
		+	==Properly closing the Database connection==

@@ Line 91: / Line 91: @@
 </pre>
 This method is self-evident. Whenever it is called with an RFID event object, it creates a map containing the information to be inserted and the columns to insert that data into. Then it inserts a new row into the database.
-=Extensions=
+=Extra Credit=
 ==Query the database==
 ==Properly closing the Database connection==