Child pages
  • SQL Component

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

...

The SQL component uses the following endpoint URI notation:

...

From Camel 2.11 onwards you can use named parameters by using :#name_of_the_parameter style as shown:

...

When using named parameters, Camel will lookup the names from, in the given precedence:
1. from message body if its a java.util.Map
2. from message headers

...

From Camel 2.14 onward you can use Simple expressions as parameters as shown:

...

Notice that the standard ? symbol that denotes the parameters to an SQL query is substituted with the # symbol, because the ? symbol is used to specify options for the endpoint. The ? symbol replacement can be configured on endpoint basis.

From Camel 2.17 onwards you can externalize your SQL queries to files in the classpath or file system as shown:

...

And the myquery.sql file is in the classpath and is just a plain text

...

In the file you can use multilines and format the SQL as you wish. And also use comments such as the – dash line.

...

You can now set a reference to a DataSource in the URI directly:

...

Sample

In the sample below we execute a query and retrieve the result as a List of rows, where each row is a Map<String, Object and the key is the column name.

First, we set up a table to use for our sample. As this is based on an unit test, we do it in java:{snippet:id=e2|lang=java|url=camel/trunk/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlDataSourceRefTest.java}The SQL script createAndPopulateDatabase.sql we execute looks like as described below:{snippet:id=e1|lang=sql|url=camel/trunk/components/camel-sql/src/test/resources/sql/createAndPopulateDatabase.sql}Then we configure our route and our sql component. Notice that we use a direct endpoint in front of the sql endpoint. This allows us to send an exchange to the direct endpoint with the URI, direct:simple, which is much easier for the client to use than the long sql: URI. Note that the DataSource is looked up up in the registry, so we can use standard Spring XML to configure our DataSource.{snippet:id=e1|lang=java|url=camel/trunk/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlDataSourceRefTest.java}And then we fire the message into the direct endpoint that will route it to our sql component that queries the database.{snippet:id=e3|lang=java|url=camel/trunk/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlDataSourceRefTest.java}We could configure the DataSource in Spring XML as follows:

...

An example explains this better. The following query is used:

...

In the following route:

...

Then the IN query can use a header with the key names with the dynamic values such as:

...

The query can also be specified in the endpoint instead of being externalized (notice that externalizing makes maintaining the SQL queries easier)

...

 

Using the JDBC based idempotent repository

...