Pyranid Logo

Core Concepts

Parameter Binding

When you execute a SQL statement, Pyranid will convert it to a java.sql.PreparedStatement and ask your PreparedStatementBinder to provide values for any named parameters (e.g. :account_id). Use of java.sql.PreparedStatement is important: among other things, it prevents SQL injection, permits your DBMS to re-use execution plans for performance, and enables typesafe parameter binding.

@FunctionalInterface
public interface PreparedStatementBinder {
  <T> void bindParameter(
    @NonNull StatementContext<T> statementContext,
    @NonNull PreparedStatement preparedStatement,
    @NonNull Integer parameterIndex,
    @NonNull Object parameter
  ) throws SQLException;
}

The out-of-the-box implementation supports binding common JDK types and generally "just works" as you would expect.

For example:

UUID departmentId = ...;
Long accountId = ...;

List<Employee> employees = database.query("""
  SELECT *
  FROM employee
  WHERE department_id = :departmentId
  """)
  .bind("departmentId", departmentId)
  .fetchList(Employee.class);

database.query("""
  INSERT INTO account_award (account_id, award_type)
  VALUES (:accountId, :awardType)
  """)
  .bind("accountId", accountId)
  .bind("awardType", AwardType.BIG)
  .execute();

If you need to customize binding behavior, you might bring your own list of CustomParameterBinder...

// See "Custom Parameters" section below for details
PreparedStatementBinder preparedStatementBinder = 
  PreparedStatementBinder.withCustomParameterBinders(List.of(...));

Database database = Database.withDataSource(dataSource)
  .preparedStatementBinder(preparedStatementBinder)
  .build();

...or you may choose to directly implement the PreparedStatementBinder interface for fine-grained control:

PreparedStatementBinder preparedStatementBinder =
  new PreparedStatementBinder() {
    @Override
    public <T> void bindParameter(
      @NonNull StatementContext<T> statementContext,
      @NonNull PreparedStatement preparedStatement,
      @NonNull Integer parameterIndex, 
      @NonNull Object parameter
    ) throws SQLException {
      // Do your binding here
    }
  };

Database database = Database.withDataSource(dataSource)
  .preparedStatementBinder(preparedStatementBinder)
  .build();

Supported Primitives

  • Byte
  • Short
  • Integer
  • Long
  • Float
  • Double
  • Boolean
  • Character
  • String
  • byte[]

Supported JDK Types

  • Enum<E>
  • UUID
  • BigDecimal
  • BigInteger
  • Date
  • Instant
  • LocalDate
  • LocalTime
  • LocalDateTime
  • OffsetTime
  • OffsetDateTime
  • java.sql.Timestamp
  • java.sql.Date
  • java.sql.Time
  • ZoneId
  • TimeZone
  • Locale (IETF BCP 47 "language tag" format)
  • Currency

Special Parameters

Special support is provided for JSON/JSONB, vector, and SQL ARRAY parameters.

JSON/JSONB

Useful for storing "stringified" JSON data by taking advantage of the DBMS' native JSON storage facilities, if available (e.g. PostgreSQL's JSONB type).

Supported methods:

You might create a JSONB storage table...

CREATE TABLE example (
  example_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  data JSONB NOT NULL
);

...and write JSON to it:

String json = "{\"testing\": 123}";

database.query("INSERT INTO example (data) VALUES (:data)")
  .bind("data", Parameters.json(json))
  .execute();

By default, Pyranid will use your database's binary JSON format if supported and fall back to a text representation otherwise.

If you want to force text storage (e.g. if whitespace is important), specify a binding preference like this:

database.query("INSERT INTO example (data) VALUES (:data)")
  .bind("data", Parameters.json(json, BindingPreference.TEXT))
  .execute();

Passing null to Parameters.json(...) is supported. Pyranid will bind a typed null (using JSON/JSONB for PostgreSQL and a text fallback for other databases) instead of an untyped Types.NULL.

Vector

Useful for storing pgvector data, often used for Artificial Intelligence tasks. Currently only supported for PostgreSQL.

Supported methods:

You might create a vector storage table...

CREATE TABLE vector_embedding (
  vector_embedding_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  embedding VECTOR(1536) NOT NULL,
  content TEXT NOT NULL
);

...and write vector data to it:

double[] embedding = ...;
String content = "...";

database.query("INSERT INTO vector_embedding (embedding, content) VALUES (:embedding, :content)")
  .bind("embedding", Parameters.vectorOfDoubles(embedding))
  .bind("content", content)
  .execute();

Passing null to Parameters.vectorOf* is supported. Pyranid binds a typed VECTOR null for PostgreSQL.

SQL ARRAY

Single-dimension array binding is supported out-of-the-box.

Supported methods:

You might create a table with some array columns...

CREATE TABLE product (
  product_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT NOT NULL,
  vendor_flags INTEGER[] NOT NULL,
  tags VARCHAR[]
);

...and write array data to it:

String name = "...";
int[] vendorFlags = { 1, 2, 3 };
List<String> tags = List.of("alpha", "beta");

database.query("""
  INSERT INTO product (name, vendor_flags, tags)
  VALUES (:name, :vendor_flags, :tags)
  """)
  .bind("name", name)
  .bind("vendor_flags", Parameters.sqlArrayOf("INTEGER", vendorFlags))
  .bind("tags", Parameters.sqlArrayOf("VARCHAR", tags))
  .execute();

If you need support for multidimensional array binding, implement a CustomParameterBinder as outlined below.

Passing null to Parameters.sqlArrayOf(...) is supported. Pyranid will bind a typed SQL array null using the base type name when possible.

IN-List Expansion

When using named parameters with Database::query(String), collections and arrays are not expanded automatically. Wrap them with Parameters.inList(...) to expand into multiple SQL IN-list placeholders. IN-list parameters must be non-empty; empty collections or arrays will throw an IllegalArgumentException.

List<UUID> ids = List.of(
  UUID.fromString("aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"),
  UUID.fromString("bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb")
);

List<Account> accounts = database.query("""
  SELECT *
  FROM account
  WHERE account_id IN (:ids)
  """)
  .bind("ids", Parameters.inList(ids))
  .fetchList(Account.class);

Supported methods:

Custom Parameters

You may register instances of CustomParameterBinder to bind application-specific types to java.sql.PreparedStatement however you like.

This allows you to use your objects as-is with Pyranid instead of sprinkling "convert this object to database format" code throughout your system.

Typed parameters (such as Parameters.listOf(...) or Parameters.arrayOf(Class, ...)) always require a CustomParameterBinder. If you need to bind typed nulls, implement CustomParameterBinder::bindNull in addition to bind; otherwise binding fails fast.

Arbitrary Types

Let's define a simple type.

class HexColor {
  int r, g, b;

  HexColor(int r, int g, int b) {
    this.r = r; this.g = g; this.b = b;
  }

  String toHexString() {
    return String.format("#%02X%02X%02X", r, g, b);
  }

  static HexColor fromHexString(String s) {
    int r = Integer.parseInt(s.substring(1, 3), 16);
    int g = Integer.parseInt(s.substring(3, 5), 16);
    int b = Integer.parseInt(s.substring(5, 7), 16);
    return new HexColor(r, g, b);
  }
}

Then, we'll register a CustomParameterBinder to handle binding it:

PreparedStatementBinder preparedStatementBinder = 
  PreparedStatementBinder.withCustomParameterBinders(List.of(
  new CustomParameterBinder() {
    @NonNull
    @Override
    public Boolean appliesTo(@NonNull TargetType targetType) {
      return targetType.matchesClass(HexColor.class);
    }		
			
    @NonNull
    @Override
    public BindingResult bind(
      @NonNull StatementContext<?> statementContext, 
      @NonNull PreparedStatement preparedStatement,
      @NonNull Integer parameterIndex,
      @NonNull Object parameter
    ) throws SQLException {
      HexColor hexColor = (HexColor) parameter; 

      // Bind to the PreparedStatement as a value like "#6a5acd"
      preparedStatement.setString(parameterIndex, hexColor.toHexString());

      // Or return BindingResult.fallback() to indicate "I don't want to do custom binding"
      // and Pyranid will fall back to the registered PreparedStatementBinder's binding behavior
      return BindingResult.handled();
    }

    @NonNull
    @Override
    public BindingResult bindNull(
      @NonNull StatementContext<?> statementContext,
      @NonNull PreparedStatement preparedStatement,
      @NonNull Integer parameterIndex,
      @NonNull TargetType targetType,
      @NonNull Integer sqlType
    ) throws SQLException {
      // Handle typed nulls if you want null TypedParameter values to bind successfully
      preparedStatement.setNull(parameterIndex, sqlType);
      return BindingResult.handled();
    }
  }  
));

Database database = Database.withDataSource(dataSource)
  .preparedStatementBinder(preparedStatementBinder)
  .build();

With the custom binder in place, your application code might look like this:

// Given a reference to a hex color...
UUID themeId = ...;
HexColor backgroundColor = HexColor.fromHexString("#6a5acd");

// ...we use the reference as-is and Pyranid will apply the custom binder
database.query("""
  UPDATE theme
  SET background_color = :backgroundColor
  WHERE theme_id = :themeId 
  """)
  .bind("backgroundColor", backgroundColor)
  .bind("themeId", themeId)
  .execute();

Collections and Arrays

Runtime binding of generic types is made difficult by type erasure. For convenience, Pyranid offers special parameters that perform type capture for standard List<E>, Set<E>, and Map<K,V> types:

This makes it easy to create custom binders for common scenarios.

For example, this code...

List<UUID> ids = List.of(
  UUID.fromString("aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"),
  UUID.fromString("bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb")
);

database.query("INSERT INTO t(v) VALUES (:v)")
  .bind("v", Parameters.listOf(UUID.class, ids))
  .execute();

...would be handled by this custom binder, because targetType.matchesParameterizedType(List.class, UUID.class) returns true thanks to runtime type capturing:

PreparedStatementBinder preparedStatementBinder = 
  PreparedStatementBinder.withCustomParameterBinders(List.of(
  new CustomParameterBinder() {
    @NonNull
    @Override
    public Boolean appliesTo(@NonNull TargetType targetType) {
      // For Parameters::mapOf(Class<K>, Class<V>, Map<K,V>), you'd say:
      // matchesParameterizedType(Map.class, MyKey.class, MyValue.class)
      return targetType.matchesParameterizedType(List.class, UUID.class);
    }		
			
    @NonNull
    @Override
    public BindingResult bind(
      @NonNull StatementContext<?> statementContext, 
      @NonNull PreparedStatement preparedStatement,
      @NonNull Integer parameterIndex,
      @NonNull Object parameter
    ) throws SQLException {
      // Convert UUIDs to a comma-delimited string, or null for the empty list 
      List<UUID> uuids = (List<UUID>) parameter;
      String uuidsAsString = uuids.isEmpty()
        ? null
        : uuids.stream().map(Object::toString).collect(Collectors.joining(","));

      // Bind to the PreparedStatement
      preparedStatement.setString(parameterIndex, uuidsAsString);
			
      return BindingResult.handled();
    }
  }  
));

Arrays can also be type-captured for custom binding by wrapping them with Parameters.arrayOf(...):

String[] tags = { "alpha", "beta" };

database.query("INSERT INTO product(tags) VALUES (:tags)")
  .bind("tags", Parameters.arrayOf(String.class, tags))
  .execute();

In most cases, what you actually want is SQL ARRAY binding. SQL arrays are handled directly by the JDBC driver and map cleanly to database array column types, which avoids bespoke encoding/decoding and keeps queries portable across tools.

Heads Up!

If you use any of these collection/array typed parameters, you must define a corresponding CustomParameterBinder to handle them:

These special parameter types do not automatically work out-of-the-box because Pyranid cannot reliably guess how you intend to bind them. This applies even when the wrapped value is null; implement CustomParameterBinder::bindNull if you want typed nulls to bind successfully. Pyranid will detect a missing-binder scenario and throw an exception to indicate programmer error.

Previous
Transactions
Next
ResultSet Mapping