Using SQL gives more control to the developer, or at least thats what it feels like. And most of the people, who resist ORM do so because they do not want to lose that control.
In my experience,the biggest advantage is in that it helps you adopt OOP, and that it minimises effort of writing SQL for 90% of CRUD operation. Of course, there would be cases where it wouldn't work, e.g. Searches, where queries need to be optimized/tuned and you will have to leverage DB specific features. However, that would only be a small fraction for most of the applications.
Ill answer the questions, then give the full personal sitiation.
With tools like Hibernate, EJB 3.0 and JDO. When do you see that it is better to use straight JDBC code? Never. Wouldn't mix JDBC and ORM. Typically ORM allows you to get to inner workings of database if you need to without using JDBC.
I find Hibernate to generate the exact SQL that I would have created if I used JDBC directly in code. Thats great. I hear JDO is good at code generation as well. When its not you make a donation to the library folks and get them to improve it. Probably more efficient use of time/money to let the experts do it than doing it yourself. Cant be expert in everything!?
How do you handle where to place the SQL statements, rather than directly in code? JDO gives me a form of SQL which is JDOQL and I place it in the library of my code that runs the JDO. Afterall, who wants to get tied into an ORM
Do you find that having JDBC code directly in your code, that it is more difficult to maintain than using an ORM tool? Especially if you change say a stored procedure and add a parameter, and you have to find all the places where that procedure is called from. Definitely. I removed all of it and replaced with ORM techniques. Where thats not possible I make donation to ORM folks and ask them to make it so.
I enjoy total absense of SQL in my code (sorta). I rely totally on JDO.
There is some JDOQL or whatever its called which falls in the same category as SQL. The problem comes in when you want to use your 'data' like data and not like an object. For instance, I have had good success storing and retrieving objects, but if I try to ask "how many objects" or "how many objects with name 'x'" then it becomes a problem without some form of SQL. In this case my object may represent a class of people and I may be looking for data, or meta-data about the people. Its a tough thing since now I actually need to identify the person in order to ask questions like this, which is against the libraries attempt to act like there is no data but only objects. I can be non SQLish and load all objects into an array and sort in an totally OO fashion but that is not smart.
I dont wan't the database to only be a datastore, I want to use the power of the database to do things as well.
So I don't write SQL, but I do write JDOQL which is a form. Difference is i get the JDO library people to optimize the SQL to the particular database. Thats the purpose of JDOQL, to let them do that and not me.
My code does have two datastore architectures though. One is the MS JET database in which I use JDBC, the other is 'any database' in which I use JDO. The JDO architecture does not contain JDBC/SQL, and in general is totally independent from the database. I know nothing about the database on the other end and rely totally on the skill of the JDO library writer (JPOX).
When occasion arrises where I find need to know something about the database (SQL or parameters or settings, etc.), I complain to JDO or JPOX people that they need to save me from this. They usually agree, in principle
[ March 24, 2006: Message edited by: Mr. C Lamont Gilbert ] [ March 24, 2006: Message edited by: Mr. C Lamont Gilbert ]