Java Database Connectivity Key points

1. Use Type two driver for two tiered applications to communicate from java client to database that gives better performance than Type1 driver.
2. Use Type four driver for applet to database communication that is two tiered applications and three tiered applications when compared to other drivers.
3. Use Type one driver if you don't have a driver for your database. This is a rare situation because all major databases support drivers or you will get a driver from third party vendors.
4. Use Type three driver to communicate between client and proxy server ( weblogic, websphere etc) for three tiered applications that gives better performance when compared to Type 1 &2 drivers.
5. ass database specific properties like defaultPrefetch if your database supports any of them.
6. Get database connection from connection pool rather than getting it directly
7. Use batch transactions.
8. Choose right isolation level as per your requirement. TRANSACTION_READ_UNCOMMITED gives best performance for concurrent transaction based applications. TRANSACTION_NONE gives best performance for non-concurrent transaction based applications.
9. Your database server may not support all isolation levels, be aware of your database server features.
10. Use PreparedStatement when you execute the same statement more than once.
11. Use CallableStatement when you want result from multiple and complex statements for a single request.
12. Use batch update facility available in Statements.
13. Use batch retrieval facility available in Statements or ResultSet.
14. Set up proper direction for processing rows.
15. Use proper getXXX() methods.
16. Close ResultSet, Statement and Connection whenever you finish your work with them.
17. Write precise SQL queries.
18. Cache read-only and read-mostly tables data.
19. Fetch small amount of data iteratively rather than whole data at once when retrieving large amount of data like searching database etc.