Is a raw JDBC approach worth it?
The advantages of JDBC are:
Best performance: We will not have any framework between the persistence layer and the database. We can get the best performance with a raw JDBC
Control over the SQL: The written SQL is the SQL that will be executed in the database, no framework will edit/update/generate the query SQL
Native Resource: We could access all natives database resources without a problem, e.g.: functions, stored procedures, hints, etc
The disadvantages are:
Verbose Code: After receiving the database query result we need to instantiate and populate the object manually, invoking all the required “set” methods. This code will get worse if we have classes relationships like one-to-many. It will be very easy to find a while inside another while.
Fragile Code: If a database table column changes its name it will be necessary to edit all the project queries that uses this column. Some project uses constants with the column name to help with this task, e.g. Customer.NAME_COLUMN, with this approach the table column name update would be easier. If a column is removed from the database all the project queries would be updated, even if you have a column constants.
Complex Portability: If your project uses more than one database it would be necessary to have almost all queries written for each vendor. For any update in any query it would be necessary to update every vendor query, this could take a lot the time from the developers.
I can see only one factor that would make me choose a raw JDBC approach almost instantly:
Performance: If your project need to process thousands of transactions per minutes, need to be scalable and with a low memory usage this is the best choice. Usually median/huge projects has all this high performance requirements. It is also possible to have a hybrid solution to the projects; most of the project repository (DAO) will use a framework, and just a small part of it will use JDBC
I do like JDBC a lot, I have worked and I still working with it. I just ask you to not think that JDBC is the silver bullet for every problem.
If you know any other advantage/disadvantage that is not listed here, just tell me and I will add here with the credits going to you. [=