Parametric Table Name

[CemoKoc]

Hi, 

I am using Dropwizard with JDBI. The way which JDBI exposing is wonderful in terms of DRY concepts.

Now what I would like to is parametric table names.

@SqlQuery("select name_:table from something where id = :id")

String findName(@Bind("table") String table, @Bind("id") int id);

I am quite novice and still trying to figure out natural way of using JDBI.

Seems RegisterArgumentFactory is not suitable but considering using a StringTemplate but I am not sure what I should do.

Any help?

Thanks

[Cemo]

Hi,

I have actually done the necessary implementation. But I am little dubious.

1. Added a new Lexer. It appears almost same as Colon statement lexer just added a table section.

...
TABLE: '#';
TABLE_PARAM: TABLE ('a'..'z' | 'A'..'Z' | '0'..'9' | '_' | '.')+;
COLON: ':';
NAMED_PARAM: COLON ('a'..'z' | 'A'..'Z' | '0'..'9' | '_' | '.' | '#')+; 
...

2. Implemented a new StatementRewriter. It is almost same as org.skife.jdbi.v2.ColonPrefixNamedParamStatementRewriter . Added only this part

                case TABLE_PARAM:
                    //           note that there is not any change in stmt
                    //          stmt.addNamedParamAt(t.getText().substring(1, t.getText().length()));
                    b.append(t.getText().substring(1, t.getText().length()));
                    break; 

3. And used like this, note that table paremeter.

    @OverrideStatementRewriterWith(DyntableStatementRewriter.class)
    @RegisterMapper(SomethingMapper.class)
    interface DyntableTest
    {
        @SqlUpdate("insert into something (id, name) values (:id, :name)")
        public void insert(@BindBean Something s);
        @SqlQuery("select id, name from some#table where id = :id")
        public Something findById(@Bind("id") int id, @Bind("table") String table);
    }

Any comment about this?

Thanks

[Brian McCallister]

I would tend towards using stringtemplate for this, doing it in the
rewriter certainly works, but you are now tied to your own grammar
(far from the end of the world).

I'll get some docu on the stringtemplate approach up today, if I can
(my son is sick, so my day is already a bit of a mess), which should
help a lot in general as well as with your specific case. For what you
described, given the class:

@ExternalizedSqlViaStringTemplate3
class Foo {

@SqlQuery
String findName(@Define("table") String table, @Bind("id") int id);

}

You'd have a template group file on the classpath at

/com/example/Foo.sql.stg with the contents

findName(table) <<
select name from user_<table> where id = :id
>>

(I took the liberty of changing the select so that the table name
suffix is being passed in)

When this executes it will find the template group for the Foo class,
and load the template from the template group for the method name
(findName). The @Define'd value wil be passed to the template (matches
on the formal parameter names) and interpolated. Then the resultant
sql will be parsed (rewritten) to determine the named parameters for
binding.

Regardless of which approach you take, be careful about the source of
strings for interpolation. The interpolation needs to happen before
the prepared statement is created (unlike named parameter binding) so
the string becomes part of the statement itself, opening the door to
sql injection attacks.

> --
>
>

[Cemo]

Hi Brian,

Thanks for the explanation. Actually It was my first day with JDBI and I can honestly say that it is really, as you aimed, simple and predictable.

I loved JDBI because of reducing extra boilerplate codes. But extra template group file is not only hard to maintain but also disrupting the beauty of JDBI.

If it can be possible these

  @SqlQuery("select name from user_<table> where id = :id")

  String findName(@Define("table") String table, @Bind("id") int id);

 

or

  @SqlQuery("select name from user_:table where id = :id")

  String findName(@Define("table") String table, @Bind("id") int id);

 

I would go with these. 

Note that in this approach it is possible using for "in queries" too. 

What do you think about these? 

Thanks

[Brian McCallister]

On Wed, Aug 22, 2012 at 12:00 PM, Cemo <cemalet...@gmail.com> wrote:
> Hi Brian,
>
> Thanks for the explanation. Actually It was my first day with JDBI and I can
> honestly say that it is really, as you aimed, simple and predictable.
>
> I loved JDBI because of reducing extra boilerplate codes. But extra template
> group file is not only hard to maintain but also disrupting the beauty of
> JDBI.
>
> If it can be possible these
>
> @SqlQuery("select name from user_<table> where id = :id")
> String findName(@Define("table") String table, @Bind("id") int id);
>
> or
>
> @SqlQuery("select name from user_:table where id = :id")
> String findName(@Define("table") String table, @Bind("id") int id);

I would tend towards the first as it is much clearer that they are
fundamentally different things being bound. Having a template
processor run across it ahead of bind parsing is pretty
straightforward (heck, the st3 one may do that now for all I know, I
forget). If the ST3 one does not, I'd be happy to change it so that it
does (if there is a sql literal, treat it as a lone template).

On externalizing vs not -- I find myself generally putting the sql
directly into the annotations by default, but externalizing when I get
a lot of sql, or when the sql tends to get long. I dearly wish Java
supported heredocs :-(

-Brian

> --
>
>

[Cemo]

Hi Brian,

I have written something like this. But it is not working. The first error was lack of statement template file. (I believe it should not be mandatory.) Second error was UnableToCreateStatementException.

If you will make it work, you will make my day. :) I will immediately test.

    @ExternalizedSqlViaStringTemplate3

    @RegisterMapper(SomethingMapper.class)

    static interface MyTest

    {

        @SqlUpdate("insert into something (id, name) values (:id, :name)")

        public void insert(@BindBean Something s);

        @SqlQuery("select id, name from some<table> where id = :id")

        public Something findById(@Bind("id") int id, @Define("table") String table);

    }

Thanks

[Brian McCallister]

On Thu, Aug 23, 2012 at 4:18 AM, Cemo <cemalet...@gmail.com> wrote:
> Hi Brian,
>
> I have written something like this. But it is not working. The first error
> was lack of statement template file. (I believe it should not be mandatory.)

I agree, if there is no template group file it should default to an
empty template group.

> Second error was UnableToCreateStatementException.

Given a default empty template group, if there is no template for a
name, then it should treat the string literal as a template.

> If you will make it work, you will make my day. :) I will immediately test.

Hacking on it right now.

-Brian

>
> @ExternalizedSqlViaStringTemplate3
> @RegisterMapper(SomethingMapper.class)
> static interface MyTest
> {
> @SqlUpdate("insert into something (id, name) values (:id, :name)")
> public void insert(@BindBean Something s);
>
> @SqlQuery("select id, name from some<table> where id = :id")
> public Something findById(@Bind("id") int id, @Define("table")
> String table);
>
> }
>
> Thanks
>
> On 22 August 2012 22:05, Brian McCallister <bri...@skife.org> wrote:
>>
>> I would tend towards the first as it is much clearer that they are
>> fundamentally different things being bound. Having a template
>> processor run across it ahead of bind parsing is pretty
>> straightforward (heck, the st3 one may do that now for all I know, I
>> forget). If the ST3 one does not, I'd be happy to change it so that it
>> does (if there is a sql literal, treat it as a lone template).
>
>

> --
>
>

[Brian McCallister]

On Thu, Aug 23, 2012 at 9:18 AM, Brian McCallister <bri...@skife.org> wrote:

> I agree, if there is no template group file it should default to an
> empty template group.
>
>> Second error was UnableToCreateStatementException.
>
> Given a default empty template group, if there is no template for a
> name, then it should treat the string literal as a template.
>
>> If you will make it work, you will make my day. :) I will immediately test.
>
> Hacking on it right now.
>

Okay, so I have the changes. One question I am unsure of is whether or
not to use an implicit (empty) template group by default or not. My
hesitation on doing it by default is that the name of the annotation
right now (@ExternalizeSqlViaStringtemplate3) strongly indicates that
it is for externalizing the sql. The implementation I have right now
requires a flag to use an implicit group:


@ExternalizedSqlViaStringTemplate3(implicitTemplateGroup = true)
@RegisterMapper(SomethingMapper.class)
static interface HoneyBadger
{
@SqlUpdate("insert into <table> (id, name) values (:id, :name)")
public void insert(@Define("table") String table, @BindBean
Something s);

@SqlQuery("select id, name from <table> where id = :id")
public Something findById(@Define("table") String table,
@Bind("id") Long id);
}

But this is pretty yucky. I lean towards defining a new annotation
which just says @UseStringTemplate3StatementLocator or such, which
defaults to the new behavior, and deprecating the old annotation while
maintaining exact backwards compatibility on the current annotation.

-Brian

[Brian McCallister]

I just published jdbi-2.39-SNAPSHOT to oss.s.o with
@UseStringTemplate3StatementLocator which has an implicit empty
template group, and treats literals as templates. Can you give it a
try and see if it works for you?

I'm not sure if oss.s.o syncs snapshots to maven central, you may need
to build and install it locally to get the snapshot :-/

-Brian

[Cemo]

I have tested a few use case. And checked some your test cases too. Working like a charm.

How simple and effective it is :) No template file, no extra layer, no repetitive implementations :) 

You are simply great.

Tomorrow I will start to implement in our live system. I will inform you in case a problem.

Thanks

[Brian McCallister]

On Thu, Aug 23, 2012 at 11:06 AM, Cemo <cemalet...@gmail.com> wrote:
> I have tested a few use case. And checked some your test cases too. Working
> like a charm.
>

Great! I'll cut 2.39 now, should be in central within a couple hours.

-Brian

> How simple and effective it is :) No template file, no extra layer, no
> repetitive implementations :)
>
> You are simply great.
>
> Tomorrow I will start to implement in our live system. I will inform you in
> case a problem.
>
> Thanks
>
>
>
>
> On 23 August 2012 19:10, Brian McCallister <bri...@skife.org> wrote:
>>
>> I just published jdbi-2.39-SNAPSHOT to oss.s.o with
>> @UseStringTemplate3StatementLocator which has an implicit empty
>> template group, and treats literals as templates. Can you give it a
>> try and see if it works for you?
>
>

> --
>
>

[Tatu Saloranta]

On Thu, Aug 23, 2012 at 11:40 AM, Brian McCallister <bri...@skife.org> wrote:
> On Thu, Aug 23, 2012 at 11:06 AM, Cemo <cemalet...@gmail.com> wrote:
>> I have tested a few use case. And checked some your test cases too. Working
>> like a charm.
>>
>
> Great! I'll cut 2.39 now, should be in central within a couple hours.

That sounds like a great deal: "Now only 2.39! Hurry, this offer won't
last forever!" :-)

-+ Tatu +-

[Cemo]

Hi Brian,

We have a minor issue about StringTemplates.

This is valid

@SqlQuery("select id, name from <table> where id = :id and 2>1")

but not this one

@SqlQuery("select id, name from <table> where id = :id and 1<2")

Because of the 'less than' operator is interfering with template. Double Maybe using double curly brackets is better. {{table}} 

By the way the double less than '>>' or greater than '>>' is shifting operator.

Thanks

-+ Tatu +-

--

[Brian McCallister]

You can escape the open angle, but this is a limitation of string template :-(

ST3 only supports <foo> or $foo$. For literals with just straight interpolation, another template mechanism which supports different delimiters may be good.

-Brian

--