PreparedStatement template policy

[Oleg Sobolev]

Hi Jim.

> I suspect at some point that some organization might implement an
> optimizing SQL policy where types are gleaned from the BSM call site

Do you need to implement TemplatePolicy.Linkage interface for this? It
is currently sealed, allowing only two implementations.

> A requirement of TemplatePolicys is that a policy always have a fallback
> apply method

For my use case it would be sufficient to add a method like
    List<Class> types()
to TemplatedString. It does not affect the existing apply method.

> 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

It's easy to create some SqlBuilder for this, but in the end when
building PreparedStatement from it we still need types for null
parameters.

> Create a set of formatting specifications that indicate expected type

This solves the problem, but I want to point out some disadvantages:
1) It is not as user-friendly, the library user has to remember
formatting specifiers (I always have to look them up when using
Formatter)
2) It is not as easily extensible. Suppose the user wants to add a new
supported parameter type. In the case of types he can simply say:
policy.register(MyType.class, new MyTypeParameterSetter());
In the case of format specifiers there are possible problems when
using them in a large project: different teams can use different
specifiers for the same type, or the same specifier for different
types.

I understand that adding the "List<Class> types()" method is more or
less useful only for this specific use case, but having it would be
nice.

пт, 10 дек. 2021 г. в 17:49, Jim Laskey <james.laskey at oracle.com>:
>
> 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?
>