• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6348
  • Last Modified:

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

0
Adwait Chitaley
Asked:
Adwait Chitaley
  • 9
  • 8
1 Solution
 
CEHJCommented:
You should configure it to be obtainable using JNDI:

http://www.microdeveloper.com/html/JNDI_Orcl_Tomcat1p.html
0
 
objectsCommented:
what version of struts?

0
 
objectsCommented:
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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Adwait ChitaleyAuthor Commented:
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;
            }
0
 
objectsCommented:
you don't need to use JNDI, you can access the struts datasource directly from the struts config (regardless of where it is specified).

0
 
Adwait ChitaleyAuthor Commented:
can u pls givemme detail of strutconfig  connection pooling for oracle database
0
 
objectsCommented:
you're already using a connection pool.
I thought your problem was how to access the struts data source from your action

0
 
Adwait ChitaleyAuthor Commented:
no my prbm is how to configure data source in struts-config.xml  and then how to get datasource for oracle in action class
0
 
objectsCommented:
I already posted how to get your datasource in my first comment, have you tried that?

0
 
Adwait ChitaleyAuthor Commented:
i jus tried ur posted code ,so what wl be   DATA_SOURCE_KEY value;
0
 
objectsCommented:
Thats a static variable in the Action class, just use the code as posted
0
 
Adwait ChitaleyAuthor Commented:
i`m using strut 1.2.9 and Action.DATA_SOURCE_KEY  is deprecated  any solution for that
0
 
objectsCommented:
Globals.DATA_SOURCE_KEY
0
 
Adwait ChitaleyAuthor Commented:

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>
0
 
objectsCommented:
should that be:




0
 
Adwait ChitaleyAuthor Commented:
thanks ...Its been great help, my problem is now resolved
0
 
objectsCommented:
great, can you accept my comment and close the question please.

0
 
Adwait ChitaleyAuthor Commented:
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;
    }  
   
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now