Method of making Connection to database

Hi experts
i am using beans with my JSP and making database connection in beans.
/***********************************/
connectionURL="jdbc:mysql://localhost:3306/dbname?user=;password=";
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
con = DriverManager.getConnection(connectionURL, "", "");
/********************************/
I am doing this in every bean.  The problem is if in future I need to make some change in the connectionURL I'll need to do in every page.
Is this the right way to do it?
Is this a good idea that connection is made in every page or is there a way that conenction is made once and not in every bean.
In short whats the best way to make a connection.            
LVL 8
thomas908Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimYatesCommented:
Write a bean which does it for you...

----------------------------

import java.sql.* ;

public class ConnectionManager
{
    public Connection getConnection()
    {
        Class.forName("org.gjt.mm.mysql.Driver").newInstance();
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname?user=;password=", "", "");
    }
}

----------------------------

Save this as ConnectionManager.java

compile it with:

javac ConnectionManager.java

then copy ConnectionManager.class to WEB-INF/classes

Then, in your JSP, you should be able to do:

    conn = ConnectionManager.getConnection() ;

Tim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KuldeepchaturvediCommented:
Now if you don't want to make and drop connection on every page than put this bean on session..
and get it back on every page......
0
orhanbabaCommented:
create a url string and include it every page.
you can change it when you want
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

jimmackCommented:
You should really use a connection pool.

This will open a number of connections to start with and new ones as you require them (up to a maximum that you specify).  Then, instead of opening connections in your beans/servlets etc., you request one from the pool and give it back when you have finished with it.

http://archive.coreservlets.com/coreservlets/ConnectionPool.java
0
jarasaCommented:
I agree with jimmack, besides if you are using an aplication server, normally the already have a connection pools which you can configure from administration.
0
kennethxuCommented:
>> than put this bean on session.. and get it back on every page......
NEVER do this! connection object is not thread safe so CANNOT be shared by multiple request.

>> You should really use a connection pool.
That's what I'd say. J2EE compliant servlet container must have build in connection pool, you should be using JNDI DataSource according to spec.

You can start with what Tim suggested, but make the method static. in future, you can change this method to use connection pool:

import javax.sql.DataSource;
import java.sql.Connection;
import javax.naming.InitialContext;
public class ConnectionManager
{
    private DataSource source =  null;
    public static Connection getConnection()
        throws java.sql.SQLException, javax.naming.NamingException
    {
        if( source == null ) {
            InitialContext jndiContext = new InitialContext();
            source = (DataSource)jndiContext.lookup("java:comp/env/MyDBName" );
        }
        return source == null ? null; source.getConnection();
    }
}
0
TimYatesCommented:
Hehe ;-)  

I was so close ;-)
0
kennethxuCommented:
you was so close that I think yours should be accepted as answer ;-)
0
TimYatesCommented:
;-)  hee hee...

I reckon it should be a split...  jimmack is right about the connection pool, you are right about it being a static method, and I'm right about farming it off to a bean :-)
0
jimmackCommented:
;-)
0
thomas908Author Commented:
Thanks everyone. I am finding it really hard to distribute points. All are gr8 suggestions.
;)
0
jimmackCommented:
;-)
0
TimYatesCommented:
:-) Good luck Thomas :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.