Example usage for java.sql ResultSet getObject

List of usage examples for java.sql ResultSet getObject

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. getObject

Introduction

In this page you can find the example usage for java.sql ResultSet getObject.

Prototype

Object getObject(String columnLabel) throws SQLException;

Source Link

Document

Gets the value of the designated column in the current row of this ResultSet object as an Object in the Java programming language.

Usage

From source file:ru.org.linux.user.UserLogDao.java

@Nonnull
public List<UserLogItem> getLogItems(@Nonnull User user, boolean includeSelf) {
    String sql = includeSelf//ww  w. j  a  va2  s .  co m
            ? "SELECT id, userid, action_userid, action_date, action, info FROM user_log WHERE userid=? ORDER BY id DESC"
            : "SELECT id, userid, action_userid, action_date, action, info FROM user_log WHERE userid=? AND userid!=action_userid ORDER BY id DESC";

    return jdbcTemplate.query(sql, new RowMapper<UserLogItem>() {
        @Override
        public UserLogItem mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new UserLogItem(rs.getInt("id"), rs.getInt("userid"), rs.getInt("action_userid"),
                    new DateTime(rs.getTimestamp("action_date")),
                    UserLogAction.valueOf(rs.getString("action").toUpperCase()),
                    (Map<String, String>) rs.getObject("info"));
        }
    }, user.getId());
}

From source file:io.lightlink.spring.StreamingMapper.java

@Override
public Object mapRow(ResultSet resultSet, int i) throws SQLException {

    if (nestedRowMapper != null) {
        Object row;//from   www. ja  v a 2s  .c o m
        row = nestedRowMapper.mapRow(resultSet, i);
        responseStream.writeFullObjectToArray(new BeanMap(row));
    } else {
        if (colCount == 0)
            readMetadata(resultSet);
        responseStream.writeObjectStart();
        for (int j = 0; j < colCount; j++) {
            Object value = resultSet.getObject(j + 1);
            Object converted = SQLHandler.genericConvertFromJdbc(null, value);
            responseStream.writeProperty(colNames[j], converted);
        }
        responseStream.writeObjectEnd();
    }
    return null;
}

From source file:mayoapp.migrations.V0400_2024__move_order_items_to_own_table.java

@Override
public void migrate(Connection connection) throws Exception {
    connection.setAutoCommit(false);/*from   w w  w .  j a v a  2 s  .c  o  m*/

    Statement queryStatement = connection.createStatement();
    ResultSet data = queryStatement.executeQuery("SELECT * from purchase_order");

    List<Order> orders = Lists.newArrayList();
    List<OrderItem> orderItems = Lists.newArrayList();

    ObjectMapper mapper = new ObjectMapper();

    while (data.next()) {
        Order order = new Order();
        order.setId((UUID) data.getObject("entity_id"));

        String orderDataString = data.getString("order_data");
        Map<String, Object> orderData = mapper.readValue(orderDataString,
                new TypeReference<Map<String, Object>>() {
                });

        List<Map<String, Object>> items = (List<Map<String, Object>>) orderData.get("items");

        for (Map<String, Object> item : items) {
            OrderItem orderItem = new OrderItem();

            orderItem.setId(UUID.randomUUID());
            orderItem.setOrderId(order.getId());

            if (item.containsKey("id") && String.class.isAssignableFrom(item.get("id").getClass())) {
                orderItem.setPurchasableId(UUID.fromString((String) item.get("id")));
            }
            orderItem.setType((String) item.get("type"));
            orderItem.setTitle((String) item.get("title"));
            orderItem.setQuantity(((Integer) item.get("quantity")).longValue());
            orderItem.setUnitPrice(BigDecimal.valueOf((Double) item.get("unitPrice")));
            orderItem.setItemTotal(BigDecimal.valueOf((Double) item.get("itemTotal")));
            if (item.containsKey("vatRate")) {
                orderItem.setVatRate(BigDecimal.valueOf((Double) item.get("vatRate")));
            }

            if (item.containsKey("addons")) {
                orderItem.addData("addons", convertAddonsToMap((List<Map<String, Object>>) item.get("addons")));
            }

            orderItems.add(orderItem);
        }

        orderData.remove("items");
        order.setOrderData(orderData);
        orders.add(order);
    }

    queryStatement.close();

    // 1. Update orders

    PreparedStatement updateOrders = connection
            .prepareStatement("UPDATE purchase_order SET order_data = CAST (? AS json) WHERE entity_id =?");

    for (Order order : orders) {
        updateOrders.setObject(1, mapper.writeValueAsString(order.getOrderData()));
        updateOrders.setObject(2, order.getId());
        updateOrders.addBatch();
    }

    updateOrders.executeBatch();

    // 2. Insert items

    PreparedStatement insertItems = connection.prepareStatement(
            "INSERT INTO purchase_order_item (id, order_id, purchasable_id, type, title, quantity, unit_price, "
                    + "item_total, vat_rate, data) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, CAST (? as json))");

    for (OrderItem item : orderItems) {
        insertItems.setObject(1, item.getId());
        insertItems.setObject(2, item.getOrderId());
        insertItems.setObject(3, item.getPurchasableId());
        insertItems.setString(4, item.getType());
        insertItems.setString(5, item.getTitle());
        insertItems.setLong(6, item.getQuantity());
        insertItems.setBigDecimal(7, item.getUnitPrice());
        insertItems.setBigDecimal(8, item.getItemTotal());
        insertItems.setBigDecimal(9, item.getVatRate());
        insertItems.setString(10, mapper.writeValueAsString(item.getData()));
        insertItems.addBatch();
    }

    insertItems.executeBatch();
}

From source file:com.manydesigns.portofino.persistence.QueryUtils.java

/**
 * Runs a SQL query against a session. The query can contain placeholders for the parameters, as supported by
 * {@link PreparedStatement}.//from   w w  w .j  a  va 2 s. com
 * 
 * @param session the session
 * @param queryString the query
 * @param parameters parameters to substitute in the query
 * @return the results of the query as an Object[] (an array cell per column)
 */
public static List<Object[]> runSql(Session session, final String queryString, final Object[] parameters) {
    final List<Object[]> result = new ArrayList<Object[]>();

    try {
        session.doWork(new Work() {
            public void execute(Connection connection) throws SQLException {
                PreparedStatement stmt = connection.prepareStatement(queryString);
                ResultSet rs = null;
                try {
                    for (int i = 0; i < parameters.length; i++) {
                        stmt.setObject(i + 1, parameters[i]);
                    }
                    rs = stmt.executeQuery();
                    ResultSetMetaData md = rs.getMetaData();
                    int cc = md.getColumnCount();
                    while (rs.next()) {
                        Object[] current = new Object[cc];
                        for (int i = 0; i < cc; i++) {
                            current[i] = rs.getObject(i + 1);
                        }
                        result.add(current);
                    }
                } finally {
                    if (null != rs) {
                        rs.close();
                    }
                    if (null != stmt) {
                        stmt.close();
                    }
                }
            }
        });
    } catch (HibernateException e) {
        session.getTransaction().rollback();
        session.beginTransaction();
        throw e;
    }

    return result;
}

From source file:com.itdaoshi.dokeos.dao.UserDAObject.java

@Override
protected Long getNextPrimaryID() {

    QueryRunner run = new QueryRunner();
    ResultSetHandler h = new ResultSetHandler() {
        public Object handle(ResultSet rs) throws SQLException {
            if (!rs.next()) {
                return null;
            }/* ww w  .ja  v  a 2 s.  co  m*/

            ResultSetMetaData meta = rs.getMetaData();
            int cols = meta.getColumnCount();
            Object[] result = new Object[cols];

            for (int i = 0; i < cols; i++) {
                result[i] = rs.getObject(i + 1);
            }

            return result;
        }
    };
    try {
        Object[] result = (Object[]) run.query(conn, "SELECT MAX(user_id) FROM USER ", h);
        return (Long) result[0] + 1;
        // do something with the result
    } catch (Exception e) {
        e.printStackTrace();

    }

    return null;

}

From source file:com.streamsets.pipeline.stage.it.KeywordsInObjectNamesIT.java

@Test
public void testKeywordInDbTableColumnName() throws Exception {
    executeUpdate(Utils.format("CREATE DATABASE IF NOT EXISTS `{}`", keyword));
    HiveMetadataProcessor processor = new HiveMetadataProcessorBuilder().database(keyword).table(keyword)
            .build();//from w w  w  .j  a  v a 2s. co m
    HiveMetastoreTarget hiveTarget = new HiveMetastoreTargetBuilder().build();

    Map<String, Field> map = new LinkedHashMap<>();
    map.put(keyword, Field.create("value"));
    Record record = RecordCreator.create("s", "s:1");
    record.set(Field.create(map));

    try {
        processRecords(processor, hiveTarget, ImmutableList.of(record));
    } catch (StageException se) {
        LOG.error("Processing exception", se);
        Assert.fail("Processing testing record unexpectedly failed: " + se.getMessage());
        throw se;
    }

    String fullTableName = Utils.format("`{}`.`{}`", keyword, keyword);
    assertTableExists(fullTableName);
    assertQueryResult(Utils.format("select * from {}", fullTableName), new QueryValidator() {
        @Override
        public void validateResultSet(ResultSet rs) throws Exception {
            assertResultSetStructure(rs,
                    new ImmutablePair(Utils.format("{}.{}", keyword, keyword), Types.VARCHAR),
                    new ImmutablePair(Utils.format("{}.dt", keyword), Types.VARCHAR));

            Assert.assertTrue("Table tbl doesn't contain any rows", rs.next());
            Assert.assertEquals("value", rs.getObject(1));
            Assert.assertFalse("Table tbl contains more then one row", rs.next());
        }
    });
}

From source file:org.string_db.jdbc.Postgres2HSQLDB.java

void dumpTableData(final String schema, final String table, String filter, final StringBuilder result,
        final String[] columnsToInclude) {
    final String selectedColumns = columnsToInclude != null ? Joiner.on(',').join(columnsToInclude) : "*";
    final String sql = "SELECT " + selectedColumns + " FROM " + schema + "." + table
            + (filter != null ? " WHERE " + filter : "");
    final String columns = columnsToInclude != null ? "(" + Joiner.on(", ").join(columnsToInclude) + ")" : null;

    result.append("SET SCHEMA ").append(schema).append(";\n");
    jdbcTemplate.query(sql, new RowCallbackHandler() {
        @Override//from  ww  w. j a va2s.  co  m
        public void processRow(ResultSet rs) throws SQLException {
            result.append("INSERT INTO ").append(table);
            if (columns != null)
                result.append(columns);
            result.append(" VALUES(");
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                if (i > 0) {
                    result.append(", ");
                }
                Object value = rs.getObject(i + 1);
                if (value == null) {
                    result.append("NULL");
                } else {
                    String outputValue = value.toString();
                    // In a string started with ' (singlequote) use '' (two singlequotes) to create a ' (singlequote).
                    //see http://www.hsqldb.org/doc/guide/ch09.html#expression-section
                    //XXX use Connection.escapeString ?
                    outputValue = outputValue.replaceAll("'", "''");
                    result.append("'" + outputValue + "'");
                }
            }
            result.append(");\n");
        }
    });
}

From source file:com.squid.core.jdbc.formatter.DefaultJDBCDataFormatter.java

public String formatJDBCObject(final Object jdbcObject, final int colType) throws SQLException {
    String value = "";

    switch (colType) {
    case Types.BIT:
        if (jdbcObject != null) {
            value = String.valueOf(jdbcObject);
        }//from   w w  w  . j a va 2s  .  c  om
        break;
    case Types.BOOLEAN:
        if (jdbcObject != null) {
            value = new Boolean(jdbcObject.toString()).toString();
        }
        break;
    case Types.BIGINT:
    case Types.DECIMAL:
    case Types.DOUBLE:
    case Types.FLOAT:
    case Types.REAL:
    case Types.NUMERIC:
        if (jdbcObject != null) {
            value = "" + formatter.formatDecimal(jdbcObject);
        }
        break;
    case Types.INTEGER:
    case Types.TINYINT:
    case Types.SMALLINT:
        if (jdbcObject != null) {
            value = "" + formatter.formatDecimal(jdbcObject);
        }
        break;
    case Types.CLOB:
        if (jdbcObject != null) {
            try {
                value = read((Clob) jdbcObject);
            } catch (SQLException sqle) {
                value = "";
            } catch (IOException ioe) {
                value = "";
            }
        }
        break;
    case Types.ARRAY:
        try {
            ResultSet rs = ((Array) jdbcObject).getResultSet();
            int arrayType = ((Array) jdbcObject).getBaseType();
            boolean isNotNew = false;
            while (rs.next()) {
                String current = formatJDBCObject(rs.getObject(2), arrayType);
                if ("".equals(current.trim()) == false) {
                    if (isNotNew) {
                        value = value + ",";
                    } else {
                        isNotNew = !isNotNew;
                    }
                    value = value + current;
                }
            }
            if ("".equals(value) == false) {
                value = "{" + value + "}";
            }
        } catch (SQLException sqle) {
            value = "";
        }
        break;
    case Types.JAVA_OBJECT:
        if (jdbcObject != null) {
            value = String.valueOf(jdbcObject);
        }
        break;
    case Types.DATE:
        if (jdbcObject != null) {
            value = formatter.formatDate(jdbcObject);
        }
        break;
    case Types.TIME:
        if (jdbcObject != null) {
            value = ((Time) jdbcObject).toString();
        }
        break;
    case Types.TIMESTAMP:
        if (jdbcObject != null) {
            value = formatter.formatTimestamp(jdbcObject);
        }
        break;
    case Types.LONGVARCHAR:
    case Types.VARCHAR:
    case Types.CHAR:
        if (jdbcObject != null) {
            value = jdbcObject.toString();
        } else {
            value = "NULL";
        }
        break;
    case Types.BINARY:
        value = new String((byte[]) jdbcObject);
        break;
    default:
        value = "";
    }
    if (value == null) {
        value = "";
    }
    return value;
}

From source file:org.zenoss.zep.dao.impl.EventIndexQueueDaoImpl.java

@Override
@TransactionalRollbackAllExceptions/*from  ww w  . ja va 2  s .  c o m*/
public List<Long> indexEvents(final EventIndexHandler handler, final int limit, final long maxUpdateTime)
        throws ZepException {
    final Map<String, Object> selectFields = new HashMap<String, Object>();
    selectFields.put("_limit", limit);

    final String sql;

    // Used for partition pruning
    final String queryJoinLastSeen = (this.isArchive) ? "AND iq.last_seen=es.last_seen " : "";

    if (maxUpdateTime > 0L) {
        selectFields.put("_max_update_time", timestampConverter.toDatabaseType(maxUpdateTime));
        sql = "SELECT iq.id AS iq_id, iq.uuid AS iq_uuid, iq.update_time AS iq_update_time," + "es.* FROM "
                + this.queueTableName + " AS iq " + "LEFT JOIN " + this.tableName + " es ON iq.uuid=es.uuid "
                + queryJoinLastSeen + "WHERE iq.update_time <= :_max_update_time "
                + "ORDER BY iq_id LIMIT :_limit";
    } else {
        sql = "SELECT iq.id AS iq_id, iq.uuid AS iq_uuid, iq.update_time AS iq_update_time," + "es.* FROM "
                + this.queueTableName + " AS iq " + "LEFT JOIN " + this.tableName + " es ON iq.uuid=es.uuid "
                + queryJoinLastSeen + "ORDER BY iq_id LIMIT :_limit";
    }

    final Set<String> eventUuids = new HashSet<String>();
    final List<Long> indexQueueIds = this.template.query(sql, new RowMapper<Long>() {
        @Override
        public Long mapRow(ResultSet rs, int rowNum) throws SQLException {
            final long iqId = rs.getLong("iq_id");
            final String iqUuid = uuidConverter.fromDatabaseType(rs, "iq_uuid");
            // Don't process the same event multiple times.
            if (eventUuids.add(iqUuid)) {
                final Object uuid = rs.getObject("uuid");
                if (uuid != null) {
                    EventSummary summary = rowMapper.mapRow(rs, rowNum);
                    try {
                        handler.handle(summary);
                    } catch (Exception e) {
                        throw new RuntimeException(e.getLocalizedMessage(), e);
                    }
                } else {
                    try {
                        handler.handleDeleted(iqUuid);
                    } catch (Exception e) {
                        throw new RuntimeException(e.getLocalizedMessage(), e);
                    }
                }
            }
            return iqId;
        }
    }, selectFields);

    if (!indexQueueIds.isEmpty()) {
        try {
            handler.handleComplete();
        } catch (Exception e) {
            throw new ZepException(e.getLocalizedMessage(), e);
        }
    }

    // publish current size of event_*_index_queue table
    this.lastQueueSize = this.template.queryForInt("SELECT COUNT(1) FROM " + this.queueTableName);
    this.applicationEventPublisher
            .publishEvent(new EventIndexQueueSizeEvent(this, tableName, this.lastQueueSize, limit));

    return indexQueueIds;
}

From source file:org.gofleet.openLS.ddbb.dao.postgis.PostGisHBGeoCodingDAO.java

@Transactional(readOnly = true)
public List<AbstractResponseParametersType> geocoding(final GeocodeRequestType param) {
    HibernateCallback<List<AbstractResponseParametersType>> action = new HibernateCallback<List<AbstractResponseParametersType>>() {
        public List<AbstractResponseParametersType> doInHibernate(Session session)
                throws HibernateException, SQLException {

            List<AddressType> addressList = param.getAddress();
            List<AbstractResponseParametersType> res_ = new LinkedList<AbstractResponseParametersType>();

            for (AddressType addressType : addressList) {

                // TODO change deprecation?
                @SuppressWarnings("deprecation")
                CallableStatement consulta = session.connection()
                        .prepareCall("{call gls_geocoding(?, ?, ?, ?, ?)}");

                String street = GeoUtil.extractStreet(addressType);
                String munsub = GeoUtil.extractMunSub(addressType);
                String mun = GeoUtil.extractMun(addressType);
                String subcountry = GeoUtil.extractSubCountry(addressType);
                String country = GeoUtil.extractCountry(addressType);

                consulta.setString(1, street);
                consulta.setString(2, munsub);
                consulta.setString(3, mun);
                consulta.setString(4, subcountry);
                consulta.setString(5, country);

                LOG.debug(consulta);//from  www. jav  a 2  s . com

                ResultSet o = consulta.executeQuery();
                GeocodeResponseType grt = new GeocodeResponseType();
                while (o.next()) {
                    GeocodeResponseListType geocode = new GeocodeResponseListType();
                    try {
                        PGgeometry g = (PGgeometry) o.getObject("geometry");
                        Jdbc4Array address = (Jdbc4Array) o.getArray("address");

                        GeocodedAddressType addresstype = new GeocodedAddressType();
                        addresstype.setPoint(PostGisUtils.getReferencedPoint(g));
                        addresstype.setAddress(PostGisUtils.getAddress(address));

                        geocode.getGeocodedAddress().add(addresstype);

                        geocode.setNumberOfGeocodedAddresses(BigInteger.valueOf(1l));

                        grt.getGeocodeResponseList().add(geocode);
                    } catch (Throwable t) {
                        LOG.error("Error extracting data from database.", t);
                    }
                    res_.add(grt);
                }
            }
            return res_;
        }

    };

    return hibernateTemplate.executeWithNativeSession(action);
}