I can't get the DAO/DTO pattern with SQLite in java

Question

I've been trying to understand the DAO pattern but now I have not been successful . Probably because I have not been able to apply what I've found on the internet to problem I try solve. I want to encapsulate the database, do the things right.

I did this so far, but i feel it's pretty useless.

My DTO class:

   public class PersonDTO{
        final public static String TABLE = "PEOPLE";
        private int id;
        private String name;
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
    }

My "DAO"

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

public class PersonDAO {
    private Connection connection;
    private DTO dto;
    private Statement stmt = null;
    private String tableName;
    private Integer id;

    public PersonDAO() {

    }

    public PersonDTO getPerson(int id) {
        connection = ConnectionFactory.getInstance();
        PersonDTO person = new PersonDTO();
        try {
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + PersonDTO.TABLE +" WHERE ID = '"+id+"'");
            person.setId(rs.getInt("id"));
            person.setName(rs.getString("age"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        closeConnection();
        return person;
    }

    public void save() {
        throw new UnsupportedOperationException(); //not implemented yet
    }

    public void update() {
        throw new UnsupportedOperationException(); //not implemented yet
    }

    public void delete() {
        throw new UnsupportedOperationException(); //not implemented yet
    }

    public List<DTO> getDTO(String filter) {
        return null;
    }

    protected void closeConnection() {
        try {
            connection.close();
            connection = null;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

I can't get:

1. Which it is supposed to be the relationship between the DTO class and the DAO class.
2. The DAO class has to have the methods for get the information from the database?
3. What is DTO class for, why just don't use a "Person" class instead?.

This is pretty frustrating. I will be grateful with any help.

Answer 1

DAO stands for Data Access Object. As its name indicates, its responsibility is the access of data. That means that it knows how to use a data connection to retrieve objects from the data store.

DTO stands for Data Transfer Object. Its responsibility is to encapsulate the data format in a programming language construct that makes it easy to use in code.

In my opinion the DAO should deal in your object model, not in DTO's. In other words the interface should return Person not PersonDTO. Internally to your DAO implementation it may be convenient to use a intermediary DTO object to assist you in fetching and storing your objects. If you're using Hibernate and/or JPA, you would create a DTO with your JPA annotations on it for example.

Here's how I would implement:

// Define an interface for your DAO so you can mock in unit tests, or swap out an implementation if you decide not to use SQLite
public interface PersonDao {
    Person getPerson(int id) throws PersonDaoException;
}

// Write your implementation for SQLite. I fixed some design/implementation issues
class PersonDaoSqlite implements PersonDao {
    private final DataSource ds;

    public PersonDaoSqlite(DataSource ds) {
        this.ds = ds;
    }

    public Person getPerson(int id) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet result = null;

        // As of Java 7 and onwards, one can use try-with-resources here.
        try {
            connection = ds.getConnection();
            statement = connection.prepareStatement("SELECT * FROM PEOPLE WHERE ID = ?");
            statement .setInt(1, id);
            result = statement .executeQuery();

            Person person = new Person();
            person.setId(rs.getInt("id"));
            person.setName(rs.getString("age"));
        } catch (SQLException e) {
            throw new PersonDaoException(e);
        } finally {
            if (result != null) {
                result.close();
            }

            if (statement != null) {
                statement.close();
            }

            if (connection != null) {
                connection.close();
            }
        }
    }
}

public class PersonDaoException extends Exception {
     public PersonDaoException(Throwable cause) {
          super(cause);
     }
}

public class Person {
    private int id;
    private String name;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
}

Answer 2

Here in your example having a DTO may be useless, but it's not useless in general.

DTO object is supposed to be output of a remote service (RMI/Web service request).

An object that carries data between processes in order to reduce the number of method calls.

Ref : http://martinfowler.com/eaaCatalog/dataTransferObject.html

This object is supposed to carry data to reduce the number of method calls.

As you have used PersonDTO to carry Person table data. However it's useless to create PersonDTO for just one object, instead just user Person.

If you had a list of persons, or may be some other data that carries more information regarding the state of the request like below

public class PersonDTO {
    public List<Person> personList;
    public Fault fault;
    
    public class Fault {
        String faultCode;
        String faultMessage
    }
    
    public Date requestDate;
    public UUID requestId;
    public String requestSignature;
    
    ...
}

In this case it makes sense to use a DTO object as there's more to the response than just a person.

DTO can also carry aggregated data. It's supposed to be the outside view of your remote method. Normal object is private inside view for you only to interact with.