Separate files created upon change in SerialNo. doesnot contain full data.

**renu** · 11-17-2010, 02:37 PM

Hi

I have a query which gives me data related to schools in a county.
the data contains information abt ..i am giving sample data below.
sno. classname schoolname year
1 class3 KPMG 2010
1 class9 GTNS 2009
1 class4 KAT 2008
2 class2 ABCS 2009
2 class3 DALS 2010
2 class1 DHPS 2010
2 class4 SAMS 2005
2 class6 WQAS 2009
2 class5 QBCS 2008

I want every unique serial number records to be in a separate file
That means
data containing sno. 1 ...will be in one separate file named after the sno

My problem , is i dont get all the records of sno.1 in the separate file.
I see all records of sno.2 in another separate file.

How can i achieve all the records of sno.1 to be in the separate file.
Pls see my code and help me figure out the mistake.
I tried by changing the flush and close statements in different places in the code ..to achieve all records of sno.1 in the separate file..But i didnot get to solve the problem.

Java Code:

BufferedWriter write2Sep =  null;
		FileWriter fw = null;

		try{			
			ResultSet  resultSet = statement.executeQuery("select sno,classname,schoolname,year from county");

//			Looping through each record at a time
			String oldSno="";	
			while(resultSet.next()){
				CountRec1++;
				String sno = resultSet.getString(1);
				String classname = resultSet.getString(2);
				String schoolname = resultSet.getString(3);
				String year = resultSet.getString(4);

				if(!(oldSno.equals(sno))){
					SeparateFile = location + "\\" +yyyy+"_"+sno+"_RXG_"+type+".csv";
					fw=new FileWriter(SeparateFile, false);
					write2Sep = new BufferedWriter(fw);
//					write2Sep.flush();
					String mainStr = sno + "," + classname + "," + schoolname + "," + yyyy;
					write2Sep.write(mainStr);
					write2Sep.write("\n");
					CountSepRec++ ;
				}else{
					String mainStr = sno + "," + classname + "," + schoolname + "," + yyyy;
					write2Sep.write(mainStr);
					write2Sep.write("\n");
					CountSepRec++ ;
				}			
				oldSno = sno;
			}
			System.out.println("The number of records obtained from the first query: "+CountRec1);
			resultSet.close();
		}catch(IOException e){
			e.printStackTrace();
		}catch (SQLException sqlException1) {
			System.out.println("SQL Exception - Query One ");
			System.out.println(sqlException1.getMessage());
		}
		finally{
			try
			{
				if(write2Sep != null){
					write2Sep.flush();
					write2Sep.close();
				}
				if(fw != null)
					fw.close();
			}catch(IOException e)
			{
				e.printStackTrace();
			}
		}

**Tolls** · 11-17-2010, 03:00 PM

Your resultset isn't ordered (that is there is no ORDER BY in the query), so how do you know what order the rows are going to come back in?
If you have a serialId 1 row followed by a serialId 2 row, followed again by a serialId 1 row then the first one will be overwritten.

The other option (though I, personally, would go for ordered) is to set the append flag in the FileWriter constructor to true. However this might mean earlier runs will have rows appended to them, depending on what you're doing.

**renu** · 11-17-2010, 03:15 PM

Sir,
I did use the order by clause in my query and saw the result of the query using SQL*PLUS and it returns the records as mentioned in the above example.

As said i also changed the append flag to true .

But it does not give the results i am expecting ..

The first separate file doesnot contain all records with sno.1

Is their any problem with flush and close statements order in my coding ..

Pls help me ..in solving this problem

**Tolls** · 11-17-2010, 03:31 PM

Your query in the code above is:
select sno,classname,schoolname,year from county
...no ORDER BY.

Assuming the above code still stands, you're not closing your writers before opening a new one. At least I can't see where you're closing them.

**renu** · 11-17-2010, 03:38 PM

Hi , The above peice of code is to create a unique separate file for sno.1 records only .
If you see the if clause , gets the first unique record and the else part will continue adding records with the same sno. into the file.
In the if clause , i write lines to add records to the file .FileWriter constructor , BuffereWriter constructor .
I also wrote a flush statement their. If i close the writer after flush. Records are not entering into the file.

Thats why , i added the finally block at the end ..adding flushing and closing statements .(pls see my first mail with the full code )

Java Code:

while(resultSet.next()){
				CountRec1++;
				String sno = resultSet.getString(1);
				String classname = resultSet.getString(2);
				String schoolname = resultSet.getString(3);
				String year = resultSet.getString(4);

				if(!(oldSno.equals(sno))){
					SeparateFile = location + "\\" +yyyy+"_"+sno+"_RXG_"+type+".csv";
					fw=new FileWriter(SeparateFile, false);
					write2Sep = new BufferedWriter(fw);
//					write2Sep.flush();
					String mainStr = sno + "," + classname + "," + schoolname + "," + yyyy;
					write2Sep.write(mainStr);
					write2Sep.write("\n");
					CountSepRec++ ;
				}else{
					String mainStr = sno + "," + classname + "," + schoolname + "," + yyyy;
					write2Sep.write(mainStr);
					write2Sep.write("\n");
					CountSepRec++ ;
				}			
				oldSno = sno;
			}

**Tolls** · 11-17-2010, 03:40 PM

You open the write here:
fw=new FileWriter(SeparateFile, false);

But there is no close of the previous writer. So when it moves from sno.1 to sno.2 then the former is still open.

You still haven't shown your SQL with the ORDER BY clause in it.

ETA: You could save yourself a few lines by removing the else block and simply having that code after you've exited the if:

Java Code:

				if(!(oldSno.equals(sno))){
					SeparateFile = location + "\\" +yyyy+"_"+sno+"_RXG_"+type+".csv";
					fw=new FileWriter(SeparateFile, false);
					write2Sep = new BufferedWriter(fw);
//					write2Sep.flush();
				}			
				String mainStr = sno + "," + classname + "," + schoolname + "," + yyyy;
				write2Sep.write(mainStr);
				write2Sep.write("\n");
				CountSepRec++ ;

**renu** · 11-17-2010, 03:48 PM

Sir

The original query does contain the order by clause .

ResultSet resultSet = statement.executeQuery("select sno,classname,schoolname,year from county order by sno,classname,schoolname,year");

How do i close the filewriter,
The entire code is in the whle loop which contains the resultSet of the above query.

My writer statements are in the if clause , And everytime it sees a new sno.
a new separate file is created and the file name is created using that sno. and the year.

How do i have to change the code to close the writer .....

Help me figure out , how to do that in the while and if loop

**renu** · 11-17-2010, 03:51 PM

Originally Posted by Tolls

You open the write here:
fw=new FileWriter(SeparateFile, false);

But there is no close of the previous writer. So when it moves from sno.1 to sno.2 then the former is still open.

You still haven't shown your SQL with the ORDER BY clause in it.

ETA: You could save yourself a few lines by removing the else block and simply having that code after you've exited the if:

Java Code:

				if(!(oldSno.equals(sno))){
					SeparateFile = location + "\\" +yyyy+"_"+sno+"_RXG_"+type+".csv";
					fw=new FileWriter(SeparateFile, false);
					write2Sep = new BufferedWriter(fw);
//					write2Sep.flush();
				}			
				String mainStr = sno + "," + classname + "," + schoolname + "," + yyyy;
				write2Sep.write(mainStr);
				write2Sep.write("\n");
				CountSepRec++ ;

No Sir , i cannot do that ..as

The program crates a cumulative file for all data and separate files created upon each change in sno.

I have another file writer adding all the records into the cumulative file.

Thats why i keep the else part to create the separate file with unqiue records

**Tolls** · 11-17-2010, 03:52 PM

Then why did you not put that in the original post?

write2Sep.close()...you already do it later on. And you do that before creating the new one.

This may well not cure your problem, but leaving them open has the potential to cause a problem.

**Tolls** · 11-17-2010, 03:53 PM

Originally Posted by renu

No Sir , i cannot do that ..as

The program crates a cumulative file for all data and separate files created upon each change in sno.

I have another file writer adding all the records into the cumulative file.

Thats why i keep the else part to create the separate file with unqiue records

That makes no senxe. The if statement is simply to decide whether to open a new writer or not. Since the if statement and the else statement contain several lines of the same code then that code should not be in either and should be outside of those statements.

What I wrote above is logically exactly the same as the code you wrote using if/else.

**renu** · 11-17-2010, 04:03 PM

Originally Posted by Tolls

Then why did you not put that in the original post?

write2Sep.close()...you already do it later on. And you do that before creating the new one.

This may well not cure your problem, but leaving them open has the potential to cause a problem.

Yes Sir , I did change the code as you said , putting the writer in the if clause and it does create 2 separate files.
But i still have the problem , of not having all the records with sno.1 (that is my first separate file does not contain all the records)
where do you suggest me to close the writer .

**Tolls** · 11-17-2010, 04:23 PM

I've just told you.

And without you actually trying to do something I might be forced to exit this thread in frustration.

**renu** · 11-17-2010, 04:55 PM

Pls dont get angry on me .

I am not really knowing , how to use the flush and close method , before i create another separate file .Because first time i create the file in the if clause and every new file is created in the if clause.

XML Code:

while(resultset.next) {
:
:
:
if(!(oldSno.equals(sno))){
					SeparateFile = location + "\\" +yyyy+"_"+sno+"_RXG_"+type+".csv";
					fw=new FileWriter(SeparateFile, false);
					write2Sep = new BufferedWriter(fw);
//					write2Sep.flush();
				}		

:
:
:

**Tolls** · 11-17-2010, 05:03 PM

Sorry about that...
OK, you don't need to flush(). Just close() (which calls flush normally anyway).

I said earlier, just before opening the new FileWriter, ensure you close write2Sep (if it's not null). If you don;t do that then you don't close anything except the last file and I can't guarantee what will happen.

You also only need to close write2Sep. That will close the FileWriter at the same time.
In fact you can change this:

Java Code:

write2Sep.close();  // This is the close I'm talking about
fw=new FileWriter(SeparateFile, false);
write2Sep = new BufferedWriter(fw);

to this:

Java Code:

write2Sep.close();  // This is the close I'm talking about
write2Sep = new BufferedWriter(new FileWriter(SeparateFile, false));

And forget about "fw" entirely.

**renu** · 11-17-2010, 05:15 PM

I get a null pointer exception , when i do like that

Exception in thread "main" java.lang.NullPointerException
And it points to the write2Sep.close();
statement .

Java Code:

BufferedWriter write2Sep =  null;
QUERY is written here
oldSno="";
while(resultset.next()){
:
:
:
:
if(!(oldSno.equals(sno))){
SeparateFile = location + "\\" +yyyy+"_"+sno+"_RXG_"+type+".csv";
write2Sep.close();  // This is the close I'm talking about
write2Sep = new BufferedWriter(new FileWriter(SeparateFile, false));
}
:
:
:

Sir,

before even we create the write2Sep ..how can we close it at the first instance ..
that is what is happening here...and it gives me a nullpointer exception.

I am not knowing ..how i can create new separate files with different names based on the sno ...if i dont write the write statement in the if clause...

ANy more help ..is appreciated .
Thank You for the patience ...

**renu** · 11-17-2010, 05:19 PM

Originally Posted by Tolls

Sorry about that...
OK, you don't need to flush(). Just close() (which calls flush normally anyway).

I said earlier, just before opening the new FileWriter, ensure you close write2Sep (if it's not null). If you don;t do that then you don't close anything except the last file and I can't guarantee what will happen.

You also only need to close write2Sep. That will close the FileWriter at the same time.
In fact you can change this:

Java Code:

write2Sep.close();  // This is the close I'm talking about
fw=new FileWriter(SeparateFile, false);
write2Sep = new BufferedWriter(fw);

to this:

Java Code:

write2Sep.close();  // This is the close I'm talking about
write2Sep = new BufferedWriter(new FileWriter(SeparateFile, false));

And forget about "fw" entirely.

Thank You very much Sir,

Pls ignore my previous mail

i followed your advise this time in a coorect way

if(!(oldSno.equals(sno))){
if(write2Sep != null){
write2Sep.flush();
write2Sep.close();
}
:
:

Thanks again Sir.Your help today is of lot of value for me.

**Tolls** · 11-17-2010, 05:23 PM

You just need the close().
The flush will happen as part of the close.
No harm in flushing, but it's unecessary.

Anyway, you'll now have to see if that's helped.
If not, then you'll need to find out which row(s) is going missing.

**renu** · 11-22-2010, 08:43 PM

Sir

Can you please help me ..I wrote a post describing the error and also given code.
Please look at this link and help me .
I need to complete it and not knowing how to solve it .

How to create a zip out file from the current directory of input files