Hi All,
From Java Iam looping through Array and providing some id's to SQL select statement,now some id's may be present in DB and some not.So i would like to detect id's not present in DB through JDBC.Is it possible or will I have to work out with arrays and comparing the resultset after storing it in an array.
Thanks in Advance.
> So i would like to detect id's not present in DB through JDBC.
Can you give an example of what type of output you would like to see in a ResultSet that would satisfy your needs? Your question is a bit to vague (for me) to be able to provide any suggestions. It sounds like you may have to do a match merge of an array with a ResultSet which isn't very difficult (just place the ResultSet into an array of some kind) and then it's just traditional Java coding. But depending on your requirements, you may be able to have the database do all the work (the match merge) for you which in my mind is usually preferable.
Suppose in my Array i have id's 25,26,29,85 and Iam passing id's for select like
for(int i=0;i<id.length;i++)
{
Select * from emp where id='"+id[i]+'"
}
Now if suppose 29 is not present in Db then Iam going to get only values other than 29.Now my requirement is that after select is executed for 29 I want to capture NULL for a row(in this case 29) in Java as usual other than 29 everything is going to come.So how to capture null row in Java.
Presently Iam storing resultset in Arraylist and comparing id's from id array and detecting which id is not present in Arraylist.
Thanks in advance>
if you do it that way, it might look like
for(int i=0;i<id.length;i++)
{
String sSQL = "Select * from emp where id='"+id[i]+"'" ;
Statement stmt = con.createStatement() ;
ResultSet rs = stmt.executeQuery(sSQL) ;
if (rs.next()) {
// there is a row with the requested ID, add the corresponding row
} else {
// there is no row with this id, add null row to your array list.
// this has nothing to do with jdbc
}
}
to represent a row in your array list you might define a class that has a member field for each column of your table and two constructors. One accepting a ResultSet as a parameter. This constructor fills the member fields with the corresponding values of the columns in the Resultset (the positioning is already done by rs.next().
The other constructor only accepts an id and leaves all other member fields as null.
You could 'optimize' that code using by using a PreparedStatement.
Still, if you have an arry with 1000 ids you would create and execute 1000 sql statements. I would not do that.
A better idea IMHO would be to generate a sorted array for the id s
and then do something like this
String sSQL = "select * from emp where id>= '" + id[0] +"' and id><='" + id[id.length]+"' order by id" ;
Statement stmt = con.createStatement() ;
ResultSet rs = stmt.executeQuery(sSQL) ;
// you now have a sorted id array and a ResultSet sorted by ids
// The next part is to 'merge' them
int iCurrent= 0 ;
while (rs.next()) {
String idCurrent = rs.getString("id") ;
int iCompare = id[iCurrent].compareTo(idCurrent) ;
if (iCompare == 0){
// this id exists, add the corresponding row to your arraylist
... your add to arraylist stuff
// now position on the next id in the id array
iCurrent++ ;
} else if (iCompare <0) {
//idCurrent is greater than the current entry in the id array
// that means for the current entry in id there is no row in the resultset
// skip through id array until you find an id that is not less than idCurrent
while ( iCompare < 0 && iCurrent<id.length) {
// add a null row for id[iCurrent]
iCurrent = iCurrent + 1 ;
iCompare = id[iCurrent].compareTo(idCurrent) ;
}
if (iCompare==0) {
// the id[iCurrent] has been found in the ResultSet
... your add to arraylist stuff
// position on the next id in the list
iCurrent = iCurrent+1;
}
} else {
// idCurrent is not in the id array. Ignore this row
}
}
This should be much faster, as you only send one Request to your database.>
