CODEWITHSUNDEEP
javasqljsp
user1665700
user1665700

Reputation: 512

Should I keep opening and closing connections in JSP

I created a Class called MySQL.

This Class contains only 1 method, which connects me to my database.

public static void Connect() {
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException ex) {
        System.out.println(ex.getMessage());
    }
    try {

        connection = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/"+database,username,password);

    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
}

So now, when I need to connect inside a JSP page, I write this.

<% MySQL.Connect();
   Statement stmt = MySQL.getConnection().createStatement();
   ResultSet rset = stmt.executeQuery("....");
   //Some codes here
   //Then I close both rset and stmt
   rset.close();
   stmt.close();
%>

Should I close the connection as well? note that there will be more queries executed when the page is loaded, should I work on 1 connection for all queries or 1 connection for each query and then close it?

Upvotes: 0

Views: 3220

Answers (2)

sunleo
sunleo

Reputation: 10943

1, getting connection directly in jsp is not good idea.

2, No need to create sepereate connections for each queries to be executed in a same page.also that is based on operations you are doing with that connection.

3, where ever you open connection, connection must be closed.otherwise leakage will be problem.

4, Before closing connections make sure they are not null.So nullity check is important otherwise you may get nullpointer exception.

5, Close connections in finally block so when object is to be garbage collected there no more operation will happen based on the connection. Do the closing connection work in finally block. *

Upvotes: 0

Stephen Connolly
Stephen Connolly

Reputation: 14096

Ideally you would just use the one connection for the duration of rendering the page and then close it at the end of the page.

That has the effect of ensuring that once you start rendering the page, you can complete as you don't loose a connection half way through due to exhausting the connection pool (and you should be using a connection pool rather than establishing connections directly if you value your page performance.

Upvotes: 1

Related Questions