Improve company productivity with a Business Account.Sign Up

x
?
Solved

JSP - Database Query and Vector

Posted on 2002-04-23
13
Medium Priority
?
601 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 

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 200 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

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.

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.

Join & Write a Comment

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will learn how to implement Singleton Design Pattern in Java.

601 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