Jumat, 31 Januari 2014

Effective datatable paging and sorting






Effective datatable paging and sorting at DAO level


In the 2 year old article Using datatables I wrote how to sort and page a JSF h:dataTable. Those are useful and nice if the dataset is small. But it is in fact less efficient as it first loads the whole data list from the database and uses Java to do the sorting and paging. It is much more efficient if you let the database do all the task. A self-respected database can sort the results much faster than Java can do. Querying a sublist from the database consumes much less memory in Java than when you query the complete list from the database. This all will make it much faster when you have a database with at least hundreds of rows.




Sorting using SQL can easily be done with the standardized ORDER BY clause. The way to obtain a subset of results differs per database. This article is targeted on MySQL. In MySQL you can obtain a subset of results with the LIMIT x, y clause. PostgreSQL uses LIMIT x OFFSET y. In Oracle you need to execute a ORDER BY subquery first and then use the ROWNUM clause on its results (SELECT * FROM (SELECT * FROM table ORDER BY column) WHERE ROWNUM BETWEEN x AND y). For MSSQL and DB2 you'll need to write a (w)hacky SQL query or to create a stored procedure. Consult Google or database specific documentation for details.


Back to top


Preparations


Next to a standard JSF implementation, we need the Tomahawk component library as it offers us the t:dataList and t:saveState components. The t:dataList is needed to display a collection of links with page numbers. It is preferred above JSTL's c:forEach, because it does its work more efficient. The t:saveState is needed to cache the displayed list and some important paging and sorting variables for the next request. It is preferred above h:inputHidden, because it does its work more efficient and it doesn't require a converter for non-standard object types. You can even cache a complete bean for the subsequent request, with which you can simulate a "conversation scope".


Integrating Tomahawk isn't that hard, you can even do that on a Sun Mojarra environment. You just need to add at least the following JAR's to the classpath, e.g. /WEB-INF/lib. The version numbers doesn't matter that much, as long as you get the newest.



The Tomahawk JAR is the Tomahawk component library itself which under each contains the t:dataList and t:saveState components. The commons JAR's are required by other components and/or the core of the Tomahawk component library.


Back to top


Backing Bean


Here is how the basic backing bean code look like. It is request scoped. If you're interested, an example of the DAOFactory can be found here: DAO tutorial - the data layer.



package mypackage;

import java.io.Serializable;
import java.util.List;

import javax.faces.component.UICommand;
import javax.faces.event.ActionEvent;

import mydao.DAOException;
import mydao.DAOFactory;
import mydao.MyDataDAO;
import mymodel.MyData;

/**
* The example backing bean for effective datatable paging and sorting.
*
* @author BalusC
* @link http://balusc.blogspot.com/2008/10/effective-datatable-paging-and-sorting.html
*/

public class MyBean implements Serializable {

// Properties ---------------------------------------------------------------------------------

// DAO.
private static MyDataDAO dao = DAOFactory.getInstance("javabase").getMyDataDAO();

// Data.
private List dataList;
private int totalRows;

// Paging.
private int firstRow;
private int rowsPerPage;
private int totalPages;
private int pageRange;
private Integer[] pages;
private int currentPage;

// Sorting.
private String sortField;
private boolean sortAscending;

// Constructors -------------------------------------------------------------------------------

public MyBean() {
// Set default values somehow (properties files?).
rowsPerPage = 10; // Default rows per page (max amount of rows to be displayed at once).
pageRange = 10; // Default page range (max amount of page links to be displayed at once).
sortField = "id"; // Default sort field.
sortAscending = true; // Default sort direction.
}

// Paging actions -----------------------------------------------------------------------------

public void pageFirst() {
page(0);
}

public void pageNext() {
page(firstRow + rowsPerPage);
}

public void pagePrevious() {
page(firstRow - rowsPerPage);
}

public void pageLast() {
page(totalRows - ((totalRows % rowsPerPage != 0) ? totalRows % rowsPerPage : rowsPerPage));
}

public void page(ActionEvent event) {
page(((Integer) ((UICommand) event.getComponent()).getValue() - 1) * rowsPerPage);
}

private void page(int firstRow) {
this.firstRow = firstRow;
loadDataList(); // Load requested page.
}

// Sorting actions ----------------------------------------------------------------------------

public void sort(ActionEvent event) {
String sortFieldAttribute = (String) event.getComponent().getAttributes().get("sortField");

// If the same field is sorted, then reverse order, else sort the new field ascending.
if (sortField.equals(sortFieldAttribute)) {
sortAscending = !sortAscending;
} else {
sortField = sortFieldAttribute;
sortAscending = true;
}

pageFirst(); // Go to first page and load requested page.
}

// Loaders ------------------------------------------------------------------------------------

private void loadDataList() {

// Load list and totalCount.
try {
dataList = dao.list(firstRow, rowsPerPage, sortField, sortAscending);
totalRows = dao.count();
} catch (DAOException e) {
throw new RuntimeException(e); // Handle it yourself.
}

// Set currentPage, totalPages and pages.
currentPage = (totalRows / rowsPerPage) - ((totalRows - firstRow) / rowsPerPage) + 1;
totalPages = (totalRows / rowsPerPage) + ((totalRows % rowsPerPage != 0) ? 1 : 0);
int pagesLength = Math.min(pageRange, totalPages);
pages = new Integer[pagesLength];

// firstPage must be greater than 0 and lesser than totalPages-pageLength.
int firstPage = Math.min(Math.max(0, currentPage - (pageRange / 2)), totalPages - pagesLength);

// Create pages (page numbers for page links).
for (int i = 0; i < pagesLength; i++) {
pages[i] = ++firstPage;
}
}

// Getters ------------------------------------------------------------------------------------

public List getDataList() {
if (dataList == null) {
loadDataList(); // Preload page for the 1st view.
}
return dataList;
}

public int getTotalRows() {
return totalRows;
}

public int getFirstRow() {
return firstRow;
}

public int getRowsPerPage() {
return rowsPerPage;
}

public Integer[] getPages() {
return pages;
}

public int getCurrentPage() {
return currentPage;
}

public int getTotalPages() {
return totalPages;
}

// Setters ------------------------------------------------------------------------------------

public void setRowsPerPage(int rowsPerPage) {
this.rowsPerPage = rowsPerPage;
}

}


Define it as usual in the faces-config.xml:






myBean
mypackage.MyBean
request




Back to top


Example DTO


Here is the basic DTO example. It's nothing special. It's just a dummy DTO with three fields: ID, Name and Value.



package mymodel;

import java.io.Serializable;

/**
* MyData. The example DTO (Data Transfer Object).
*
* @author BalusC
* @link http://balusc.blogspot.com/2008/10/effective-datatable-paging-and-sorting.html
*/

public class MyData implements Serializable {

// Properties ---------------------------------------------------------------------------------

private Long id;
private String name;
private Integer value;

// Constructors -------------------------------------------------------------------------------

public MyData() {
// Keep default constructor alive.
}

public MyData(Long id, String name, Integer value) {
this.id = id;
this.name = name;
this.value = value;
}

// Getters ------------------------------------------------------------------------------------

public Long getId() {
return id;
}

public String getName() {
return name;
}

public Integer getValue() {
return value;
}

// Setters ------------------------------------------------------------------------------------

public void setId(Long id) {
this.id = id;
}

public void setName(String name) {
this.name = name;
}

public void setValue(Integer value) {
this.value = value;
}

}


Back to top


Example DAO


The basic DAO example. Note that you cannot set the ORDER BY field and direction as PreparedStatement value. That's why it uses the String#format() for it. Keep SQL injection risks in mind. As long as the client can't control the values, you don't need to be afraid.


For more information and examples of the DAO layer and the DAOUtil class, you may find this article useful: DAO tutorial - the data layer.



package mydao;

import static mydao.DAOUtil.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import mymodel.MyData;

/**
* This class represents a SQL Database Access Object for the {@link MyData} DTO.
*
* @author BalusC
* @link http://balusc.blogspot.com/2008/10/effective-datatable-paging-and-sorting.html
*/

public final class MyDataDAO {

// Constants ----------------------------------------------------------------------------------

private static final String SQL_LIST_BY_ORDER_AND_LIMIT =
"SELECT id, name, value FROM mydata ORDER BY %s %s LIMIT ?, ?";
private static final String SQL_COUNT =
"SELECT count(*) FROM mydata";

// Properties ---------------------------------------------------------------------------------

private DAOFactory daoFactory;

// Constructors -------------------------------------------------------------------------------

/**
* Construct MyData DAO for the given DAOFactory. Package private so that it can be constructed
* inside the DAO package only.
* @param daoFactory The DAOFactory to construct this MyData DAO for.
*/

MyDataDAO(DAOFactory daoFactory) {
this.daoFactory = daoFactory;
}

// Actions ------------------------------------------------------------------------------------

/**
* Returns list of MyData items starting at the given first index with the given row count,
* sorted by the given sort field and sort order.
* @param firstRow First index of rows to be returned.
* @param rowCount Amount of rows to be returned.
* @param sortField Field to sort the data on.
* @param sortAscending Whether to sort data ascending or not.
* @return list of MyData items starting at the given first index with the given row count,
* sorted by the given sort field and sort order.
* @throws DAOException If something fails at DAO level.
*/

public List list(int firstRow, int rowCount, String sortField, boolean sortAscending)
throws DAOException
{
Object[] values = { firstRow, rowCount };

String sortDirection = sortAscending ? "ASC" : "DESC";
String sql = String.format(SQL_LIST_BY_ORDER_AND_LIMIT, sortField, sortDirection);
List dataList = new ArrayList<>();

try (
Connection connection = daoFactory.getConnection();
PreparedStatement statement = prepareStatement(connection, sql, false, values);
ResultSet resultSet = statement.executeQuery();
) {
while (resultSet.next()) {
dataList.add(mapMyData(resultSet));
}
} catch (SQLException e) {
throw new DAOException(e);
}

return dataList;
}

/**
* Returns total amount of rows in table.
* @return Total amount of rows in table.
* @throws DAOException If something fails at DAO level.
*/

public int count() throws DAOException {
int count = 0;

try (
Connection connection = daoFactory.getConnection();
PreparedStatement statement = connection.prepareStatement(SQL_COUNT);
ResultSet resultSet = statement.executeQuery();
) {
if (resultSet.next()) {
count = resultSet.getInt(1);
}
} catch (SQLException e) {
throw new DAOException(e);
}

return count;
}

/**
* Map the current row of the given ResultSet to MyData.
* @param resultSet The ResultSet of which the current row is to be mapped to MyData.
* @return The mapped MyData from the current row of the given ResultSet.
* @throws SQLException If something fails at database level.
*/

private static MyData mapMyData(ResultSet resultSet) throws SQLException {
return new MyData(
resultSet.getLong("id"),
resultSet.getString("name"),
resultSet.getObject("value") != null ? resultSet.getInt("value") : null
);
}

}


Back to top


JSF file


And now the JSF file, it has a sortable datatable, a bunch of paging buttons (first, previous, next and last), the status of current page and total pages, a bunch of links pointing to a specific page and finally a input field where you can specify the amount of rows to be displayed at once.



<%@taglib uri="http://java.sun.com/jsf/core" prefix="f"%>
<%@taglib uri="http://java.sun.com/jsf/html" prefix="h"%>
<%@taglib uri="http://myfaces.apache.org/tomahawk" prefix="t"%>

html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">


xmlns="http://www.w3.org/1999/xhtml">

</span>Effective datatable paging and sorting at DAO level<span class="codetag">


id="form">

<%-- The sortable datatable --%>
value="#{myBean.dataList}" var="item">

name="header">
value="ID" actionListener="#{myBean.sort}">
name="sortField" value="id" />


value="#{item.id}" />


name="header">
value="Name" actionListener="#{myBean.sort}">
name="sortField" value="name" />


value="#{item.name}" />


name="header">
value="Value" actionListener="#{myBean.sort}">
name="sortField" value="value" />


value="#{item.value}" />



<%-- The paging buttons --%>
value="first" action="#{myBean.pageFirst}"
disabled="#{myBean.firstRow == 0}" />
value="prev" action="#{myBean.pagePrevious}"
disabled="#{myBean.firstRow == 0}" />
value="next" action="#{myBean.pageNext}"
disabled="#{myBean.firstRow + myBean.rowsPerPage >= myBean.totalRows}" />
value="last" action="#{myBean.pageLast}"
disabled="#{myBean.firstRow + myBean.rowsPerPage >= myBean.totalRows}" />
value="Page #{myBean.currentPage} / #{myBean.totalPages}" />



<%-- The paging links --%>
value="#{myBean.pages}" var="page">
value="#{page}" actionListener="#{myBean.page}"
rendered="#{page != myBean.currentPage}" />
value="#{page}"
escape="false"
rendered="#{page == myBean.currentPage}" />




<%-- Set rows per page --%>
for="rowsPerPage" value="Rows per page" />
id="rowsPerPage" value="#{myBean.rowsPerPage}" size="3" maxlength="3" />
value="Set" action="#{myBean.pageFirst}" />
for="rowsPerPage" errorStyle="color: red;" />

<%-- Cache bean with data list, paging and sorting variables for next request --%>
value="#{myBean}" />





Save it as paging.jsp or so and invoke it by http://localhost:8080/playground/paging.jsf, assuming that your development server runs at port 8080 and the playground environment's context root is called 'playground'.



That's all!


Back to top


Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair.


(C) October 2008, BalusC



Source:http://balusc.blogspot.com/2008/10/effective-datatable-paging-and-sorting.html

Validate required checkbox






Introduction


The required attribute of a h:selectBooleanCheckbox is a bit non-intuitive. If you want to require the user to check the desired checkbox, you would assume that setting the required attribute to true ought to be sufficient.




But it is not. As for every other UIInput component the default required="true" validator would only check if the value is actually filled and been sent to the server side, i.e. the value is not null nor empty. In case of a h:selectBooleanCheckbox, which accepts Boolean or boolean properties only, JSF EL will coerce the unchecked value to Boolean.FALSE during apply request values phase, right before validations phase. This value is not null nor empty! Thus, the required attribute of the h:selectBooleanCheckbox is fairly pointless. It would always pass the validation and thus never display the desired required message in case of an unchecked checkbox.


Back to top


RequiredCheckboxValidator


To solve this non-intuitive behaviour (I am still not sure if this is a bug or a feature; the coercion of a null or empty Boolean property to Boolean.FALSE instead of null might be a bug, but this is not the case when you used boolean; after all I would consider it as an unwanted feature which should be better documented), best what you can do is to create your own javax.faces.validator.Validator implementation specific for a h:selectBooleanCheckbox of which a checked value is required. It is relatively simple, just check if the provided value parameter equals Boolean.FALSE and handle accordingly.



package mypackage;

import java.text.MessageFormat;

import javax.faces.application.FacesMessage;
import javax.faces.component.UIComponent;
import javax.faces.component.UIInput;
import javax.faces.context.FacesContext;
import javax.faces.validator.Validator;
import javax.faces.validator.ValidatorException;

public class RequiredCheckboxValidator implements Validator {

public void validate(FacesContext context, UIComponent component, Object value)
throws ValidatorException
{
if (value.equals(Boolean.FALSE)) {
String requiredMessage = ((UIInput) component).getRequiredMessage();

if (requiredMessage == null) {
Object label = component.getAttributes().get("label");
if (label == null || (label instanceof String && ((String) label).length() == 0)) {
label = component.getValueExpression("label");
}
if (label == null) {
label = component.getClientId(context);
}
requiredMessage = MessageFormat.format(UIInput.REQUIRED_MESSAGE_ID, label);
}

throw new ValidatorException(
new FacesMessage(FacesMessage.SEVERITY_ERROR, requiredMessage, requiredMessage));
}
}

}

Note that this validator checks if the developer has set any requiredMessage attribute so that it uses its value instead of the JSF default required message.



Here is how you should define it in the faces-config.xml:




requiredCheckboxValidator
mypackage.RequiredCheckboxValidator


That's it! Attach this validator to the h:selectBooleanCheckbox using f:validator.


Back to top


Basic demonstration example



Here is a sample form. It represents a kind of an agreement form which requires the checkbox being checked before submit. This is not an uncommon functional requirement.


The stuff is developed and tested in a Java EE 5.0 environment with Tomcat 6.0.18 with Servlet 2.5, JSP 2.1 and JSF 1.2_09.




for="agree" value="Do you agree with me?" />
id="agree" value="#{myBean.agree}" requiredMessage="You must agree!">
validatorId="requiredCheckboxValidator" />

value="Submit" action="#{myBean.submit}" />
infoStyle="color: green;" errorStyle="color: red;" />


The appropriate test backing bean (request scoped) look like:



package mypackage;

import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;

public class MyBean {

// Properties ---------------------------------------------------------------------------------

private Boolean agree;

// Actions ------------------------------------------------------------------------------------

public void submit() {
FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("You agreed with me!"));
}

// Getters ------------------------------------------------------------------------------------

public Boolean getAgree() {
return agree;
}

// Setters ------------------------------------------------------------------------------------

public void setAgree(Boolean agree) {
this.agree = agree;
}

}


Now, when you submit the form with an checked checkbox, JSF will just proceed with form processing, otherwise it will display the desired required message!



Back to top



Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair.


(C) September 2008, BalusC




Source:http://balusc.blogspot.com/2008/09/validate-required-checkbox.html

Styling options in h:selectOneMenu






Introduction


Whenever you want to style a HTML element using CSS, you could just use its style or, preferably, class attribute. But in the default Sun JSF Mojarra implementation there is no comparable attribute available for that. The h:selectOneMenu, h:selectManyMenu and f:selectItem tags simply doesn't support it.



When looking at comparable attributes in other elements, you'll notice that h:dataTable has an elegant approach in form of the rowClasses attribute which accepts a commaseparated string of CSS class names which are to be applied on the elements repeatedly. Now, it would be nice to let among others the h:selectOneMenu support a similar optionClasses attribute.


This can be achieved at two ways: overriding the default renderer class and using the f:attribute to add it as an external component attribute, or overriding the default renderer class, the component class and the tag class to let it support the optionClasses attribute. It might be obvious that the first way is a bit hacky, but it costs much less effort. The second way is more elegant, but it require more code and a custom tld file which should copy all existing component attributes over (tld files unfortunately doesn't know anything about inheritance). BalusC did it and the tld file was almost 500 lines long for only the selectOneMenu and selectManyMenu. Ouch.


This article will handle only the first approach in detail.


Back to top


ExtendedMenuRenderer


Here is how the extended MenuRenderer look like:



package net.balusc.jsf.renderer.html;

import java.io.IOException;

import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;
import javax.faces.context.ResponseWriter;
import javax.faces.convert.Converter;
import javax.faces.model.SelectItem;

import com.sun.faces.renderkit.html_basic.MenuRenderer;

/**
* Extended menu renderer which renders the 'optionClasses' attribute above the standard menu
* renderer. To use it, define it as follows in the render-kit tag of faces-config.xml.
*
*

* <renderer>
* <component-family>javax.faces.SelectOne</component-family>
* <renderer-type>javax.faces.Menu</renderer-type>
* <renderer-class>net.balusc.jsf.renderer.html.ExtendedMenuRenderer</renderer-class>
* </renderer>
* <renderer>
* <component-family>javax.faces.SelectMany</component-family>
* <renderer-type>javax.faces.Menu</renderer-type>
* <renderer-class>net.balusc.jsf.renderer.html.ExtendedMenuRenderer</renderer-class>
* </renderer>
*

*
* And define the 'optionClasses' attribute as a f:attribute of the h:selectOneMenu or
* h:selectManyMenu as follows:
*
*

* <f:attribute name="optionClasses" value="option1,option2,option3" />
*

*
* It accepts a comma separated string of CSS class names which are to be applied on the options
* repeatedly (the same way as you use rowClasses in h:dataTable). The optionClasses will be
* rendered only if there is no 'disabledClass' or 'enabledClass' being set as an attribute.
*
* @author BalusC
* @link http://balusc.blogspot.com/styling-options-in-hselectonemenu.html
*/

public class ExtendedMenuRenderer extends MenuRenderer {

// Override -----------------------------------------------------------------------------------

/**
* @see com.sun.faces.renderkit.html_basic.MenuRenderer#renderOption(
* javax.faces.context.FacesContext, javax.faces.component.UIComponent,
* javax.faces.convert.Converter, javax.faces.model.SelectItem, java.lang.Object,
* java.lang.Object[])
*/

protected void renderOption(FacesContext context, UIComponent component, Converter converter,
SelectItem currentItem, Object currentSelections, Object[] submittedValues)
throws IOException
{
// Copied from MenuRenderer#renderOption() (and a bit rewritten, but that's just me) ------

// Get writer.
ResponseWriter writer = context.getResponseWriter();
assert (writer != null);

// Write 'option' tag.
writer.writeText("\t", component, null);
writer.startElement("option", component);

// Write 'value' attribute.
String valueString = getFormattedValue(context, component, currentItem.getValue(), converter);
writer.writeAttribute("value", valueString, "value");

// Write 'selected' attribute.
Object valuesArray;
Object itemValue;
if (containsaValue(submittedValues)) {
valuesArray = submittedValues;
itemValue = valueString;
} else {
valuesArray = currentSelections;
itemValue = currentItem.getValue();
}
if (isSelected(context, itemValue, valuesArray)) {
writer.writeAttribute("selected", true, "selected");
}

// Write 'disabled' attribute.
Boolean disabledAttr = (Boolean) component.getAttributes().get("disabled");
boolean componentDisabled = disabledAttr != null && disabledAttr.booleanValue();
if (!componentDisabled && currentItem.isDisabled()) {
writer.writeAttribute("disabled", true, "disabled");
}

// Write 'class' attribute.
String labelClass;
if (componentDisabled || currentItem.isDisabled()) {
labelClass = (String) component.getAttributes().get("disabledClass");
} else {
labelClass = (String) component.getAttributes().get("enabledClass");
}

// Inserted custom code which checks the optionClasses attribute --------------------------

if (labelClass == null) {
String optionClasses = (String) component.getAttributes().get("optionClasses");
if (optionClasses != null) {
String[] labelClasses = optionClasses.split("\\s*,\\s*");
String indexKey = component.getClientId(context) + "_currentOptionIndex";
Integer index = (Integer) component.getAttributes().get(indexKey);
if (index == null || index == labelClasses.length) {
index = 0;
}
labelClass = labelClasses[index];
component.getAttributes().put(indexKey, ++index);
}
}

// The remaining copy of MenuRenderer#renderOption() --------------------------------------

if (labelClass != null) {
writer.writeAttribute("class", labelClass, "labelClass");
}

// Write option body (the option label).
if (currentItem.isEscape()) {
String label = currentItem.getLabel();
if (label == null) {
label = valueString;
}
writer.writeText(label, component, "label");
} else {
writer.write(currentItem.getLabel());
}

// Write 'option' end tag.
writer.endElement("option");
writer.writeText("\n", component, null);
}

}


Configure it as follows in the faces-config.xml:





javax.faces.SelectOne
javax.faces.Menu
net.balusc.jsf.renderer.html.ExtendedMenuRenderer


javax.faces.SelectMany
javax.faces.Menu
net.balusc.jsf.renderer.html.ExtendedMenuRenderer



That's all!



Back to top


Basic demonstration example


And now a basic demonstration example how to use it.


The relevant part of the JSF file should look like:




value="#{myBean.selectedItem}">
name="optionClasses" value="option1, option2" />
value="#{myBean.selectItems}" />

value="submit" action="#{myBean.submit}" />


Note the f:attribute: this sets the optionClasses attribute value which is been picked up by the ExtendedMenuRenderer. It will apply the given CSS style classes repeatedly on the rendered option elements. You can even use EL in it so that a backing bean can generate the desired String of comma separated CSS style classes based on some conditions.



The CSS styles are definied as follows:



option.option1 {
background-color: #ccc;
}

option.option2 {
background-color: #fcc;
}


Note that some web browsers wouldn't apply this on the selected option in the h:selectOneMenu. If desired, you need to add a style class for the



















Introduction



In almost every application there is less or more interaction with a database or any other kind of datastore. In a well designed application the database logic is separated from the business and presentation logic. It must be able to run independently from the business and the presentation logic. This allows the developer much better maintainability, testability, reusability and replaceability.



Better maintainability, because the database logic is concentrated in a single layer, so there is no need to search for all related code in different classes with mixed code. Better testability, because the database logic can be run independently, so there is no need to run the business and/or presentation logic to test the database logic. Better reusability, because you only need to put the same database logic in the classpath of another applications (JSF, JSP, Swing, plain Java application, etcetera) to use it, so there is no need to copypaste it over all places. Better replaceability, because you can more easy provide interfaces to it and make use of the factory pattern, so there is no need to replace all code in the business and/or presentation logic when you want to switch from e.g. plain JDBC to Hibernate or JPA.



This article will show step by step how to create a data layer using basic JDBC.





Back to top


The data layer



The database logic is also called the 'data layer'. It exist of database access objects (DAO's). The DAO's can create, read, update and delete (CRUD) the model objects in/from the datastore. The model objects are just javabeans representing real world data, such as User, Product, Order, etcetera.



In this part of the DAO tutorial we'll create a data layer based on the basic JDBC API. The final goal is to map between the 'User' table in the database and the 'User' model in the Java code. This tutorial assumes that you're familiar with at least the Java basics, the JDBC API and the SQL language. This is essential to understand the code properly, although the code is well documented and almost self-explaining. If you're not familiar with one or more of them, then follow the links to learn about them first.



Back to top


Preparing database



In this tutorial we'll pick MySQL as database server and create a 'javabase' database with the 'User' table. Although, when the code is well written, you can use any SQL database server to your choice. You might need to change the CREATE statements slightly to suit what the specific database server understands. Here is how you could create the 'User' table in MySQL:




CREATE DATABASE javabase DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE javabase.User (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(60) NOT NULL,
password VARCHAR(32) NOT NULL,
firstname VARCHAR(40) NULL,
lastname VARCHAR(40) NULL,
birthdate DATE NULL,

PRIMARY KEY (id),
UNIQUE (email)
);


This 'User' table has an autogenerated ID field which indicate the unique user ID. The email and password fields are required (handy to have as a login). The password field is to be filled with a MD5 hash of the password (to improve security, so that one who hijacked the database still can't recover the password) so it needs to be exactly 32 chars. The firstname, lastname and birthdate are optional (NULL is allowed). The email address is unique in the whole table. For a good compatibility with all characters available on the world, it's a good practice to set the database to use the Unicode character encoding UTF-8 by default.



Also as a good practice -it is always better than using 'root'- also create a new database user who has privileges on the table. We will create a new user 'java' which is exclusively to be used by Java code. Here we assume that the user 'java' is operating at domain 'localhost' (when the JVM as well as the database server runs at the same machine, which might not always occur in production, but that's beyond the scope of this tutorial). The statements below are MySQL specific, you might need to change the statements depending on the database server used.




CREATE USER 'java'@'localhost' IDENTIFIED BY 'd$7hF_r!9Y';
GRANT ALL ON javabase.* TO 'java'@'localhost' IDENTIFIED BY 'd$7hF_r!9Y';


Last but not least, to access a database server using the JDBC API, you need a JDBC driver. The JDBC driver is a concrete implementation of the JDBC API (which exist of almost only interfaces). The JDBC driver is generally already provided by the database manfacturer itself. In case of MySQL you can download it here. Just place the JAR file in the runtime classpath.



Back to top


User model



Now we need to create a model class which represents a single row of the 'User' table of the 'javabase' database. It must be a class which follows the Javabean specifications. That is, having a (implicit) no-arg constructor, having private properties which are exposed by public getters and setters, having the Object#equals() and Object#hashCode() implemented and finally the class must also implement java.io.Serializable.



Also using wrapper datatype objects (Long, Integer, etc.) instead of primitive datatypes (long, int, etc.) is preferred. This among others because of the fact that any datatype in a database can be null, while a primitive in Java cannot be null at all. That would cause problems if you for example are using an integer field in the database which allows null, while you're mapping it against int in Java.




package com.example.model;

import java.io.Serializable;
import java.util.Date;

/**
* This class represents the User model. This model class can be used thoroughout all
* layers, the data layer, the controller layer and the view layer.
*
* @author BalusC
* @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
*/

public class User implements Serializable {

// Constants ----------------------------------------------------------------------------------

private static final long serialVersionUID = 1L;

// Properties ---------------------------------------------------------------------------------

private Long id;
private String email;
private String password;
private String firstname;
private String lastname;
private Date birthdate;

// Getters/setters ----------------------------------------------------------------------------

public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public String getPassword() {
return password;
}

public void setPassword(String password) {
this.password = password;
}

public String getFirstname() {
return firstname;
}

public void setFirstname(String firstname) {
this.firstname = firstname;
}

public String getLastname() {
return lastname;
}

public void setLastname(String lastname) {
this.lastname = lastname;
}

public Date getBirthdate() {
return birthdate;
}

public void setBirthdate(Date birthdate) {
this.birthdate = birthdate;
}

// Object overrides ---------------------------------------------------------------------------

/**
* The user ID is unique for each User. So this should compare User by ID only.
* @see java.lang.Object#equals(java.lang.Object)
*/

@Override
public boolean equals(Object other) {
return (other instanceof User) && (id != null)
? id.equals(((User) other).id)
: (other == this);
}

/**
* The user ID is unique for each User. So User with same ID should return same hashcode.
* @see java.lang.Object#hashCode()
*/

@Override
public int hashCode() {
return (id != null)
? (this.getClass().hashCode() + id.hashCode())
: super.hashCode();
}

/**
* Returns the String representation of this User. Not required, it just pleases reading logs.
* @see java.lang.Object#toString()
*/

@Override
public String toString() {
return String.format("User[id=%d,email=%s,firstname=%s,lastname=%s,birthdate=%s]",
id, email, firstname, lastname, birthdate);
}

}


Back to top


Handling DAO exceptions



To 'hide' the specific implementation code of the data layer (e.g. JDBC, Hibernate or JPA), it may be useful to wrap their (runtime) exceptions with DAO specific exception classes. So after extending java.lang.RuntimeException the usual way you should get something like as the following classes:




package com.example.dao;

/**
* This class represents a generic DAO exception. It should wrap any exception of the underlying
* code, such as SQLExceptions.
*
* @author BalusC
* @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
*/

public class DAOException extends RuntimeException {

// Constants ----------------------------------------------------------------------------------

private static final long serialVersionUID = 1L;

// Constructors -------------------------------------------------------------------------------

/**
* Constructs a DAOException with the given detail message.
* @param message The detail message of the DAOException.
*/

public DAOException(String message) {
super(message);
}

/**
* Constructs a DAOException with the given root cause.
* @param cause The root cause of the DAOException.
*/

public DAOException(Throwable cause) {
super(cause);
}

/**
* Constructs a DAOException with the given detail message and root cause.
* @param message The detail message of the DAOException.
* @param cause The root cause of the DAOException.
*/

public DAOException(String message, Throwable cause) {
super(message, cause);
}

}



package com.example.dao;

/**
* This class represents an exception in the DAO configuration which cannot be resolved at runtime,
* such as a missing resource in the classpath, a missing property in the properties file, etcetera.
*
* @author BalusC
* @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
*/

public class DAOConfigurationException extends RuntimeException {

// Constants ----------------------------------------------------------------------------------

private static final long serialVersionUID = 1L;

// Constructors -------------------------------------------------------------------------------

/**
* Constructs a DAOConfigurationException with the given detail message.
* @param message The detail message of the DAOConfigurationException.
*/

public DAOConfigurationException(String message) {
super(message);
}

/**
* Constructs a DAOConfigurationException with the given root cause.
* @param cause The root cause of the DAOConfigurationException.
*/

public DAOConfigurationException(Throwable cause) {
super(cause);
}

/**
* Constructs a DAOConfigurationException with the given detail message and root cause.
* @param message The detail message of the DAOConfigurationException.
* @param cause The root cause of the DAOConfigurationException.
*/

public DAOConfigurationException(String message, Throwable cause) {
super(message, cause);
}

}


Back to top


Properties file



To access a database using JDBC you need at least information about the JDBC URL of the database to be connected, the class name of the JDBC driver to be loaded and the username and password to login to the database. Instead of the JDBC URL and the JDBC driver class name, you can also use only the JNDI name of the container managed datasource to be used. Those parameters must be stored somewhere that the Java code can access it and that you can easily maintain it externally. A common practice is to put those kind of configuration settings in a properties file and to place the properties file in the classpath so that the Java code can access it.



It is useful to keep a single properties file for multiple database configuration settings. To separate the database specific settings it is useful to use a specific key prefix. In this case we'll take the database name as specific key prefix.



Here is how the file dao.properties look like:




javabase.jdbc.url = jdbc:mysql://localhost:3306/javabase
javabase.jdbc.driver = com.mysql.jdbc.Driver
javabase.jdbc.username = java
javabase.jdbc.password = d$7hF_r!9Y


Put it somewhere in the default classpath (e.g. the root package) or specify its path in the -classpath parameter when running the Java application. In case of a webapplication you may consider to place it outside the WAR (the webapp project) so that you can edit it without the need to redeploy (only an appserver/webapp restart is enough). In such case you need to add its path to the (shared) classpath of the application server in question. In for example Apache Tomcat 6.0, you can specify it in the shared.loader property of the catalina.properties file in the Tomcat/conf folder.



Back to top


Properties file loader



We can use the java.util.Properties API to load those properties. We only want to wrap it in another class so that it can take a specific key prefix in the constructor. We also want to wrap the getProperty() so that it takes a boolean parameter 'mandatory' so that it throws a DAOConfigurationException when the property value is null or empty while it is mandatory. Also, instead of loading the properties file from the disk everytime, we want to load it once and keep the properties file in the memory.



Note: it depends on how often you think that this file changes in your environment, if it changes only once per year, then it is really not worth that to load it from disk everytime, but if it changes for example every day, then it might be worth to add a static method which reloads the properties file and execute it by some (scheduled) background job.




package com.example.dao;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

/**
* This class immediately loads the DAO properties file 'dao.properties' once in memory and provides
* a constructor which takes the specific key which is to be used as property key prefix of the DAO
* properties file. There is a property getter which only returns the property prefixed with
* 'specificKey.' and provides the option to indicate whether the property is mandatory or not.
*
* @author BalusC
* @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
*/

public class DAOProperties {

// Constants ----------------------------------------------------------------------------------

private static final String PROPERTIES_FILE = "dao.properties";
private static final Properties PROPERTIES = new Properties();

static {
ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
InputStream propertiesFile = classLoader.getResourceAsStream(PROPERTIES_FILE);

if (propertiesFile == null) {
throw new DAOConfigurationException(
"Properties file '" + PROPERTIES_FILE + "' is missing in classpath.");
}

try {
PROPERTIES.load(propertiesFile);
} catch (IOException e) {
throw new DAOConfigurationException(
"Cannot load properties file '" + PROPERTIES_FILE + "'.", e);
}
}

// Vars ---------------------------------------------------------------------------------------

private String specificKey;

// Constructors -------------------------------------------------------------------------------

/**
* Construct a DAOProperties instance for the given specific key which is to be used as property
* key prefix of the DAO properties file.
* @param specificKey The specific key which is to be used as property key prefix.
* @throws DAOConfigurationException During class initialization if the DAO properties file is
* missing in the classpath or cannot be loaded.
*/

public DAOProperties(String specificKey) throws DAOConfigurationException {
this.specificKey = specificKey;
}

// Actions ------------------------------------------------------------------------------------

/**
* Returns the DAOProperties instance specific property value associated with the given key with
* the option to indicate whether the property is mandatory or not.
* @param key The key to be associated with a DAOProperties instance specific value.
* @param mandatory Sets whether the returned property value should not be null nor empty.
* @return The DAOProperties instance specific property value associated with the given key.
* @throws DAOConfigurationException If the returned property value is null or empty while
* it is mandatory.
*/

public String getProperty(String key, boolean mandatory) throws DAOConfigurationException {
String fullKey = specificKey + "." + key;
String property = PROPERTIES.getProperty(fullKey);

if (property == null || property.trim().length() == 0) {
if (mandatory) {
throw new DAOConfigurationException("Required property '" + fullKey + "'"
+ " is missing in properties file '" + PROPERTIES_FILE + "'.");
} else {
// Make empty value null. Empty Strings are evil.
property = null;
}
}

return property;
}

}


Back to top


DAO factory



And now the 'main' class of the DAO package. You can use getInstance() to obtain an instance for the given database name, which on its turn is to be used as specific key for the properties file. The specific instance returned depends on the properties file configuration. Then you can obtain DAO's for the database instance using the DAO getters.



It has a package private getConnection() method which returns a connection to the database. The DAO classes in the same package can acquire the connection using that method. If you specified both the URL and the driver in the properties file, then it would load the driver and make use of a java.sql.DriverManager#getConnection() call to return the connection. If you specified only the URL in the properties file, it will be assumed as JNDI name of the datasource and make use of javax.sql.DataSource#getConnection() call to return the connection. With a DataSource you can easily make use of connection pooling to improve performance. If you're using for example Apache Tomcat as application server, then you can find here how to configure a JNDI datasource with connection pooling capabilities in Tomcat: Apache Tomcat 6.0 JNDI Datasource HOW-TO. You can find a complete example in the before-last chapter: How about connection pooling?.




package com.example.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
* This class represents a DAO factory for a SQL database. You can use {@link #getInstance(String)}
* to obtain a new instance for the given database name. The specific instance returned depends on
* the properties file configuration. You can obtain DAO's for the DAO factory instance using the
* DAO getters.
*


* This class requires a properties file named 'dao.properties' in the classpath with among others
* the following properties:
*


* name.url *
* name.driver
* name.username
* name.password
*

* Those marked with * are required, others are optional and can be left away or empty. Only the
* username is required when any password is specified.
*

    *
  • The 'name' must represent the database name in {@link #getInstance(String)}.

  • *
  • The 'name.url' must represent either the JDBC URL or JNDI name of the database.

  • *
  • The 'name.driver' must represent the full qualified class name of the JDBC driver.

  • *
  • The 'name.username' must represent the username of the database login.

  • *
  • The 'name.password' must represent the password of the database login.

  • *

* If you specify the driver property, then the url property will be assumed as JDBC URL. If you
* omit the driver property, then the url property will be assumed as JNDI name. When using JNDI
* with username/password preconfigured, you can omit the username and password properties as well.
*


* Here are basic examples of valid properties for a database with the name 'javabase':
*


* javabase.jdbc.url = jdbc:mysql://localhost:3306/javabase
* javabase.jdbc.driver = com.mysql.jdbc.Driver
* javabase.jdbc.username = java
* javabase.jdbc.password = d$7hF_r!9Y
*

*

* javabase.jndi.url = jdbc/javabase
*

* Here is a basic use example:
*

* DAOFactory javabase = DAOFactory.getInstance("javabase.jdbc");
* UserDAO userDAO = javabase.getUserDAO();
*

*
* @author BalusC
* @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
*/

public abstract class DAOFactory {

// Constants ----------------------------------------------------------------------------------

private static final String PROPERTY_URL = "url";
private static final String PROPERTY_DRIVER = "driver";
private static final String PROPERTY_USERNAME = "username";
private static final String PROPERTY_PASSWORD = "password";

// Actions ------------------------------------------------------------------------------------

/**
* Returns a new DAOFactory instance for the given database name.
* @param name The database name to return a new DAOFactory instance for.
* @return A new DAOFactory instance for the given database name.
* @throws DAOConfigurationException If the database name is null, or if the properties file is
* missing in the classpath or cannot be loaded, or if a required property is missing in the
* properties file, or if either the driver cannot be loaded or the datasource cannot be found.
*/

public static DAOFactory getInstance(String name) throws DAOConfigurationException {
if (name == null) {
throw new DAOConfigurationException("Database name is null.");
}

DAOProperties properties = new DAOProperties(name);
String url = properties.getProperty(PROPERTY_URL, true);
String driverClassName = properties.getProperty(PROPERTY_DRIVER, false);
String password = properties.getProperty(PROPERTY_PASSWORD, false);
String username = properties.getProperty(PROPERTY_USERNAME, password != null);
DAOFactory instance;

// If driver is specified, then load it to let it register itself with DriverManager.
if (driverClassName != null) {
try {
Class.forName(driverClassName);
} catch (ClassNotFoundException e) {
throw new DAOConfigurationException(
"Driver class '" + driverClassName + "' is missing in classpath.", e);
}
instance = new DriverManagerDAOFactory(url, username, password);
}

// Else assume URL as DataSource URL and lookup it in the JNDI.
else {
DataSource dataSource;
try {
dataSource = (DataSource) new InitialContext().lookup(url);
} catch (NamingException e) {
throw new DAOConfigurationException(
"DataSource '" + url + "' is missing in JNDI.", e);
}
if (username != null) {
instance = new DataSourceWithLoginDAOFactory(dataSource, username, password);
} else {
instance = new DataSourceDAOFactory(dataSource);
}
}

return instance;
}

/**
* Returns a connection to the database. Package private so that it can be used inside the DAO
* package only.
* @return A connection to the database.
* @throws SQLException If acquiring the connection fails.
*/

abstract Connection getConnection() throws SQLException;

// DAO implementation getters -----------------------------------------------------------------

/**
* Returns the User DAO associated with the current DAOFactory.
* @return The User DAO associated with the current DAOFactory.
*/

public UserDAO getUserDAO() {
return new UserDAOJDBC(this);
}

// You can add more DAO implementation getters here.

}

// Default DAOFactory implementations -------------------------------------------------------------

/**
* The DriverManager based DAOFactory.
*/

class DriverManagerDAOFactory extends DAOFactory {
private String url;
private String username;
private String password;

DriverManagerDAOFactory(String url, String username, String password) {
this.url = url;
this.username = username;
this.password = password;
}

@Override
Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
}

/**
* The DataSource based DAOFactory.
*/

class DataSourceDAOFactory extends DAOFactory {
private DataSource dataSource;

DataSourceDAOFactory(DataSource dataSource) {
this.dataSource = dataSource;
}

@Override
Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
}

/**
* The DataSource-with-Login based DAOFactory.
*/

class DataSourceWithLoginDAOFactory extends DAOFactory {
private DataSource dataSource;
private String username;
private String password;

DataSourceWithLoginDAOFactory(DataSource dataSource, String username, String password) {
this.dataSource = dataSource;
this.username = username;
this.password = password;
}

@Override
Connection getConnection() throws SQLException {
return dataSource.getConnection(username, password);
}
}


Back to top


User DAO interface



Now we need to define a DAO interface which defines all actions which needs to be performed on the User model class. All DAO implementations (JDBC, Hibernate, JPA, etc) should then adhere the contract as specified by this interface. A concrete implementation of the DAO interface should only be obtained from the DAO factory and you should use it as a central point for the mapping between the User model and a SQL database. When you want to add new methods, then you should specify them in the interface first.




package com.example.dao;

import java.util.List;

import com.example.model.User;

/**
* This interface represents a contract for a DAO for the {@link User} model.
* Note that all methods which returns the {@link User} from the DB, will not
* fill the model with the password, due to security reasons.
*
* @author BalusC
* @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
*/

public interface UserDAO {

// Actions ------------------------------------------------------------------------------------

/**
* Returns the user from the database matching the given ID, otherwise null.
* @param id The ID of the user to be returned.
* @return The user from the database matching the given ID, otherwise null.
* @throws DAOException If something fails at database level.
*/

public User find(Long id) throws DAOException;

/**
* Returns the user from the database matching the given email and password, otherwise null.
* @param email The email of the user to be returned.
* @param password The password of the user to be returned.
* @return The user from the database matching the given email and password, otherwise null.
* @throws DAOException If something fails at database level.
*/

public User find(String email, String password) throws DAOException;

/**
* Returns a list of all users from the database ordered by user ID. The list is never null and
* is empty when the database does not contain any user.
* @return A list of all users from the database ordered by user ID.
* @throws DAOException If something fails at database level.
*/

public List list() throws DAOException;

/**
* Create the given user in the database. The user ID must be null, otherwise it will throw
* IllegalArgumentException. After creating, the DAO will set the obtained ID in the given user.
* @param user The user to be created in the database.
* @throws IllegalArgumentException If the user ID is not null.
* @throws DAOException If something fails at database level.
*/

public void create(User user) throws IllegalArgumentException, DAOException;

/**
* Update the given user in the database. The user ID must not be null, otherwise it will throw
* IllegalArgumentException. Note: the password will NOT be updated. Use changePassword() instead.
* @param user The user to be updated in the database.
* @throws IllegalArgumentException If the user ID is null.
* @throws DAOException If something fails at database level.
*/

public void update(User user) throws IllegalArgumentException, DAOException;

/**
* Delete the given user from the database. After deleting, the DAO will set the ID of the given
* user to null.
* @param user The user to be deleted from the database.
* @throws DAOException If something fails at database level.
*/

public void delete(User user) throws DAOException;

/**
* Returns true if the given email address exist in the database.
* @param email The email address which is to be checked in the database.
* @return True if the given email address exist in the database.
* @throws DAOException If something fails at database level.
*/

public boolean existEmail(String email) throws DAOException;

/**
* Change the password of the given user. The user ID must not be null, otherwise it will throw
* IllegalArgumentException.
* @param user The user to change the password for.
* @throws IllegalArgumentException If the user ID is null.
* @throws DAOException If something fails at database level.
*/

public void changePassword(User user) throws DAOException;

}


Back to top


User DAO implementation



And here is where it all happens. This is a concrete JDBC implementation of the User DAO interface. It has a package private constructor so that only the DAOFactory can construct it and associate the DAO class with itself. Another good practice is to acquire and close the Connection, (Prepared)Statement and ResultSet in the shortest possible scope. This DAO class shows how to do it properly, using the package private DAOFactory#getConnection() method. Closing should happen in the finally block in the reversed order as you have acquired them. Otherwise an (unexpected) exception would cause the resources left open. When you don't close the resources properly, they may be kept open for too long and the database would run out of them on long term (for example in a lifetime long running Java EE web application!) and the whole application would crash.



Also using PreparedStatement has strong preference over using Statement as it is faster, eases setting of Java objects (e.g. Date and InputStream) in a SQL query and, most importantingly, it saves your logic from SQL injection attacks, especially when the query data can be controlled by client input.




package com.example.dao;

import static com.example.dao.DAOUtil.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.example.model.User;

/**
* This class represents a concrete JDBC implementation of the {@link UserDAO} interface.
*
* @author BalusC
* @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
*/

public class UserDAOJDBC implements UserDAO {

// Constants ----------------------------------------------------------------------------------

private static final String SQL_FIND_BY_ID =
"SELECT id, email, firstname, lastname, birthdate FROM User WHERE id = ?";
private static final String SQL_FIND_BY_EMAIL_AND_PASSWORD =
"SELECT id, email, firstname, lastname, birthdate FROM User WHERE email = ? AND password = MD5(?)";
private static final String SQL_LIST_ORDER_BY_ID =
"SELECT id, email, firstname, lastname, birthdate FROM User ORDER BY id";
private static final String SQL_INSERT =
"INSERT INTO User (email, password, firstname, lastname, birthdate) VALUES (?, MD5(?), ?, ?, ?)";
private static final String SQL_UPDATE =
"UPDATE User SET email = ?, firstname = ?, lastname = ?, birthdate = ? WHERE id = ?";
private static final String SQL_DELETE =
"DELETE FROM User WHERE id = ?";
private static final String SQL_EXIST_EMAIL =
"SELECT id FROM User WHERE email = ?";
private static final String SQL_CHANGE_PASSWORD =
"UPDATE User SET password = MD5(?) WHERE id = ?";

// Vars ---------------------------------------------------------------------------------------

private DAOFactory daoFactory;

// Constructors -------------------------------------------------------------------------------

/**
* Construct an User DAO for the given DAOFactory. Package private so that it can be constructed
* inside the DAO package only.
* @param daoFactory The DAOFactory to construct this User DAO for.
*/

UserDAOJDBC(DAOFactory daoFactory) {
this.daoFactory = daoFactory;
}

// Actions ------------------------------------------------------------------------------------

@Override
public User find(Long id) throws DAOException {
return find(SQL_FIND_BY_ID, id);
}

@Override
public User find(String email, String password) throws DAOException {
return find(SQL_FIND_BY_EMAIL_AND_PASSWORD, email, password);
}

/**
* Returns the user from the database matching the given SQL query with the given values.
* @param sql The SQL query to be executed in the database.
* @param values The PreparedStatement values to be set.
* @return The user from the database matching the given SQL query with the given values.
* @throws DAOException If something fails at database level.
*/

private User find(String sql, Object... values) throws DAOException {
User user = null;

try (
Connection connection = daoFactory.getConnection();
PreparedStatement statement = prepareStatement(connection, sql, false, values);
ResultSet resultSet = statement.executeQuery();
) {
if (resultSet.next()) {
user = map(resultSet);
}
} catch (SQLException e) {
throw new DAOException(e);
}

return user;
}

@Override
public List list() throws DAOException {
List users = new ArrayList<>();

try (
Connection connection = daoFactory.getConnection();
PreparedStatement statement = connection.prepareStatement(SQL_LIST_ORDER_BY_ID);
ResultSet resultSet = statement.executeQuery();
) {
while (resultSet.next()) {
users.add(map(resultSet));
}
} catch (SQLException e) {
throw new DAOException(e);
}

return users;
}

@Override
public void create(User user) throws IllegalArgumentException, DAOException {
if (user.getId() != null) {
throw new IllegalArgumentException("User is already created, the user ID is not null.");
}

Object[] values = {
user.getEmail(),
user.getPassword(),
user.getFirstname(),
user.getLastname(),
toSqlDate(user.getBirthdate())
};

try (
Connection connection = daoFactory.getConnection();
PreparedStatement statement = prepareStatement(connection, SQL_INSERT, true, values);
) {
int affectedRows = statement.executeUpdate();
if (affectedRows == 0) {
throw new DAOException("Creating user failed, no rows affected.");
}

try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
if (generatedKeys.next()) {
user.setId(generatedKeys.getLong(1));
} else {
throw new DAOException("Creating user failed, no generated key obtained.");
}
}
} catch (SQLException e) {
throw new DAOException(e);
}
}

@Override
public void update(User user) throws DAOException {
if (user.getId() == null) {
throw new IllegalArgumentException("User is not created yet, the user ID is null.");
}

Object[] values = {
user.getEmail(),
user.getFirstname(),
user.getLastname(),
toSqlDate(user.getBirthdate()),
user.getId()
};

try (
Connection connection = daoFactory.getConnection();
PreparedStatement statement = prepareStatement(connection, SQL_UPDATE, false, values);
) {
int affectedRows = statement.executeUpdate();
if (affectedRows == 0) {
throw new DAOException("Updating user failed, no rows affected.");
}
} catch (SQLException e) {
throw new DAOException(e);
}
}

@Override
public void delete(User user) throws DAOException {
Object[] values = {
user.getId()
};

try (
Connection connection = daoFactory.getConnection();
PreparedStatement statement = prepareStatement(connection, SQL_DELETE, false, values);
) {
int affectedRows = statement.executeUpdate();
if (affectedRows == 0) {
throw new DAOException("Deleting user failed, no rows affected.");
} else {
user.setId(null);
}
} catch (SQLException e) {
throw new DAOException(e);
}
}

@Override
public boolean existEmail(String email) throws DAOException {
Object[] values = {
email
};

boolean exist = false;

try (
Connection connection = daoFactory.getConnection();
PreparedStatement statement = prepareStatement(connection, SQL_EXIST_EMAIL, false, values);
ResultSet resultSet = statement.executeQuery();
) {
exist = resultSet.next();
} catch (SQLException e) {
throw new DAOException(e);
}

return exist;
}

@Override
public void changePassword(User user) throws DAOException {
if (user.getId() == null) {
throw new IllegalArgumentException("User is not created yet, the user ID is null.");
}

Object[] values = {
user.getPassword(),
user.getId()
};

try (
Connection connection = daoFactory.getConnection();
PreparedStatement statement = prepareStatement(connection, SQL_CHANGE_PASSWORD, false, values);
) {
int affectedRows = statement.executeUpdate();
if (affectedRows == 0) {
throw new DAOException("Changing password failed, no rows affected.");
}
} catch (SQLException e) {
throw new DAOException(e);
}
}

// Helpers ------------------------------------------------------------------------------------

/**
* Map the current row of the given ResultSet to an User.
* @param resultSet The ResultSet of which the current row is to be mapped to an User.
* @return The mapped User from the current row of the given ResultSet.
* @throws SQLException If something fails at database level.
*/

private static User map(ResultSet resultSet) throws SQLException {
User user = new User();
user.setId(resultSet.getLong("id"));
user.setEmail(resultSet.getString("email"));
user.setFirstname(resultSet.getString("firstname"));
user.setLastname(resultSet.getString("lastname"));
user.setBirthdate(resultSet.getDate("birthdate"));
return user;
}

}


Back to top


DAO utility class



Repeated and/or standardized code is best to be refactored into an utility class (a final and non-constructable class with only static methods). This also applies on DAO code for which we create the following class.




package com.example.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
* Utility class for DAO's. This class contains commonly used DAO logic which is been refactored in
* single static methods. As far it contains a PreparedStatement values setter and a
* java.util.Date to java.sql.Date converter.
*
* @author BalusC
* @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
*/

public final class DAOUtil {

// Constructors -------------------------------------------------------------------------------

private DAOUtil() {
// Utility class, hide constructor.
}

// Actions ------------------------------------------------------------------------------------

/**
* Returns a PreparedStatement of the given connection, set with the given SQL query and the
* given parameter values.
* @param connection The Connection to create the PreparedStatement from.
* @param sql The SQL query to construct the PreparedStatement with.
* @param returnGeneratedKeys Set whether to return generated keys or not.
* @param values The parameter values to be set in the created PreparedStatement.
* @throws SQLException If something fails during creating the PreparedStatement.
*/

public static PreparedStatement prepareStatement
(Connection connection, String sql, boolean returnGeneratedKeys, Object... values)
throws SQLException
{
PreparedStatement statement = connection.prepareStatement(sql,
returnGeneratedKeys ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS);
setValues(statement, values);
return statement;
}

/**
* Set the given parameter values in the given PreparedStatement.
* @param connection The PreparedStatement to set the given parameter values in.
* @param values The parameter values to be set in the created PreparedStatement.
* @throws SQLException If something fails during setting the PreparedStatement values.
*/

public static void setValues(PreparedStatement statement, Object... values)
throws SQLException
{
for (int i = 0; i < values.length; i++) {
statement.setObject(i + 1, values[i]);
}
}

/**
* Converts the given java.util.Date to java.sql.Date.
* @param date The java.util.Date to be converted to java.sql.Date.
* @return The converted java.sql.Date.
*/

public static Date toSqlDate(java.util.Date date) {
return (date != null) ? new Date(date.getTime()) : null;
}

}


Back to top


DAO test harness



And now finally the test harness for all classes in the DAO package. This is just a simple class with a main method which can be runt as a Java application in the command console or in the IDE. Everytime when you change the data layer it is useful to use this test harness to test the data layer to verify if nothing has been broken or if newly added stuff (add it to the test harness code then) functions correctly.




package com.example;

import java.text.SimpleDateFormat;
import java.util.List;

import com.example.dao.DAOFactory;
import com.example.dao.UserDAO;
import com.example.model.User;

/**
* Test harness for the com.example.dao package. This require the following preconditions:
*

    *
  1. A MySQL server running at localhost:3306 with a database named 'javabase'.
    *
  2. A 'user' table in the 'javabase' database which is created as follows:
    *

    * CREATE TABLE javabase.user (
    * id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    * email VARCHAR(60) NOT NULL,
    * password VARCHAR(32) NOT NULL,
    * firstname VARCHAR(40) NULL,
    * lastname VARCHAR(40) NULL,
    * birthdate DATE NULL,
    *
    * PRIMARY KEY (id),
    * UNIQUE (email)
    * )
    *

    *
  3. A MySQL user with the name 'java' and password 'd$7hF_r!9Y' which has sufficient rights on
    * the javabase.user table.
    *
  4. A MySQL JDBC Driver JAR file in the classpath.
    *
  5. A properties file 'dao.properties' in the classpath with the following entries:
    *

    * javabase.jdbc.driver = com.mysql.jdbc.Driver
    * javabase.jdbc.url = jdbc:mysql://localhost:3306/javabase
    * javabase.jdbc.username = java
    * javabase.jdbc.password = d$7hF_r!9Y
    *

    *

*
* @author BalusC
* @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
*/

public class DAOTest {

public static void main(String[] args) throws Exception {
// Obtain DAOFactory.
DAOFactory javabase = DAOFactory.getInstance("javabase.jdbc");
System.out.println("DAOFactory successfully obtained: " + javabase);

// Obtain UserDAO.
UserDAO userDAO = javabase.getUserDAO();
System.out.println("UserDAO successfully obtained: " + userDAO);

// Create user.
User user = new User();
user.setEmail("foo@bar.com");
user.setPassword("password");
userDAO.create(user);
System.out.println("User successfully created: " + user);

// Create another user.
User anotherUser = new User();
anotherUser.setEmail("bar@foo.com");
anotherUser.setPassword("anotherPassword");
anotherUser.setFirstname("Bar");
anotherUser.setLastname("Foo");
anotherUser.setBirthdate(new SimpleDateFormat("yyyy-MM-dd").parse("1978-03-26"));
userDAO.create(anotherUser);
System.out.println("Another user successfully created: " + anotherUser);

// Update user.
user.setFirstname("Foo");
user.setLastname("Bar");
userDAO.update(user);
System.out.println("User successfully updated: " + user);

// Update user.
user.setFirstname("Foo");
user.setLastname("Bar");
userDAO.update(user);
System.out.println("User successfully updated: " + user);

// List all users.
List users = userDAO.list();
System.out.println("List of users successfully queried: " + users);
System.out.println("Thus, amount of users in database is: " + users.size());

// Delete user.
userDAO.delete(user);
System.out.println("User successfully deleted: " + user);

// Check if email exists.
boolean exist = userDAO.existEmail("foo@bar.com");
System.out.println("This email should not exist anymore, so this should print false: " + exist);

// Change password.
anotherUser.setPassword("newAnotherPassword");
userDAO.changePassword(anotherUser);
System.out.println("Another user's password successfully changed: " + anotherUser);

// Get another user by email and password.
User foundAnotherUser = userDAO.find("bar@foo.com", "newAnotherPassword");
System.out.println("Another user successfully queried with new password: " + foundAnotherUser);

// Delete another user.
userDAO.delete(foundAnotherUser);
System.out.println("Another user successfully deleted: " + foundAnotherUser);

// List all users again.
users = userDAO.list();
System.out.println("List of users successfully queried: " + users);
System.out.println("Thus, amount of users in database is: " + users.size());
}

}


On a fresh and empty database table this test harness should produce the following output (slightly changed with newlines to fit on this blog article's screen):




DAOFactory successfully obtained: com.example.dao.DriverManagerDAOFactory@74c74b55

UserDAO successfully obtained: com.example.dao.UserDAOJDBC@5b0f3bd7

User successfully created:

    User[id=1,email=foo@bar.com,firstname=null,lastname=null,birthdate=null]

Another user successfully created:

    User[id=2,email=bar@foo.com,firstname=Bar,lastname=Foo,birthdate=Sun Mar 26 00:00:00 GMT-04:00 1978]

User successfully updated:

    User[id=1,email=foo@bar.com,firstname=Foo,lastname=Bar,birthdate=null]

User successfully updated:

    User[id=1,email=foo@bar.com,firstname=Foo,lastname=Bar,birthdate=null]

List of users successfully queried:

    [User[id=1,email=foo@bar.com,firstname=Foo,lastname=Bar,birthdate=null],

    User[id=8,email=bar@foo.com,firstname=Bar,lastname=Foo,birthdate=1978-03-26]]

Thus, amount of users in database is: 2

User successfully deleted:

    User[id=null,email=foo@bar.com,firstname=Foo,lastname=Bar,birthdate=null]

This email should not exist anymore, so this should print false: false

Another user's password successfully changed:

    User[id=2,email=bar@foo.com,firstname=Bar,lastname=Foo,birthdate=Sun Mar 26 00:00:00 GMT-04:00 1978]

Another user successfully queried with new password:

    User[id=2,email=bar@foo.com,firstname=Bar,lastname=Foo,birthdate=1978-03-26]

Another user successfully deleted:

    User[id=null,email=bar@foo.com,firstname=Bar,lastname=Foo,birthdate=1978-03-26]

List of users successfully queried: []

Thus, amount of users in database is: 0



You see, it successfully creates the user (the user ID is been set by DAO), finds the user, updates the user, lists the users, deletes the user (the user ID is nulled out by DAO), checks the email and changes the password.



Back to top


Further exercises



To extend your data layer further, you may consider to extract interfaces from the DAOFactory and all DAO classes such as UserDAO, rename the existing implementations to DaoFactoryJDBC and UserDAOJDBC and write another implementations for those interfaces (DaoFactoryHibernate and UserDAOHibernate maybe?). You can also decide to let all your DAO interfaces extend a single base interface and replace all DAO getters in the DAOFactory by a single getter, something like:




public extends BaseDAO> DAO getDAOImpl(Class daoInterface)
throws DAOConfigurationException
{
String daoInterfaceName = daoInterface.getName();

if (!daoInterface.isInterface()) {
throw new DAOConfigurationException("Class '" + daoInterfaceName + "'"
+ " is actually not an Interface.");
}

String daoClassName = daoProperties.getProperty(daoInterfaceName, true);
DAO daoImplementation;

try {
daoImplementation = daoInterface.cast(Class.forName(daoClassName).newInstance());
} catch (ClassNotFoundException e) {
throw new DAOConfigurationException("DAO class '" + daoClassName
+ "' is missing in classpath. Verify the class or the '" + daoInterfaceName
+ "' property.", e);
} catch (IllegalAccessException e) {
throw new DAOConfigurationException("DAO class '" + daoClassName
+ "' cannot be accessed. Verify the class or the '" + daoInterfaceName
+ "' property.", e);
} catch (InstantiationException e) {
throw new DAOConfigurationException("DAO class '" + daoClassName
+ "' cannot be instantiated. Verify the class or the '" + daoInterfaceName
+ "' property.", e);
} catch (ClassCastException e) {
throw new DAOConfigurationException("DAO class '" + daoClassName
+ "' does not implement '" + daoInterfaceName + "'. Verify the class or the '"
+ daoInterfaceName + "' property.", e);
}

daoImplementation.setDAOFactory(this);

return daoImplementation;
}



UserDAO userDAO = daoFactory.getDAOImpl(UserDAO.class);


Back to top


How about connection pooling?



Connecting the database is a fairly expensive task which can consume up to around 200ms of time. If your application needs to run a lot of queries, then it is really worth to consider connection pooling to improve performance. The idea of connection pooling is just an extra abstract layer between the existing JDBC code and the database which keeps the connections open for (re)use until it is inactive / timed-out by the database. Acquiring an already opened connection from the pool can be as fast as one millisecond or two.



There should be no need to change the existing JDBC code (the DAO classes). Only the way to acquire the connection will differ. In general a JNDI datasource is used to acquire a connection from the connection pool. Decent application servers ships with connection pool implementations. Apache Tomcat for example uses Apache Commons DBCP under the hood.



As explained in the DAO factory chapter, if you omit the driver property in the properties file, then the url property will be assumed as JNDI name of the datasource and the DAOFactory will make use of javax.sql.DataSource#getConnection() call to return the connection. With a DataSource you can easily make use of connection pooling to improve performance. So the dao.properties file should look like this:




javabase.jndi.url = java:comp/env/jdbc/javabase


NOTE: in full fledged application servers like Glassfish, the default JNDI root is the java:comp/env, but in Tomcat it is not. Hence the java:comp/env in the JNDI URL. If you're using for example Glassfish, you should remove that part:




javabase.jndi.url = jdbc/javabase


Here the jdbc/javabase part should be the JNDI name of the datasource. You can obtain the associated DAOFactory as follows:


DAOFactory javabase = DAOFactory.getInstance("javabase.jndi");


How to configure the datasource depends on the server used. In case of Tomcat, you normally define the datasource in the context.xml file. You can do that at two different places. If you want to make the one and the same datasource available for all webapplications which runs at the server, then you need to define it in the general tomcat/conf/context.xml of the appserver. Its configuration is explained in Apache Tomcat 6.0 JNDI Datasource HOW-TO. But this requires that you've full control over Tomcat installation. An alternative is to give the webapplication its own context.xml file with the datasource definition in webapp/webcontent/META-INF/context.xml (to be clear, the META-INF is at the same level as the WEB-INF of the webapp). We'll assume the last approach. In case of the MySQL example in this tutorial, just put the following in the file:




version="1.0" encoding="UTF-8"?>



name="jdbc/javabase" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
url="jdbc:mysql://localhost:3306/javabase"
driverClassName="com.mysql.jdbc.Driver"
username="java" password="d$7hF_r!9Y"
/>



This basically tells Tomcat that it should create a datasource with the JNDI name jdbc/javabase with a maximum of 100 active connections, a maximum of 30 idle connections and a maximum wait time of 10000 milliseconds before a connection should be returned from your application (actually: closed by your application, so your application has 10 seconds time between acquiring the connection and closing the connection). The remnant of the settings should be familiar and self-explaining enough to you; those are the JDBC settings. For more details, read the Apache Tomcat 6.0 JNDI Datasource HOW-TO.



Finally in your web project, edit the file webapp/webcontent/WEB-INF/web.xml to add the following entry to the file:






jdbc/javabase
javax.sql.DataSource
Container




This basically tells the webapplication that is should use the datasource with the name jdbc/javabase which is managed by the container (thus, Tomcat).



That's all! Just deploy your webapplication with the above changes and run it. Oh, don't forget to place the database JDBC driver in the tomcat/lib.



Once again, no change in all of the DAO code is needed. Even not in the closing code. Heck, closing is still very important. It frees up the pooled connection and makes it available for reuse. Roughly said, the connection pool actually returns a wrapped implementation of the actual connection, where in the close() method is decorated like as the following pseudo code:





public void close() throws SQLException {
if (this.connection is still eligible for reuse) {
do not close this.connection, but just return it to pool for reuse;
} else {
actually invoke this.connection.close();
}
}



So, there's nothing to worry about in the DAO code. That's the nice thing of properties files and well-written object oriented code.



Back to top




Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair.


(C) July 2008, BalusC




Source:http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html