Getting started with the SQL packageThis document will describe the use of the sql packe inside jZonic. This package is meant to ease the use of sql statements in your code.
Short descriptionIn this demo we will build a small task manager that is supposed to handle tasks.
It will demonstrate the usage of the org.jzonic.core.sql.BaseDBManager. The database we are going to use is the hypersonic db. This BaseDBManager uses the connection pool provided as service by the jZonic service provider. Actually there are two services that will be used. Beside the connection pool there is another service that will start the hypersonic database as standalone server. If you are trying this demo with a different database then you do not need this service. There is a part about using different databases at the end.
Before we startYou can download the entire source code from our sourceforge project page.:
jZonic-sql-demo-v1.0.tar.gz
jZonic-sql-demo-v1.0.zip
Step by step
Creating the databaseFirst we will create the table:
CREATE TABLE TASK(ID INTEGER NOT NULL IDENTITY PRIMARY KEY,
DESCRIPTION VARCHAR(255),STATUS INTEGER,PRIORITY INTEGER,CREATED BIGINT)
Next we will put in one task so that we have something to start with:
INSERT INTO TASK VALUES(1,'This is my first task',1,5,0)
Since we are using a hypersonic database this means that you have to put the two lines in the demoDB.script file. That is all and our database is ready to be used.
The task objectNow we are going to write the TaskObject which is a simple java bean containing all our attributes. The is the source:
package org.jzonic.examples.sql;
public class TaskObject {
private long id;
private String description;
private int priority;
private int status;
private long created;
public TaskObject() {
}
... getter and setter goes here
}
The TaskManagerThe TaskManager will contain all our methods. It is the DAO. The only thing we have to do is to extends the org.jzonic.core.sql.BaseDBManager. Here is the source:
package org.jzonic.examples.sql;
import org.jzonic.core.sql.BaseDBManager;
public class TaskManager extends BaseDBManager {
public TaskManager() {
}
}
The BaseDBManager offers all methods that we will need later on and also offers a connection pool.
The first methodAfter all the preparation it is time to get the hands dirty. The first method we will write is getAllTasks() which will return all tasks from the database.
package org.jzonic.examples.sql;
import java.sql.SQLException;
import java.util.List;
import java.util.Vector;
import org.jzonic.core.sql.BaseDBManager;
import org.jzonic.core.sql.DataRow;
import org.jzonic.core.sql.Dataset;
import org.jzonic.core.sql.SQLQuery;
public class TaskManager extends BaseDBManager {
public TaskManager() {
}
/**
* This method will return all tasks that are stored in the database
*
* @return list of tasks
* @throws SQLException
*/
public List getAllTasks() throws SQLException {
List all = new Vector();
SQLQuery query = new SQLQuery("SELECT * from TASK");
Dataset dp = query(query);
if ( dp != null && dp.getRowCount() > 0 ) {
for ( int i = 0; i < dp.getRowCount();i++) {
DataRow dr = dp.getRow(i);
TaskObject task = new TaskObject();
task.setId(dr.getLong("ID"));
task.setDescription(dr.getString("DESCRIPTION"));
task.setStatus(dr.getInt("STATUS"));
task.setPriority(dr.getInt("PRIORITY"));
task.setCreated(dr.getLong("CXREATED"));
all.add( task );
}
}
return all;
}
}
So what do we have done so far. The first step was to create a org.jzonic.core.sql.SQLQuery object with the actual sql statement. Next we have called the query method of the BaseDBManager that returns a Dataset. This Dataset is like a ResultSet beside that contains already all resulting rows. Since we have the Dataset we can now walk through this resultset and get the DataRow of very line. With every DataRow we can get the data and put it into our TaskObject.
The first testcaseSince we are good developers we will write a testcase for the method.
package org.jzonic.examples.sql;
import java.sql.SQLException;
import java.util.List;
import junit.framework.TestCase;
import junit.framework.TestResult;
public class TaskManagerTest extends TestCase {
public void testGetAllTasks() {
TaskManager manager = new TaskManager();
try {
List all = manager.getAllTasks();
assertNotNull(all);
assertEquals(1,all.size());
TaskObject task = (TaskObject)all.get(0);
assertEquals("This is my first task",task.getDescription());
} catch (SQLException e) {
e.printStackTrace();
fail("unexpected exception");
}
}
}
But before you can run the testcase we need to take a look at the services.xml which will define the two services that we need.
Preparing the service providerThe BaseDBManager needs one service called CP which is the connection pool. The other service just starts the hypersonic database as standalone server. This service assumes that the actual database files can be found in the classpath:
<?xml version="1.0" encoding="UTF-8"?>
<services>
<service name="database" code="org.jzonic.core.sp.services.HypersonicDB">
<attribute name="port" type="int" value="1478"/>
<attribute name="databaseName" type="java.lang.String" value="demoDB"/>
<attribute name="wait" type="long" value="3000"/>
<method name="startDatabase"/>
<load-on-startup/>
</service>
<service name="CP" code="org.jzonic.core.sp.services.ConnectionPoolManager">
<attribute name="jdbcUrl" type="java.lang.String" value="jdbc:hsqldb:hsql://localhost:1478"/>
<attribute name="user" type="java.lang.String" value="sa"/>
<attribute name="password" type="java.lang.String" value=""/>
<attribute name="driver" type="java.lang.String" value="org.hsqldb.jdbcDriver"/>
<method name="getPool"/>
</service>
</services>
Now you can run the testcase and see the green light.
Next method Getting a specific taskThe next method will return one specific task from the database defined by the id.
public TaskObject getTaskByID(long id) throws SQLException {
SQLQuery query = new SQLQuery("SELECT * from TASK WHERE ID=?");
query.addParameter(new Long(id));
Dataset dp = query(query);
if ( dp != null && dp.getRowCount() > 0 ) {
return getTaskFromDataRow(dp.getRow(0));
}
return null;
}
private TaskObject getTaskFromDataRow(DataRow dr) {
TaskObject task = new TaskObject();
task.setId(dr.getLong("ID"));
task.setDescription(dr.getString("DESCRIPTION"));
task.setStatus(dr.getInt("STATUS"));
task.setPriority(dr.getInt("PRIORITY"));
task.setCreated(dr.getLong("CXREATED"));
return task;
}
We have also moved the retrieving of a task from a DataRow to a separate method since it is exactly the same as the part used in out first method.
The difference to the first method is simply that we have added a parameter to our query. You can add as many parameters as you need for your query.
The junit testcase is also pretty simple:
public void testGetTaskById() {
TaskManager manager = new TaskManager();
try {
TaskObject task = manager.getTaskByID(1);
assertNotNull(task);
assertEquals("This is my first task",task.getDescription());
} catch (SQLException e) {
e.printStackTrace();
fail("unexpected exception");
}
}
Finishing what we have startedThe last methods we will add in this short demo are of course to add, edit and delete a task.
public void addTask(TaskObject task) throws SQLException {
if ( task != null ) {
SQLQuery query = new SQLQuery("INSERT INTO TASK (DESCRIPTION,STATUS,PRIORITY,CREATED) values (?,?,?,?)");
query.addParameter(task.getDescription());
query.addParameter(new Integer(task.getStatus()));
query.addParameter(new Integer(task.getPriority()));
query.addParameter(new Long(task.getCreated()));
excuteQuery(query);
}
}
public void editTask(TaskObject task) throws SQLException {
if ( task != null ) {
SQLQuery query = new SQLQuery("UPDATE TASK set DESCRIPTION=?,STATUS=?,PRIORITY=? WHERE ID=?");
query.addParameter(task.getDescription());
query.addParameter(new Integer(task.getStatus()));
query.addParameter(new Integer(task.getPriority()));
query.addParameter(new Long(task.getId()));
excuteQuery(query);
}
}
public void deleteTask(long id) throws SQLException {
SQLQuery query = new SQLQuery("DELETE FROM TASK WHERE ID=?");
query.addParameter(new Long(id));
excuteQuery(query);
}
SummaryThis little demo was meant to show how simple it is to use the BaseDBManager of the jZonic framework. There is no need to take care of the database connections or prepare any sql statements.
|
