Sally ZX

Greenhorn
+ Follow
since Nov 12, 2002
Merit badge: grant badges
For More
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Sally ZX

Thanks everyone.
I am using BufferedReader.readLine(), not read char. About the nio package, I think since I am download from a web server, not local disk, I cannot use FileChannel. I tried to use SocketChannel, but the problem is I cannot get the port number from the server. I guess since that web site is directly connected with underlying database, maybe the server won't let anyone to directly download or upload stuffs to it, so they can pretect their database. I can accept the current performance, which is 3-4 minutes for one file (17MB). I just need to let the computer run overnight for the 120 files.
Thanks anyway.
19 years ago
I need to download some .xls files from a web site. Since they are kind of big, about 17MB each, I rather not use BufferedReader to open them, read in the data and then write data to a local file. I noticed that FileChannel class has some really nice features, which makes transfer big files much faster and easier. But I cannot figure out how to use FileChannel with online file's URL, instead of local file.
Have someone ever done these before? Besides FileChannel and read/write data using BufferedReader, does anyone have any other suggestions on downloading big files in java?
Thanks a lot.
19 years ago
Hi,
I am not an expert, but this is what I learnt in the past 2 weeks, which I think might help you a little bit.
When you get the resultSet from a database, there is a default FetchSize. Everytime you do ResultSet.next(), it will first check whether there are records already fetched. If not, it will connect to the database again and fetch some records(FetchSize amount) and then let you go through them until there is no more records left. So it will be a big problem if your resultset is huge.
My suggestion is use CachedRowSet instead. It will save the entire ResultSet in the cache, and then the database connection closed. Then you can use CachedRowSet.next() and other methods to retrive it and do whatever to it, just like what you would do with the ResultSet. This one should be very fast. However, there is also problem with this one. If your ResultSet is really huge, you may get OutOfMemory Error. If that is the case, you may want to look at the JDBCRowSet or WebRowSet. You can check all three of them out at the java.sun.com tutorials. Here is the link:
http://developer.java.sun.com/developer/Books/JDBCTutorial/chapter5.html
Again, these are what I learnt in past 2 weeks. I am trying it, and ran into some problems. I posted my program and some running results too. The topic is "Please help me with CachedRowSet preformance!" If you interested, you can check it out. Hope someone will help me with it, and maybe it will solve your problem at the mean time.
Sally
Hi, everyone,
I have been stuck here for a week. Could someone please help me out?
I am using Oracle JDBC thin driver to connect to an Oracle DB, and
since the data I am pulling out of the DB is really large amount, I am
using CachedRowSet to get better performance. It works fine for some
query statements, like I got results in about 2 seconds. But for some
other query statements, it seems like the program just sit there for
several minutes, while at the mean time, the CPU usage and memory usage
are 0. Sometime it stuck at the Statement.executeQuery method, other
times it stuck at the CachedRowSet.populate method. I tried my best,
still it's not getting any better. I am posting my program and some
running results here. Please anyone, if you have any idea, please help
me! I am really appreciate it.
Note: For debugging purpose, I am displaying results in a JTable, on
the DOS window, and saving them into a file named "temp". Please ignore
those parts. And those date time stuffs are for debugging too. I was
trying to see how long each part takes.
Here is the code:
import java.io.*;
import java.sql.*;
import javax.sql.*;
import java.util.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.math.*;
import sun.jdbc.rowset.CachedRowSet;
public class cachedRowSetTest2 {
public static void main (String[] args) {
getConnection();
}
public static void getConnection () {
Vector data = new Vector();
Vector columnName = new Vector();
Connection con = null;
Statement rs_stmt = null;
ResultSet rs = null;
CachedRowSet crs = null;
String userid = "...";
String passwd = "...";
String url = "jdbc racle:thin:@...:...:...";
String query = "select PAT_TEST_HISTV.PI, PAT_TEST_HISTV.TEST_ID, PAT_TEST_HISTV.TEST_ABBR, PAT_TEST_HISTV.RSLT_VAL, PAT_TEST_HISTV.RSLT_DT_TM from RESULT.PAT_TEST_HISTV where ((PAT_TEST_HISTV.TEST_ABBR = 'ALKP') or (PAT_TEST_HISTV.TEST_ABBR = 'GGTP') or (PAT_TEST_HISTV.TEST_ABBR='ALT') or (PAT_TEST_HISTV.TEST_ABBR='AST') or (PAT_TEST_HISTV.TEST_ABBR='CK'))and (PAT_TEST_HISTV.PI between 31631 and 46766)";
BufferedWriter resultFile = null;
java.util.Date date1, date2, date3, date4;
int s1=0, s2=0, s3=0, s4=0, m1=0, m2=0, m3=0, m4=0, h1=0, h2=0, h3=0, h4=0;
int numberOfColumns = 0;
int counter=0;
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
DriverManager.setLogWriter(new PrintWriter(System.out) );
con = DriverManager.getConnection (url, userid, passwd);
System.out.println("before createStatement");
rs_stmt = con.createStatement();
System.out.println("after createStatement");
date1 = new java.util.Date();
s1 = date1.getSeconds();
m1 = date1.getMinutes();
h1 = date1.getHours();
System.out.println("the query statement is: " + query);
rs = rs_stmt.executeQuery(query);
date2 = new java.util.Date();
s2 = date2.getSeconds();
m2 = date2.getMinutes();
h2 = date2.getHours();
System.out.println("after executeQuery");
System.out.println("before create the cachedRowSet");
crs = new CachedRowSet();
System.out.println("after create cachedRowSet");
crs.populate(rs);
date3 = new java.util.Date();
s3 = date3.getSeconds();
m3 = date3.getMinutes();
h3 = date3.getHours();
System.out.println("after populate the cachedRowSet");
}catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (rs != null) rs.close();
if (rs_stmt != null) rs_stmt.close();
if (con != null) con.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
try {
resultFile = new BufferedWriter (new FileWriter("temp"));
}catch (IOException e) {
e.printStackTrace();
}

try {
ResultSetMetaData rsmd = crs.getMetaData();
numberOfColumns = rsmd.getColumnCount();
for (int i=1; i<=numberOfColumns; i++) {
columnName.addElement(rsmd.getColumnName(i));
}
while (crs.next()) {
counter++;
System.out.print(counter + ": " );
Vector record = new Vector();
try {
for (int i=1; i<=numberOfColumns; i++) {
System.out.print (crs.getString(i) + ", ");
String tempStr = crs.getString(i);
if (tempStr == null) {
resultFile.write(" ");
}
else {
resultFile.write(tempStr);
}
resultFile.write("\t");
record.addElement(crs.getString(i));
}
resultFile.newLine();
resultFile.flush();
}
catch (IOException e) {
e.printStackTrace();
}
System.out.println();
data.addElement(record);
}
}
catch (SQLException e) {
e.printStackTrace();
}
try {
resultFile.close();
}catch (IOException e) {
e.printStackTrace();
}
date4 = new java.util.Date();
s4 = date4.getSeconds();
m4 = date4.getMinutes();
h4 = date4.getHours();
System.out.println("time before the executeQuery: " + h1 + ":" + m1 + ":" + s1);
System.out.println("time after the executeQuery: " + h2 + ":" + m2 + ":" + s2);
System.out.println("time after populate the crs: " + h3 + ":" + m3 + ":" + s3);
System.out.println("time after printing out the crs contents: " + h4 + ":" + m4 + ":" + s4);
JTable resultTable = new JTable(data, columnName);
resultTable.setPreferredScrollableViewportSize (new Dimension (600, 600));
JScrollPane scrollPane = new JScrollPane (resultTable);
JPanel tablePanel = new JPanel();
tablePanel.setLayout (new BoxLayout(tablePanel, BoxLayout.Y_AXIS));
tablePanel.setBackground(Color.white);
tablePanel.add(scrollPane);
JFrame resultWindow = new JFrame ("Query Results");
resultWindow.addWindowListener(new CloseQuit());
resultWindow.getContentPane().add(scrollPane);
resultWindow.pack();
resultWindow.setVisible(true);
}
private static class CloseQuit extends WindowAdapter {
public void windowClosing (WindowEvent e ) {
System.exit(0);
}
}
}
And some sample running results are:
(1). ...
total amount of record is: 1449
connection, statement, and resultset are all closed
time before the executeQuery: 15:39:33
time after the executeQuery: 15:51:12
time after populate the crs: 15:51:12
time after printing out the crs contents: 15:51:13
...
(2). ...
total amount of record is: 1449
connection, statement, and resultset are all closed
time before the executeQuery: 16:15:02
time after the executeQuery: 16:15:09
time after populate the crs: 16:26:40
time after printing out the crs contents: 16:26:41
...

The only difference between the query statements for these two is the
first one has "order by" part. And it seems to me that the first one is
stuck at the executeQuery, and the second one stuck at the crs.populate.
It doesn't make any sense to me. Please help me out with this!
Thanks a lot.

[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]