Sending the DEFAULT placeholder via JDBC?

Question

Is there any way, via JDBC, to send the DEFAULT placeholder explicitly, like in INSERT INTO sometables VALUES (blah, DEFAULT)? (I'm almost certain the answer is "no", but I'm looking for JDBC-expert confirmation).

Say you had a PreparedStatement like:

INSERT INTO mytable(a, b) VALUES (?, ?)

for table:

CREATE TABLE mytable (
    a integer,
    b integer default some_function()
);

and you wanted to use the database-set DEFAULT for mytable.b in some executions in a batch but not others.

In regular SQL you'd write:

INSERT INTO mytable(a, b) VALUES (1, 42)
INSERT INTO mytable(a, b) VALUES (2, DEFAULT);
...

or of course:

INSERT INTO mytable(a, b) VALUES (1, 42)
INSERT INTO mytable(a) VALUES (2);

... but you can't do this via JDBC. setString("DEFAULT") will of course not send the DEFAULT keyword, just the string-literal 'DEFAULT'.

Is there a way to set a placeholder-parameter that means DEFAULT in any widely used drivers?

I don't see a way to do it with the standard API and spec.

I'm imagining something like:

pstmt.setObject(2, Postgresql.DEFAULT, Types.OTHER);

where Postgresql.DEFAULT is a special placeholder instance, since there doesn't seem to be a setDefault() method for PreparedStatement.

Does any existing driver support this?

Answer 1

There is no standard way of doing this. As far as I know the SQL standard does not support a mechanism for declaring to use the DEFAULT through parameters. The SQL standard seems to assume that each INSERT is crafted for its specific purpose. So declaring DEFAULT can only be done in the insert statement itself and not as a value for a parameter. In these kinds of decisions, the JDBC specification usually follows the SQL standard.

The only method you currently have is to create a vendor-specific method in the JDBC driver, for example as you specified in your question itself, but you could also think of something like:

To clarify: below is an example of how a driver implementation could solve it, it doesn't actually work like this.

Using setNull(int, int)

setNull(idx, PostgresTypes.DEFAULT_VALUE)

or using setNull(int, int, String)

setNull(idx, Types.<correct-field-type>, Postgres.DEFAULT_VALUE_MARKER)

However this assumes that PostgreSQL actually has a method of specifying DEFAULT through parameters, or that the driver implementation will parse and recreate the statement for each set of received parameters so that it can declare a literal DEFAULT.

I don't know if there is any driver that currently supports such a workaround.

Answer 2

default isn't a value literal - it's a pseudocolumn (or at least I hope that's what they're called in postgres). Since it isn't a value but a part of insert's syntax, it can't be bound to a placeholder, just like the table name cannot.