Unicode - How to get the characters right?

Fast menu Introduction Well, where does it go wrong? Unicode, what's it all about? OK .. So, I have an "Unicode problem", what now?    1) Development environment    2) Java properties files    3) JSP/Servlet request    4) JSP/Servlet response    5) Facelets request/response    6) Databases    7) Text files    8) Strings References

Introduction

Computers understand only bits and bytes. You know, the binary numeral system of zeros and ones. Humans, on the other hand, understand characters only. You know, the building blocks of the natural languages. So, to handle human readable characters using a computer (read, write, store, transfer, etcetera), they have to be converted to bytes. One byte is an ordered collection of eight zeros or ones (bits). The characters are only used for pure presentation to humans. Behind any character you see, there is a certain order of bits. For a computer a character is in fact nothing less or more than a simple graphical picture (a font) which has an unique "identifier" in form of a certain order of bits.

To convert between chars and bytes a computer needs a mapping where every unique character is associated with unique bytes. This mapping is also called the character encoding. The character encoding exist of basically two parts. The one is the character set (charset), which represents all of the unique characters. The other is the numeral representation of each of the characters of the charset. The numeral representation to humans is usually in hexadecimal, which is in turn easily to be "converted" to bytes (both are just numeral systems, only with a different base).

Character Encoding
Character set (human presentation)	Numeral representation (computer identification)
A	x0041 (01000001)
B	x0042 (01000010)
C	x0043 (01000011)
D	x0044 (01000100)
E	x0045 (01000101)
F	x0046 (01000110)
G	x0047 (01000111)
H	x0048 (01001000)
I	x0049 (01001001)
J	x004A (01001010)
K	x004B (01001011)
L	x004C (01001100)
M	x004D (01001101)
N	x004E (01001110)
O	x004F (01001111)
...	...

Back to top

Well, where does it go wrong?

The world would be much simpler if only one character encoding existed. That would have been clear enough for everyone. Unfortunately the truth is different. There are a lot of different character encodings, each with its own charsets and numeral mappings. So it may be obvious that a character which is converted to bytes using character encoding X may not be the same character when it is converted back from bytes using character encoding Y. That would in turn lead to confusion among humans, because they wouldn't understand the way the computer represented their natural language. Humans would see completely different characters and thus not be able to understand the "language" which is also known as the "mojibake". It can also happen that humans would not see any linguistic character at all, because the numeral representation of the character in question isn't covered by the numeral mapping of the character encoding used. It's simply unknown.

How such an unknown character is displayed differs per application which handles the character. In the webbrowser world, Firefox would display an unknown character as a black diamond with a question mark in it, while Internet Explorer would display it as an empty white square with a black border. Both represents the same Unicode character though: xFFFD, which is displayed in your webbrowser as "�". Internet Explorer simply doesn't have a font (a graphical picture) for it, hence the empty square. In Java/JSP/Servlet world, any unknown character which is passed through the write() methods of an OutputStream (e.g. the one obtained by ServletResponse#getOutputStream()) get printed as a plain question mark "?". Those question marks can in some cases also be caused by the database. Most database engines replaces uncovered numeral representations by a plain question mark during save (INSERT/UPDATE), which is in turn later displayed to the human when the data is been queried and sent to the webbrowser. The plain question marks are thus not per se caused by the webbrowser.

Here is a small test snippet which demonstrates the problem. Keep in mind that Java supports and uses Unicode all the time. So the encoding problem which you see in the output is not caused by Java itself, but by using the ISO 8859-1 character encoding to display Unicode characters. The ISO 8859-1 character encoding namely doesn't cover the numeral representations of a large part of the Unicode charset which is also known as the UTF-8 charset. By the way, the term "Unicode character" is nowhere defined, but it usually used by (unaware) programmers/users who actually meant "Any character which is not covered by the ISO 8859 character encoding".

package test;

public class Test {

    public static void main(String... args) throws Exception {
        String czech = "Český";
        String japanese = "日本語";

        System.out.println("UTF-8 czech: " + new String(czech.getBytes("UTF-8")));
        System.out.println("UTF-8 japanese: " + new String(japanese.getBytes("UTF-8")));

        System.out.println("ISO-8859-1 czech: " + new String(czech.getBytes("ISO-8859-1")));
        System.out.println("ISO-8859-1 japanese: " + new String(japanese.getBytes("ISO-8859-1")));
    }

}

UTF-8 czech: Český
UTF-8 japanese: 日本語
ISO-8859-1 czech: ?esk�
ISO-8859-1 japanese: ???

These kinds of problems are often referred to as the "Unicode problem".

Important note: your own operating system should of course have the proper fonts (yes, the human representations) supporting those Unicode charsets for both Czech and Japanese languages installed to see the proper characters/glyphs at this webpage :) Otherwise you will see in for example Firefox a black-bordered square with hexcode inside (0-9 and/or A-F) and in most other webbrowsers such as IE, Safari and Chrome a nothing-saying empty square with a black border. Below is a screenshot from Chrome which shows the right characters, so you can compare if necessary:

If your operating system for example doesn't have the Japanese glyphs in the font as required by this page, then you should in Firefox see three squares with hexcodes 65E5, 672C and 8A9E. Those hexcodes are actually also called 'Unicode codepoints'. In Windows, you can view all available fonts and the supported characters using 'charmap.exe' (Start > Run > charmap).

Another important note: if you have problems when copypasting the test snippet in your development environment (e.g. you are not seeing the proper characters, but only empty squares or something like), then please wait with playing until you have read the entire article, including step 1 of the OK .. So, I have an "Unicode problem", what now? chapter ;)

Back to top

Unicode, what's it all about?

Let's go back in the history of character encoding. Most of you may be familiar with the term "ASCII". This was less or more the first character encoding ever. At the ages when a byte was very expensive and 1MHz was extremely fast, only the characters which appeared on those ancient US typewriters (as well as at the average US International keyboard nowadays) were covered by the charset of the ASCII character encoding. This includes the complete Latin alphabet (A-Z, in both the lowercased and uppercased flavour), the numeral digits (0-9), the lexical control characters (space, dot, comma, colon, etcetera) and some special characters (the at sign, the sharp sign, the dollar sign, etcetera). All those characters fill up the space of 7 bits, half of the room a byte provides, with a total of 128 characters.

Later the remaining bit of a byte is used for Extended ASCII which provides room for a total of 255 characters. Most of the remaining room is used by special characters, such as diacritical characters and line drawing characters. Because everyone used the remaining room their own way (IBM, Commodore, Universities, etcetera), it was not interchangeable. Later ISO came up with standard character encoding definitions for 8 bit ASCII extensions, resulting in the known ISO 8859 character encoding standards such as ISO 8859-1.

8 bits may be enough for the languages using the Latin alphabet, but it is certainly not enough for the remaining non-Latin languages in the world, such as Chinese, Japanese, Hebrew, Cyrillic, Sanskrit, Arabic, etcetera. They developed their own non-ISO character encodings which was -again- not interchangeable, such as Guobiao, BIG5, JIS, KOI, MIK, TSCII, etcetera. Finally a new 16 bits character encoding standard based on top of ISO 8859-1 was established to cover any of the characters used at the world so that it is interchangeable everywhere: Unicode. You can find all of those linguistic characters here. Unicode also covers many special characters (symbols) such as punctuation and mathematical operators, which you can find here.

Back to top

OK .. So, I have an "Unicode problem", what now?

To the point: just ensure that you use UTF-8 (a character encoding which conforms the Unicode standard) all the way. There are more Unicode character encodings as well, but as far they are used very, very seldom. UTF-8 is likely the Unicode standard. To solve the "Unicode problem" you need to ensure that every step which involves byte-character conversion uses the one and the same character encoding: reading data from input stream, writing data to output stream, querying data from database, storing data in database, manipulating the data, displaying the data, etcetera. For a Java EE web developer, there are a lot of things you have to take into account.

1. Development environment: yes, the development environment has to use UTF-8 as well. By default most text files are saved using the operating system default encoding such as ISO 8859-1 or even an proprietary encoding such as Windows ANSI (also known as CP-1252, which is in turn not interchangeable with non-Windows platforms!). The most basic text editor of Windows, Notepad, uses Windows ANSI by default, but Notepad supports UTF-8 as well. To save a text file containing Unicode characters using Notepad, you need to choose the File » Save As option and select UTF-8 from the Encoding dropdown. The same Save As story applies on many other self-respected text editors as well, like EditPlus, UltraEdit and Notepad++.
   
   
   In an IDE such as Eclipse you can set the encoding at several places. You need to explore the IDE preferences thoroughly to find and change them. In case of Eclipse, just go to Window » Preferences and enter filter text encoding. In the filtered preferences (Workspace, JSP files, etcetera) you can select the desired encoding from a dropdown. Important note: the Workspace encoding also covers the output console and thus also the outcome of System.out.println(). If you sysout an Unicode character using the default encoding, it would likely be printed as a plain vanilla question mark!
   
   
   In the command console it is not possible.

   C:\Java>java test.Test
   UTF-8 czech: Český
   UTF-8 japanese: 日本語
   ISO-8859-1 czech: ─?esk├╜
   ISO-8859-1 japanese: µ?ѵ?¼Φ¬?
   
   C:\Java>_
   

   In theory, in the Windows command prompt you have to use a font which supports a broad range of Unicode characters. You can set the font by opening the command console (Start > Run > cmd), then clicking the small cmd icon at the left top, then choosing Properties and finally choosing the Font tab. In a default Windows environment only the Lucida Console font has the "best" support of Unicode fonts. It unfortunately lacks a broad range of Unicode characters though.
   
   The cmd.exe parameter \U and/or the command chcp 65001 (which changes the code page to UTF-8) doesn't help much if the font already doesn't support the desired characters. You could hack the registry to add more fonts, but you still have to find a specific command console font which supports all of the desired characters. In the end it's better to use Swing to create a command console like UI instead of using the standard command console. Especially if the application is intended to be distributed (you don't want to require the enduser to hack/change their environment to get your application to work, do you? ;) ).
   
   
2. Java properties files: as stated in its Javadoc the load(InputStream) method of the java.util.Properties API uses ISO 8859-1 as the default encoding. Here's an extract of the class' Javadoc:
   
   .. the input/output stream is encoded in ISO 8859-1 character encoding. Characters that cannot be directly represented in this encoding can be written using Unicode escapes ; only a single 'u' character is allowed in an escape sequence. The native2ascii tool can be used to convert property files to and from other character encodings.
   
   If you have full control over loading of the properties files, then you should use the Java 1.6 load(Reader) method in combination with an InputStreamReader instead:

   
   Properties properties = new Properties();
   properties.load(new InputStreamReader(classLoader.getResourceAsStream(filename), "UTF-8"));
   
   

   If you don't have full control over loading of the properties files (e.g. managed by some framework), then you need the in the Javadoc mentioned native2ascii tool. The native2ascii tool can be found in the /bin folder of the JDK installation directory. When you for example need to maintain properties files with Unicode characters for i18n (Internationalization; also known as resource bundles), then it's a good practice to have both an UTF-8 properties file and an ISO 8859-1 properties file and some batch program to convert from the UTF-8 properties file to an ISO 8859-1 properties file. You use the UTF-8 properties file for editing only. You use the converter to convert it to ISO 8859-1 properties file after every edit. You finally just leave the ISO 8859-1 properties file as it is. In most (smart) IDE's like Eclipse you cannot use the .properties extension for those UTF-8 properties files, it would complain about unknown characters because it is forced to save properties files in ISO 8859-1 format. Name it .properties.utf8 or something else. Here's an example of a simple Windows batch file which does the conversion task:

   
   cd c:\path\to\properties\files
   c:\path\to\jdk\bin\native2ascii.exe -encoding UTF-8 text_cs.properties.utf8 text_cs.properties
   c:\path\to\jdk\bin\native2ascii.exe -encoding UTF-8 text_ja.properties.utf8 text_ja.properties
   c:\path\to\jdk\bin\native2ascii.exe -encoding UTF-8 text_zh.properties.utf8 text_zh.properties
   # You can add more properties files here.
   
   

   Save it as utf8.converter.bat (or something like) and run it once to convert all UTF-8 properties files to standard ISO 8859-1 properties files. If you're using Maven and/or Ant, this can even be automated to take place during the build of the project.
   
   For JSF there are better ways using ResourceBundle.Control API. Check this blog article: Internationalization in JSF with UTF-8 properties files.
   
   
3. JSP/Servlet request: during request processing an average application server will by default use the ISO 8859-1 character encoding to URL-decode the request parameters. You need to force the character encoding to UTF-8 yourself. First this: "URL encoding" must not to be confused with "character encoding". URL encoding is merely a conversion of characters to their numeral representations in the %xx format, so that special characters can be passed through URL without any problems. The client will URL-encode the characters before sending them to the server. The server should URL-decode the characters using the same character encoding. Also see "percent encoding".
   
   How to configure this depends on the server used, so the best is to refer its documentation. In case of for example Tomcat you need to set the URIEncoding attribute of the <Connector> element in Tomcat's /conf/server.xml to set the character encoding of HTTP GET requests, also see this document:

   
   <Connector (...) URIEncoding="UTF-8" />
   
   

   In for example Glassfish you need to set the <parameter-encoding> entry in webapp's /WEB-INF/sun-web.xml (or, since Glassfish 3.1, glassfish-web.xml), see also this document:

   
   <parameter-encoding default-charset="UTF-8" />
   
   

   URL-decoding POST request parameters is a story apart. The webbrowser is namely supposed to send the charset used in the Content-Type request header. However, most webbrowsers doesn't do it. Those webbrowsers will just use the same character encoding as the page with the form was delivered with, i.e. it's the same charset as specified in Content-Type header of the HTTP response or the <meta> tag. Only Microsoft Internet explorer will send the character encoding in the request header when you specify it in the accept-charset attribute of the HTML form. However, this implementation is broken in certain circumstances, e.g. when IE-win says "ISO-8859-1", it is actually CP-1252! You should really avoid using it. Just let it go and set the encoding yourself.
   
   You can solve this by setting the same character encoding in the ServletRequest object yourself. An easy solution is to implement a Filter for this which is mapped on an url-pattern of /* and basically contains only the following lines in the doFilter() method:

   
   if (request.getCharacterEncoding() == null) {
       request.setCharacterEncoding("UTF-8");
   }
   chain.doFilter(request, response);
   
   

   Note: URL-decoding POST request parameters the above way is not necessary when you're using Facelets instead of JSP as it defaults to UTF-8 already. It's also not necessary when you're using Glassfish as the <parameter-encoding> also takes care about this.
   
   Here's a test snippet which demonstrates what exactly happens behind the scenes when it all fails:

   package test;
   
   import java.net.URLDecoder;
   import java.net.URLEncoder;
   
   public class Test {
   
       public static void main(String... args) throws Exception {
           String input = "日本語";
           System.out.println("Original input string from client: " + input);
   
           String encoded = URLEncoder.encode(input, "UTF-8");
           System.out.println("URL-encoded by client with UTF-8: " + encoded);
   
           String incorrectDecoded = URLDecoder.decode(encoded, "ISO-8859-1");
           System.out.println("Then URL-decoded by server with ISO-8859-1: " + incorrectDecoded);
   
           String correctDecoded = URLDecoder.decode(encoded, "UTF-8");
           System.out.println("Server should URL-decode with UTF-8: " + correctDecoded);
       }
   
   }
   

   Original input string from client: 日本語
   URL-encoded by client with UTF-8: %E6%97%A5%E6%9C%AC%E8%AA%9E
   Then URL-decoded by server with ISO-8859-1: 日本語
   Server should URL-decode with UTF-8: 日本語
   
   
4. JSP/Servlet response: during response processing an average application server will by default use ISO 8859-1 to encode the response outputstream. You need to force the response encoding to UTF-8 yourself. If you use JSP as view technology, then adding the following line to the top (yes, as the first line) of your JSP ought to be sufficient:

   
   <%@ page pageEncoding="UTF-8" %>
   
   

   This will set the response outputstream encoding to UTF-8 and set the HTTP response content-type header to text/html;charset=UTF-8. To apply this setting globally so that you don't need to edit every individual JSP, you can also add the following entry to your /WEB-INF/web.xml file:

   
   <jsp-config>
       <jsp-property-group>
           <url-pattern>*.jsp</url-pattern>
           <page-encoding>UTF-8</page-encoding>
       </jsp-property-group>
   </jsp-config>
   
   

   Note: this is not necessary when you're using Facelets instead of JSP as it defaults to UTF-8 already.

   The HTTP content-type header actually does nothing at the server side, but it should instruct the webbrowser at the client side which character encoding to use for display. The webbrowser must use it above any specified HTML meta content-type header as specified by w3 HTML spec chapter 5.2.2. In other words, the HTML meta content-type header is totally ignored when the page is served over HTTP. But when the enduser saves the page locally and views it from the local disk file system, then the meta content-type header will be used. To cover that as well, you should add the following HTML meta content-type header to your JSP anyway:

   
   <meta http-equiv="content-type" content="text/html; charset=utf-8">
   
   

   Note: lowercase utf-8 or uppercase UTF-8 doesn't really matter in all circumstances.
   
   If you (ab)use a HttpServlet instead of a JSP to generate HTML content using out.write(), out.print() statements and so on, then you need to set the encoding in the ServletResponse object itself inside the servlet method block before you call getWriter() or getOutputStream() on it:

   
   response.setCharacterEncoding("UTF-8");
   
   

   You can do that in the aforementioned Filter, but this can lead to problems if you have servlets in your webapplication which uses the response for something else than generating HTML content. After all, there shouldn't be any need to do this. Use JSP to generate HTML content, that's where it is for. When generating other plain text content than HTML, such as XML, CSV, JSON, etcetera, then you need to set the response character encoding the above way.
   
   
5. JSF/Facelets request/response: JSF/Facelets uses by default already UTF-8 for all HTTP requests and responses. You only need to configure the server as well to use the same encoding as described in JSP/Servlet request section.
   
   Only when you're using a custom filter or a 3rd party component library which calls request.getParameter() or any other method which implicitly needs to parse the request body in order to extract the data, then there's chance that it's too late for JSF/Facelets to set the UTF-8 character encoding before the request body is been parsed for the first time. PrimeFaces 3.2 for example is known to do that. In that case, you'd still need a custom filter as described in JSP/Servlet request section.
   
   
6. Databases: also the database has to take the character encoding in account. In general you need to specify it during the CREATE and if necessary also during the ALTER statements and in some cases you also need to specify it in the connection string or the connection parameters. The exact syntax depends on the database used, best is to refer its documentation using the keywords "character set". In for example MySQL you can use the CHARACTER SET clause as pointed out here:

   
   CREATE DATABASE db_name CHARACTER SET utf8;
   CREATE TABLE tbl_name (...) CHARACTER SET utf8;
   
   

   Usually the database's JDBC driver is smart enough to use the database and/or table specified encoding for querying and storing the data. But in worst cases you have to specify the character encoding in the connection string as well. This is true in case of MySQL JDBC driver because it does not use the database-specified encoding, but the client-specified encoding. How to configure it should already be answered in the JDBC driver documentation. In for example MySQL you can read it here:

   
   jdbc:mysql://localhost:3306/db_name?useUnicode=true&characterEncoding=UTF-8
   
   

   
   
7. Text files: when reading/writing a text file with unicode characters using Reader/Writer, you need java.io.InputStreamReader/java.io.OutputStreamWriter where in you can specify the UTF-8 encoding in one of its constructors:

   
   Reader reader = new InputStreamReader(new FileInputStream("c:/file.txt"), "UTF-8");
   Writer writer = new OutputStreamWriter(new FileOutputStream("c:/file.txt"), "UTF-8");
   
   

   Otherwise the operating system default encoding will be used.

   
   
8. Strings: although Java uses Unicode all the time under the hood, when you convert between String and byte[] using String#getBytes() or String(byte[]), you should rather use the overloaded method/constructor which takes the character encoding:

   
   byte[] bytesInDefaultEncoding = someString.getBytes(); // May generate corrupt bytes.
   byte[] bytesInUTF8 = someString.getBytes("UTF-8"); // Correct.
   String stringUsingDefaultEncoding = new String(bytesInUTF8); // Unknown bytes becomes "?".
   String stringUsingUTF8 = new String(bytesInUTF8, "UTF-8"); // Correct.
   
   

   Otherwise the platform default encoding will be used, which can be the one of the underlying operating system or the IDE(!).

Summarized: everywhere where you have the possibility to specify the character encoding, you should make use of it and set it to UTF-8.

Back to top

References

Here are some very useful references.

• The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)
• Character Conversions from Browser to Database
• The Java EE 7 Tutorial - Character Sets and Encodings

Last but not least, as Java just supports and uses Unicode all the time, also internally in the compiler, it's cool to know that it's possible to have such a class in Java:

\u0070\u0075\u0062\u006C\u0069\u0063\u0020\u0020\u0020\u0063\u006C\u0061\u0073\u0073\u0020\u0020
\u0055\u006E\u0069\u0063\u006F\u0064\u0065\u0020\u007B\u0020\u0070\u0075\u0062\u006C\u0069\u0063
\u0020\u0020\u0073\u0074\u0061\u0074\u0069\u0063\u0020\u0020\u0076\u006F\u0069\u0064\u0020\u0020
\u006D\u0061\u0069\u006E\u0020\u0028\u0020\u0053\u0074\u0072\u0069\u006E\u0067\u0020\u005B\u005D
\u0061\u0072\u0067\u0073\u0020\u0029\u0020\u007B\u0020\u0053\u0079\u0073\u0074\u0065\u006D\u002E
\u006F\u0075\u0074\u002E\u0070\u0072\u0069\u006E\u0074\u006C\u006E\u0028\u0022\u0049\u0022\u002B
\u0022\u0020\u2665\u0020\u0055\u006E\u0069\u0063\u006F\u0064\u0065\u0022\u0029\u003B\u007D\u007D

Save it unchanged as Unicode.java (without package), compile it and run it ;)

Back to top

Copyright - None of this article may be taken over without explicit authorisation.

(C) May 2009, BalusC

Effective datatable paging and sorting

WARNING - OUTDATED CONTENT!

This article is targeted on JSF 1.2. For JSF 2.0, many component libraries offer builtin "lazy loading" capability for effective paging and sorting, such as PrimeFaces <p:dataTable>.

Fast menu Effective datatable paging and sorting at DAO level Preparations Backing Bean Example DTO Example DAO Example JSF

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.

• tomahawk-1.1.7.jar
• commons-fileupload-1.2.jar
• commons-io-1.4.jar
• commons-logging-1.1.1.jar
• commons-el.jar

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<MyData> 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<MyData> 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:

    <managed-bean>
        <managed-bean-name>myBean</managed-bean-name>
        <managed-bean-class>mypackage.MyBean</managed-bean-class>
        <managed-bean-scope>request</managed-bean-scope>
    </managed-bean>

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<MyData> 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<MyData> 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"%>

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

<f:view>
    <html xmlns="http://www.w3.org/1999/xhtml">
        <head>
            <title>Effective datatable paging and sorting at DAO level</title>
        </head>
        <body>
            <h:form id="form">

                <%-- The sortable datatable --%>
                <h:dataTable value="#{myBean.dataList}" var="item">
                    <h:column>
                        <f:facet name="header">
                            <h:commandLink value="ID" actionListener="#{myBean.sort}">
                                <f:attribute name="sortField" value="id" />
                            </h:commandLink>
                        </f:facet>
                        <h:outputText value="#{item.id}" />
                    </h:column>
                    <h:column>
                        <f:facet name="header">
                            <h:commandLink value="Name" actionListener="#{myBean.sort}">
                                <f:attribute name="sortField" value="name" />
                            </h:commandLink>
                        </f:facet>
                        <h:outputText value="#{item.name}" />
                    </h:column>
                    <h:column>
                        <f:facet name="header">
                            <h:commandLink value="Value" actionListener="#{myBean.sort}">
                                <f:attribute name="sortField" value="value" />
                            </h:commandLink>
                        </f:facet>
                        <h:outputText value="#{item.value}" />
                    </h:column>
                </h:dataTable>

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

                <%-- The paging links --%>
                <t:dataList value="#{myBean.pages}" var="page">
                    <h:commandLink value="#{page}" actionListener="#{myBean.page}"
                        rendered="#{page != myBean.currentPage}" />
                    <h:outputText value="<b>#{page}</b>" escape="false"
                        rendered="#{page == myBean.currentPage}" />
                </t:dataList>
                <br />

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

                <%-- Cache bean with data list, paging and sorting variables for next request --%>
                <t:saveState value="#{myBean}" />
            </h:form>
        </body>
    </html>
</f:view>

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

DAO tutorial - the data layer

Fast menu Introduction The data layer Preparing database User model Handling DAO exceptions Properties file Properties file loader DAO factory User DAO interface User DAO implementation DAO utility class DAO test harness Further exercises How about connection pooling?

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.
 * <p>
 * This class requires a properties file named 'dao.properties' in the classpath with among others
 * the following properties:
 * <pre>
 * name.url *
 * name.driver
 * name.username
 * name.password
 * </pre>
 * 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.
 * <ul>
 * <li>The 'name' must represent the database name in {@link #getInstance(String)}.</li>
 * <li>The 'name.url' must represent either the JDBC URL or JNDI name of the database.</li>
 * <li>The 'name.driver' must represent the full qualified class name of the JDBC driver.</li>
 * <li>The 'name.username' must represent the username of the database login.</li>
 * <li>The 'name.password' must represent the password of the database login.</li>
 * </ul>
 * 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.
 * <p>
 * Here are basic examples of valid properties for a database with the name 'javabase':
 * <pre>
 * 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
 * </pre>
 * <pre>
 * javabase.jndi.url = jdbc/javabase
 * </pre>
 * Here is a basic use example:
 * <pre>
 * DAOFactory javabase = DAOFactory.getInstance("javabase.jdbc");
 * UserDAO userDAO = javabase.getUserDAO();
 * </pre>
 *
 * @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<User> 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<User> list() throws DAOException {
        List<User> 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
 * <code>java.util.Date</code> to <code>java.sql.Date</code> 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:
 * <ol>
 * <li>A MySQL server running at localhost:3306 with a database named 'javabase'.
 * <li>A 'user' table in the 'javabase' database which is created as follows:
 * <pre>
 * 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)
 * )
 * </pre>
 * <li>A MySQL user with the name 'java' and password 'd$7hF_r!9Y' which has sufficient rights on
 * the javabase.user table.
 * <li>A MySQL JDBC Driver JAR file in the classpath.
 * <li>A properties file 'dao.properties' in the classpath with the following entries:
 * <pre>
 * 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
 * </pre>
 * </ol>
 *
 * @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<User> 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 <DAO extends BaseDAO> DAO getDAOImpl(Class<DAO> 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:

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

<Context>
    <Resource
        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"
    />
</Context>

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:

    <resource-ref>
        <res-ref-name>jdbc/javabase</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

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