How to add optional column in JDBI?

userkY681 0
Viewed 24

I have big SQL, about 50 lines, and we use JDBI with Postgresql, and I need to add one additional not mandatory column, so I did:

,case when :byPortfolio is true then ia1.investmentAccountAbbreviationCode else null end as portfolio

so if :byPortfolio is true this column should be removed, and if false this column should be present. Also ia1.investmentAccountAbbreviationCode should be present in Group by clause if it's present, so I added:

,case when :byPortfolio2 is true then ia1.investmentAccountAbbreviationCode else null end

and it works if I fun it as SQL query, then I copied it to sql.stg template file for JDBI and now when I run it I see an exceptioin:

org.postgresql.util.PSQLException: ERROR: column "ia1.investmentaccountabbreviationcode" must appear in the GROUP BY clause or be used in an aggregate function

I don't understand why JDBI can't handle it. Function described like this:

@SqlQuery
@UseStringTemplateSqlLocator
@RegisterBeanMapper(SolutionOwnershipDataEntity.class)
List<SolutionOwnershipDataEntity> getSolutionOwnershipPercentageOfFundByDate(
        @Bind("inputDate") String inputDate,
        @Bind("fundSeriesId") Integer fundSeriesId,
        @Bind("governanceCommitteeTypeCode") String governanceCommitteeTypeCode,
        @Bind("byPortfolio") boolean byPortfolio,
        @Bind("assetClass") String assetClass,
        @Bind("vehicleType") String vehicleType);

Please help to fix it? We can fix current code or maybe there is other way to handle unnecessary column? I mean I can write two 50 lines SQL with same code, only difference would be presence or absence of one column, but I want to have one query with one unnecessary column, what is the best way to achieve this?

jdbi java sql postgresql
1 Answers

For byPortfolio, you need to use @Define instead of @Bind. @Bind is only for binding values to parameters of a query, while @Define can be used for placeholders and templating.

For the templating, you can then put the optional parts between <if(byPortfolio)> and <endif>. The template syntax is not related to normal SQL.

Here is a complete example from the documentation:

@SqlQuery("""
  select id, name
  from account
  order by <if(sort)> <sortBy>, <endif> id
  """)
@UseStringTemplateEngine
List<Account> selectAll(@Define boolean sort, @Define String sortBy);

For more details, please have look at the official documentation: https://jdbi.org/#_stringtemplate_4

Henning0
Related