asked on

connection pooling in struts using oracle DataSource

Hi,

I configure data source in struts-config.xml using oracle JDBC driver my prbm is that,i want to use DataSource in my action class , so i did like this
OracleDataSource ds=new OracleDataSource() and try to get connection by using
Connection con=ds.getConnection() but it return null
after that i tried this way
ds.setUrl("jdbc:oracle:thin:@localhost:1521:ORCL")
ds.setUser("system");
ds.setPassword("system");
and get connection using ds.getConnection()
this time it work fine but i want to use connection pooling from struts-config.xml file

<data-source type="oracle.jdbc.pool.OracleDataSource" key="dataSourceOracle">
            <set-property  property="autoCommit"       value="false"/>
            <set-property    property="description"    value="Configuration"/>
            <set-property    property="driverClass"  value="oracle.jdbc.driver.OracleDriver"/>
            <set-property property="factory" value="oracle.jdbc.pool.OracleDataSourceFactory" />
            <set-property   property="maxCount"        value="4"/>
            <set-property   property="minCount"         value="2"/>
            <set-property   property="password"      value="system"/>
            <set-property   property="URL"  value="jdbc:oracle:thin:@localhost:1521:ORCL"/>
           
            <set-property    property="user"        value="system"/>
        </data-source>
 
 
 
 
 
import org.apache.struts.action.*;
import javax.servlet.http.*;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Connection;
public class Check extends Action{
 
    public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
        ResultSet rs =null;
        DBConnection dbcon=new DBConnection();
           PreparedStatement stmt=null;
           Connection con=null;
           try{
               System.out.println("##################Connection Successfull");
               rs=dbcon.executeQuery("SELECT SYSDATE FROM DUAL");
           }catch(Exception e){
                e.printStackTrace();
           }finally{
                rs.close();
                dbcon.closeConnection();
            }
        return mapping.findForward("success");
 
    }
 
}
 
 
 
 
 
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Connection;
import oracle.jdbc.pool.OracleDataSource;
public class DBConnection {
    private OracleDataSource ds;
    private Connection con;
    private ResultSet rs;
    private PreparedStatement stmt;
    
    public DBConnection() {
        ds=null;
        con=null;
        rs=null;
        stmt=null;
    }
    public Connection getDBConnection(){
        try{
            ds=new OracleDataSource();
            ds.setURL("jdbc:oracle:thin:@localhost:1521:ORCL");
            ds.setUser("system");
            ds.setPassword("system");
            con=ds.getConnection();
        }catch(Exception sqle){
            System.err.print(sqle);
        }finally{
            return con;
        }
    }
    public void closeConnection(){
        try{
            con.close();
            rs.close();
        }catch(Exception e){
            System.out.println("Connection Close");
        }finally{
            
        }
    }
    public ResultSet executeQuery(String sql){
        try{
            stmt=getDBConnection().prepareStatement(sql);
            rs=stmt.executeQuery();
        }catch(Exception e){
            System.err.print(e);
        }finally{
            return rs;
        }
    }
 
}

Open in new window

CEHJ

You should configure it to be obtainable using JNDI:

http://www.microdeveloper.com/html/JNDI_Orcl_Tomcat1p.html

Mick Barry

what version of struts?

Mick Barry

We don't use struts anymore but it used to be in the application context
http://helpdesk.objects.com.au/java/how-to-access-struts-datasource

Adwait Chitaley

ASKER

Hi

Thank for solution..but still prbm is not resolve ,i used JNDI for connection pooling

http://www.microdeveloper.com/html/JNDI_Orcl_Tomcat1p.html

as guided by this link .. but i`m getting following error which says that name java: is not bound in this context

javax.naming.NameNotFoundException: Name java: is not bound in this Context
at org.apache.naming.NamingContext.lookup(NamingContext.java:769)
at org.apache.naming.NamingContext.lookup(NamingContext.java:152)
at javax.naming.InitialContext.lookup(InitialContext.java:351)
at com.vaannila.DBConnection.getDBConnectionJNDI(DBConnection.java:81)
at com.vaannila.DBConnection.executeQuery(DBConnection.java:64)
at com.vaannila.ActorData.loadDataDB(ActorData.java:76)
at com.vaannila.UserAction.execute(UserAction.java:53)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:390)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:630)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302)
at org.apache.jasper.runtime.PageContextImpl.doForward(PageContextImpl.java:696)
at org.apache.jasper.runtime.PageContextImpl.forward(PageContextImpl.java:667)
at org.apache.jsp.index_jsp._jspService(index_jsp.java:57)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:390)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:595)
java.lang.NullPointerExceptionConnection Close

my database connection code

public Connection getDBConnectionJNDI(){

System.out.println("Inside JNDI::::::::");
try {
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
OracleDataSource ds1 = (OracleDataSource) envContext.lookup("jdbc/db1");
if (envContext == null) throw new Exception("Error: No Context");
if (ds1 == null) throw new Exception("Error: No DataSource");
if (ds1 != null) con = ds1.getConnection();

/*if (con != null) {
message = "Got Connection " + con.toString() + ", ";
stmt = con.prepareStatement(URL);
//rst = stmt.executeQuery("SELECT 'Success obtaining connection' FROM DUAL");
}*/

}catch(Exception e){
e.printStackTrace();
}finally{
return con;
}

Mick Barry

you don't need to use JNDI, you can access the struts datasource directly from the struts config (regardless of where it is specified).

Adwait Chitaley

ASKER

can u pls givemme detail of strutconfig connection pooling for oracle database

Mick Barry

you're already using a connection pool.
I thought your problem was how to access the struts data source from your action

Adwait Chitaley

ASKER

no my prbm is how to configure data source in struts-config.xml and then how to get datasource for oracle in action class

Mick Barry

I already posted how to get your datasource in my first comment, have you tried that?

Adwait Chitaley

ASKER

i jus tried ur posted code ,so what wl be DATA_SOURCE_KEY value;

Mick Barry

Thats a static variable in the Action class, just use the code as posted

Adwait Chitaley

ASKER

i`m using strut 1.2.9 and Action.DATA_SOURCE_KEY is deprecated any solution for that

Mick Barry

Globals.DATA_SOURCE_KEY

Adwait Chitaley

ASKER

java.sql.SQLException: Invalid Oracle URL specified: OracleDataSource.makeURLConnection Close

my strut-config.xml file

<data-sources>
<data-source type="oracle.jdbc.pool.OracleDataSource">
<set-property property="driverClass" value="oracle.jdbc.OracleDriver" />
<set-property property="url" value="jdbc:oracle:thin:@localhost:1521:ORCL" />
<set-property property="maxCount" value="5" />
<set-property property="minCount" value="1" />
<set-property property="autoCommit" value="false" />
<set-property property="user" value="sandeep" />
<set-property property="password" value="kolhe" />
</data-source>
</data-sources>

Mick Barry

should that be:

Adwait Chitaley

ASKER

thanks ...Its been great help, my problem is now resolved

ASKER CERTIFIED SOLUTION

Mick Barry

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Adwait Chitaley

ASKER

hi sorry to trouble u again.....i`m facing some problem in connection pooling
dsource.getConnection() returns null, ..behavior is inconsistence some time i get connection object some time null can u pls help me regarding asap

java.lang.NullPointerException

datasource connection return nulll

<data-sources>
<data-source type="oracle.jdbc.pool.OracleDataSource">
<set-property property="autoCommit" value="false"/>
<set-property property="description" value="Configuration"/>
<set-property property="driverClass" value="oracle.jdbc.OracleDriver"/>
<set-property property="factory" value="org.apache.commons.dbcp.BasicDataSourceFactory" />
<set-property property="URL" value="jdbc:oracle:thin:@128.127.50.122:1521:airsanchay"/>
<set-property property="user" value="airuser"/>
<set-property property="password" value="airsample"/>
<set-property property="maxCount" value="20"/>
<set-property property="minCount" value="2"/>
</data-source>
</data-sources>

ServletContext application=servlet.getServletContext();
DataSource datasource = (DataSource) application.getAttribute(Globals.DATA_SOURCE_KEY);
ConnectionPool.dsource=datasource;
return mapping.findForward(SUCCESS);

public static Connection openConnection() throws Exception{
Connection con = null;
con = dsource.getConnection();

message = "Successful Connection.";
System.out.println("-----conOpenedCount---->>"+(++conOpenedCount));
return con;
}