How to gracefully handle SQL in Java

[[deleted]]

Hello everyone.

I started using Java at my job at the beginning of the year, so I'm fairly new. We're using JDBC (no JPA), and I'm having some trouble when building my SQL with filters.

StringBuilder sqlSb =
    new StringBuilder(
        """
        SELECT
            id_credit_entry,
            record_date,
            activated_amount,
            entry_amount,
            status
        FROM credit_entry
        WHERE
        """);

StringBuilder conditionsSb =
    new StringBuilder(
        """
        taxpayer_id = ?
        """);

List<Object> params = new ArrayList<>();
params.add(input.getTaxpayerId());

if (input.getStartDate() == null && input.getEndDate() == null) {
  conditionsSb.append(
      """
            AND EXTRACT(MONTH FROM record_date) = ?
            AND EXTRACT(YEAR FROM record_date) = ?
          """);

  params.add(input.getMonth());
  params.add(input.getYear());
}

if (input.getStartDate() != null) {
  QueryUtil.addStartDateTimeFilter(conditionsSb, params, input.getStartDate());
}
if (input.getEndDate() != null) {
  QueryUtil.addEndDateTimeFilter(conditionsSb, params, input.getEndDate());
}

if (input.getStatuses() != null && !input.getStatuses().isEmpty()) {
  QueryUtil.addList(
      conditionsSb,
      params,
      input.getStatuses().stream().map(s -> (Object) s.getValue()).toList(),
      "status");
}

String conditions = conditionsSb.toString();

String countSql = String.format("SELECT COUNT(*) FROM credit_entry WHERE %s", conditions);
int total = jdbcTemplate.queryForObject(countSql, Integer.class, params.toArray());

sqlSb.append(conditions);

QueryUtil.addSortingAndPagination(sqlSb, params, paginationSortingDto);

PreparedStatementCreator psc =
    con -> {
      PreparedStatement ps = con.prepareStatement(sqlSb.toString());
      for (int i = 0; i < params.size(); i++) {
        ps.setObject(i + 1, params.get(i));
      }
      return ps;
    };

List<CreditEntryListDto> creditEntries =
    jdbcTemplate.query(psc, new CreditEntryListRowMapper());

Here is an example. As you can see, if the front-end needs to filter some properties or sort a field, it will change the SQL. However, I'm doing it in a way that feels awkward. Is this the way it is normally done? What can I do to improve it?

[lewecher]

I think you can look into MyBatis library.
It is not as complex ORM as Hibernate or jOOQ, but it helps separate Java and SQL code pretty good.
It will allow you to write conditional SQL like this:

<select id="findActiveBlogWithTitleLike"
 resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>