How To determine the ResultSet Column type dynamically

Question

I need to generate an INSERT stmt for all the tables in DB dynamically at runtime. I'm fetching the table contents(data) one by one and determining the column type at runtime while looping thru the ResultSet & ResultSetMetaData in order to add Double Quotes(") for String type and not adding the same for non String types of columns. However with the current logic implemented, it ignores the first record and works perfectly fine for all remaining records in a table. I know why it is working, as while fetching the rs.getColumnType(int columnIndex) we need to give column name which starts from 1 index..but not able to build the logic so that it will check the first row also rather then ignoring it at the moment..

Please see below code snippet

if (rs != null) {
    while (rs.next()) {
        sqlString = new StringBuffer();

        for (int i = 1; i <= columnCount; i++) {
            Object obj = rs.getObject(i);

            if (obj == null) {
                sqlString.append("null");
                sqlString.append(",");
            }
            else
            {
                int sqlTypes = rsmd.getColumnType(i);

                switch (sqlTypes) {
                    case Types.VARCHAR:
                        sqlString.append("\"");
                        if (obj != null)
                            sqlString.append(rs.getString(i));

                        sqlString.append("\"");
                        sqlString.append(",");
                        break;
                    case Types.NULL:
                        sqlString.append("null");
                        sqlString.append(",");
                        break;
                    case Types.CHAR:
                        sqlString.append("\"");
                        if (obj != null)
                            sqlString.append(rs.getString(i));

                        sqlString.append("\"");
                        sqlString.append(",");
                        break;
                    case Types.TIMESTAMP:
                        sqlString.append("\"");
                        if (obj != null)
                            sqlString.append(rs.getTimestamp(i));

                        sqlString.append("\"");
                        sqlString.append(",");
                        break;
                    case Types.DOUBLE:
                        if (obj != null)
                            sqlString.append(rs.getDouble(i));

                        sqlString.append(",");
                        break;
                    case Types.INTEGER:
                        if (obj != null)
                            sqlString.append(rs.getInt(i));

                        sqlString.append(",");
                        break;
                    case Types.SMALLINT:
                        if (obj != null)
                            sqlString.append(rs.getInt(i));

                        sqlString.append(",");
                        break;
                    case Types.DECIMAL:
                        if (obj != null)
                            sqlString.append(rs.getBigDecimal(i));

                        sqlString.append(",");
                        break;

                    /*default:
                        if (obj != null)
                            sqlString.append(obj.toString());

                        sqlString.append(",");
                        break;*/
                }
            }
        }

Answer 1

Have you tried using a do while loop like this:

if (rs != null) {
  do{
    sqlString = new StringBuffer();

    for (int i = 1; i <= columnCount; i++) {
        Object obj = rs.getObject(i);

        if (obj == null) {
            sqlString.append("null");
            sqlString.append(",");
        }
        else
        {
            int sqlTypes = rsmd.getColumnType(i);

            switch (sqlTypes) {
                case Types.VARCHAR:
                    sqlString.append("\"");
                    if (obj != null)
                        sqlString.append(rs.getString(i));

                    sqlString.append("\"");
                    sqlString.append(",");
                    break;
                case Types.NULL:
                    sqlString.append("null");
                    sqlString.append(",");
                    break;
                case Types.CHAR:
                    sqlString.append("\"");
                    if (obj != null)
                        sqlString.append(rs.getString(i));

                    sqlString.append("\"");
                    sqlString.append(",");
                    break;
                case Types.TIMESTAMP:
                    sqlString.append("\"");
                    if (obj != null)
                        sqlString.append(rs.getTimestamp(i));

                    sqlString.append("\"");
                    sqlString.append(",");
                    break;
                case Types.DOUBLE:
                    if (obj != null)
                        sqlString.append(rs.getDouble(i));

                    sqlString.append(",");
                    break;
                case Types.INTEGER:
                    if (obj != null)
                        sqlString.append(rs.getInt(i));

                    sqlString.append(",");
                    break;
                case Types.SMALLINT:
                    if (obj != null)
                        sqlString.append(rs.getInt(i));

                    sqlString.append(",");
                    break;
                case Types.DECIMAL:
                    if (obj != null)
                        sqlString.append(rs.getBigDecimal(i));

                    sqlString.append(",");
                    break;

                /*default:
                    if (obj != null)
                        sqlString.append(obj.toString());

                    sqlString.append(",");
                    break;*/
            }
        }while (rs.next());
    }