Member Avatar for valatharv

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

  • 3 Contributors
  • 3 Replies
  • 129 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by JamesCherrill

Dani AI

Generated

A practical, low-latency pattern is: 1) fetch all requested rows in one set-based query, 2) map each row into a small POJO (an Item class, as suggested), and 3) push those Items back to the database in bulk — either as a single set-based call (preferred) or as a batched stored-proc call.

  • Use a POJO (Item) instead of a flat List<String>. A List<Item> keeps fields typed and avoids brittle index-based access; keep a HashMap<String,Item> only if you need fast lookups by id later.
  • Fetch in one round trip. Build a single SELECT with placeholders for the ids (or, on SQL Server, use a Table-Valued Parameter or a temporary table for very large sets). Map the ResultSet into Item objects and keep them in a collection.
  • Send data back efficiently. For SQL Server prefer a set-based server-side operation: pass a TVP (Table-Valued Parameter) to a stored procedure so the server processes all rows in one shot. If that is not possible, use a CallableStatement and addBatch()/executeBatch() to reduce round trips — but validate that your JDBC driver supports batching for CallableStatement; otherwise fall back to a small number of calls in a transaction.

Example patterns (outline):

String placeholders = String.join(",", Collections.nCopies(ids.length, "?"));
String sql = "SELECT ITEM_ID, ITEMNAME, ITEMLOCATION, STATUS FROM ITEM_RENEW WHERE ITEM_ID IN (" + placeholders + ")";
PreparedStatement ps = conn.prepareStatement(sql);
for (int i=0;i<ids.length;i++) ps.setString(i+1, ids[i]);
ResultSet rs = ps.executeQuery();
// map rows into List<Item>
CallableStatement cs = conn.prepareCall("{call dbo.RequeueItem(?, ?, ?)}");
for (Item it : items) {
  cs.setString(1, it.getName());
  cs.setString(2, it.getLocation());
  cs.setString(3, it.getStatus());
  cs.addBatch();
}
cs.executeBatch();

Notes and cautions: watch SQL Server limits (about 2100 parameters per batch), chunk very large id lists, use transactions when you need atomicity, always validate/parameterize inputs to avoid injection, and close resources (try-with-resources). For SQL Server TVP guidance see Using table-valued parameters with JDBC. This addresses ’s desire to avoid per-id queries and answers ’s retrieve-vs-delete clarification: use set-based SQL for bulk operations whenever possible.

Recommended Answers

All 3 Replies

Member Avatar for thekashyap

>> 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..

Member Avatar for valatharv

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    	
	...
}
Member Avatar for JamesCherrill

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.