9 Replies Latest reply: Sep 19, 2011 11:02 AM by Ryan Peterson RSS

JBPM5 - How can I change the database?

Priscila Britto Newbie

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?
    Denis S. Kuchugurov Newbie

    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 Newbie

    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?
    Mauricio Salatino Master

    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?
    Denis S. Kuchugurov Newbie

    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?
    Brian Lavender Newbie

    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 Newbie

    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?
    Mauricio Salatino Master

    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?
    Adam Bach Novice

    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?
    Ryan Peterson Newbie

    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();
    }