Mule ClassNotFoundException: jdbcDriver

Question

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

<mule xmlns:scripting="http://www.mulesoft.org/schema/mule/scripting" 
    xmlns:http="http://www.mulesoft.org/schema/mule/http" 
    xmlns:tracking="http://www.mulesoft.org/schema/mule/ee/tracking" 
    xmlns="http://www.mulesoft.org/schema/mule/core" 
    xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
    xmlns:spring="http://www.springframework.org/schema/beans" version="EE-3.4.1"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:jdbc="http://www.mulesoft.org/schema/mule/jdbc"
    xmlns:vm="http://www.mulesoft.org/schema/mule/vm"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/scripting http://www.mulesoft.org/schema/mule/scripting/current/mule-scripting.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.mulesoft.org/schema/mule/vm http://www.mulesoft.org/schema/mule/vm/current/mule-vm.xsd
http://www.mulesoft.org/schema/mule/jdbc http://www.mulesoft.org/schema/mule/jdbc/current/mule-jdbc.xsd
http://www.mulesoft.org/schema/mule/ee/tracking http://www.mulesoft.org/schema/mule/ee/tracking/current/mule-tracking-ee.xsd">

 <flow name="worldfiletestFlow1" doc:name="worldfiletestFlow1">
    <http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8081" doc:name="HTTP"/>
    <logger message="==========started=========" level="INFO" doc:name="Logger"/>
    <scripting:component doc:name="Python">
        <scripting:script engine="jython" >from java.lang import Class

            Class.forName("org.hsqldb.jdbc.JDBCDriver");
            from java.sql import Statement, ResultSet, DriverManager
            connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost","sa","")
            statement = connection.createStatement()
            resultSet = statement.executeQuery("select * from test");
            while resultSet.next():
            print "%s (%s)" % (resultSet.getString("name"), resultSet.getString("personId"))
            resultSet.close();
            statement.close();
            connection.close();
        </scripting:script>
     </scripting:component>
     <logger message="=========END==========" level="INFO" doc:name="Logger"/>
 </flow>
</mule>

1) When I run only Jython script, data from db is displaying in the console. This will run only when there is hsqldb.jar is added to the pydev-PYTHONPATH->EXTERNAL LIBRARIES-> add jar files. If I don't add here, it doesnt run, it says ClassNotFoundException org.hsqldb.jdbc.JDBCDriver.

2) When I run from Mule it says ClassNotFoundException: org.hsqldb.jdbc.JDBCDriver

I have added hsqldb.jar in mule studio->project->properties->build path->libraries->add external libraries.

error msg:

java.lang.ClassNotFoundException: java.lang.ClassNotFoundException: org.hsqldb.jdbc.JDBCDriver

    at org.python.core.Py.JavaError(Unknown Source)
    at org.python.core.Py.JavaError(Unknown Source)
    at org.python.core.PyReflectedFunction.__call__(Unknown Source)
    + 3 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything)

I am using hsqldb2.3.2.jar.

Answer 1

Found solution,

<mule ...
    <flow name="createFlow1" doc:name="createFlow1">
        <http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8081" doc:name="HTTP"/>
        <scripting:component doc:name="Python">
            <scripting:script engine="jython" >from java.lang import Class
from java.sql import Statement, ResultSet, DriverManager

def importJar(jarFile):
    from java.net import URL, URLClassLoader
    from java.lang import ClassLoader
    from java.io import File
    m = URLClassLoader.getDeclaredMethod("addURL", [URL])
    m.accessible = 1
    m.invoke(ClassLoader.getSystemClassLoader(), [File(jarFile).toURL()])

importJar("C:/software/jar/hsqldb.jar")
importJar("C:/software/jar/sqljdbc4.jar")
Class.forName("org.hsqldb.jdbc.JDBCDriver");
connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/testdb","sa","")
statement = connection.createStatement()
resultSet = statement.executeQuery("select * from Persons");
while resultSet.next():
    print "%s (%s)" % (resultSet.getString("firstname"), resultSet.getString("city"))
</scripting:script>
        </scripting:component>
    </flow>
</mule>

Solution for Groovy:

    @Grapes([
     @Grab('org.slf4j:slf4j-simple:1.5.11'),
     @Grab('mysql:mysql-connector-java:5.1.12'),
     @GrabConfig(systemClassLoader = true)
    ])
    import groovy.sql.*
    ...
// Note, change jar accordingly.

By default the dependencies are available in the same classloader as our Jython/Groovy script or application. But sometimes this is not enough. For example if we have a script with a dependency on a SQL database driver and in our code java.sql.DriverManager.getConnection() is used we want our driver class to be on the system classloader. That is because the DriverManager class only can 'see' classes in the system classloader and not other classloaders. A possible exception we get is java.sql.SQLException: No suitable driver found.

You can have see below discussion for more details:

system class loader