PreparedStatement template policy

[Remi Forax]

Hi Jim,
most templating languages JSP, ASP, etc have moved to a typed version,
by example the template of Play framework were not initially typed but they are typed in Play 2 (since 2012).
We do not have to make the same mistake.

A template is not only something that goes from Java to the String world,
it can be an abstract specification that goes into the other direction, that the reason why the return type of a template policy is not a String and why the parameters should to be typed.

BTW, i would prefer to have the real types, the Type object not the Class object, otherwise doing pattern matching on JSON is not possible because of the mismatch between the JavaScript type (number ?) and the Java type (int or double ?).

cheers,
Rémi


----- Original Message -----
> From: "Jim Laskey" <james.laskey at oracle.com>
> To: "Oleg Sobolev" <sobolev.ov at gmail.com>
> Cc: "amber-dev" <amber-dev at openjdk.java.net>
> Sent: Friday, December 10, 2021 3:49:47 PM
> Subject: Re: PreparedStatement template policy

> 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?