2
\$\begingroup\$

I'm trying to increase the speed of my application at the moment. My inserting/updating methods were opening every time a new connection which took too much time. I read that it's better to create a connection object and pass it to different methods. It worked fine but I'm just curious if this is a good practice.

This is a part of my code I wrote:

public class BusinessRuleUpdaterDaoImplV2 extends OracleBaseDao implements BusinessRuleUpdaterDaoV2 {

    @Override
    public boolean updateBusinessRule(BusinessRule businessRule) {
        try(Connection con = super.getConnectionConfigDb()) {
            con.setAutoCommit(false);
            if (updateTableBusinessRule(con,businessRule)){
                return true;
            } else {
                con.rollback();
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    private boolean updateTableBusinessRule(Connection con, BusinessRule businessRule){
        String queryBr = "UPDATE BUSINESSRULE SET ERRORMESSAGE = ?, SQLCODE = ?, CUSTOMNAME = ?, OPERATOR_ID = ? WHERE BUSINESSRULE_ID = ?";
        try(PreparedStatement pstmtBr = con.prepareStatement(queryBr)) {
            pstmtBr.setString(1,businessRule.getErrorMessage());
            pstmtBr.setString(2,businessRule.getSqlQuery());
            pstmtBr.setString(3,businessRule.getName());
            pstmtBr.setInt(4,businessRule.getOperatorID());
            pstmtBr.setInt(5,businessRule.getBusinessRuleID());
            pstmtBr.executeUpdate();
            pstmtBr.close();
            return updateTableTargetTable(con,businessRule);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    private boolean updateTableTargetTable(Connection con, BusinessRule businessRule){
        String queryTt = "UPDATE TARGETTABLE SET NAME = ? WHERE TABLE_ID = ?";
        for (int i = 0 ; i < businessRule.getTableListSize() ; i++) {
            try (PreparedStatement pstmtTt = con.prepareStatement(queryTt)) {
                pstmtTt.setString(1,businessRule.getListOfTables().get(i).getName());
                pstmtTt.setInt(1, businessRule.getListOfTables().get(i).getId());
                pstmtTt.executeUpdate();
                pstmtTt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }

        return false;
    }

    private boolean updateTableAttribute(Connection con, BusinessRule businessRule){
        // return updateTableValue(con, businessRule)
        return false;
    }

    private boolean updateTableValue(Connection con, BusinessRule businessRule){
        //return updateTableStack(con, businessRule)
        return false;
    }

    private boolean updateTableStack(Connection con, BusinessRule businessRule){
        return false;
    }
}

As you can see in the first method, updateBusinessRule(), I'm opening a connection and passing it to the next method. If one of the methods returns false, it will make a roll back in the first method.

I'm also curious about why intellij is telling me that pstmtTt.close() is redundant.

\$\endgroup\$
1
\$\begingroup\$

I think it is ok to re-use the Connection.

I would not try-catch all the SqlExceptions in the update* methods. Just let them throw. Then you don't need all the boolean logic either. Makes it simpler I guess.

\$\endgroup\$
0
\$\begingroup\$

If your application constantly/periodically makes DB queries it is logical to keep/store a DAO object as a singleton that connects to the service once (possibly on application startup). This way you are not going to lose time waiting until the connection appears every time

\$\endgroup\$
-1
\$\begingroup\$

You can use a connection pool like HikariCP or c3p0; it reduces the time for new connection setup.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.