PreparedStatement template policy

[Jim Laskey]

Oleg,

I suspect at some point that some organization might implement an optimizing SQL policy where types are gleaned from the BSM call site. However, this isn't going to be a type panacea either. A requirement of TemplatePolicys is that a policy always have a fallback apply method. This is so that TemplateStrings can be passed around or composed and later have a policy applied.

When we talked to the DB/JDBC community and looked at thousands of examples, we realized that it's fairly uncommon for an SQL statement to be used as-is. More often than not a statement is built up. Ex.:

	String statement = "select id,name from HRDB";
	...
	if (...) {
		statement += " where country = ?";
	}
	...
	if (...) {
		statement += " order by startDate desc";
	}
	...
	
It was the consensus of the DB/JDBC experts that some style of formatting directives needed to added to statements to indicate how the following value should be handled. Ex.

	String statement = "select id,name from HRDB";
	...
	if (...) {
		statement += " where country = %q\{country}"; // quote the country
	}
	...
	if (...) {
		statement += " order by startDate desc";
	}
	...

So I would take that approach. Create a set of formatting specifications that indicate expected type. This would also be useful in debugging when an unexpected type is arrives. You could also make it an error if the specification is missing.

Cheers,

-- Jim





> On Dec 9, 2021, at 4:25 PM, Oleg Sobolev <sobolev.ov at gmail.com> wrote:
> 
> I want to implement a template policy which safely generates
> PreparedStatement with ?-parameter substitution. I have a problem with
> null values:
> 
> public class PreparedStatementPolicy implements
> TemplatePolicy<PreparedStatement, SQLException>{
> 
>    private final Connection connection;
> 
>    public PreparedStatementPolicy(Connection connection) {
>        this.connection = connection;
>    }
> 
>    @Override
>    public PreparedStatement apply(TemplatedString templatedString)
> throws SQLException {
>        String sql = templatedString.template().replace('\uFFFC', '?');
>        PreparedStatement ps = connection.prepareStatement(sql);
>        List<Object> values = templatedString.values();
>        for (int i = 0; i < values.size(); i++) {
>            int index = i + 1;
>            Object value = values.get(i);
>            if (value == null) {
>                int type = Types.VARCHAR; // todo: how to determine
> it, could be INTEGER as well???
>                ps.setNull(index, type);
>            } else {
>                // set nonnull parameter...
>            }
>        }
>        return ps;
>    }
> 
>    public static void main(String[] args) {
>        Connection connection = ...;
>        PreparedStatementPolicy prepare = new
> PreparedStatementPolicy(connection);
>        String name = null;
>        PreparedStatement ps = prepare."update tab set name = \{name}";
>    }
> }
> 
> The question is: how to determine which of Types.* constants to use in
> the case of a null parameter value? For this I need to get the static
> type of a parameter from a TemplatedString, but it is not available.
> There are workarounds, but they either require additional database
> roundtrips or library user inconvenience.
> 
> Is it possible to add information about static types of template
> parameters to TemplatedString?