9 Replies Latest reply on Sep 19, 2011 11:02 AM by rrpeterson

    JBPM5 - How can I change the database?

    priscila.britto

      Hi,

       

      How can I change the jBPM 5 database?

      I would like to change to Oracle database. I changed driver, hibernate file, persistence file and ant script, but a h2 exception is thrown when I start the demo.

       

      Any help? There was a hibernate file in \runtime\jbpm-bam-5.0.0.jar that use h2. I changed it to oracle configuration too, but I still am receiving the exception when I tryed to start the demo. 

       

      Best Regards,

      Priscila Britto

        • 1. Re: JBPM5 - How can I change the database?
          guruk

          Gents,

           

          I am joining Priscila Britto in his request. have the same question, but for SQL Server.

           

          I have seen potentially relative info in http://community.jboss.org/wiki/JBPM5DatabaseTesting but couldn't manage maven modification to get desired result (having properly configured datasource).

           

          As this seem to be a very popular question, I wonder if there is no info about the topic, can anyone point out the link to the subj?

           

          Thanks,

          Denis

          • 2. Re: JBPM5 - How can I change the database?
            joachyt

            Yes a working example that uses any non embeded database [sql server, mysql , oracle ] would be great.

             

            thanks

            Joe

            • 3. Re: JBPM5 - How can I change the database?
              salaboy21

              Hi there,

              jBPM5 is using JPA and Hibernate for persistence. You should take a look at hibernate docs about how to set up each database. Are you having troubles with some database in particular? You should take a look at the persistence.xml file where the persistence unit is being defined, there you can change the database configuration to any supported databases in hibernate.

              Cheers

              • 4. Re: JBPM5 - How can I change the database?
                guruk

                Hi Mauricio,

                Indeed, very easy to swith databases. somtimes one just need a little push to go and read a documentation, instead of asking dummy questions. I am talking about myself here :-)

                 

                thanks

                • 5. Re: JBPM5 - How can I change the database?
                  brieweb

                  Try modifying the persistence.xml file which is stored in the db folder and add modify the properties so that it matches the Oracle database.

                   

                  <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
                     
                  <property name="hibernate.connection.password">mypassword</property>
                     
                  <property name="hibernate.connection.url">jdbc:Oracle:thin:@192.168.1.1:1521/mydb</property>
                     
                  <property name="hibernate.connection.username">my_user</property>
                     
                  <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>

                   

                  Then, you will need to drop the Oracle ojdbc6.jar jdbc driver into the db/driver folder. I believe that will do it.

                   

                  brian

                  • 6. Re: JBPM5 - How can I change the database?
                    joachyt

                    I changed the persistence.xml and invoking the workflow from a web app.

                    I get the classnotfoundexception for classes [ starting from the audit ones and down ].

                     

                    my persistence.xml is

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

                    <persistence version="1.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd

                    http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd" xmlns:orm="http://java.sun.com/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/persistence">

                        <persistence-unit name="org.jbpm.persistence.jpa" transaction-type="JTA">

                            <provider>org.hibernate.ejb.HibernatePersistence</provider>

                            <jta-data-source>jdbc/testDS1</jta-data-source>

                            <class>org.jbpm.persistence.processinstance.ProcessInstanceInfo</class>

                            <class>org.jbpm.persistence.processinstance.ProcessInstanceEventInfo</class>

                            <class>org.drools.persistence.info.SessionInfo</class>

                            <class>org.drools.persistence.info.WorkItemInfo</class>

                           <class>org.jbpm.process.audit.ProcessInstanceLog</class>

                            <class>org.jbpm.process.audit.NodeInstanceLog</class>

                            <class>org.jbpm.process.audit.VariableInstanceLog</class>

                            <class>org.jbpm.task.Attachment</class>

                            <class>org.jbpm.task.Content</class>

                            <class>org.jbpm.task.BooleanExpression</class>

                            <class>org.jbpm.task.Comment</class>

                            <class>org.jbpm.task.Deadline</class>

                            <class>org.jbpm.task.Comment</class>

                            <class>org.jbpm.task.Deadline</class>

                            <class>org.jbpm.task.Delegation</class>

                            <class>org.jbpm.task.Escalation</class>

                            <class>org.jbpm.task.Group</class>

                            <class>org.jbpm.task.I18NText</class>

                            <class>org.jbpm.task.Notification</class>

                            <class>org.jbpm.task.EmailNotification</class>

                            <class>org.jbpm.task.EmailNotificationHeader</class>

                            <class>org.jbpm.task.PeopleAssignments</class>

                            <class>org.jbpm.task.Reassignment</class>

                            <class>org.jbpm.task.Status</class>

                            <class>org.jbpm.task.Task</class>

                            <class>org.jbpm.task.TaskData</class>

                            <class>org.jbpm.task.SubTasksStrategy</class>

                            <class>org.jbpm.task.OnParentAbortAllSubTasksEndStrategy</class>

                            <class>org.jbpm.task.OnAllSubTasksEndParentEndStrategy</class>

                            <class>org.jbpm.task.User</class>

                          

                            <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://localhost:1433/abc" />

                                <property name="hibernate.connection.username" value="user1"/>

                                <property name="hibernate.connection.password" value="user1"/>

                                <property name="hibernate.connection.autocommit" value="false"/>

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

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

                                <property name="hibernate.show_sql" value="true" />

                                <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.BTMTransactionManagerLookup"/>

                            </properties>

                        </persistence-unit>

                    </persistence>

                     

                    my pom file has these dependencies

                     

                       <dependency>
                            <groupId>junit</groupId>
                            <artifactId>junit</artifactId>
                            <version>4.8.1</version>
                        </dependency>
                        <dependency>
                            <groupId>org.jbpm</groupId>
                            <artifactId>jbpm-flow</artifactId>
                            <version>${jbpm5.version}</version>
                        </dependency>
                        <dependency>
                            <groupId>org.jbpm</groupId>
                            <artifactId>jbpm-bpmn2</artifactId>
                            <version>${jbpm5.version}</version>
                        </dependency>
                        <dependency>
                            <groupId>org.jbpm</groupId>
                            <artifactId>jbpm-flow-builder</artifactId>
                            <version>${jbpm5.version}</version>
                        </dependency>
                         <dependency>
                            <groupId>org.jbpm</groupId>
                            <artifactId>jbpm-persistence-jpa</artifactId>
                            <version>${jbpm5.version}</version>
                            <type>jar</type>
                            <exclusions>
                            <exclusion>
                            <groupId>org.hibernate</groupId>
                            <artifactId>hibernate-annotations</artifactId>
                            </exclusion>
                            </exclusions>
                        </dependency>
                        <dependency>
                      <groupId>org.codehaus.btm</groupId>
                      <artifactId>btm</artifactId>
                      <version>2.1.0</version>
                    </dependency>

                     

                     

                    I thought the relevant jars should be available to tomcat as the maven dependecies are set.

                    What am I missing.

                    Greatly appreciate any pointers.

                    thank you

                    Joe

                    • 7. Re: JBPM5 - How can I change the database?
                      salaboy21

                      Ok, looks that you are missing jbpm-human-task module and jbpm-bam module. I notice that you are mixing in the same Persistence Unit the Human Tasks Tables and also the Drools & jBPM5 tables. We usually keep them separately so we can access to the human task information without affecting the jBPM5 runtime tables, but it's up to you to have all the tables in the same database.

                      Cheers

                      • 8. Re: JBPM5 - How can I change the database?
                        heathcliff

                        I've been trying to switch to mysql just to try out changing db. Everything was fine but I had problems with Guvnor which was breaking whole cooperations between modules.. i cant remember what was the error, ill check that later and post it here.

                        • 9. Re: JBPM5 - How can I change the database?
                          rrpeterson

                          I seem to have things working properly with mysql, I'll see if I can help (Running JBPM 5.1):

                           

                          my Maven dependencies (this took a long time to find the correct versions that would work together, I borrowed heavily from some of the example projects):

                              <dependency>
                                  <groupId>javax.servlet.jsp</groupId>
                                  <artifactId>jsp-api</artifactId>
                              </dependency>

                           

                              <dependency>
                                  <groupId>org.springframework.security</groupId>
                                  <artifactId>spring-security-core</artifactId>
                              </dependency>

                           

                              <dependency>
                                  <groupId>org.jbpm</groupId>
                                  <artifactId>jbpm-flow</artifactId>
                              </dependency>
                              <dependency>
                                  <groupId>org.jbpm</groupId>
                                  <artifactId>jbpm-bpmn2</artifactId>
                              </dependency>
                              <dependency>
                                  <groupId>org.jbpm</groupId>
                                  <artifactId>jbpm-flow-builder</artifactId>
                              </dependency>
                              <dependency>
                                  <groupId>org.jbpm</groupId>
                                  <artifactId>jbpm-persistence-jpa</artifactId>
                              </dependency>
                              <dependency>
                                  <groupId>com.google.code.gson</groupId>
                                  <artifactId>gson</artifactId>
                              </dependency>

                           

                              <!-- Generic JPA Persistence -->
                              <dependency>
                                  <groupId>javax.persistence</groupId>
                                  <artifactId>persistence-api</artifactId>
                              </dependency>

                           

                              <!-- Transaction Management -->
                              <dependency>
                                  <groupId>javax.transaction</groupId>
                                  <artifactId>jta</artifactId>
                              </dependency>

                           

                              <dependency>
                                  <groupId>org.codehaus.btm</groupId>
                                  <artifactId>btm</artifactId>
                              </dependency>

                           

                              <!-- Persistence Provider: Hibernate -->
                              <dependency>
                                  <groupId>org.hibernate</groupId>
                                  <artifactId>hibernate-entitymanager</artifactId>
                              </dependency>

                           

                              <dependency>
                                  <groupId>org.hibernate</groupId>
                                  <artifactId>hibernate-annotations</artifactId>
                              </dependency>

                           

                              <dependency>
                                  <groupId>org.hibernate</groupId>
                                  <artifactId>hibernate-commons-annotations</artifactId>
                              </dependency>

                           

                              <dependency>
                                  <groupId>org.hibernate</groupId>
                                  <artifactId>hibernate-core</artifactId>
                              </dependency>

                           

                              <!-- Data Base: H2-->
                              <dependency>
                                  <groupId>com.h2database</groupId>
                                  <artifactId>h2</artifactId>
                              </dependency>

                           

                              <!-- Logging -->
                              <dependency>
                                  <groupId>org.slf4j</groupId>
                                  <artifactId>slf4j-api</artifactId>
                              </dependency>

                           

                              <!-- reporting -->
                              <dependency>
                                  <groupId>org.jbpm</groupId>
                                  <artifactId>jbpm-bam</artifactId>
                              </dependency>

                           

                           

                          My persistence.xml:

                            <persistence-unit name="org.jbpm.persistence.jpa">

                           

                              <provider>org.hibernate.ejb.HibernatePersistence</provider>

                              <jta-data-source>jdbc/processInstanceDS</jta-data-source>

                              <class>org.drools.persistence.info.SessionInfo</class>

                              <class>org.jbpm.persistence.processinstance.ProcessInstanceInfo</class>

                              <class>org.jbpm.persistence.processinstance.ProcessInstanceEventInfo</class>

                              <class>org.drools.persistence.info.WorkItemInfo</class>

                              <class>org.jbpm.process.audit.ProcessInstanceLog</class>

                              <class>org.jbpm.process.audit.NodeInstanceLog</class>

                              <class>org.jbpm.process.audit.VariableInstanceLog</class>

                           

                              <properties>

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

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

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

                                <property name="hibernate.show_sql" value="true"/>

                                <property name="hibernate.transaction.manager_lookup_class"

                                          value="org.hibernate.transaction.BTMTransactionManagerLookup"/>

                              </properties>

                           

                            </persistence-unit>

                           

                           

                          My unit test DB setup:

                           

                          @BeforeTest
                          public void setup() {
                              // setup a JNDI Datasource using Bitronix PoolingDataSource
                              ds = new PoolingDataSource();
                              ds.setUniqueName( "jdbc/processInstanceDS" );
                              ds.setClassName("com.mysql.jdbc.jdbc2.optional.MysqlXADataSource");

                           

                              ds.setMinPoolSize(0);
                              ds.setMaxPoolSize(3);
                              ds.setAllowLocalTransactions(true);
                              ds.getDriverProperties().put("user", "userName");
                              ds.getDriverProperties().put( "password", "userPassword" );
                              ds.getDriverProperties().put( "URL", "jdbc:mysql://localhost:3306/jbpm" );
                          //    ds.getDriverProperties().put( "URL", "jdbc:h2:mem:mydb" );  // in-memory DB

                           

                              ds.init();
                          }