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

JSP Method question

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.*" %>


Connection conn = null;
      conn = DriverManager.getConnection(
      "jdbc:oracle:thin:@turing:1521:CompSci","cs340a", "abcde");        
    catch(SQLException se){
      conn = null;


///////////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);      
            nextone = Integer.toString(Integer.parseInt(rs.getString("RID")) + 1);
            nextone ="1";
      return nextone;

1 Solution
You cannot access the conn variable from a seperate class.
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.

An easy way for you to not have to repeat you database connections would be to use a JavaBean.


ConnectionBean.java code // our javabean
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(){
      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

Database.jsp code
<%@ page import="java.sql.*, com.bloodredsun.beans.*" %>
<jsp:useBean id="beanconnection" class="ConnectionBean" scope="request"/>
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") %>


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.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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