How to get up on the correct data retrieval from the database?

Question

PostgreSQL

To obtain information from the database in 90% of cases, the following scheme of the method is used:

public static String getAnything() throws SQLException, FrameworkException {

        OraclePreparedStatement preparedStatement = null;
        OracleConnection connection = (OracleConnection) TransactionScope.getConnection();
        ResultSet resultSet = null;

        try {
            String sql = ""; // SQL запрос

            preparedStatement = (OraclePreparedStatement)connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();

            String anything = "";

            while (resultSet.next()){ 
                anything = resultSet.getString(1); // Получаем нужные данные
            }

            return anything;

        } catch (Exception e) {
            e.printStackTrace();
            return "";
        } finally {
            if (resultSet != null)
                resultSet.close();
            if (preparedStatement != null)
                preparedStatement.close();
            if (connection != null)
                TransactionScope.releaseConnection(connection);
        }
    }

It is very disturbing that the company's Java classes consist almost entirely of a set of methods, the framework of which is given above. That is, copy-paste the code above, changing the SQL query text and method name, and sometimes the type of data returned.
But that's not all! The huge text of the SQL query is inserted directly into the body of the method into which the variables are bound. Reading and maintaining such code is not convenient.

How to get out of this situation (remove duplicate methods, hide SQL texts from Java code)?

So far, I see two ways out:

1. Writing several methods (for a different type of returned data) of the form:

getString(String sql, String...binds) {  // binds - переменные, которые подставятся в SQL запрос
}

getArray(String sql, String...binds) {
}

2. Active study of Hibernate, and its promotion.

There is 1 Java developer in the company so far - I (Junior position), I have very little experience. There is a lot of code.

Thank you!

Answer 1

You can rewrite on Spring JdbcTemplate
a rather convenient thing (and it will be easy for you to switch to it)
(in the latest version, you can make specific parameters instead of "?", which is convenient)
SQL can be taken out to a file, but it is often more convenient when they are in the code. Look at the situation.
From the point of view of further frequent refactoring of the base, it is better to use JPA (ala Hibernate),
but it is not always easy to write the desired query there.