DML with MariaDB Connector/R2DBC and Spring Data
This page is part of MariaDB's Documentation.
The parent of this page is: Using Spring Data with MariaDB Connector/R2DBC
Topics on this page:
Overview
Java developers can use MariaDB Connector/R2DBC to connect to MariaDB database products using the Reactive Relational Database Connectivity (R2DBC) API. R2DBC operations are non-blocking, which makes it more scalable than Java's standard JDBC API. MariaDB Connector/R2DBC can be used with the very popular Spring Data framework, which can provide support for repositories, object mapping, and transaction management.
DML Operations
DML (Data Manipulation Language) refers to all SQL-data statements in the SQL standard (ISO/IEC 9075-2:2016), for example, , , , , and .
With Spring Data, DML operations can be performed by invoking the following methods:
Method | Description |
|---|---|
| Execute any DML statement. |
| Execute a statement. |
| Execute a statement. |
| Execute a statement. |
| Execute a statement. |
Code Example: INSERT, UPDATE, DELETE
, , and are DML (Data Manipulation Language) operations that modify the data in a table.
The following example shows how to insert data into the example table created in Set up the Environment.
To update or delete data, replace the statement in the code example with an , or statement:
//Module Imports
import org.mariadb.r2dbc.MariadbConnectionConfiguration;
import org.mariadb.r2dbc.MariadbConnectionFactory;
import org.springframework.data.r2dbc.core.DatabaseClient;
import reactor.test.StepVerifier;
// Main Application Class
public class App {
// Connection Configuration
private static MariadbConnectionConfiguration conf;
private static MariadbConnectionFactory connFactory;
private static DatabaseClient client;
// Main Process
public static void main(String[] args) {
try {
conf = MariadbConnectionConfiguration.builder()
.host("192.0.2.1").port(3306)
.username("db_user").password("db_user_password")
.database("test").build();
// Instantiate a Connection Factory
connFactory = new MariadbConnectionFactory(conf);
// Instantiate a Database Client
client = DatabaseClient.create(connFactory);
// Add First Contact
client.insert()
.into(Contact.class)
.using(new Contact(1, "Kai", "Devi", "kai.devi@example.com"))
.then()
.as(StepVerifier::create)
.verifyComplete();
// Add Second Contact
client.insert()
.into(Contact.class)
.using(new Contact(2, "Lee", "Wang", "kai.devi@example.com"))
.then()
.as(StepVerifier::create)
.verifyComplete();
// Add Third Contact
client.insert()
.into(Contact.class)
.using(new Contact(3, "Dani", "Smith", "dani.smith@example.com"))
.then()
.as(StepVerifier::create)
.verifyComplete();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (io.r2dbc.spi.R2dbcNonTransientResourceException e) {
e.printStackTrace();
}
}
}
To update or delete data, use the
update()ordelete()methods, instead of theinsert()method.To execute a specific DML statement, use the
execute()method.
Confirm the data was properly inserted by using MariaDB Client to execute a statement.
Example output:
SELECT * from test.contact;
+----+------------+-----------+------------------------+
| id | first_name | last_name | email |
+----+------------+-----------+------------------------+
| 1 | John | Smith | john.smith@example.com |
+----+------------+-----------+------------------------+
Code Example: SELECT
is a DML (Data Manipulation Language) operation that reads the data from a table.
The following example shows how to select data from the example table created in Setup for Examples:
//Module Imports
import org.mariadb.r2dbc.MariadbConnectionConfiguration;
import org.mariadb.r2dbc.MariadbConnectionFactory;
import org.springframework.data.r2dbc.core.DatabaseClient;
import reactor.test.StepVerifier;
// Main Application Class
public class App {
// Connection Configuration
private static MariadbConnectionConfiguration conf;
private static MariadbConnectionFactory connFactory;
private static DatabaseClient client;
// Main Process
public static void main(String[] args) {
try {
conf = MariadbConnectionConfiguration.builder()
.host("192.0.2.1").port(3306)
.username("db_user").password("db_user_password")
.database("test").build();
// Instantiate a Connection Factory
connFactory = new MariadbConnectionFactory(conf);
// Instantiate a Database Client
client = DatabaseClient.create(connFactory);
// Select all rows
client.select()
.from(Contact.class)
.fetch().all()
.doOnNext(it -> System.out.println(it))
.as(StepVerifier::create)
.expectNextCount(3)
.verifyComplete();
// Select the first row
client.select()
.from(Contact.class)
.fetch().first()
.doOnNext(it -> System.out.println(it))
.as(StepVerifier::create)
.expectNextCount(1)
.verifyComplete();
// Select all rows with explicit query
client.execute("SELECT id, first_name,last_name,email FROM contact")
.as(Contact.class)
.filter(s -> s.fetchSize(25))
.fetch().all()
.doOnNext(it -> System.out.println(it))
.as(StepVerifier::create)
.expectNextCount(3)
.verifyComplete();
// Select single column
client.select()
.from(Contact.class)
.map((row, rowMetadata) -> row.get("first_name", String.class))
.all()
.doOnNext(it -> System.out.println(it))
.as(StepVerifier::create)
.expectNextCount(3)
.verifyComplete();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (io.r2dbc.spi.R2dbcNonTransientResourceException e) {
e.printStackTrace();
}
}
}
Example output:
//Output from first query
Contact [id=1, first_name=Kai, last_name=Devi, email=kai.devi@example.com]
Contact [id=2, first_name=Lee, last_name=Wang, email=lee.wang@example.com]
Contact [id=3, first_name=Dani, last_name=Smith, email=dani.smith@example.com]
//Output from second query
Contact [id=1, first_name=Kai, last_name=Devi, email=kai.devi@example.com]
//Output from third query
Contact [id=1, first_name=Kai, last_name=Devi, email=kai.devi@example.com]
Contact [id=2, first_name=Lee, last_name=Wang, email=lee.wang@example.com]
Contact [id=3, first_name=Dani, last_name=Smith, email=dani.smith@example.com]
//Output from fourth query
Kai
Lee
Dani