# Handling Null Values Efficiently with Spring Named Parameter Binding
# Introduction
Using Spring’s `NamedParameterJdbcTemplate` and binding `null` values with a SQL type code and type name may lead to the [pgJDBC](https://github.com/pgjdbc/pgjdbc) driver performing costly metadata queries to infer internal types. This is by JDBC specification and pgJDBC design, but it’s good to know for performance reasons as it may go unnoticed. This post provides a few tips on how to avoid such queries entirely.
# Problem
Any application or framework that use `PreparedStatement.setNull(int,int,String)` is candidate to these meta queries. The [javadoc](https://docs.oracle.com/en/java/javase/25/docs/api/java.sql/java/sql/PreparedStatement.html#setNull\(int,int,java.lang.String\)) states:
> void setNull(int parameterIndex, int sqlType, [String](https://docs.oracle.com/en/java/javase/25/docs/api/java.base/java/lang/String.html) t[ypeNam](https://docs.oracle.com/en/java/javase/25/docs/api/java.base/java/lang/String.html)e) throws [SQLException](https://docs.oracle.com/en/java/javase/25/docs/api/java.sql/java/sql/SQLException.html)
>
> Sets the designated parameter to SQL `NULL`. This version of the method `setNull` should be used for user-defined types and REF type parameters. Examples of user-defined types include: STRUCT, DISTINCT, JAVA\_OBJECT, and named array types.
>
> **Note:** To be portable, applications must give the SQL type code and the fully-qualified SQL type name when specifying a NULL user-defined or REF parameter. In the case of a user-defined type the name is the type name of the parameter itself. For a REF parameter, the name is the type name of the referenced type. If a JDBC driver does not need the type code or type name information, it may ignore it. Although it is intended for user-defined and Ref parameters, this method may be used to set a null parameter of any JDBC type. If the parameter does not have a user-defined or REF type, the given typeName is ignored.
The SHOULD phrase means you can use it for any column type, including primitives and JSONB for example. Typically, the pgJDBC implementation lways attempts to resolve the internal type when both the `sqlType` and `typeName` is specified. In case of `ARRAY`, it also attempts to resolve the array element type, which is another even more involved meta data query. This repeats for every parameter bind operation, so it can really amplify write latency in the worst case.
A metadata query can look like:
```pgsql
SELECT pg_type.oid, typname
FROM pg_catalog.pg_type LEFT
JOIN (SELECT ns.oid AS nspoid, ns.nspname, r.r
FROM pg_namespace AS ns
JOIN (SELECT s.r, (current_schemas(_))[s.r] AS nspname
FROM ROWS
FROM (generate_series(_, array_upper(current_schemas(_), _))) AS s (r)) AS r USING (nspname)) AS sp
ON sp.nspoid = typnamespace
WHERE typname = _
ORDER BY sp.r, pg_type.oid DESC
LIMIT _
```
To demonstrate this, the following code sample passes `null` for the “price” and “description” columns. If `null` values are passed along with a column type constant and a type name, then the above query is sent by the driver to resolve the internal PG type.
```java
MapSqlParameterSource namedParameters = new MapSqlParameterSource()
.addValue("id", UUID.randomUUID())
.addValue("inventory", 99)
.addValue("name", "product-x99")
.addValue("sku", "X-99")
.addValue("price", null, Types.DECIMAL, "decimal")
.addValue("description", null, Types.OTHER, "jsonb");
new NamedParameterJdbcTemplate(jdbcTemplate)
.update("insert into product (id, inventory, name, description, price, sku) " +
"values (:id,:inventory,:name,:description,:price,:sku)", namedParameters);
```
# Solution
* Prefer indexed placeholders `(?,?,..)` over named parameter placeholders, thus avoiding the `NamedParameterJdbcTemplate` problem entirely
* Omit specifying the column type name. For example:
* `addValue("price", null)`
* `addValue("description", null)`
* If still binding a `NULL` value with a type name, use another type constant like `Types.NULL`:
* `addValue("price", null, Types.NULL, "decimal")`
* `addValue("description", null, Types.NULL, "jsonb")`
* Set the system property `-Dspring.jdbc.getParameterType.ignore=true` ([source](https://github.com/spring-projects/spring-framework/blob/2641b5d783aac7c76acb8db86%5B%E2%80%A6%5Dn/java/org/springframework/jdbc/core/StatementCreatorUtils.java)) to avoid similar situations with ARRAYs
# How to verify
Set the pgJDBC and spring jdbc core namespace logger levels to `TRACE`.
```xml
```
The log output will be verbose but watch out for all `pg_catalog` queries before your actual INSERT or UPDATE statements:
```pgsql
2025-11-26 12:33:06.157 TRACE [o.postgresql.core.v3.QueryExecutorImpl] simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@5c134052, maxRows=0, fetchSize=0, flags=17
2025-11-26 12:33:06.157 TRACE [o.postgresql.core.v3.QueryExecutorImpl] FE=> Parse(stmt=null,query="SELECT pg_type.oid, typname FROM pg_catalog.pg_type LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r from pg_namespace as ns join ( select s.r, (current_schemas(false))[s.r] as nspname from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r using ( nspname ) ) as sp ON sp.nspoid = typnamespace WHERE typname = $1 ORDER BY sp.r, pg_type.oid DESC LIMIT 1",oids={1043})
2025-11-26 12:33:06.157 TRACE [o.postgresql.core.v3.QueryExecutorImpl] FE=> Bind(stmt=null,portal=null,$1=<('jsonb')>,type=VARCHAR)
2025-11-26 12:33:06.157 TRACE [o.postgresql.core.v3.QueryExecutorImpl] FE=> Describe(portal=null)
2025-11-26 12:33:06.157 TRACE [o.postgresql.core.v3.QueryExecutorImpl] FE=> Execute(portal=null,limit=0)
2025-11-26 12:33:06.157 TRACE [o.postgresql.core.v3.QueryExecutorImpl] FE=> Sync
```
# Conclusion
Efficiently handling `null` values in Spring's `NamedParameterJdbcTemplate` is essential for optimizing database interactions and minimizing unnecessary overhead. This involves preferring indexed placeholders, omitting column type names, and configuring a system property to avoid costly metadata queries.