Solved

Method of making Connection to database

Posted on 2003-12-03
13
449 Views
Last Modified: 2010-04-01
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.            
0
Comment
Question by:thomas908
  • 4
  • 3
  • 2
  • +4
13 Comments
 
LVL 35

Accepted Solution

by:
TimYates earned 40 total points
ID: 9865563
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
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 9865750
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
 
LVL 4

Expert Comment

by:orhanbaba
ID: 9865796
create a url string and include it every page.
you can change it when you want
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 15

Assisted Solution

by:jimmack
jimmack earned 40 total points
ID: 9865810
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
 
LVL 6

Expert Comment

by:jarasa
ID: 9867410
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
 
LVL 14

Assisted Solution

by:kennethxu
kennethxu earned 45 total points
ID: 9867480
>> 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
 
LVL 35

Expert Comment

by:TimYates
ID: 9867571
Hehe ;-)  

I was so close ;-)
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9867657
you was so close that I think yours should be accepted as answer ;-)
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9867664
;-)  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
 
LVL 15

Expert Comment

by:jimmack
ID: 9867701
;-)
0
 
LVL 8

Author Comment

by:thomas908
ID: 9872162
Thanks everyone. I am finding it really hard to distribute points. All are gr8 suggestions.
;)
0
 
LVL 15

Expert Comment

by:jimmack
ID: 9872938
;-)
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9873129
:-) Good luck Thomas :-)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
There's a lot of hype surrounding blockchain technology. Here's how it works and some of the novel ways it' s now being used - including for data protection.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question