asked on

Result set in java

Hi There,

file Name: dir
below are the contents in the file.
-----------
AB123DE456
CD789DA123
.
.
.
.
.
.
.
.
.
.

-------

openning the file and looping through the records and checking if employee exists.

                  FileInputStream fis = new FileInputStream(dir);
                  InputStreamReader inputStream = new InputStreamReader(fis);
                  BufferedReader brreader = new BufferedReader(inputStream);

                  // Read the file
                  while ((line = brreader.readLine()) != null){


                        employee.setName(line.subString(0,2));
                        employee.setID(line.subString(5));

                        factory.existsEmployee(employee);

}
if(employee exists){

// do this

}
else{

//do this

}

in the employee exists method

query = select * from employee where employeeID = ? and employeeName = ?

query.setValue(1, employee.getID());
query.setValue(2, employee.getName());

return;

Instead of making calls to DB to check the employee for every record in the file, is there any better way?

Please advice.

Thanks.

for_yan

You can use ArrayList of "ID Name" say with the space or with the comma,
then you expand ArrayList into string for the query (se below) and
and use just one query

select * from emploeyee where employeeID || ' ' || employeeName in
('35 Peter', '25 Alex','15 Steve'...);

and then you can collect those which you find in the new ArryaList
and you'l know which exist

for_yan

Let me know if you need more clarification - I'll elaborate - I do it all the time this way

theartha

ASKER

@for_yan: can you elaborate. I am confused.

theartha

ASKER

How to get the whole table into result set and update few columns(e.g: time, amount) only which matches my employee iD?

for_yan

So we want to check if employe in the file are persent in databse, correct?
So we setup ArrayList:
ArrayList al = new ArrayList();

Then we read ID and Name s from file:

DataInputStream in = new DataInputStream(new FileInputStream("filename.txt"));
while(in.readline() is not null........){
{Somweher in the file we have ID and Nmae:

String Id = buff.nxtToken();
String name = buff.nextToken();

al.add(id + name);

}
in.close();

String s = "";

for(int j=0; j<al.size(); j++){
if(j != al.size()-1)
s += "'" + (String) al.get(j) + "',";
else
s += "'" + (String) al.get(j) + "'";

}

You should be careful about the last item - it does not
reequire comma after it.

then you use the lstruing we creatdin the query

ResultSet rs = stmt.executeQuery("select employee_id, employee_name from employees where
employeeID || employeeName in (s));

Then you iterate through the rResultSet and accumulate those employee which are present in the databse.

You can probably in this case use just ID fort concatenation.
If thiose are integeres - just create integeres sperateed by commas.
If ID are uniqe that would be fine

And you can accumulate Id is the strung raiht away withour ArrayList of course

for_yan

I'll send you simpler variant soon but the idea is the same - use
sql with thhe clasue "IN" and make up the string for contents of the IN

theartha

ASKER

@for_yan: What is buff ?? I don't see any buff initialized.

for_yan

That was not a program taht was a sketch - by buff I meant the String
where we read the line of the file line by line..

I'll have a little more time and i'll write it more organized

ASKER CERTIFIED SOLUTION

for_yan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mick Barry

you shouldn't need to check whether the record exists. Thats unnecessarily expensive
What is it exactly you are trying to achieve?

theartha

ASKER

@objects: My main reason to check the if the record exists because if the record exists I need to increase the amount and update another table with status success or failure(0 or 1).

This update happens in if .. else... in my code...

for_yan

perhaps you may update that table in the same SQL query?
That is usually more efective than going through java

for_yan

you can do for instance some query of such type:

update second_table s set s.field_to_update=(s.field_to_update + 1), s.another_field = 1 where s.employee_id = employee.employee_id
and employee.employee_id in ('id1','id2','id3',...)

at least in oracle this should be working.

I'm not sure I correctly understood about succes and failure, though

If you use update in sql,
then in the program above you just need to replace the query itself and
to replace executeQuery with executeUpdate.

Mick Barry

> My main reason to check the if the record exists because if the record exists I need to increase the amount and update another table with status success or failure(0 or 1).

should be able to do that without a select, let me know the update calls you need and I'll show you how you can do it

eg. might be something like

update employee set something='blah' where employeeID=?