• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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.
0
theartha
Asked:
theartha
  • 8
  • 4
  • 2
1 Solution
 
for_yanCommented:
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
0
 
for_yanCommented:
Let me know if you need more clarification - I'll elaborate - I do it all the time this way
0
 
thearthaAuthor Commented:
@for_yan: can you elaborate. I am confused.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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?
0
 
for_yanCommented:

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








0
 
for_yanCommented:
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
0
 
thearthaAuthor Commented:
@for_yan: What is buff ?? I don't see any buff initialized.
0
 
for_yanCommented:
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
0
 
for_yanCommented:
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.io.DataInputStream;
import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.StringTokenizer;

public class CompareFileDB {
    public CompareFileDB(){
        try{
            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 + "'";
            }

            in.close();

                                   Class.forName ("oracle.jdbc.driver.OracleDriver");
     Connection conn =
                  DriverManager.getConnection ("jdbc:oracle:thin:@myhostname.com:1521:sid","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();

            while(rs.next()){
                String id = rs.getString(1);
                String name = rs.getString(2);
                Employee emp  = new Employee(id, name);
                 existingEmployees.add(emp);


            }
  conn.close();






        }   catch(Exception ex){
            System.out.println(ex.toString());
            ex.printStackTrace();
        }

    }

}

Open in new window

0
 
objectsCommented:
you shouldn't need to check whether the record exists. Thats unnecessarily expensive
What is it exactly you are trying to achieve?
0
 
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...
0
 
for_yanCommented:
perhaps you may update that table in the same SQL query?
That is usually more efective than going through java
0
 
for_yanCommented:

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.





0
 
objectsCommented:
>  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=?


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.

  • 8
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now