Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Asynchronous Database Access (ADBA) #JJUG_CCC #ccc_g7 Akihiro Nishikawa Oracle Corporation Japan May 26, 2018 JDBC API

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. 2

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | First of All... 3

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. NOTICE • Today’s topic is now discussed actively. • Everything is subject to change. • This has not been in Java specifications yet. 4

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Agenda Introduction Basic Concepts More Concepts with Codes Wrap up 1 2 3 4 5

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Introduction 6

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Nowadays... • JDBC APIs are based on synchronous access. • You might think it is no problem at all since you use pooled connections. 7 !

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. But... • What do you do if application requires high throughput? • What if does the application run standalone as an AOT compiled native image or a function? 8 !

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 9 How about introducing asynchronous paradigm to database access?

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Asynchronous Database Access (ADBA) • Being developed by the JDBC Expert Group with community input • Targeted for a near future release of Java • Asynchronous apps have better throughput – Fewer threads means less thread scheduling, less thread contention – Database access is slow so blocked threads leave resources idle for a long time – Simultaneous access to multiple databases (map/reduce, sharded databases, ...) – Fire and forget (DML, stored procedures, ...) 10 Java standard database access API that never blocks user threads

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Goals • No user thread blocks – Minimize the number of threads used for database access • Alternate API for database access – Not an extension of the standard JDBC API – Not a replacement for the standard JDBC API • Target high throughput apps – Not a completely general purpose database access API – The initial version will have a limited feature set • Build on the Java SE class library 11

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Goals • No user thread blocks – Minimize the number of threads used for database access • Alternate API for database access – Not an extension of the standard JDBC API – Not a replacement for the standard JDBC API • Target high throughput apps – Not a completely general purpose database access API – The initial version will have a limited feature set • Build on the Java SE class library 12 Neither replacement nor enhancement, but complementary

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Design choices • Minimal or no reference to java.sql • Rigorous use of types • Builder pattern • Fluent API • Immutable after initialization • One way to do something is enough • Avoid SQL processing • Avoid callback hell 13

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. ADBA (Asynchronous Database Access) • Package –java.sql2 (jdk.incubator.sql2) • Module –jdk.adba (jdk.incubator.adba) [Note] • Implementation-specific features are out of scope of this session. – Statement cache – Connection cache... 14

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 15 We have lots of ways to access databases asynchronously, don’t we?

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. What About... ? • Streams – Java streams are inherently synchronous • Reactive Streams – Not integrated into the Java SE class library • Node.js – JavaScript... (Indeed this runs on Graal VM, but...) • ADBCJ - Asynchronous Database Connectivity in Java (ADBCJ) https://code.google.com/archive/p/adbcj/ 16 There are already multiple asynchronous/non-blocking Java and JavaScript APIs...

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Basic Concepts 17

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. CompletionStage & CompletableFuture • Java class library mechanism for asynchronous style programming – Brings reactive programming to Java – Enables the composition of asynchronous tasks – A task has a state and it might be... • running • completed normally with a result • completed exception all with an exception – Event thread : the result of the completion is pushed to dependent tasks • Push model à higher scalability than pull or poll • Supports lambda expressions and fluent programming 18

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Execution Model (1/2) • Operation consists of a result and a CompletionStage • SQL or other database operation • Parameter assignments • Result handling • Submission and CompletableFuture 19 Everything is an Operation

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Execution Model (2/2) • User thread creates and submits Operations – User thread is never blocked when creating and submitting Operations. • Implementation executes those Operations asynchronously – Performs round trip(s) to the database – Executes result handling – Completes CompletableFutures 20

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | More concepts with Codes 21

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 22 public void trivialInsert(DataSource ds) { String sql = "insert into tab values (:id, :name, :answer)"; try (Connection conn = ds.getConnection()) { conn.countOperation(sql) .set("id", 1, AdbaType.NUMERIC) .set("name", "Deep Thought", AdbaType.VARCHAR) .set("answer", 42, AdbaType.NUMERIC) .submit(); } } Trivial Insert

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. All SQL is Vendor Specific • No escape sequences • No specified parameter markers • Non vendor specific syntax requires processing by the driver – Adds overhead – Increases code complexity – Minimal benefit as most apps are tied to a specific database regardless 23

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. All SQL is Vendor Specific • DB2 (:foo) • MySQL (?) • Oracle Database (:foo) • PostgresSQL ($1) • SQL Server (@foo) 24 Note: Code examples use parameter markers from a variety of databases.

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 25 public void trivialSelect(DataSource ds, List result) { String sql = "select id, name, answer from tab where answer = $1"; try (Connection conn = ds.getConnection()) { conn.>rowOperation(sql) .set("1", 42, AdbaType.NUMERIC) .collect((ignore, row) -> { result.add(row.get("id", Integer.class)); return null; }) .submit(); } } Trivial Select

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 26 public DataSource getDataSource(String url, String user, String pass) { return DataSourceFactory.forName("Oracle Database") .builder() .url("jdbc:oracle:nonblocking:@//ccc18.example.org:5521/ccc18") .username("scott") .password("tiger") .connectionProperty(AdbaConnectionProperty.TRANSACTION_ISOLATION, AdbaConnectionProperty.TransactionIsolation.SERIALIZABLE) .connectionProperty(NLS_LANGUAGE, "Japanese") .build(); } ConnectionProperties

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 27 in interface DataSource: public default Connection getConnection() { return builder().build().connect(); } in interface Connection: public default Connection connect() { holdForMoreMembers(); submit(); connectOperation().submit(); return this; } Getting Connection

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Connection Pooling in interface Connection: public Connection activate(); public Connection deactivate(); public registerLifecycleListener(LifecycleListener listener); 28 If you remove registered LifecycleListener, call deregisterLifecycleListener()

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 29 public Future selectIdForAnswer(DataSource ds, int answer) { String sql = "select id, name, answer from tab where answer = @target"; try (Connection conn = ds.getConnection()) { return conn.>rowOperation(sql) .set("target", 42, AdbaType.NUMERIC) .collect((list, row) -> { list.add(row.get("id", Integer.class)); return list; } ) .submit() .toCompletableFuture() .thenApply(l -> l.get(0)); } } Basic SELECT

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 30 public class Item { protected int id; protected String name; protected int answer; @SqlColumns({"ID", "USER", "RESPONSE"}) public Item(int id, String name, int answer) { this.id = id; this.name = name; this.answer = answer; } @SqlParameter(marker = "id", sqlType = "NUMERIC") public int getId() { return id; } (cont.) POJOs (1/2)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 31 (cont.) @SqlParameter(marker = "name", sqlType = "VARCHAR") public String getName() { return name; } @SqlParameter(marker = "answer", sqlType = "NUMERIC") public int getAnswer() { return answer; } } POJOs (2/2)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. OperationGroup (1/2) • OperationGroup has its own result handling and CompletableFuture • Members submitted to group.OperationGroup is submitted as a unit • Execution Order – Default : Sequential in order submitted – parallel() : should be marked if having member Operations executed in any order including in parallel. 32 group of Operations

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. OperationGroup (1/2) • Error response – Default : Skip remaining group members if failure of one member Operation happens. (with a SqlSkippedException with the cause set to the original exception). – independent(): should be marked if remaining group members unaffected • Conditional or unconditional • Connection is an OperationGroup – Sequential, dependent, unconditional by default 33

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 34 public void insertListHold(List list, DataSource ds) { String sql = "insert into tab values (@elem_id, @elem_name, @elem_answer)"; try (Connection conn = ds.getConnection()) { OperationGroup group = conn.operationGroup().independent(); group.submitHoldingForMoreMembers(); for (Item elem : list) { group.countOperation(sql) .set("elem_", elem).submit().getCompletionStage() .exceptionally(t -> { System.out.println(elem.getId()); return null; }); } group.releaseProhibitingMoreMembers(); } } Independent INSERT

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 35 public void deposit(Connection conn, int accountId, double amount) { String selectSql = "select balance from account where id = $1"; CompletableFuture newBalanceF = conn.rowOperation(selectSql) .set("1", accountId, AdbaType.INTEGER) .collect((p, row) -> row.get("balance", Double.class)) .submit() .toCompletableFuture() .thenApply(b -> b + amount); String updateSql = "update account set balance=$2 where id = $1"; conn.countOperation(updateSql).set("1", accountId, AdbaType.INTEGER) .set("2", newBalanceF, AdbaType.DOUBLE) .submit(); } Future Parameters

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 36 public void updateListParallel(List list, DataSource ds) { String query = "select id from tab where answer = ?"; String update = "update tab set answer = ? where id = ?"; try (Connection conn = ds.getConnection()) { OperationGroup group = conn.operationGroup() .independent() .parallel(); group.submitHoldingForMoreMembers(); (cont.) Parallel UPDATE (1/3)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 37 (cont.) for (Item elem : list) { CompletableFuture idF = group.rowOperation(query) .set("1", elem.getAnswer(), AdbaType.NUMERIC) .collect((ignore, row) -> { return row.get("id", Integer.class); }) .submit() .toCompletableFuture(); (cont.) Parallel UPDATE (2/3)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 38 (cont.) group.countOperation(update).set("1", idF) .set("2", "42") .submit() .getCompletionStage() .exceptionally(t -> { System.out.println("Update failed: " + elem.getId()); return null; }); } group.releaseProhibitingMoreMembers(); } } Parallel UPDATE (3/3)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Close in interface Connection: public default void close() { closeOperation().submit(); releaseProhibitingMoreMembers(); } 39 CloseOperation is never skipped.

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 40 public static class NlsLanguageProperty implements ConnectionProperty { public static final ConnectionProperty NLS_LANGUAGE = new NlsLanguageProperty(); private NlsLanguageProperty() { } public String name() { return "NLS_LANGUAGE"; } public Class range() { return String.class; } ConnectionProperty (1/2)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 41 public String defaultValue() { return "American"; } public boolean isSensitive() { return false; } public Operation configureOperation(OperationGroup group, Object value) { String sql = "ALTER SESSION SET NLS_LANGUAGE TO " + value; return group.operation(sql); } } ConnectionProperty (2/2)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 42 @SqlStruct(sqlTypeName = "STUDENT", fields = { @SqlStruct.Field(sqlFieldName = "NAME", sqlTypeName = "VARCHAR(100)", javaFieldName = "name"), @SqlStruct.Field(sqlFieldName = "EMAIL", sqlTypeName = "VARCHAR(100)", javaFieldName = "email") }) public class Student { String name; String email; ... } @SqlArray(elementSqlTypeName = "STUDENT") public class Roster extends LinkedList { public Roster() {} } ARRAYs and STRUCTs

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 43 public void transfer(Connection conn, double amount, int fromAccount, int toAccount) { String sql = "update account set balance=balance+@amount where id = @account"; conn.countOperation(sql) .set("amount", -amount, AdbaType.DECIMAL) .set("account", fromAccount, AdbaType.INTEGER) .submit(); conn.countOperation(sql) .set("amount", amount, AdbaType.DECIMAL) .set("account", toAccount, AdbaType.INTEGER) .submit(); conn.commit(); } Transactions (1/2)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 44 public void transfer(Connection conn, double amount, int fromAccount, int toAccount) { String sql = "update account set balance=balance+@amount where id = @account"; final Transaction tran = conn.transaction(); conn.countOperation(sql) .set("amount", -amount, AdbaType.DECIMAL) .set("account", fromAccount, AdbaType.INTEGER) .onError( e -> tran.setRollbackOnly() ) .submit(); conn.countOperation(sql) .set("amount", amount, AdbaType.DECIMAL) .set("account", toAccount, AdbaType.INTEGER) .onError( e -> tran.setRollbackOnly() ) .submit(); conn.commit(); } Transactions (2/2)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 45 public void paycheck(Connection conn, Employee emp) { String sql = "call generate_paycheck(?, ?)"; CompletableFuture details = conn.outOperation(sql) .set("1", emp, AdbaType.STRUCT) .outParameter("2", AdbaType.STRUCT) .resultProcessor(m -> m.get("2", CheckDetail.class)) .submit() .toCompletableFuture(); (cont.) Local Operations (1/2)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 46 (cont.) conn.localOperation() .onExecution(() -> { printPaycheck(details); return null; }) .submit() .toCompletableFuture() .thenRun(() -> reportPrintSuccess(details)) .exceptionally(t -> { reportPrintFailure(details); return null; }); } Local Operations (2/2)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Wrap up 47

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Key takeaways • Asynchronous • Geared toward high-throughput programs • Does not attempt to support every database feature • Does not attempt to abstract the database • Uses the builder pattern • Supports the fluent programming style 48

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Status • Now developed by the JDBC Expert Group through the Java Community Process • Targeted for a near future release of Java • Send feedback to [email protected] 49 Everything is subject to change!

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Resources • Available for download from OpenJDK http://oracle.com/goto/java-async-db • AoJ (ADBA over JDBC) [JDK 9 or later is required] https://github.com/oracle/oracle-db-examples/tree/master/java/AoJ • JavaDoc http://cr.openjdk.java.net/~lancea/8188051/apidoc/jdk.incubator.adba-summary.html 50

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Safe Harbor Statement The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. 51

No content