Solved

JSP - Database Query and Vector

Posted on 2002-04-23
13
561 Views
Last Modified: 2013-11-23
hi,

I am running a database query which returns 7000 records with 4 fields each.

The flow is as follows:

Page 1:

Click on link to Page 2

Page 2:

Accesses bean which runs query and stores records in vector.
Vector is passed back to Page 2.
First 20 element of vector is displayed.

The scenario is a back/forward  through records and display 20 results at a time.

My problem is that getting the results into the vector takes very long (13-15 secs).
So the initial 20 results seem to take forever to load, thereafter everything is fast because it only reads the vector.

Is there a faster way to do this ? As far as I know, a vector is the quickest.

Any help to get the execution time down will be appreciated.

Regards

Pierre
0
Comment
Question by:barnarp
13 Comments
 
LVL 9

Expert Comment

by:Venci75
ID: 6962234
You can use a different thread to store the results in the Vector. When this thread is ready with the needed record (for the first page - the first 20) - you can start displaying the results.
0
 

Author Comment

by:barnarp
ID: 6962277
yes,

Have thought of that, but have no idea how to handle threads in jsp!

Any idea?

0
 
LVL 3

Expert Comment

by:rjackman
ID: 6962299
which JDBC Driver r u using?
Arrays are faster than Vector coz vector takes time to reallocate when it is full
cheers
RJ
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:barnarp
ID: 6962302
yes,

Have thought of that, but have no idea how to handle threads in jsp!

Any idea?

0
 

Author Comment

by:barnarp
ID: 6962310
using classes12 oracle 805 client, oracle 806 server.
0
 
LVL 1

Expert Comment

by:pepenieto
ID: 6962370
barnarp,

Vectors are synchronized, so is much better in a multi-threaded system to use ArrayList.

If you´re using oracle, you can retrieve the first 20 records using:

select * from <table> where rownum<20

But this is only usefull when you only want the 20 first records.


0
 

Author Comment

by:barnarp
ID: 6962461
hi,

I am trying to build the following array without success:

List rows = new Vector();
Statement qry = null;
ResultSet rs = null;
ResultSetMetaData rsmeta = null;

 String querystr = "select...";
 
 qry = conn.createStatement();
 rs = qry.executeQuery (querystr);
 rsmeta = rs.getMetaData();


if (rs.next())
{
int rowcount = rsmeta.getColumnCount();
     do {
          List row = new Vector();

for (int i = 1; i <= rowcount ; i++) {
row.put(rs.getObject(i));

}
rows.put(row);
       } while (rs.next());
       
}

I get a compiler error about the ".put" method.

I dont know which method can be used with a list object.

Please help
0
 
LVL 9

Accepted Solution

by:
Venci75 earned 50 total points
ID: 6962485
try something like that:


import java.util.*;
import java.sql.*;

public class MyThread extends Thread {
  private Vector data = new Vector(20000);
  private boolean loaded = false;
  private String sql;
  private int rowsProcessed = 0;

  private Thread caller = null;
  private int callerInt;

  public MyThread(String sql) {
      this.sql = sql;
  }

  public Vector getData() {
      return data;
  }
  // use this method for getting the vector
  public synchronized Vector getData(int start, int count) {
      int maxNum = start + count;
      if (maxNum > rowsProcessed) {
          caller = Thread.currentThread();
          caller.wait();
          callerInt = maxNum;
      } else return data;
  }
  public boolean isLoaded() {
      return loaded;
  }
  public synchronized void addVectorRow(ResultSet rs) {
      rowsProcessed++;
      Object obj = "row# " + rowsProcessed;
      // .. creating the object
      data.add(obj);
      if (callerInt > 0 && callerInt < rowsProcessed) {
          callerInt = 0;
          caller.notify();
          caller = null;
      }
  }
  public void run() {
      // .... construct the result set
      ResultSet rs;
      while (rs.next()) {
          addVectorRow(rs);
      }
  }
}


to use this class:

MyThread loader = new MyThread("select ....");
loader.start();
Vector data = loader.getData(0, 20);

0
 

Expert Comment

by:sakan
ID: 6963495
Hi,
I have addition from pepenieto's comment. I use the way that query only the records we're going to use. Oracle has "minus" to minus recordset like this...

select * from <table> where rownum<101
minus
select * from <table> where rownum<81

this query will return record number 81-100

So, you can retrieve any records that u want.

0
 

Author Comment

by:barnarp
ID: 6963997
Thanks everyone for your input.

All your comments really helped me alot.

I think under the circumstances, a seperate thread will work best, due to slow performance of our databases.

I have changed my vector to a array in the meantime, and could already see a performance increase.

Regards

Pierre

0
 

Author Comment

by:barnarp
ID: 6964700
Venci75,

I am sending a DB Connection to the thread class as well.

Where do I execute the query?

In this block?

public MyThread(String sql,Connection conn) {
     this.sql = sql;
--> qry = conn.createStatement();
--> qryrs = qry.executeQuery (sql);
--> qryrsmeta = qryrs.getMetaData();
 }

Can't I add this code:

if (qryrs.next())
{
int rowcount = qryrsmeta.getColumnCount();
     do {
          row = new ArrayList();
 
for (int i = 1; i <= rowcount ; i++) {
row.add(qryrs.getString(i));
}

Rows.add(row);
       } while (qryrs.next());
}

to this block:

public void run() {
     // .... construct the result set
     ResultSet rs;
     while (rs.next()) {
         addArrayList(rs);
     }

and leave out this block:

public synchronized void addArrayListRow(ResultSet rs)

Please help me construct this thread. I can even start a new question thread and allocate you the points.

Regards

Pierre  
0
 
LVL 9

Expert Comment

by:Venci75
ID: 6964852
Yes - you are right - this is exactly what you should do
0
 

Author Comment

by:barnarp
ID: 6964870
thanks,

My thread class now looks like this:

import java.util.*;
import java.sql.*;

public class MyThread extends Thread {
 private ArrayList Rows ;
 private ArrayList Row  ;
 private String sql;
 public Connection connect;


 public MyThread(String sql,Connection conn) {
     this.sql = sql;
     this.connect = conn;
 }

public void run()
{
 
 try
 {execSQL();
}
catch(Exception e)
{
     }

 
       
}    
     
public void execSQL() throws ClassNotFoundException,SQLException,Exception
{
 Statement qrygetlabor = null;
 ResultSet qrygetlaborrs = null;
 ResultSetMetaData qrygetlaborrsmeta = null;

 qrygetlabor = connect.createStatement();
 qrygetlaborrs = qrygetlabor.executeQuery (sql);
 qrygetlaborrsmeta = qrygetlaborrs.getMetaData();


 if (qrygetlaborrs.next())
 {
 
 
    int rowcount = qrygetlaborrsmeta.getColumnCount();
     do {
          Row = new ArrayList();

 for (int i = 1; i <= rowcount ; i++)
    {
 Row.add(qrygetlaborrs.getString(i));
    }
 Rows.add(Row);}
          while (qrygetlaborrs.next());
}
}
 
 
 
 public ArrayList getData()
  {
     return Rows;
  }
}


As you can see, i simplified it to return the whole arraylist, and hioe it will work.

I can however not get to test it because of a compile error:

org.apache.jasper.JasperException: Unable to compile class for JSP

Generated servlet error:
E:\tomcat\work\localhost\_\Issues$jsp.java:331: Class org.apache.jsp.MyThread not found.

in

MyThread loader = new MyThread("select laborcode,name from labor order by name",conn);
               
??

I have copied MyThread.class in every tomcat/JRE and JDK directories!

classpath issue?


0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
web services creation SOAP vs REST 5 57
jdbc error in jsp application 20 64
ejb entity bean example 2 17
java example issue 5 26
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

821 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