JBoss SOA/ESB DataSource Configuration using MySql

說明 JBoss SOA/ESB 5.0 配置 MySQL DataSource。

準備執行環境

預設JBoss SOA/ESB 5.0 及 MySQL Server 5.1 已配置這裡不再贅述

• JBoss SOA Server 5.0.2.GA+
• JBoss ESB Server 4.10+
• MySQL Server 5.1

%JBOSS_HOME%設定為jboss-soa-5.0\jboss-as，須跟JBoss EAP做區別。

JBoss SOA Server Configuration

1. 請參照 JBoss 5.0 DefaultDS Configuration using MySql 設定 JDBC driver for MySQL、mysql-ds.xml、mysql-persistence-service.xml ，只是設定對象由 JBoss EAP 改為 JBoss SOA 。


2. 比照 mysql-ds.xml 如下修改 %JBOSS_HOME%\server\PROFILE\deploy\jbossesb.esb\message-store-ds.xml 。 (here PROFILE means all,produciton,default etc)

<?xml version="1.0" encoding="UTF-8"?>

<datasources>
   <local-tx-datasource>
      <jndi-name>JBossESBDS</jndi-name>
      <use-java-context>true</use-java-context>
      <connection-url>jdbc:mysql://localhost:3306/soadatabase</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>soausername</user-name>
      <password>soapassword</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>



3. 如下修改 %JBOSS_HOME%\server\PROFILE\deploy\jbossesb.esb\jbossesb-service.xml ，將參數 SqlFiles 的路徑由 hsqldb 改為 mysql。

<?xml version="1.0" encoding="UTF-8"?>

<server>
   <mbean code="org.jboss.internal.soa.esb.dependencies.DatabaseInitializer"
       name="jboss.esb:service=MessageStoreDatabaseInitializer">
      <attribute name="Datasource">java:/JBossESBDS</attribute>
      <attribute name="ExistsSql">select count(*) from message</attribute>
      <attribute name="SqlFiles">
         message-store-sql/mysql/create_database.sql
      </attribute>
      <depends>jboss.jca:service=DataSourceBinding,name=JBossESBDS</depends>
   </mbean>
</server>



4. 如下修改 %JBOSS_HOME%\server\PROFILE\deploy\jbossesb-registry.sar\juddi-ds.xml ，<local-tx-datasource> 比照 message-store-ds.xml 修改，參數 SqlFiles 則修改為 mysql 路徑。

<?xml version="1.0" encoding="UTF-8"?>

<datasources>
   <local-tx-datasource>
      <jndi-name>juddiDB</jndi-name>
      <use-java-context>true</use-java-context>
      <connection-url>jdbc:mysql://localhost:3306/soadatabase</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>soausername</user-name>
      <password>soapassword</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>

   <mbean code="org.jboss.internal.soa.esb.dependencies.DatabaseInitializer"
      name="jboss.esb:service=JUDDIDatabaseInitializer">
      <attribute name="Datasource">java:/juddiDB</attribute>
      <attribute name="ExistsSql">select count(*) from  j3_publisher</attribute>
      <attribute name="SqlFiles">
         juddi-sql/mysql/import.sql
      </attribute>
      <depends>jboss.jca:service=DataSourceBinding,name=juddiDB</depends>
   </mbean>
</datasources>



5. 如下修改 %JBOSS_HOME%\server\PROFILE\deploy\jbossesb-registry.sar\juddi_config\META-INF\persistence.xml ，修改hibernate.dialect 為 MySQL5Dialect，僅節錄修改的部份。

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd" 
             version="1.0">
  <persistence-unit name="juddiDatabase" transaction-type="RESOURCE_LOCAL">

....

    <properties>
      <property name="hibernate.archive.autodetection" value="class"/>
      <property name="hibernate.show_sql" value="false"/>
      <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />
    </properties>
  </persistence-unit>
</persistence>



6. 複製 jboss-soa-5.0\jbpm-jpdl\config\hibernate.cfg.mysql.xml -> %JBOSS_HOME%\server\PROFILE\deploy\jbpm.esb\hibernate.cfg.xml ，並將相關參數修改如下，僅節錄修改的部份。

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
  "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
  <session-factory>

    <!-- hibernate dialect -->
    <property name="hibernate.dialect">org.jbpm.db.hibernate.MySQLDialect</property>

    <!-- JDBC connection properties (begin) -->
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/soadatabase</property>
    <property name="hibernate.connection.username">soausername</property>
    <property name="hibernate.connection.password">soapassword</property>
    <property name="hibernate.connection.isolation">2</property>
    <property name="hibernate.query.substitutions">true 1, false 0</property>
    <!-- JDBC connection properties (end) -->

....

  </session-factory>
</hibernate-configuration>



7. 複製 jboss-soa-5.0\jbpm-jpdl\config\jbpm-mysql-ds.xml -> %JBOSS_HOME%\server\PROFILE\deploy\jbpm.esb\jbpm-ds.xml ，並將相關參數修改如下。

<?xml version="1.0" encoding="UTF-8"?>

<datasources>
  <xa-datasource>
    <jndi-name>JbpmDS</jndi-name>

    <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
    <xa-datasource-property name="ServerName">localhost</xa-datasource-property>
    <xa-datasource-property name="PortNumber">3306</xa-datasource-property>
    <xa-datasource-property name="DatabaseName">soadatabase</xa-datasource-property>
    <user-name>soausername</user-name>
    <password>soapassword</password>

    <!-- reduce isolation from the default level (repeatable read) -->
    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
    <!-- disable transaction interleaving -->
    <track-connection-by-tx />

    <!-- leverage mysql integration features -->
    <exception-sorter-class-name>
      com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter
    </exception-sorter-class-name>
    <valid-connection-checker-class-name>
      com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker
    </valid-connection-checker-class-name>

    <!-- corresponding type-mapping in conf/standardjbosscmp-jdbc.xml -->
    <type-mapping>mySQL</type-mapping>
  </xa-datasource>
</datasources>



8. 如下修改 %JBOSS_HOME%\server\PROFILE\deploy\jbpm.esb\jbpm-service.xml ，參數 SqlFiles 修改為 jbpm.jpdl.mysql.sql 的檔名，僅節錄修改的部份。

<?xml version="1.0" encoding="UTF-8"?>

<server>
   <mbean code="org.jboss.internal.soa.esb.dependencies.DatabaseInitializer"
       name="jboss.esb:service=JBPMDatabaseInitializer">
      <attribute name="Datasource">java:/JbpmDS</attribute>
      <attribute name="ExistsSql">select count(*) from JBPM_ID_USER</attribute>
      <attribute name="SqlFiles">
      jbpm-sql/jbpm.jpdl.mysql.sql,
      jbpm-sql/import.sql
      </attribute>
      <depends>jboss.jca:service=DataSourceBinding,name=JbpmDS</depends>
      <attribute name="UseEOL">true</attribute>
   </mbean>

....

</server>



9. Start MySQL，接著Start JBoss SOA，JBoss SOA 正常啟動也可看到建立如下 Table，check log 看是否配置成功。

相關設定可參考：
Migrating JBoss ESB 4.5 GA from hsqldb to mysql
JBossESB Administration Guide Switching Databases
JBossESBSwitchDatabase
Switching database to MySQL in JBoss AS 5.1 for JBossESB 4.9
myjbpmtour - hibernate.cfg.mysql.xml