Using ResultSetMetaData to frame SQL query

Question

I am trying to use ResultSetMetaData to frame a SQL query for checking if there are duplicate records in a table dynamically.

The query for checking duplicate records will look like this

select 
col1,
col2,
col3
from Table A
group by 
col1,
col2,
col3
having count(*)>1

Most of this code is boilerplate and i can extract all the columns of the table using ResultSetMetaData. But I am not sure how to do this in an elegant way and handle the last missing comma.

@utility : Yes . This query returns an empty result set if there are no duplicate records in table. — Santhiya, Aug 06 '15 at 14:43
You can save execution time by first checking whether the table is defined with a unique key (DatabaseMetaData). If comma is such a problem, use `, 1 as Smrt` and `, Smrt` or such. — Joop Eggen, Aug 06 '15 at 14:44
This seems to be related to http://stackoverflow.com/questions/1978933/a-quick-and-easy-way-to-join-array-elements-with-a-separator-the-opposite-of-sp — Aleksi Yrttiaho, Aug 06 '15 at 15:58

score 0 · Answer 1 · answered Aug 06 '15 at 15:56

I tend to use Joiner from the Guava Libraries in these kind of situation. With joiner you could write something like

String.format(
  "select %1$s from table %s group by %1$s having count(*) > 1",
  "tablename",
  Joiner.on(",").join("col1","col2","col3")
);

If you are using Java 8 then you can use String.join instead.

Please do check that the tablenames are properly quoted if necessary.

Using ResultSetMetaData to frame SQL query

1 Answers1