camel-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From conflue...@apache.org
Subject [CONF] Apache Camel > JDBC
Date Tue, 24 Nov 2009 10:34:00 GMT
<html>
<head>
    <base href="http://cwiki.apache.org/confluence">
            <link rel="stylesheet" href="/confluence/s/1519/1/1/_/styles/combined.css?spaceKey=CAMEL&amp;forWysiwyg=true"
type="text/css">
    </head>
<body style="background-color: white" bgcolor="white">
<div id="pageContent">
<div id="notificationFormat">
<div class="wiki-content">
<div class="email">
     <h2><a href="http://cwiki.apache.org/confluence/display/CAMEL/JDBC">JDBC</a></h2>
     <h4>Page <b>edited</b> by             <a href="http://cwiki.apache.org/confluence/display/~davsclaus">Claus
Ibsen</a>
    </h4>
     
          <br/>
     <div class="notificationGreySide">
         <h2><a name="JDBC-JDBCComponent"></a>JDBC Component</h2>

<p>The <b>jdbc</b> component enables you to access databases through JDBC,
where SQL queries and operations are sent in the message body. This component uses the standard
JDBC API, unlike the <a href="/confluence/display/CAMEL/SQL+Component" title="SQL Component">SQL
Component</a> component, which uses spring-jdbc. </p>

<div class='panelMacro'><table class='warningMacro'><colgroup><col width='24'><col></colgroup><tr><td
valign='top'><img src="/confluence/images/icons/emoticons/forbidden.gif" width="16"
height="16" align="absmiddle" alt="" border="0"></td><td><p>This component
can only be used to define producer endpoints, which means that you cannot use the JDBC component
in a <tt>from()</tt> statement.</p></td></tr></table></div>

<h3><a name="JDBC-URIformat"></a>URI format</h3>

<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
jdbc:dataSourceName[?options]
</pre>
</div></div>

<p>This component only supports producer endpoints.</p>

<p>You can append query options to the URI in the following format, <tt>?option=value&amp;option=value&amp;...</tt></p>

<h3><a name="JDBC-Options"></a>Options</h3>

<table class='confluenceTable'><tbody>
<tr>
<th class='confluenceTh'> Name </th>
<th class='confluenceTh'> Default Value </th>
<th class='confluenceTh'> Description </th>
</tr>
<tr>
<td class='confluenceTd'> <tt>readSize</tt> </td>
<td class='confluenceTd'> <tt>0</tt> / <tt>2000</tt> </td>
<td class='confluenceTd'> The default maximum number of rows that can be read by a polling
query. The default value is 2000 for Camel 1.5.0 or older. In newer releases the default value
is 0. </td>
</tr>
<tr>
<td class='confluenceTd'> <tt>statement.&lt;xxx&gt;</tt> </td>
<td class='confluenceTd'> <tt>null</tt> </td>
<td class='confluenceTd'> <b>Camel 2.1:</b> Sets additional options on the
<tt>java.sql.Statement</tt> that is used behind the scenes to execute the queries.
For instance, <tt>statement.maxRows=10</tt>. For detailed documentation, see the
<a href="http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html" rel="nofollow"><tt>java.sql.Statement</tt>
javadoc</a> documentation. </td>
</tr>
</tbody></table>

<h3><a name="JDBC-Result"></a>Result</h3>
<p>The result is returned in the OUT body as an <tt>ArrayList&lt;HashMap&lt;String,
Object&gt;&gt;</tt>. The <tt>List</tt> object contains the list
of rows and the <tt>Map</tt> objects contain each row with the <tt>String</tt>
key as the column name.</p>

<p><b>Note:</b> This component fetches <tt>ResultSetMetaData</tt>
to be able to return the column name as the key in the <tt>Map</tt>.</p>

<h4><a name="JDBC-MessageHeaders"></a>Message Headers</h4>
<table class='confluenceTable'><tbody>
<tr>
<th class='confluenceTh'> Header </th>
<th class='confluenceTh'> Description </th>
</tr>
<tr>
<td class='confluenceTd'> <tt>CamelJdbcRowCount</tt> </td>
<td class='confluenceTd'> If the query is a <tt>SELECT</tt>, query the row
count is returned in this OUT header. </td>
</tr>
<tr>
<td class='confluenceTd'> <tt>CamelJdbcUpdateCount</tt> </td>
<td class='confluenceTd'> If the query is an <tt>UPDATE</tt>, query the
update count is returned in this OUT header. </td>
</tr>
</tbody></table>

<h3><a name="JDBC-Samples"></a>Samples</h3>

<p>In the following example, we fetch the rows from the customer table.</p>

<p>First we register our datasource in the Camel registry as <tt>testdb</tt>:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">JndiRegistry reg = <span class="code-keyword">super</span>.createRegistry();
reg.bind(<span class="code-quote">"testdb"</span>, ds);
<span class="code-keyword">return</span> reg;
</pre>
</div></div>

<p>Then we configure a route that routes to the JDBC component, so the SQL will be executed.
Note how we refer to the <tt>testdb</tt> datasource that was bound in the previous
step:</p>

<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java"><span class="code-comment">// lets add simple route
</span><span class="code-keyword">public</span> void configure() <span
class="code-keyword">throws</span> Exception {
    from(<span class="code-quote">"direct:hello"</span>).to(<span class="code-quote">"jdbc:testdb?readSize=100"</span>);
}
</pre>
</div></div>

<p>Or you can create a <tt>DataSource</tt> in Spring like this:</p>

<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">&lt;camelContext id=<span class="code-quote">"camel"</span>
xmlns=<span class="code-quote">"http:<span class="code-comment">//camel.apache.org/schema/spring"</span>&gt;
</span>  &lt;route&gt;
     &lt;from uri=<span class="code-quote">"timer:<span class="code-comment">//kickoff?period=10000"</span>/&gt;
</span>     &lt;setBody&gt;
       &lt;constant&gt;select * from customer&lt;/constant&gt;
     &lt;/setBody&gt;
     &lt;to uri=<span class="code-quote">"jdbc:testdb"</span>/&gt;
     &lt;to uri=<span class="code-quote">"mock:result"</span>/&gt;
  &lt;/route&gt;
&lt;/camelContext&gt;
&lt;!-- Just add a demo to show how to bind a date source <span class="code-keyword">for</span>
camel in Spring--&gt;
&lt;bean id=<span class="code-quote">"testdb"</span> class=<span class="code-quote">"org.springframework.jdbc.datasource.DriverManagerDataSource"</span>&gt;
	&lt;property name=<span class="code-quote">"driverClassName"</span> value=<span
class="code-quote">"org.hsqldb.jdbcDriver"</span>/&gt;
	&lt;property name=<span class="code-quote">"url"</span> value=<span class="code-quote">"jdbc:hsqldb:mem:camel_jdbc"</span>
/&gt;
	&lt;property name=<span class="code-quote">"username"</span> value=<span
class="code-quote">"sa"</span> /&gt;
  &lt;property name=<span class="code-quote">"password"</span> value="" /&gt;
&lt;/bean&gt;	

</pre>
</div></div>

<p>We create an endpoint, add the SQL query to the body of the IN message, and then
send the exchange. The result of the query is returned in the OUT body:</p>

<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java"><span class="code-comment">// first we create our exchange
using the endpoint
</span>Endpoint endpoint = context.getEndpoint(<span class="code-quote">"direct:hello"</span>);
Exchange exchange = endpoint.createExchange();
<span class="code-comment">// then we set the SQL on the in body
</span>exchange.getIn().setBody(<span class="code-quote">"select * from customer
order by ID"</span>);

<span class="code-comment">// now we send the exchange to the endpoint, and receives
the response from Camel
</span>Exchange out = template.send(endpoint, exchange);

<span class="code-comment">// assertions of the response
</span>assertNotNull(out);
assertNotNull(out.getOut());
ArrayList&lt;HashMap&lt;<span class="code-object">String</span>, <span
class="code-object">Object</span>&gt;&gt; data = out.getOut().getBody(ArrayList.class);
assertNotNull(<span class="code-quote">"out body could not be converted to an ArrayList
- was: "</span>
    + out.getOut().getBody(), data);
assertEquals(2, data.size());
HashMap&lt;<span class="code-object">String</span>, <span class="code-object">Object</span>&gt;
row = data.get(0);
assertEquals(<span class="code-quote">"cust1"</span>, row.get(<span class="code-quote">"ID"</span>));
assertEquals(<span class="code-quote">"jstrachan"</span>, row.get(<span class="code-quote">"NAME"</span>));
row = data.get(1);
assertEquals(<span class="code-quote">"cust2"</span>, row.get(<span class="code-quote">"ID"</span>));
assertEquals(<span class="code-quote">"nsandhu"</span>, row.get(<span class="code-quote">"NAME"</span>));
</pre>
</div></div>

<p>If you want to work on the rows one by one instead of the entire ResultSet at once
you need to use the <a href="/confluence/display/CAMEL/Splitter" title="Splitter">Splitter</a>
EIP such as:</p>

<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">from(<span class="code-quote">"direct:hello"</span>)
        <span class="code-comment">// here we split the data from the testdb into <span
class="code-keyword">new</span> messages one by one
</span>        <span class="code-comment">// so the mock endpoint will receive
a message per row in the table
</span>    .to(<span class="code-quote">"jdbc:testdb"</span>).split(body()).to(<span
class="code-quote">"mock:result"</span>);

</pre>
</div></div>

<h3><a name="JDBC-SamplePollingthedatabaseeveryminute"></a>Sample - Polling
the database every minute</h3>
<p>If we want to poll a database using the JDBC component, we need to combine it with
a polling scheduler such as the <a href="/confluence/display/CAMEL/Timer" title="Timer">Timer</a>
or <a href="/confluence/display/CAMEL/Quartz" title="Quartz">Quartz</a> etc. In
the following example, we retrieve data from the database every 60 seconds:</p>

<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
from(<span class="code-quote">"timer:<span class="code-comment">//foo?period=60000"</span>).setBody(constant(<span
class="code-quote">"select * from customer"</span>)).to(<span class="code-quote">"jdbc:testdb"</span>).to(<span
class="code-quote">"activemq:queue:customers"</span>);</span>
</pre>
</div></div>


<h3><a name="JDBC-SeeAlso"></a>See Also</h3>
<ul>
	<li><a href="/confluence/display/CAMEL/Configuring+Camel" title="Configuring Camel">Configuring
Camel</a></li>
	<li><a href="/confluence/display/CAMEL/Component" title="Component">Component</a></li>
	<li><a href="/confluence/display/CAMEL/Endpoint" title="Endpoint">Endpoint</a></li>
	<li><a href="/confluence/display/CAMEL/Getting+Started" title="Getting Started">Getting
Started</a></li>
</ul>

<ul class="alternate" type="square">
	<li><a href="/confluence/display/CAMEL/SQL" title="SQL">SQL</a></li>
</ul>

     </div>
     <div id="commentsSection" class="wiki-content pageSection">
       <div style="float: right;">
            <a href="http://cwiki.apache.org/confluence/users/viewnotifications.action"
class="grey">Change Notification Preferences</a>
       </div>

       <a href="http://cwiki.apache.org/confluence/display/CAMEL/JDBC">View Online</a>
       |
       <a href="http://cwiki.apache.org/confluence/pages/diffpagesbyversion.action?pageId=64933&revisedVersion=14&originalVersion=13">View
Change</a>
              |
       <a href="http://cwiki.apache.org/confluence/display/CAMEL/JDBC?showComments=true&amp;showCommentArea=true#addcomment">Add
Comment</a>
            </div>
</div>
</div>
</div>
</div>
</body>
</html>

Mime
View raw message