Design on connecting to db
Hi,
I am using SUN ONE application server as my web server and connection pooling has been configured.
I am wondering what is the best design to connect to database ( consider about the memory). From my reseach over the Internet. I saw a lot of ppls are using lookup of JNDI and storing the connection in datasource in the main entrance. Then they pass the datasource to different class which need connection. Some of them do the lookup JNDI and store in data source when each single sql is called
My concern is passing datasource to different class does it taking lots memory? What about each single sql and do the lookup n store in datasource does it consuming quite lots of time?
Any other better way on the design? Any idea is appreciate!
Thanks
# 1
Generally the jndi lookup and the datasource creation take place at application startup. A single datasource is then created an used by all the other classes that need db calls.To ensure that you have only one datasource instance, use singleton pattern.
# 2
Hi, thanks for your promptly reply.
However I am not really sure the singleton pattern. As I search thru the net, singleton pattern means a class has only one instance, and provide global point of access to it. However I am not really sure how to implemention looked like. Following is one of my example and please correct me if i am wrong.
package com.inav.sample.db;
import java.sql.Connection;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class ConnectionManager {
private static DataSource ds = null;
private String _sJndi = "";
public ConnectionManager(String s) throws Exception {
_sJndi = s;
}
public Connection getConnection() {
Connection conn = null;
try {
Context ctx = new InitialContext();
ds = (javax.sql.DataSource)ctx.lookup("jdbc/" + _sJndi);
conn = ds.getConnection();
} catch (Exception e) {
e.printStackTrace(System.err);
}
return conn;
}
public DataSource getDataSource(){
return ds;
}
}
public class charge extends HttpServlet {
public void init(ServletConfig config) throws ServletException {
super.init(config);
_sJndi = res.getString("JNDI");
}
private void Process(HttpServletRequest request,
HttpServletResponse response) throws IOException {
ConnectionManager ConnMng = null;
try {
ConnMng = new ConnectionManager(_sJndi);
} catch (Exception e) {
e.printStackTrace();
}
String a = "test";
//pass the ConnectionManager to other class
sample(ConnMng,s)
}
# 3
Here a possible implementation :
import java.sql.Connection;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class ConnectionManager {
private static DataSource ds = null;
private static ConnectionManager instance = null;
private ConnectionManager(String sJndi){
createDataSource(sJndi);
}
public static synchronized ConnectionManager getInstance(String sJndi){
return (instance!=null)?instance:new ConnectionManager(sJndi);
}
private DataSource createDataSource(String sJndi){
DataSource ds = null;
try {
Context ctx = new InitialContext();
ds = (javax.sql.DataSource) ctx.lookup("jdbc/" + sJndi);
} catch (Exception e) {
e.printStackTrace();
System.exit(-1);
}
return ds;
}
public static Connection getConnection() throws Exception{
return ds.getConnection();
}
}
At the startup of you application, initialize the datasource like the following:
ConnectionManager.getInstance("YOUR_JNDI_HERE");
every time you need an sql connection, you have just to do this:
ConnectionManager.getConnection();
For example, in the charge servlet class, you'll need only this to get a connection.
Hope That Helps
# 4
Hi, really appreciated with the implementation code.
I understand normal class start up is in main function. However in servlet, the startup is meant in the init or i can put out side of the init ( doPost/doGet )?
What about there are few servlet involve, means ConnectionManager.getInstance("YOUR_JNDI_HERE");
is appear in every servlet?
Please advice.
Thanks.
# 5
MVC framework project (Struts, JSF or Spring, ...)
The call of getInstance method can be done in the init method of the servlet dispatcher.
Basic JSP/Servlet project
create a servlet called InitServlet for example and call the getInstance method in the init method (the servlet init method without arguments for instance), then, add this servlet to your web.xml.
web.xml
<servlet>
<servlet-name>InitServlet</servlet-name>
<servlet-class>com.company.package.InitServlet</servlet-class>
<init-param>
<param-name>jndi.name</param-name>
<param-value>YOUR_JNDI_NAME_HERE</param-name>
</init-param>
<load-on-startup>2</load-on-startup>
</servlet>
InitServlet.java
import javax.servlet.*;
import javax.servlet.http.*;
public class InitServlet extends HttpServlet {
public void init(){
String jndiName = getInitParameter("jndi.name");
ConnectionManager.getInstance(jndiName );
}
}
NB:
you don't need to add the init servlet mapping to the web.xml file
# 6
notice the initservlet u created is start automatically(which u set in web.xml)Means the rest of my servlet dun need to include ConnectionManagetr.getInstace(jndi) rite?Please advice.Thanks
# 7
I think I get what you meants..I hv tested and it is working perfectly.Thanks a lot for your help.
# 8
Yes, you don't need to call ConnectionManagetr.getInstace(jndi) in the rest of your code.>Thanks a lot for your help.You are welcome, but, of course you forgot the dukes
