Example usage for java.sql ResultSetMetaData 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:org.apache.hive.jdbc.TestJdbcDriver2.java

@Test
public void testMetaDataGetColumns() throws SQLException {
    Map<String[], Integer> tests = new HashMap<String[], Integer>();
    tests.put(new String[] { "testhivejdbcdriver\\_table", null }, 2);
    tests.put(new String[] { "testhivejdbc%", null }, 7);
    tests.put(new String[] { "testhiveJDBC%", null }, 7);
    tests.put(new String[] { "%jdbcdriver\\_table", null }, 2);
    tests.put(new String[] { "%jdbcdriver\\_table%", "under\\_col" }, 1);
    //    tests.put(new String[]{"%jdbcdriver\\_table%", "under\\_COL"}, 1);
    tests.put(new String[] { "%jdbcdriver\\_table%", "under\\_co_" }, 1);
    tests.put(new String[] { "%jdbcdriver\\_table%", "under_col" }, 1);
    tests.put(new String[] { "%jdbcdriver\\_table%", "und%" }, 1);
    tests.put(new String[] { "%jdbcdriver\\_table%", "%" }, 2);
    tests.put(new String[] { "%jdbcdriver\\_table%", "_%" }, 2);

    for (String[] checkPattern : tests.keySet()) {
        ResultSet rs = con.getMetaData().getColumns(null, null, checkPattern[0], checkPattern[1]);

        // validate the metadata for the getColumns result set
        ResultSetMetaData rsmd = rs.getMetaData();
        assertEquals("TABLE_CAT", rsmd.getColumnName(1));

        int cnt = 0;
        while (rs.next()) {
            String columnname = rs.getString("COLUMN_NAME");
            int ordinalPos = rs.getInt("ORDINAL_POSITION");
            switch (cnt) {
            case 0:
                assertEquals("Wrong column name found", "under_col", columnname);
                assertEquals("Wrong ordinal position found", ordinalPos, 1);
                break;
            case 1:
                assertEquals("Wrong column name found", "value", columnname);
                assertEquals("Wrong ordinal position found", ordinalPos, 2);
                break;
            default:
                break;
            }/*from   www .j  a  va2s.c  om*/
            cnt++;
        }
        rs.close();
        assertEquals("Found less columns then we test for.", tests.get(checkPattern).intValue(), cnt);
    }
}

---

From source file:com.linkedin.pinot.integration.tests.BaseClusterIntegrationTest.java

/**
 * Run equivalent Pinot and H2 query and compare the results.
 * <p>LIMITATIONS:/*from  w  w w.  ja va 2 s .co m*/
 * <ul>
 *   <li>Skip comparison for selection and aggregation group-by when H2 results are too large to exhaust.</li>
 *   <li>Do not examine the order of result records.</li>
 * </ul>
 *
 * @param pqlQuery Pinot PQL query.
 * @param sqlQueries H2 SQL queries.
 * @throws Exception
 */
protected void runQuery(String pqlQuery, @Nullable List<String> sqlQueries) throws Exception {
    try {
        _queryCount++;

        // Run the query.
        // TODO Use Pinot client API for this
        JSONObject response = postQuery(pqlQuery);

        // Check exceptions.
        JSONArray exceptions = response.getJSONArray("exceptions");
        if (exceptions.length() > 0) {
            String failureMessage = "Got exceptions: " + exceptions;
            failure(pqlQuery, sqlQueries, failureMessage, null);
            return;
        }

        // Check total docs.
        long numTotalDocs = response.getLong("totalDocs");
        if (numTotalDocs != TOTAL_DOCS) {
            String failureMessage = "Number of total documents does not match, expected: " + TOTAL_DOCS
                    + ", got: " + numTotalDocs;
            failure(pqlQuery, sqlQueries, failureMessage, null);
            return;
        }

        // Skip comparison if SQL queries not specified.
        if (sqlQueries == null) {
            return;
        }

        // Check results.
        Statement h2statement = _connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        int numDocsScanned = response.getInt("numDocsScanned");
        if (response.has("aggregationResults")) {
            // Aggregation and Group-by results.

            // Get results type.
            JSONArray aggregationResultsArray = response.getJSONArray("aggregationResults");
            int numAggregationResults = aggregationResultsArray.length();
            int numSqlQueries = sqlQueries.size();
            if (numAggregationResults != numSqlQueries) {
                String failureMessage = "Number of aggregation results: " + numAggregationResults
                        + " does not match number of SQL queries: " + numSqlQueries;
                failure(pqlQuery, sqlQueries, failureMessage);
            }
            JSONObject firstAggregationResult = aggregationResultsArray.getJSONObject(0);

            if (firstAggregationResult.has("value")) {
                // Aggregation results.

                // Check over all aggregation functions.
                for (int i = 0; i < numAggregationResults; i++) {
                    // Get expected value for the aggregation.
                    h2statement.execute(sqlQueries.get(i));
                    ResultSet sqlResultSet = h2statement.getResultSet();
                    sqlResultSet.first();
                    String sqlValue = sqlResultSet.getString(1);

                    // If SQL value is null, it means no record selected in H2.
                    if (sqlValue == null) {
                        // Check number of documents scanned is 0.
                        if (numDocsScanned != 0) {
                            String failureMessage = "No record selected in H2 but number of records selected in Pinot: "
                                    + numDocsScanned;
                            failure(pqlQuery, sqlQueries, failureMessage);
                        }
                        // Skip further comparison.
                        return;
                    }

                    // Get actual value for the aggregation.
                    String pqlValue = aggregationResultsArray.getJSONObject(i).getString("value");

                    // Fuzzy compare expected value and actual value.
                    double expectedValue = Double.parseDouble(sqlValue);
                    double actualValue = Double.parseDouble(pqlValue);
                    if (Math.abs(actualValue - expectedValue) >= 1.0) {
                        String failureMessage = "Value: " + i + " does not match, expected: " + sqlValue
                                + ", got: " + pqlValue;
                        failure(pqlQuery, sqlQueries, failureMessage);
                        return;
                    }
                }
            } else if (firstAggregationResult.has("groupByResult")) {
                // Group-by results.

                // Get number of groups and number of group keys in each group.
                JSONArray firstGroupByResults = aggregationResultsArray.getJSONObject(0)
                        .getJSONArray("groupByResult");
                int numGroups = firstGroupByResults.length();
                // If no group-by result returned by Pinot, set numGroupKeys to 0 since no comparison needed.
                int numGroupKeys;
                if (numGroups == 0) {
                    numGroupKeys = 0;
                } else {
                    numGroupKeys = firstGroupByResults.getJSONObject(0).getJSONArray("group").length();
                }

                // Check over all aggregation functions.
                for (int i = 0; i < numAggregationResults; i++) {
                    // Get number of group keys.
                    JSONArray groupByResults = aggregationResultsArray.getJSONObject(i)
                            .getJSONArray("groupByResult");

                    // Construct expected result map from group keys to value.
                    h2statement.execute(sqlQueries.get(i));
                    ResultSet sqlResultSet = h2statement.getResultSet();
                    Map<String, String> expectedValues = new HashMap<>();
                    int sqlNumGroups;
                    for (sqlNumGroups = 0; sqlResultSet.next()
                            && sqlNumGroups < MAX_COMPARISON_LIMIT; sqlNumGroups++) {
                        if (numGroupKeys != 0) {
                            StringBuilder groupKey = new StringBuilder();
                            for (int groupKeyIndex = 1; groupKeyIndex <= numGroupKeys; groupKeyIndex++) {
                                // Convert boolean value to lower case.
                                groupKey.append(
                                        convertBooleanToLowerCase(sqlResultSet.getString(groupKeyIndex)))
                                        .append(' ');
                            }
                            expectedValues.put(groupKey.toString(), sqlResultSet.getString(numGroupKeys + 1));
                        }
                    }

                    if (sqlNumGroups == 0) {
                        // No record selected in H2.

                        // Check if no record selected in Pinot.
                        if (numGroups != 0) {
                            String failureMessage = "No group returned in H2 but number of groups returned in Pinot: "
                                    + numGroups;
                            failure(pqlQuery, sqlQueries, failureMessage);
                            return;
                        }

                        // Check if number of documents scanned is 0.
                        if (numDocsScanned != 0) {
                            String failureMessage = "No group returned in Pinot but number of records selected: "
                                    + numDocsScanned;
                            failure(pqlQuery, sqlQueries, failureMessage);
                        }

                        // Skip further comparison.
                        return;
                    } else if (sqlNumGroups < MAX_COMPARISON_LIMIT) {
                        // Only compare exhausted results.

                        // Check that all Pinot results are contained in the H2 results.
                        for (int resultIndex = 0; resultIndex < numGroups; resultIndex++) {
                            // Fetch Pinot group keys.
                            JSONObject groupByResult = groupByResults.getJSONObject(resultIndex);
                            JSONArray group = groupByResult.getJSONArray("group");
                            StringBuilder groupKeyBuilder = new StringBuilder();
                            for (int groupKeyIndex = 0; groupKeyIndex < numGroupKeys; groupKeyIndex++) {
                                groupKeyBuilder.append(group.getString(groupKeyIndex)).append(' ');
                            }
                            String groupKey = groupKeyBuilder.toString();

                            // Check if Pinot group keys contained in H2 results.
                            if (!expectedValues.containsKey(groupKey)) {
                                String failureMessage = "Group returned in Pinot but not in H2: " + groupKey;
                                failure(pqlQuery, sqlQueries, failureMessage);
                                return;
                            }

                            // Fuzzy compare expected value and actual value.
                            String sqlValue = expectedValues.get(groupKey);
                            String pqlValue = groupByResult.getString("value");
                            double expectedValue = Double.parseDouble(sqlValue);
                            double actualValue = Double.parseDouble(pqlValue);
                            if (Math.abs(actualValue - expectedValue) >= 1.0) {
                                String failureMessage = "Value: " + i + " does not match, expected: " + sqlValue
                                        + ", got: " + pqlValue + ", for group: " + groupKey;
                                failure(pqlQuery, sqlQueries, failureMessage);
                                return;
                            }
                        }
                    } else {
                        // Cannot get exhausted results.

                        // Skip further comparison.
                        LOGGER.debug("SQL: {} returned at least {} rows, skipping comparison.",
                                sqlQueries.get(0), MAX_COMPARISON_LIMIT);
                        return;
                    }
                }
            } else {
                // Neither aggregation or group-by results.
                String failureMessage = "Inside aggregation results, no aggregation or group-by results found";
                failure(pqlQuery, sqlQueries, failureMessage);
            }
        } else if (response.has("selectionResults")) {
            // Selection results.

            // Construct expected result set.
            h2statement.execute(sqlQueries.get(0));
            ResultSet sqlResultSet = h2statement.getResultSet();
            ResultSetMetaData sqlMetaData = sqlResultSet.getMetaData();

            Set<String> expectedValues = new HashSet<>();
            Map<String, String> reusableExpectedValueMap = new HashMap<>();
            Map<String, List<String>> reusableMultiValuesMap = new HashMap<>();
            List<String> reusableColumnOrder = new ArrayList<>();
            int numResults;
            for (numResults = 0; sqlResultSet.next() && numResults < MAX_COMPARISON_LIMIT; numResults++) {
                reusableExpectedValueMap.clear();
                reusableMultiValuesMap.clear();
                reusableColumnOrder.clear();

                int numColumns = sqlMetaData.getColumnCount();
                for (int i = 1; i <= numColumns; i++) {
                    String columnName = sqlMetaData.getColumnName(i);

                    // Handle null result and convert boolean value to lower case.
                    String columnValue = sqlResultSet.getString(i);
                    if (columnValue == null) {
                        columnValue = "null";
                    } else {
                        columnValue = convertBooleanToLowerCase(columnValue);
                    }

                    // Handle multi-value columns.
                    int length = columnName.length();
                    if (length > 5 && columnName.substring(length - 5, length - 1).equals("__MV")) {
                        // Multi-value column.
                        String multiValueColumnName = columnName.substring(0, length - 5);
                        List<String> multiValue = reusableMultiValuesMap.get(multiValueColumnName);
                        if (multiValue == null) {
                            multiValue = new ArrayList<>();
                            reusableMultiValuesMap.put(multiValueColumnName, multiValue);
                            reusableColumnOrder.add(multiValueColumnName);
                        }
                        multiValue.add(columnValue);
                    } else {
                        // Single-value column.
                        reusableExpectedValueMap.put(columnName, columnValue);
                        reusableColumnOrder.add(columnName);
                    }
                }

                // Add multi-value column results to the expected values.
                // The reason for this step is that Pinot does not maintain order of elements in multi-value columns.
                for (Map.Entry<String, List<String>> entry : reusableMultiValuesMap.entrySet()) {
                    List<String> multiValue = entry.getValue();
                    Collections.sort(multiValue);
                    reusableExpectedValueMap.put(entry.getKey(), multiValue.toString());
                }

                // Build expected value String.
                StringBuilder expectedValue = new StringBuilder();
                for (String column : reusableColumnOrder) {
                    expectedValue.append(column).append(':').append(reusableExpectedValueMap.get(column))
                            .append(' ');
                }

                expectedValues.add(expectedValue.toString());
            }

            JSONObject selectionColumnsAndResults = response.getJSONObject("selectionResults");
            JSONArray selectionColumns = selectionColumnsAndResults.getJSONArray("columns");
            JSONArray selectionResults = selectionColumnsAndResults.getJSONArray("results");
            int numSelectionResults = selectionResults.length();

            if (numResults == 0) {
                // No record selected in H2.

                // Check if no record selected in Pinot.
                if (numSelectionResults != 0) {
                    String failureMessage = "No record selected in H2 but number of records selected in Pinot: "
                            + numSelectionResults;
                    failure(pqlQuery, sqlQueries, failureMessage);
                    return;
                }

                // Check if number of documents scanned is 0.
                if (numDocsScanned != 0) {
                    String failureMessage = "No selection result returned in Pinot but number of records selected: "
                            + numDocsScanned;
                    failure(pqlQuery, sqlQueries, failureMessage);
                }
            } else if (numResults < MAX_COMPARISON_LIMIT) {
                // Only compare exhausted results.

                // Check that Pinot results are contained in the H2 results.
                int numColumns = selectionColumns.length();
                for (int i = 0; i < numSelectionResults; i++) {
                    // Build actual value String.
                    StringBuilder actualValueBuilder = new StringBuilder();
                    JSONArray selectionResult = selectionResults.getJSONArray(i);

                    for (int columnIndex = 0; columnIndex < numColumns; columnIndex++) {
                        // Convert column name to all uppercase to make it compatible with H2.
                        String columnName = selectionColumns.getString(columnIndex).toUpperCase();

                        Object columnResult = selectionResult.get(columnIndex);
                        if (columnResult instanceof JSONArray) {
                            // Multi-value column.
                            JSONArray columnResultsArray = (JSONArray) columnResult;
                            List<String> multiValue = new ArrayList<>();
                            int length = columnResultsArray.length();
                            for (int elementIndex = 0; elementIndex < length; elementIndex++) {
                                multiValue.add(columnResultsArray.getString(elementIndex));
                            }
                            for (int elementIndex = length; elementIndex < MAX_ELEMENTS_IN_MULTI_VALUE; elementIndex++) {
                                multiValue.add("null");
                            }
                            Collections.sort(multiValue);
                            actualValueBuilder.append(columnName).append(':').append(multiValue.toString())
                                    .append(' ');
                        } else {
                            // Single-value column.
                            actualValueBuilder.append(columnName).append(':').append((String) columnResult)
                                    .append(' ');
                        }
                    }
                    String actualValue = actualValueBuilder.toString();

                    // Check actual value in expected values set.
                    if (!expectedValues.contains(actualValue)) {
                        String failureMessage = "Selection result returned in Pinot but not in H2: "
                                + actualValue;
                        failure(pqlQuery, sqlQueries, failureMessage);
                        return;
                    }
                }
            } else {
                // Cannot get exhausted results.
                LOGGER.debug("SQL: {} returned at least {} rows, skipping comparison.", sqlQueries.get(0),
                        MAX_COMPARISON_LIMIT);
            }
        } else {
            // Neither aggregation or selection results.
            String failureMessage = "No aggregation or selection results found for query: " + pqlQuery;
            failure(pqlQuery, sqlQueries, failureMessage);
        }
    } catch (Exception e) {
        String failureMessage = "Caught exception while running query.";
        failure(pqlQuery, sqlQueries, failureMessage, e);
    }
}

---

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

@Test
public void testMetaDataGetCatalogs() throws SQLException {
    ResultSet rs = con.getMetaData().getCatalogs();
    ResultSetMetaData resMeta = rs.getMetaData();
    assertEquals(1, resMeta.getColumnCount());
    assertEquals("TABLE_CAT", resMeta.getColumnName(1));

    assertFalse(rs.next());//from  ww w.  j  a  v a  2 s .c  om
}

---

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

@Test
public void testMetaDataGetSchemas() throws SQLException {
    ResultSet rs = con.getMetaData().getSchemas();
    ResultSetMetaData resMeta = rs.getMetaData();
    assertEquals(2, resMeta.getColumnCount());
    assertEquals("TABLE_SCHEM", resMeta.getColumnName(1));
    assertEquals("TABLE_CATALOG", resMeta.getColumnName(2));

    assertTrue(rs.next());//  w  w w .  j a v a  2  s  .co m
    assertEquals("default", rs.getString(1));

    assertFalse(rs.next());
    rs.close();
}

---

From source file:org.alinous.plugin.derby.DerbyDataSource.java

private void fetchWithOffset(ResultSet rs, ResultSetMetaData metaData, List<Record> retList,
        LimitOffsetClause limit, PostContext context, VariableRepository provider, AdjustWhere adjWhere,
        TypeHelper helper) throws ExecutionException, SQLException {
    ISQLStatement limitStmt = limit.getLimit();
    ISQLStatement offsetStmt = limit.getOffset();

    int nLimit = 0, nOffset = 0;
    if (limitStmt != null && limitStmt.isReady(context, provider, adjWhere)) {
        String str = limitStmt.extract(context, provider, adjWhere, null, helper);
        nLimit = Integer.parseInt(str);
    }//  ww w. j av  a2s  . c  o m
    if (offsetStmt != null && offsetStmt.isReady(context, provider, adjWhere)) {
        String str = offsetStmt.extract(context, provider, adjWhere, null, helper);
        nOffset = Integer.parseInt(str);
    }

    if (offsetStmt != null) {
        rs.absolute(nOffset);
    }

    int count = 0;
    while (rs.next()) {
        if (count >= nLimit) {
            break;
        }
        count++;

        int cnt = metaData.getColumnCount();
        Record rec = new Record();
        for (int i = 0; i < cnt; i++) {
            String colName = metaData.getColumnName(i + 1).toUpperCase();
            String value = rs.getString(i + 1);

            int colType = metaData.getColumnType(i + 1);
            rec.addFieldValue(colName, value, colType);
        }

        retList.add(rec);
    }

}

---

From source file:org.cloudgraph.rdb.service.JDBCSupport.java

protected List<PropertyPair> executeInsertWithGeneratedKeys(PlasmaType type, StringBuilder sql,
        Map<String, PropertyPair> values, Connection con) {
    List<PropertyPair> resultKeys = new ArrayList<PropertyPair>();
    PreparedStatement statement = null;
    List<InputStream> streams = null;
    ResultSet generatedKeys = null;
    try {/*ww w. j  a  va  2 s . c o m*/

        if (log.isDebugEnabled()) {
            log.debug("execute: " + sql.toString());
            StringBuilder paramBuf = createParamDebug(values);
            log.debug("params: " + paramBuf.toString());
        }

        statement = con.prepareStatement(sql.toString(), PreparedStatement.RETURN_GENERATED_KEYS);

        for (PropertyPair pair : values.values()) {
            PlasmaProperty valueProp = pair.getProp();
            if (pair.getValueProp() != null)
                valueProp = pair.getValueProp();
            int jdbcType = converter.toJDBCDataType(valueProp, pair.getValue());
            Object jdbcValue = converter.toJDBCDataValue(valueProp, pair.getValue());
            if (jdbcType != Types.BLOB && jdbcType != Types.VARBINARY) {
                statement.setObject(pair.getColumn(), jdbcValue, jdbcType);
            } else {
                byte[] bytes = (byte[]) jdbcValue;
                long len = bytes.length;
                ByteArrayInputStream is = new ByteArrayInputStream(bytes);
                statement.setBinaryStream(pair.getColumn(), is, len);
                if (streams == null)
                    streams = new ArrayList<InputStream>();
                streams.add(is);
            }
        }

        statement.execute();
        generatedKeys = statement.getGeneratedKeys();
        ResultSetMetaData rsMeta = generatedKeys.getMetaData();
        int numcols = rsMeta.getColumnCount();
        if (log.isDebugEnabled())
            log.debug("returned " + numcols + " keys");

        if (generatedKeys.next()) {
            // FIXME; without metadata describing which properties
            // are actually a sequence, there is guess work
            // involved in matching the values returned
            // automatically from PreparedStatment as they
            // are anonymous in terms of the column names
            // making it impossible to match them to a metadata
            // property.
            List<Property> pkPropList = type.findProperties(KeyType.primary);
            if (pkPropList == null || pkPropList.size() == 0)
                throw new DataAccessException("no pri-key properties found for type '" + type.getName() + "'");
            if (pkPropList.size() > 1)
                throw new DataAccessException("multiple pri-key properties found for type '" + type.getName()
                        + "' - cannot map to generated keys");
            PlasmaProperty prop = (PlasmaProperty) pkPropList.get(0);
            // FIXME: need to find properties per column by physical name
            // alias
            // in case where multiple generated pri-keys
            for (int i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnName(i);
                if (log.isDebugEnabled())
                    log.debug("returned key column '" + columnName + "'");
                int columnType = rsMeta.getColumnType(i);
                Object value = converter.fromJDBCDataType(generatedKeys, i, columnType, prop);
                PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value);
                resultKeys.add(pair);
            }
        }
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        if (streams != null)
            try {
                for (InputStream stream : streams)
                    stream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
    }

    return resultKeys;
}

---

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

/**
 * Test the type returned for pre-created table type table and view type
 * table/* www . j av  a 2 s  . c o  m*/
 * @param tableTypeName expected table type
 * @param viewTypeName expected view type
 * @throws SQLException
 */
private void getTablesTest(String tableTypeName, String viewTypeName) throws SQLException {
    Map<String, Object[]> tests = new HashMap<String, Object[]>();
    tests.put("test%jdbc%", new Object[] { "testhivejdbcdriver_table", "testhivejdbcdriverpartitionedtable",
            "testhivejdbcdriverview" });
    tests.put("%jdbcdriver\\_table", new Object[] { "testhivejdbcdriver_table" });
    tests.put("testhivejdbcdriver\\_table", new Object[] { "testhivejdbcdriver_table" });
    tests.put("test_ivejdbcdri_er\\_table", new Object[] { "testhivejdbcdriver_table" });
    tests.put("test_ivejdbcdri_er_table", new Object[] { "testhivejdbcdriver_table" });
    tests.put("test_ivejdbcdri_er%table",
            new Object[] { "testhivejdbcdriver_table", "testhivejdbcdriverpartitionedtable" });
    tests.put("%jdbc%", new Object[] { "testhivejdbcdriver_table", "testhivejdbcdriverpartitionedtable",
            "testhivejdbcdriverview" });
    tests.put("", new Object[] {});

    for (String checkPattern : tests.keySet()) {
        ResultSet rs = con.getMetaData().getTables("default", null, checkPattern, null);
        ResultSetMetaData resMeta = rs.getMetaData();
        assertEquals(5, resMeta.getColumnCount());
        assertEquals("TABLE_CAT", resMeta.getColumnName(1));
        assertEquals("TABLE_SCHEM", resMeta.getColumnName(2));
        assertEquals("TABLE_NAME", resMeta.getColumnName(3));
        assertEquals("TABLE_TYPE", resMeta.getColumnName(4));
        assertEquals("REMARKS", resMeta.getColumnName(5));

        int cnt = 0;
        while (rs.next()) {
            String resultTableName = rs.getString("TABLE_NAME");
            assertEquals("Get by index different from get by name.", rs.getString(3), resultTableName);
            assertEquals("Excpected a different table.", tests.get(checkPattern)[cnt], resultTableName);
            String resultTableComment = rs.getString("REMARKS");
            assertTrue("Missing comment on the table.", resultTableComment.length() > 0);
            String tableType = rs.getString("TABLE_TYPE");
            if (resultTableName.endsWith("view")) {
                assertEquals("Expected a tabletype view but got something else.", viewTypeName, tableType);
            } else {
                assertEquals("Expected a tabletype table but got something else.", tableTypeName, tableType);
            }
            cnt++;
        }
        rs.close();
        assertEquals("Received an incorrect number of tables.", tests.get(checkPattern).length, cnt);
    }

    // only ask for the views.
    ResultSet rs = con.getMetaData().getTables("default", null, null, new String[] { viewTypeName });
    int cnt = 0;
    while (rs.next()) {
        cnt++;
    }
    rs.close();
    assertEquals("Incorrect number of views found.", 1, cnt);
}

---

From source file:com.glaf.dts.transform.MxTransformThread.java

@SuppressWarnings("unchecked")
public void run() {
    logger.debug(taskId + "----------------execution-----------------");
    TransformTask task = transformTaskService.getTransformTask(taskId);
    if (task != null) {
        if (task.getStatus() == 9 || task.getRetryTimes() > 3) {
            return;
        }//  w  w  w  .  j  a va2s.  c om
        task.setStartTime(new java.util.Date());
        task.setRetryTimes(task.getRetryTimes() + 1);
        task.setStatus(1);
        transformTaskService.save(task);
    }

    List<TableModel> resultList = new java.util.ArrayList<TableModel>();
    Map<String, Object> singleDataMap = new HashMap<String, Object>();
    Connection conn = null;
    PreparedStatement psmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    boolean success = true;
    long start = System.currentTimeMillis();
    logger.debug("start:" + DateUtils.getDateTime(new java.util.Date()));
    try {
        Database database = getDatabaseService().getDatabaseById(queryDefinition.getDatabaseId());
        if (database != null) {
            conn = DBConnectionFactory.getConnection(database.getName());
        } else {
            conn = DBConnectionFactory.getConnection();
        }

        logger.debug("conn:" + conn.toString());

        String sql = queryDefinition.getSql();
        sql = QueryUtils.replaceSQLVars(sql);
        List<Object> values = null;
        if (paramMap != null) {
            SqlExecutor sqlExecutor = JdbcUtils.rebuildSQL(sql, paramMap);
            sql = sqlExecutor.getSql();
            values = (List<Object>) sqlExecutor.getParameter();
        }

        logger.debug("--------------execute query----------------------");
        logger.debug(queryDefinition.getTitle());

        logger.debug("::sql::" + sql);
        psmt = conn.prepareStatement(sql);

        if (values != null && !values.isEmpty()) {
            JdbcUtils.fillStatement(psmt, values);
            logger.debug("::values::" + values);
        }

        List<ColumnDefinition> columns = new java.util.ArrayList<ColumnDefinition>();

        rs = psmt.executeQuery();
        rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();
        for (int i = 1; i <= count; i++) {
            int sqlType = rsmd.getColumnType(i);
            ColumnDefinition column = new ColumnDefinition();
            column.setColumnName(rsmd.getColumnName(i));
            column.setColumnLabel(rsmd.getColumnLabel(i));
            column.setJavaType(FieldType.getJavaType(sqlType));
            column.setPrecision(rsmd.getPrecision(i));
            column.setScale(rsmd.getScale(i));
            columns.add(column);
        }

        Set<String> cols = new HashSet<String>();

        while (rs.next()) {
            int index = 0;
            TableModel rowModel = new TableModel();

            ColumnModel cell01 = new ColumnModel();
            cell01.setColumnName("ID");
            cell01.setType("String");
            rowModel.addColumn(cell01);
            rowModel.setIdColumn(cell01);
            cols.add(cell01.getColumnName());

            ColumnModel cell04 = new ColumnModel();
            cell04.setColumnName("AGGREGATIONKEY");
            cell04.setType("String");
            rowModel.addColumn(cell04);
            cols.add(cell04.getColumnName());

            Iterator<ColumnDefinition> iterator = columns.iterator();
            while (iterator.hasNext()) {
                ColumnDefinition column = iterator.next();
                /**
                 * ????
                 */
                if (cols.contains(column.getColumnName())) {
                    continue;
                }
                ColumnModel cell = new ColumnModel();
                String columnName = column.getColumnName();
                String javaType = column.getJavaType();
                cell.setColumnName(columnName);
                cell.setType(javaType);
                index = index + 1;
                if ("String".equals(javaType)) {
                    String value = rs.getString(columnName);
                    cell.setStringValue(value);
                    cell.setValue(value);
                } else if ("Integer".equals(javaType)) {
                    try {
                        Integer value = rs.getInt(columnName);
                        cell.setIntValue(value);
                        cell.setValue(value);
                    } catch (Exception e) {
                        String str = rs.getString(columnName);
                        logger.error("integer:" + str);
                        str = StringTools.replace(str, "$", "");
                        str = StringTools.replace(str, "", "");
                        str = StringTools.replace(str, ",", "");
                        NumberFormat fmt = NumberFormat.getInstance();
                        Number num = fmt.parse(str);
                        cell.setIntValue(num.intValue());
                        cell.setValue(cell.getIntValue());
                        logger.debug("?:" + num.intValue());
                    }
                } else if ("Long".equals(javaType)) {
                    try {
                        Long value = rs.getLong(columnName);
                        cell.setLongValue(value);
                        cell.setValue(value);
                    } catch (Exception e) {
                        String str = rs.getString(columnName);
                        logger.error("long:" + str);
                        str = StringTools.replace(str, "", "");
                        str = StringTools.replace(str, ",", "");
                        NumberFormat fmt = NumberFormat.getInstance();
                        Number num = fmt.parse(str);
                        cell.setLongValue(num.longValue());
                        cell.setValue(cell.getLongValue());
                        logger.debug("?:" + num.longValue());
                    }
                } else if ("Double".equals(javaType)) {
                    try {
                        Double d = rs.getDouble(columnName);
                        cell.setDoubleValue(d);
                        cell.setValue(d);
                    } catch (Exception e) {
                        String str = rs.getString(columnName);
                        logger.error("double:" + str);
                        str = StringTools.replace(str, "", "");
                        str = StringTools.replace(str, ",", "");
                        NumberFormat fmt = NumberFormat.getInstance();
                        Number num = fmt.parse(str);
                        cell.setDoubleValue(num.doubleValue());
                        cell.setValue(cell.getDoubleValue());
                        logger.debug("?:" + num.doubleValue());
                    }
                } else if ("Boolean".equals(javaType)) {
                    Boolean value = rs.getBoolean(columnName);
                    cell.setBooleanValue(value);
                    cell.setValue(value);
                } else if ("Date".equals(javaType)) {
                    Date value = rs.getTimestamp(columnName);
                    cell.setDateValue(value);
                    cell.setValue(value);
                } else {
                    String value = rs.getString(columnName);
                    cell.setStringValue(value);
                    cell.setValue(value);
                }
                rowModel.addColumn(cell);
                if (resultList.isEmpty()) {
                    singleDataMap.put(column.getColumnLabel(), cell.getValue());
                }
            }
            resultList.add(rowModel);
        }

        logger.debug("--------------------resultList size:" + resultList.size());

    } catch (Exception ex) {
        success = false;
        ex.printStackTrace();
        logger.error(ex);
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(rs);
        JdbcUtils.close(psmt);
        JdbcUtils.close(conn);
        if (!success) {
            if (task != null) {
                task.setStatus(2);
                transformTaskService.save(task);
            }
        }
    }

    logger.debug("--------------execute mybatis save----------------------");

    try {

        if (!StringUtils.equalsIgnoreCase(queryDefinition.getRotatingFlag(), "R2C")) {
            TransformTable tbl = new TransformTable();
            tbl.createOrAlterTable(tableDefinition);
        }

        List<ColumnDefinition> columns = DBUtils.getColumnDefinitions(tableDefinition.getTableName());
        if (columns != null && !columns.isEmpty()) {
            tableDefinition.setColumns(columns);
        }

        if (resultList != null && !resultList.isEmpty() && tableDefinition.getTableName() != null
                && tableDefinition.getAggregationKeys() != null) {
            logger.debug("RotatingFlag:" + queryDefinition.getRotatingFlag());
            logger.debug("RotatingColumn:" + queryDefinition.getRotatingColumn());
            /**
             * ????
             */
            if (StringUtils.equalsIgnoreCase(queryDefinition.getRotatingFlag(), "R2C")
                    && StringUtils.isNotEmpty(queryDefinition.getRotatingColumn()) && resultList.size() == 1) {

                logger.debug("?dataMap?:" + singleDataMap);
                logger.debug("AggregationKeys:" + tableDefinition.getAggregationKeys());
                ColumnDefinition idField = columnMap.get(tableDefinition.getAggregationKeys().toLowerCase());
                ColumnDefinition field = columnMap.get(queryDefinition.getRotatingColumn().toLowerCase());
                logger.debug("idField:" + idField);
                logger.debug("field:" + field);
                if (idField != null && field != null) {
                    String javaType = field.getJavaType();
                    List<TableModel> list = new ArrayList<TableModel>();
                    Set<Entry<String, Object>> entrySet = singleDataMap.entrySet();
                    for (Entry<String, Object> entry : entrySet) {
                        String key = entry.getKey();
                        Object value = entry.getValue();
                        if (key == null || value == null) {
                            continue;
                        }
                        TableModel tableModel = new TableModel();
                        tableModel.setTableName(queryDefinition.getTargetTableName());
                        ColumnModel cell = new ColumnModel();
                        cell.setColumnName(queryDefinition.getRotatingColumn());
                        cell.setType(javaType);

                        // logger.debug(cell.getColumnName()+"->"+javaType);

                        if ("String".equals(javaType)) {
                            cell.setStringValue(ParamUtils.getString(singleDataMap, key));
                            cell.setValue(cell.getStringValue());
                        } else if ("Integer".equals(javaType)) {
                            cell.setIntValue(ParamUtils.getInt(singleDataMap, key));
                            cell.setValue(cell.getIntValue());
                        } else if ("Long".equals(javaType)) {
                            cell.setLongValue(ParamUtils.getLong(singleDataMap, key));
                            cell.setValue(cell.getLongValue());
                        } else if ("Double".equals(javaType)) {
                            cell.setDoubleValue(ParamUtils.getDouble(singleDataMap, key));
                            cell.setValue(cell.getDoubleValue());
                        } else if ("Date".equals(javaType)) {
                            cell.setDateValue(ParamUtils.getDate(singleDataMap, key));
                            cell.setValue(cell.getDateValue());
                        } else {
                            cell.setValue(value);
                        }

                        tableModel.addColumn(cell);

                        ColumnModel idColumn = new ColumnModel();
                        idColumn.setColumnName(tableDefinition.getAggregationKeys());
                        idColumn.setJavaType(idField.getJavaType());
                        idColumn.setValue(key);
                        tableModel.setIdColumn(idColumn);
                        list.add(tableModel);
                    }
                    logger.debug("update datalist:" + list);
                    tableDataService.updateTableData(list);
                }
            } else {
                tableDataService.saveAll(tableDefinition, null, resultList);
            }
        }

        resultList.clear();
        resultList = null;

        long time = System.currentTimeMillis() - start;

        if (task != null) {
            task.setEndTime(new java.util.Date());
            task.setStatus(9);
            task.setDuration(time);
        }
        logger.debug("execute time(ms)--------------------------" + time);
    } catch (Exception ex) {
        if (task != null) {
            task.setStatus(2);
        }
        ex.printStackTrace();
        logger.error(ex);
        throw new RuntimeException(ex);
    } finally {
        if (task != null) {
            transformTaskService.save(task);
            if (task.getStatus() != 9) {
                this.run();
            }
        }
    }
}

---

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

/**
 * Creates a CalendarWrapper object of Calendar type from a ResultSet.
 *
 * @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_CALENDAR table, with the cursor before its first row
 * @return a newly created CalendarWrapper initialized with the fields in
 *         the result set/*from   w  ww .  j  a v a  2  s .c o m*/
 * @throws Exception
 * @throws NotFoundException
 */
protected static CalendarWrapper createCalendar(String wrapperId, ResultSet rs)
        throws NotFoundException, Exception {

    if (!rs.next()) {
        throw new NotFoundException("No calendar found.");
    }
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();

    RecurrencePattern rp = null;

    String column = null;

    String uid = null;
    String userId = null;

    short recurrenceType = -1;
    int interval = 0;
    short monthOfYear = 0;
    short dayOfMonth = 0;
    short dayOfWeekMask = 0;
    short instance = 0;
    String startDatePattern = null;
    String endDatePattern = null;
    int occurrences = -1;
    boolean noEndDate = false;
    boolean aDay = false;
    Reminder r = null;
    Date replyTime = null;
    Date dstart = null;
    Date dend = null;
    Date completed = null;
    Date reminderTime = null;

    uid = String.valueOf(rs.getLong(SQL_FIELD_ID));
    userId = rs.getString(SQL_FIELD_USERID);

    Calendar cal = new Calendar();

    r = new Reminder();

    CalendarContent c;
    boolean isAnEvent = true;
    for (int i = 1; i <= columnCount; i++) {
        if (SQL_FIELD_TYPE.equalsIgnoreCase(rsmd.getColumnName(i))) {
            if (rs.getShort(i) == CALENDAR_TASK_TYPE) {
                isAnEvent = false;
            }
            break;
        }
    }
    if (isAnEvent) {
        c = new Event();
        cal.setEvent((Event) c);
    } else {
        c = new Task();
        cal.setTask((Task) c);
    }

    c.setReminder(r);
    CalendarWrapper cw = new CalendarWrapper(wrapperId, userId, cal);

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

        column = rsmd.getColumnName(i);

        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_ALL_DAY.equalsIgnoreCase(column)) {
            String allDay = null;
            allDay = rs.getString(i);
            if (allDay != null && allDay.length() > 0) {
                if (allDay.charAt(0) == '1') {
                    aDay = true;
                    c.setAllDay(Boolean.TRUE);
                } else if (allDay.charAt(0) == '0') {
                    c.setAllDay(Boolean.FALSE);
                }
            }
        } else if (SQL_FIELD_BODY.equalsIgnoreCase(column)) {
            c.getDescription().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_BUSY_STATUS.equalsIgnoreCase(column)) {
            short bs = rs.getShort(i);
            if (rs.wasNull()) {
                c.setBusyStatus(null);
            } else {
                c.setBusyStatus(new Short(bs));
            }
        } else if (SQL_FIELD_CATEGORIES.equalsIgnoreCase(column)) {
            c.getCategories().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_COMPANIES.equalsIgnoreCase(column)) {
            c.getOrganizer().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_DURATION.equalsIgnoreCase(column)) {
            c.getDuration().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_DATE_END.equalsIgnoreCase(column)) {
            if (rs.getTimestamp(i) != null) {
                dend = new Date(rs.getTimestamp(i).getTime());
            }
        } else if (SQL_FIELD_IMPORTANCE.equalsIgnoreCase(column)) {
            c.getPriority().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_LOCATION.equalsIgnoreCase(column)) {
            c.getLocation().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_MEETING_STATUS.equalsIgnoreCase(column)) {
            short meetingStatus = rs.getShort(i);
            if (!rs.wasNull()) {
                if (c instanceof Task) {
                    c.getStatus().setPropertyValue(Short.toString(meetingStatus));
                } else {
                    c.setMeetingStatus(new Short(meetingStatus));
                }
            }
        } else if (SQL_FIELD_MILEAGE.equalsIgnoreCase(column)) {
            String mileage = null;
            mileage = rs.getString(i);
            if (mileage != null && mileage.length() > 0 && !("null".equals(mileage))) {
                c.setMileage(Integer.valueOf(mileage));
            }
        } else if (SQL_FIELD_REMINDER_TIME.equalsIgnoreCase(column)) {
            if (rs.getTimestamp(i) != null) {
                reminderTime = new Date(rs.getTimestamp(i).getTime());
            }
        } else if (SQL_FIELD_REMINDER_REPEAT_COUNT.equalsIgnoreCase(column)) {
            r.setRepeatCount(rs.getInt(i));
        } else if (SQL_FIELD_REMINDER.equalsIgnoreCase(column)) {
            String reminder = null;
            reminder = rs.getString(i);
            if (reminder != null && reminder.length() > 0) {
                r.setActive(reminder.charAt(0) == '1');
            } else {
                r.setActive(false);
            }
        } else if (SQL_FIELD_REMINDER_SOUND_FILE.equalsIgnoreCase(column)) {
            r.setSoundFile(rs.getString(i));
        } else if (SQL_FIELD_REMINDER_OPTIONS.equalsIgnoreCase(column)) {
            r.setOptions(rs.getInt(i));
        } else if (SQL_FIELD_REPLY_TIME.equalsIgnoreCase(column)) {
            if (rs.getTimestamp(i) != null) {
                replyTime = new Date(rs.getTimestamp(i).getTime());
            }
        } else if (SQL_FIELD_SENSITIVITY.equalsIgnoreCase(column)) {
            Short sensitivity = rs.getShort(i);
            if (sensitivity == null) {
                c.getAccessClass().setPropertyValue(new Short((short) 0));
            } else {
                c.getAccessClass().setPropertyValue(sensitivity);
            }
        } else if (SQL_FIELD_DATE_START.equalsIgnoreCase(column)) {
            if (rs.getTimestamp(i) != null) {
                dstart = new Date(rs.getTimestamp(i).getTime());
            }
        } else if (SQL_FIELD_SUBJECT.equalsIgnoreCase(column)) {
            c.getSummary().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_RECURRENCE_TYPE.equalsIgnoreCase(column)) {
            recurrenceType = rs.getShort(i);
        } else if (SQL_FIELD_INTERVAL.equalsIgnoreCase(column)) {
            interval = rs.getInt(i);
        } else if (SQL_FIELD_MONTH_OF_YEAR.equalsIgnoreCase(column)) {
            monthOfYear = rs.getShort(i);
        } else if (SQL_FIELD_DAY_OF_MONTH.equalsIgnoreCase(column)) {
            dayOfMonth = rs.getShort(i);
        } else if (SQL_FIELD_DAY_OF_WEEK_MASK.equalsIgnoreCase(column)) {
            String dayOfWeekMaskStr = rs.getString(i);
            if (dayOfWeekMaskStr != null && dayOfWeekMaskStr.length() > 0) {
                dayOfWeekMask = Short.parseShort(dayOfWeekMaskStr);
            }
        } else if (SQL_FIELD_INSTANCE.equalsIgnoreCase(column)) {
            instance = rs.getShort(i);
        } else if (SQL_FIELD_START_DATE_PATTERN.equalsIgnoreCase(column)) {
            startDatePattern = rs.getString(i);
        } else if (SQL_FIELD_NO_END_DATE.equalsIgnoreCase(column)) {
            String noEndDateStr = null;
            noEndDateStr = rs.getString(i);
            if (noEndDateStr != null && noEndDateStr.length() > 0) {
                if (noEndDateStr.charAt(0) == '1') {
                    noEndDate = true;
                } else if (noEndDateStr.charAt(0) == '0') {
                    noEndDate = false;
                }
            }
        } else if (SQL_FIELD_END_DATE_PATTERN.equalsIgnoreCase(column)) {
            endDatePattern = rs.getString(i);
        } else if (SQL_FIELD_OCCURRENCES.equalsIgnoreCase(column)) {
            occurrences = rs.getShort(i);
        } else if (SQL_FIELD_TYPE.equalsIgnoreCase(column)) {
            // Already handled
        } else if (SQL_FIELD_COMPLETED.equalsIgnoreCase(column)) {
            if (rs.getTimestamp(i) != null) {
                completed = new Date(rs.getTimestamp(i).getTime());
            }
        } else if (SQL_FIELD_PERCENT_COMPLETE.equalsIgnoreCase(column)) {
            if (c instanceof Task) {
                short percentage = rs.getShort(i);
                ((Task) c).getPercentComplete().setPropertyValue(String.valueOf(percentage));
                if (percentage == 100) {
                    ((Task) c).getComplete().setPropertyValue(Boolean.TRUE);
                } else {
                    ((Task) c).getComplete().setPropertyValue(Boolean.FALSE);
                }
            }
        } else if (SQL_FIELD_FOLDER.equalsIgnoreCase(column)) {
            c.getFolder().setPropertyValue(rs.getString(i));
        } else if (SQL_FIELD_START_DATE_TIME_ZONE.equalsIgnoreCase(column)) {
            c.getDtStart().setTimeZone(rs.getString(i));
            // @todo Set the same time zone for other non-UTC and 
            //       non-all-day date-time properties
        } else if (SQL_FIELD_END_DATE_TIME_ZONE.equalsIgnoreCase(column)) {
            c.getDtEnd().setTimeZone(rs.getString(i));
        } else if (SQL_FIELD_REMINDER_TIME_ZONE.equalsIgnoreCase(column)) {
            c.getReminder().setTimeZone(rs.getString(i));
        }
        // Unhandled columns are just ignored
    }

    if (dstart != null) {
        c.getDtStart().setPropertyValue(getStringFromDate(aDay, dstart));
    }

    if (dend != null) {
        c.getDtEnd().setPropertyValue(getStringFromDate(aDay, dend));
    }

    if (replyTime != null && (c instanceof Event)) {
        ((Event) c).getReplyTime().setPropertyValue(getStringFromDate(aDay, // @todo or false?
                replyTime));
    }

    if (completed != null && (c instanceof Task)) {
        ((Task) c).getDateCompleted().setPropertyValue(getStringFromDate(aDay, completed));
    }

    if (reminderTime != null) {
        //
        // Also the reminder time follows the start and end dates convention
        // relative to the all day format: if the all day flag is on then it
        // has to be interpreted as a local time (else in UTC).
        //
        r.setTime(getStringFromDate(aDay, reminderTime));
        if (dstart != null) {
            r.setMinutes((int) ((dstart.getTime() - reminderTime.getTime()) / (60000))); // 60 seconds in a minutes *
                                                                                         // 1000 millis in a second = 60000
        } else {
            r.setMinutes(0);
        }
    }

    switch (recurrenceType) {

    case RecurrencePattern.TYPE_DAILY:
        rp = RecurrencePattern.getDailyRecurrencePattern(interval, startDatePattern, endDatePattern, noEndDate,
                occurrences, dayOfWeekMask);
        c.setRecurrencePattern(rp);
        break;

    case RecurrencePattern.TYPE_WEEKLY:
        rp = RecurrencePattern.getWeeklyRecurrencePattern(interval, dayOfWeekMask, startDatePattern,
                endDatePattern, noEndDate, occurrences);
        c.setRecurrencePattern(rp);
        break;

    case RecurrencePattern.TYPE_MONTHLY:
        rp = RecurrencePattern.getMonthlyRecurrencePattern(interval, dayOfMonth, startDatePattern,
                endDatePattern, noEndDate, occurrences);
        c.setRecurrencePattern(rp);
        break;

    case RecurrencePattern.TYPE_MONTH_NTH:
        rp = RecurrencePattern.getMonthNthRecurrencePattern(interval, dayOfWeekMask, instance, startDatePattern,
                endDatePattern, noEndDate, occurrences);
        c.setRecurrencePattern(rp);
        break;

    case RecurrencePattern.TYPE_YEARLY:
        rp = RecurrencePattern.getYearlyRecurrencePattern(interval, dayOfMonth, monthOfYear, startDatePattern,
                endDatePattern, noEndDate, occurrences);
        c.setRecurrencePattern(rp);
        break;

    case RecurrencePattern.TYPE_YEAR_NTH:
        rp = RecurrencePattern.getYearNthRecurrencePattern(interval, dayOfWeekMask, monthOfYear, instance,
                startDatePattern, endDatePattern, noEndDate, occurrences);
        c.setRecurrencePattern(rp);
        break;

    default:
        //
        // ignore any unknown recurrence pattern type
        //
        break;
    }

    //
    // Set timezone in the RecurrencePattern using the dtStart timezone.
    // Note: the tasks could have only the due date (or neither that)
    //
    if (rp != null) {
        if (!isAnEvent && dend != null) {
            rp.setTimeZone(c.getDtEnd().getTimeZone());
        } else {
            rp.setTimeZone(c.getDtStart().getTimeZone());
        }
        c.setRecurrencePattern(rp);
    }

    return cw;
}

---

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

/**
 * @param fromConn//w  w w .  j  a  v  a 2s  . c  om
 * @param toConn
 * @param sql
 * @param fromTableName
 * @param toTableName
 * @param colNewToOldMap
 * @param verbatimDateMapper
 * @param newColDefValues
 * @param sourceServerType
 * @param destServerType
 * @return
 */
public static boolean copyTable(final Connection fromConn, final Connection toConn, final String sql,
        final String countSQL, final String fromTableName, final String toTableName,
        final Map<String, String> colNewToOldMap, final Map<String, String> verbatimDateMapper,
        final Map<String, String> newColDefValues, final SERVERTYPE sourceServerType,
        final SERVERTYPE destServerType) {
    //Timestamp now = new Timestamp(System.currentTimeMillis());

    IdMapperMgr idMapperMgr = IdMapperMgr.getInstance();

    if (frame != null) {
        frame.setDesc("Copying Table " + fromTableName);
    }
    log.info("Copying Table " + fromTableName);

    List<String> fromFieldNameList = getFieldNamesFromSchema(fromConn, fromTableName);

    String sqlStr = sql + " ORDER BY " + fromTableName + "." + fromFieldNameList.get(0);
    log.debug(sqlStr);

    int numRecs;
    if (countSQL == null) {
        numRecs = getNumRecords(fromConn, fromTableName);
    } else {
        numRecs = getCountAsInt(fromConn, countSQL);
    }
    setProcess(0, numRecs);

    DBTableInfo tblInfo = DBTableIdMgr.getInstance().getInfoByTableName(toTableName);
    Statement updateStatement = null;
    String id = "";
    try {

        updateStatement = toConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) {
            BasicSQLUtils.removeForeignKeyConstraints(toConn, BasicSQLUtils.myDestinationServerType);

        }

        //HashMap<String, Integer> newDBFieldHash   = new HashMap<String, Integer>();
        List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(toConn, toTableName);
        //int inx = 1;
        //for (FieldMetaData fmd : newFieldMetaData)
        //{
        //    newDBFieldHash.put(fmd.getName(), inx++);
        //}

        Statement stmt = fromConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        //System.out.println(sqlStr);
        ResultSet rs = stmt.executeQuery(sqlStr);
        ResultSetMetaData rsmd = rs.getMetaData();

        Vector<Integer> dateColumns = new Vector<Integer>();

        //System.out.println(toTableName);
        Hashtable<String, Integer> fromHash = new Hashtable<String, Integer>();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            String colName = rsmd.getColumnName(i);

            fromHash.put(colName, i);
            //System.out.println(rsmd.getColumnName(i)+" -> "+i);

            if (rsmd.getColumnType(i) == java.sql.Types.DATE || colName.toLowerCase().endsWith("date")
                    || colName.toLowerCase().startsWith("date")) {
                //System.out.println("Date: "+rsmd.getColumnName(i)+" -> "+i);
                dateColumns.add(i);
            }
        }

        Hashtable<String, String> oldNameToNewNameHash = new Hashtable<String, String>();
        if (colNewToOldMap != null) {
            for (String newName : colNewToOldMap.keySet()) {
                String oldName = colNewToOldMap.get(newName);
                System.out
                        .println("Mapping oldName[" + (oldName == null ? newName : oldName) + " -> " + newName);

                oldNameToNewNameHash.put(oldName == null ? newName : oldName, newName);
            }
        }

        // System.out.println("Num Cols: "+rsmd.getColumnCount());

        Map<String, PartialDateConv> dateMap = new Hashtable<String, PartialDateConv>();

        String insertSQL = null;

        // Get the columns that have dates in case we get a TimestampCreated date that is null
        // and then we can go looking for an older date to try to figure it out
        Integer timestampModifiedInx = fromHash.get("TimestampModified");
        Integer timestampCreatedInx = fromHash.get("TimestampCreated");
        boolean isAccessionTable = fromTableName.equals("accession");
        boolean hasInstIdCol = fromTableName.equals("permit") || fromTableName.equals("journal")
                || fromTableName.equals("referencework");

        StringBuffer str = new StringBuffer(1024);
        int count = 0;
        while (rs.next()) {
            boolean skipRecord = false;

            dateMap.clear();

            // Start by going through the resultset and converting all dates from Integers
            // to real dates and keep the verbatium date information if it is a partial date
            for (int i : dateColumns) {
                String oldColName = rsmd.getColumnName(i);
                Integer oldColIndex = fromHash.get(oldColName);

                if (oldColIndex == null) {
                    log.error("Couldn't find new column for old column for date for Table[" + fromTableName
                            + "] Col Name[" + newFieldMetaData.get(i).getName() + "]");
                    continue;
                }

                if (oldColIndex > newFieldMetaData.size()) {
                    continue;
                }

                String newColName = colNewToOldMap != null ? oldNameToNewNameHash.get(oldColName) : null;
                if (newColName == null) {
                    newColName = oldColName;
                }

                Object dataObj = rs.getObject(i);

                if (dataObj instanceof Integer) {
                    PartialDateConv datep = new PartialDateConv();
                    getPartialDate((Integer) dataObj, datep); // fills in Verbatim also

                    dateMap.put(newColName, datep);
                }
            }

            // OK here we make sure that both the created dated ad modified date are not null
            // and we copy the date if one has a value and the other does not.
            Date timestampCreatedCached = now;
            Date timestampModifiedCached = now;

            if (timestampModifiedInx != null && timestampCreatedInx != null) {
                timestampModifiedCached = rs.getDate(timestampModifiedInx);
                timestampCreatedCached = rs.getDate(timestampCreatedInx);
                if (timestampModifiedCached == null && timestampCreatedCached == null) {
                    timestampCreatedCached = Calendar.getInstance().getTime();
                    timestampModifiedCached = Calendar.getInstance().getTime();

                } else if (timestampModifiedCached == null && timestampCreatedCached != null) {
                    timestampModifiedCached = new Date(timestampCreatedCached.getTime());
                } else {
                    timestampCreatedCached = timestampModifiedCached != null
                            ? new Date(timestampModifiedCached.getTime())
                            : new Date();
                }
            } else {

                if (timestampModifiedInx != null) {
                    timestampModifiedCached = rs.getDate(timestampModifiedInx);
                    if (timestampModifiedCached == null) {
                        timestampModifiedCached = now;
                    }
                }

                if (timestampCreatedInx != null) {
                    timestampCreatedCached = rs.getDate(timestampCreatedInx);
                    if (timestampCreatedCached == null) {
                        timestampCreatedCached = now;
                    }
                }
            }

            str.setLength(0);
            if (insertSQL == null) {
                StringBuffer fieldList = new StringBuffer();
                fieldList.append("( ");
                for (int i = 0; i < newFieldMetaData.size(); i++) {
                    if ((i > 0) && (i < newFieldMetaData.size())) {
                        fieldList.append(", ");
                    }
                    String newFieldName = newFieldMetaData.get(i).getName();
                    fieldList.append(newFieldName + " ");
                }
                fieldList.append(")");

                str.append("INSERT INTO " + toTableName + " " + fieldList + " VALUES (");

                insertSQL = str.toString();

                log.debug(str);
            } else {
                str.append(insertSQL);
            }

            id = rs.getString(1);

            // For each column in the new DB table...
            for (int i = 0; i < newFieldMetaData.size(); i++) {
                FieldMetaData newFldMetaData = newFieldMetaData.get(i);
                String newColName = newFldMetaData.getName();
                String oldMappedColName = null;

                //System.out.println("["+newColName+"]");

                // Get the Old Column Index from the New Name
                // String  oldName     = colNewToOldMap != null ? colNewToOldMap.get(newColName) : newColName;
                Integer columnIndex = fromHash.get(newColName);

                if (columnIndex == null && colNewToOldMap != null) {
                    oldMappedColName = colNewToOldMap.get(newColName);
                    if (oldMappedColName != null) {
                        columnIndex = fromHash.get(oldMappedColName);

                    } else if (isOptionOn(SHOW_NAME_MAPPING_ERROR) && (ignoreMappingFieldNames == null
                            || ignoreMappingFieldNames.get(newColName) == null)) {
                        String msg = "No Map for table [" + fromTableName + "] from New Name[" + newColName
                                + "] to Old Name[" + oldMappedColName + "]";
                        log.error(msg);

                        writeErrLog(msg);

                    }
                } else {
                    oldMappedColName = newColName;
                }

                String verbatimDateFieldName = null;
                if (verbatimDateMapper != null) {
                    verbatimDateFieldName = verbatimDateMapper.get(newColName);
                }

                //System.out.println("new["+newColName+"]  old["+oldMappedColName+"]");

                if (columnIndex != null) {
                    if (i > 0)
                        str.append(", ");
                    Object dataObj = rs.getObject(columnIndex);

                    if (idMapperMgr != null && oldMappedColName != null && oldMappedColName.endsWith("ID")) {
                        IdMapperIFace idMapper = idMapperMgr.get(fromTableName, oldMappedColName);
                        if (idMapper != null) {
                            int showNullOption = SHOW_NULL_FK;
                            int showFkLookUpOption = SHOW_FK_LOOKUP;

                            int oldPrimaryKeyId = rs.getInt(columnIndex);
                            if (oldMappedColName.equalsIgnoreCase(fromTableName + "id")) {
                                showNullOption = SHOW_NULL_PM;
                                showFkLookUpOption = SHOW_PM_LOOKUP;
                            }

                            // if the value was null, getInt() returns 0
                            // use wasNull() to distinguish real 0 from a null return
                            if (rs.wasNull()) {
                                dataObj = null;

                                if (isOptionOn(showNullOption)) {

                                    String msg = "Unable to Map "
                                            + (showNullOption == SHOW_NULL_FK ? "Foreign" : "Primary")
                                            + " Key Id[NULL] old Name[" + oldMappedColName + "]   colInx["
                                            + columnIndex + "]   newColName[" + newColName + "]";
                                    log.error(msg);
                                    writeErrLog(msg);
                                    skipRecord = true;
                                }
                            } else {
                                dataObj = idMapper.get(oldPrimaryKeyId);

                                if (dataObj == null && isOptionOn(showFkLookUpOption)) {
                                    String msg = "Unable to Map Primary Id[" + oldPrimaryKeyId + "] old Name["
                                            + oldMappedColName + "] table[" + fromTableName + "]";
                                    log.error(msg);
                                    writeErrLog(msg);
                                    skipRecord = true;
                                }
                            }
                        } else {
                            if (isOptionOn(SHOW_NAME_MAPPING_ERROR) && (ignoreMappingFieldIDs == null
                                    || ignoreMappingFieldIDs.get(oldMappedColName) == null)) {
                                // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                                // XXX Temporary fix so it doesn't hide other errors
                                // Josh has promised his first born if he doesn't fix this!
                                // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                                if (!oldMappedColName.equals("RankID")) {
                                    //idMapperMgr.dumpKeys();
                                    String msg = "No ID Map for [" + fromTableName + "] Old Column Name["
                                            + oldMappedColName + "]";
                                    log.error(msg);
                                    writeErrLog(msg);
                                    skipRecord = true;
                                }
                            }
                        }
                    }

                    // First check to see if it is null
                    if (dataObj == null) {
                        if (newFldMetaData.getName().equals("TimestampCreated")) {
                            if (timestampCreatedInx != null) {
                                if (isAccessionTable) {
                                    Date date = UIHelper
                                            .convertIntToDate(rs.getInt(fromHash.get("DateAccessioned")));
                                    str.append(date != null ? getStrValue(date)
                                            : getStrValue(timestampCreatedCached, newFldMetaData.getType()));
                                } else {
                                    str.append(getStrValue(timestampCreatedCached, newFldMetaData.getType()));
                                }

                            } else {
                                str.append(getStrValue(timestampCreatedCached, newFldMetaData.getType()));
                            }

                        } else if (newFldMetaData.getName().equals("TimestampModified")) {
                            if (timestampModifiedInx != null) {
                                if (isAccessionTable) {
                                    Date date = UIHelper
                                            .convertIntToDate(rs.getInt(fromHash.get("DateAccessioned")));
                                    str.append(date != null ? getStrValue(date)
                                            : getStrValue(timestampCreatedCached, newFldMetaData.getType()));

                                } else {
                                    str.append(getStrValue(timestampModifiedCached, newFldMetaData.getType()));
                                }
                            } else {
                                str.append(getStrValue(timestampModifiedCached, newFldMetaData.getType()));
                            }
                        } else {
                            str.append("NULL");
                        }

                    } else if (dataObj instanceof Integer && (newFldMetaData.getSqlType() == java.sql.Types.DATE
                            || newColName.toLowerCase().endsWith("date")
                            || newColName.toLowerCase().startsWith("date"))) {
                        PartialDateConv datePr = dateMap.get(newColName);
                        if (datePr != null) {
                            str.append(datePr.getDateStr());
                        } else {
                            str.append("NULL");
                        }

                    } else if (verbatimDateFieldName != null) {
                        PartialDateConv datePr = dateMap.get(newColName);
                        str.append(datePr != null ? datePr.getVerbatim() : "NULL");

                    } else if (dataObj instanceof Number) {
                        DBFieldInfo fi = tblInfo.getFieldByColumnName(newColName);
                        String type = newFldMetaData.getType().toLowerCase().startsWith("tiny") ? fi.getType()
                                : newFldMetaData.getType();
                        str.append(getStrValue(dataObj, type));

                    } else {
                        if (columnValueMapper != null) {
                            BasicSQLUtilsMapValueIFace valueMapper = columnValueMapper.get(newColName);
                            if (valueMapper != null) {
                                dataObj = valueMapper.mapValue(dataObj);
                            }
                        }

                        if (dataObj instanceof String && newFldMetaData.isString()) {
                            DBFieldInfo fi = tblInfo.getFieldByColumnName(newColName);
                            String s = (String) dataObj;
                            if (s.length() > fi.getLength()) {
                                String msg = String.format(
                                        "Truncating Table '%s' Field '%s' with Length %d, db len %d Value[%s]",
                                        toTableName, newColName, s.length(), fi.getLength(), s);
                                tblWriter.logError(msg);
                                log.error(msg);
                                dataObj = s.substring(0, fi.getLength());
                            }
                        }
                        str.append(getStrValue(dataObj, newFldMetaData.getType()));
                    }

                } else if (hasInstIdCol && newFldMetaData.getName().equals("InstitutionID")) {
                    if (i > 0)
                        str.append(", ");
                    str.append("1");

                } else if (newColName.endsWith("Version")) {
                    if (i > 0)
                        str.append(", ");
                    str.append("0");

                } else if (newColName.endsWith("DatePrecision")) {
                    if (i > 0)
                        str.append(", ");

                    String cName = newColName.substring(0, newColName.length() - 9);
                    PartialDateConv datePr = dateMap.get(cName);
                    if (datePr != null) {
                        str.append(datePr.getPartial());
                    } else {
                        str.append("NULL");
                    }

                } else if (idMapperMgr != null && newColName.endsWith("ID") && oneToOneIDHash != null
                        && oneToOneIDHash.get(newColName) != null) {

                    IdMapperIFace idMapper = idMapperMgr.get(toTableName, newColName);
                    if (idMapper != null) {
                        idMapper.setShowLogErrors(false);
                        Integer newPrimaryId = idMapper.get(Integer.parseInt(id));
                        if (newPrimaryId != null) {
                            if (i > 0)
                                str.append(", ");
                            str.append(newPrimaryId);
                        } else {
                            if (i > 0)
                                str.append(", ");
                            str.append("NULL");

                            if (isOptionOn(SHOW_VAL_MAPPING_ERROR)) {
                                String msg = "For Table[" + fromTableName + "] mapping new Column Name["
                                        + newColName + "] ID[" + id + "] was not mapped";
                                log.error(msg);
                                writeErrLog(msg);
                                skipRecord = true;
                            }
                        }
                    }

                } else // there was no old column that maps to this new column
                {
                    String newColValue = null;
                    if (newColDefValues != null) {
                        newColValue = newColDefValues.get(newColName);
                    }

                    if (newColValue == null) {
                        newColValue = "NULL";
                        //System.out.println("ignoreMappingFieldNames" + ignoreMappingFieldNames);
                        //System.out.println("ignoreMappingFieldNames.get(colName)" + ignoreMappingFieldNames.get(colName));
                        if (isOptionOn(SHOW_NAME_MAPPING_ERROR) && (ignoreMappingFieldNames == null
                                || ignoreMappingFieldNames.get(newColName) == null)) {
                            String msg = "For Table[" + fromTableName + "] mapping new Column Name["
                                    + newColName + "] was not mapped";
                            log.error(msg);
                            writeErrLog(msg);
                            skipRecord = true;
                        }
                    }
                    if (i > 0)
                        str.append(", ");

                    BasicSQLUtilsMapValueIFace valueMapper = columnValueMapper.get(newFldMetaData.getName());
                    if (valueMapper != null) {
                        newColValue = valueMapper.mapValue(newColValue);
                    }

                    str.append(newColValue);
                }

            }

            str.append(")");
            if (frame != null) {
                if (count % 500 == 0) {
                    frame.setProcess(count);
                }

            } else {
                if (count % 2000 == 0) {
                    log.info(toTableName + " processed: " + count);
                }
            }

            //setQuotedIdentifierOFFForSQLServer(toConn, BasicSQLUtils.myDestinationServerType);
            //exeUpdateCmd(updateStatement, "SET FOREIGN_KEY_CHECKS = 0");
            //if (str.toString().toLowerCase().contains("insert into locality"))
            //{
            //log.debug(str.toString());
            //}

            //String str2 = "SET QUOTED_IDENTIFIER ON";
            //log.debug("executing: " + str);
            //updateStatement.execute(str2);
            // updateStatement.close();
            if (!skipRecord) {
                if (isOptionOn(SHOW_COPY_TABLE)) {
                    log.debug("executing: " + str);
                }
                int retVal = exeUpdateCmd(updateStatement, str.toString());
                if (retVal == -1) {
                    rs.close();
                    stmt.clearBatch();
                    stmt.close();
                    return false;
                }
            }
            count++;
            // if (count == 1) break;
        }

        if (frame != null) {
            frame.setProcess(count);

        } else {
            log.info(fromTableName + " processed " + count + " records.");
        }

        rs.close();
        stmt.clearBatch();
        stmt.close();

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

        edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
        edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
        //e.printStackTrace();
        log.error(sqlStr);
        log.error(ex);
        log.error("ID: " + id);
    } finally {
        try {
            updateStatement.clearBatch();
            updateStatement.close();

        } catch (SQLException ex) {

        }
    }
    BasicSQLUtils.setFieldsToIgnoreWhenMappingNames(null);//meg added
    return true;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »