Example usage for java.sql ResultSetMetaData getColumnClassName

List of usage examples for java.sql ResultSetMetaData getColumnClassName

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

Introduction

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

Prototype

String getColumnClassName(int column) throws SQLException;

Source Link

Document

Returns the fully-qualified name of the Java class whose instances are manufactured if the method ResultSet.getObject is called to retrieve a value from the column.

Usage

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

/**
 * @param oldNewIdStr/*from  ww w.ja  v  a 2s  . co  m*/
 * @param newColInx
 * @param oldColInx
 * @return
 * @throws SQLException
 */
private StatusType compareDates(final String oldNewIdStr, final int newColInx, final int oldColInx)
        throws SQLException {
    PartialDateConv datePair = new PartialDateConv();

    Object newObj = newDBRS.getObject(newColInx);
    Object oldObj = oldDBRS.getObject(oldColInx);

    ResultSetMetaData newRsmd = newDBRS.getMetaData();
    ResultSetMetaData oldRsmd = oldDBRS.getMetaData();

    String newColName = newRsmd.getColumnName(newColInx);
    String oldColName = oldRsmd.getColumnName(oldColInx);

    if (newObj == null) {
        String clsName = newRsmd.getColumnClassName(newColInx);

        if (compareTo6DBs) {
            if (!clsName.equals("java.sql.Date") || oldObj != null) {
                String msg = "New Value was null and shouldn't have been for Key Value New  Field ["
                        + newColName + "] [" + oldObj + "]";
                log.error(msg);
                tblWriter.logErrors(newColName, msg);
                return StatusType.NEW_VAL_NULL;
            }

        } else if (oldObj != null) {
            if (oldObj instanceof Number && ((Number) oldObj).intValue() == 0) {
                return StatusType.COMPARE_OK;

            } else if (!clsName.equals("java.sql.Date")
                    || (!(oldObj instanceof String) && ((Number) oldObj).intValue() != 0)) {
                String msg = "New Value was null and shouldn't have been for Key Value New Field [" + newColName
                        + "] [" + oldObj + "]";
                log.error(msg);
                tblWriter.logErrors(newColName, msg);
                return StatusType.NEW_VAL_NULL;
            }
        } else {
            return StatusType.COMPARE_OK;
        }
    }

    StringBuilder errSB = new StringBuilder();

    //System.out.println(newObj.getClass().getName()+"  "+oldObj.getClass().getName());

    if (newObj instanceof java.sql.Date) {
        boolean isPartialDate = false;
        Byte partialDateType = null;
        if (StringUtils.contains(newRsmd.getColumnName(newColInx + 1), "DatePrecision")) {
            partialDateType = newDBRS.getByte(newColInx);
            isPartialDate = true;
        }

        if (compareTo6DBs) {
            Object dateObj = oldDBRS.getObject(oldColInx);

            boolean isPartialDate2 = false;
            Byte partialDateType2 = null;
            if (StringUtils.contains(oldRsmd.getColumnName(oldColInx + 1), "DatePrecision")) {
                partialDateType2 = newDBRS.getByte(oldColInx);
                isPartialDate2 = true;

            } else {
                log.error("Next isn't DatePrecision and can't be!");
                tblWriter.logErrors(oldNewIdStr, errSB.toString());
            }

            if (!newObj.equals(dateObj) || (isPartialDate2 && !partialDateType2.equals(partialDateType))) {
                errSB.insert(0, oldColName + "  ");
                errSB.append("[");
                errSB.append(datePair);
                errSB.append("][");
                errSB.append(dateFormatter.format((Date) newObj));
                errSB.append("] oldDate[");
                errSB.append(dateFormatter.format((Date) dateObj));
                errSB.append("]");
                log.error(errSB.toString());
                tblWriter.logErrors(oldNewIdStr, errSB.toString());
                return StatusType.BAD_DATE;
            }

        } else {
            int oldIntDate = oldDBRS.getInt(oldColInx);
            if (oldIntDate == 0) {
                return StatusType.NO_OLD_REC;
            }

            BasicSQLUtils.getPartialDate(oldIntDate, datePair, false);

            if (partialDateType != null) {
                if (Byte.parseByte(datePair.getPartial()) != partialDateType.byteValue()) {
                    errSB.append("Partial Dates Type do not match. Old[" + datePair.getPartial() + "]  New ["
                            + partialDateType.byteValue() + "]");
                    // error partial dates don't match
                }
            }

            Calendar cal = Calendar.getInstance();
            cal.setTime((Date) newObj);

            int year = Integer.parseInt(datePair.getDateStr().substring(0, 4));
            int mon = Integer.parseInt(datePair.getDateStr().substring(5, 7));
            int day = Integer.parseInt(datePair.getDateStr().substring(8, 10));

            if (mon > 0)
                mon--;

            boolean isYearOK = true;

            int yr = cal.get(Calendar.YEAR);
            if (year != yr) {
                errSB.append("Year mismatch Old[" + year + "]  New [" + yr + "] ");
                isYearOK = false;
            }

            if (mon != cal.get(Calendar.MONTH)) {
                errSB.append("Month mismatch Old[" + mon + "]  New [" + cal.get(Calendar.MONTH) + "] ");
            }

            if (day != cal.get(Calendar.DAY_OF_MONTH)) {
                errSB.append("Day mismatch Old[" + day + "]  New [" + cal.get(Calendar.DAY_OF_MONTH) + "] ");
            }

            if (errSB.length() > 0 && (!isYearOK || !isPartialDate)) {
                errSB.insert(0, oldColName + "  ");
                errSB.append("[");
                errSB.append(datePair);
                errSB.append("][");
                errSB.append(dateFormatter.format((Date) newObj));
                errSB.append("]");
                log.error(errSB.toString());
                tblWriter.logErrors(oldNewIdStr, errSB.toString());
                return StatusType.BAD_DATE;
            }
        }
    }

    return StatusType.COMPARE_OK;
}

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

/**
 * @param oldSQL/*w  ww .j  a v  a 2s . c  om*/
 * @param newSQL
 * @return
 * @throws SQLException
 */
private StatusType compareRecords(final String desc, final String oldCatNumArg, final String newCatNumArg,
        final String oldSQLArg, final String newSQLArg, final boolean nullsAreOK, final boolean notRetarded)
        throws SQLException {
    boolean dbg = false;
    if (dbg) {
        System.out.println(oldSQLArg);
        System.out.println(newSQLArg);
    }
    if (dbg) {
        System.out.println("\n" + desc);
        dump(desc, oldDBConn, compareTo6DBs ? newSQLArg : oldSQLArg);
        dump(desc, newDBConn, newSQLArg);
    }

    String oldCatNum = oldCatNumArg;
    String newCatNum = newCatNumArg;
    if (compareTo6DBs) {
        oldCatNum = newCatNumArg;
    }

    if (notRetarded) {
        getResultSetsNotRetarded(oldSQLArg, newSQLArg, oldCatNum, newCatNum);
    } else {
        getResultSets(oldSQLArg, newSQLArg);
    }

    try {

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

        if (!hasOldRec && !hasNewRec) {
            return StatusType.COMPARE_OK;
        }

        if (!hasOldRec) {
            if (nullsAreOK) {
                log.error(desc + " - No Old Record for [" + oldCatNum + "]");
                tblWriter.logErrors(oldCatNum, "No Old Record");
                return StatusType.NO_OLD_REC;
            }
            return StatusType.COMPARE_OK;
        }
        if (!hasNewRec) {
            log.error(desc + " - No New Record for [" + newCatNum + "]");
            tblWriter.logErrors(newCatNum, "No New Record");
            return StatusType.NO_NEW_REC;
        }

        //check number of rows, if not equal don't try to compare
        oldDBRS.last();
        newDBRS.last();
        if (oldDBRS.getRow() != newDBRS.getRow()) {
            String msg = desc + " Cat Num [" + oldCatNum + "]: Sp5 DB has " + oldDBRS.getRow()
                    + " related records. Sp6 DB has " + newDBRS.getRow();
            log.error(msg);
            tblWriter.logErrors(newCatNum, msg);
            return oldDBRS.getRow() < newDBRS.getRow() ? StatusType.NO_NEW_REC : StatusType.NO_OLD_REC;
        }
        oldDBRS.first();
        newDBRS.first();

        String oldNewIdStr = oldCatNum + " / " + newCatNum;

        boolean checkForAgent = newSQL.indexOf("a.LastName") > -1;

        ResultSetMetaData oldRsmd = oldDBRS.getMetaData();
        ResultSetMetaData newRsmd = newDBRS.getMetaData();

        PartialDateConv datePair = new PartialDateConv();
        Calendar cal = Calendar.getInstance();
        StringBuilder errSB = new StringBuilder();

        while (hasNewRec && hasOldRec) {
            errSB.setLength(0);

            int oldColInx = 0;
            int newColInx = 0;
            String idMsgStr = "";

            int numCols = newRsmd.getColumnCount();

            for (int col = 0; col < numCols; col++) {
                newColInx++;
                oldColInx++;

                if (dbg) {
                    System.out.println("\ncol       " + col + " / " + oldRsmd.getColumnCount());
                    System.out.println("newColInx " + newColInx);
                    System.out.println("oldColInx " + oldColInx);
                    System.out.println(oldRsmd.getColumnName(oldColInx));
                    System.out.println(newRsmd.getColumnName(newColInx));
                }

                Object newObj = newDBRS.getObject(newColInx);
                Object oldObj = oldDBRS.getObject(oldColInx);

                if (oldObj == null && newObj == null) {
                    String colName = newRsmd.getColumnName(newColInx);

                    if (StringUtils.contains(colName, "Date")
                            && StringUtils.contains(newRsmd.getColumnName(newColInx + 1), "DatePrecision")) {
                        newColInx++;
                        numCols--;
                        if (compareTo6DBs)
                            oldColInx++;
                    }
                    continue;
                }

                if (col == 0) {
                    idMsgStr = String.format(" - Rec Ids[%s / %s] ", (oldObj != null ? oldObj : -1),
                            (newObj != null ? newObj : -1));
                    continue;
                }

                String oldColName = oldRsmd.getColumnName(oldColInx);
                if (oldColName.equals("PreparationMethod") && newObj != null) {
                    String newObjStr = newObj.toString();
                    if ((oldObj == null && !newObjStr.equalsIgnoreCase("Misc"))
                            || (oldObj != null && !newObjStr.equalsIgnoreCase(oldObj.toString()))) {
                        String msg = idMsgStr + "Old Value was null and shouldn't have been for Old CatNum ["
                                + oldCatNum + "] Field [" + oldColName + "] oldObj[" + oldObj + "] newObj ["
                                + newObj + "]";
                        log.error(desc + " - " + msg);
                        tblWriter.logErrors(oldCatNum, msg);
                        return StatusType.OLD_VAL_NULL;
                    }
                    continue;
                }

                if (oldObj == null && !StringUtils.contains(oldColName, "LastName")) {
                    if (!oldColName.equals("PreparationMethod") || !newObj.equals("Misc")) {
                        String msg = idMsgStr + "Old Value was null and shouldn't have been for Old CatNum ["
                                + oldCatNum + "] Field [" + oldColName + "]  New Val[" + newObj + "]";
                        log.error(desc + " - " + msg);
                        tblWriter.logErrors(oldCatNum, msg);
                        return StatusType.OLD_VAL_NULL;
                    }
                }

                if (newObj == null) {
                    String clsName = newRsmd.getColumnClassName(newColInx);
                    String colName = newRsmd.getColumnName(newColInx);

                    if (compareTo6DBs) {
                        if (!clsName.equals("java.sql.Date") || oldObj != null) {
                            String msg = "New Value was null and shouldn't have been for Key Value New CatNo["
                                    + newCatNum + "] Field [" + colName + "] [" + oldObj + "]";
                            log.error(desc + " - " + msg);
                            tblWriter.logErrors(newCatNum, msg);
                            return StatusType.NEW_VAL_NULL;
                        }

                    } else {
                        if (!clsName.equals("java.sql.Date")
                                || (!(oldObj instanceof String) && ((Number) oldObj).intValue() != 0)) {
                            String msg = "New Value was null and shouldn't have been for Key Value New CatNo["
                                    + newCatNum + "] Field [" + colName + "] [" + oldObj + "]";
                            log.error(desc + " - " + msg);
                            if (tblWriter != null && newCatNum != null && msg != null)
                                tblWriter.logErrors(newCatNum, msg);
                            dbg = true;
                            return StatusType.NEW_VAL_NULL;
                        }
                    }

                    if (StringUtils.contains(colName, "Date")
                            && StringUtils.contains(newRsmd.getColumnName(newColInx + 1), "DatePrecision")) {
                        newColInx++;
                        numCols--;
                        if (compareTo6DBs)
                            oldColInx++;
                    }
                    continue;
                }

                //String colName = newRsmd.getColumnName(col);
                //System.out.println(newObj.getClass().getName()+"  "+oldObj.getClass().getName());

                if (newObj instanceof java.sql.Date) {
                    boolean isPartialDate = false;
                    Byte partialDateType = null;
                    if (StringUtils.contains(newRsmd.getColumnName(newColInx + 1), "DatePrecision")) {
                        newColInx++;
                        numCols--;
                        partialDateType = newDBRS.getByte(newColInx);
                        isPartialDate = true;
                    }

                    if (compareTo6DBs) {
                        Object dateObj = oldDBRS.getObject(oldColInx);

                        boolean isPartialDate2 = false;
                        Byte partialDateType2 = null;
                        if (StringUtils.contains(oldRsmd.getColumnName(oldColInx + 1), "DatePrecision")) {
                            oldColInx++;
                            partialDateType2 = newDBRS.getByte(oldColInx);
                            isPartialDate2 = true;

                        } else {
                            log.error("Next isn't DatePrecision and can't be!");
                            tblWriter.logErrors(oldNewIdStr, errSB.toString());
                        }

                        if (!newObj.equals(dateObj)
                                || (isPartialDate2 && !partialDateType2.equals(partialDateType))) {
                            errSB.insert(0, oldColName + "  ");
                            errSB.append("[");
                            errSB.append(datePair);
                            errSB.append("][");
                            errSB.append(dateFormatter.format((Date) newObj));
                            errSB.append("] oldDate[");
                            errSB.append(dateFormatter.format((Date) dateObj));
                            errSB.append("]");
                            log.error(errSB.toString());
                            tblWriter.logErrors(oldNewIdStr, errSB.toString());
                            return StatusType.BAD_DATE;
                        }

                    } else {
                        int oldIntDate = oldDBRS.getInt(oldColInx);
                        if (oldIntDate == 0) {
                            continue;
                        }

                        BasicSQLUtils.getPartialDate(oldIntDate, datePair, false);

                        if (partialDateType != null) {
                            boolean ok = StringUtils.isNotEmpty(datePair.getPartial())
                                    && StringUtils.isNumeric(datePair.getPartial());
                            if (!ok || (Byte.parseByte(datePair.getPartial()) != partialDateType.byteValue())) {
                                errSB.append("Partial Dates Type do not match. Old[" + datePair.getPartial()
                                        + "]  New [" + partialDateType.byteValue() + "]");
                                // error partial dates don't match
                            }
                        }

                        cal.setTime((Date) newObj);

                        if (StringUtils.isNotEmpty(datePair.getDateStr())
                                && !datePair.getDateStr().equalsIgnoreCase("null")) {
                            int year = Integer.parseInt(datePair.getDateStr().substring(0, 4));
                            int mon = Integer.parseInt(datePair.getDateStr().substring(5, 7));
                            int day = Integer.parseInt(datePair.getDateStr().substring(8, 10));

                            if (mon > 0)
                                mon--;

                            boolean isYearOK = true;

                            int yr = cal.get(Calendar.YEAR);
                            if (year != yr) {
                                errSB.append("Year mismatch Old[" + year + "]  New [" + yr + "] ");
                                isYearOK = false;
                            }

                            if (mon != cal.get(Calendar.MONTH)) {
                                errSB.append("Month mismatch Old[" + mon + "]  New [" + cal.get(Calendar.MONTH)
                                        + "] ");
                            }

                            if (day != cal.get(Calendar.DAY_OF_MONTH)) {
                                errSB.append("Day mismatch Old[" + day + "]  New ["
                                        + cal.get(Calendar.DAY_OF_MONTH) + "] ");
                            }

                            if (errSB.length() > 0 && (!isYearOK || !isPartialDate)) {
                                errSB.insert(0, oldColName + "  ");
                                errSB.append("[");
                                errSB.append(datePair);
                                errSB.append("][");
                                errSB.append(dateFormatter.format((Date) newObj));
                                errSB.append("]");
                                log.error(errSB.toString());
                                tblWriter.logErrors(oldNewIdStr, errSB.toString());
                                return StatusType.BAD_DATE;
                            }
                        } else {
                            //String msg = "Date contains the string 'NULL'";
                            //log.error(msg);
                            //tblWriter.logErrors(oldNewIdStr, msg);
                            //return StatusType.BAD_DATE;
                        }
                    }
                } else if (newObj instanceof Float || newObj instanceof Double) {
                    String s1 = String.format("%10.5f",
                            newObj instanceof Float ? (Float) newObj : (Double) newObj);
                    String s2 = String.format("%10.5f",
                            oldObj instanceof Float ? (Float) oldObj : (Double) oldObj);
                    if (!s1.equals(s2)) {
                        String msg = idMsgStr + "Columns don't compare[" + s1 + "][" + s2 + "]  ["
                                + newRsmd.getColumnName(col) + "][" + oldRsmd.getColumnName(oldColInx) + "]";
                        log.error(desc + " - " + msg);
                        tblWriter.logErrors(oldNewIdStr, msg);
                        return StatusType.NO_COMPARE;
                    }

                } else {
                    String newColName = newRsmd.getColumnName(newColInx);
                    if (checkForAgent && StringUtils.contains(newColName, "LastName")) {
                        String lastName = oldDBRS.getString(oldColInx);
                        String agentName = oldDBRS.getString(oldColInx + 1); // The 'Name' Column
                        String newLastName = newDBRS.getString(newColInx);
                        if (!newLastName.equals(lastName) && !newLastName.equals(agentName)) {
                            String msg = idMsgStr + "Name Columns don't compare[" + newObj + "][" + oldObj
                                    + "]  [" + newColName + "][" + oldColName + "]";
                            log.error(desc + " - " + msg);
                            tblWriter.logErrors(oldNewIdStr, msg);
                            log.error(oldSQLArg + "\n" + newSQLArg);
                            return StatusType.NO_COMPARE;
                        }

                    } else if (StringUtils.contains(newColName, "YesNo")) {
                        boolean yesNoNew = newDBRS.getBoolean(newColInx);
                        boolean yesNoOld = oldDBRS.getInt(oldColInx) != 0;

                        if (yesNoNew != yesNoOld) {
                            String msg = idMsgStr + "Columns don't Cat Num[" + oldCatNum + "] compare["
                                    + yesNoNew + "][" + yesNoOld + "]  [" + newColName + "][" + oldColName
                                    + "]";
                            log.error(desc + " - " + msg);
                            tblWriter.logErrors(oldNewIdStr, msg);
                            return StatusType.NO_COMPARE;
                        }

                    } else if (!newObj.equals(oldObj)) {
                        String msg = idMsgStr + "Columns don't Cat Num[" + oldCatNum + "] compare[" + newObj
                                + "][" + oldObj + "]  [" + newColName + "][" + oldColName + "]";
                        log.error(desc + " - " + msg);
                        tblWriter.logErrors(oldNewIdStr, msg);
                        return StatusType.NO_COMPARE;

                        /*boolean isOK = false;
                        if (oldObj instanceof String)
                        {
                        String oldStr = (String)oldObj;
                        String newStr = (String)newObj;
                        String lof    = "\\r\\n";
                        int    inx    = newStr.indexOf(lof);
                        if (inx > -1)
                        {
                            String tok = oldStr.substring(0, inx);
                            if (newStr.equals(tok))
                            {
                                isOK = true;
                            }
                        }
                        }
                        if (!isOK)
                        {
                        log.error(desc+ " - Columns don't compare["+newObj+"]["+oldObj+"]  ["+newRsmd.getColumnName(newColInx)+"]["+oldRsmd.getColumnName(oldColInx)+"]");
                        return false;
                        }*/
                    }
                }
            }

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

            if (!hasOldRec && !hasNewRec) {
                return StatusType.COMPARE_OK;
            }

            if (!hasOldRec) {
                log.error(desc + idMsgStr + " - No Old Record for [" + oldCatNum + "]");
                tblWriter.logErrors(oldNewIdStr, "No Old Record for [" + oldCatNum + "]");
                return StatusType.NO_OLD_REC;
            }
            if (!hasNewRec) {
                log.error(desc + idMsgStr + " No New Record for [" + newCatNum + "]");
                tblWriter.logErrors(oldNewIdStr, "No New Record for [" + newCatNum + "]");
                return StatusType.NO_NEW_REC;
            }
        }
    } finally {
        doneWithRS();
    }

    return StatusType.COMPARE_OK;
}