Building Simple Data Access Layer Using JDBC

The aim of this tutorial is to manage the access of a table in database from separate layer written in java, this layer usually called Data Access Layer (DAL)

The most benefit from DAL is that it simplifies database access operations through some method calls like insert() and find() rather than making connection and executing some queries

this layer instead handles all database related calls and queries inside it.

Database Creation

We want to create a simple table for users, we may create it using these fields

id        int
name      varchar(200)
password  varchar(200)
age       int

Data Transfer Object

this layer should contain a simple class called Data Transfer Object(DTO) this object is just a simple mapping to the table, every column in the table would be a member variable in the class.

our aim is to order this layer to create, modify, delete or search for an entity Using simple java objects rather than dealing with SQL statements and other database related commands.

We want to map this table to our java code, we can do so by creating a simple class(bean) that contains the same fields.

To make it more encapsulated we should declare all field variables as private and create acessors (Setters and Getters) in addition to constructors, one of them is default constructor.

public class User {
    private Integer id;
    private String name;
    private String pass;
    private Integer age;
}

To map fields correctly, we should consider the NULL value in database. The default value for java primitives is a value like 0 in the case of int so we should provide a new data type that can hold the null value. We can do so by using special type of objects called wrappers like Integer instead on int.

Our final class would be like that:

public class User {

    private Integer id;
    private String name;
    private String pass;
    private Integer age;

    public User() {
    }

    public User(String name, String pass, Integer age) {
        this.name = name;
        this.pass = pass;
        this.age = age;
    }

    public User(Integer id, String name, String pass, Integer age) {
        this.id = id;
        this.name = name;
        this.pass = pass;
        this.age = age;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getPass() {
        return pass;
    }

    public void setPass(String pass) {
        this.pass = pass;
    }

}

A good practice is to provide default empty constructor, a full constructor and a full constructor without the id parameter.

Connecting to Database

We can facilitate Connecting to database by making a central class for connecting to the database
in this class we would provide connection parameters like database JDBC URL, user name and password as final variables (It'd be better to get them from a properties or XML configuration file)
provide a method to return a Connection object or null if it failed to connect, or it may be better throw a runtime exception in that case

public static final String URL = "jdbc:mysql://localhost:3306/testdb";
public static final String USER = "testuser";
public static final String PASS = "testpass";

/**
 * Get a connection to database
 * @return Connection object
*/
public static Connection getConnection() {
    try {
        DriverManager.registerDriver(new Driver());
        return DriverManager.getConnection(URL, USER, PASS);
    } catch (SQLException ex) {
        throw new RuntimeException("Error connecting to the database", ex);
    }
}

Also we can include a main method to test this connection the whole class would be like this:

import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * Connect to Database
 * @author hany.said
 */
public class ConnectionFactory {

    public static final String URL = "jdbc:mysql://localhost:3306/testdb";
    public static final String USER = "testuser";
    public static final String PASS = "testpass";

    /**
     * Get a connection to database
     * @return Connection object
     */
    public static Connection getConnection()
    {
      try {
          DriverManager.registerDriver(new Driver());
          return DriverManager.getConnection(URL, USER, PASS);
      } catch (SQLException ex) {
          throw new RuntimeException("Error connecting to the database", ex);
      }
    }

    /**
     * Test Connection
     */
    public static void main(String[] args) {
        Connection connection = connectionFactory.getConnection();
    }

}

Data Access Object

This DAO can do CRUD operations, it can Create, Retreive, Updata, Delete from our table.

The interface of our DAO should be like this:

public interface UserDao {
    User getUser();
    Set<User> getAllUsers();
    User getUserByUserNameAndPassword();
    boolean insertUser();
    boolean updateUser();
    boolean deleteUser();
}

Retreive User

The user can be retreived by any unique field like id or name or mail for example. In this method we're searching for a user by his id. The first step is to create a connection from the connector class then execute the select statement to get the user whose id is 7 we may query using this statement:

SELECT * FROM user WHERE id=7

Just there we made a dynamic statement that takes the id from method parameter

by executing this query we get a result set holding the user or null, we can check for that using the next() method in the Resultset. If returned true, we shall proceed to get user data from the Resultset using data getters. After we fill the user with all the data we return with it. If there is no user with this id or any other Exception happened(like invalid SQL Statement) this method would return null.

public User getUser(int id) {
    Connection connection = connectionFactory.getConnection();
        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM user WHERE id=" + id);

            if(rs.next())
            {
                User user = new User();

                user.setId( rs.getInt("id") );
                user.setName( rs.getString("name") );
                user.setPass( rs.getString("pass") );
                user.setAge( rs.getInt("age") );

                return user;
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    return null;
}

It's more convenient to make a separate method to extract user data from result set as we'd use it in many methods.

The new method would throw SQLException and would be provate to limit access only inside the class:

private User extractUserFromResultSet(ResultSet rs) throws SQLException {
    User user = new User();

    user.setId( rs.getInt("id") );
    user.setName( rs.getString("name") );
    user.setPass( rs.getString("pass") );
    user.setAge( rs.getInt("age") );

    return user;
}

Our method would be modified to use the new method:

public User getUser(int id) {
    Connection connection = connectionFactory.getConnection();
    try {
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM user WHERE id=" + id);

        if(rs.next())
        {
            return extractUserFromResultSet(rs);
        }

    } catch (SQLException ex) {
        ex.printStackTrace();
    }

    return null;
}

Login Method

The login would be similar. We want to provide user ans pass instaed of id, this should affect parameter list and query statement. This method would return a valid user if the user name and password are correct, null otherwise as there are many parameters, it's more useful to use PreparedStatement

public User getUserByUserNameAndPassword(String user, String pass) {
    Connector connector = new Connector();
    Connection connection = connector.getConnection();
    try {
        PreparedStatement ps = connection.prepareStatement("SELECT * FROM user WHERE user=? AND pass=?");
        ps.setString(1, user);
        ps.setString(2, pass);
        ResultSet rs = ps.executeQuery();

        if(rs.next())
        {
    return extractUserFromResultSet(rs);
        }

    } catch (SQLException ex) {
        ex.printStackTrace();
    }

    return null;
}

Select All Method

This method would return all users, so we should return them in a convenient container like array, but as we can't expect the returned rows count. It's better to use a collection like Set or List:

public Set getAllUsers() {
    Connector connector = new Connector();
    Connection connection = connector.getConnection();
    try {
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM user");

        Set users = new HashSet();

        while(rs.next())
        {
            User user = extractUserFromResultSet(rs);
            users.add(user);
        }

        return users;

    } catch (SQLException ex) {
        ex.printStackTrace();
    }

    return null;
}

Insert Method

The insert method would take a user as an argument and execute an SQL update statement using the PreparedStatement object.
The executeUpdate method returns number of affected rows, we expect to insert a single row thus means It should return 1, if so we return true, otherwise we return false

public boolean insertUser(User user) {
    Connector connector = new Connector();
    Connection connection = connector.getConnection();
    try {
        PreparedStatement ps = connection.prepareStatement("INSERT INTO user VALUES (NULL, ?, ?, ?)");
        ps.setString(1, user.getName());
        ps.setString(2, user.getPass());
        ps.setInt(3, user.getAge());
        int i = ps.executeUpdate();

      if(i == 1) {
        return true;
      }

    } catch (SQLException ex) {
        ex.printStackTrace();
    }

    return false;
}

update method

the update method is similar to the insert one
the only change is the SQL statement

public boolean updateUser(User user) {
    Connector connector = new Connector();
    Connection connection = connector.getConnection();
    try {
        PreparedStatement ps = connection.prepareStatement("UPDATE user SET name=?, pass=?, age=? WHERE id=?");
        ps.setString(1, user.getName());
        ps.setString(2, user.getPass());
        ps.setInt(3, user.getAge());
        ps.setInt(4, user.getId());
        int i = ps.executeUpdate();

      if(i == 1) {
    return true;
      }

    } catch (SQLException ex) {
        ex.printStackTrace();
    }

    return false;
}

Delete method

The delete method would use a simple query like
DELETE FROM user WHERE id=7
sending this query with the id parameter would delete this record, returning 1 if it's deleted successfully

public boolean deleteUser(int id) {
    Connector connector = new Connector();
    Connection connection = connector.getConnection();
    try {
        Statement stmt = connection.createStatement();
        int i = stmt.executeUpdate("DELETE FROM user WHERE id=" + id);

      if(i == 1) {
    return true;
      }

    } catch (SQLException ex) {
        ex.printStackTrace();
    }

    return false;
}