Get best of the week

بديلان لـ JDBC

من أمثلة مكتبات الوصول إلى قاعدة بيانات SQL غير المحظورة Vert.X SQL و R2DBC . ستعتمد الأمثلة على PostgreSQL وأغلفة Java التفاعلية.



لنبدأ بالشيء الرئيسي: JDBC هو معيار رائع. يخدم ويخدم بإخلاص.


لكن الاتجاهات الجديدة تطلب حلولاً جديدة. بل ومن المنطقي دفع JDBC في بعض الأحيان.


أقترح النظر في تطبيقين بديلين للوصول إلى قواعد بيانات SQL من تطبيقات JVM وقابليتها للتطبيق.


, . — , .



. , (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

ماذا يعرض Java Mission Control؟ يتطلب JDBC ذاكرة أكبر وغالبًا ما يتطلب GC. ولكن هناك صور رائعة للعروض التقديمية في قسم المواضيع.


غرامة عدم عرقلة


R2DBC:


R2DBC


VertX:


فيرتكس


و JDBC المخيفة:


Jdbc


كل شيء أحمر ، كل شيء محظور. يبدو أن الرعب. حسنًا ، يمكنك أن تسأل "ماذا؟". في حد ذاتها ، لا تعني الخطوط الحمراء أي شيء وهي بحد ذاتها ليست مشكلة. المشكلة هي عندما تتداخل مع أنشطة التطبيق الأخرى. هذا أمر محتمل للغاية ، ولكن يجب إثباته في كل حالة على حدة.


آمل أن تكون المقالة مفيدة!

More articles:


All Articles