?
Solved

JSP Method question

Posted on 2005-04-01
3
Medium Priority
?
269 Views
Last Modified: 2010-04-01
My JSP program is made  up of many pages im now making the method page.  My DBconnect page is included before the methods page on the main page.  It still can find the conn variable when many other pages that use it can with the same code.  What do I have to do to get it to work?


Generated servlet error:
/opt/jakarta-tomcat-5.0.27/work/Catalina/localhost/~mdmiller/org/apache/jsp/admin_jsp.java:27: cannot find symbol
symbol  : variable conn
location: class org.apache.jsp.admin_jsp.Incrementer
      Statement stmt = conn.createStatement();
                         ^
/////this is my DBconnect page///////
<%@ page import="java.sql.*" %>

<%

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = null;
      
 try{            
      conn = DriverManager.getConnection(
      "jdbc:oracle:thin:@turing:1521:CompSci","cs340a", "abcde");        
    }
    catch(SQLException se){
      conn = null;
      se.printStackTrace();
    }

%>

///////////This is my method page////////////
 <%!
    class Incrementer
    {
        String incrementer( String database )
         {
      String nextone ="1";
      Statement stmt = conn.createStatement();
      String query = "SELECT * FROM "+database+" ORDER BY To_Number(RID) DESC";
      ResultSet rs = stmt.executeQuery(query);      
      if(rs.next())
            nextone = Integer.toString(Integer.parseInt(rs.getString("RID")) + 1);
      else
            nextone ="1";
      return nextone;
         }
    }
%>



0
Comment
Question by:Millkind
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 92

Expert Comment

by:objects
ID: 13685301
You cannot access the conn variable from a seperate class.
0
 
LVL 10

Expert Comment

by:kupra1
ID: 13685815
The conn variable is not available in another jsp because it is declared in  DBconnect page and it scope ends as the control goes out of the jsp page.
>>> It still can find the conn variable when many other pages that use it can with the same code.
Because you are creating a connection every time through these codes.

To get it working, either paste the same code in all the jsps (which is bad) or creata a Database Connection utility class and create the connection there.
After that instantiate the class and get the connection from that class.

0
 
LVL 29

Accepted Solution

by:
bloodredsun earned 2000 total points
ID: 13687021
An easy way for you to not have to repeat you database connections would be to use a JavaBean.

e.g.

ConnectionBean.java code // our javabean
<code>
package com.bloodredsun.beans ;
import java.sql.*;

public class Connectionbean{

private Connection conn ;
private Statement stmt ;
private static final String driver = "" ; // your db driver
private static final String dbURL= "" ; //your db url
private static final String login= "" ; // your db login
private static final String password= "" ; //your db password


  public ConnectionBean(){
    try{
      Class.forName( driver ) ;
      conn = DriverManager.getConnection(dbURL, login, password);
      stmt = conn.createStatement() ;

    }catch (Exception e){
      System.out.print("Whoops, buggered") ;
    }
  }//end ConnectionBbean constructor

      public ResultSet executeQuery ( String pSql){
      return stmt.executeQuery( pSql ) ;
      }
}//end ConnectionBean.java
</code>

Database.jsp code
<code>
<%@ page import="java.sql.*, com.bloodredsun.beans.*" %>
<jsp:useBean id="beanconnection" class="ConnectionBean" scope="request"/>
<html>
<body>
<%
String sql = "Select * from myTable" ;
ResultSet rs = beanconnection.executeQuery( sql) ;
%>
<%-- We now have the ResultSet, do what you will --%>
<%
while ( rs.next() ){
%>
A line of db stuff <%= rs.getString("id") %>

<%
}
%>
</body>
</html>
</code>

This is bare-arsed stuff, you will have to complete it with the normal extra methods in the bean for closing the connection, and other things like that and therer is minimal exception handling (in fact I've not tested this but written it straight out, but the theory is sound). What you can see is that Javabeans allow you to COMPONENTIZE code in that the code required for creating the db connection and statement were put in the JavaBean. This can then be dropped in as many pages as you like with out the need for repeatedly setting up the connection.

Once you have got used to JavaBeans in your JSP, I would recommend then doing it in a servlet. Then, once you've done that, have a look at custom tags and you'll see why JSTL is so popular (JSTL just being a set of standardised custom tags).

My next advice would be to look at dataSources and connection pools, this has a huge performance benefit for larger projects and also takes you into the world of JNDI (which isn't a frightening as it seems!).

Good luck and happy coding.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
The Summer 2017 Scholarship Winners have been announced!
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month10 days, 11 hours left to enroll

765 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