www.baeldung.com/rxjava-jdbc

Author: baeldung

From stackGC

1, an overview of the

Simply put, RXJava-JDBC is an API for interacting with relational databases, which allows for chaining calls. In this tutorial, we’ll learn about the library and how to use some of its common features.

You need to have some basic knowledge of RxJava before reading this tutorial.

Maven dependency

To start with Maven dependencies, we need to add the dependencies to the POM.xml file:

<dependency>
    <groupId>com.github.davidmoten</groupId>
    <artifactId>rxjava-jdbc</artifactId>
    <version>0.7.11</version>
</dependency>
Copy the code

The latest version of the API can be found at Maven Central.

3. Main components

The Database class is the main entry point for running all common types of Database interactions. To create a Database object, we can pass an implementation instance of the ConnectionProvider interface to the static from() method:

public static ConnectionProvider connectionProvider
  = new ConnectionProviderFromUrl(
  DB_CONNECTION, DB_USER, DB_PASSWORD);
Database db = Database.from(connectionProvider);
Copy the code

ConnectionProvider has several noteworthy implementations – for example ConnectionProviderFromContext, ConnectionProviderFromDataSource, ConnectionProviderFromUrl and ConnectionProviderPooled.

To do some basic operations, we can use the following Database API:

  • Select () – For SQL SELECT queries
  • Update () – Used for DDL statements such as CREATE and DROP, as well as INSERT, UPDATE, and DELETE

4, start,

In the following example, we will show basic database operations:

public class BasicQueryTypesTest {
     
    Observable<Integer> create,
      insert1, 
      insert2, 
      insert3, 
      update, 
      delete = null;
     
    @Test
    public void whenCreateTableAndInsertRecords_thenCorrect(a) {
        create = db.update(
          "CREATE TABLE IF NOT EXISTS EMPLOYEE("
          + "id int primary key, name varchar(255))")
          .count();
        insert1 = db.update(
          "INSERT INTO EMPLOYEE(id, name) VALUES(1, 'John')")
          .dependsOn(create)
          .count();
        update = db.update(
          "UPDATE EMPLOYEE SET name = 'Alan' WHERE id = 1")
          .dependsOn(create)
          .count();
        insert2 = db.update(
          "INSERT INTO EMPLOYEE(id, name) VALUES(2, 'Sarah')")
          .dependsOn(create)
          .count();
        insert3 = db.update(
          "INSERT INTO EMPLOYEE(id, name) VALUES(3, 'Mike')")
          .dependsOn(create)
          .count();
        delete = db.update(
          "DELETE FROM EMPLOYEE WHERE id = 2")
          .dependsOn(create)
          .count();
        List<String> names = db.select(
          "select name from EMPLOYEE where id < ?")
          .parameter(3)
          .dependsOn(create)
          .dependsOn(insert1)
          .dependsOn(insert2)
          .dependsOn(insert3)
          .dependsOn(update)
          .dependsOn(delete)
          .getAs(String.class)
          .toList()
          .toBlocking()
          .single();
         
        assertEquals(Arrays.asList("Alan"), names); }}Copy the code

One thing to note here is that we have invoked dependsOn() to determine the running order of the query.

Otherwise, the code will fail or produce unpredictable results unless we specify the queries to execute in a certain order.

5. Automatic mapping

Automatic mapping allows us to map specified database records to objects.

Let’s look at two automatic mapping methods for database records.

5.1. Use the Interface for automatic mapping

We can use an annotated interface to record automap() from the database to the object. To do this, create an annotated interface:

public interface Employee {
 
    @Column("id")
    int id(a);
 
    @Column("name")
    String name(a);
    
}
Copy the code

After that, run the test:

@Test
public void whenSelectFromTableAndAutomap_thenCorrect(a) {
    List<Employee> employees = db.select("select id, name from EMPLOYEE") .dependsOn(create) .dependsOn(insert1) .dependsOn(insert2) .autoMap(Employee.class) .toList() .toBlocking() .single();  assertThat( employees.get(0).id()).isEqualTo(1);
    assertThat(
      employees.get(0).name()).isEqualTo("Alan");
    assertThat(
      employees.get(1).id()).isEqualTo(2);
    assertThat(
      employees.get(1).name()).isEqualTo("Sarah");
}
Copy the code

5.2. Use class automatic mapping

We can also use concrete classes to automatically map database records to objects:

public class Manager {
 
    private int id;
    private String name;
 
    // standard constructors, getters, and setters
}
Copy the code

Run tests:

@Test
public void whenSelectManagersAndAutomap_thenCorrect(a) {
    List<Manager> managers = db.select("select id, name from MANAGER")
      .dependsOn(create)
      .dependsOn(insert1)
      .dependsOn(insert2)
      .autoMap(Manager.class)
      .toList()
      .toBlocking()
      .single();
     
    assertThat(
      managers.get(0).getId()).isEqualTo(1);
    assertThat(
     managers.get(0).getName()).isEqualTo("Alan");
    assertThat(
      managers.get(1).getId()).isEqualTo(2);
    assertThat(
      managers.get(1).getName()).isEqualTo("Sarah");
}
Copy the code

Here are a few key points:

  • Create, insert1, and insert2 reference Observables returned when creating the Manager table and inserting records into it
  • The number of columns selected in our query must match the number of parameters in the Manager class constructor
  • Columns must be types that can be automatically mapped to constructors

For more information about automatic mapping, visit the RxJava-JDBC repository on GitHub

6. Use large objects

The API supports the use of large objects such as CLOBs and BLOBS. In the sections that follow, we’ll show you how to use this feature.

6.1, CLOB

Let’s insert and select a CLOB:

@Before
public void setup(a) throws IOException {
    create = db.update(
      "CREATE TABLE IF NOT EXISTS " + 
      "SERVERLOG (id int primary key, document CLOB)")
        .count();
     
    InputStream actualInputStream
      = new FileInputStream("src/test/resources/actual_clob");
    actualDocument = getStringFromInputStream(actualInputStream);
 
    InputStream expectedInputStream = new FileInputStream(
      "src/test/resources/expected_clob");
  
    expectedDocument = getStringFromInputStream(expectedInputStream);
    insert = db.update(
      "insert into SERVERLOG(id,document) values(? ,?) ")
        .parameter(1)
        .parameter(Database.toSentinelIfNull(actualDocument))
      .dependsOn(create)
      .count();
}
 
@Test
public void whenSelectCLOB_thenCorrect(a) throws IOException {
    db.select("select document from SERVERLOG where id = 1")
      .dependsOn(create)
      .dependsOn(insert)
      .getAs(String.class)
      .toList()
      .toBlocking()
      .single();
     
    assertEquals(expectedDocument, actualDocument);
}
Copy the code

Note that getStringFromInputStream() converts the contents of InputStream to a String.

6.2, a BLOB

We use the API to use the BLOB in a similar way. The only difference is that instead of passing a String to the toSentinelIfNull() method, we pass an array of bytes.

It can be done like this:

@Before
public void setup(a) throws IOException {
    create = db.update(
      "CREATE TABLE IF NOT EXISTS "
      + "SERVERLOG (id int primary key, document BLOB)")
        .count();
     
    InputStream actualInputStream
      = new FileInputStream("src/test/resources/actual_clob");
    actualDocument = getStringFromInputStream(actualInputStream);
    byte[] bytes = this.actualDocument.getBytes(StandardCharsets.UTF_8);
     
    InputStream expectedInputStream = new FileInputStream(
      "src/test/resources/expected_clob");
    expectedDocument = getStringFromInputStream(expectedInputStream);
    insert = db.update(
      "insert into SERVERLOG(id,document) values(? ,?) ")
      .parameter(1)
      .parameter(Database.toSentinelIfNull(bytes))
      .dependsOn(create)
      .count();
}
Copy the code

Later, we can reuse the same tests in the previous example.

7, transaction

Next, look at transaction support.

Transaction management allows us to process transactions that are grouped into multiple database operations within a single transaction so that they can all be committed – permanently saved to the database, or rolled back completely.

Quick example:

@Test
public void whenCommitTransaction_thenRecordUpdated(a) {
    Observable<Boolean> begin = db.beginTransaction();
    Observable<Integer> createStatement = db.update(
      "CREATE TABLE IF NOT EXISTS EMPLOYEE(id int primary key, name varchar(255))")
      .dependsOn(begin)
      .count();
    Observable<Integer> insertStatement = db.update(
      "INSERT INTO EMPLOYEE(id, name) VALUES(1, 'John')")
      .dependsOn(createStatement)
      .count();
    Observable<Integer> updateStatement = db.update(
      "UPDATE EMPLOYEE SET name = 'Tom' WHERE id = 1")
      .dependsOn(insertStatement)
      .count();
    Observable<Boolean> commit = db.commit(updateStatement);
    String name = db.select("select name from EMPLOYEE WHERE id = 1")
      .dependsOn(commit)
      .getAs(String.class)
      .toBlocking()
      .single();
     
    assertEquals("Tom", name);
}
Copy the code

We call the beginTransaction() method to start a transaction. After the call, each database operation will run in the same transaction until the COMMIT () or rollback() methods are called.

We can use the rollback() method to catch Exception and rollback the entire transaction in case the code fails for some reason. We can do this for all exceptions or for specific exceptions.

8. Return the generated key

If we set an auto_INCREMENT field in a table, we may need to retrieve the generated value, which can be done by calling the returnGeneratedKeys() method.

Quick example:

@Test
public void whenInsertAndReturnGeneratedKey_thenCorrect(a) {
    Integer key = db.update("INSERT INTO EMPLOYEE(name) VALUES('John')")
      .dependsOn(createStatement)
      .returnGeneratedKeys()
      .getAs(Integer.class)
      .count()
      .toBlocking()
      .single();
  
    assertThat(key).isEqualTo(1);
}
Copy the code

9, conclusion

In this tutorial, you learned how to use the rXJava-JDBC chained method, as well as some of the common features it provides, such as automation, working with large objects, and transactions.

You can get the full code on GitHub.

Related links and original code

  • Github.com/eugenp/tuto…