0

I am new to java, I have a doubt:

I am retunging con from this method:

package mypackage;
public class DBconnection {
 Connection con = null;

public Connection getConnection() throws Exception, SQLException
{
    try
    {
         Class.forName("oracle.jdbc.driver.OracleDriver");
         con=DriverManager.getConnection("jdbc:oracle:thin:@zzz:1521:zzz","zzz", "zzz");
    }
    catch(Exception e)
    {

    }
    return con;
}

public void removeConnection() throws SQLException
{
    con.close();
}

}

Now when I am calling that getConnection(); method then I am getting con , now while using prepareStatement how can I use that in my query??

I called it 

DBconnection dbconnect = new DBconnection();

 dbconnect.getConnection().prepareStatement(""); //is this the right way to write??

or 

DBconnection dbconnect = new DBconnection();
dbconnect.getConnection();

dbconnect.con.prepareStatement(""); //is this the right way to write??

after these how should i close the connection??

dbconnect.removeConnection();
Improve this question

5 Answers 5

Reset to default
2

No, this is not how you get database connection in Java EE. You should leave it to Java EE to handle connections for you, and you simply get it from Java EE. This tutorial might be a good start for you: J2EE DB Connection

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

no no i am using these in my jsp and servlet and java class. I have defined this connection in a java class and calling getConnection(); in different servlets, as i am implementing this in a java web that's why i have written j2ee
ok thnx for your reply but can you just give me few ideas how to create connection pooling, i am new to this field and i do n't have enough knowledge but i want to know how to create connection pooling to handle jdbc connection?
ya i am using tomcat server and type 4 jdbc connection with oracle 10g
Go to the end of this page: people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html, and there are some examples, you can try POJO first, but eventually it might be better to go with the resource way.
thnx if i will try with POJO then should i go with that : DBconnection dbconnect = new DBconnection(); try{ PreparedStatement ps = dbconnect.getConnection().prepareStatement("..."); try{ // Something with ps... }finally{ ps.close(); } }finally{ dbconnect.removeConnection(); }
|
1

You should use this code :

DBconnection dbconnect = new DBconnection();
Connection con = dbconnect.getConnection();
String sql = "SELECT * FROM table_name"
PreparedStatement prest = con.prepareStatement(sql);

This code is similar to your first choice.

Improve this answer

8 Comments

but while closing connection?? will i call removeConnection(); method or con.close(); ??
You can use both... Because when you will receive object of connection you will be able to call con.removeConnection() or con.close()
thnx for d reply but after con.close(); or con.removeConnection() i am still getting the value of dbconnect.getConnection(); how?? i should get the value null in out.println(dbconnect.getConnection()); but i am receing some value how?? How can i ensure that connection is being properly closed??
Yes it will return because when you call again dbconnect.getConnection() it create a new connection. Each time this method will create a new connection. You should make this connection static for whole application. And check when creating object that either it is null or not. If this is null or closed then create object otherwise not.
ok when Connection con=dbconnect.getConnection(); then the value of con is not remaining constant throughout the application. when i am refreshing the servlet i am seeing a new value of out.println(con); not that previous con value. when DBconnection dbconnect = new DBconnection(); then while checking null value for object then it showing that object(dbconnect) is not null, so suggest me how can i make this object null or closed? how can avoid this new connection each time i am refreshing?
|
0

Yes, something like this:

DBconnection dbconnect = new DBconnection();
try{
    PreparedStatement ps = dbconnect.getConnection().prepareStatement("...");
    try{
      // Something with ps...
    }finally{
      ps.close();
    }
}finally{
    dbconnect.removeConnection();
}
Improve this answer

4 Comments

thnx for d reply but is it closing the connection properly? I mean if i am writing dbconnect.removeConnection(); after one PreparedStatement ps = dbconnect.getConnection().prepareStatement("first sql query"); then when i am using another dbconnect.getConnection().prepareStatement("second sql query"); then how second sql query is being executed while i have already closed my connection after first sql query??
Run all of your code within the block already containing the one PreparedStatement. If you need to be reusing the DBconnection elsewhere, you're best off to modify the DBconnection class to have a method that always returns a new connection (with no need to save it to an instance variable), and have the removeConnection method accept the connection to be removed - though this method is typically called "releaseConnection" by convention.
i declared another getConnection1() method and also another removeConnection1() and PreparedStatement ps = dbconnect.getConnection1().prepareStatement("second sql query"); and to close dbconnect.removeConnection1();// is this the way you were telling??
No, please don't do something like that. You're really best off following PeterPeiGuo's advice instead, and using the J2EE standards for connection pooling.
0 
private static Connection con = null;

public Connection getConnection(){
    if(con == null){
        try
        {
             Class.forName("oracle.jdbc.driver.OracleDriver");
             con=(Connection) DriverManager.getConnection("jdbc:oracle:thin:@zzz:1521:zzz","zzz", "zzz");
        }
        catch(Exception e)
        {

        }
    }
    return con;
}
Improve this answer

1 Comment

thanks i used it but again one error is coming on servlet saying that con has private access in mypackage.DBconnection, how to resolve this? If i want to use it in any jsp page then con variable is not coming from that object dbconnect. how can i resolve these two?
0

The best way to manage database connection is to allow your Java EE container to manage your database connection pool. This ensures that idle connections are disconnected and reused for other incoming connection requests.

However if you prefer to use your own Database Manager class for maintaining connections then it would be better to ensure that connections are closed in the finally block, this will ensure that there are no active connections due to exception thrown in the code block.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.