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.
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.
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.
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;
public class User implements Serializable {
private static final long serialVersionUID = 1L;
private Long id;
private String email;
private String password;
private String firstname;
private String lastname;
private Date birthdate;
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;
}
@Override
public boolean equals(Object other) {
return (other instanceof User) && (id != null)
? id.equals(((User) other).id)
: (other == this);
}
@Override
public int hashCode() {
return (id != null)
? (this.getClass().hashCode() + id.hashCode())
: super.hashCode();
}
@Override
public String toString() {
return String.format("User[id=%d,email=%s,firstname=%s,lastname=%s,birthdate=%s]",
id, email, firstname, lastname, birthdate);
}
}
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;
public class DAOException extends RuntimeException {
private static final long serialVersionUID = 1L;
public DAOException(String message) {
super(message);
}
public DAOException(Throwable cause) {
super(cause);
}
public DAOException(String message, Throwable cause) {
super(message, cause);
}
}
package com.example.dao;
public class DAOConfigurationException extends RuntimeException {
private static final long serialVersionUID = 1L;
public DAOConfigurationException(String message) {
super(message);
}
public DAOConfigurationException(Throwable cause) {
super(cause);
}
public DAOConfigurationException(String message, Throwable cause) {
super(message, cause);
}
}
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.
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;
public class DAOProperties {
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);
}
}
private String specificKey;
public DAOProperties(String specificKey) throws DAOConfigurationException {
this.specificKey = specificKey;
}
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 {
property = null;
}
}
return property;
}
}
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;
public abstract class DAOFactory {
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";
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 (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 {
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;
}
abstract Connection getConnection() throws SQLException;
public UserDAO getUserDAO() {
return new UserDAOJDBC(this);
}
}
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);
}
}
class DataSourceDAOFactory extends DAOFactory {
private DataSource dataSource;
DataSourceDAOFactory(DataSource dataSource) {
this.dataSource = dataSource;
}
@Override
Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
}
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);
}
}
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;
public interface UserDAO {
public User find(Long id) throws DAOException;
public User find(String email, String password) throws DAOException;
public List<User> list() throws DAOException;
public void create(User user) throws IllegalArgumentException, DAOException;
public void update(User user) throws IllegalArgumentException, DAOException;
public void delete(User user) throws DAOException;
public boolean existEmail(String email) throws DAOException;
public void changePassword(User user) throws DAOException;
}
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;
public class UserDAOJDBC implements UserDAO {
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 = ?";
private DAOFactory daoFactory;
UserDAOJDBC(DAOFactory daoFactory) {
this.daoFactory = daoFactory;
}
@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);
}
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);
}
}
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;
}
}
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;
public final class DAOUtil {
private DAOUtil() {
}
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;
}
public static void setValues(PreparedStatement statement, Object... values)
throws SQLException
{
for (int i = 0; i < values.length; i++) {
statement.setObject(i + 1, values[i]);
}
}
public static Date toSqlDate(java.util.Date date) {
return (date != null) ? new Date(date.getTime()) : null;
}
}
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;
public class DAOTest {
public static void main(String[] args) throws Exception {
DAOFactory javabase = DAOFactory.getInstance("javabase.jdbc");
System.out.println("DAOFactory successfully obtained: " + javabase);
UserDAO userDAO = javabase.getUserDAO();
System.out.println("UserDAO successfully obtained: " + userDAO);
User user = new User();
user.setEmail("foo@bar.com");
user.setPassword("password");
userDAO.create(user);
System.out.println("User successfully created: " + 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);
user.setFirstname("Foo");
user.setLastname("Bar");
userDAO.update(user);
System.out.println("User successfully updated: " + user);
user.setFirstname("Foo");
user.setLastname("Bar");
userDAO.update(user);
System.out.println("User successfully updated: " + user);
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());
userDAO.delete(user);
System.out.println("User successfully deleted: " + user);
boolean exist = userDAO.existEmail("foo@bar.com");
System.out.println("This email should not exist anymore, so this should print false: " + exist);
anotherUser.setPassword("newAnotherPassword");
userDAO.changePassword(anotherUser);
System.out.println("Another user's password successfully changed: " + anotherUser);
User foundAnotherUser = userDAO.find("bar@foo.com", "newAnotherPassword");
System.out.println("Another user successfully queried with new password: " + foundAnotherUser);
userDAO.delete(foundAnotherUser);
System.out.println("Another user successfully deleted: " + foundAnotherUser);
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.
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);
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.
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
