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 ? parameter placeholders. 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> = database.queryForList("""
  SELECT *
  FROM employee
  WHERE department_id=?
""", Employee.class, departmentId);

database.execute("""
  INSERT INTO account_award (
    account_id, 
    award_type
  ) VALUES (?,?)
  """, accountId, AwardType.BIG);

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.execute("INSERT INTO example (data) VALUES (?)",
  Parameters.json(json)
);

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.execute("INSERT INTO example (data) VALUES (?)",
  Parameters.json(json, BindingPreference.TEXT)
);

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.execute("INSERT INTO vector_embedding (embedding, content) VALUES (?,?)",
  Parameters.vectorOfDoubles(embedding), content
);

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.execute("""
  INSERT INTO product (
    name,
    vendor_flags,
    tags
  ) VALUES (?,?,?)
""", 
  name,
  Parameters.arrayOf("INTEGER", vendorFlags),
  Parameters.arrayOf("VARCHAR", tags)
);

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

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.

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();
    }
  }  
));

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.execute("""
  UPDATE theme
  SET background_color=?
  WHERE theme_id=? 
""", backgroundColor, themeId);

Standard Collections

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.execute("INSERT INTO t(v) VALUES (?)", Parameters.listOf(UUID.class, ids));

...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>) param;
      String uuidsAsString = uuids.isEmpty()
        ? null
        : uuids.stream().map(Object::toString).collect(Collectors.joining(","));

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

Heads Up!

If you use Parameters::listOf(Class<E>, List<E>), Parameters::setOf(Class<E>, Set<E>), or Parameters::mapOf(Class<K>, Class<V>, Map<K,V>), 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.

Pyranid will detect this missing-binder scenario and throw an exception to indicate programmer error.

Previous
Transactions
Next
ResultSet Mapping