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)
this layer consists of A simple class/POJO/Bean 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 dialing with SQL statements and Database issues.

Database Creation

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

  • id
  • name
  • password
  • age

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

Bean creation
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

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

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;
}

Mapping
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 practise is to provide default empty constructor, a full constructor and a full constructor without the id parameter.

Connect to database

we can facilate 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
provide a method to return a Connection object or null if it failed to connect

    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 Connection getConnection()
    {
	try {
	    DriverManager.registerDriver(new Driver());
	    return DriverManager.getConnection(URL, USER, PASS);
	} catch (SQLException ex) {
	    ex.printStackTrace();
	}
	return null;
    }

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 Connector {
    
    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 Connection getConnection()
    {
	try {
	    DriverManager.registerDriver(new Driver());
	    return DriverManager.getConnection(URL, USER, PASS);
	} catch (SQLException ex) {
	    ex.printStackTrace();
	}
	return null;
    }
    
    /**
     * Test Connection
     */
    public static void main(String[] args) {
	Connector connector = new Connector();
	Connection connection = connector.getConnection();
	
	if(connection != null)
	    System.out.println("Connected successfully");
	else
	    System.out.println("failed to connect");
    }
    
}

Data Access Object

we want now to build DAO class
this DAO can do CRUD operations, it can Create, Retreive, Updata, Delete from our table

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 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)
    {
	Connector connector = new Connector();
	Connection connection = connector.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 getUserFromRS(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)
    {
	Connector connector = new Connector();
	Connection connection = connector.getConnection();
	try {
	    Statement stmt = connection.createStatement();
	    ResultSet rs = stmt.executeQuery("SELECT * FROM user WHERE id=" + id);
	    
	    if(rs.next())
	    {
		return getUserFromRS(rs);
	    }
	    
	} catch (SQLException ex) {
	    ex.printStackTrace();
	}
	
	return null;
    }

login method
The login ‘d be similar
just 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 usefull to use PreparedStatement

    public User login(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 getUserFromRS(rs);
	    }
	    
	} catch (SQLException ex) {
	    ex.printStackTrace();
	}
	
	return null;
    }

Seleact 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(int id)
    {
	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 = getUserFromRS(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

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;
    }