I guess, now I am going to disagree to prove that Dave indeed is right !There will be as many opinions around as people.
I use stored procedures in most of the scenarios.Especially when working in the corporate and enterprise scenarios, your application is never gonna be the only application to access the data. Do you want to duplicate your data access logic in all those apps ? What if another application is .Net based ? You cannot use your same Java objects there. Now if you don't enforce your rules in the DB, the next application that talks to your data can very well compromise your data integrity.
Enterprise application are all about managing data. What changes is the delivery mechanism. It was client server first, now its web based. Tomorrow there will be some other paradigm. Java is not the last language. Paradigms will change.. data in the database won't. You can use Stored Procs from all your clients - Java based, .Net Based, PRO C or PHP based.
I have seen many Java programs using SQL / Jdbc directly in the java code. If I have to tune that query , then it would mean changing Java class. I can do so in Procedures directly where SQL is more readable, easily tunable and maintainable (as far as maintenance ask DBA's they will agree).
Other thing that people go JDBC way is to have DB independance. Well.. how much really can you acheive that ? Complying to SQL 92 std will only give you basic CRUD things. If you really want to exploit the power of SQL, you will be in need some point to use functionality like DECODE, TO_DATE , NVL, Analytical functions (in Oracle). If you have this SQL in Java JDBC client you are anyway depending on DB. Now add the fact that every db implement concurrency, locking differently. Only Oracle provides the multiversioning and non blocking reads right now. (SQL '05 will provide it with entirely new isolation level called snapshot) It's very difficult to acheive DB independence for truly scalable enterprise apps. And in my experience I have never seen any enterprise application yet changing the DB. MS apps were convereted to J2ee, some J2ee apps were converted to .Net .. some .net apps were converted to j2ee... however DB remained same.
PL/SQL, T-SQL all these languages are built for only one reason - tight integration with SQL and to exploit power of SQL. Use right tool to do right things .. we say that all the time dont we ?
Middle tier - all about application logic,presentation, content delivery mechanisms, integration with 3rd party system using webservices / Message Oriented Softwares.
DB - all about data access / business logic.
Do same things on the middle tier that you used to do in client tier env.
Java is good for computational intesive stuff. Stored Procs are good for SQL intensive stuff, reducing round trips to DB and having centrlized data access logic.
As far as difference between preparedstatement and SP goes, if you gonna do a SELECT / INSERT / UPDATE and your SP's gonna have just one SQL statement then, there's no difference between two. PreparedStatement have the advantage that, it always use bind variables protecting developer from misusing them, however all SQL is dynamic and Hard to read ! SP can use static SQL, explicit cursors / implicit cursors and proper use of bind variables in dynamic SQL.. giving better performance and allowing you to use features that clients paid for.