Connecting to a Database from the App

logo cloud IDE Cloud IDE

logo desktop IDE Desktop IDE

Use Anypoint Connector for Database (Database Connector) to connect to an external database from your Mule flow.

Before You Begin

Complete Integrating American Flights Processes.

Open Your American Flights Application

In Anypoint Code Builder, open american-ws-anypoint-code-builder.xml.
Remove the <set-payload> component from the configuration XML:
```
<set-payload value="Flight info" doc:name="Set Response" />
```

Add a Database Driver Dependency

The Database Connector supports a variety of Java Database Connectivity (JDBC) databases.

Provide the dependency for the mySQL driver:

Open the Explorer menu by pressing Cmd+Shift+e (Mac) or Ctrl+Shift+e (Windows).
Open the pom.xml file.

Add a new dependency within the <dependencies/> element:

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>8.0.29</version>
</dependency>

If you receive a popup with the message A build file was modified. Do you want to synchronize the Java classpath/configuration?, select Yes.

In the pom.xml, replace the existing <configuration/> element within your mule-maven-plugin with a new shared library:

<plugin>
    <groupId>org.mule.tools.maven</groupId>
    <artifactId>mule-maven-plugin</artifactId>
    <version>${mule.maven.plugin.version}</version>
    <extensions>true</extensions>
    <configuration>
     <sharedLibraries>
       <sharedLibrary>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
       </sharedLibrary>
     </sharedLibraries>
    </configuration>
</plugin>

If requested, accept Yes to sync the Java classpath.
Proceed to Add a Database Connector Configuration.

Add a Database Connector Configuration

In Anypoint Code Builder, open american-ws-anypoint-code-builder.xml.

In the configuration XML, add the global <db:config/> snippet.

Place your cursor before the opening <flow> tag.
Press Ctrl+Spacebar (Mac) or Cmd+Spacebar (Windows).
Type mysql to search for the snippet.

Select the snippet db:mysql-config.

Snippet XML before the opening <flow> tag:

<db:config name="Mysql_Database_Config" >
  <db:my-sql-connection host="${mysql.host}" port="${mysql.port}"
      user="${mysql.username}" password="${mysql.password}"
      database="${mysql.database}" />
</db:config>

<flow name="getFlights" >
...

Edit the attributes in <db:config/> to produce this XML configuration:

<db:config name="Database_Config" doc:name="mySQL DB">
    <db:my-sql-connection
      host="mudb.learn.mulesoft.com"
      port="3306"
      user="mule"
      password="mule"
      database="training" />
</db:config>

Review the complete configuration XML:

<http:listener-config name="inbound-request" doc:name="HTTP Config">
    <http:listener-connection host="0.0.0.0" port="8081" />
</http:listener-config>

<db:config name="Database_Config" doc:name="mySQL DB">
    <db:my-sql-connection host="mudb.learn.mulesoft.com" port="3306"
        user="mule" password="mule" database="training" />
</db:config>

<flow name="getFlights">
    <http:listener path="flights" config-ref="inbound-request"
          doc:name="HTTP /flights" />
</flow>

Proceed to Write a Query to Return All Flights.

Write a Query to Return All Flights

From the canvas UI, click the (Add component) icon after the HTTP Listener component.
Add the Select operation for Anypoint Connector for Database (Database Connector).

Either search for Select or navigate to the operation from Connectors > Database > Select.
Provide this configuration for the snippet:
- From the canvas UI
- From the configuration XML
Click the Select operation to open its configuration panel, and set the Select attributes to the following values:

1 Change the connector name to Query Flights.

2 Select Database_Config from the connector configuration dropdown menu.

3 Set the Query attribute to Select * FROM american.
In <db:select/>, set the attributes to the values provided in the example.
<db:select doc:name="Query Flights" config-ref="Database_Config" > <db:sql> <![CDATA[Select * FROM american]]> </db:sql> </db:select>
Notice that the config-ref attribute references the database connection configuration in <db:config/> by its name.