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

Supported JDK Types

Temporal binding uses JDBC ParameterMetaData when available to distinguish TIMESTAMP from TIMESTAMP WITH TIME ZONE targets. For known zone-less TIMESTAMP targets, Pyranid converts Instant and OffsetDateTime parameters through Database.Builder::timeZone(...) and binds the resulting local timestamp. For known TIMESTAMP WITH TIME ZONE targets, Pyranid binds them as time-zone-aware timestamps. ZonedDateTime parameters are normalized to OffsetDateTime first and follow the same rules.

If parameter metadata is unavailable or non-identifying, Instant and OffsetDateTime default to TIMESTAMP_WITH_TIME_ZONE. For drivers or proxies that cannot provide identifying parameter metadata when your target columns are zone-less TIMESTAMP values, configure Database.Builder::ambiguousTimestampBindingStrategy(TIMESTAMP_WITHOUT_TIME_ZONE).

Special Parameters

Special support is provided for JSON/JSONB, vector, SQL ARRAY, and secure diagnostics wrapper parameters. PostgreSQL-specific helpers require pgjdbc on the application classpath, and pgvector parameters require the pgvector database extension.

JSON/JSONB

Useful for storing "stringified" JSON data by taking advantage of the DBMS' native JSON storage facilities, if available (for example, PostgreSQL JSONB, MySQL/MariaDB JSON, SQLite JSON text, SQL Server nvarchar + ISJSON, or Oracle JSON/CLOB columns).

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/native JSON format if supported and fall back to a text representation otherwise. MySQL-family databases bind JSON as text to avoid driver character-set traps.

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 the Parameters::vectorOfDoubles(...), Parameters::vectorOfFloats(...), and Parameters::vectorOfBigDecimals(...) helpers is supported. Pyranid binds a typed VECTOR null for PostgreSQL.

SQL ARRAY

Single-dimension SQL ARRAY binding is supported out-of-the-box for databases whose JDBC drivers support Connection::createArrayOf, such as PostgreSQL. MySQL, MariaDB, SQLite, SQL Server, and Oracle are guarded as unsupported and fail with a clear DatabaseException; use IN-list expansion, JSON, or a database-specific custom binder for those engines.

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, database-specific collection types, or table-valued parameters, 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.

Secure Parameters

Use Parameters::secure(...) for sensitive values that should bind normally but render as a mask in Pyranid diagnostics.

For example, a logger that renders the full StatementLog will include Pyranid's parameter diagnostics:

// Configure a simple StatementLogger that writes to stdout
Database database = Database.withDataSource(dataSource)
  .statementLogger(statementLog -> System.out.println(statementLog))
  .build();

Before wrapping a sensitive value, that log output can display the raw bound value:

database.query("""
  INSERT INTO api_credential (account_id, token_hash)
  VALUES (:accountId, :tokenHash)
  """)
  .bind("accountId", accountId)
  .bind("tokenHash", tokenHash)
  .execute();

parameters=[acct_123, token_hash_abc123]

After wrapping the value, Pyranid still binds tokenHash normally, but the same log output renders the mask:

database.query("""
  INSERT INTO api_credential (account_id, token_hash)
  VALUES (:accountId, :tokenHash)
  """)
  .bind("accountId", accountId)
  .bind("tokenHash", Parameters.secure(tokenHash))
  .execute();

parameters=[acct_123, <redacted>]

Parameters::secure(Object) renders as <redacted>. Use Parameters::secure(Object, String) for a custom display token. This is display-only: the underlying value is still passed to the JDBC PreparedStatement as if it had not been wrapped.

Parameters::secure(...) composes with Parameters::inList(...), Parameters::json(...), Parameters::sqlArrayOf(...), typed parameters, Optional values, and null values.

For broader policies, configure a database-wide ParameterRedactor:

// Use a "redact everything" globally
Database database = Database.withDataSource(dataSource)
  .parameterRedactor(ParameterRedactor.redactAll())
  .build();

The default redactor is ParameterRedactor::none(), which leaves non-secure, non-batch values unchanged. ParameterRedactor::redactAll() masks every non-secure value. Note that use of SecureParameter always takes precedence; its wrapped value is never passed to the redactor.

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 and must not contain null or empty Optional elements. Empty collections, empty arrays, null elements, and empty Optional elements will throw an IllegalArgumentException.

SQL IN does not match NULL; use an explicit IS NULL predicate when null matching is required.

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.

When multiple custom parameter binders apply, Pyranid tries them in the order supplied. Returning BindingResult::fallback() lets the next applicable binder run; if none handles the value, Pyranid's normal binding rules apply.

Because a binder can be asked speculatively before falling back, only mutate the PreparedStatement or other externally-visible state after you have decided to handle the value.

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 CustomParameterBinder::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 let the next applicable custom binder run.
      // If none handles the value, Pyranid's normal binding rules apply.
      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(...) 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();

When your database and JDBC driver support SQL ARRAY values, SQL ARRAY binding is usually a better fit than custom string encoding. SQL arrays are handled directly by the JDBC driver and map cleanly to database array column types, which avoids bespoke encoding/decoding and keeps values visible to database tools.

Parameterized-Type Example

You can also target specific generic shapes. For example, suppose you want to store Map values keyed by Locale with String values in a PostgreSQL JSONB column.

Map<Locale, String> nameTranslations = Map.of(
  Locale.ENGLISH, "Sparkling Water",
  Locale.forLanguageTag("es-MX"), "Agua mineral"
);

PreparedStatementBinder preparedStatementBinder =
  PreparedStatementBinder.withCustomParameterBinders(List.of(
  new CustomParameterBinder() {
    @NonNull
    @Override
    public Boolean appliesTo(@NonNull TargetType targetType) {
      return targetType.matchesParameterizedType(Map.class, Locale.class, String.class);
    }

    @NonNull
    @Override
    public BindingResult bind(
      @NonNull StatementContext<?> statementContext,
      @NonNull PreparedStatement preparedStatement,
      @NonNull Integer parameterIndex,
      @NonNull Object parameter
    ) throws SQLException {
      Map<Locale, String> valuesByLocale = (Map<Locale, String>) parameter;

      PGobject jsonbValue = new PGobject();
      jsonbValue.setType("jsonb");
      jsonbValue.setValue(GSON.toJson(valuesByLocale));
      preparedStatement.setObject(parameterIndex, jsonbValue);

      return BindingResult.handled();
    }

    @NonNull
    @Override
    public BindingResult bindNull(
      @NonNull StatementContext<?> statementContext,
      @NonNull PreparedStatement preparedStatement,
      @NonNull Integer parameterIndex,
      @NonNull TargetType targetType,
      @Nullable Integer sqlType
    ) throws SQLException {
      if (!appliesTo(targetType))
        return BindingResult.fallback();

      preparedStatement.setNull(parameterIndex, Types.OTHER);
      return BindingResult.handled();
    }
  }));

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

database.query("""
  INSERT INTO product (name_translations)
  VALUES (:nameTranslations)
  """)
  .bind("nameTranslations", Parameters.mapOf(Locale.class, String.class, nameTranslations))
  .execute();

This pattern pairs naturally with a matching CustomColumnMapper. See the Parameterized-Type Example in the ResultSet Mapping docs for the read side.

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.

SQL Injection and Dynamic SQL

Pyranid named parameters bind SQL values through JDBC PreparedStatement placeholders. Use them for values such as IDs, names, timestamps, limits, and status codes.

Named Parameters Bind Values, Not SQL Structure

Table names, column names, sort directions, operators, and other SQL syntax must not be built from untrusted input. If SQL structure needs to vary, map application-level choices to hardcoded SQL fragments with an allowlist.

// For example - don't use untrusted input to build ORDER BY.
// Instead, map to trusted types internally like this:
String orderBy = switch (sort) {
  case NAME -> "name";
  case CREATED_AT -> "created_at";
};

String direction = descending ? "DESC" : "ASC";

List<Employee> employees = database.query("""
  SELECT *
  FROM employee
  ORDER BY %s %s
  """.formatted(orderBy, direction))
  .fetchList(Employee.class);

Pyranid exception messages include bounded SQL and bounded parameter display values. Under the default ParameterRedactor::none(), non-secure, non-batch values render verbatim. Wrap sensitive bind values with Parameters::secure(...) or configure Database.Builder::parameterRedactor(...) when values should not appear in diagnostics. Custom StatementLogger implementations that read StatementContext::getParameters() receive raw values; use StatementContext::getRedactedParameters() for safe display.

Previous
Transactions
Next
ResultSet Mapping