Example usage for java.sql ResultSetMetaData getColumnName

List of usage examples for java.sql ResultSetMetaData getColumnName

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSetMetaData
  6. getColumnName

Introduction

In this page you can find the example usage for java.sql ResultSetMetaData getColumnName.

Prototype

String getColumnName(int column) throws SQLException;

Source Link

Document

Get the designated column's name.

Usage

From source file:mil.army.usace.data.dataquery.rdbms.RdbmsDataQuery.java

public <T> List<T> getRecordsAsSimpleCollection(ReturnType returnType, String sql, Class[] returnTypes,
        Object[] params) {/*from  w  w w  .  j a v a2s .c o m*/
    if (returnTypes.length == 0)
        returnTypes = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    ResultSetMetaData rsMetaData = null;
    try {
        List<T> records = new ArrayList<>();
        st = conn.prepareStatement(sql);
        setParams(st, params);
        rs = st.executeQuery();
        if (returnType == ReturnType.RECORDSET || returnType == ReturnType.RECORDSETMAP)
            rsMetaData = rs.getMetaData();
        while (rs.next()) {
            switch (returnType) {
            case ARRAYLIST:
                if (returnTypes != null)
                    records.add((T) ConversionUtility.convertType(rs.getObject(1), returnTypes[0]));
                else
                    records.add((T) rs.getObject(1));
                break;
            case ARRAYLIST_TUPLE:
                if (returnTypes != null)
                    records.add((T) new Tuple(ConversionUtility.convertType(rs.getObject(1), returnTypes[0]),
                            ConversionUtility.convertType(rs.getObject(2), returnTypes[1])));
                else
                    records.add((T) new Tuple(rs.getObject(1), rs.getObject(2)));
                break;
            case ARRAYLIST_TUPLE3:
                if (returnTypes != null)
                    records.add((T) new Tuple3(ConversionUtility.convertType(rs.getObject(1), returnTypes[0]),
                            ConversionUtility.convertType(rs.getObject(2), returnTypes[1]),
                            ConversionUtility.convertType(rs.getObject(3), returnTypes[2])));
                else
                    records.add((T) new Tuple3(rs.getObject(1), rs.getObject(2), rs.getObject(3)));
                break;
            case RECORDSET:
                ArrayList record = new ArrayList();
                for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
                    record.add((returnTypes == null) ? rs.getObject(i + 1)
                            : ConversionUtility.convertType(rs.getObject(i + 1), returnTypes[i]));
                }
                ((ArrayList) records).add(record);
                break;
            case RECORDSETMAP:
                HashMap<String, Object> recordmap = new HashMap<>();
                for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
                    recordmap.put(rsMetaData.getColumnName(i + 1), (returnTypes == null) ? rs.getObject(i + 1)
                            : ConversionUtility.convertType(rs.getObject(i + 1), returnTypes[i]));
                }
                ((ArrayList) records).add(recordmap);
                break;
            }
        }
        return records;
    } catch (Exception ex) {
        throw new DataQueryException(sql, null, ex);
    } finally {
        closeOnFinally(rs, st);
    }
}

From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java

private List<LogMessageData> toLogMessageDataInternal(ResultSet rs, List<LogMessageData> logMessageData) {

    try {/*  ww w. ja v a 2  s. c  o  m*/

        // we will need the column names.
        java.sql.ResultSetMetaData rsmd = rs.getMetaData();
        //loop through the ResultSet
        while (rs.next()) {

            //figure out how many columns there are
            int numColumns = rsmd.getColumnCount();
            String tableName = rsmd.getTableName(1);
            LogMessageData obj = null;
            if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_01_NAME)) {
                obj = new LogMessageData_Partition_01();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_02_NAME)) {
                obj = new LogMessageData_Partition_02();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_03_NAME)) {
                obj = new LogMessageData_Partition_03();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_04_NAME)) {
                obj = new LogMessageData_Partition_04();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_05_NAME)) {
                obj = new LogMessageData_Partition_05();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_06_NAME)) {
                obj = new LogMessageData_Partition_06();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_07_NAME)) {
                obj = new LogMessageData_Partition_07();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_08_NAME)) {
                obj = new LogMessageData_Partition_08();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_09_NAME)) {
                obj = new LogMessageData_Partition_09();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_10_NAME)) {
                obj = new LogMessageData_Partition_10();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_11_NAME)) {
                obj = new LogMessageData_Partition_11();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_12_NAME)) {
                obj = new LogMessageData_Partition_12();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_13_NAME)) {
                obj = new LogMessageData_Partition_13();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_14_NAME)) {
                obj = new LogMessageData_Partition_14();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_15_NAME)) {
                obj = new LogMessageData_Partition_15();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_16_NAME)) {
                obj = new LogMessageData_Partition_16();
            } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_17_NAME)) {
                obj = new LogMessageData_Partition_17();
            }

            // loop through all the columns 
            for (int i = 1; i < numColumns + 1; i++) {
                String column_name = rsmd.getColumnName(i);

                if (column_name.equals("ID")) {
                    obj.setId(rs.getBigDecimal(column_name).longValueExact());
                }

                if (column_name.equals("CONTENT")) {
                    obj.setContent(rs.getNString(column_name));
                }

                if (column_name.equals("LABEL")) {
                    obj.setLabel(rs.getNString(column_name));
                }

                if (column_name.equals("MIMETYPE")) {
                    obj.setMimeType(rs.getNString(column_name));
                }

                if (column_name.equals("MODIFIED")) {
                    obj.setModified(rs.getBoolean(column_name));
                }

                if (column_name.equals("CONTENTSIZE")) {
                    obj.setContentSize(rs.getBigDecimal(column_name).longValueExact());
                }

                if (column_name.equals("SEARCHABLE")) {
                    obj.setSearchable(rs.getBoolean(column_name));
                }

                if (column_name.equals("UTCLOCALTIMESTAMP")) {
                    obj.setUtcLocalTimeStamp(rs.getTimestamp(column_name));
                }

                if (column_name.equals("UTCSERVERTIMESTAMP")) {
                    obj.setUtcServerTimeStamp(rs.getTimestamp(column_name));
                }

                //                    if (column_name.equals("LOGMESSAGE_ID")) {
                //                        obj.setUtcServerTimeStamp(rs.getTimestamp(column_name));
                //                    }

            } //end foreach
            logMessageData.add(obj);
        } //end while
    } catch (Exception e) {
        e.printStackTrace();
    }

    return logMessageData;
}

From source file:com.egt.core.db.xdp.RecursoCachedRowSetDataProvider.java

private int getSqlColumnType(String fieldId) {
    try {//  ww  w.  j a  v a2s  .c o m
        ResultSetMetaData rsmd = this.getCachedRowSet().getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        for (int i = 1; i <= numberOfColumns; i++) {
            if (rsmd.getColumnName(i).equalsIgnoreCase(fieldId)) {
                return rsmd.getColumnType(i);
            }
        }
    } catch (Exception ex) {
        TLC.getBitacora().fatal(ex);
    }
    return java.sql.Types.OTHER;
}

From source file:com.egt.core.db.xdp.RecursoCachedRowSetDataProvider.java

public boolean isColumnaDisponible(String fieldId) {
    try {//from w w  w  .  j  a v  a2  s .c o  m
        ResultSetMetaData rsmd = this.getCachedRowSet().getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        for (int i = 1; i <= numberOfColumns; i++) {
            if (rsmd.getColumnName(i).equalsIgnoreCase(fieldId)) {
                return true;
            }
        }
    } catch (Exception ex) {
        TLC.getBitacora().fatal(ex);
    }
    return false;
}

From source file:com.egt.core.db.xdp.RecursoCachedRowSetDataProvider.java

private boolean isNullableColumn(String fieldId) {
    try {//from  w  w w .  java  2s.com
        ResultSetMetaData rsmd = this.getCachedRowSet().getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        for (int i = 1; i <= numberOfColumns; i++) {
            if (rsmd.getColumnName(i).equalsIgnoreCase(fieldId)) {
                if (rsmd.isNullable(i) == ResultSetMetaData.columnNullable) {
                    return true;
                } else {
                    return false;
                }
            }
        }
    } catch (Exception ex) {
        TLC.getBitacora().fatal(ex);
    }
    return false;
}

From source file:edu.ku.brc.specify.conversion.ConvertVerifier.java

/**
 * /*from  w w w .  j a  v  a2  s  .c  om*/
 */
private void verifyLoanRetPreps() {

    newSQL = "SELECT l.LoanNumber, r.ReturnedDate, r.QuantityResolved, r.Remarks "
            + "FROM loan AS l Inner Join loanpreparation AS lp ON l.LoanID = lp.LoanID "
            + "LEFT Join loanreturnpreparation AS r ON lp.LoanPreparationID = r.LoanPreparationID "
            + "WHERE l.LoanNumber = '%s' ORDER BY lp.LoanPreparationID";

    oldSQL = "SELECT l.LoanNumber, r.`Date`, r.Quantity, r.Remarks "
            + "FROM loan AS l Inner Join loanphysicalobject AS lp ON l.LoanID = lp.LoanID "
            + "LEFT Join loanreturnphysicalobject AS r ON lp.LoanPhysicalObjectID = r.LoanPhysicalObjectID Left Join loanphysicalobject_LoanPhysicalObjectID AS lr ON lp.LoanPhysicalObjectID = lr.OldID "
            + "WHERE l.Category = 0 AND LoanNumber = '%s' ORDER BY lr.NewID ASC";

    String newSQLCnt = "SELECT COUNT(ID) FROM (SELECT LoanReturnPreparationID AS ID, LoanNumber FROM loan AS l Inner Join loanpreparation AS lp ON l.LoanID = lp.LoanID Left Join loanreturnpreparation AS r ON lp.LoanPreparationID = r.LoanPreparationID WHERE LoanReturnPreparationID IS NOT NULL AND LoanNumber = '%s') T1";
    String oldSQLCnt = "SELECT COUNT(ID) FROM (SELECT LoanReturnPhysicalObjectID AS ID, LoanNumber FROM loan AS l Inner Join loanphysicalobject AS lp ON l.LoanID = lp.LoanID Left Join loanreturnphysicalobject AS r ON lp.LoanPhysicalObjectID = r.LoanPhysicalObjectID WHERE LoanReturnPhysicalObjectID IS NOT NULL AND LoanNumber = '%s') T1";

    log.info(newSQL);
    log.info(oldSQL);

    try {
        Vector<Object> loanNums = BasicSQLUtils.querySingleCol(oldDBConn,
                "SELECT LoanNumber FROM loan WHERE Category = 0");

        for (Object loanNum : loanNums) {
            int oldCnt = BasicSQLUtils.getCountAsInt(oldDBConn, String.format(oldSQLCnt, loanNum));
            int newCnt = BasicSQLUtils.getCountAsInt(newDBConn, String.format(newSQLCnt, loanNum));

            if (oldCnt != newCnt) {
                String msg = "For Loan Number[" + loanNum + " the number of New LoanPreps[" + newCnt
                        + "] doesn't match the old[" + oldCnt + "]";
                log.error(msg);
                tblWriter.logErrors(loanNum.toString(), msg);
            }

            getResultSets(String.format(oldSQL, loanNum), String.format(newSQL, loanNum));

            ResultSetMetaData rmd = newDBRS.getMetaData();
            while (true) {

                boolean hasOldRec = oldDBRS.next();
                boolean hasNewRec = newDBRS.next();

                if (!hasOldRec && !hasNewRec) {
                    break;
                }

                String newId = newDBRS.getString(1);
                String oldId = oldDBRS.getString(1);

                String oldNewIdStr = oldId + " / " + newId;

                int inx = 1;

                compareStrings(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // LoanNumber
                compareDates(oldNewIdStr, inx, inx);
                inx++; // Gift Date
                compareNumber(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // Quantity
                compareStrings(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // Remarks
            }

            oldDBRS.close();
            newDBRS.close();
        }

    } catch (Exception ex) {
        ex.printStackTrace();
    }

}

From source file:ProcessRequest.java

public void parseQueryResults(ResultSet rs, String table, OutputStream os, boolean append)
        throws SQLException, JSONException, IOException {
    //JSONArray resultJSONArray = new JSONArray();
    ResultSetMetaData rsmd = rs.getMetaData();
    int columns = rsmd.getColumnCount();
    rs.last();//from w  w w .  j av a2s  .  com
    int rows = rs.getRow();
    os.write(new String("total rows: " + rows).getBytes());
    rs.first();
    int rowCount = 0;
    while (rs.next()) {
        if (!rs.isFirst() || append) {
            os.write(new String(",\n").getBytes());
            os.write(new String("" + rowCount).getBytes());
        }
        if (rowCount >= 69)
            System.out.println("break point");
        rowCount++;
        JSONObject result = new JSONObject();
        JSONObject resultMeta = new JSONObject();
        resultMeta.put("table", table);
        result.put("metadata", resultMeta);
        for (int i = 1; i <= columns; i++) {
            //out.println("<td>"+rs.getString(i)+"</td>");
            int type = rsmd.getColumnType(i);
            //result.put(rsmd.getColumnName(i), rs.get)
            switch (type) {
            case Types.BIT:
                result.put(rsmd.getColumnName(i), rs.getBoolean(i));
                break;
            case Types.TINYINT:
                result.put(rsmd.getColumnName(i), rs.getInt(i));
                break;
            case Types.SMALLINT:
                result.put(rsmd.getColumnName(i), rs.getInt(i));
                break;
            case Types.INTEGER:
                //System.out.println(rsmd.getColumnName(i) + "  type: "+type);
                result.put(rsmd.getColumnName(i), rs.getInt(i));
                break;
            case Types.BIGINT:
                result.put(rsmd.getColumnName(i), rs.getInt(i));
                break;
            case Types.FLOAT:
                result.put(rsmd.getColumnName(i), rs.getFloat(i));
                break;
            case Types.REAL:
                result.put(rsmd.getColumnName(i), rs.getDouble(i));
                break;
            case Types.DOUBLE:
                result.put(rsmd.getColumnName(i), rs.getDouble(i));
                break;
            case Types.NUMERIC:
                result.put(rsmd.getColumnName(i), rs.getDouble(i));
                break;
            case Types.DECIMAL:
                result.put(rsmd.getColumnName(i), rs.getDouble(i));
                break;
            case Types.CHAR:
                result.put(rsmd.getColumnName(i), rs.getString(i));
                break;
            case Types.VARCHAR:
                result.put(rsmd.getColumnName(i), rs.getString(i));
                break;
            case Types.LONGVARCHAR:
                result.put(rsmd.getColumnName(i), rs.getString(i));
                break;
            case Types.DATE: {
                java.util.Date date = rs.getDate(i);
                result.put(rsmd.getColumnName(i), date.getTime());
                break;
            }
            case Types.TIME: {
                java.util.Date date = rs.getDate(i);
                result.put(rsmd.getColumnName(i), date.getTime());
                break;
            }
            case Types.TIMESTAMP: {
                java.util.Date date = rs.getDate(i);
                result.put(rsmd.getColumnName(i), date.getTime());
                break;
            }
            case Types.BINARY:
                result.put(rsmd.getColumnName(i), rs.getInt(i));
                break;
            case Types.VARBINARY:
                result.put(rsmd.getColumnName(i), rs.getInt(i));
                break;
            case Types.LONGVARBINARY:
                result.put(rsmd.getColumnName(i), rs.getLong(i));
                break;
            case Types.NULL:
                result.put(rsmd.getColumnName(i), "");
                break;
            case Types.BOOLEAN:
                result.put(rsmd.getColumnName(i), rs.getBoolean(i));
                break;
            case Types.ROWID:
                result.put(rsmd.getColumnName(i), rs.getInt(i));
                break;
            case Types.NCHAR:
                result.put(rsmd.getColumnName(i), rs.getString(i));
                break;
            case Types.NVARCHAR:
                result.put(rsmd.getColumnName(i), rs.getString(i));
                break;
            case Types.LONGNVARCHAR:
                result.put(rsmd.getColumnName(i), rs.getString(i));
                break;
            case Types.SQLXML:
            case Types.NCLOB:
            case Types.DATALINK:
            case Types.REF:
            case Types.OTHER:
            case Types.JAVA_OBJECT:
            case Types.DISTINCT:
            case Types.STRUCT:
            case Types.ARRAY:
            case Types.BLOB:
            case Types.CLOB:
            default:
                result.put(rsmd.getColumnName(i), rs.getString(i));
                break;
            }
        }
        //if(table.equals("Ticket"))
        //System.out.println(result.toString(5));
        //if(result.getInt("TicketNumber")==126868)
        //   System.out.println("break point");
        //resultJSONArray.put(result);
        os.write(result.toString(5).getBytes());
    }
    //return resultJSONArray;
}

From source file:edu.ku.brc.specify.conversion.ConvertVerifier.java

/**
 * /*from w ww  . j a  v a2 s.  com*/
 */
private void verifyGifts() {

    newSQL = "SELECT g.GiftNumber, g.GiftDate, g.Remarks, g.Number1, g.Number2, g.Text1, g.Text2, g.YesNo1, g.YesNo2, gp.Quantity, gp.DescriptionOfMaterial, gp.OutComments, gp.InComments "
            + "FROM gift AS g Inner Join giftpreparation AS gp ON g.GiftID = gp.GiftID WHERE g.GiftNumber = '%s' ORDER BY gp.GiftPreparationID";

    oldSQL = "SELECT g.LoanNumber, g.LoanDate, g.Remarks, g.Number1, g.Number2, g.Text1, g.Text2, g.YesNo1, g.YesNo2, "
            + "gp.Quantity, gp.DescriptionOfMaterial, gp.OutComments, gp.InComments "
            + "FROM loan AS g Inner Join loanphysicalobject AS gp ON g.LoanID = gp.LoanID Left Join loanphysicalobject_LoanPhysicalObjectID AS lr ON gp.LoanPhysicalObjectID = lr.OldID "
            + "WHERE g.Category = 1 AND g.LoanNumber = '%s' ORDER BY lr.NewID ASC";

    String newSQLCnt = "SELECT COUNT(*) FROM gift AS g Inner Join giftpreparation AS gp ON g.GiftID = gp.GiftID WHERE GiftNumber = '%s'";
    String oldSQLCnt = "SELECT COUNT(*) FROM loan AS g Inner Join loanphysicalobject AS gp ON g.LoanID = gp.LoanID WHERE g.Category = 1 AND g.LoanNumber = '%s'";

    log.info(newSQL);
    log.info(oldSQL);

    try {
        Vector<Object> loanNums = BasicSQLUtils.querySingleCol(oldDBConn,
                "SELECT LoanNumber FROM loan WHERE Category = 1"); // Gifts

        for (Object loanNum : loanNums) {
            int oldCnt = BasicSQLUtils.getCountAsInt(oldDBConn, String.format(oldSQLCnt, loanNum));
            int newCnt = BasicSQLUtils.getCountAsInt(newDBConn, String.format(newSQLCnt, loanNum));

            if (oldCnt != newCnt) {
                String msg = "For Loan Number[" + loanNum + " the number of New LoanPreps[" + newCnt
                        + "] doesn't match the old[" + oldCnt + "]";
                log.error(msg);
                tblWriter.logErrors(loanNum.toString(), msg);
            }

            getResultSets(String.format(oldSQL, loanNum), String.format(newSQL, loanNum));

            ResultSetMetaData rmd = newDBRS.getMetaData();
            while (true) {

                boolean hasOldRec = oldDBRS.next();
                boolean hasNewRec = newDBRS.next();

                if (!hasOldRec && !hasNewRec) {
                    break;
                }

                int newId = newDBRS.getInt(1);
                int oldId = oldDBRS.getInt(1);

                String oldNewIdStr = oldId + " / " + newId;// + " ("+oldDBRS.getInt(19)+" / "+newDBRS.getInt(19)+")";

                int inx = 1;

                compareStrings(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // Gift Number

                compareDates(oldNewIdStr, inx, inx);
                inx++; // Gift Date

                compareStrings(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // Remarks

                compareNumber(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // Number1
                compareNumber(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // Number2

                compareStrings(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // Test1
                compareStrings(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // Test2

                compareBoolean(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // YesNo1
                compareBoolean(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // YesNo2

                compareNumber(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // Qty
                compareStrings(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // DescOfMat
                compareStrings(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // OutComm
                compareStrings(oldNewIdStr, rmd.getColumnName(inx), inx, inx);
                inx++; // InComm
            }

            oldDBRS.close();
            newDBRS.close();
        }

    } catch (Exception ex) {
        ex.printStackTrace();
    }

}

From source file:edu.ku.brc.specify.conversion.ConvertVerifier.java

/**
 * //  www.  j a  v a  2s  .c  o m
 */
private void verifyLoans() {

    newSQL = "SELECT l.LoanNumber, l.LoanDate, l.IsClosed, l.CurrentDueDate, l.OriginalDueDate, l.DateClosed, l.Text1, l.Text2, l.Number1, l.Number2, l.YesNo1, l.YesNo2, "
            + "lp.Quantity, lp.DescriptionOfMaterial, lp.OutComments, lp.InComments, lp.QuantityResolved, lp.QuantityReturned, LoanPreparationID "
            + "FROM loan l Inner Join loanpreparation lp ON l.LoanID = lp.LoanID WHERE LoanNumber = '%s' ORDER BY LoanPreparationID";

    oldSQL = "SELECT l.LoanNumber, l.LoanDate, l.Closed, l.CurrentDueDate, l.OriginalDueDate, l.DateClosed, l.Text1, l.Text2, l.Number1, l.Number2, l.YesNo1, l.YesNo2, "
            + "lp.Quantity, lp.DescriptionOfMaterial, lp.OutComments, lp.InComments, lp.QuantityResolved, lp.QuantityReturned, lp.LoanPhysicalObjectID "
            + "FROM loan AS l Inner Join loanphysicalobject AS lp ON l.LoanID = lp.LoanID Left Join loanphysicalobject_LoanPhysicalObjectID AS lr ON lp.LoanPhysicalObjectID = lr.OldID "
            + "WHERE l.Category = 0 AND LoanNumber = '%s' ORDER BY lr.NewID ASC";

    String newSQLCnt = "SELECT COUNT(*) FROM loan l Inner Join loanpreparation lp ON l.LoanID = lp.LoanID WHERE LoanNumber = '%s'";
    String oldSQLCnt = "SELECT COUNT(*) FROM loan AS l Inner Join loanphysicalobject AS lp ON l.LoanID = lp.LoanID WHERE l.Category = 0 AND LoanNumber = '%s'";

    log.info(newSQL);
    log.info(oldSQL);

    try {
        Vector<Object> loanNums = BasicSQLUtils.querySingleCol(oldDBConn,
                "SELECT LoanNumber FROM loan WHERE Category = 0");

        for (Object loanNum : loanNums) {
            int oldCnt = BasicSQLUtils.getCountAsInt(oldDBConn, String.format(oldSQLCnt, loanNum));
            int newCnt = BasicSQLUtils.getCountAsInt(newDBConn, String.format(newSQLCnt, loanNum));

            if (oldCnt != newCnt) {
                String msg = "For Loan Number[" + loanNum + " the number of New LoanPreps[" + newCnt
                        + "] doesn't match the old[" + oldCnt + "]";
                log.error(msg);
                tblWriter.logErrors(loanNum.toString(), msg);
            }

            getResultSets(String.format(oldSQL, loanNum), String.format(newSQL, loanNum));

            ResultSetMetaData rmd = newDBRS.getMetaData();
            while (true) {

                boolean hasOldRec = oldDBRS.next();
                boolean hasNewRec = newDBRS.next();

                if (!hasOldRec && !hasNewRec) {
                    break;
                }

                String newId = newDBRS.getString(1);
                String oldId = oldDBRS.getString(1);

                String oldNewIdStr = oldId + " / " + newId + " (" + oldDBRS.getInt(19) + " / "
                        + newDBRS.getInt(19) + ")";

                compareStrings(oldNewIdStr, rmd.getColumnName(1), 1, 1);

                compareDates(oldNewIdStr, 2, 2);

                compareBoolean(oldNewIdStr, rmd.getColumnName(3), 3, 3);

                compareDates(oldNewIdStr, 4, 4);
                compareDates(oldNewIdStr, 5, 5);
                compareDates(oldNewIdStr, 6, 6);

                compareStrings(oldNewIdStr, rmd.getColumnName(7), 7, 7);
                compareStrings(oldNewIdStr, rmd.getColumnName(8), 8, 8);

                compareNumber(oldNewIdStr, rmd.getColumnName(9), 9, 9);
                compareNumber(oldNewIdStr, rmd.getColumnName(10), 10, 10);

                compareBoolean(oldNewIdStr, rmd.getColumnName(11), 11, 11);
                compareBoolean(oldNewIdStr, rmd.getColumnName(12), 12, 12);

                compareNumber(oldNewIdStr, rmd.getColumnName(13), 13, 13);

                compareStrings(oldNewIdStr, rmd.getColumnName(14), 14, 14);
                compareStrings(oldNewIdStr, rmd.getColumnName(15), 15, 15);
                compareStrings(oldNewIdStr, rmd.getColumnName(16), 16, 16);

                compareNumber(oldNewIdStr, rmd.getColumnName(17), 17, 17);
                compareNumber(oldNewIdStr, rmd.getColumnName(18), 18, 18);
            }

            oldDBRS.close();
            newDBRS.close();
        }

    } catch (Exception ex) {
        ex.printStackTrace();
    }

}

From source file:com.funambol.foundation.items.dao.PIMContactDAO.java

/**
 * Creates a ContactWrapper object from a ResultSet. Only the basic data are
 * set./*from   ww w.j av  a2 s  .  co  m*/
 *
 * @param wrapperId the UID of the wrapper object to be returned
 * @param rs the result of the execution of a proper SQL SELECT statement on
 *           the fnbl_pim_contact table, with the cursor before its first row
 * @return a newly created ContactWrapper initialized with the fields in the
 *         result set
 * @throws java.sql.SQLException
 * @throws NotFoundException
 */
private static ContactWrapper createContact(String wrapperId, ResultSet rs)
        throws SQLException, NotFoundException {

    if (!rs.next()) {
        throw new NotFoundException("No contact found.");
    }

    ResultSetMetaData rsmd = rs.getMetaData();
    ContactWrapper cw = null;
    Note note = null;
    Title title = null;

    String column = null;

    String userId = rs.getString(SQL_FIELD_USERID);

    Contact c = new Contact();
    cw = new ContactWrapper(wrapperId, userId, c);

    int columnCount = rsmd.getColumnCount();

    for (int i = 1; i <= columnCount; ++i) {

        column = rsmd.getColumnName(i);

        //
        // General
        //
        if (SQL_FIELD_ID.equalsIgnoreCase(column)) {
            // Does nothing: field already set at construction time
        } else if (SQL_FIELD_LAST_UPDATE.equalsIgnoreCase(column)) {
            cw.setLastUpdate(new Timestamp(rs.getLong(i)));
        } else if (SQL_FIELD_USERID.equalsIgnoreCase(column)) {
            // Does nothing: field already set at construction time
        } else if (SQL_FIELD_STATUS.equalsIgnoreCase(column)) {
            cw.setStatus(rs.getString(i).charAt(0));
        } else if (SQL_FIELD_PHOTO_TYPE.equalsIgnoreCase(column)) {
            short phType = rs.getShort(i);
            if (!rs.wasNull()) {
                cw.setPhotoType(Short.valueOf(phType));
            }
            //
            // contact details
            //
        } else if (SQL_FIELD_IMPORTANCE.equalsIgnoreCase(column)) {
            short importance = rs.getShort(i);
            if (!rs.wasNull()) {
                c.setImportance(Short.valueOf(importance));
            }
        } else if (SQL_FIELD_SENSITIVITY.equalsIgnoreCase(column)) {
            short sensitivity = rs.getShort(i);
            if (!rs.wasNull()) {
                c.setSensitivity(Short.valueOf(sensitivity));
            }
        } else if (SQL_FIELD_SUBJECT.equalsIgnoreCase(column)) {
            c.setSubject(rs.getString(i));
        } else if (SQL_FIELD_FOLDER.equalsIgnoreCase(column)) {
            c.setFolder(rs.getString(i));
            //
            // Personal details
            //
        } else if (SQL_FIELD_ANNIVERSARY.equalsIgnoreCase(column)) {
            c.getPersonalDetail().setAnniversary(rs.getString(i));
        } else if (SQL_FIELD_FIRST_NAME.equalsIgnoreCase(column)) {
            c.getName().getFirstName().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_MIDDLE_NAME.equalsIgnoreCase(column)) {
            c.getName().getMiddleName().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_LAST_NAME.equalsIgnoreCase(column)) {
            c.getName().getLastName().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_DISPLAY_NAME.equalsIgnoreCase(column)) {
            c.getName().getDisplayName().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_BIRTHDAY.equalsIgnoreCase(column)) {
            c.getPersonalDetail().setBirthday(rs.getString(i));
        } else if (SQL_FIELD_BODY.equalsIgnoreCase(column)) {
            String noteStr = rs.getString(i);
            if (noteStr != null) {
                note = new Note();
                note.setNoteType(FIELD_NOTE);
                note.setPropertyValue(noteStr);
                c.addNote(note);
            }
        } else if (SQL_FIELD_CATEGORIES.equalsIgnoreCase(column)) {
            c.getCategories().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_CHILDREN.equalsIgnoreCase(column)) {
            c.getPersonalDetail().setChildren(rs.getString(i));
        } else if (SQL_FIELD_HOBBIES.equalsIgnoreCase(column)) {
            c.getPersonalDetail().setHobbies(rs.getString(i));
        } else if (SQL_FIELD_GENDER.equalsIgnoreCase(column)) {
            c.getPersonalDetail().setGender(rs.getString(i));
        } else if (SQL_FIELD_INITIALS.equalsIgnoreCase(column)) {
            c.getName().getInitials().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_LANGUAGES.equalsIgnoreCase(column)) {
            c.setLanguages(rs.getString(i));
        } else if (SQL_FIELD_NICKNAME.equalsIgnoreCase(column)) {
            c.getName().getNickname().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_SPOUSE.equalsIgnoreCase(column)) {
            c.getPersonalDetail().setSpouse(rs.getString(i));
        } else if (SQL_FIELD_SUFFIX.equalsIgnoreCase(column)) {
            c.getName().getSuffix().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_TITLE.equalsIgnoreCase(column)) {
            c.getName().getSalutation().setPropertyValue(rs.getString(i));
            //
            // Business details
            //
        } else if (SQL_FIELD_ASSISTANT.equalsIgnoreCase(column)) {
            c.getBusinessDetail().setAssistant(rs.getString(i));
        } else if (SQL_FIELD_COMPANY.equalsIgnoreCase(column)) {
            c.getBusinessDetail().getCompany().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_COMPANIES.equalsIgnoreCase(column)) {
            c.getBusinessDetail().setCompanies(rs.getString(i));
        } else if (SQL_FIELD_DEPARTMENT.equalsIgnoreCase(column)) {
            c.getBusinessDetail().getDepartment().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_JOB_TITLE.equalsIgnoreCase(column)) {
            String titleStr = null;
            titleStr = rs.getString(i);
            if (titleStr != null) {
                title = new Title();
                title.setTitleType(FIELD_JOB_TITLE);
                title.setPropertyValue(titleStr);
                c.getBusinessDetail().addTitle(title);
            }
        } else if (SQL_FIELD_MANAGER.equalsIgnoreCase(column)) {
            c.getBusinessDetail().setManager(rs.getString(i));
        } else if (SQL_FIELD_MILEAGE.equalsIgnoreCase(column)) {
            c.setMileage(rs.getString(i));
        } else if (SQL_FIELD_OFFICE_LOCATION.equalsIgnoreCase(column)) {
            c.getBusinessDetail().setOfficeLocation(rs.getString(i));
        } else if (SQL_FIELD_PROFESSION.equalsIgnoreCase(column)) {
            c.getBusinessDetail().getRole().setPropertyValue(rs.getString(i));
        } else {
            throw new SQLException("Unhandled column: " + column);
        }
    }

    return cw;
}