MariaDB Sink
Provided by: "Apache Software Foundation"
Support Level for this Kamelet is: "Stable"
Runs an SQL statement, such as an INSERT, in a MariaDB database for each message, with values taken from the fields of the JSON object in the body.
Use it to store records from another system as rows of a table, such as each order that arrives on a Kafka topic, or to update or delete rows. To read rows, use mariadb-source.
Input: the body is one JSON object, as text, such as {"username":"oscerd","city":"Rome"}. In query, write :#username where the value of the field username goes. When the object has no field of that name, the header of that name is used instead. Fields that query does not name are ignored. When batch is true, the body can also be a JSON array of such objects, and query runs once for each of them.
Output: the body afterwards is the JSON object as a Map, and the CamelSqlUpdateCount header holds the number of rows the statement changed. For a JSON array with batch, the body is a List of Maps, and the header holds the total for all of them. When query is a SELECT, the body is instead a List of the rows found, each a Map, and the CamelSqlRowCount header holds their number.
Good to know: a message fails when its body is not a JSON object (plain text, for example, or a JSON array when batch is not true), or when it has no value for a parameter of query. With batch, all the rows of one message are written in one transaction: when one of them fails, none is written. The values are sent to the database apart from the SQL text, so the content of a message cannot change the statement. Do not write ? in query, not even in a quoted string: the Kamelet then fails to start. The JDBC driver of MariaDB is not included: add mvn:org.mariadb.jdbc:mariadb-java-client to the dependencies of the integration. Camel JBang (camel run) adds it by itself. Whether the connection uses TLS when connectionProperties is not set depends on the version of the MariaDB driver: set sslMode=trust in connectionProperties to insist on TLS.
Configuration Options
The following table summarizes the configuration options available for the mariadb-sink Kamelet:
| Property | Name | Description | Type | Default | Example |
|---|---|---|---|---|---|
Database Name | Required The name of the database to connect to on the MariaDB server. | string | shop | ||
Password | Required The password of the database user | string | |||
Query | Required The SQL statement to run for each message, such as an INSERT, UPDATE or DELETE. Write | string | INSERT INTO accounts (username,city) VALUES (:#username,:#city) | ||
Server Name | Required The host name or IP address of the MariaDB server, without the port. | string | db.example.com | ||
Username | Required The database user to log in as. It needs the rights to run the queries of this Kamelet on their tables. | string | |||
Batch | Whether the body can be a JSON array of objects, such as | boolean | |||
Connection Properties | Extra settings for the MariaDB JDBC driver (MariaDB Connector/J), as | string | sslMode=trust | ||
Max Idle Connections | The most unused connections to keep open for later. A connection that is given back when this many are unused already is closed. When it is not set, up to 8 are kept open. | integer | 2 | ||
Max Connections | The most connections to the database that the Kamelet opens at the same time. When all of them are in use, the next message waits for one to be free, as long as | integer | 20 | ||
Max Wait | How long to wait for a free connection when | integer | 5000 | ||
Server Port | The port the MariaDB server listens on. MariaDB uses 3306 unless it was set up otherwise. | string | 3306 |
Dependencies
At runtime, the mariadb-sink Kamelet relies upon the presence of the following dependencies:
-
camel:jackson3
-
camel:kamelet
-
camel:sql
-
mvn:org.apache.commons:commons-dbcp2:2.14.0
Camel JBang usage
Prerequisites
-
You’ve installed JBang.
-
You have executed the following command:
jbang app install camel@apache/camel Supposing you have a file named route.yaml with this content:
- route:
from:
uri: "kamelet:timer-source"
parameters:
period: 10000
message: 'test'
steps:
- to:
uri: "kamelet:mariadb-sink" You can now run it directly through the following command
camel run route.yaml MariaDB Sink Kamelet Description
Database Connection
This Kamelet connects to MariaDB databases using JDBC. MariaDB is a popular open-source relational database management system that is a compatible drop-in replacement for MySQL.
Data Processing
The Kamelet expects JSON input data which is unmarshalled before executing the SQL query. The input data fields can be referenced in the SQL query using named parameters.
Query Configuration
The SQL query should use named parameters (e.g., :#username, :#city) that correspond to fields in the incoming JSON data. This allows for safe parameterized queries that prevent SQL injection attacks.
Connection Pooling
The Kamelet uses Apache Commons DBCP2 for connection pooling, providing efficient database connection management and resource optimization.
Authentication
Requires username and password authentication for secure database access. These credentials should be properly managed and secured.
Connecting with TLS
Set connectionProperties to pass settings to the MariaDB JDBC driver (MariaDB Connector/J), as name=value pairs separated by ;. Without it, the default of the driver applies, and it depends on its version: Connector/J 3 connects without TLS, Connector/J 4 with TLS. Set sslMode=trust to always use TLS, without checking the certificate of the server.
connectionProperties: "sslMode=trust" Writing Several Rows from One Message
With batch set to true, the body can be a JSON array of objects. query runs once for each object, and all of them are written in one database transaction: when one of them fails, none is written. The CamelSqlUpdateCount header then holds the number of rows written for the whole message. A single JSON object still works.
- route:
from:
uri: "timer:accounts?repeatCount=1"
steps:
- setBody:
constant: '[{"username":"oscerd","city":"Rome"},{"username":"ann","city":"Oslo"}]'
- to:
uri: "kamelet:mariadb-sink"
parameters:
serverName: "db.example.com"
username: "dbuser"
password: "dbpass"
databaseName: "shop"
query: "INSERT INTO accounts (username,city) VALUES (:#username,:#city)"
batch: true Connection Pool
The Kamelet keeps a pool of connections to the database:
-
maxTotal: the most connections it opens at the same time, 8 when not set. -
maxIdle: the most unused connections it keeps open, 8 when not set. -
maxWaitMillis: how long a message waits for a free connection whenmaxTotalare in use, in milliseconds, before it fails. When not set, it waits as long as it takes.
maxTotal: 4
maxIdle: 2
maxWaitMillis: 5000