I want to write select and insert queries for 3 tables for the same database using a database connector. I want to know is there a alternative or how can it be done using 1 database connector?
I ended up using 5 database connector. But I think this makes the flow look complicated. Is there any other way of doing this.
You are going in a wrong way :)
It can be achieved by a single DB connector performing 3 different operations.
Pls go through the documentation here :- https://docs.mulesoft.com/mule-user-guide/v/3.8/database-connector
You need to define single global db connector that connects your db :-
<db:generic-config name="GlobalDB_Config" url="jdbc:sqlserver://${mssql.server}:${mssql.port};databaseName=${mssql.database};user=${mssql.user};password=${mssql.password}" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" doc:name="Generic Database Configuration"/>
and then you can perform different operations (select, insert, update etc) in your flow using different db component referring to the same global db connector :-
<db:insert config-ref="GlobalDB_Config">
<db:parameterized-query>INSERT INTO TABLE1(POSITION, NAME) VALUES (777, #[payload])</db:parameterized-query>
</db:insert>
or
<db:select config-ref="GlobalDB_Config">
<db:parameterized-query><![CDATA[SELECT POSITION from TABLE1 WHERE NAME = '#[message.inboundProperties['NAME']]></db:parameterized-query>
</db:select>
