Result set in java

Hi There,

file Name: dir
below are the contents in the file.


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){



if(employee exists){

// do this


//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());


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

Please advice.

Who is Participating?
for_yanConnect With a Mentor Commented:
This is more organized - assuming the your employeeid
are unique strings this is something
like it could be - the end you end
up with the ArrayList existingEmployees -
I assume that you have an object Employee - that would be array of these objects.

This is still not a program as I could not test it without tables,
files, etc. but I hope it will give you impresiion how to do it with one query.

import java.sql.*;
import java.util.ArrayList;
import java.util.StringTokenizer;

public class CompareFileDB {
    public CompareFileDB(){
            DataInputStream in = new DataInputStream(new FileInputStream("file.txt"));
            String buff = null;
            String inputString = "";
            while((buff=in.readLine()) != null){
                StringTokenizer t = new StringTokenizer(buff);
                String employee_id = t.nextToken();
                inputString += ",'" + employee_id + "'";


                                   Class.forName ("oracle.jdbc.driver.OracleDriver");
     Connection conn =
                  DriverManager.getConnection ("","user","pswd");
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery("select empolyee_id, eployee_name from employees where employee_id IN (" +
            inputString.substring(1)+ ")");

            ArrayList existingEmployees = new ArrayList();

                String id = rs.getString(1);
                String name = rs.getString(2);
                Employee emp  = new Employee(id, name);


        }   catch(Exception ex){



Open in new window

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
Let me know if you need more clarification - I'll elaborate - I do it all the time this way
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

thearthaAuthor Commented:
@for_yan: can you elaborate. I am confused.
thearthaAuthor Commented:
How to get the whole table into result set and update few columns(e.g: time, amount) only which matches my employee iD?

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);


String s = "";

for(int j=0; j<al.size(); j++){
if(j != al.size()-1)
s += "'" + (String) al.get(j) + "',";
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

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
thearthaAuthor Commented:
@for_yan: What is buff ?? I don't see any buff initialized.
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
you shouldn't need to check whether the record exists. Thats unnecessarily expensive
What is it exactly you are trying to achieve?
thearthaAuthor Commented:
@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...
perhaps you may update that table in the same SQL query?
That is usually more efective than going through java

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.

>  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=?

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.

All Courses

From novice to tech pro — start learning today.