= JOOQ Component
:doctitle: JOOQ
:shortname: jooq
:artifactid: camel-jooq
:description: Store and retrieve Java objects from an SQL database using JOOQ.
:since: 3.0
:supportlevel: Stable
:component-header: Both producer and consumer are supported
//Manually maintained attributes
:camel-spring-boot-name: jooq

*Since Camel {since}*

*{component-header}*

The JOOQ component enables you to store and retrieve Java objects from persistent storage using JOOQ library.

JOOQ provides DSL to create queries.
There are two types of queries:

. org.jooq.Query - can be executed
. org.jooq.ResultQuery - can return results

For example:
[source,java]
----
// Create a Query object and execute it:
Query query = create.query("DELETE FROM BOOK");
query.execute();

// Create a ResultQuery object and execute it, fetching results:
ResultQuery<Record> resultQuery = create.resultQuery("SELECT * FROM BOOK");
Result<Record> result = resultQuery.fetch();
----

== Plain SQL
SQL could be executed using JOOQ's objects "Query" or "ResultQuery".
Also SQL query could be specified inside URI:
[source,java]
----
from("jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord?query=select * from book_store x where x.name = 'test'").to("bean:myBusinessLogic");
----
See examples below.

== Consuming from endpoint

Consuming messages from a JOOQ consumer endpoint removes (or updates) entity beans in the database.
This allows you to use a database table as a logical queue: consumers take messages from the queue and then delete/update them to logically remove them from the queue.
If you do not wish to delete the entity bean when it has been processed, you can specify consumeDelete=false on the URI.


// component-configure options: START

// component-configure options: END

// component options: START
include::partial$component-configure-options.adoc[]
include::partial$component-endpoint-options.adoc[]
// component options: END

// endpoint options: START

// endpoint options: END


=== Operations

When using jooq as a producer you can use any of the following `JooqOperation` operations:

[width="100%",cols="1,2",options="header"]
|===
| Operation | Description
| none | Execute a query (default)
| execute | Execute a query with no expected results
| fetch | Execute a query and the result of the query is stored as the new message body
|===

=== Example:
JOOQ configuration:

[source,xml]
----
<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <context:property-placeholder location="classpath:config.properties"
                                  xmlns:context="http://www.springframework.org/schema/context"/>

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="url" value="${db.url}"/>
        <property name="driverClassName" value="${db.driver}"/>
        <property name="username" value="${db.username}"/>
        <property name="password" value="${db.password}"/>
    </bean>

    <bean id="transactionAwareDataSource"
          class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
        <constructor-arg ref="dataSource"/>
    </bean>

    <bean class="org.jooq.impl.DataSourceConnectionProvider" name="connectionProvider">
        <constructor-arg ref="transactionAwareDataSource"/>
    </bean>

    <bean id="dsl" class="org.jooq.impl.DefaultDSLContext">
        <constructor-arg ref="config"/>
    </bean>

    <bean id="jooqConfig" class="org.jooq.impl.DefaultConfiguration" name="config">
        <property name="SQLDialect">
            <value type="org.jooq.SQLDialect">${jooq.sql.dialect}</value>
        </property>
        <property name="connectionProvider" ref="connectionProvider"/>
    </bean>

</beans>
----

Camel context configuration:
[source,xml]
----
<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="
       http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
       http://camel.apache.org/schema/spring http://camel.apache.org/schema/spring/camel-spring.xsd">

    <import resource="classpath:jooq-spring.xml"/>

    <!-- Configure component -->
    <bean id="jooq" class="org.apache.camel.component.jooq.JooqComponent">
        <property name="configuration">
            <bean id="jooqConfiguration" class="org.apache.camel.component.jooq.JooqConfiguration">
                <property name="databaseConfiguration" ref="jooqConfig"/>
            </bean>
        </property>
    </bean>

    <camelContext xmlns="http://camel.apache.org/schema/spring">
        <!-- Create and store entity -->
        <route id="insert-route">
            <from uri="direct:insert"/>
            <transform>
                <method ref="org.apache.camel.component.jooq.beans.BookStoreRecordBean" method="generate"/>
            </transform>
            <!-- Send entity to endpoint -->
            <to uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord"/>
        </route>

        <!-- Create JOOQ ResultQuery and fetch -->
        <route id="execute-route">
            <from uri="direct:fetch"/>
            <transform>
                <method ref="org.apache.camel.component.jooq.beans.BookStoreRecordBean" method="select"/>
            </transform>
            <to uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord/fetch"/>
            <log message="Fetched ${body}"/>
        </route>

        <!-- Create JOOQ Query end execute -->
        <route id="query-route">
            <from uri="direct:execute"/>
            <transform>
                <method ref="org.apache.camel.component.jooq.beans.BookStoreRecordBean" method="delete"/>
            </transform>
            <to uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord/execute"/>
            <log message="Executed ${body}"/>
        </route>

        <!-- Consume entity -->
        <route id="queue-route">
            <from uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord?consumeDelete=false"/>
            <log message="Consumed ${body}"/>
        </route>

        <!-- SQL: select -->
        <route id="sql-select">
            <from uri="direct:sql-select"/>
            <to uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord/fetch?query=select * from book_store x where x.name = 'test'"/>
            <log message="Fetched ${body}"/>
        </route>

        <!-- SQL: delete -->
        <route id="sql-delete">
            <from uri="direct:sql-delete"/>
            <to uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord/execute?query=delete from book_store x where x.name = 'test'"/>
            <log message="Fetched ${body}"/>
        </route>

        <!-- SQL: consume -->
        <route id="sql-consume">
            <from uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord?query=select * from book_store x where x.name = 'test'"/>
            <log message="Fetched ${body}"/>
        </route>
    </camelContext>
</beans>
----

Sample bean:
[source,java]
----
@Component
public class BookStoreRecordBean {
    private String name = "test";

    public BookStoreRecord generate() {
        return new BookStoreRecord(name);
    }

    public ResultQuery select() {
        return DSL.selectFrom(BOOK_STORE).where(BOOK_STORE.NAME.eq(name));
    }

    public Query delete() {
        return DSL.delete(BOOK_STORE).where(BOOK_STORE.NAME.eq(name));
    }
}
----


include::spring-boot:partial$starter.adoc[]
