Concatenating string texts based of key value

[Tariq Ahsan]

Hello,

I am doing a query in a database table where there is ID column and DATA column. There could be multiple rows of the same ID in the table.
I like to concatenate the text values of all the DATA column for all matching ID values. The query will look for all the IDs in the table.

I am thinking of using HashMap for this to store the all the ID values as a key and DATA as the value. But using the HashMap's 'put' removes the old DATA value. I could store the DATA value in a separate StringBuffer object. But that does'nt help me in what I am trying to do which is store all the associated DATA content into it's corresponding ID value.

Here's a sample query output -

ID DATA
-------------------------------------------------------------------------
001 <?xml version="1.0" encoding="UTF-8"?>
<_doc xml:space="preserve">
<ORADATA>
<DATA1>3</DATA1>
<DATA2>abc</DATA2>
<DATA3>UVW</DATA

001 3></ORADATA>
<XMLDATA>
<XMLDATA1>1</XMLDATA1>
<XMLDATA2>0</XMLDATA2>
</XMLDATA>
</_doc>

002 <?xml version="1.0" encoding="UTF-8"?>
<_doc xml:space="preserve">
<ORADATA>
<DATA1>5</DATA1>
<DATA2>XYZ</DATA2>
<DATA3>DEF</DATA

...

So, what I want is for the ID value '001' to have a String text -

<?xml version="1.0" encoding="UTF-8"?>
<_doc xml:space="preserve">
<ORADATA>
<DATA1>3</DATA1>
<DATA2>abc</DATA2>
<DATA3>UVW</DATA3>
<XMLDATA>
<XMLDATA1>1</XMLDATA1>
<XMLDATA2>0</XMLDATA2>
</XMLDATA>
</_doc>

I can always do a second sql query to find all the rows for a particular ID and then use that as the unique key. But was wondering if there could be another java way to achieve that.

Thanks.

[fred rosenberger]

isn't there a way to see if the key is already used in the map? could you check to see if the key exists first. if not, add the key/value in. if it does, extract the old key, concatenate, and then re-insert.

or am i missing something?

[Bill Cruise]

Just to reinforce Fred's answer, yes that's a good way to do it. I use this technique a lot, and since you're using a HashMap (as opposed to a List-based collection) it's really fast.

Just do this as you loop through your result set.

if(map.get(ID) == null) {
map.put(ID, DATA);
}
else {
map.put(ID, map.get(ID) + DATA);
}

Here, DATA is the newest piece of data from your result set, map.get(ID) gives you the older data cached in the HashMap.

[Tariq Ahsan]

Thanks guys! I'll try out your suggestions.