Jboss Admin Tutorial: Database Integration on JBoss

15. Database Integration on JBoss

Application components deployed on JBoss that need access to a relational database can connect to it

  • Directly - by managing their own connections

    • Bloats the code
    • Requires more testing
    • Complicated deployments - requires separate configuration for each web app
    • Slow if connections are not pooled, which is not trivial to implement (though libraries exist)
    • If a connection pool is used, it cannot be shared with other applications further complicating deployments
  • Via a shared database connection pool managed by JBoss

    • Simplifies configuration and maintenance (single file to edit in a "standard" format)
    • Faster because the connections are pooled (production-tested)
    • Can be shared among applications so the connections can be better utilized
    • Applications are portable - as they don’t depend on some internal configuration of the external environment
    • Recommended!

15.1. Steps Involved

  • Define a resource references in your application

    • Require connectivity to RDBMS
  • Provide RDBMS resources (connection pools) in the server

    • Instal JDBC drivers
    • Define a RDBMS DBCP
    • Map JBoss-managed RDBMS DBCP to the application’s resource reference

15.2. Resource Requirement

For example, in a web application we would communicate our need for a container-managed RDBMS in WEB-INF/web.xml file:

<web-app ...>
  ...
  <resource-ref>
    <description>DB Connection</description>
        <res-ref-name>jdbc/NorthwindDB</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
  </resource-ref>
  ...
</web-app>
[Note]Note

The resource reference jdbc/NorthwindDB is relative to java:comp/env JNDI context (ENC). This context is unique to each application and is isolated from the rest of the system.

15.3. Install JDBC Drivers

  • JDBC Driver is what enables Java applications to talk to specific RDBMS, such as MySQL, DB2, Oracle, etc.
  • Download the JDBC Driver from the database vendor (for MySQL go to http://www.mysql.com/products/connector)
  • Copy the driver JAR into directory ${jboss.server.lib.url} or ${jboss.common.lib.url}
  • Restart JBoss
[Note]Note

In addition to its standard JDBC driver, the MySQL team has also released another connector called MXJ. This is a Java utility package (a JMX Mbean) for deploying and managing a MySQL database. MXJ requires JMX 1.2, which is available since JBoss 4.x. For more info, see http://dev.mysql.com/downloads/connector/mxj/.

15.4. Define a RDBMS DBCP Resource

  • Create a datasource (*-ds.xml) file - e.g. deploy/northwind-ds.xml:

    <datasources>
      <local-tx-datasource>
        <jndi-name>NorthwindDB</jndi-name>
            <connection-url>jdbc:mysql://localhost:3306/Northwind?autoReconnect=true</connection-url>
            <driver-class>com.mysql.jdbc.Driver</driver-class>
            <user-name>northwind</user-name>
            <password>secret</password>
            <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
            <new-connection-sql>SELECT 1</new-connection-sql>
            <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
      </local-tx-datasource>
    </datasources>
  • Start with ${jboss.home.dir}/docs/examples/jca/mysql-ds.xml as the template.
  • Some of the other common elements:

    • min-pool-size - the minimum number of pooled database connections. Initialized when the pool is first accessed. Defaults to 0.
    • max-pool-size - the maximum number of pooled database connections. Once this limit is reached, clients block. Defaults to 20.
    • blocking-timeout-millis - the maximum blocking time (in ms) while waiting on an available connection before timing out by throwing an exception. Defaults to 5000 (or 5 seconds).
    • track-statements - if true, unclosed statements are reported on check-in (via a warning message). Defaults to false.
    • idle-timeout-minutes - the maximum time (in minutes) before idle connections are closed.
[Note]Note

In JBoss AS, resources like this DataSource are relative to java:/ JNDI context (remember, this is context is accessible to all applications running in the same JVM). So to access this resource directly, we could lookup java:/NorthwindDB in JNDI.

15.5. Map our Resource

  • Map the application’s resource-ref to the real resource provided by JBoss AS
  • In case of a web application, we would create WEB-INF/jboss-web.xml:

    <jboss-web>
      <resource-ref>
        <res-ref-name>jdbc/NorthwindDB</res-ref-name>
            <res-type>javax.sql.DataSource</res-type>
            <jndi-name>java:/NorthwindDB</jndi-name>
      </resource-ref>
    </jboss-web>
  • This effectively maps java:comp/env/jdbc/NorthwindDB to java:/NorthwindDB

15.6. Using our DataSource (RDBMS DBCP)

Once mapped, the applications can access this resource to get a database connection:

Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/NorthwindDB");
Connection conn = ds.getConnection();
try {
  // use connection to create statements, etc.
} finally {
  conn.close();
}
[Important]Important

It is imperative to close the connection - as that is the mechanism of returning the connection back to the pool. Additionally, it is also very important to also close the connection-created resources, such as Statements and ResultSets, as these are are maintained in memory as long as the connection is opened, unless explicitly closed

15.7. Hypersonic Database

  • JBoss embedded Java-based RDBMS
  • deploy/hsqldb-ds.xml configures:

    • Embedded database (known as DefaultDS)
    • Connection factories
  • Used by JMS MQ for state management and persistence
  • Can be used for CMP
  • Data can be kept in memory or persisted
  • Can allow access to remote clients over TCP
  • This service is for development/testing use only. It is not production-quality.
  • To enable remote access, edit deploy/hsqldb-ds.xml:

    • Enable remote Hypersonic service:

      <mbean code="org.jboss.jdbc.HypersonicDatabase" name="jboss:service=Hypersonic">
        <attribute name="Port">1701</attribute>
        <attribute name="Silent">true</attribute>
        <attribute name="Database">default</attribute>
        <attribute name="Trace">false</attribute>
        <attribute name="No_system_exit">true</attribute>
      </mbean>
    • Change connection URL to:

      <connection-url>jdbc:hsqldb:hsql://localhost:1701</connection-url>
  • The data will be persisted to: ${jboss.server.data.dir}/hypersonic/default

15.8. Detecting Connection Leaks

  • JBoss has a CachedConnectionManager service that can be used to detect connection leaks (within the scope of a request)
  • Configured in ${jboss.server.url}/deploy/jbossjca-service.xml
  • Triggered by Tomcat’s server.xml<Host>CachedConnectionValve

    • Enabled by default - slight overhead
    • Should be used during testing
    • Can be turned off in production if the code is stable
    • If the CachedConnectionValve is enabled in Tomcat’s server.xml file, then Tomcat must wait for the CachedConnectionManager service on startup. This is accomplished by adding the following line to Tomcat’s META-INF/jboss-service.xml file (near the end):

      <depends>jboss.jca:service=CachedConnectionManager</depends>
  • Connection pools could be monitored (through JMX) by looking at jboss.jca:name=MyDS,service=ManagedConnectionPoolInUseConnectionCount attribute.
  • The example web application northwind.war can be made to leak resources (on /ListCustomers) by

    • appending requesting /ListCustomers?leak=true, and/or by
    • adding a custom system property: -Dleak.jdbc.resources=true to JAVA_OPTS in run.conf or run.bat (on Windows)

15.9. Lab: Database Connectivity

This lab will require to configure a datasource in order to use a database with the provided application.

  • For this lab, use the northwind.war application.
  • Make sure you have mysql installed
  • The data can be loaded from file northwind.sql by running the following in mysql client:

    SOURCE /path/to/northwind.sql
  • Once the data is loaded, create a database user to access Northwind database:

    GRANT ALL PRIVILEGES ON Northwind.* TO northwind@localhost IDENTIFIED BY 'secret';
  • Test that the database and the user are properly set up:

    /path/to/mysql/bin/mysql -u northwind -psecret Northwind
    mysql> SELECT count(*) FROM Customers;
  • The count should be 91.
  • Install the appropriate JDBC driver
  • Look for the required resource-reference
  • Define the needed database resource
  • Set up resource mapping
  • Deploy the application
  • Test that the application can access the referenced resource
  • Enable connection leak detection and test

Table of Contents

1. Overview of Java Enterprise Edition
1.1. What is Java EE?
1.2. Open and Standard-based
1.3. Multi-tier
1.4. Web-Enabled
1.5. Server Centric
1.6. Component-Based Distributed Architecture
1.7. Enterprise Applications
1.8. Java EE Contents
1.9. Java EE Services
2. Overview of JBoss Application Server
2.1. JBoss Organization
2.2. JBoss AS Background
2.3. Highlights of JBoss AS
2.4. What is new in JBoss AS 5?
2.5. JBoss AS Architecture
2.6. JBoss Microcontainer Layer
2.7. Services Layer
2.8. Aspect Layer
2.9. Application Layer
2.10. JBoss AS Services
2.11. JBoss AS Requirements
3. Installing JBoss AS
3.1. Getting and Installing Java
3.2. Configuring Java
3.3. Getting JBoss AS
3.4. Installing JBoss AS 5
4. JBoss Directory Structure
4.1. JBoss AS Directory Structure
4.2. The bin Directory
4.3. The client Directory
4.4. The common directory
4.5. The docs Directory
4.6. The lib Directory
4.7. The server Directory
4.8. The server Configuration Sets
4.9. The default/conf Directory
4.10. The default/data Directory
4.11. The default/deploy Directory
4.12. The default/deployers Directory
4.13. The default/lib Directory
4.14. The default/log Directory
4.15. The default/tmp Directory
4.16. The default/work Directory
5. Controlling the Life-Cycle of JBoss AS
5.1. Starting JBoss AS
5.2. Verifying JBoss AS Startup
5.3. Stopping JBoss AS
5.4. Starting From a Remote Server
6. Deployments on JBoss
6.1. Java EE Deployment Lifecycle
6.2. Deployment Descriptors
6.3. Deployment on JBoss AS
6.4. Deployers on JBoss AS
6.5. Deployment Dependencies
6.6. Hot vs. Cold Deployment
6.7. Bootstrapping JBoss
6.8. Lab: Deployment
7. Web Application Administration
7.1. Web Technologies
7.2. CGI vs. Servlets/JSPs
7.3. Tomcat Web Container
7.4. Tomcat’s server.xml
7.5. Tomcat’s web.xml
7.6. Defining and Mapping Servlets
7.7. Defining and Mapping Filters
7.8. Session Configuration
7.9. Welcome File List
7.10. Error Documents
7.11. Serving Static Content
7.12. Virtual Hosting with Tomcat
7.13. Web Access Logging
7.14. Lab: Tomcat
8. JNDI Administration
8.1. Java Naming and Directory Interface
8.2. JNDI in Java EE
8.3. JNDI on JBoss
8.4. Lab: JNDI View
9. Javamail Administration
9.1. What is JavaMail?
9.2. Configuring JavaMail Service
9.3. Lab: Mail
10. JMS Administration
10.1. JMS Overview
10.2. JMS in Java EE
10.3. When is JMS Used
10.4. JMS Architecture
10.5. JMS Messaging Domains
10.6. JMS Message Consumption
10.7. JMS on JBoss Configuration
10.8. Configure JMS connection factories
10.9. Configure JMS destinations
10.10. Advanded JBoss Messaging
10.11. JBoss Messaging bridge
10.12. Persistence service configuration
10.13. Lab: JMS
11. Enterprise Java Beans Administration
11.1. Introduction to EJB 3.0
11.2. EJB 3.0 Components
11.3. EJB Container
11.4. Benefits of EJB Technology
11.5. Drawbacks of EJBs
11.6. Session Beans
11.7. Interceptors
11.8. Entity Beans
11.9. Message-Driven Bean
11.10. Session Beans Client Interfaces
11.11. Stateless Session Beans Life Cycle
11.12. Stateful Session Beans Life Cycle
11.13. Message-Driven Beans Life Cycle
11.14. Configuring the EJB container
11.15. Stateful Session Bean Configuration
11.16. Lab: Stateless Session Bean
12. Web Services and JBoss
12.1. Web Services Overview
12.2. Service Oriented Architecture
12.3. Web Services With JAX-WS
12.4. Web Services on JBoss
12.5. JBoss Web Services Tools
12.6. Lab: Web Services
13. JMX Administration
13.1. What is JMX?
13.2. Why JMX?
13.3. JMX Architecture
13.4. JMX on JBoss AS
13.5. JMX Console
13.6. Web Console
13.7. Twiddle Tool
13.8. JBoss AS Administration Console
13.9. Lab: JMX Print Service
13.10. JBoss Monitoring
13.11. Snapshot and Web Console
13.12. Monitoring with JConsole
13.13. Scheduling on JBoss
13.14. Lab: Monitoring
14. Class Loading on JBoss
14.1. Class Namespace Isolation
14.2. Java Class Runtime Identity
14.3. Class Loading in Java EE
14.4. Class Loading On JBoss
14.5. The Class Loader
14.6. Default Class Search Order
14.7. Scoping Classes
14.8. Scoped Class Search Order
14.9. App-specific Log4J Config
14.10. Problems With Class Loading
14.11. Lab: Class Loading
15. Database Integration on JBoss
15.1. Steps Involved
15.2. Resource Requirement
15.3. Install JDBC Drivers
15.4. Define a RDBMS DBCP Resource
15.5. Map our Resource
15.6. Using our DataSource (RDBMS DBCP)
15.7. Hypersonic Database
15.8. Detecting Connection Leaks
15.9. Lab: Database Connectivity
16. Security on JBoss
16.1. Securing Applications
16.2. Filtering Clients by Source
16.3. Authentication & Authorization
16.4. Requiring A&A
16.5. Plain-Text Login Module
16.6. Database Login Module
16.7. FORM-based Login
16.8. Configuring JBoss AS for SSL
16.9. Creating SSL Certificates
16.10. Configure SSL Connector
16.11. Testing SSL Configuration
16.12. Requiring SSL in Apps
16.13. Lab: Application Security
16.14. Securing JMS destinations
16.15. Securing JBoss AS
16.16. JBoss AS System User
16.17. File System Security
16.18. Securing JMX Invoker
16.19. Securing JBoss Applications
16.20. Securing Hypersonic DB
16.21. Java Security Manager
16.22. Running Behind a Firewall
16.23. Lab: JBoss Security
17. Tuning JBoss
17.1. JVM Tuning
17.2. Tomcat Tuning
17.3. RMI Tuning
17.4. Log4J Tuning
17.5. Tuning Other Services
17.6. JMS Tuning
17.7. Slimming JBoss
18. High Availability and Scalability on JBoss
18.1. Requirements
18.2. Clustering: General understanding
18.3. Clustering and JBoss
18.4. Simple Web Architecture
18.5. External Load Balancer Architecture
18.6. Smart Proxy Architecture
18.7. General configuration for the following examples
18.8. Fronting with a Web Server
18.9. Fronting with Apache HTTPD
18.10. Installing mod_jk
18.11. Configuring mod_jk
18.12. Simple Load Balancing
18.13. Enabling Sticky Sessions
18.14. Clustered Session Replication
18.15. Clustering Single Sign-On
18.16. Clustering with HA-JNDI
18.17. HA-JNDI Client Configuration
18.18. Clustering with HA-JMS
18.19. Clustering with Stateless Session Beans
18.20. Clustering with Stateful Session Beans
18.21. Lab: Clustering