Solved

JSP - Database Query and Vector

Posted on 2002-04-23
13
556 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
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 

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

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to get all the API from website? 11 91
Where to store the queries for modification of table 4 62
arguments to jar 5 26
how to see all occupied ports on windows 10 laptop 15 65
An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…

778 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