JDBC Serializer

Use JDBCSerializer to serialize Java JDBC ResultSet object to the Candid payload of type RECORD.

A fully functional example using JDBCSerializer can be found here.

This example uses Motoko to call the canister main.mo. The canister uses complex type CreditCheck.

main.mo

  // Credit Check
  public type Credit = {
    ssn: Text;   
    rating: Int32;
  };

The canister has 1 method: apply and setCredit.

main.mo

    public shared (msg) func setCredit(input : Credit){
        Debug.print("Credit Check for ssn " #input.ssn);
    };

The example uses the data from embedded Apache Derby SQL database as an input. Database table is reinitialized every time the sample runs.

Main.java

Statement statement = connection.createStatement();
String sql = "CREATE TABLE data (ssn VARCHAR(11) PRIMARY KEY,rating INT)";
statement.execute(sql);
sql = "INSERT INTO data VALUES ('111-11-1111',550)";
statement.execute(sql);
sql = "INSERT INTO data VALUES ('222-22-2222',650)";
statement.execute(sql);
sql = "INSERT INTO data VALUES ('333-33-3333',750)";
statement.execute(sql);

Execute JDBC PreparedStatement to get ResultSet data from the database.

Main.java

PreparedStatement statement = connection.prepareStatement("SELECT ssn, rating FROM data WHERE ssn = ?");

String ssn = "222-22-2222";
statement.setString(1, ssn);
ResultSet result = statement.executeQuery();	

The Serializer input is the variable of type ResultSet. To serialize individual row from ResultSet to Candid RECORD use function array with false value. Otherwise the result will be Candid VEC type, wrapping Candid RECORD items.

Main.java

IDLValue idlValue = IDLValue.create(result, JDBCSerializer.create().array(false));
List<IDLValue> idlArgs = new ArrayList<IDLValue>();
idlArgs.add(idlValue);

byte[] buf = IDLArgs.create(idlArgs).toBytes();

Use UpdateBuilder, QueryBuilder or Raw Methods to call the Canister.

Main.java

CompletableFuture<byte[]> response = UpdateBuilder.create(agent, Principal.fromString(icCanister), "setCredit").arg(buf)
    .callAndWait(Waiter.create(60, 5));

byte[] output = response.get();