DB MetaData change - how to create a dynamic sql?
<p>The db tables that I am using are changing very often meaning new column can be add which will reflect my sql’s.</p>

<p>The solution that I was thinking is first  “read” the meta data into some map and use it in order to retrieve the values something like this.</p>

<p>Read meta data:</p>

<pre><code>public class Dynamic {
static public final Map&lt;Integer, String&gt; metadata = initMetaData();
HashMap&lt;String, String&gt; data = new HashMap&lt;String, String&gt;();

private static Map&lt;Integer, String&gt; initMetaData() {
    Map&lt;Integer, String&gt; tmpMap = new HashMap&lt;Integer, String&gt;();
    try {
        Connection connection = DBConnection.getConnection();
        try {
            Statement stmt = connection.createStatement();
            ResultSet result = stmt.executeQuery("SELECT * FROM TMP WHERE ROWNUM = 1");
            for (int i = 1; i &lt;= result.getMetaData().getColumnCount(); i++) {
                tmpMap.put(new Integer(i), result.getMetaData().getColumnName(i));
            }
        } finally {
            connection.close();
        }
    } catch (SQLException ex) {
        …..
    }
    return Collections.unmodifiableMap(tmpMap);
}

public static String getColumnName(Integer index) {
    return metadata.get(index);
}
</code></pre>

<p>And when running the sql:</p>

<pre><code>public static void test()        
    try {
        Connection connection = DBConnection.getConnection()
        try {
            Statement stmt = connection.createStatement();
            ResultSet result = stmt.executeQuery("SELECT * FROM TMP where idx = 'R5'");

            while (result.next()) {
            Dynamic d = new Dynamic()
                for (int i = 1; i &lt;= Dynamic.metadata.size(); i++) {                        
                         d.setData(Dynamic.getColumnName(i),result.getString(Dynamic.getColumnName(i)));       
                }
            }
</code></pre>

<p>In this approach I have two problems(that I notice):</p>

<p>1)  I need to execute two loops </p>

<p>2)  I don’t know which get function to use via resultset since the type can also change.</p>

<p>How can I overcome those problems ?</p>

<p>I would also appreciate to get some other suggestion maybe there is a simple why  </p>

<p>Thanks</p>
