How to perform object persistence in Delphi database?

Question

I work with Delphi, but always in a procedural way and using Data-Aware components. I recently started reading some things about working with POO at Delphi, and did some fairly basic examples, my doubts are these:

How can I work with a class (Tclientes for example) by persistence in the database?
How to restore this data for viewing using OO?
What Data-Aware Components Look Like?

Answer 1

The diagrams of your BD and your class diagram will look very similar, but be careful not to make confusion when implementing them, each one works in a very different way from the other and the amount of classes and tables are not necessarily the same.

Consider the following example for a supposed class Cliente:

public class Cliente{
    private String nomeCliente;
    private String endereco;                //supondo que cada cliente só possui um endereco
    private List<String> telefones = 
            new ArrayList<String>();        //0..n telefones
    //construtor
    //getters and setters
}

First doubt: we have a ArrayList in class Cliente, how we keep it in the comic book?

Don’t even think about creating a zillion columns in the table Clientes in your comic book, like this: telefone1, telefone2, telefone3, ..., telefone1000. So you’ve already created some problems such as a lot of columns that most of the time will never be used, a horrible table to read and create a diagram, and a limited amount of your customer’s phones (sure it’s a high limit, but it’s still a limit).

The correct thing is you have two tables with the relationship 1..n, so you will have a table for customers and one for customers' phones. Doing so will not be unused records causing difficulty in reading them and there will be a limit on the number of phones of each customer. Both are connected by a foreign key in the phone table.

An example of implementing how to click on ArrayList the phones of each customer would be so:

getClient(String client name)

    prestat = conn.prepareStatement("SELECT * FROM Clientes WHERE nomeCliente=?");
    prestat.setString(1, nomeCliente);
    pw = prestat.executeQuery();

    if (pw.next()) {
        //criando cliente
        cliente = new Cliente(pw.getString(2), pw.getString(3));

        // incluindo telefone do cliente
        cliente.setTelefones(getTelefonesCliente(pw.getInt(1)));
    }

getTelefonesClient(int idClient)

    List<TelefoneCliente> telefonesCliente = new ArrayList<TelefoneCliente>();

    //..

    prestat = conn.prepareStatement("SELECT * FROM TelefonesCliente WHERE idCliente=?");
    prestat.setInt(1, idCliente);
    pw = prestat.executeQuery();

    while (pw.next()) {
        telefonesCliente.add(pw.getString(3)));
    }

    //..

    return telefonesCliente;

And the problem is solved :)

This solution even makes it easy to expand your program. For example, suppose that once implemented arises the need for you to need to save the customer’s phone carrier.

You have two options:

• Can create one more ArrayList in class Cliente, however in my opinion it would be a little strange, and might even hurt the principle of sole responsibility; or
• You can do what I think is most natural, create a new class:

Note that you will now have two classes and two tables, but the procedure of loading the data into the objects of these classes is very similar to the one mentioned above. This one stays as homework ;-)

Answer 2

You need a framework ORM for Delphi. You can develop yours or use a third party. Some options are:

• TMS Aurelius (commercial, only supports newer versions of Delphi by using features like Generics. That’s what I use and recommend. The main developer is even Brazilian and can support you in Portuguese.)
• tiOPF (open source, one of the oldest)
• DORM (open source, a little newer, I think also only supports newer versions of Delphi)
• Entitydac (commercial, announced these days, I think it is still in beta)
• Synopse mORMot (that always speak well to me, but seems to be a little more than an ORM, by having the communication part together, and also never used)