CODEWITHSUNDEEP
javamysqldatabase-connection
user7482873
user7482873

Reputation:

When doing multiple SQL queries should I try to use same connection?

So sometimes in my Java application I need to send multiple queries to my MySQL database in the same method.

I am using connection pooling in my application:

private static final BasicDataSource dataSource = new BasicDataSource();

    static {
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://--------:---/agenda?useSSL=true");
        dataSource.setUsername("----");
        dataSource.setPassword("----");
    }

I have a method in my Database class:

public static Employee getEmployee(int id) {
        Employee employee = null;
        try { String query = "SELECT * FROM entity WHERE entityId = " + id + " LIMIT 0, 1;";
            Connection con = dataSource.getConnection();
            java.sql.Statement indexStmt = con.createStatement();
            ResultSet indexRs = indexStmt.executeQuery(query);

            while (indexRs.next()) {
                employee = new Employee(indexRs.getInt(1),
                        indexRs.getString(3), indexRs.getString(4),
                        indexRs.getString(5), indexRs.getString(6));
            }
            indexStmt.close();
            indexRs.close();
            con.close();
        } catch (SQLException e) {e.printStackTrace();}
        return employee;    
    }

Should I take the time to try to use the same Connection con = DataBase.getSource() Or is it fine to just get a new connection from the pool even if I am doing something like this?

init() {

    Employee employee1= DataBase.getEmployee(11);
    Employee employee2 = DataBase.getEmployee(12);
}

I could implement something that these two lines of code would use the same connection but would that be wise or unnecessary?

//side note the employee id is never inputed from the user so SQL injection is not possible there.

Upvotes: 1

Views: 909

Answers (1)

Ralf Kleberhoff
Ralf Kleberhoff

Reputation: 7290

That would mean running your own connection pooling on top of the existing connection pooling. To make these two layers interact cleanly will not be easy.

But you'd probably get a performance improvement by passing an opened Connection into your getEmployee() method, so that you can acquire your connection outside, use it for multiple consecutive calls, and then close it. But I can't tell how much performance difference it makes, and it surely makes your code less elegant, compared to the current architecture.

Upvotes: 1

Related Questions