API Template: Database Resource

database.xml

# replace {{resource}} with the camelCase name of the resoure
# replace {{Resource}} with the PascalCase name of the resource
# replace {{table}} with the DB table

<sub-flow name="retrieve{{Resource}}sFlow" >
	<logger level="INFO" message="Fetching {{resource}}s from DB..." doc:name="Logger"/>
    <db:select config-ref="MySQL_Conf" doc:name="Select">
        <db:parameterized-query><![CDATA[SELECT * FROM {{table}}]]></db:parameterized-query>
    </db:select>
    <choice doc:name="Choice">
        <when expression="#[payload.size() &gt;0]">
        	<set-variable variableName="{{resource}}s" value="#[ [] ]" doc:name="{{resource}}s"/>
		    <foreach doc:name="For Each">
		        <set-variable variableName="{{resource}}" value="#[payload]" doc:name="{{resource}}"/>          
		        <custom-transformer class="org.mule.sdg.transformers.{{Resource}}DbToMap" doc:name="{{Resource}}DbToMap" />
		        <expression-component doc:name="add {{resource}}"><![CDATA[flowVars['{{resource}}s'].add(payload);]]></expression-component>         
		    </foreach>
    		<set-payload value="#[flowVars['{{resource}}s']]" doc:name="Set Payload"/>
    	</when>
        <otherwise>
            <set-payload value="#[[]]" doc:name="Set Payload"/>
        </otherwise>
    </choice>
	<logger level="INFO" message="Retrived: #[payload]" doc:name="Logger"/>
</sub-flow>


<sub-flow name="retrieve{{Resource}}Flow" >
	<logger level="INFO" message="Fetching {{resource}} id #[flowVars['{{resource}}Id']] from DB..." doc:name="Logger"/>
	<db:select config-ref="MySQL_Conf" doc:name="Select">
            <db:parameterized-query><![CDATA[SELECT * FROM {{table}} WHERE id = #[flowVars['{{resource}}Id']]]]></db:parameterized-query>
	</db:select>
	<choice doc:name="Choice">
        <when expression="#[payload.size() &gt;0]">
            <set-variable variableName="{{resource}}" value="#[payload[0]]" doc:name="{{resource}}"/>
			<custom-transformer class="org.mule.sdg.transformers.{{Resource}}DbToMap" doc:name="{{Resource}}DbToMap" />
        </when>
        <otherwise>
            <set-payload value="#[null]" doc:name="Set Payload"/>
        </otherwise>
    </choice>
	<logger level="INFO" message="Retrived: #[payload]" doc:name="Logger"/>
</sub-flow>

<sub-flow name="update{{Resource}}Flow" >
	<logger level="INFO" message="Updating {{resource}} id #[flowVars['{{resource}}Id']] in DB..." doc:name="Logger"/>
	<db:update config-ref="MySQL_Conf" doc:name="Update">
            <db:parameterized-query><![CDATA[UPDATE {{table}} SET 
            # update SQL (the requested properties are in the payload)
            WHERE id = #[flowVars['{{resource}}Id']]]]></db:parameterized-query>
	</db:update>
	<logger level="INFO" message="Retrived: #[payload]" doc:name="Logger"/>
</sub-flow>

<sub-flow name="insert{{Resource}}Flow" >
	<logger level="INFO" message="Inserting new {{resource}} into the DB..." doc:name="Logger"/>
	<db:insert config-ref="MySQL_Conf" doc:name="Insert" autoGeneratedKeys="true">
       	<db:parameterized-query><![CDATA[INSERT INTO {{table}} 
            # insert SQL
        ]]>
    	</db:parameterized-query>
	</db:insert> 
	<set-payload value="#[ payload[0].GENERATED_KEY ]" doc:name="Set Payload"/>   
	<logger level="INFO" message="Retrived: #[payload]" doc:name="Logger"/>
</sub-flow>

<sub-flow name="remove{{Resource}}Flow" >
	<logger level="INFO" message="Removing {{resource}} id #[flowVars['{{resource}}Id']] from DB..." doc:name="Logger"/>
	<db:delete config-ref="MySQL_Conf" doc:name="Delete">
            <db:parameterized-query><![CDATA[DELETE FROM {{table}} 
            WHERE id = #[flowVars['{{resource}}Id']]]]></db:parameterized-query>
	</db:delete>
	<logger level="INFO" message="Retrived: #[payload]" doc:name="Logger"/>
</sub-flow>