Simple JSP & Database Connection

Hi Everyone;

I'm creating a class for my JSP so that I could display a record from my database :

/*
 * Records.java
 *
 * Created on November 11, 2004, 11:49 AM
 */
import java.sql.*;

/**
 *
 * @author  Rajah
 */
public class Records {
   
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;
    private String query;

    /** Creates a new instance of Records */
     
    public Records() {
    }
   
    public String getAllData(){
       
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dburl = "jdbc:odbc:Records";
            conn = DriverManager.getConnection(dburl);
            stmt = conn.createStatement();
            query = "Select * from Employees ";
            rs = stmt.executeQuery(query);
            /**
            while(rs.next()){
            // get the data from the table.
            }  **/        
        }
        catch (ClassNotFoundException x) {
            System.err.println( "Failed to load JDBC/ODBC driver." );
            x.printStackTrace();
        }
        catch(SQLException y){
            y.printStackTrace();
        }
    }
}

However I have an error saying :

C:\Rajah\Java\WebApplication1\src\Records.java:44: missing return statement
    }
1 error

What should I do ? Thanks.

Rajah
rajah_mohammedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodingExpertsCommented:
>> public String getAllData(){

This function should return a String value which it doesnot do. So either make it return a String or make it

public void getAllData() {

<CE>
0
gdrnecCommented:
Not to intrude but but to clarify.

You need to return something from your method. You declare a returned String but your method does not return anything.

Chances are you are going to return more than just a string but a collextion of something:
            ArrayList list = new ArrayList()
            while(rs.next()){
            // get the data from the table.
                   list.add(rs.getObject(x));
            }  
            //other stuff
            return list; // which would make your method return a Collection

or even:

            ArrayList list = new ArrayList()
            HashMap row = null;
            while(rs.next()){
            // get the data from the table.
                   map = new HashMap();
                   map.put("col1Name", rs.get("colName"));
                   map.put("col2Name", rs.get("col2Name"));
                   list.add(map);
            }  
            //other stuff
            return list; // which would make your method return a Collection

This is obviously an implementation question at this point.

Geoff
0
gdrnecCommented:
If you just want it to compile try:

/*
 * Records.java
 *
 * Created on November 11, 2004, 11:49 AM
 */
import java.sql.*;

/**
 *
 * @author  Rajah
 */
public class Records {
   
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;
    private String query;

    /** Creates a new instance of Records */
     
    public Records() {
    }
   
    public String getAllData(){
       
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dburl = "jdbc:odbc:Records";
            conn = DriverManager.getConnection(dburl);
            stmt = conn.createStatement();
            query = "Select * from Employees ";
            rs = stmt.executeQuery(query);
            /**
            while(rs.next()){
            // get the data from the table.
            }  **/        
        }
        catch (ClassNotFoundException x) {
            System.err.println( "Failed to load JDBC/ODBC driver." );
            x.printStackTrace();
        }
        catch(SQLException y){
            y.printStackTrace();
        }
    }
    return null;
}
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

rajah_mohammedAuthor Commented:
Okey thanks for all the inputs.

I have a jsp :

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<html>
    <head><title>JSP Page</title></head>
    <body>

        <%-- <jsp:useBean id="beanInstanceName" scope="session" class="beanPackage.BeanClassName" /> --%>
        <%-- <jsp:getProperty name="beanInstanceName"  property="propertyName" /> --%>
       
        <% Records tc = new Records();
        String str= tc.getAllData();  
        out.println("Data = " + str);
        %>

    </body>
</html>

This is my Java Class :

/*
 * Records.java
 *
 * Created on November 11, 2004, 11:49 AM
 */
import java.sql.*;
import javax.swing.*;
/**
 *
 * @author  Rajah
 */
public class Records {
   
    /** Creates a new instance of Records */
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;
    private String query;
     
    public Records() {
    }
   
    public void getAllData(){
       
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dburl = "jdbc:odbc:Records";
            conn = DriverManager.getConnection(dburl);
            stmt = conn.createStatement();
           
            query = "Select * from Employees ";
            rs = stmt.executeQuery(query);
            /**
            while(rs.next()){
            // get the data from the table.
            }  **/        
        }
        catch (ClassNotFoundException x) {
            System.err.println( "Failed to load JDBC/ODBC driver." );
            x.printStackTrace();
        }
        catch(SQLException y){
            y.printStackTrace();
        }
    }
}

All I wanna do is for my Records class to retrieve the records and display it in my jsp.
However I'm getting an error when I try running my jsp, netbeans is saying :

C:\Rajah\Java\WebApplication1\build\generated\src\org\apache\jsp\records_jsp.java:54: cannot find symbol
symbol  : class Records
location: class org.apache.jsp.records_jsp
 Records tc = new Records();
 ^
C:\Rajah\Java\WebApplication1\build\generated\src\org\apache\jsp\records_jsp.java:54: cannot find symbol
symbol  : class Records
location: class org.apache.jsp.records_jsp
 Records tc = new Records();
                  ^
2 errors

What do I do ?
0
gdrnecCommented:
I usually don't use net beans to debug my jsps. I tend to deploy them and let Tomcat tell me the errors. Chances are you have a NetBeans config problem. Not sure what though. Looks like it cant find your class Records.
0
rajah_mohammedAuthor Commented:
My source files and my class is in the folder :
C:\Rajah\Java\WebApplication1\src

If I need to import the class how would I do that in my jsp above ?
0
CodingExpertsCommented:
add
       <jsp:useBean id="rec" scope="session" class="Records" />

and then see
 
0
CodingExpertsCommented:
then use rec as a record object which will be is session
then u do
 String str= rec.getAllData();  
0
rajah_mohammedAuthor Commented:
CodingExperts;

Thanks for that tip. However I'm getting a new error. Here's my codes :

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>

<html>
    <head><title>JSP Page</title></head>
    <body>
   
        <jsp:useBean id="rec" scope="session" class="Records" />
       
        <% Records rec = new Records();
        String str= rec.getAllData();  
        out.println("Data = " + str);
        %>

    </body>
</html>

Here's the error :

C:\Rajah\Java\WebApplication1\build\generated\src\org\apache\jsp\records_jsp.java:50: cannot find symbol
symbol  : class Records
location: class org.apache.jsp.records_jsp
      Records rec = null;
      ^
C:\Rajah\Java\WebApplication1\build\generated\src\org\apache\jsp\records_jsp.java:52: cannot find symbol
symbol  : class Records
location: class org.apache.jsp.records_jsp
        rec = (Records) _jspx_page_context.getAttribute("rec", PageContext.SESSION_SCOPE);

C:\Rajah\Java\WebApplication1\build\generated\src\org\apache\jsp\records_jsp.java:54: cannot find symbol
symbol  : class Records
location: class org.apache.jsp.records_jsp
          rec = new Records();

C:\Rajah\Java\WebApplication1\build\generated\src\org\apache\jsp\records_jsp.java:61: cannot find symbol
symbol  : class Records
location: class org.apache.jsp.records_jsp
 Records rec = new Records();
 ^
C:\Rajah\Java\WebApplication1\build\generated\src\org\apache\jsp\records_jsp.java:61: cannot find symbol
symbol  : class Records
location: class org.apache.jsp.records_jsp
 Records rec = new Records();
                   ^
5 errors
0
rajah_mohammedAuthor Commented:
Okey if my question is hard can you give me an easy solution. How can I retrieve the information
in my database on my jsp ?
0
CodingExpertsCommented:
replace this
        <% Records rec = new Records();
        String str= rec.getAllData();  
        out.println("Data = " + str);
        %>
by
        <%
              String str= rec.getAllData();  
              out.println("Data = " + str);
        %>


secondly your function getData doesnot return a string !!!

first make a function that returns a String.

for test purpose modify getAllData  by

   public String  getAllData(){
      try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dburl = "jdbc:odbc:Records";
            conn = DriverManager.getConnection(dburl);
            stmt = conn.createStatement();
           
            query = "Select * from Employees ";
            rs = stmt.executeQuery(query);
            /*while(rs.next()){
               
            }*/        
        }
        catch (ClassNotFoundException x) {
            System.err.println( "Failed to load JDBC/ODBC driver." );
            x.printStackTrace();
        }
        catch(SQLException y){
            y.printStackTrace();
        }
    return "rajah_mohammed ";
    }


0
gdrnecCommented:
Good plan! One step at a time!.
0
CodingExpertsCommented:
0
CodingExpertsCommented:
I am sorry please ignore the previous post !!!!!!
0
rajah_mohammedAuthor Commented:
I tried it and here's what I got :

/*
 * Records.java
 *
 * Created on November 11, 2004, 11:49 AM
 */
import java.sql.*;

/**
 *
 * @author  Rajah
 */
public class Records {
   
    /** Creates a new instance of Records */
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;
    private String query;
     
    public Records() {
    }
   
    public String getAllData(){
       
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dburl = "jdbc:odbc:Records";
            conn = DriverManager.getConnection(dburl);
            stmt = conn.createStatement();
           
            query = "Select * from Employees ";
            rs = stmt.executeQuery(query);
            /**
            while(rs.next()){
            // get the data from the table.
            }  **/        
        }
        catch (ClassNotFoundException x) {
            x.printStackTrace();
        }
        catch(SQLException y){
            y.printStackTrace();
        }
        return "rajah_mohammed ";
    }
}

My JSP :

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>

<html>
    <head><title>JSP Page</title></head>
    <body>
   
        <jsp:useBean id="rec" scope="session" class="Records" />
       
        <%
              String str= rec.getAllData();  
              out.println("Data = " + str);
        %>

    </body>
</html>

I compiled the Records and its fine but when I run my JSP here's the error :

C:\Rajah\Java\WebApplication1\build\generated\src\org\apache\jsp\records2_jsp.java:50: cannot find symbol
symbol  : class Records
location: class org.apache.jsp.records2_jsp
      Records rec = null;
      ^
C:\Rajah\Java\WebApplication1\build\generated\src\org\apache\jsp\records2_jsp.java:52: cannot find symbol
symbol  : class Records
location: class org.apache.jsp.records2_jsp
        rec = (Records) _jspx_page_context.getAttribute("rec", PageContext.SESSION_SCOPE);
C:\Rajah\Java\WebApplication1\build\generated\src\org\apache\jsp\records2_jsp.java:54: cannot find symbol
symbol  : class Records
location: class org.apache.jsp.records2_jsp
          rec = new Records();
3 errors
0
rajah_mohammedAuthor Commented:
Okey I'll just make it easy. This is the other version I'm doing it in pure jsp but its not displaying anything :

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>

<html>
    <head><title>1st Database Connection</title></head><body>
       
    <%@ page import ="java.sql.*" %>
   
    <%! Connection conn; %>
    <%! Statement stmt; %>
    <%! ResultSet rs; %>
    <%! String query; %>

    <%
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dburl = "jdbc:odbc:Records";
            conn = DriverManager.getConnection(dburl);
            stmt = conn.createStatement();
           
            query = "SELECT * FROM Employees " + "WHERE Id Number = 001";
            rs = stmt.executeQuery(query);
            rs.next();
            out.println("" + rs.getString(1));
        }
        catch (ClassNotFoundException x) {
            x.printStackTrace();
        }
        catch(SQLException y){
            y.printStackTrace();
        }
    %>
    </body>
</html>

I have a database Records w/a Employees Table. I want to retrieve the record on an Id Number
that starts w/ 001. However all I can see is a blank page. What do I do ?
0
CodingExpertsCommented:
where is your class file located. Ideally it has to be in the WEB_INF/classes folder. and then try the previous code.
0
CodingExpertsCommented:
By class file I meant the Record.class file
0
rajah_mohammedAuthor Commented:
Ha same thing. The Record.class is in the classes of my WEB_INF/classes folder is its not doing any good
same error. Can you work on my new JSP above I think that is much easier and better, thanks.
0
gdrnecCommented:
Is your column actually called Id Number with a space? Just as a point of information, spaces are not a good idea in column names even though some databases (MS) allow it. I think you need to change your SQL to :
query = "SELECT * FROM Employees " + "WHERE [Id Number] = 001";

Another problem might be that you are confusing data types. If you are trying to compare to 001 it sounds like it is a String (VARCHAR). You might try:

query = "SELECT * FROM Employees " + "WHERE Id Number = '001'";

Hope this helps
0
rajah_mohammedAuthor Commented:
Ha I got it working now. The only problem was I havent registered it to my odbc datasource under control panel.

Here's my final cut :

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>

<html>
    <head><title>Database Connection</title></head><body>
       
    <%@ page import ="java.sql.*" %>
   
    <%! Connection con; %>
    <%! Statement stmt; %>
    <%! ResultSet rs; %>
    <%! String query; %>
    <%! String url; %>

    <%
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            url = "jdbc:odbc:Records";
            con = DriverManager.getConnection(url);
            stmt = con.createStatement();  
            query = "SELECT * FROM Employees WHERE IdNumber = '001'";
            rs = stmt.executeQuery(query);
           
            while (rs.next()) {    
            out.println("<b>IdNumber :</b> " + rs.getString(1) + "<br>");
            out.println("<b>Lastname  :</b> " + rs.getString(2) + "<br>");
            out.println("<b>Firstname :</b> " + rs.getString(3));
            }
        }
        catch (ClassNotFoundException x) {
            x.printStackTrace();
        }
        catch(SQLException y){
            y.printStackTrace();
        }
    %>
    </body>
</html>
0
ee_ai_constructCommented:
Question answered by asker or dialog valuable.
Closed, 100 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.