Do we need to write manual queries again when using OData?

Question

While using OData, do we need to write manual SQL queries in order to communicate to the database? If so, what's the advantage of OData? I'm using the Apache Olingo implementation.

What prior research have you performed to ty to answer this question on your own? — theforestecologist, Jun 04 '18 at 22:39
I tried to use olingo implementation of Odata, and successfully exposed some tables with it. But the problem is, whenever a request is received, we have to manually create a query based on the parameters what we received in the request. And also, all the filter, search and every other operations were executed on the collection what we retrive and provide to the olingo library, which is not efficient. — user3075800, Jun 05 '18 at 14:26
I dont know, whats the purpose of odata and its implementation, if we need to manually write all the queries and return the records. we can do the same through plain rest API itself. Am confused, what exactly the odata standard and the olingo implementation serves us — user3075800, Jun 05 '18 at 14:26

score 0 · Answer 1 · answered Jun 06 '18 at 19:59

Welcome to StackOverflow!

The question is way too broad to be answered completely, hence the comments asking about prior research. Nevertheless, I would simply enumerate a couple of points to try and steer you in the right direction:

OData is a protocol on top of REST (i.e. OData is RESTful in itself).
OData has a fixed structure, with a well-known URI schemata, request / response formats, etc.
The advantage of using OData vs using plain REST is that the consumers of the service just need to know the OData protocol and not your specific implementation. This actually means that the effort for implementing the clients (e.g. the UI) should be considerably lower. Also, it means that you can easily support several clients (e.g. a UI and also another external system).
You should really consider if you want to do a 1 to 1 mapping of your persistent model to your OData model. Generally speaking, non-trivial applications tend to expose a different API than the persistent model (e.g. you de-normalise the entities or do some projections...).
Also, you should consider where you want to actually have your business logic. Just allowing the UI to do database operations via OData is a very bad idea, because it moves all your logic on the UI.
Lastly, I would mention that there actually is a way of using Olingo + JPA and generate the database queries automatically. You can find more information on the Olingo documentation. In a nutshell, you just need to create an ODataJPAServiceFactory subclass and pass your Entity Manager Factory to the OData JPA context in the initializeODataJPAContext method:

private static final String PUNIT_NAME = "punit"; 
public ODataJPAContext initializeODataJPAContext() {
    ODataJPAContext oDataJPAContext = this.getODataJPAContext(); 
    EntityManagerFactory emf = 
        Persistence.createEntityManagerFactory(PUNIT_NAME); 
    oDataJPAContext.setEntityManagerFactory(emf);   
    oDataJPAContext.setPersistenceUnitName(PUNIT_NAME); 
    return oDataJPAContext;
}

Do we need to write manual queries again when using OData?

1 Answers1