In relation to object orientation, should queries have a class of their own or can they stay in a specific class?

Question

What I have is this: a user class and a connection class with the bank.

I need to insert a user into a database, the query "insert into usuarios values (..)" should be in my user class or in the database class?

I think of the following, I’m using Mysql, if one day I need to use another database and use a different SQL syntax, I’ll have to change my entire program if I leave it in the user class, but if I leave it in the connection class, I could just create another connection class and modify the darlings, but I found it confusing, because I would be assigning to the database class something that would be of the users class, or not?

Answer 1

There is no rule that determines this, especially the object orientation paradigm has nothing to say about. It is possible to reach a conclusion by adopting other concepts.

Since there is no rule, everything can be right. It is possible to do both, but it is also possible to have other options.

Option to separate concepts

Can have a class Usuario which only maintains the data structure (DTO) and a class to manipulate database access for this class. This meets the principle of cohesion and decoupling functions. Often this class can be called DAOUsuario since she adopts the standard of data access Objects (do not like this kind of nomenclature). This is in line with the principle of single responsibility.

Or it can further improve and implement the repository standard. You can try to find out the difference between approaches in an existing question (I have reservations in it).

Stay tuned because the way you seem to want to do it is important to separate well what is the business rule and what is the access to the data itself. The above pattern facilitates this, but it doesn’t center everything on one point. See mgibsonbr’s comment.

As Onosendai’s answer says, search separate the concepts. This is what will really bring flexibility to the software, but there is no free lunch. Organizing brings its difficulties.

Modularize

Contrary to popular belief (justifiable), OOP encourages modularization rather than grouping functionalities. Modularization is what really allowed software to get better, more organized, easier to maintain. And modularization is possible in any paradigm. People happen to learn to do it better in OOP, but it’s a cultural issue.

I must group everything in one place?

The connection and general manipulations of the database should be in another class.

It is rare to put everything in the database class. I’ve never actually seen it in well designed systems. This causes a high coupling and the class becomes a God Object. This would go against one of the principles of the OO paradigm. This makes it difficult to maintain and extend the application. Object orientation was created precisely to avoid the codes that were previously grouped in a single location (although I don’t need this paradigm to do this). This gives a false sense of organization. This is procedural programming at its worst.

Separating the access to the database table, the entity it represents and the global control of the bank would even facilitate the exchange of one database manager system for another. It would facilitate formal testing.

Abstracting the storage medium

I suggest using something that abstracts the use of the database, as is the case of Orms, or assess whether you really need to keep this open. It’s very common for people to think they need it and never actually use it. This is probably because they don’t trust their own decision. Of course there are cases of real need to have other banks available, in genuinely generic software.

I don’t particularly like it, but I understand that people benefit from it. So if you adopt a ORM look for one that already implements the repository pattern as automatically as possible. So you’ll have to worry less about abstraction and more about business rules.

In PHP people often use PDO. One of his problems, among others, is that he is only half a solution, you still need to take care of several aspects of this abstraction, the queries will not adapt themselves because he used the PDO.

Completion

Does it pay to always do this? No. It depends on the project, the goals. Many people place access behavior in the very class of the entity structure. And it works well too, but you have to know what you’re doing. It obviously makes it difficult to exchange the database that may never even occur.

Of course there are still more specific doubts on the subject.

Answer 2

If you are really representing queries by classes[1], consider that "entering a user into the database" is a concept, but the realization of this concept depends on which DBMS is being used. This could therefore be modeled through the standard Abstract Factory.

In this case, each model of your domain could have an interface containing the various methods that will take care of its persistence, and this interface can have N implementations (1 for standard SQL and other specific). The class representing your specific DBMS would be the factory of these implementations. At first, assuming that standard SQL is sufficient to handle a model, these factories would only need to return the standard implementation. But whenever a specific model needs customization, this factory would return a custom implementation.

An example (in pseudocode) with two models and three Dbms would be:

/* Modelos */
class Usuario
    username
    password

class Produto
    nome
    categoria

...

/* Persistência */
interface PersistenciaUsuario
    inserir(username, password): Usuario
    obter(username): Usuario
    remover(username): boolean
    verificar_senha(username, password): boolean
    listar(): Usuario[]

interface PersistenciaProduto
    inserir(nome, categoria): Produto
    obter(id): Produto
    pesquisar(nome): Produto[]
    remover(id): boolean
    listar(): Produto[]
    listar(categoria): Produto[]

...

/* Fábrica abstrata */
interface Fabrica:
    obter_persistencia_usuario(): PersistenciaUsuario
    obter_persistencia_produto(): PersistenciaProduto
    ...

A standard implementation would use simple SQL:

class PUsuarioSQL implements PersistenciaUsuario
    sql_engine
    PUsuarioSQL(engine) { sql_engine = engine; }
    inserir(username, password) { ... }
    obter(username) { ... }
    ...

class PProdutoSQL implements PersistenciaProduto
    sql_engine
    PProdutoSQL(engine) { sql_engine = engine; }
    inserir(nome, categoria) { ... }
    obter(id) { ... }
    ...

abstract class FabricaSQL implements Fabrica
    sql_engine
    obter_persistencia_usuario() { return new PUsuarioSQL(engine); }
    obter_persistencia_produto() { return new PProdutoSQL(engine); }
    ...

A concrete implementation would only customize what was needed:

class PUsuarioMySQL extends PUsuarioSQL
    inserir(username, password) { ... }
    verificar_senha(username, password) { ... }
    /* Reaproveitou parte da implementação de PersistenciaUsuario */

class FabricaMySQL extends FabricaSQL
    FabricaMySQL() { super(...); }
    obter_persistencia_usuario() { return new PUsuarioMySQL(engine); }
    /* Reaproveitou toda a implementação de PersistenciaProduto */

class PProdutoPostgres extends PProduto
    pesquisar(nome): Produto[]
    /* Reaproveitou parte da implementação de PersistenciaProduto */

class FabricaPostgres extends FabricaSQL
    FabricaPostgres() { super(...); }
    obter_persistencia_produto() { return new PProdutoPostgres(engine); }
    /* Reaproveitou toda a implementação de PersistenciaUsuario */

class FabricaMongo implements Fabrica
    /* Aqui não dá pra reaproveitar nada, pois não é baseado em SQL... */

Some remarks:

• If you have M models and N Dbms, there will potentially be Mxn classes responsible for persistence;
  • If a new model has appeared, you will have to create up to N new classes; if a new DBMS has appeared, you will have to create up to M new classes.
  • Note that this scheme does not necessarily obey the principle open/closed - because any change in the interface Fabrica would require changes in specific N implementations.
• The persistence of each model is neither in the model itself (which would "tie" it to a specific implementation, and would compromise the separation of responsibilities) nor in the class that makes the connection with the SGBD (which would create a "God Object"). You’re in a separate class, with a homogeneous interface (i.e. same interface for any DBMS).
  • The consequence of this is that the code that needs to act on the model (insert, query, exclude, etc.) will be the same regardless of the means of persistence used. All this consumer code needs is to receive an instance of Fabrica and call the relevant methods.
• Concrete implementations can take advantage of the similarities, only writing new code for cases really different from the existing ones. In the example above, the FabricaMySQL took advantage of the entire standard implementation of PersistenciaProduto, and still partially took advantage of the standard implementation of PersistenciaUsuario. That is, only what is different is that it needs to be reimplemented by the final classes, all that can be reused is reused.

---

[1]: I wouldn’t do that, even if I ran away from the OO model. Instead I would use a lighter medium, for example a resource file where each query is a simple string (parameterized, of course). But a good one ORM could be even better.

Answer 3

Applique Separation of Interests up to its maximum possible extent.

In an ideal world your Users class would not need to know how to enter a new record. In situations like this, an ORM adapter is the best option: You don’t need to know how the current implementation of the database works, just using .Save() in an instance would already cause the desired effect, for example.

If you don’t have an ORM layer, ask yourself: What’s worse, a generic layer of database knowing what a user is (and, logically, aspects of all other classes spread across your application) or a user class know how to issue SQL expressions?

My choice to contain damage and compartmentalize scopes would be the second option.

Answer 4

A basic principle accepted in Object Orientation is the separation of responsibilities.

Behold Principle of Separation of Responsibilities for more details.

Four responsibilities

A widely used design standard identifies at least 4 responsibilities in solving this problem:

1) Entity

Object representing the entity; in this case, a system user. It has the entity’s business attributes and behaviors.

2) Repository

Object that abstracts the persistence and recovery of the entity.

Has CRUD methods, if necessary, and other research methods.

Example:

class UsuarioRepo
    Usuario[] usuariosAtivos()
    

The repository can also be generic, where a single object abstracts the persistence and recovery of all types of system entity:

class Repositorio
    T[] obtem<T>(CriteriosPesquisa)
    

But the Repository does not hold the information of the database structure, nor does it know the specifics of the database being used. In other words, the repository knows nothing about the tables in the database and does not know how to make SQL commands.

To do its work, the repository uses the following objects (and after we talk about them, we return to the repository).

3) Entity to Database Mapping

Based on definitions in XML files or metadata declared in the entity class, this object delivers a mapping between entities and tables and columns of the database.

This object has the knowledge of the structure of the database and its relation to the entities, but it also does not know how to make SQL commands.

4) Interaction with the database

Here we have one or more objects specialized in mounting SQL commands.

There may be an object or a set of objects handling the specifics of each supported database server.

There may also be an abstraction of this interaction with the database, especially in case more than one database is supported.

Back to the Repository

As I said, the repository uses the other objects to do its work. For example:

class UsuarioRepo
    Usuario[] usuariosAtivos() 
    {
        var mapeamentoUsuario = mapeamento.get<Usuario>()
        var sql = sqlBuilder.select(mapeamentoUsuario).criterio("ATIVO = 'S'")
        return conexao.executeSelect<Usuario>(sql)
    }

Note that I have abstracted other responsibilities in this response, such as the connection object and the objects that execute commands against the database.

ORM

If you implement this solution, you will have developed a ORM, but you can also use a market.

A ORM provides Entity/Database mapping capabilities and abstracts the connection to the database, the construction of SQL commands and the execution of these commands in the database.

Orms for Java, Ruby (on Rails*) and . Net are very simple to use and although they are complex, they do not add complexity to the project because this complexity is encapsulated within them.

Using an ORM, the code looks something like this:

@Entity("TAB_USUARIO")
class Usuario
    @Column("LOGIN")
    username
    @Column("SENHA")
    password      
    
class UsuarioRepo
   Usuario[] usuariosAtivos() 
   {
     return orm.query<Usuario>("select u from Usuario u where u.ativo = true").result()
   }

Note that this command is not native SQL code of the database and is not about tables, but about entity names according to their class. The ORM, in turn, dealt with the mapping, the specifics of the bank in question, connection pool, etc.

There is still the option to use lambda and other Patterns instead of typing the command as string; and . Net still offers LINQ.

You can still choose not to encapsulate persistence and recovery in repositories but, instead, write the queries every time you need them - as they are queries against entities and not native SQL against tables, you would not be spreading database code through your application. I usually use repositories due to some features of my context.

_{*Rails uses the model Activerecord, where persistence and recovery behaviors belong to the entity and its class respectively, and not to a separate object.}

Completion

Separation of responsibilities can go far beyond writing SQL in a class other than the entity class.

You should make this separation according to the characteristics of your context.

For example, if the software meets a complex need, separating responsibilities well can help prevent the complexity of the domain from contaminating the code. The more complex the domain, the more it compensates for the separation of responsibilities, which helps to keep the code simpler (although this may be a bit counterintuitive).

Finally, you don’t usually need to develop an ORM to help separate responsibilities because there are good frameworks for this available in the market.