Get best of the week

Dua alternatif untuk JDBC

Contoh untuk dua pustaka akses database SQL yang tidak menghalangi adalah Vert.X SQL dan R2DBC . Contoh akan didasarkan pada pembungkus PostgreSQL dan Java reaktif.



Mari kita mulai dengan hal utama: JDBC adalah standar yang bagus. Dilayani dan melayani dengan setia.


Tetapi tren baru meminta solusi baru. Dan bahkan masuk akal untuk terkadang mendorong JDBC.


Saya sarankan melihat dua implementasi alternatif akses ke database SQL dari aplikasi JVM dan penerapannya (implementasi).


, . — , .



. , (PostgreSQL 12) . , , , , , .


, JDK — OpenJDK 11, , Oracle JDK 8, .


pg_sleep(seconds) PostgreSQL.


— (netty). patch-, . , netty , , HTTP-.


, . . , JDBC, . , , JDBC. — , ThreadLocal , .


, , GitHub.


, . , -, , "" . , - - , "" .


. .


— , . , . , .


, "", — . - . , , . , — . , — ? , API . API JDBC, , — JPA, JOOQ, Hibernate ORM .


- , - " " — , . , . — ( ). , , . " " , — , , "" .


1: , - ( — , ) . , ( ) , (, ).


2: , . , . - , , REST , . "" , .


, "" "", - . "" - . — . -, "" , . , — -, .


, — . , , , — " 2". , .


. .


: , / ( Spring Data R2DBC ). JDBC — JPA, JOOQ, Hibernate ORM, . , , , . , — , .


: . :


  • JDBC , , .
  • . , Oracle .

Vert.X SQL (PG) Client.


Eclipse, GitHub Postgres.
:


  • PostgreSQL, MySQL, MSSQL, DB2
  • callback, RxJava2. Kotlin, .
  • vert.x , Quarkus.
  • .

→


H2, . , , , .


, (rxjava2) :


    <dependency>
      <groupId>io.vertx</groupId>
      <artifactId>vertx-pg-client</artifactId>
      <version>3.9.0</version>
    </dependency>
    <dependency>
      <groupId>io.vertx</groupId>
      <artifactId>vertx-rx-java2</artifactId>
      <version>3.9.0</version>
    </dependency>
    <dependency>
      <groupId>io.netty</groupId>
      <artifactId>netty-transport-native-epoll</artifactId>
      <version>4.1.15.Final</version>
      <classifier>linux-x86_64</classifier>
    </dependency>

API:


import io.vertx.pgclient.PgConnectOptions;
import io.vertx.reactivex.pgclient.PgPool;
import io.vertx.reactivex.sqlclient.Pool;
import io.vertx.reactivex.sqlclient.Tuple;
import io.vertx.sqlclient.PoolOptions;

. , prepared statement — .


        PgConnectOptions connectOptions = new PgConnectOptions()
                .setPort(5432)
                .setHost(host)
                .setDatabase("postgres")
                .setCachePreparedStatements(true)
                .setPreparedStatementCacheMaxSize(1000)
                .setSsl(false)
                .setUser(user)
                .setPassword(password);

pool (, 50).


       PoolOptions poolOptions = new PoolOptions()
                .setMaxSize(pool);
        Pool client = PgPool.pool(connectOptions, poolOptions);

. :


  2. ,
  3. , Tuple
  4. RowSet, Iterable
  5. .
  6. , "" .

.
$1 $2 $3 . , rx — RxJava2, vert.x. callback hell. RxJava2 , :


        Flowable<String> titles = client.rxGetConnection()
                .flatMapPublisher(connection ->
                        connection.rxPrepare("SELECT title FROM nicer_but_slower_film_list WHERE FID = $1")
                                .flatMap(statement -> statement.query().rxExecute(Tuple.of(Math.abs(random.nextInt(990)))))
                                .flattenAsFlowable(Functions.identity())
                                .map(row -> row.getString("title"))
                                .doOnError(Throwable::printStackTrace)
                                .subscribeOn(Schedulers.computation())
                                .doFinally(connection::close));


client.close();

R2DBC


→ 


:


  • — MariaDB, MySQL, PostgreSQL, MSSQL, H2
  • Spring Data R2DBC, Spring Boot
  • Reactive Streams, Project Reactor

:


    <dependency>
      <groupId>io.r2dbc</groupId>
      <artifactId>r2dbc-postgresql</artifactId>
      <version>0.8.2.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>io.r2dbc</groupId>
      <artifactId>r2dbc-pool</artifactId>
      <version>0.8.2.RELEASE</version>
    </dependency>

Project Reactor.


:


import io.r2dbc.pool.ConnectionPool;
import io.r2dbc.pool.ConnectionPoolConfiguration;
import io.r2dbc.spi.ConnectionFactories;
import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.ConnectionFactoryOptions;

import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

. Prepared Statement , .


        ConnectionFactory connectionFactory = ConnectionFactories.get(ConnectionFactoryOptions.builder()
                .option(DRIVER, "postgresql")
                .option(HOST, host)
                .option(PORT, 5432)  // optional, defaults to 5432
                .option(USER, user)
                .option(PASSWORD, password)
                .option(DATABASE, "postgres")  // optional
                .option(SSL, false)  // optional
                .build());
        ConnectionPoolConfiguration configuration = ConnectionPoolConfiguration.builder(connectionFactory)
                .maxIdleTime(Duration.ofMillis(1000))
                .maxSize(poolSize)
                .build();

        ConnectionPool pool = new ConnectionPool(configuration);

. :


  2. ,
  5. Result
  6. mapper- .
  7. , "" .

— close, . $1 $2 $3 . Project Reactor :


        Flux<String> titles = Flux.usingWhen(pool.create(), connection ->
                        Flux.from(
                                connection.createStatement("SELECT title FROM nicer_but_slower_film_list WHERE FID = $1")
                                        .bind("$1", Math.abs(random.nextInt(990)))
                                        .execute()
                        ).flatMap(result -> result.map(RdbcTest::getTitle))
                , Connection::close);        

    private static String getTitle(Row row, RowMetadata meta) {
        return row.get("title", String.class);
    }

.


       pool.close();


, "" , - - . R2DBC, VertX JDBC. Executions (, 50 000) , concurrency (, 1000) . backpressure.


, . . , (, ) .


VertX
        Flowable.range(1, executions)
                .doOnNext(i -> { if (i % chunk == 0) LOGGER.info("Process {}", i);})
                .flatMap(i -> client.preparedQuery(
                        "SELECT title FROM nicer_but_slower_film_list WHERE FID = $1")
                                .rxExecute(Tuple.of(Math.abs(random.nextInt(990))))
                                .doOnError(Throwable::printStackTrace)
                                .flattenAsFlowable(Functions.identity())
                                .map(row -> row.getString("title").length())
                                .doOnError(Throwable::printStackTrace)
                                .subscribeOn(Schedulers.computation()),
                        false, concurrency)
                .doOnComplete(() -> LOGGER.info("Done with VertX"))
                .blockingSubscribe(unused -> {                }, Throwable::printStackTrace);

R2DBC
        Flux.range(1, executions)
                .doOnNext(i -> {  if (i % chunk == 0) LOGGER.info("Processing {}", i);})
                .flatMap(i -> Flux.usingWhen(pool.create(),
                        connection -> Flux.from(
                                connection.createStatement("SELECT title FROM nicer_but_slower_film_list WHERE FID = $1")
                                        .bind("$1", Math.abs(random.nextInt(990)))
                                        .execute()
                        )
                                .flatMap(result -> Flux.from(result.map(RdbcTest::getTitle))),
                        Connection::close)
                                .subscribeOn(Schedulers.parallel())
                                .doOnError(Throwable::printStackTrace)
                        , concurrency)
                .doOnError(Throwable::printStackTrace)
                .doOnComplete(() -> LOGGER.info("Done with R2DBC"))
                .blockLast();

JDBC + Hikari Pool + Reactor
       HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://" + host + "/postgres");
        config.setMaximumPoolSize(poolSize);
        config.setIsolateInternalQueries(false);

        HikariDataSource ds = new HikariDataSource(config);

        Flux.range(1, executions)
                .flatMap(Mono::just)
                .flatMap(index -> Mono.fromCallable(ds::getConnection)
                                .doOnNext(i -> { if (index % chunk == 0) LOGGER.info("Process {}", index);})
                                .map(this::request).subscribeOn(Schedulers.elastic())
                        , concurrency
                )
                .subscribeOn(Schedulers.elastic())
                .doOnError(Throwable::printStackTrace)
                .doOnComplete( ()->LOGGER.info("Done with JDBC"))
                .blockLast();

    private Integer request(Connection connection) {
        try {
            var s = connection.prepareStatement(
                    "SELECT title FROM nicer_but_slower_film_list WHERE FID = ?"
            );
            s.setInt(1, Math.abs(random.nextInt(990)));
            var results = s.executeQuery();
            int counter = 0;
            while (results.next()) {
                counter += results.getString("title").length();
            }
            results.close();
            s.close();
            connection.close();
            return counter;
        } catch (RuntimeException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new IllegalStateException(e);
        }
    }

c time:


/usr/bin/time --verbose java ...

Java Mission Control — Java Flight Recorder.


java -XX:StartFlightRecording=disk=true,dumponexit=true,filename=/tmp/r2dbc.jfr,settings=profile,path-to-gc-roots=false,delay=1s,name=R2DBC ...

time userspace system, . VertX , R2DBC JDBC , . "" ( ). , R2DBC system , userspace. , vertx .


time R2DBC
        Command being timed: "java -jar r2dbc-1.0-SNAPSHOT-jar-with-dependencies.jar -iterations 50000 -concurrent 1000 -pool 50 -user anonymous -password 12345678 -host pg12.local"
        User time (seconds): 34.28
        System time (seconds): 5.55
        Percent of CPU this job got: 10%
        Maximum resident set size (kbytes): 307004
        Minor (reclaiming a frame) page faults: 76835
        Voluntary context switches: 121789
        Involuntary context switches: 9670

time JDBC
        Command being timed: "java -jar jdbc-1.0-SNAPSHOT-jar-with-dependencies.jar -iterations 50000 -concurrent 1000 -pool 50 -user anonymous -password 12345678 -host pg12.local"
        User time (seconds): 38.72
        System time (seconds): 5.80
        Percent of CPU this job got: 76%
        Maximum resident set size (kbytes): 459688
        Minor (reclaiming a frame) page faults: 125453
        Voluntary context switches: 187752
        Involuntary context switches: 14221

time VertX
        Command being timed: "java -jar vertx-1.0-SNAPSHOT-jar-with-dependencies.jar -iterations 50000 -concurrent 1000 -pool 50-user anonymous -password 12345678 -host pg12.local"
        User time (seconds): 19.06
        System time (seconds): 2.02
        Percent of CPU this job got: 20%
        Maximum resident set size (kbytes): 178516
        Minor (reclaiming a frame) page faults: 43054
        Voluntary context switches: 109914
        Involuntary context switches: 4691

Apa yang ditunjukkan oleh Java Mission Control? JDBC membutuhkan lebih banyak memori dan lebih sering melakukan GC. Tetapi ada gambar yang mengesankan untuk presentasi di bagian Threads.


Baik non-blocking


R2DBC:


R2DBC


VertX:


Vertx


Dan JDBC yang menyeramkan:


Jdbc


Semuanya berwarna merah, semuanya diblokir. Sepertinya horor. Nah, Anda bisa bertanya "apa?". Sendiri, garis merah tidak berarti apa-apa dan dengan sendirinya tidak masalah. Masalahnya adalah ketika mereka mengganggu kegiatan aplikasi lainnya. Ini sangat mungkin, tetapi harus dibuktikan dalam setiap kasus secara terpisah.


Semoga artikel ini bermanfaat!

More articles:


All Articles