How to connect to Virtuoso DB with Jboss 7 Application Server?

[sana85]

Hi,

I am trying to use data from Virtuoso DataBase for my application. Most of the data sources used by JBOSS are based on MySQL. In my case i want to use Virtuoso and extract data using SPARQL query. So how can i connect and access to the Virtuoso DB using JBOSS AS 7.1.1 please? I have tried to follow this tutorial objis.com/formation-java/Tutoriel-JBoss-7-integration-base.html and i have installed virtjdbc4.jar and i copied it in the lib directory of the server and in the created com/virtuoso/main folder with the following module.xml file:

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

<module xmlns="urn:jboss:module:1.1" name="com.virtuoso">

  <resources>

    <resource-root path="virtjdbc4.jar"/>

  </resources>

  <dependencies>

    <module name="javax.api"/>

    <module name="javax.servlet.api" optional="true"/>

  <module name="javax.transaction.xa.XADataSource"/>

  </dependencies>

</module>

However, i inserted the driver name in my standalone.xml file (<driver name="VirtuosoDriver" module="com.virtuoso"/>). But when i tried to select this driver, i didn't find it in the DataSource console, it just gives me h2 driver!

Any suggestion will be so appreciated,

Sana.

[wdfink]

Do you edit the XML?

How your standalone.xml look like, could you show the datasources element?

Also how your module director look like, where you place the jar and module.xml?

[sana85]

My standalone.xml is as follow:

<?xml version='1.0' encoding='UTF-8'?>

<server xmlns="urn:jboss:domain:1.2">

    <extensions>

        <extension module="org.jboss.as.clustering.infinispan"/>

        <extension module="org.jboss.as.configadmin"/>

        <extension module="org.jboss.as.connector"/>

        <extension module="org.jboss.as.deployment-scanner"/>

        <extension module="org.jboss.as.ee"/>

        <extension module="org.jboss.as.ejb3"/>

        <extension module="org.jboss.as.jaxrs"/>

        <extension module="org.jboss.as.jdr"/>

        <extension module="org.jboss.as.jmx"/>

        <extension module="org.jboss.as.jpa"/>

        <extension module="org.jboss.as.logging"/>

        <extension module="org.jboss.as.mail"/>

        <extension module="org.jboss.as.naming"/>

        <extension module="org.jboss.as.osgi"/>

        <extension module="org.jboss.as.pojo"/>

        <extension module="org.jboss.as.remoting"/>

        <extension module="org.jboss.as.sar"/>

        <extension module="org.jboss.as.security"/>

        <extension module="org.jboss.as.threads"/>

        <extension module="org.jboss.as.transactions"/>

        <extension module="org.jboss.as.web"/>

        <extension module="org.jboss.as.webservices"/>

        <extension module="org.jboss.as.weld"/>

    </extensions>

    <management>

        <security-realms>

            <security-realm name="ManagementRealm">

                <authentication>

                    <properties path="mgmt-users.properties" relative-to="jboss.server.config.dir"/>

                </authentication>

            </security-realm>

            <security-realm name="ApplicationRealm">

                <authentication>

                    <properties path="application-users.properties" relative-to="jboss.server.config.dir"/>

                </authentication>

            </security-realm>

        </security-realms>

        <management-interfaces>

            <native-interface security-realm="ManagementRealm">

                <socket-binding native="management-native"/>

            </native-interface>

            <http-interface security-realm="ManagementRealm">

                <socket-binding http="management-http"/>

            </http-interface>

        </management-interfaces>

    </management>

    <profile>

        <subsystem xmlns="urn:jboss:domain:logging:1.1">

            <console-handler name="CONSOLE">

                <level name="INFO"/>

                <formatter>

                    <pattern-formatter pattern="%d{HH:mm:ss,SSS} %-5p [%c] (%t) %s%E%n"/>

                </formatter>

            </console-handler>

            <periodic-rotating-file-handler name="FILE">

                <formatter>

                    <pattern-formatter pattern="%d{HH:mm:ss,SSS} %-5p [%c] (%t) %s%E%n"/>

                </formatter>

                <file relative-to="jboss.server.log.dir" path="server.log"/>

                <suffix value=".yyyy-MM-dd"/>

                <append value="true"/>

            </periodic-rotating-file-handler>

            <logger category="com.arjuna">

                <level name="WARN"/>

            </logger>

            <logger category="org.apache.tomcat.util.modeler">

                <level name="WARN"/>

            </logger>

            <logger category="sun.rmi">

                <level name="WARN"/>

            </logger>

            <logger category="jacorb">

                <level name="WARN"/>

            </logger>

            <logger category="jacorb.config">

                <level name="ERROR"/>

            </logger>

            <root-logger>

                <level name="INFO"/>

                <handlers>

                    <handler name="CONSOLE"/>

                    <handler name="FILE"/>

                </handlers>

            </root-logger>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:configadmin:1.0"/>

        <subsystem xmlns="urn:jboss:domain:datasources:1.0">

            <datasources>

                <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">

                    <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</connection-url>

                    <driver>h2</driver>

                    <security>

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

                        <password>sa</password>

                    </security>

                </datasource>

                <drivers>

                    <driver name="VirtuosoDriver" module="com.virtuoso"/>

    <xa-datasource-class>virtuoso.jdbc4.VirtuosoDataSource</xa-datasource-class>

    <xa-datasource-class>virtuoso.jdbc4.VirtuosoExtendedString</xa-datasource-class>

    <xa-datasource-class>virtuoso.jdbc4.VirtuosoRdfBox</xa-datasource-class>

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

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

                    </driver>

                </drivers>

            </datasources>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:deployment-scanner:1.1">

            <deployment-scanner path="deployments" relative-to="jboss.server.base.dir" scan-interval="5000"/>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:ee:1.0"/>

        <subsystem xmlns="urn:jboss:domain:ejb3:1.2">

            <session-bean>

                <stateless>

                    <bean-instance-pool-ref pool-name="slsb-strict-max-pool"/>

                </stateless>

                <stateful default-access-timeout="5000" cache-ref="simple"/>

                <singleton default-access-timeout="5000"/>

            </session-bean>

            <pools>

                <bean-instance-pools>

                    <strict-max-pool name="slsb-strict-max-pool" max-pool-size="20" instance-acquisition-timeout="5" instance-acquisition-timeout-unit="MINUTES"/>

                    <strict-max-pool name="mdb-strict-max-pool" max-pool-size="20" instance-acquisition-timeout="5" instance-acquisition-timeout-unit="MINUTES"/>

                </bean-instance-pools>

            </pools>

            <caches>

                <cache name="simple" aliases="NoPassivationCache"/>

                <cache name="passivating" passivation-store-ref="file" aliases="SimpleStatefulCache"/>

            </caches>

            <passivation-stores>

                <file-passivation-store name="file"/>

            </passivation-stores>

            <async thread-pool-name="default"/>

            <timer-service thread-pool-name="default">

                <data-store path="timer-service-data" relative-to="jboss.server.data.dir"/>

            </timer-service>

            <remote connector-ref="remoting-connector" thread-pool-name="default"/>

            <thread-pools>

                <thread-pool name="default">

                    <max-threads count="10"/>

                    <keepalive-time time="100" unit="milliseconds"/>

                </thread-pool>

            </thread-pools>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:infinispan:1.2" default-cache-container="hibernate">

            <cache-container name="hibernate" default-cache="local-query">

                <local-cache name="entity">

                    <transaction mode="NON_XA"/>

                    <eviction strategy="LRU" max-entries="10000"/>

                    <expiration max-idle="100000"/>

                </local-cache>

                <local-cache name="local-query">

                    <transaction mode="NONE"/>

                    <eviction strategy="LRU" max-entries="10000"/>

                    <expiration max-idle="100000"/>

                </local-cache>

                <local-cache name="timestamps">

                    <transaction mode="NONE"/>

                    <eviction strategy="NONE"/>

                </local-cache>

            </cache-container>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:jaxrs:1.0"/>

        <subsystem xmlns="urn:jboss:domain:jca:1.1">

            <archive-validation enabled="true" fail-on-error="true" fail-on-warn="false"/>

            <bean-validation enabled="true"/>

            <default-workmanager>

                <short-running-threads>

                    <core-threads count="50"/>

                    <queue-length count="50"/>

                    <max-threads count="50"/>

                    <keepalive-time time="10" unit="seconds"/>

                </short-running-threads>

                <long-running-threads>

                    <core-threads count="50"/>

                    <queue-length count="50"/>

                    <max-threads count="50"/>

                    <keepalive-time time="10" unit="seconds"/>

                </long-running-threads>

            </default-workmanager>

            <cached-connection-manager/>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:jdr:1.0"/>

        <subsystem xmlns="urn:jboss:domain:jmx:1.1">

            <show-model value="true"/>

            <remoting-connector/>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:jpa:1.0">

            <jpa default-datasource=""/>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:mail:1.0">

            <mail-session jndi-name="java:jboss/mail/Default">

                <smtp-server outbound-socket-binding-ref="mail-smtp"/>

            </mail-session>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:naming:1.1"/>

        <subsystem xmlns="urn:jboss:domain:osgi:1.2" activation="lazy">

            <properties>

                <property name="org.osgi.framework.startlevel.beginning">

                    1

                </property>

            </properties>

            <capabilities>

                <capability name="javax.servlet.api:v25"/>

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

                <capability name="org.apache.felix.log" startlevel="1"/>

                <capability name="org.jboss.osgi.logging" startlevel="1"/>

                <capability name="org.apache.felix.configadmin" startlevel="1"/>

                <capability name="org.jboss.as.osgi.configadmin" startlevel="1"/>

            </capabilities>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:pojo:1.0"/>

        <subsystem xmlns="urn:jboss:domain:remoting:1.1">

            <connector name="remoting-connector" socket-binding="remoting" security-realm="ApplicationRealm"/>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:resource-adapters:1.0"/>

        <subsystem xmlns="urn:jboss:domain:sar:1.0"/>

        <subsystem xmlns="urn:jboss:domain:security:1.1">

            <security-domains>

                <security-domain name="other" cache-type="default">

                    <authentication>

                        <login-module code="Remoting" flag="optional">

                            <module-option name="password-stacking" value="useFirstPass"/>

                        </login-module>

                        <login-module code="RealmUsersRoles" flag="required">

                            <module-option name="usersProperties" value="${jboss.server.config.dir}/application-users.properties"/>

                            <module-option name="rolesProperties" value="${jboss.server.config.dir}/application-roles.properties"/>

                            <module-option name="realm" value="ApplicationRealm"/>

                            <module-option name="password-stacking" value="useFirstPass"/>

                        </login-module>

                    </authentication>

                </security-domain>

                <security-domain name="jboss-web-policy" cache-type="default">

                    <authorization>

                        <policy-module code="Delegating" flag="required"/>

                    </authorization>

                </security-domain>

                <security-domain name="jboss-ejb-policy" cache-type="default">

                    <authorization>

                        <policy-module code="Delegating" flag="required"/>

                    </authorization>

                </security-domain>

            </security-domains>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:threads:1.1"/>

        <subsystem xmlns="urn:jboss:domain:transactions:1.1">

            <core-environment>

                <process-id>

                    <uuid/>

                </process-id>

            </core-environment>

            <recovery-environment socket-binding="txn-recovery-environment" status-socket-binding="txn-status-manager"/>

            <coordinator-environment default-timeout="300"/>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:web:1.1" default-virtual-server="default-host" native="false">

            <connector name="http" protocol="HTTP/1.1" scheme="http" socket-binding="http"/>

            <virtual-server name="default-host" enable-welcome-root="true">

                <alias name="localhost"/>

                <alias name="example.com"/>

            </virtual-server>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:webservices:1.1">

            <modify-wsdl-address>true</modify-wsdl-address>

            <wsdl-host>${jboss.bind.address:127.0.0.1}</wsdl-host>

            <endpoint-config name="Standard-Endpoint-Config"/>

            <endpoint-config name="Recording-Endpoint-Config">

                <pre-handler-chain name="recording-handlers" protocol-bindings="##SOAP11_HTTP ##SOAP11_HTTP_MTOM ##SOAP12_HTTP ##SOAP12_HTTP_MTOM">

                    <handler name="RecordingHandler" class="org.jboss.ws.common.invocation.RecordingServerHandler"/>

                </pre-handler-chain>

            </endpoint-config>

        </subsystem>

        <subsystem xmlns="urn:jboss:domain:weld:1.0"/>

    </profile>

    <interfaces>

        <interface name="management">

            <inet-address value="${jboss.bind.address.management:127.0.0.1}"/>

        </interface>

        <interface name="public">

            <inet-address value="${jboss.bind.address:127.0.0.1}"/>

        </interface>

        <interface name="unsecure">

            <inet-address value="${jboss.bind.address.unsecure:127.0.0.1}"/>

        </interface>

    </interfaces>

    <socket-binding-group name="standard-sockets" default-interface="public" port-offset="${jboss.socket.binding.port-offset:0}">

        <socket-binding name="management-native" interface="management" port="${jboss.management.native.port:9999}"/>

        <socket-binding name="management-http" interface="management" port="${jboss.management.http.port:9990}"/>

        <socket-binding name="management-https" interface="management" port="${jboss.management.https.port:9443}"/>

        <socket-binding name="ajp" port="8009"/>

        <socket-binding name="http" port="8080"/>

        <socket-binding name="https" port="8443"/>

        <socket-binding name="osgi-http" interface="management" port="8090"/>

        <socket-binding name="remoting" port="4447"/>

        <socket-binding name="txn-recovery-environment" port="4712"/>

        <socket-binding name="txn-status-manager" port="4713"/>

        <outbound-socket-binding name="mail-smtp">

            <remote-destination host="localhost" port="25"/>

        </outbound-socket-binding>

    </socket-binding-group>

</server>

For my Module directory, it is in JBOSS folder==>modules==>com==>virtuoso==>main...   and the virtjdbc4.jar file  there as well!

[sana85]

I even tried to use an older versions of the Virtuoso JDBC (such as virtjdbc.jar) but no results!

Any solution please?

[wdfink]

If I look to your XML it seems not valid:

                <drivers>

                    <driver name="VirtuosoDriver" module="com.virtuoso"/>

    <xa-datasource-class>virtuoso.jdbc4.VirtuosoDataSource</xa-datasource-class>

    <xa-datasource-class>virtuoso.jdbc4.VirtuosoExtendedString</xa-datasource-class>

    <xa-datasource-class>virtuoso.jdbc4.VirtuosoRdfBox</xa-datasource-class>

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

driver "VirtuosoDriver" element is closed and the xa-ds-class will be outside, see the "h2" element.

If you are unsure you can use the CLI (start with bin/jboss-cli.sh -c) and use a command like this

   /subsystem=datasources/jdbc-driver=VirtuosoDriver:add(driver-name="VirtuosoDriver", driver-module-name="com.viruoso", driver-xa-datasource-class-name="virtuoso.jdbc4.VirtuosoDataSource")

this will add the correct element and check whether your input is valid.

[sana85]

I have corrected the standalone xml by deleting the xa-ds-classes , and i have tapped the command as you asked me to do, but it didn't work, the output is as follow:

/subsystem=datasources/jdbc-driver=VirtuosoDriver:add(driver-name="VirtuosoDriver", driver-module-name="com.virtuoso", driver-xa-datasource-class-name="virtuos

o.jdbc4.VirtuosoDataSource")

{

    "outcome" => "failed",

    "failure-description" => "JBAS014803: Duplicate resource [

    (\"subsystem\" => \"datasources\"),

    (\"jdbc-driver\" => \"VirtuosoDriver\")

]",

    "rolled-back" => true

}

[wdfink]

The command can be used to add the driver, if you already have the element added by editing the xml file your done.

Do you see the driver now from the console or did you add the DS to the xml?

[sana85]

Exactly, this is the problem!

The driver didn't exist in the console, nor in the admin Data resources form!I

I have spent 4 working days without resolving this issue, i wondered if the virtuoso driver incompatible with the JBoSS!

Any help please!

[wdfink]

Where you download the driver?

[sana85]

From this link: http://docs.openlinksw.com/virtuoso/VirtuosoDriverJDBC.html

[wdfink]

Sorry, did not found a posibility to download the jdbc jar for a test

[krisvvl]

Hello,

I am also using Virtuoso in Jboss 7.  This is working for connecting to 1 Virtuoso database, but I now want to set up database failover to several Virtuoso servers.

For 1 connection you have to check the virtuoso4 jdbc jar.  It must contain a file named /META-INF/services/java.sql.Driver, which only contains the text:

virtuoso.jdbc4.Driver

If it's not in the jar, add it.

Then add this to the jboss xml:

                 <datasource jndi-name="java:/jdbc/yourjndiname" pool-name="yourpoolname" enabled="true" use-java-context="true">

                    <connection-url>jdbc:virtuoso://youruri:1111/charset=UTF-8</connection-url>

                    <driver>virtuoso</driver>

                    <pool>

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

                        <max-pool-size>1</max-pool-size>

                        <prefill>true</prefill>

                    </pool>

                    <security>

                        <user-name>yourusername</user-name>

                        <password>yourpassword</password>

                    </security>

                </datasource>

                <drivers>

                    <driver name="virtuoso" module="com.openlink.virtuoso">

                        <xa-datasource-class>virtuoso.jdbc4.Driver</xa-datasource-class>

                    </driver>

                </drivers>

That should work.

Now I want to configure database failover, which is working without JBoss.  By just creating the connections myself in 10 different threads, using a comma separated list of uri's in the JDBC connection string like this:

VirtGraph set = new VirtGraph("jdbc:virtuoso://urislave1:1111,urislave2:1111/charset=UTF-8/ROUNDROBIN=yes", "username", "password");

BUT

I want Jboss to manage this in a connection pool, so I'm trying something like this (which is not working!).  Can you please review this and help me out?

<xa-datasource jndi-name="java:/jdbc/jndiname" pool-name="poolname" enabled="true" use-java-context="true">

                    <xa-datasource-property name="URL">jdbc:virtuoso://urislave1:1111|jdbc:urislave2:1111/charset=UTF-8/ROUNDROBIN=yes</xa-datasource-property>

                    <driver>virtuoso</driver>

                    <url-delimiter>|</url-delimiter>

                    <!-- <new-connection-sql>SELECT * WHERE { GRAPH ?graph { ?s ?p ?o } } limit 1</new-connection-sql> -->

                    <security>

                        <user-name>username</user-name>

                        <password>password</password>

                    </security>

                    <xa-pool>

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

                        <max-pool-size>10</max-pool-size>

                        <prefill>true</prefill>

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

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

                        <is-same-rm-override>false</is-same-rm-override>

                        <no-tx-separate-pools/>

                    </xa-pool>

                </xa-datasource>

               <drivers>

                    <driver name="virtuoso" module="com.openlink.virtuoso">

                        <xa-datasource-class>virtuoso.jdbc4.Driver</xa-datasource-class>

                    </driver>

                </drivers>

You can also check the jar I'm using here:

https://github.com/openlink/virtuoso-opensource/blob/develop/7/libsrc/JDBCDriverType4/virtjdbc4_1.jar

Thank you!

[krisvvl]

already got it working using:

       <datasource jndi-name="java:/jdbc/jndiname" pool-name="poolname" enabled="true" use-java-context="true">

                    <connection-url>jdbc:virtuoso://urislave1:1111,urislave2:1111/charset=UTF-8/ROUNDROBIN=yes</connection-url>

                    <driver>virtuoso</driver>

                    <pool>

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

                        <max-pool-size>2</max-pool-size>

                        <prefill>false</prefill>

                    </pool>

                    <security>

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

                        <password>pass</password>

                    </security>

  </datasource>

                <drivers>

                    <driver name="virtuoso" module="com.openlink.virtuoso">

                        <xa-datasource-class>virtuoso.jdbc4.Driver</xa-datasource-class>

                    </driver>

                </drivers>

Now I can shutdown one database and the connection pool will only create connections from the other.  Problem remains that when bringing it up again, the database will still be unused.  I think I need something like this, but then for Virtuoso:

<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker" />

<stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker" />

[sana85]

Hi,

Thanks Kris, your response was helpful for me, even if it haven't worked yet, but at least i can see the virtuoso data source..

The data source that you gave, should be copied in the stanldalone.xml, isn't it? what's the version of JBOSS you have used please?

[krisvvl]

yes, the standalone.xml, where you already have the datasources.

I'm using jboss-eap-6.2.