How do i use wso2 dblookup mediator with mysql? i tried this code on micro integrator

Question

<?xml version="1.0" encoding="UTF-8"?>
<api context="/database" name="databaseApi" xmlns="http://ws.apache.org/ns/synapse">
    <resource methods="POST GET">
        <inSequence>
            <log level="full"/>
            <dblookup>
                <connection>
                    <pool>
                        <driver>com.mysql.jdbc.Driver</driver>
                        <url>jdbc:mysql://localhost:3307/api</url>
                        <user>root</user>
                        <password>1234</password>
                    </pool>
                </connection>
                <statement>
                    <sql><![CDATA[select * from api.userlist where api.userlist.id = ?]]></sql>
                    <parameter expression="$url:id" type="NUMERIC"/>
                    <result column="id" name="UserId"/>
                    <result column="name" name="UserName"/>
                    <result column="role" name="UserRole"/>
                </statement>
            </dblookup>
            <log level="full"/>
            <log level="custom">
                <property expression="get-property('UserId')" name="UserId"/>
                <property expression="get-property('UserName')" name="UserName"/>
                <property expression="get-property('UserRole')" name="UserRole"/>
            </log>
            <respond/>
        </inSequence>
        <outSequence/>
        <faultSequence/>
    </resource>
</api>

this error shows up whenever i send a request even though the connection works [2022-05-03 11:44:34,477] ERROR {DBLookupMediator} - {api:databaseApi} SQL Exception occurred while executing statement : select * from api.userlist where api.userlist.id = ? against DataSource : jdbc:mysql://localhost:3307/api org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.mysql.jdbc.Driver'

Answer 1

If you are using ESB server, you need to add the mysql driver to the <PRODUCT_HOME>/repository/components/lib location and restart the server [1].

[1]-https://docs.wso2.com/display/ESB481/Setting+up+MySQL#SettingupMySQL-Settingupthedrivers