Using @Define to @Bind a list leads to a syntaxt error

[Luca Pette]

Hello folks!

First of all, let me thank you for this wonderful library. I've been using it for a few weeks now and It's a delightful experience.

I have run into an issue which I believe is more about me misusing the library than anything else. But I thought it was worth sharing and ask for pointers.

Suppose I have a DAO that looks like this:

 @SqlQuery("SELECT name FROM players p where p.id = :playerId")
 String queryA(@Bind("playerId") UUID playerId);
 

 @SqlQuery("SELECT count(*) FROM players p <query>")  
 @AllowUnusedBindings
 int queryB(@Define("query") String query, @Bind("playerId") UUID playerIds);
 

 @SqlQuery("SELECT count(*) FROM players p <query>")  
 @AllowUnusedBindings
 int queryC(@Define("query") String query, @Bind("playerIds") List<UUID> playerIds);

And then I try to use the queries this way:

Jdbi jdbi = Jdbi.create("jdbc:postgresql://localhost/jdbi_test");
jdbi.installPlugin(new SqlObjectPlugin());
jdbi.installPlugin(new PostgresPlugin());

PlayerDAO playerDAO = jdbi.onDemand(PlayerDAO.class);

playerDAO.queryA(UUID.randomUUID());
playerDAO.queryB("where p.id = :playerId", UUID.randomUUID());
playerDAO.queryC("where p.id in (<playerIds>)", List.of(UUID.randomUUID()));

The third query will fail with the following error:

 org.jdbi.v3.core.statement.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "<"
 Position: 47 [statement:"SELECT count(*) FROM players p where p.id in (<playerIds>)", arguments:{positional:{1:[1f876165-7df8-435a-ae4d-1ccd5b3a6053]}, named:{playerIds:[1f876165-7df8-435a-ae4d-1ccd5b3a6053]}, finder:[]}]
 at org.jdbi.v3.core.statement.SqlStatement.internalExecute(SqlStatement.java:1676

So my understanding is that I can't bind a list this way. Am I just misusing the API here? Or should this be possible?

For context, in the project I'm using jdbi I have mostly very simple queries and jdbi is really shining. 
But I've got a couple of endpoints that require some fancy search and I'd want to construct the query 
on the fly (so that I'm in full control of the query that will get executed, after all that's the number one reason why I chose jdbi). 

The idea pattern (from my perspective, please bear in mind I'm new to the library and I may be missing some obviously solution) for such cases would be a DAO with a signature like:

 @SqlQuery("SELECT p.id, p.name FROM players p <query>")  
 @AllowUnusedBindings 
 PlayerStatsDTO search(@Define("query") String query, @Bind PlayerSearchDTO playerSearchDTO); 

That would allow one to build a simple query builder and be in full control of the query with a neat api (and all the safeness of parameter binding). but it's not working with lists and I'm not sure how to solve it.

Any pointer would be greatly appreciated.

In case you want to play around with the code, I published a test repo here: https://github.com/lucapette/jdbi-uuid-test

Thank you for your time!

[Steven Schlansker]

Hi Luca, thanks for your interest and question!

I believe the problem is that templates are not recursively expanded:  when <query> is expanded, <playerIds> is a literal value, not itself a template expression.

I recommend instead of constructing <query> in code, have a template literal that encompasses all query possibilities.  I end up with things like

select fields from table

   where true

  <if(name)> and name = :name<endif>

  <if(age)> and age = :age<endif>

  <if(sortBy)> order by <sortBy> <endif>

  <if(limit)> limit <limit> <endif>

You need to have careful use of @Bind vs @Define (@BindPojo helps if you have a query object and @DefineNamedBindings can help a lot here too) but once you get it set up, it works beautifully.

Also, if you're going to do a lot of template work and haven't already committed, I recommend replacing the stock template engine with FreeMarker.  It's overall just a much nicer system to work with.

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/aedad8ee-9088-4168-83b0-eb963b3f8ec2o%40googlegroups.com.

[Luca Pette]

Hi Steven,

thank you very much for your answer! TIL I can use FreeMarker (just looked up the docs) and that's a very nice solution indeed (especially as I use it already to render HTML pages).

I suspected it was about recursive expansion and maybe it's a feature that jdbi doesn't support it. After all, as you're suggesting, I can go for FreeMarker templates.

Thank you again for your time!

To unsubscribe from this group and stop receiving emails from it, send an email to jd...@googlegroups.com.

[luca...@gmail.com]

Hi Steven,

I just tried out FreeMarker templates and feel very good about it. I have one more question if you have the time: as I understand it now, I can't have a list inside a pojo? I would still have to use @DefineList for a list parameter to bind the list correctly?

Thank you very much again!

[Steven Schlansker]

That's correct, currently DefineList and BindList can only be used as formal Jdbi parameters, unfortunately not nested bean / pojo properties.

In our long-term plans we'd like to create much more flexible mapping that allows for advanced combinations like this but it's a large undertaking with nobody on it at the moment :)

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/8f859dda-2a4a-49b0-99ec-02b8e0dca147n%40googlegroups.com.