JavaSercer Pages Chapter 17. Developing Database Access Components (Web hosting ssh)
JavaSercer Pages Chapter 17. Developing Database Access Components In this final chapter, we look at more examples of how to develop custom actions, namely the database custom actions introduced in Chapter 9. Before digging into the code for these actions, a number of fundamental Java database access features are discussed. First, we take a look at the JDBC Connection class, and how pooling Connection objects helps solve a number of common problems. We look at two ways to provide connection pooling capabilities to an application: with JDBC 2.0, and by letting a JDBC 1.0 connection pool simulate a JDBC 2.0 pool. The purpose of a connection pool is to be able to share database connections between all components of an application. The approach discussed in this chapter is to use an application initialization servlet that makes the pool available to all servlets and JSP pages. No matter if you use a servlet or a custom action in a JSP page to access the database, there are a number of things to think about. We look at a generic database access bean and related classes that take care of datatype issues and make the result of a query easy to access. Next, we look at how the bean is used by the database access custom actions described in Chapter 9. You can also use the bean directly in servlets, as described in Chapter 15, or in your own application-specific database access actions. The last section contains an example of an application-specific custom action using the bean. To really appreciate the material in this chapter, you should already be familiar with JDBC. If this is not the case, I recommend that you look at the JDBC documentation online at http://java.sun.com/products/jdbc/ or read a book about JDBC, such as George Reese’s Database Programming with JDBC and Java (O’Reilly). 17.1 Using Connections and Connection Pools In a JDBC-based application, a lot revolves around the java.sql.Connection interface. Before any database operations can take place, the application must create a Connection to the database. It then acts as the communication channel between the application and the database, carrying the SQL statements sent by the application and the results returned by the database. A Connection is associated with a database user account, to allow the database to enforce access control rules for the SQL statements submitted through the Connection. Finally, the Connection is also the boundary for database transactions. Only SQL statements executed through the same Connection can make up a transaction. A transaction consists of a number of SQL statements that must either all succeed or all fail as one atomic operation. A transaction can be committed (the changes resulting from the statements are permanently saved) or rolled back (all changes are ignored) by calling Connection methods. In a standalone application, a Connection is typically created once and kept open until the application is shut down. This is not surprising, since a standalone application serves only one user at a time, and all database operations initiated by a single user are typically related to each other. In a server application that deals with unrelated requests from many different users, it’s not so obvious how to deal with connections. There are three things to consider: a Connection is time-consuming to create, it must be used for only one user at a time to avoid transaction clashes, and it is expensive to keep open. Creating a Connection is an operation that can actually take a second or two to perform. Besides establishing a network connection to the database, the database engine must authenticate the user and create a context with various data structures to keep track of transactions, cached statements, results, and so forth. Creating a new Connection for each request received by the server, while simple to implement, is far too time- consuming in a high-traffic server application. One way to minimize the number of times a connection needs to be created is to keep one Connection per servlet or JSP page that need access to the database. A Connection can be created when the web resource is initialized, and kept in an instance variable until the application is shut down. As you will discover when you deploy an application based on this approach, this route will lead to numerous multithreading issues. Each request executes as a separate thread through the same servlet or JSP page. Many JDBC drivers do not support multiple threads accessing the same Connection, causing all kinds of runtime errors. Others support it by serializing all calls, leading to poor scalability. An even more serious problem with this approach is that requests from multiple users, all using the same Connection, operate within the same transaction. If one request leads to a rollback, all other database operations using the same Connection are also rolled back. page 235
Note: If you are looking for cheapest and affordable webspace to host and run your servlet application check Astra j2ee hosting services