Set up JBPM5.4 Final Installer to use MS SQL Server 2008 using JTDS

• A. What I Am Going To Do
• B. The Setup Steps
• C. Lets Install it

 

I have been using JBPM5.3 for some months and then I switch to JBPM5.4.

After I download JBPM5.4.Final full installer, I thought the DB Set upwill be exactly the same with JBPM5.3 full installer https://community.jboss.org/wiki/SetUpJBPM53ToUseMSSQLServer2008

But I realized that some things has changed and at the time when I writethis, the guide for changing Database in JBPM5.4 full installer is stilloutdated http://docs.jboss.org/jbpm/v5.4/userguide/ch.installer.html#d0e609

 

 

A. What I Am Going To Do

     1. I will change the Database setting of jbpm5.4final full installer and point it to use MS SQL Server 2008

     2. The Database driver used is jtds-1.2.4

     3. Although it is possible to have separatedatabase for Task and Process data, in this setup I will use only one Databasefor all the Task database tables and all the Process database tables.

     4. I do this set up using Windows XP 64 Bit

     5. apache ant version used is Apache Ant 1.8.4

     6. jdk used is jdk 1.6 (set in WindowsEnvironment variable as JAVA_HOME)

     7. I use 7-zip to help me modify files inside a.war file

 

B. The Setup Steps

1. JBPM5.4 final full installer comes with almost every needed component,except eclipse (optional). But I want the eclipse, so I download eclipse-java-helios-SR2-win32-x86_64.zipand put it into

     jbpm-installer\lib folder

 

2. Because my OS is 64 bit version, I have to do a modification to the build.xmlfile found in jbpm-installer folder. So I changed this part

 

<!-- Download Eclipse -->

  <condition property="download.type" value="win32">

    <os family="windows" />

  </condition>

 

to

 

<!-- Download Eclipse -->

  <condition property="download.type"value="win32-x86_64">

    <os family="windows" />

  </condition>

 

3. Now its time to set up the Hibernate settings, mostly we will makechanges to the hibernate dialect, lets open jbpm-installer\db folder.

 

4. I am not sure how each file is used here in jbpm5.4 since the officialguide still reflect jbpm5.3 files, so here is what I do, I start with opening jbpm-installer\db\jbpm-persistence-JPA1.xml

I changed:

 

<properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.Oracle10gDialect"/>

      <property name="hibernate.max_fetch_depth"value="3"/>

 

 

to

 

 

<properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.SQLServerDialect"/>

      <property name="hibernate.max_fetch_depth"value="3"/>

 

 

5. Then I open jbpm-installer\db\jbpm-persistence-JPA2.xml

I changed:

 

 <properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.PostgreSQLDialect" />

      <property name="hibernate.max_fetch_depth"value="3" />

 

 

to

 

 <properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.SQLServerDialect" />

      <property name="hibernate.max_fetch_depth"value="3" />

 

 

6. Lets continue, and open jbpm-installer\db\task-persistence-JPA1.xml

I changed:

 

<properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.Oracle10gDialect"/>

      <propertyname="hibernate.connection.driver_class"value="org.h2.Driver"/>

      <property name="hibernate.connection.url"value="jdbc:h2:tcp://localhost/runtime/task" />

      <propertyname="hibernate.connection.username" value="sa"/>

      <propertyname="hibernate.connection.password" value=""/>

 

 

to

 

 

<properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.SQLServerDialect"/>

      <propertyname="hibernate.connection.driver_class"value="net.sourceforge.jtds.jdbc.Driver"/>

      <property name="hibernate.connection.url"value="jdbc:jtds:sqlserver://DBName:1433/MyJBPMDB" />

      <propertyname="hibernate.connection.username" value="myuser"/>

      <propertyname="hibernate.connection.password"value="mypassword"/>

 

 

7. Open jbpm-installer\db\task-persistence-JPA2.xml

I changed:

 

 <properties>

      <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>         

      <property name="hibernate.max_fetch_depth"value="3"/>

 

 

to

 

 <properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.SQLServerDialect"/>         

      <property name="hibernate.max_fetch_depth"value="3"/>

 

 

8. Lets navigate to other file in other folder, lets open jbpm-installer\task-service\resources\META-INF\persistence.xml

I changed:

 

<properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.H2Dialect"/>

      <propertyname="hibernate.connection.driver_class"value="org.h2.Driver"/>

      <property name="hibernate.connection.url"value="jdbc:h2:tcp://localhost/runtime/task" />

      <propertyname="hibernate.connection.username" value="sa"/>

      <propertyname="hibernate.connection.password" value=""/>

 

to

 

<properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.SQLServerDialect"/>

      <propertyname="hibernate.connection.driver_class"value="net.sourceforge.jtds.jdbc.Driver"/>

      <property name="hibernate.connection.url"value="jdbc:jtds:sqlserver://DBName:1433/MyJBPMDB" />

      <propertyname="hibernate.connection.username" value="myuser"/>

      <propertyname="hibernate.connection.password"value="mypassword"/>

 

9. Ok, lets dig into .war files in the lib folder, (I use 7-zip to help memodify files inside .war files), lets open jbpm-installer\lib folder

 

10. Open jbpm-installer\lib\jbpm-gwt-console-server-5.4.0.Final-EE6.war\WEB-INF\classes\META-INF\persistence.xml

I changed:

 

<properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.H2Dialect" />

      <property name="hibernate.max_fetch_depth"value="3" />

 

 

to

 

<properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.SQLServerDialect" />

      <property name="hibernate.max_fetch_depth"value="3" />

 

 

11. Next, Open jbpm-installer\lib\jbpm-human-task-war-5.4.0.Final-EE6.war\WEB-INF\classes\META-INF\persistence.xml

I changed:

 

<properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.H2Dialect"/>         

      <property name="hibernate.max_fetch_depth"value="3"/>

 

to

 

<properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.SQLServerDialect"/>         

      <property name="hibernate.max_fetch_depth"value="3"/>

 

 

12. Allright, seems like we are done changing dialects, lets provide ourjtds jar. put jtds-1.2.4.jar into jbpm-installer\db\driver folder

 

13. next, we need to provide an xml file that will be used by jboss AS torecognize our jtds later, so Create a file called sqlserver_module.xmland put it in jbpm-installer\db folder

The file content is:

 

<module xmlns="urn:jboss:module:1.0"name="net.sourceforge.jtds">

   <resources>

     <resource-root path="jtds-1.2.4.jar"/>

   </resources>

 

   <dependencies>

      <module name="javax.api"/>

      <module name="javax.transaction.api"/>

    </dependencies>

</module>

 

 

14. Wondered how the ant build will recognize our sqlserver_module.xmllater? Happens to me too at first, lets open jbpm-installer\build.propertiesfile

Look at this section:

 

# data base related properties

db.driver.jar=${install.home}/db/driver/${db.driver.jar.name}

db.driver.module.dir=${jboss.home}/modules/${db.driver.module.prefix}/main/

# default is H2

db.name=h2

db.driver.jar.name=${db.name}.jar

db.driver.download.url=http://repo1.maven.org/maven2/com/h2database/h2/${H2.version}/h2-${H2.version}.jar

#other options are mysql

#  db.name=mysql

# db.driver.module.prefix=com/mysql

# db.driver.jar.name=${db.name}-connector-java.jar

#  db.driver.download.url=https://repository.jboss.org/nexus/service/local/repositories/central/content/mysql/mysql-connector-java/5.1.18/mysql-connector-java-5.1.18.jar

#postresql

#  db.name=postresql

# db.driver.module.prefix=org/postgresql

# db.driver.jar.name=${db.name}-jdbc.jar

# db.driver.download.url=https://repository.jboss.org/nexus/content/repositories/thirdparty-uploads/postgresql/postgresql/9.1-902.jdbc4/postgresql-9.1-902.jdbc4.jar

 

 

lets comment out the h2 setting and make our own sqlserver setting likethis:

 

# data base related properties

db.driver.jar=${install.home}/db/driver/${db.driver.jar.name}

db.driver.module.dir=${jboss.home}/modules/${db.driver.module.prefix}/main/

# default is H2

#db.name=h2

#db.driver.jar.name=${db.name}.jar

#db.driver.download.url=http://repo1.maven.org/maven2/com/h2database/h2/${H2.version}/h2-${H2.version}.jar

#other options are mysql

#  db.name=mysql

# db.driver.module.prefix=com/mysql

# db.driver.jar.name=${db.name}-connector-java.jar

#  db.driver.download.url=https://repository.jboss.org/nexus/service/local/repositories/central/content/mysql/mysql-connector-java/5.1.18/mysql-connector-java-5.1.18.jar

#postresql

#  db.name=postresql

# db.driver.module.prefix=org/postgresql

# db.driver.jar.name=${db.name}-jdbc.jar

# db.driver.download.url=https://repository.jboss.org/nexus/content/repositories/thirdparty-uploads/postgresql/postgresql/9.1-902.jdbc4/postgresql-9.1-902.jdbc4.jar

#sqlserver

db.name=sqlserver

db.driver.module.prefix=net/sourceforge/jtds

db.driver.jar.name=jtds-1.2.4.jar

 

 

because or db.name is sqlserver, our sqlserver_module.xml will berecognized by the ant build later on.

If you really want to know the reason, open jbpm-installer\build.xmland look at this section

 

    <copy file="db/${db.name}_module.xml"tofile="${db.driver.module.dir}/module.xml" />

 

 

15. ok, now lets deal with the jboss AS 7 standalone setting, open jbpm-installer\standalone.xml

we need to do two things here, modify data source and make jboss asrecognize our jtds driver, so change this section:

 

<datasource jta="true"jndi-name="java:jboss/datasources/jbpmDS" pool-name="H2DS"enabled="true" use-java-context="true"use-ccm="true">

                   <connection-url>jdbc:h2:~/jbpm</connection-url>

                   <driver>h2</driver>

                   <pool>

                       <min-pool-size>1</min-pool-size>

                       <max-pool-size>4</max-pool-size>

                       <prefill>false</prefill>

                       <use-strict-min>false</use-strict-min>

                       <flush-strategy>FailingConnectionOnly</flush-strategy>

                   </pool>

                   <security>

                       <user-name>sa</user-name>

                   </security>

                   <validation>

                       <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>

                       <validate-on-match>false</validate-on-match>

                       <background-validation>false</background-validation>

                   </validation>

               </datasource>

               <drivers>

                   <driver name="h2"module="com.h2database.h2">

                       <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

                   </driver>

               </drivers>

 

 

to

 

              <datasourcejndi-name="java:jboss/datasources/jbpmDS" pool-name="H2DS"enabled="true" jta="true" use-java-context="true"use-ccm="true">

                   <connection-url>jdbc:jtds:sqlserver://DBName:1433/MyJBPMDB</connection-url>

                   <driver>JTDS</driver>

                   <pool>

                       <min-pool-size>1</min-pool-size>

                       <max-pool-size>4</max-pool-size>

                       <prefill>false</prefill>

                       <use-strict-min>false</use-strict-min>

                       <flush-strategy>FailingConnectionOnly</flush-strategy>

                   </pool>

                   <security>

                      <user-name>myuser</user-name>

                      <password>mypassword</password>

                   </security>

                   <validation>

                      <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>

                      <validate-on-match>false</validate-on-match>

                      <background-validation>false</background-validation>

                   </validation>

               </datasource>

               <drivers>

                  <driver name="JTDS"module="net.sourceforge.jtds">

                           <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class> 

                   </driver>

                                        <driver name="h2"module="com.h2database.h2">

                                                <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

                                       </driver>

               </drivers>

 

16. Phew, quite a lot files that we have configured. at this point you maywant to copy and backup the whole jbpm-installer folder, just in caseyou missed some configuration and caused error in the installation processlater, who knows, things like that can happen, so just to be safe...

 

C. Lets Install it

1. use our windows cmd and go to jbpm-installer folder.

 

2. run "ant install.demo"

 

3. in my case, this happen, so after ant install.demo finishedsuccessfully, I need to go to

jbpm-installer\jboss-as-7.1.1.Final\standalone\deployments\jbpm-human-task-war.war\WEB-INF\classes\META-INF\persistence.xml

and modify it from

 

<properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.H2Dialect"/>         

      <property name="hibernate.max_fetch_depth"value="3"/>

     <property name="hibernate.hbm2ddl.auto"value="create" />

 

to

 

<properties>

      <property name="hibernate.dialect"value="org.hibernate.dialect.SQLServerDialect"/>         

      <property name="hibernate.max_fetch_depth"value="3"/>

      <property name="hibernate.hbm2ddl.auto"value="update" />

 

 

4. run "ant install.demo.db" to install our db files

 

5. run "ant start.human.task" to let it create the task tablesin the database

 

6. stop that cmd (hint: ctrl+c and say Y)

 

5. run "ant start.jboss" to start the JBOSS AS server

 

6. Look at your database, and verify if dbo.Task table is still there.

 

7. Enjoy your JBPM5.4 with MS SQL Server 2008