Hi guys.

Some time ago I started to develop a system to test and consolidate new knowledge. I am developing a simple system, but I'm still developing the database. I'm not a DBA, I am a Java developer, and am gaining knowledge in this field (databases).

Despite the system being developed to be simple, I have to take into account that it could be accessed by different applications, perhaps written in different languages from different people. Therefore, I believe that I should design the database to act defensively, so as to avoid data inconsistency.

During the development of the database, following these basic rules I saw the need to define a after select trigger in some tables. As I'm using PostgreSQL 9.5, I saw this was not possible, and that I had to do two things to get around this:

- Define a function that performs the select and make the necessary additional operations.
- Remove the select privilege so users only access the records by function.

This operation was necessary by the need to persist the total accesses made to present records in certain tables. In case, when selecting a record a field "accesses" is incremented in the selected record.

So far there is no problem. But when I start to think more forward in the application layer (Hibernate), I get confused. When removing the select privilege (or any other) from Hibernate, how Hibernate reads data from the database? More than that, I do not quite know what to do in this kind of situation.

In my confused head I wonder the following:

Should I set a read function? If so, how should it be? Can Hibernate read data and perform joins? Can I still use JPA Criteria API and/or JPQL? What are the Hibernate limitations about this? How should I work on this case? What would be the best or recommended approach?

I have searched on the internet, and also for help at stackoverflow (please, If you don't mind, take a look ): http://stackoverflow.com/questions/3743 ... on-joining

Unfortunately, I had no return. I also have the latest Hibernate book, and from what I researched, I could not find anything about it. Can anyone help me, please?

NOTE: I'm using Hibernate 5.1.0.

I don't really understand what you're asking. You could assign privileges at database level and use multiple users, each one according to certain roles.

Yes, indeed. But I believe this is not my case. Imagine for a moment that we have a Products table. This table has fields, and it relates to another table named Categories. A product has several categories, and the same category can be used for various products (There is an intermediate table that links these two). Now imagine that in the Products table we have a field named "accesses"...

The desire here is to achieve an increment when a selection occurs. The first thing that came to my mind was to use an after select trigger, something that does not exist in PostgreSQL.

Maybe I did not understand well what you mean, but in this case, I belive that there is no way to create separate rules for connection accounts with the database. The problem is:

Record selected = field incremented.

On the application side, at some point I need to get products, and possibly the categories of it. The selection should be made on the application side, but with one more detail, which would increase the field. I can not assign such responsibility to the application, it can not do database work because the database can reach an inconsistent state. As I have said, I need to design the database defensively, because it could be used by more than one application. Imagine having to always write these rules in the application layer...

So what I did to get around this:

I created a function that makes the selection, and also increments the field. Because of this, I had to remove the privilege of selection. This allows Hibernate call this function without problems. However, a single function to select products is not enough. The program should allow complex queries, such as select records by their fields, and also joints when required. With JPQL and the JPA Criteria API that's easy, but impossible without the privilege of selection.

Here come my questions:

What do I do? Do Hibernate works with mapping by calling functions and performs appropriately selections? Or... Should I set 30.000 selection functions and ask Hibernate to call them when needed? As I posted on Stackoverflow:

Do remove privileges compromises the use of Hibernate? If so, why? What should I do as recomendation?

@mihalcea_vlad I apologize if I did not understand What you said. If this is the case, could you explain further, please? And thank you for the attention.

Why do you want to increment a field when you make a selection?

From what I have said, I am developing a simple shopping system. Whenever a record is queried, it gets a "access". It would help me to check, for example, the most viewed/accessed products.

If you stop to think, the way I want to do it makes the whole operation not precise. For example, a product may be selected from different locations, not only to be consulted for a purchase. However, I do not need precision here.

Maybe I'm complicating things. I do not know. I need to review it all. It turns out that I have no experience. It is the first time I design a system from the start. If you have any alternative, please, I'll be happy to hear (read).

Thank you again for your time and attention.

You don't need to do that for every entity/table. Think if you have a second-level cache and the entity is not even loaded from the DB.
In this case, Hibernate already collects this kind of statistics for you.

You just have to enable statistics with the following configuration property:

hibernate.generate_statistics=true

and then use EntityStatistics#loadCount.

Thanks @mihalcea_vlad. This could help. But... As I said, I need to think that the database could (in theory) be used by more than one application, such as in distributed systems, or other applications that want to access such data.

I actually can use this feature. However, all other applications have to use Hibernate and perform the same procedure, or perform specific procedures for making the accesses count. This is error-prone, could leave the database in an inconsistent state, can become a maintenance nightmare, and can cost money (a lot). As I said, I can not assign a database job to the application.

Even developing an application for learning and testing, I need to use what I have learned for future projects. And as systems developer, I need to do it the right way. I need to think about possibilities, and that in the future there may be changes.

My apologies. I know I'm being annoying and/or boring, but I need to do it right. I need to be careful and take it seriously.
If I am not understanding what you mean, please, if you do not mind, I ask you to explain yourself better.