Example usage for java.sql ResultSetMetaData getColumnLabel

List of usage examples for java.sql ResultSetMetaData getColumnLabel

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

Introduction

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

Prototype

String getColumnLabel(int column) throws SQLException;

Source Link

Document

Gets the designated column's suggested title for use in printouts and displays.

Usage

From source file:gsn.storage.StorageManager.java

/**
 * Returns false if the table doesnt exist. If the table exists but the
 * structure is not compatible with the specified fields the method throws
 * GSNRuntimeException. Note that this method doesn't close the connection
 *
 * @param tableName/*from w  w w.  j  ava 2  s  . c o  m*/
 * @param connection (this method will not close it and the caller is responsible
 *                   for closing the connection)
 * @return
 * @throws SQLException
 * @Throws GSNRuntimeException
 */

public boolean tableExists(CharSequence tableName, DataField[] fields, Connection connection)
        throws SQLException, GSNRuntimeException {
    if (!ValidityTools.isValidJavaVariable(tableName))
        throw new GSNRuntimeException("Table name is not valid");
    StringBuilder sb = new StringBuilder("select * from ").append(tableNameGeneratorInString(tableName))
            .append(" where 1=0 ");
    ResultSet rs = null;
    try {
        rs = executeQueryWithResultSet(sb, connection);
        ResultSetMetaData structure = rs.getMetaData();
        if (fields != null && fields.length > 0)
            nextField: for (DataField field : fields) {
                for (int i = 1; i <= structure.getColumnCount(); i++) {
                    String colName = structure.getColumnLabel(i);
                    int colType = structure.getColumnType(i);
                    int colTypeScale = structure.getScale(i);
                    if (field.getName().equalsIgnoreCase(colName)) {
                        byte gsnType = convertLocalTypeToGSN(colType, colTypeScale);
                        if (gsnType == -100) {
                            logger.error(
                                    "The type can't be converted to GSN form - error description: virtual sensor name is: "
                                            + tableName + ", field name is: " + colName + ", query is: " + sb);
                        }
                        if (field.getDataTypeID() == gsnType)
                            continue nextField;
                        else
                            throw new GSNRuntimeException("The column : " + colName + " in the >" + tableName
                                    + "< table is not compatible with type : " + field.getType()
                                    + ". The actual type for this table (currently in the database): "
                                    + colType);
                    }
                }
                throw new GSNRuntimeException("The table " + tableName + " in the database, doesn't have the >"
                        + field.getName() + "< column.");
            }
    } catch (SQLException e) {
        if (e.getErrorCode() == getTableNotExistsErrNo() || e.getMessage().contains("does not exist"))
            return false;
        else {
            logger.error(e.getMessage());
            throw e;
        }
    } finally {
        close(rs);
    }
    return true;
}

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

private List<List<PropertyPair>> findResults(Query query, SelectionCollector collector, PlasmaType type,
        Connection con) {/*from w  ww  . ja  v  a2  s. c om*/
    Object[] params = new Object[0];
    RDBDataConverter converter = RDBDataConverter.INSTANCE;

    AliasMap aliasMap = new AliasMap(type);

    // construct a filter adding to alias map
    RDBFilterAssembler filterAssembler = null;
    Where where = query.findWhereClause();
    if (where != null) {
        filterAssembler = new RDBFilterAssembler(where, type, aliasMap);
        params = filterAssembler.getParams();
    }

    RDBOrderingAssembler orderingDeclAssembler = null;
    OrderBy orderby = query.findOrderByClause();
    if (orderby != null)
        orderingDeclAssembler = new RDBOrderingAssembler(orderby, type, aliasMap);
    RDBGroupingAssembler groupingDeclAssembler = null;
    GroupBy groupby = query.findGroupByClause();
    if (groupby != null)
        groupingDeclAssembler = new RDBGroupingAssembler(groupby, type, aliasMap);

    String rootAlias = aliasMap.getAlias(type);
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("SELECT DISTINCT "); // FIXME: only necessary if

    // FIXME: determine if any selected column(s) are LOB and don't use
    // DISTINCT in this case
    boolean hasLob = false;
    int i = 0;
    Set<Property> props = collector.getProperties(type);
    for (Property prop : props) {
        if (prop.isMany() && !prop.getType().isDataType())
            continue;
        if (i > 0)
            sqlQuery.append(", ");
        sqlQuery.append(rootAlias);
        sqlQuery.append(".");
        sqlQuery.append(((PlasmaProperty) prop).getPhysicalName());
        i++;
    }

    // construct a FROM clause from alias map
    sqlQuery.append(" FROM ");
    Iterator<PlasmaType> it = aliasMap.getTypes();
    int count = 0;
    while (it.hasNext()) {
        PlasmaType aliasType = it.next();
        String alias = aliasMap.getAlias(aliasType);
        if (count > 0)
            sqlQuery.append(", ");
        sqlQuery.append(this.statementUtil.getQualifiedPhysicalName(aliasType));
        sqlQuery.append(" ");
        sqlQuery.append(alias);
        count++;
    }

    // append WHERE filter
    if (filterAssembler != null) {
        sqlQuery.append(" ");
        sqlQuery.append(filterAssembler.getFilter());
    }

    if (orderingDeclAssembler != null) {
        sqlQuery.append(" ");
        sqlQuery.append(orderingDeclAssembler.getOrderingDeclaration());
    }

    if (groupingDeclAssembler != null) {
        sqlQuery.append(" ");
        sqlQuery.append(groupingDeclAssembler.getGroupingDeclaration());
    }

    // set the result range
    RDBMSVendorName vendor = PlasmaRuntime.getInstance().getRDBMSProviderVendor(DataAccessProviderName.JDBC);
    switch (vendor) {
    case ORACLE:
        if (query.getStartRange() != null && query.getEndRange() != null) {
            long offset = query.getStartRange() - 1; // inclusive
            if (offset < 0)
                offset = 0;
            long rowcount = query.getEndRange() - offset;
            StringBuilder buf = new StringBuilder();

            // Pagination wrapper making sure ordering occurs before any
            // ROWNUM selected by using
            // a nested SELECT.
            if (offset == 0) {
                buf.append("SELECT * FROM (");
                buf.append(sqlQuery);
                buf.append(") WHERE ROWNUM <= ");
                buf.append(rowcount);
            } else {
                // For offsets uses limiting condition on ROWNUM itself
                // as well as a
                // ROWNUM alias to enable Oracle STOPKEY processing
                // which helps performance.
                buf.append("SELECT * FROM (SELECT ");
                buf.append(PAGE_ALIAS);
                buf.append(".*, ROWNUM AS ");
                buf.append(ROWNUM_ALIAS);
                buf.append(" FROM (");
                buf.append(sqlQuery);
                buf.append(") ");
                buf.append(PAGE_ALIAS);
                buf.append(") ");
                buf.append("WHERE ");
                buf.append(ROWNUM_ALIAS);
                buf.append(" >= ");
                buf.append(query.getStartRange());
                buf.append(" AND ROWNUM <= ");
                buf.append(rowcount);
            }

            sqlQuery = buf;
        }
        break;
    case MYSQL:
        if (query.getStartRange() != null && query.getEndRange() != null) {
            long offset = query.getStartRange() - 1; // inclusive
            if (offset < 0)
                offset = 0;
            long rowcount = query.getEndRange() - offset;
            sqlQuery.append(" LIMIT "); // e.g. LIMIT offset,numrows
            sqlQuery.append(String.valueOf(offset));
            sqlQuery.append(",");
            sqlQuery.append(String.valueOf(rowcount));
        }
        break;
    default:
    }

    List<List<PropertyPair>> rows = new ArrayList<List<PropertyPair>>();
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        statement = con.prepareStatement(sqlQuery.toString(), ResultSet.TYPE_FORWARD_ONLY, /*
                                                                                            * ResultSet
                                                                                            * .
                                                                                            * TYPE_SCROLL_INSENSITIVE
                                                                                            * ,
                                                                                            */
                ResultSet.CONCUR_READ_ONLY);
        // statement.setFetchSize(32);
        // log.debug("setting fetch size 32");

        // set params
        // FIXME: params are pre-converted
        // to string in filter assembly
        int paramCount = 0;
        if (filterAssembler != null) {
            params = filterAssembler.getParams();
            if (params != null) {
                paramCount = params.length;
                for (i = 0; i < params.length; i++)
                    statement.setObject(i + 1, params[i]);
            }
        }

        // execute
        long before = System.currentTimeMillis();
        statement.execute();
        long after = System.currentTimeMillis();

        if (log.isDebugEnabled()) {
            if (params == null || params.length == 0) {
                log.debug("executed: " + sqlQuery.toString() + " (" + String.valueOf(after - before) + ")");
            } else {
                StringBuilder paramBuf = new StringBuilder();
                paramBuf.append(" [");
                for (int p = 0; p < params.length; p++) {
                    if (p > 0)
                        paramBuf.append(", ");
                    paramBuf.append(String.valueOf(params[p]));
                }
                paramBuf.append("]");
                log.debug("executed: " + sqlQuery.toString() + " " + paramBuf.toString() + " ("
                        + String.valueOf(after - before) + ")");
            }
        }

        // read results
        before = System.currentTimeMillis();
        int numresults = 0;
        rs = statement.getResultSet();
        int numcols = rs.getMetaData().getColumnCount();
        ResultSetMetaData rsMeta = rs.getMetaData();
        List<PropertyPair> row = null;
        PropertyPair pair = null;
        while (rs.next()) {
            row = new ArrayList<PropertyPair>();
            rows.add(row);
            for (i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnLabel(i); // mysql 5.5
                // returns
                // original
                // table col
                // name for
                // views
                if (columnName == null)
                    columnName = rsMeta.getColumnName(i);
                if (ROWNUM_ALIAS.equals(columnName))
                    continue;
                int columnType = rsMeta.getColumnType(i);

                PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName);
                PlasmaProperty valueProp = prop;
                while (!valueProp.getType().isDataType()) {
                    valueProp = this.statementUtil.getOppositePriKeyProperty(valueProp);
                }

                Object value = converter.fromJDBCDataType(rs, i, columnType, valueProp);
                if (value != null) {
                    pair = new PropertyPair(prop, value);
                    pair.setColumn(i);
                    if (!valueProp.equals(prop))
                        pair.setValueProp(valueProp);
                    row.add(pair);
                }
            }
            numresults++;
        }
        after = System.currentTimeMillis();
        if (log.isDebugEnabled())
            log.debug("read " + numresults + " results (" + String.valueOf(after - before) + ")");
    } catch (Throwable t) {
        StringBuffer buf = this.generateErrorDetail(t, sqlQuery.toString(), filterAssembler);
        log.error(buf.toString());
        throw new DataAccessException(t);
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
    }
    return rows;
}

From source file:com.mmnaseri.dragonfly.fluent.impl.AbstractSelectQueryFinalizer.java

private <H> List<Map<Mapping, Object>> execute(SelectQueryExecution<E, H> selection) {
    final Connection connection = session.getConnection();
    final PreparedStatement preparedStatement;
    try {//from  w  w w  .j  a  va2  s . c o m
        final String sql = selection.getSql() + ";";
        LogFactory.getLog(Statement.class).info("Preparing statement: " + sql);
        preparedStatement = connection.prepareStatement(sql);
    } catch (SQLException e) {
        throw new DatabaseNegotiationException("Failed to get a prepared statement from the database", e);
    }
    for (ParameterDescriptor descriptor : selection.getParameters()) {
        try {
            if (descriptor.getValue() == null) {
                preparedStatement.setNull(descriptor.getIndex(), descriptor.getSqlType());
            } else {
                preparedStatement.setObject(descriptor.getIndex(), descriptor.getValue());
            }
        } catch (SQLException e) {
            throw new StatementPreparationException(
                    "Failed to prepare statement for parameter " + descriptor.getIndex(), e);
        }
    }
    final ResultSet resultSet;
    final ResultSetMetaData metaData;
    try {
        resultSet = preparedStatement.executeQuery();
    } catch (SQLException e) {
        throw new DatabaseNegotiationException("Failed to retrieve the results from the data source", e);
    }
    try {
        metaData = resultSet.getMetaData();
    } catch (SQLException e) {
        throw new DatabaseNegotiationException("Failed to get result set metadata for query", e);
    }
    final ArrayList<Map<Mapping, Object>> result = new ArrayList<Map<Mapping, Object>>();
    while (true) {
        try {
            if (!resultSet.next()) {
                break;
            }
            final HashMap<Mapping, Object> map = new HashMap<Mapping, Object>();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                map.put(new ImmutableMapping(metaData.getTableName(i), metaData.getColumnName(i),
                        metaData.getColumnLabel(i)), resultSet.getObject(i));
            }
            result.add(map);
        } catch (SQLException e) {
            throw new DatabaseNegotiationException("Failed to get the next row", e);
        }

    }
    return result;
}

From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java

/**
 * validate schema generated by "set" command
 * @throws SQLException/*from   w ww.  j  a v a2 s .  c  o  m*/
 */
public void testSetCommand() throws SQLException {
    // execute set command
    String sql = "set -v";
    Statement stmt = con.createStatement();
    ResultSet res = stmt.executeQuery(sql);

    // Validate resultset columns
    ResultSetMetaData md = res.getMetaData();
    assertEquals(1, md.getColumnCount());
    assertEquals(SET_COLUMN_NAME, md.getColumnLabel(1));

    //check if there is data in the resultset
    assertTrue("Nothing returned by set -v", res.next());

    res.close();
    stmt.close();
}

From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java

/**
 * verify 'explain ...' resultset//  ww  w.  ja va2s .c o  m
 * @throws SQLException
 */
public void testExplainStmt() throws SQLException {
    Statement stmt = con.createStatement();

    ResultSet res = stmt.executeQuery("explain select c1, c2, c3, c4, c5 as a, c6, c7, c8, c9, c10, c11, c12, "
            + "c1*2, sentences(null, null, null) as b from " + dataTypeTableName + " limit 1");

    ResultSetMetaData md = res.getMetaData();
    assertEquals(md.getColumnCount(), 1); // only one result column
    assertEquals(md.getColumnLabel(1), EXPL_COLUMN_NAME); // verify the column name
    //verify that there is data in the resultset
    assertTrue("Nothing returned explain", res.next());
}

From source file:org.springframework.jdbc.object.StoredProcedureTests.java

public void testStoredProcedureWithUndeclaredResults() throws Exception {
    MockControl ctrlResultSet1 = MockControl.createControl(ResultSet.class);
    ResultSet mockResultSet1 = (ResultSet) ctrlResultSet1.getMock();
    mockResultSet1.next();/*ww w  . ja  v  a  2 s. co  m*/
    ctrlResultSet1.setReturnValue(true);
    mockResultSet1.getString(2);
    ctrlResultSet1.setReturnValue("Foo");
    mockResultSet1.next();
    ctrlResultSet1.setReturnValue(true);
    mockResultSet1.getString(2);
    ctrlResultSet1.setReturnValue("Bar");
    mockResultSet1.next();
    ctrlResultSet1.setReturnValue(false);
    mockResultSet1.close();
    ctrlResultSet1.setVoidCallable();

    MockControl ctrlMetaData = MockControl.createControl(ResultSetMetaData.class);
    ResultSetMetaData mockMetaData = (ResultSetMetaData) ctrlMetaData.getMock();
    mockMetaData.getColumnCount();
    ctrlMetaData.setReturnValue(2);
    mockMetaData.getColumnLabel(1);
    ctrlMetaData.setReturnValue("spam");
    mockMetaData.getColumnLabel(2);
    ctrlMetaData.setReturnValue("eggs");

    MockControl ctrlResultSet2 = MockControl.createControl(ResultSet.class);
    ResultSet mockResultSet2 = (ResultSet) ctrlResultSet2.getMock();
    mockResultSet2.getMetaData();
    ctrlResultSet2.setReturnValue(mockMetaData);
    mockResultSet2.next();
    ctrlResultSet2.setReturnValue(true);
    mockResultSet2.getObject(1);
    ctrlResultSet2.setReturnValue("Spam");
    mockResultSet2.getObject(2);
    ctrlResultSet2.setReturnValue("Eggs");
    mockResultSet2.next();
    ctrlResultSet2.setReturnValue(false);
    mockResultSet2.close();
    ctrlResultSet2.setVoidCallable();

    mockCallable.execute();
    ctrlCallable.setReturnValue(true);
    mockCallable.getUpdateCount();
    ctrlCallable.setReturnValue(-1);
    mockCallable.getResultSet();
    ctrlCallable.setReturnValue(mockResultSet1);
    mockCallable.getMoreResults();
    ctrlCallable.setReturnValue(true);
    mockCallable.getUpdateCount();
    ctrlCallable.setReturnValue(-1);
    mockCallable.getResultSet();
    ctrlCallable.setReturnValue(mockResultSet2);
    mockCallable.getMoreResults();
    ctrlCallable.setReturnValue(false);
    mockCallable.getUpdateCount();
    ctrlCallable.setReturnValue(0);
    mockCallable.getMoreResults();
    ctrlCallable.setReturnValue(false);
    mockCallable.getUpdateCount();
    ctrlCallable.setReturnValue(-1);
    if (debugEnabled) {
        mockCallable.getWarnings();
        ctrlCallable.setReturnValue(null);
    }
    mockCallable.close();
    ctrlCallable.setVoidCallable();

    mockConnection.prepareCall("{call " + StoredProcedureWithResultSetMapped.SQL + "()}");
    ctrlConnection.setReturnValue(mockCallable);

    replay();
    ctrlResultSet1.replay();
    ctrlMetaData.replay();
    ctrlResultSet2.replay();

    StoredProcedureWithResultSetMapped sproc = new StoredProcedureWithResultSetMapped(mockDataSource);
    Map res = sproc.execute();

    ctrlResultSet1.verify();
    ctrlResultSet2.verify();

    assertEquals("incorrect number of returns", 3, res.size());

    List rs1 = (List) res.get("rs");
    assertEquals(2, rs1.size());
    assertEquals("Foo", rs1.get(0));
    assertEquals("Bar", rs1.get(1));

    List rs2 = (List) res.get("#result-set-2");
    assertEquals(1, rs2.size());
    Object o2 = rs2.get(0);
    assertTrue("wron type returned for result set 2", o2 instanceof Map);
    Map m2 = (Map) o2;
    assertEquals("Spam", m2.get("spam"));
    assertEquals("Eggs", m2.get("eggs"));

    Number n = (Number) res.get("#update-count-1");
    assertEquals("wrong update count", 0, n.intValue());
}

From source file:org.traccar.database.QueryBuilder.java

public <T> Collection<T> executeQuery(Class<T> clazz) throws SQLException {
    List<T> result = new LinkedList<>();

    if (query != null) {

        try {/*from  w w  w .ja va 2  s  .c o  m*/

            try (ResultSet resultSet = statement.executeQuery()) {

                ResultSetMetaData resultMetaData = resultSet.getMetaData();

                List<ResultSetProcessor<T>> processors = new LinkedList<>();

                Method[] methods = clazz.getMethods();

                for (final Method method : methods) {
                    if (method.getName().startsWith("set") && method.getParameterTypes().length == 1
                            && !method.isAnnotationPresent(QueryIgnore.class)) {

                        final String name = method.getName().substring(3);

                        // Check if column exists
                        boolean column = false;
                        for (int i = 1; i <= resultMetaData.getColumnCount(); i++) {
                            if (name.equalsIgnoreCase(resultMetaData.getColumnLabel(i))) {
                                column = true;
                                break;
                            }
                        }
                        if (!column) {
                            continue;
                        }

                        addProcessors(processors, method.getParameterTypes()[0], method, name);
                    }
                }

                while (resultSet.next()) {
                    try {
                        T object = clazz.newInstance();
                        for (ResultSetProcessor<T> processor : processors) {
                            processor.process(object, resultSet);
                        }
                        result.add(object);
                    } catch (InstantiationException | IllegalAccessException e) {
                        throw new IllegalArgumentException();
                    }
                }
            }

        } finally {
            statement.close();
            connection.close();
        }
    }

    return result;
}

From source file:org.openecomp.sdnc.sli.resource.sql.SqlResource.java

public void saveCachedRowSetToCtx(CachedRowSet results, SvcLogicContext ctx, String prefix,
        DbLibService dblibSvc) throws SQLException {
    if (ctx != null) {
        if ((prefix != null) && prefix.endsWith("[]")) {
            // Return an array.
            String pfx = prefix.substring(0, prefix.length() - 2);
            int idx = 0;
            do {/* w ww  .  jav  a 2 s. c om*/
                ResultSetMetaData rsMeta = results.getMetaData();
                int numCols = rsMeta.getColumnCount();

                for (int i = 0; i < numCols; i++) {
                    String colValue = null;
                    String tableName = rsMeta.getTableName(i + 1);
                    if (rsMeta.getColumnType(i + 1) == java.sql.Types.VARBINARY) {
                        colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1),
                                results.getBytes(i + 1), dblibSvc);
                    } else {
                        colValue = results.getString(i + 1);
                    }
                    LOG.debug("Setting " + pfx + "[" + idx + "]."
                            + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue);
                    ctx.setAttribute(pfx + "[" + idx + "]." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"),
                            colValue);
                }
                idx++;
            } while (results.next());
            LOG.debug("Setting " + pfx + "_length = " + idx);
            ctx.setAttribute(pfx + "_length", "" + idx);
        } else {
            ResultSetMetaData rsMeta = results.getMetaData();
            int numCols = rsMeta.getColumnCount();

            for (int i = 0; i < numCols; i++) {
                String colValue = null;
                String tableName = rsMeta.getTableName(i + 1);
                if ("VARBINARY".equalsIgnoreCase(rsMeta.getColumnTypeName(i + 1))) {
                    colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1), results.getBytes(i + 1),
                            dblibSvc);
                } else {
                    colValue = results.getString(i + 1);
                }
                if (prefix != null) {
                    LOG.debug("Setting " + prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-")
                            + " = " + colValue);
                    ctx.setAttribute(prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"),
                            colValue);
                } else {
                    LOG.debug(
                            "Setting " + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue);
                    ctx.setAttribute(rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue);
                }
            }
        }
    }
}

From source file:rapture.repo.jdbc.JDBCStructuredStore.java

private List<Map<String, Object>> getCursorResult(String cursorId, int count, boolean isForward) {
    ResultSet rs = cache.getCursor(cursorId);
    if (rs == null) {
        throw RaptureExceptionFactory.create(
                String.format("Invalid cursorId [%s] provided.  No existing cursor in cache.", cursorId));
    }//from   w ww  .  j av  a2  s.  c  o m
    try {
        int currentCount = 0;
        ResultSetMetaData rsmd = rs.getMetaData();
        int numColumns = rsmd.getColumnCount();
        List<Map<String, Object>> ret = new ArrayList<>();
        while (currentCount++ < count && !rs.isClosed() && (isForward ? rs.next() : rs.previous())) {
            Map<String, Object> row = new TreeMap<>(String.CASE_INSENSITIVE_ORDER);
            for (int i = 1; i <= numColumns; i++) {
                row.put(rsmd.getColumnLabel(i), rs.getObject(i));
            }
            ret.add(row);
        }
        return ret.isEmpty() ? null : ret;
    } catch (SQLException e) {
        log.error(ExceptionToString.format(e));
        throw RaptureExceptionFactory
                .create(String.format("SQL Exception while traversing ResultSet: [%s]", e.getMessage()));
    }
}

From source file:QueryConnector.java

private void loadData(XSpreadsheet sheet, CellAddress startCell, Query query, boolean saveConnectionInfo)
        throws Exception {
    XCellRange cellRange = UnoRuntime.queryInterface(XCellRange.class, sheet);
    com.sun.star.lang.Locale locale = new com.sun.star.lang.Locale();
    //effettuo la query sul db
    Class.forName(query.getDriverClass());
    Connection con = DriverManager.getConnection(query.getURL(), query.getUsername(), query.getPassword());
    Statement stmt = con.createStatement();
    ResultSet result = stmt.executeQuery(query.getQuery());
    ResultSetMetaData metaData = result.getMetaData();
    int columnCount = metaData.getColumnCount();
    //intestazione del risultato (nomi delle colonne)
    for (int i = 1, j = startCell.Column; i <= columnCount; i++, j++) {
        XCell curCellHeader = cellRange.getCellByPosition(j, startCell.Row);
        XTextRange currentCellHeaderText = UnoRuntime.queryInterface(XTextRange.class, curCellHeader);
        currentCellHeaderText.setString(metaData.getColumnLabel(i));
        if (saveConnectionInfo)
            setCellUserProperty(curCellHeader, QUERY_PROPERTY, query.getName());
    }/*www  .j  a  v a 2s .co m*/
    //contenuto della query
    int rowCount = 1;
    int rowIdx = startCell.Row + 1;
    while (result.next()) {
        for (int i = 1, j = startCell.Column; i <= columnCount; i++, j++) {
            XCell curCellData = cellRange.getCellByPosition(j, rowIdx);
            XPropertySet cellDataProps = UnoRuntime.queryInterface(XPropertySet.class, curCellData);
            XTextRange currentCellDataText = UnoRuntime.queryInterface(XTextRange.class, curCellData);
            Object cellValue = result.getObject(i);
            if (cellValue instanceof java.sql.Date || cellValue instanceof java.sql.Time
                    || cellValue instanceof java.util.Date) {
                cellDataProps.setPropertyValue("NumberFormat",
                        this.numberFormats.queryKey("GG/MM/AAAA HH.MM.SS", locale, true));
                curCellData.setValue(dateValue(DATE_FORMATTER.format((java.util.Date) cellValue)));
            } else if (cellValue instanceof Number) {
                cellDataProps.setPropertyValue("NumberFormat", this.numberFormats.queryKey("#", locale, true));
                Number number = (Number) cellValue;
                curCellData.setValue(number.doubleValue());
            } else if (cellValue == null) {
                currentCellDataText.setString("");
            } else
                currentCellDataText.setString(cellValue.toString());
            if (saveConnectionInfo)
                setCellUserProperty(curCellData, QUERY_PROPERTY, query.getName());
        }
        rowCount++;
        rowIdx++;
    }
    result.close();
    stmt.close();
    con.close();
    //aggiono le informazioni sul range dei dati selezionato nella query
    query.setPositionAndArea(startCell.Column, startCell.Row, columnCount, rowCount);
    //salvo le informazioni sul documento
    if (saveConnectionInfo)
        settings.saveQuery(query);
}