I recently come up to the problem of switching to a database production enviroment for our JBossESB, I have found two really helpfull articles:
http://www.jboss.org/community/wiki/JBossESBSwitchDatabase from here in Jboss Comunity and this one:
http://soaengine.wordpress.com/2009/09/16/switch-jbossas-4-2-3-and-jbossesb-4-5-from-hsql-to-mysql/ from SOA Engine.
And as a not experienced developer in JBossESB I hit my self with some large and quite beautifull exceptions , of course the articles wasn't made for my currently working enviroment. I will post my experience hoping that others could benefit from this saving their time.
As said before made my deployment of JBossesb-4.7 in JBoss.4.2.3.GA and wanted to change from HSQLDB to Postgresql.8.4.0. After successfully install the ESB in JBoss server the files I proceed to change were these:
<jbossesb_deploy_dir>/postgresql-ds.xml *this was replaced for hsqldb-ds.xml
<jbossesb_deploy_dir>/jbossesb-registry.sar\juddi-ds.xml
<jbossesb_deploy_dir>/jbossesb.esb\message-store-ds.xml
<jbossesb_deploy_dir>/jbossesb.esb\jbossesb-service.xml
<jbossesb_deploy_dir>/jbossesb-registry.sar\META-INF\persistence.xml
<jbossesb_deploy_dir>/jbpm.esb\jbpm-service.xml
<jbossesb_deploy_dir>/jbpm.esb\jbpm-quartz.properties
<jbossesb_deploy_dir>/jbpm.esb\hibernate.cfg.xml
<jbossesb_deploy_dir>/jbpm.esb\jbpm-ds.xml
<jbossesb_deploy_dir>/jms\jms-ds.xml
<jbossesb_deploy_dir>/jms\postgres-jdbc2-service.xml *this was replaced for hsqldb-jdbc2-service.xml
<jbossesb_deploy_dir>/jms\postgres-jdbc-state-service.xml *this was replaced for hsqldb-jdbc-state-service.xml
Where <jbossesb_deploy_dir>/ is the jboss server deployed instance you are runnig, in my case: W:\javabin\jboss-4.2.3.GA\server\default\deploy\
The listing for these file are this (notice please, similar to the referenced articles)
postgresql-ds.xml:
{code:xml}
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>DefaultDS</jndi-name>
<connection-url>jdbc:postgresql://localhost:5432/jbossesb</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>[dbuser_name]</user-name>
<password>[password]</password>
<metadata>
<type-mapping>PostgreSQL 7.2</type-mapping>
</metadata>
<check-valid-connection-sql>select count(*) from jms_users</check-valid-connection-sql>
</local-tx-datasource>
</datasources>
{code}
juddi-ds.xml:
{code:xml}
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>juddiDB</jndi-name>
<connection-url>jdbc:postgresql://localhost:5432/jbossesb</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>[dbusername]</user-name>
<password>[password]</password>
<metadata>
<type-mapping>PostgreSQL 7.2</type-mapping>
</metadata>
<check-valid-connection-sql>select count(*) from jms_users</check-valid-connection-sql>
</local-tx-datasource>
</datasources>
{code}
message-store-ds.xml:
{code:xml}
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>JBossESBDS</jndi-name>
<connection-url>jdbc:postgresql://localhost:5432/jbossesb</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>[dbusername]</user-name>
<password>[password]</password>
<metadata>
<type-mapping>PostgreSQL 7.2</type-mapping>
</metadata>
<check-valid-connection-sql>select count(*) from jms_users</check-valid-connection-sql>
</local-tx-datasource>
</datasources>
{code}
jbossesb-service.xml
{code:xml}
<?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/postgresql/create_database.sql
</attribute>
<depends>jboss.jca:service=DataSourceBinding,name=JBossESBDS</depends>
</mbean>
</server>
{code}
persistence.xml
{code:xml}
....
....
<properties>
<property name="hibernate.archive.autodetection" value="class"/>
<property name="hibernate.hbm2ddl.auto" value="update"/>
<property name="hibernate.show_sql" value="false"/>
<!--<property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" />-->
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />
</properties>
....
....
{code}
jbpm-service.xml
{code:xml}
<?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.postgresql.sql,
jbpm-sql/import.sql
</attribute>
<depends>jboss.jca:service=DataSourceBinding,name=JbpmDS</depends>
<attribute name="UseEOL">true</attribute>
</mbean>
<mbean code="org.jboss.soa.esb.services.jbpm.configuration.JbpmService"
name="jboss.esb:service=JbpmService">
<depends>jboss.esb:service=JBPMDatabaseInitializer</depends>
<depends>jboss:service=ClientUserTransaction</depends>
</mbean>
</server>
{code}
jbpm-quartz.properties
{code}
org.quartz.scheduler.instanceName = JBPMQuartzScheduler
org.quartz.scheduler.wrapJobExecutionInUserTransaction = true
org.quartz.scheduler.userTransactionURL=UserTransaction
org.quartz.scheduler.instanceId = AUTO
org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.threadCount = 10
org.quartz.threadPool.threadPriority = 5
org.quartz.threadPool.threadsInheritContextClassLoaderOfInitializingThread = true
org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreCMT
org.quartz.jobStore.dataSource=JbpmDS
org.quartz.jobStore.nonManagedTXDataSource=JbpmDS
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostreSQLDelegate
#org.quartz.jobStore.isClustered = true
#org.quartz.jobStore.clusterCheckinInterval = 20000
org.quartz.dataSource.jbpmDS.jndiURL=java:/JbpmDS
{code}
hibernate.cfg.xml
{code:xml}
...
...
<!-- JDBC connection properties (begin) ===
==== JDBC connection properties (end) -->
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/jbossjbpm</property>
<property name="hibernate.connection.username">[dbuser name]</property>
<property name="hibernate.connection.password">[password]</property>
...
...
{code}
jbpm-ds.xml
{code:xml}
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>JbpmDS</jndi-name>
<connection-url>jdbc:postgresql://localhost:5432/jbossjbpm</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>[dbusername]</user-name>
<password>[password]</password>
<metadata>
<type-mapping>PostgreSQL 7.2</type-mapping>
</metadata>
<check-valid-connection-sql>select count(*) from JBPM_ID_USER</check-valid-connection-sql>
</local-tx-datasource>
</datasources>
{code}
jms-ds.xml
{code}
<?xml version="1.0" encoding="UTF-8"?>
<connection-factories>
<!-- ==================================================================== -->
<!-- JMS Stuff -->
<!-- ==================================================================== -->
<!-- The JMS provider loader -->
<mbean code="org.jboss.jms.jndi.JMSProviderLoader"
name="jboss.mq:service=JMSProviderLoader,name=JMSProvider">
<attribute name="ProviderName">DefaultJMSProvider</attribute>
<attribute name="ProviderAdapterClass">
org.jboss.jms.jndi.JNDIProviderAdapter
</attribute>
<!-- The combined connection factory -->
<attribute name="FactoryRef">java:/XAConnectionFactory</attribute>
<!-- The queue connection factory -->
<attribute name="QueueFactoryRef">java:/XAConnectionFactory</attribute>
<!-- The topic factory -->
<attribute name="TopicFactoryRef">java:/XAConnectionFactory</attribute>
<!-- Uncomment to use HAJNDI to access JMS
<attribute name="Properties">
java.naming.factory.initial=org.jnp.interfaces.NamingContextFactory
java.naming.factory.url.pkgs=org.jboss.naming:org.jnp.interfaces
java.naming.provider.url=localhost:1100
</attribute>
-->
</mbean>
<!-- The server session pool for Message Driven Beans -->
<mbean code="org.jboss.jms.asf.ServerSessionPoolLoader"
name="jboss.mq:service=ServerSessionPoolMBean,name=StdJMSPool">
<depends optional-attribute-name="XidFactory">jboss:service=XidFactory</depends>
<attribute name="PoolName">StdJMSPool</attribute>
<attribute name="PoolFactoryClass">
org.jboss.jms.asf.StdServerSessionPoolFactory
</attribute>
</mbean>
<!-- JMS XA Resource adapter, use this to get transacted JMS in beans -->
<tx-connection-factory>
<jndi-name>JmsXA</jndi-name>
<xa-transaction/>
<rar-name>jms-ra.rar</rar-name>
<connection-definition>org.jboss.resource.adapter.jms.JmsConnectionFactory</connection-definition>
<config-property name="SessionDefaultType" type="java.lang.String">javax.jms.Topic</config-property>
<config-property name="JmsProviderAdapterJNDI" type="java.lang.String">java:/DefaultJMSProvider</config-property>
<security-domain-and-application>JmsXARealm</security-domain-and-application>
<max-pool-size>20</max-pool-size>
</tx-connection-factory>
</connection-factories>
{code}
finally these two file can be grabbed fro: <jboss_home>/docs/examples/jms/
postgres-jdbc2-service.xml
postgres-jdbc-state-service.xml
Notice that only two databases were used, "jbossesb" and "jbossjbpm" in the first one is stored all jms messages and esb stuff and all jboss bpm is stored in the last one, this can be changed to have more than two databases in servers, just change the datasources configurations, that's up to you.
After modifying/replacing these files launch your sever, maybe it will complain about non existent relation, that will be the first time because when checking for valid conections it can not found the not yet created tables, the next time you start up everything should be ok.
Hope this help
http://www.jboss.org/community/wiki/JBossESBSwitchDatabase from here in Jboss Comunity and this one:
http://soaengine.wordpress.com/2009/09/16/switch-jbossas-4-2-3-and-jbossesb-4-5-from-hsql-to-mysql/ from SOA Engine.
And as a not experienced developer in JBossESB I hit my self with some large and quite beautifull exceptions , of course the articles wasn't made for my currently working enviroment. I will post my experience hoping that others could benefit from this saving their time.
As said before made my deployment of JBossesb-4.7 in JBoss.4.2.3.GA and wanted to change from HSQLDB to Postgresql.8.4.0. After successfully install the ESB in JBoss server the files I proceed to change were these:
<jbossesb_deploy_dir>/postgresql-ds.xml *this was replaced for hsqldb-ds.xml
<jbossesb_deploy_dir>/jbossesb-registry.sar\juddi-ds.xml
<jbossesb_deploy_dir>/jbossesb.esb\message-store-ds.xml
<jbossesb_deploy_dir>/jbossesb.esb\jbossesb-service.xml
<jbossesb_deploy_dir>/jbossesb-registry.sar\META-INF\persistence.xml
<jbossesb_deploy_dir>/jbpm.esb\jbpm-service.xml
<jbossesb_deploy_dir>/jbpm.esb\jbpm-quartz.properties
<jbossesb_deploy_dir>/jbpm.esb\hibernate.cfg.xml
<jbossesb_deploy_dir>/jbpm.esb\jbpm-ds.xml
<jbossesb_deploy_dir>/jms\jms-ds.xml
<jbossesb_deploy_dir>/jms\postgres-jdbc2-service.xml *this was replaced for hsqldb-jdbc2-service.xml
<jbossesb_deploy_dir>/jms\postgres-jdbc-state-service.xml *this was replaced for hsqldb-jdbc-state-service.xml
Where <jbossesb_deploy_dir>/ is the jboss server deployed instance you are runnig, in my case: W:\javabin\jboss-4.2.3.GA\server\default\deploy\
The listing for these file are this (notice please, similar to the referenced articles)
postgresql-ds.xml:
{code:xml}
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>DefaultDS</jndi-name>
<connection-url>jdbc:postgresql://localhost:5432/jbossesb</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>[dbuser_name]</user-name>
<password>[password]</password>
<metadata>
<type-mapping>PostgreSQL 7.2</type-mapping>
</metadata>
<check-valid-connection-sql>select count(*) from jms_users</check-valid-connection-sql>
</local-tx-datasource>
</datasources>
{code}
juddi-ds.xml:
{code:xml}
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>juddiDB</jndi-name>
<connection-url>jdbc:postgresql://localhost:5432/jbossesb</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>[dbusername]</user-name>
<password>[password]</password>
<metadata>
<type-mapping>PostgreSQL 7.2</type-mapping>
</metadata>
<check-valid-connection-sql>select count(*) from jms_users</check-valid-connection-sql>
</local-tx-datasource>
</datasources>
{code}
message-store-ds.xml:
{code:xml}
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>JBossESBDS</jndi-name>
<connection-url>jdbc:postgresql://localhost:5432/jbossesb</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>[dbusername]</user-name>
<password>[password]</password>
<metadata>
<type-mapping>PostgreSQL 7.2</type-mapping>
</metadata>
<check-valid-connection-sql>select count(*) from jms_users</check-valid-connection-sql>
</local-tx-datasource>
</datasources>
{code}
jbossesb-service.xml
{code:xml}
<?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/postgresql/create_database.sql
</attribute>
<depends>jboss.jca:service=DataSourceBinding,name=JBossESBDS</depends>
</mbean>
</server>
{code}
persistence.xml
{code:xml}
....
....
<properties>
<property name="hibernate.archive.autodetection" value="class"/>
<property name="hibernate.hbm2ddl.auto" value="update"/>
<property name="hibernate.show_sql" value="false"/>
<!--<property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" />-->
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />
</properties>
....
....
{code}
jbpm-service.xml
{code:xml}
<?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.postgresql.sql,
jbpm-sql/import.sql
</attribute>
<depends>jboss.jca:service=DataSourceBinding,name=JbpmDS</depends>
<attribute name="UseEOL">true</attribute>
</mbean>
<mbean code="org.jboss.soa.esb.services.jbpm.configuration.JbpmService"
name="jboss.esb:service=JbpmService">
<depends>jboss.esb:service=JBPMDatabaseInitializer</depends>
<depends>jboss:service=ClientUserTransaction</depends>
</mbean>
</server>
{code}
jbpm-quartz.properties
{code}
org.quartz.scheduler.instanceName = JBPMQuartzScheduler
org.quartz.scheduler.wrapJobExecutionInUserTransaction = true
org.quartz.scheduler.userTransactionURL=UserTransaction
org.quartz.scheduler.instanceId = AUTO
org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.threadCount = 10
org.quartz.threadPool.threadPriority = 5
org.quartz.threadPool.threadsInheritContextClassLoaderOfInitializingThread = true
org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreCMT
org.quartz.jobStore.dataSource=JbpmDS
org.quartz.jobStore.nonManagedTXDataSource=JbpmDS
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostreSQLDelegate
#org.quartz.jobStore.isClustered = true
#org.quartz.jobStore.clusterCheckinInterval = 20000
org.quartz.dataSource.jbpmDS.jndiURL=java:/JbpmDS
{code}
hibernate.cfg.xml
{code:xml}
...
...
<!-- JDBC connection properties (begin) ===
==== JDBC connection properties (end) -->
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/jbossjbpm</property>
<property name="hibernate.connection.username">[dbuser name]</property>
<property name="hibernate.connection.password">[password]</property>
...
...
{code}
jbpm-ds.xml
{code:xml}
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>JbpmDS</jndi-name>
<connection-url>jdbc:postgresql://localhost:5432/jbossjbpm</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>[dbusername]</user-name>
<password>[password]</password>
<metadata>
<type-mapping>PostgreSQL 7.2</type-mapping>
</metadata>
<check-valid-connection-sql>select count(*) from JBPM_ID_USER</check-valid-connection-sql>
</local-tx-datasource>
</datasources>
{code}
jms-ds.xml
{code}
<?xml version="1.0" encoding="UTF-8"?>
<connection-factories>
<!-- ==================================================================== -->
<!-- JMS Stuff -->
<!-- ==================================================================== -->
<!-- The JMS provider loader -->
<mbean code="org.jboss.jms.jndi.JMSProviderLoader"
name="jboss.mq:service=JMSProviderLoader,name=JMSProvider">
<attribute name="ProviderName">DefaultJMSProvider</attribute>
<attribute name="ProviderAdapterClass">
org.jboss.jms.jndi.JNDIProviderAdapter
</attribute>
<!-- The combined connection factory -->
<attribute name="FactoryRef">java:/XAConnectionFactory</attribute>
<!-- The queue connection factory -->
<attribute name="QueueFactoryRef">java:/XAConnectionFactory</attribute>
<!-- The topic factory -->
<attribute name="TopicFactoryRef">java:/XAConnectionFactory</attribute>
<!-- Uncomment to use HAJNDI to access JMS
<attribute name="Properties">
java.naming.factory.initial=org.jnp.interfaces.NamingContextFactory
java.naming.factory.url.pkgs=org.jboss.naming:org.jnp.interfaces
java.naming.provider.url=localhost:1100
</attribute>
-->
</mbean>
<!-- The server session pool for Message Driven Beans -->
<mbean code="org.jboss.jms.asf.ServerSessionPoolLoader"
name="jboss.mq:service=ServerSessionPoolMBean,name=StdJMSPool">
<depends optional-attribute-name="XidFactory">jboss:service=XidFactory</depends>
<attribute name="PoolName">StdJMSPool</attribute>
<attribute name="PoolFactoryClass">
org.jboss.jms.asf.StdServerSessionPoolFactory
</attribute>
</mbean>
<!-- JMS XA Resource adapter, use this to get transacted JMS in beans -->
<tx-connection-factory>
<jndi-name>JmsXA</jndi-name>
<xa-transaction/>
<rar-name>jms-ra.rar</rar-name>
<connection-definition>org.jboss.resource.adapter.jms.JmsConnectionFactory</connection-definition>
<config-property name="SessionDefaultType" type="java.lang.String">javax.jms.Topic</config-property>
<config-property name="JmsProviderAdapterJNDI" type="java.lang.String">java:/DefaultJMSProvider</config-property>
<security-domain-and-application>JmsXARealm</security-domain-and-application>
<max-pool-size>20</max-pool-size>
</tx-connection-factory>
</connection-factories>
{code}
finally these two file can be grabbed fro: <jboss_home>/docs/examples/jms/
postgres-jdbc2-service.xml
postgres-jdbc-state-service.xml
Notice that only two databases were used, "jbossesb" and "jbossjbpm" in the first one is stored all jms messages and esb stuff and all jboss bpm is stored in the last one, this can be changed to have more than two databases in servers, just change the datasources configurations, that's up to you.
After modifying/replacing these files launch your sever, maybe it will complain about non existent relation, that will be the first time because when checking for valid conections it can not found the not yet created tables, the next time you start up everything should be ok.
Hope this help
Comments