I have successfully connected to my database, but when trying to return SQL query results to my index.jsp page using a Java servlet forward request receive the output of" oracle.jdbc.driver.OracleResultSetImpl@48f675" instead of the expected data results. (Note: I am receiving expected results from my SQL query in the console view of Eclipse; however, the data is not shown on my jsp page as I am expecting. Instead the ResultSetImp message is shown. Any insight anyone can share as to why I am getting this ResultSetImp instead of my expected data would be appreciated. Also does anyone care to explain what the ResultSetImp is saying...my searches have not turned up anything useful, or at least that I understand. Code is below:
package com.database;
import java.sql.*;
public class DBConnection {
public static void main(String[] args)
throws ClassNotFoundException, SQLException{
}
public static ResultSet queryExecute() throws ClassNotFoundException, SQLException
{
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection("**Connection string here**");
if (conn == null)
{
System.out.println("Database connection not successfull");
}
else
{
System.out.println("Database connection success!");
//System.out.println(conn);
}
Statement stmt = conn.createStatement();
//ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM Request natural join RequestTerm");
ResultSet rs = stmt.executeQuery("select * from (select QIPNUMBER,model,SERIALNUMBER,YEAROFMFG,DATERECEIVED,TERM,DEALERNAME from Request natural join RequestTerm)WHERE ROWNUM <= 20");
ResultSetMetaData rsmd = rs.getMetaData();
int columnsNumber = rsmd.getColumnCount();
//List<String> displayRecords = new ArrayList<String>(); try store result set into an array object
while (rs.next()) {
// Iterate through the data in the result set and display it.
//int count = rs.getInt(1);
while (rs.next()) {
//Print one row
for(int i = 1 ; i <= columnsNumber; i++){
System.out.print(rs.getString(i) + " " + " "); //Print one element of a row -- print vs println
}
System.out.println(); //Move to the next line to print the next row.
//System.out.println("Number of row:"+count);
}
}
rs.close();
stmt.close();
conn.close();
return rs;
}
}
My servlet code:
package com.srccodes.example;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.database.DBConnection;
/**
* Servlet implementation class HelloWorld
*/
@WebServlet("/HelloWorld")
public class HelloWorld extends HttpServlet {
public static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public HelloWorld() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter printWriter = response.getWriter();
printWriter.println("<h1>Hello from the doGet function!</h1>");
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//ResultSet display = DBConnection.queryExecute();
request.setAttribute("display", DBConnection.queryExecute());
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
request.getRequestDispatcher("/index.jsp").forward(request, response);
/*String nextJSP = "/result.jsp";
RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(nextJSP);
dispatcher.forward(request,response);*/
}
}
Finally the statement I use to call my servlet within my index.jsp:
<%= request.getAttribute("display")%> I know this statement is a scriplet and is frowned upon; however, I am trying to get data to populate to my jsp first and then move from there.
When you are returning Object of ResultSet, you will get the reference of the same. you wont get the values in it as you desire.
You will have to do something like this (if you really want to use scriplet):
<%
ResultSet rs = (ResultSet)request.getAttribute("display");
ResultSetMetaData rsmd = rs.getMetaData();
int columnsNumber = rsmd.getColumnCount();
while (rs.next()) {
for(int i = 1 ; i <= columnsNumber; i++){
out.print(rs.getString(i) + " " + " " + "<br/>");
}
out.print("<br/>"); //Move to the next line to print the next row.
}
}
rs.close();
%>
A more appropriate way to this will be to use a bean:
class MyBean {
int QIPNUMBER;
String model;
.....
//getters and setters
}
now in your class create list of MyBean and populate the objects, one Row = One object.
List<MyBean> lst = new ArrayList<MyBean>();
MyBean myBean;
while (rs.next()) {
myBean = new MyBean();
myBean.setModel(rs.getString("model"));
....
lst.add(myBean);
}
.....
return lst;
After this add the list to request in Servlet not the resultSet.
request.setAttribute("display", DBConnection.queryExecute());
// queryExecute now returns List<MyBean>
Now in your JSP if you want to use scriplet, then :
<%
List<MyBean> lst = (ArrayList<MyBean>)request.getAttribute("display");
for(MyBean mb : lst){
out.print(mb.getModel() + "<br/>");
.....
}
%>