JBoss 5.1 - Chapter 02 .1 - Configuring the connection to the database

Title

DB Connection

Author

Bernard Szlachta (NobleProg Ltd)

Setup Database ⌘

 create user juser;
 grant all on test.* to juser@localhost identified by 'juser';
 create table user (id numeric,user varchar(20));
 insert into user values (1,"Bernard");
 insert into user values (2,"John");

Setup MySQL Connector ⌘

1. Download mysql-connector-java-5.1.25-bin.jar and copy it to deploy folder
2. Copy jboss-as/docs/examples/jcs/mysql-ds.xml to deploy folder
3. Adjust data as show below

<?xml version="1.0" encoding="UTF-8"?>                                                                                                         
<datasources>                                                                                                                                  
  <local-tx-datasource>                                                                                                                        
    <jndi-name>MySqlDS</jndi-name>                                                                                                             
    <connection-url>jdbc:mysql://localhost:3306/test</connection-url>                                                                          
    <driver-class>com.mysql.jdbc.Driver</driver-class>                                                                                         
    <user-name>juser</user-name>                                                                                                               
    <password>juser</password>                                                                                                                 
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>                                                               
    <metadata>                                                                                                                                 
       <type-mapping>mySQL</type-mapping>                                                                                                      
    </metadata>                                                                                                                                
  </local-tx-datasource>                                                                                                                       
</datasources>                                                                                                                                 
</xml>

Testing Connection ⌘

1. Create jdbc-client.war folder in deploy folder
2. Create index.jsp file inside the jdbc-client.war folder and paste the text below

<%@page contentType="text/html"                                                                                                                
 import="java.util.*,javax.naming.*,javax.sql.DataSource,java.sql.*"                                                                           
 %>                                                                                                                                            
 <%                                                                                                                                            
                                                                                                                                               
  DataSource ds = null;                                                                                                                        
  Connection con = null;                                                                                                                       
  PreparedStatement pr = null;                                                                                                                 
  InitialContext ic;                                                                                                                           
  try {                                                                                                                                        
  ic = new InitialContext();                                                                                                                   
  ds = (DataSource)ic.lookup( "java:/MySqlDS" );                                                                                               
  con = ds.getConnection();                                                                                                                    
  pr = con.prepareStatement("SELECT * FROM user");                                                                                             
  ResultSet rs = pr.executeQuery();                                                                                                            
  while (rs.next()) {                                                                                                                          
  out.println("<br> " +rs.getString("ID") + " | " +rs.getString("USER"));                                                                      
  }                                                                                                                                            
  rs.close();                                                                                                                                  
  pr.close();                                                                                                                                  
  }catch(Exception e){                                                                                                                         
  out.println("Exception thrown " +e);                                                                                                         
  }finally{                                                                                                                                    
  if(con != null){                                                                                                                             
  con.close();                                                                                                                                 
 }                                                                                                                                             
} %>

Configuring Connection in Admin Console ⌘

• Goto Resources/Data Sources
• Edit MySqlDS

Adjusting Web Threads, EJB Threads and Connection Threads ⌘

Configuring Connection Thread ⌘

• In JMX Console go to jboss.web name=http-0.0.0.0-8080,type=ThreadPool
• Change the number of threads to 4
• Run JMeter test
• What should be the number of Web Threads in case of our JSP app

Bigger data ⌘

• Create more rows *use create-users.sh script in 00_JSP
• Run the script again
• http://t1.nobl.pw:8080/admin-console/secure/resourceInstanceMetrics.seam?path=-9&conversationId=57&conversationPropagation=end
• Do you need more connections?

Configuring Connection Thread Pool ⌘

• JBoss uses Java Connector Architecture (JCA)
• Files with datasoure configuration are *-ds.xml
• DefaultSD-ds.xml uses HSQL, apps like admin-console, etc... use this setting

Transaction Support ⌘

no-tx-datasource

does not take part in JTA

local-tx-datasoure

supports JTA transactions, but not two-phase-commit

xa-datasource

supports two-phase commit

Transaction Isolation ⌘

• transaction-isolation element

• TRANSACTION_READ_UNCOMMITTED
• TRANSACTION_READ_COMMITTED
• TRANSACTION_REPEATABLE_READ
• TRANSACTION_SERIALIZABLE
• TRANSACTION_NONE

Connection Pool statistics ⌘

• Use admin-console
• In JMX-console use name=jdbc/MySqlDS,service=
  • DataSourceBinding
  • ManagedConnectionFactory
  • ManagedConnectionPool
  • XATxCM