Catálogo de publicaciones - libros

In this chapter, we learned how to materialize collections (nested tables and varrays) in a JDBC application as Java objects. We looked at various JDBC standard classes and Oracle extensions. We learned how to use collections in JDBC program using weakly typed classes and strongly typed custom classes generated by JPublisher utility. We also learned about various performance extensions that Oracle provides you with and measured their effectiveness on data retrieval speeds. Finally we learned what references are, and why you should avoid them in general. We also learned how to query and update them using weakly typed Ref and REF classes or using strongly typed custom classes created either manually or using JPublisher utility.

In this chapter, you learned what LOBs are and how they are useful in storing large text and binary data. You learned how to read from and write to CLOBs and BLOBs, and how to read from BFILEs. You then looked at some Oracle-specific alternatives to using BFILE: the UTL_FILE PL/SQL package and external tables. Also, you discovered that UTL_FILE can be used to read and write to text and binary files, whereas external tables can be used only to read from text files. Finally, you compared these alternatives in terms of features and performance.

In this chapter, you revisited the concepts of cursors and ref cursors. You learned about session cursor cache and PL/SQL cursor cache, and you saw how they can improve the performance of your application. You then explored in detail the concept of JDBC statement caching. You learned about implicit and explicit statement caching, and how to use them in your JDBC programs to avoid unnecessary soft parses. You also compared implicit and explicit statement caches in terms of features and performance, and you discovered that in almost all cases implicit caching is the way to go. The chapter concluded with a summary of the differences between session cursor cache, PL/SQL cursor cache, and JDBC statement caching.

In this chapter, you learned the difference between connections and sessions in Oracle. You learned why connection pooling is necessary, and you distinguished between connection pooling and caching. You examined how Oracle9 implements the connection pooling framework and provides a sample connection caching implementation. As you saw, in Oracle 10, the Oracle9 connection caching has been deprecated and replaced by the more powerful implicit connection caching. Finally, you took a look at how Oracle’s OCI connection pooling improves scalability and performance by creating lightweight sessions on top of a low number of physical connections.

In this chapter, you learned about the security principles of least privilege and defense in depth. You looked at various ways of mapping an application end user to a database user for authentication purposes and each method’s pros and cons. You examined proxy authentication (a JDBC OCI driver feature as of Oracle 10 Release 1 also known as n-tier authentication) in detail, and you learned how this technique can elegantly solve the problems of authentication and password management for a web application. The proxy authentication feature allows the middle tier to authenticate itself to the database on behalf of an end user database account without supplying the end user database account’s password. You also learned how auditing captures the end user identity as well as the proxy user identity.

In this chapter, you learned about the problem known as “lost updates.” You looke various techniques you can use to solve this problem. Changing the transaction isolation level to SERIALIZABLE can solve the problem in some special cases. The technique of locking the row beforehand (pessimistic locking) solves the problem for cases where you maintain state across pages. Optimistic locking solves the problem by detecting the changes made by another transaction and asking the user to retry his transaction if unsuccessful. In most of the web applications that use connection pooling, optimistic locking is the only real alternative since it does not require the application to maintain connections across pages. You examined several implementations of optimistic locking, and finally briefly compared optimistic and pessimistic locking.

In this chapter, you learned why PL/SQL is such an important tool for a JDBC developer. You examined some PL/SQL techniques to increase the performance and maintainability of your code. These techniques included putting your code in packages, using bulk operations to improve performance, and preferring static SQL to dynamic SQL when you have a choice. You also learned about invoker rights and definer rights modes in PL/SQL, and when to use each mode. You then looked at PL/SQL debugging techniques and enhanced a custom PL/SQL package called debug, using which you can instrument your Java and PL/SQL code.