parsedQuery in Query should be settable for try-with-resources

[Marco-Sulla]

Our company uses Spring Boot 2 with sq2o. We also created a code generator for sql2o that creates Spring model, repository and service. We need to close the query because Oracle reach the max connection open. So currently we have to create a Repository like this:

public static String getByGroupidSql = (
    selectBase + "from GROUPS g " + 
    "where " + 
        "GROUPID = :groupid "
);

@Override
public Groups getByGroupid(Integer groupid, Query query, Connection con) {
    logger.debug("GroupsRepository.getByGroupid(): groupid: " + groupid);

    query.addParameter("groupid", groupid);

    Groups res = query.executeAndFetchFirst(Groups.class);
    return res;
}

@Override
public Groups getByGroupid(Integer groupid) {
    try (Connection con = sql2o.open(); Query query = con.createQuery(getByGroupidSql)) {
        return this.getByGroupid(groupid, query, con);
    }
}

and the method with connection is called by Service this way:

String getByGroupidSql = MyRepository.getByGroupidSql;

try (Connection con = sql2o.open(); Query query = con.createQuery(getByGroupidSql)) {
    return myRepository.getByGroupid(groupid, query, con);
}

This way the query autoclose with try-with-resources. BUT we have to pass to the service the sql. The service should NOT know the sql, or be able to modify it. But currently this is the only way I found to do a try-with-resources with both Connection and Query.

I propose to create a constructor of Query without the sql parameter and add a setter for the sql. If you add it, I can write the repository this way:

@Override
public Groups getByGroupid(Integer groupid, Query query, Connection con) {
    logger.debug("GroupsRepository.getByGroupid(): groupid: " + groupid);

    query.setQueryText(
        selectBase + "from GROUPS g " + 
        "where " + 
            "GROUPID = :groupid "
    );

    query.addParameter("groupid", groupid);

    Groups res = query.executeAndFetchFirst(Groups.class);
    return res;
}

@Override
public Groups getByGroupid(Integer groupid) {
    try (Connection con = sql2o.open(); Query query = con.createQuery()) {
        return this.getByGroupid(groupid, query, con);
    }
}

and the service:

try (Connection con = sql2o.open(); Query query = con.createQuery()) {
    return myRepository.getByGroupid(groupid, query, con);
}

Much cleaner and robust.

[Marco-Sulla]

Well, the solution was very simple, and I'm a bit embarassed to didn't think before:

@Override
public Groups getByGroupid(Integer groupid, Connection con) {
    logger.debug("GroupsRepository.getByGroupid(): groupid: " + groupid);

    final String sql = (
        selectBase + "from GROUPS g " + 
        "where " + 
            "GROUPID = :groupid "
    );

    Groups res;

    try (Query query = con.createQuery(sql)) {
        query.addParameter("groupid", groupid);

        res = query.executeAndFetchFirst(Groups.class);
    }

    return res;
}

I close the issue.