Using Data Sources

Once a data source is configured and deployed, you can use it within a J2EE application by locating it in JNDI, obtaining a connection, performing some work, and then closing the connection. In this example from earlier in the chapter, you use a data source to deactivate products whose expiration date has passed:

 // First, create a JNDI InitialContext so that you can look things up in JNDI InitialContext context = new InitialContext(); // Next, look the data source at the JNDI location you configured in data- sources.xml DataSource ds = (DataSource) context.lookUp("jdbc/ProductCatalogDS"); // Declare your connection var outside the try block so that you can clean it up later Connection c = null; try {     // Get connection from the data source     c = ds.getConnection();     // Execute some SQL command     Statement s = c.createStatement();     s.execute("UPDATE product SET active=0 WHERE expiration_date < sysdate");     s.close(); } finally {     // Close connection so the database can use those resources for another request     c.close(); } 

Determining the Correct JNDI Location

In the example, you looked up the data source at the JNDI location, jdbc/ProductCatalogDS . If you're using a normal DriverManagerDataSource , then this value should correspond to the ejb-location value that you specified for that data source in data-sources.xml . If this isn't a DriverManagerDataSource , then you should use the value of location instead.

Note 

In practice, you'll often use <resource-ref> elements in your deployment descriptors to remap these global JNDI locations to local names . This general J2EE technique improves portability and is described fully in Chapter 8.

Overriding the Default Username and Password

Though you'll usually configure a default username and password in data-sources.xml , you can override this by specifying a different username and password when obtaining a connection from your data source, like this:

 con = ds.getConnection("scott2", "tiger2"); 

This can be useful for applications with a limited number of users, particularly if you need to implement row-level security. Instead of embedding that logic in your application, you can use the database to enforce access controls and always connect to it using the login credentials of the current application user . It's important to note that multiple connections obtained from the same data source within a single transaction will share a single underlying "physical" connection, for which the first username and password specified will always be used. Attempting to use a different username with that DataSource object within the same transaction will cause an exception. If this behavior isn't desirable, you can use a second data source or adjust your EJB transaction settings to force the use of a separate transaction for the alternate login.

Using Oracle JDBC Extensions

Many Oracle-specific features are not available through the standard JDBC APIs. To access these features, you can cast your Connection object to oracle.jdbc.OracleConnection . This object can then be used to obtain Oracle-specific statements, result sets, and so on.

Of course, tying your code to Oracle in this way reduces your application's database portability. If it may ever need to run against a different database, then you should try to isolate Oracle-specific chunks inside instanceof tests or in easily overridden methods .