GeeCON Prague 2014*
The moose likes Struts and the fly likes Struts:DB Connection with Oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Frameworks » Struts
Bookmark "Struts:DB Connection with Oracle" Watch "Struts:DB Connection with Oracle" New topic
Author

Struts:DB Connection with Oracle

Lalit Vora
Ranch Hand

Joined: Jun 22, 2006
Posts: 37
Hi,
While i am trying to connect to oracle DB to retrieve data. I am getting Error. Please help me if you can. I am using Tomcat5.0 Oracle 9i

in my Struts-config i have

<data-sources>
<data-source
type="org.apache.common.dbcp.BasicDataSource">
<set-property property="driverClassName"
vlaue="oracle.jdbc.driver.OracleDriver"/>

<set-property property="url"
vlaue="jdbc racle:thin:@//myhost:1521/orcl"/>

<set-property property="username"
vlaue="scott"/>
<set-property property="password"
vlaue="tiger"/>

</data-source>
</data-sources>




My Action Class is

package ch03;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionError;
import org.apache.struts.action.ActionErrors;
import org.apache.struts.action.Action;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class LookupAction extends Action
{
public Double getQuote(String symbol,
HttpServletRequest request)
throws Exception

{

Double price=null;
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
DataSource dataSource=null;

try
{

dataSource = getDataSource(request);
conn= dataSource.getConnection();
stmt= conn.createStatement();
rs= stmt.executeQuery("select * from stocks where" + "symbol='" + symbol +"'");

if (rs.next())
{
double tmp=0;
tmp = rs.getDouble("price");

price= new Double(tmp);
System.err.println("price:" +price);

}

else
{
System.err.println("Symbol not found returning null");
}

}

catch(SQLException e)
{
System.err.println(e.getMessage());
}

finally
{
if (rs!= null)
{

try
{
rs.close();
}

catch (SQLException sqle)
{
System.err.println(sqle.getMessage());
}
rs=null;
}

if (stmt!=null)
{

try{ stmt.close(); }

catch (SQLException sqle)
{
System.err.println(sqle.getMessage());
}
stmt=null;
}

if (conn!=null)
{
try{ conn.close(); }

catch (SQLException sqle)
{
System.err.println(sqle.getMessage());
}
conn=null;
}


}



return price;
}


public ActionForward execute(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response)
throws Exception{


Double price=null;
String target=new String("success");

if(form !=null)
{

LookupForm lookupForm = (LookupForm) form;

String symbol = lookupForm.getSymbol();


price = getQuote(symbol,request);

}

if(price == null)
{
target = new String("failure");
}

else
{
request.setAttribute("PRICE",price);
}

return(mapping.findForward(target));
}


}


ERROR:

HTTP Status 404: Servlet Action is not avalibale.


when i am running without datasource. I can run code obviously without connecting to DB. So there is some problem with connection code

Please help me. Also what are jar files u mentioned?

Thanks
Lalit
Merrill Higginson
Ranch Hand

Joined: Feb 15, 2005
Posts: 4864
In the latest version of Struts (1.2.9), The Struts Data Source facility is deprecated.

I'd suggest that you remove the data source definition in your struts-config.xml file. Then go to the Tomcat documentation and find the information on how to set up an Oracle data source in Tomcat. Once you have set it up, you can access it in your code with a JNDI lookup.


Merrill
Consultant, Sima Solutions
Merrill Higginson
Ranch Hand

Joined: Feb 15, 2005
Posts: 4864
Here is a link to the portion the Tomcat Version 5 documentation that tells you how to set up a JDBC Data Source. You will note that there is a section that deals specifically with Oracle.

This process is the same whether you're using Struts or not.
Lalit Vora
Ranch Hand

Joined: Jun 22, 2006
Posts: 37
Wat other changes are required to be done in Action class?

Please see my action class

public class LookupAction extends Action
{
public Double getQuote(String symbol,
HttpServletRequest request)
throws Exception

{

Double price=null;
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
DataSource dataSource=null;

try
{

dataSource = getDataSource(request);
conn= dataSource.getConnection();
stmt= conn.createStatement();
rs= stmt.executeQuery("select * from stocks where" + "symbol='" + symbol +"'");

if (rs.next())
{
double tmp=0;
tmp = rs.getDouble("price");

price= new Double(tmp);
System.err.println("price:" +price);

}


--------
--------
--------
Please guide me.Also i would like to know is there any other way to connect to oracle dB? I mean directly writing class as simple java classs?
Merrill Higginson
Ranch Hand

Joined: Feb 15, 2005
Posts: 4864
As I mentioned before, the Struts DataSource API is deprecated in the latest version, so you won't be able to use the getDataSource(request) method from the Action class. You will have to write your own getDataSource method, something like this:


This assumes you set up the DataSource in Tomcat according to the directions in the link I gave you, and that you named it "jdbc/myoracle". It stores the DataSource in the ServletContext object so that there is only one copy available to the entire application. It uses the JNDI object InitialContext to find the DataSource in the Acpplication Server's JNDI service.

The rest of your code will work as written, except that you will want to add a finally block in which you put code to close the connection.

A note about style: In a Model/View/Controller (MVC) application, you want to separate the functions of controller and model. Since an Action class acts as a controller, you don't want to put model logic in it. Logic to retrieve or update data in a Relational database generally belongs to the model, so it doesn't really belong in an Actoin Class. You will generally want to create other classes to do this work and then call methods on those classes from your Action class.
[ June 27, 2006: Message edited by: Merrill Higginson ]
Lalit Vora
Ranch Hand

Joined: Jun 22, 2006
Posts: 37
Hi,
Thank you very much for your support. I will try this. Can you let me know without doing this modification in tomcat or in struts config is it possible to connect to DB? I mean using normal java code if i dont want to use connection pool then. I initially wanted to try to connect to DB and retrieve something so used action class. Once this is done i will seperate logic as u suggested. If you can guide me like this code u used in method how to write in action class. Do you have connection code? which can help me.
Lalit Vora
Ranch Hand

Joined: Jun 22, 2006
Posts: 37
Sorry, I forgot to mention that i asked you this question because after some time i will have to use this with weblogic. With weblogic connection pool
Merrill Higginson
Ranch Hand

Joined: Feb 15, 2005
Posts: 4864
Here is a link that shows you how to get a connection without using a connection pool.

Using this method is strongly discouraged for a production website because:

1-It's much slower
2-It's wasteful of the Database resources
3-It requires hard-coding of things like database URLs and user Name/Password combinations


The programming in your application to use a connection pool is identical regardless of which App Server you use. It's the setting up of the connection pool that is a little different for each App Server.
Lalit Vora
Ranch Hand

Joined: Jun 22, 2006
Posts: 37
ok,

So if i am using without connection pool no need to modify anything in tomcat right? just use connection code u provided right? and struts app. will connect to DB without writing anything in struts config and tomcat right? Yes, As u said i will have to hardcode url and all
but its fine for me temporarily as i just want to check query and see result.

Thanks alot for your support
Merrill Higginson
Ranch Hand

Joined: Feb 15, 2005
Posts: 4864
That's correct. If you use the non-connection-pool method of obtaining a JDBC connection, there's no need to modify either the Tomcat configuration or the struts-config.xml file.
Lalit Vora
Ranch Hand

Joined: Jun 22, 2006
Posts: 37
Hi Merrill,

Thanks Its running now. I have a question. I want to generate 3 reports by retrieving data from database. What will be good design? where should i write query to generate report and where to write connection code? and what will be in execute method? Can you help me please? Also i want that after retrieving data from DB i dont want to show it in jsp I want to open report in Excel while from my index page someone selects criteria and click on submit button. So need to save as CSV. and that url should open when someone clicks on submit. Can you guide me? If you have code example which does this can you provide me for clear idea?

Thanks for your support
 
jQuery in Action, 2nd edition
 
subject: Struts:DB Connection with Oracle