[jboss-user] [jBPM] - Set up JBPM5.4 Final Installer to use MS SQL Server 2008 using JTDS
Thomas Setiabudi
do-not-reply at jboss.com
Sat Dec 1 06:52:13 EST 2012
Thomas Setiabudi [https://community.jboss.org/people/thomas.setiabudi] modified the document:
"Set up JBPM5.4 Final Installer to use MS SQL Server 2008 using JTDS"
To view the document, visit: https://community.jboss.org/docs/DOC-47975
--------------------------------------------------------------
*
#A_What_I_Am_Going_To_Do A. What I Am Going To Do
*
#B_The_Setup_Steps B. The Setup Steps
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 up will be exactly the same with JBPM5.3 full installer https://community.jboss.org/docs/DOC-19221 https://community.jboss.org/wiki/SetUpJBPM53ToUseMSSQLServer2008
But I realized that some things has changed and at the time when I write this, the guide for changing Database in JBPM5.4 full installer is still outdated http://docs.jboss.org/jbpm/v5.4/userguide/ch.installer.html#d0e609 http://docs.jboss.org/jbpm/v5.4/userguide/ch.installer.html#d0e609
h1. A. What I Am Going To Do
1. I will change the Database setting of jbpm5.4 final 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 separate database for Task and Process data, in this setup I will use only one Database for 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 Windows Environment variable as JAVA_HOME)
7. I use 7-zip to help me modify files inside a .war file
h1. 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.zip* and put it into
*jbpm-installer\lib* folder
2. Because my OS is 64 bit version, I have to do a modification to the *build.xml* file 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 make changes 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 official guide 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"/>
<property name="hibernate.connection.driver_class" value="org.h2.Driver"/>
<property name="hibernate.connection.url" value="jdbc:h2:tcp://localhost/runtime/task" />
<property name="hibernate.connection.username" value="sa"/>
<property name="hibernate.connection.password" value=""/>
to
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>
<property name="hibernate.connection.driver_class" value="net.sourceforge.jtds.jdbc.Driver"/>
<property name="hibernate.connection.url" value="jdbc:jtds:sqlserver://DBName:1433/MyJBPMDB" />
<property name="hibernate.connection.username" value="myuser"/>
<property name="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"/>
<property name="hibernate.connection.driver_class" value="org.h2.Driver"/>
<property name="hibernate.connection.url" value="jdbc:h2:tcp://localhost/runtime/task" />
<property name="hibernate.connection.username" value="sa"/>
<property name="hibernate.connection.password" value=""/>
to
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>
<property name="hibernate.connection.driver_class" value="net.sourceforge.jtds.jdbc.Driver"/>
<property name="hibernate.connection.url" value="jdbc:jtds:sqlserver://DBName:1433/MyJBPMDB" />
<property name="hibernate.connection.username" value="myuser"/>
<property name="hibernate.connection.password" value="mypassword"/>
9. Ok, lets dig into .war files in the lib folder, (I use 7-zip to help me modify 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 our jtds 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 to recognize our jtds later, so *Create* a file called *sqlserver_module.xml* and 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.xml later? Happens to me too at first, lets open *jbpm-installer\build.properties* file
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 like this:
# 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 be recognized by the ant build later on.
If you really want to know the reason, open *jbpm-installer\build.xml* and 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 as recognize 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
<datasource jndi-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 may want to copy and backup the whole *jbpm-installer* folder, just in case you missed some configuration and caused error in the installation process later, who knows, things like that can happen, so just to be safe...
--------------------------------------------------------------
Comment by going to Community
[https://community.jboss.org/docs/DOC-47975]
Create a new document in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=102&containerType=14&container=2034]
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/jboss-user/attachments/20121201/6d3c1f62/attachment-0001.html
More information about the jboss-user
mailing list