Hi,

I am looking for best approach to query db table and get corresponding values on the basis of Ids passed.

I have ids in String array, please provide best approach for point (a) and (b), what to use ArrayList, HashMap, etc... so that there are minimum loops...

Sample code:
-------

String[] idsToDelete = request.getParameterValues("idValue");   
System.out.println("idsToDelete.length is :"+idsToDelete.length);
for (int i = 0; i < idsToDelete.length; i++) {   
 System.out.println("Id to pass to query to get corresponsing items : "+idsToDelete[i]);
}

Output:
-------

[STDOUT] idsToDelete.length is :2
[STDOUT] Id to pass to query to get corresponsing items : 25
[STDOUT] Id to pass to query to get corresponsing items : 24

a) Get itemName, itemLocation, status from single table on the basis of this id in some method.
//Some method where I can pass above "idsToDelete" (25, 24...) and store corresponding values (itemName, itemLocation, status)

b) Then pass these itemName, itemLocation, status to a proceduer in java method which will call proceduer for each set of itemName, itemLocation, status.
//Some method using the stored corresponding values (itemName, itemLocation, status) and pass to proceduer for each set of itemName, itemLocation, status

>> Question is quite confusing. Do you want to know what's the best data structure to use? Or you want to know if the function design is okay or not? What's the connection of code you posted and your question?

a) Get itemName, itemLocation, status from single table on the basis of this id in some method.
//Some method where I can pass above "idsToDelete" (25, 24...) and store corresponding values (itemName, itemLocation, status)
>> Do you wnat to delete the rows matching ids or you want to retrieve them?


b) Then pass these itemName, itemLocation, status to a proceduer in java method which will call proceduer for each set of itemName, itemLocation, status.
//Some method using the stored corresponding values (itemName, itemLocation, status) and pass to proceduer for each set of itemName, itemLocation, status

>> No clue what's meant here, except some wild guesses..

Sorry for the confusion... here are some details and what I have achieved...

Summary : I need to first get itemValues on the basis of EACH id then pass the itemValues (itemName, itemLocation, status) to a proceduer..

Though it is working but would like to know any better approach...

If you see the code below, it fires select query everytime for each id, then immediately pass the values to proceduer...

a) Is there any better way around so that select is fired one's, may be we can use "WHERE ITEM_ID in (<pass array>)" or something...
b) I am storing item values in List("itemList") before passing to proceduer, is it right or I should simply use some string array...

Assume I have the ids available in String array

public void passRecords(String [] ids){	
	for (int i = 0; i < ids.length; i++) {		 
		getItemValues(ids[i]);
	}
}


private void getItemValues(String ids){
	List<String> itemList = new ArrayList<String>();
	ResultSet rs = null;
	PreparedStatement preparedStatement = null;
		try
		{				
			String strQuery = " SELECT ITEM_ID, ITEMNAME, ITEMLOCATION, ORDERLOCATION, STATUS, LST_DATE " +
			"FROM ITEM_RENEW  " +
			"WHERE ITEM_ID =?";
			
			preparedStatement = conn.prepareStatement(strQuery);
			preparedStatement.setString(1, ids);
			rs = preparedStatement.executeQuery();
			
			while (rs.next()) {
				itemList.add(rs.getString("ITEM_ID"));
				itemList.add(rs.getString("ITEMNAME"));
				itemList.add(rs.getString("ITEMLOCATION"));
				itemList.add(rs.getString("ORDERLOCATION"));
				itemList.add(rs.getString("STATUS"));
			}			
		}//end of try
   ....
	//CALL PL/SQL
	 try {
		callRequeueProc(itemList.get(1), itemList.get(2), itemList.get(3),conn);
	}//END CALL PL/SQL    	
	...
}

In general, the right way to do this is to create an Item class, with ID, name, location etc as instance variables. When you retrieve an item from the database use the row values to construct a new Item instance and pass that as a simple parameter. You can also create ArrayLists or other collections of Items to process then in bulk.

This article has been dead for over six months. Start a new discussion instead.