Solved

JSP - Database Query and Vector

Posted on 2002-04-23
13
547 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now