List of usage examples for java.sql ResultSet getObject
Object getObject(String columnLabel) throws SQLException;
Gets the value of the designated column in the current row of this ResultSet object as an Object in the Java programming language.
From source file:org.mayocat.shop.billing.store.jdbi.mapper.OrderMapper.java
@Override public Order map(int index, ResultSet resultSet, StatementContext ctx) throws SQLException { Order order = new Order(); fillOrderSummary(resultSet, order);//from w w w . j a v a 2s . com ObjectMapper mapper = new ObjectMapper(); //mapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false); try { List<Map<String, Object>> itemsData = mapper.readValue(resultSet.getString("items"), new TypeReference<List<Map<String, Object>>>() { }); List<OrderItem> items = FluentIterable.from(itemsData) .transform(new Function<Map<String, Object>, OrderItem>() { public OrderItem apply(Map<String, Object> map) { OrderItem orderItem = new OrderItem(); orderItem.setId(UUID.fromString((String) map.get("id"))); orderItem.setOrderId(UUID.fromString((String) map.get("order_id"))); if (map.containsKey("purchasable_id") && map.get("purchasable_id") != null) { // There might not be a purchasable id orderItem.setPurchasableId(UUID.fromString((String) map.get("purchasable_id"))); } orderItem.setType((String) map.get("type")); orderItem.setTitle((String) map.get("title")); orderItem.setMerchant((String) map.get("merchant")); orderItem.setQuantity(((Integer) map.get("quantity")).longValue()); orderItem.setUnitPrice(BigDecimal.valueOf((Double) map.get("unit_price"))); orderItem.setItemTotal(BigDecimal.valueOf((Double) map.get("item_total"))); if (map.containsKey("vat_rate") && map.get("vat_rate") != null) { // There might not be a VAT rate orderItem.setVatRate(BigDecimal.valueOf((Double) map.get("vat_rate"))); } if (map.containsKey("data") && map.get("data") != null) { // There might not be data orderItem.addData((Map<String, Object>) map.get("data")); } return orderItem; } }).toList(); order.setOrderItems(items); } catch (IOException e) { logger.error("Failed to deserialize order data", e); } try { resultSet.findColumn("email"); Customer customer = new Customer(); customer.setId(order.getCustomerId()); customer.setSlug(resultSet.getString("customer_slug")); customer.setEmail(resultSet.getString("email")); customer.setFirstName(resultSet.getString("first_name")); customer.setLastName(resultSet.getString("last_name")); customer.setPhoneNumber(resultSet.getString("phone_number")); order.setCustomer(customer); } catch (SQLException e) { // Nevermind } try { if (resultSet.getObject("billing_address_id") != null) { resultSet.findColumn("billing_address_full_name"); Address billing = new Address(); billing.setId((UUID) resultSet.getObject("billing_address_id")); billing.setFullName(resultSet.getString("billing_address_full_name")); billing.setStreet(resultSet.getString("billing_address_street")); billing.setStreetComplement(resultSet.getString("billing_address_street_complement")); billing.setZip(resultSet.getString("billing_address_zip")); billing.setCity(resultSet.getString("billing_address_city")); billing.setCountry(resultSet.getString("billing_address_country")); billing.setNote(resultSet.getString("billing_address_note")); order.setBillingAddress(billing); } } catch (SQLException e) { // Nevermind } try { if (resultSet.getObject("delivery_address_id") != null) { resultSet.findColumn("delivery_address_full_name"); Address delivery = new Address(); delivery.setId((UUID) resultSet.getObject("delivery_address_id")); delivery.setFullName(resultSet.getString("delivery_address_full_name")); delivery.setStreet(resultSet.getString("delivery_address_street")); delivery.setStreetComplement(resultSet.getString("delivery_address_street_complement")); delivery.setZip(resultSet.getString("delivery_address_zip")); delivery.setCity(resultSet.getString("delivery_address_city")); delivery.setCountry(resultSet.getString("delivery_address_country")); delivery.setNote(resultSet.getString("delivery_address_note")); order.setDeliveryAddress(delivery); } } catch (SQLException e) { // Nevermind } return order; }
From source file:com.egt.core.db.xdp.RecursoCachedRowSetDataProvider.java
private Object execute(String procedure, Object argumento) { // throws SQLException Bitacora.trace(getClass(), "executeFunction", procedure, argumento); if (StringUtils.isNotBlank(procedure) && argumento != null) { try {//from ww w . j av a2 s .c om if (TLC.getAgenteSql().isStoredProcedure(procedure)) { Object[] args = new Object[] { argumento }; Object resultado = TLC.getAgenteSql().executeProcedure(procedure, args); if (resultado instanceof ResultSet) { ResultSet resultSet = (ResultSet) resultado; if (resultSet.next()) { return resultSet.getObject(1); } // } else if (resultado instanceof Number) { // return resultado; } return resultado; } } catch (SQLException ex) { String localizedMessage = StringUtils.substringBefore(ex.getLocalizedMessage(), " Where: "); TLC.getBitacora().error(localizedMessage); return ex; } } return null; }
From source file:com.alibaba.wasp.jdbc.TestJdbcResultSet.java
@Test public void testInt() throws SQLException { trace("test INT"); ResultSet rs; Object o;//from w ww. j av a 2s .c om stat = conn.createStatement(); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(31,-1, 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(32,0, 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(33,1, 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(34," + Integer.MAX_VALUE + ", 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(35," + Integer.MIN_VALUE + ", 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(36,0, 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(37,0, 'testInt')"); // this should not be read - maxrows=6 // MySQL compatibility (is this required?) rs = stat.executeQuery("SELECT column1,column2,column3 FROM test where column3='testInt' ORDER BY column1"); // MySQL compatibility assertEquals(1, rs.findColumn("column1")); assertEquals(2, rs.findColumn("column2")); ResultSetMetaData meta = rs.getMetaData(); assertEquals(3, meta.getColumnCount()); assertTrue(rs.getRow() == 0); rs.next(); trace("default fetch size=" + rs.getFetchSize()); // 0 should be an allowed value (but it's not defined what is actually // means) rs.setFetchSize(1); assertThrows(SQLErrorCode.INVALID_VALUE_2, rs).setFetchSize(-1); // fetch size 100 is bigger than maxrows - not allowed rs.setFetchSize(6); assertTrue(rs.getRow() == 1); assertEquals(2, rs.findColumn("COLUMN2")); assertEquals(2, rs.findColumn("column2")); assertEquals(2, rs.findColumn("Column2")); assertEquals(1, rs.findColumn("COLUMN1")); assertEquals(1, rs.findColumn("column1")); assertEquals(1, rs.findColumn("Column1")); assertEquals(1, rs.findColumn("colUMN1")); assertTrue(rs.getInt(2) == -1 && !rs.wasNull()); assertTrue(rs.getInt("COLUMN2") == -1 && !rs.wasNull()); assertTrue(rs.getInt("column2") == -1 && !rs.wasNull()); assertTrue(rs.getInt("Column2") == -1 && !rs.wasNull()); assertTrue(rs.getString("Column2").equals("-1") && !rs.wasNull()); o = rs.getObject("column2"); trace(o.getClass().getName()); assertTrue(o instanceof Long); assertTrue(((Long) o).longValue() == -1); o = rs.getObject(2); trace(o.getClass().getName()); assertTrue(o instanceof Long); assertTrue(((Long) o).longValue() == -1); assertTrue(rs.getBoolean("Column2")); assertTrue(rs.getByte("Column2") == (byte) -1); assertTrue(rs.getShort("Column2") == (short) -1); assertTrue(rs.getLong("Column2") == -1); assertTrue(rs.getFloat("Column2") == -1.0); assertTrue(rs.getDouble("Column2") == -1.0); assertTrue(rs.getString("Column2").equals("-1") && !rs.wasNull()); assertTrue(rs.getInt("COLUMN1") == 31 && !rs.wasNull()); assertTrue(rs.getInt("column1") == 31 && !rs.wasNull()); assertTrue(rs.getInt("Column1") == 31 && !rs.wasNull()); assertTrue(rs.getInt(1) == 31 && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 2); assertTrue(rs.getInt(2) == 0 && !rs.wasNull()); assertTrue(!rs.getBoolean(2)); assertTrue(rs.getByte(2) == 0); assertTrue(rs.getShort(2) == 0); assertTrue(rs.getLong(2) == 0); assertTrue(rs.getFloat(2) == 0.0); assertTrue(rs.getDouble(2) == 0.0); assertTrue(rs.getString(2).equals("0") && !rs.wasNull()); assertTrue(rs.getInt(1) == 32 && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 3); assertTrue(rs.getInt("COLUMN1") == 33 && !rs.wasNull()); assertTrue(rs.getInt("COLUMN2") == 1 && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 4); assertTrue(rs.getInt("COLUMN1") == 34 && !rs.wasNull()); assertTrue(rs.getInt("COLUMN2") == Integer.MAX_VALUE && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 5); assertTrue(rs.getInt("column1") == 35 && !rs.wasNull()); assertTrue(rs.getInt("column2") == Integer.MIN_VALUE && !rs.wasNull()); assertTrue(rs.getString(1).equals("35") && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 6); assertTrue(rs.getInt("column1") == 36 && !rs.wasNull()); assertTrue(rs.getInt("column2") == 0 && !rs.wasNull()); assertTrue(rs.getInt(2) == 0 && !rs.wasNull()); assertTrue(rs.getInt(1) == 36 && !rs.wasNull()); assertTrue(rs.getString(1).equals("36") && !rs.wasNull()); assertTrue(rs.getString(2).equals("0") && !rs.wasNull()); assertTrue(!rs.wasNull()); // assertFalse(rs.next()); // assertEquals(0, rs.getRow()); // there is one more row, but because of setMaxRows we don't get it }
From source file:com.alibaba.wasp.jdbc.TestJdbcResultSet.java
@Test public void testVarchar() throws SQLException { trace("test VARCHAR"); ResultSet rs; Object o;//from w w w. j av a 2s . com stat = conn.createStatement(); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(1,10,'')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(2,10,' ')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(3,10,' ')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(4,10,'')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(5,10,'Hi')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(6,10,' Hi ')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(7,10,'Joe''s')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(8,10,'{escape}')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(9,10,'\\n')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(10,10,'\\'')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(11,10,'\\%')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(12,10,'\\%')"); rs = stat.executeQuery("SELECT column1,column2,column3 FROM test where column2=10 ORDER BY column1"); String value; rs.next(); value = rs.getString(3); trace("Value: <" + value + "> (should be: <>)"); assertTrue(value != null && value.equals("") && !rs.wasNull()); assertTrue(rs.getInt(1) == 1 && !rs.wasNull()); rs.next(); value = rs.getString(3); trace("Value: <" + value + "> (should be: < >)"); assertTrue(rs.getString(3).equals(" ") && !rs.wasNull()); assertTrue(rs.getInt(1) == 2 && !rs.wasNull()); rs.next(); value = rs.getString(3); trace("Value: <" + value + "> (should be: < >)"); assertTrue(rs.getString(3).equals(" ") && !rs.wasNull()); assertTrue(rs.getInt(1) == 3 && !rs.wasNull()); rs.next(); value = rs.getString(3); trace("Value: <" + value + "> (should be: <null>)"); assertTrue(rs.getString(3).equals("") && !rs.wasNull()); assertTrue(rs.getInt(1) == 4 && !rs.wasNull()); rs.next(); value = rs.getString(3); trace("Value: <" + value + "> (should be: <Hi>)"); assertTrue(rs.getInt(1) == 5 && !rs.wasNull()); assertTrue(rs.getString(3).equals("Hi") && !rs.wasNull()); o = rs.getObject("column3"); trace(o.getClass().getName()); assertTrue(o instanceof String); assertTrue(o.toString().equals("Hi")); rs.next(); value = rs.getString(3); trace("Value: <" + value + "> (should be: < Hi >)"); assertTrue(rs.getInt(1) == 6 && !rs.wasNull()); assertTrue(rs.getString(3).equals(" Hi ") && !rs.wasNull()); rs.next(); value = rs.getString(3); trace("Value: <" + value + "> (should be: <Joe's>)"); assertTrue(rs.getInt(1) == 7 && !rs.wasNull()); assertTrue(rs.getString(3).equals("Joe's") && !rs.wasNull()); rs.next(); value = rs.getString(3); trace("Value: <" + value + "> (should be: <{escape}>)"); assertTrue(rs.getInt(1) == 8 && !rs.wasNull()); assertTrue(rs.getString(3).equals("{escape}") && !rs.wasNull()); rs.next(); value = rs.getString(3); trace("Value: <" + value + "> (should be: <\\n>)"); assertTrue(rs.getInt(1) == 9 && !rs.wasNull()); assertTrue(rs.getString(3).equals("\n") && !rs.wasNull()); rs.next(); value = rs.getString(3); trace("Value: <" + value + "> (should be: <\\'>)"); assertTrue(rs.getInt(1) == 10 && !rs.wasNull()); assertTrue(rs.getString(3).equals("\'") && !rs.wasNull()); rs.next(); value = rs.getString(3); trace("Value: <" + value + "> (should be: <\\%>)"); assertTrue(rs.getInt(1) == 11 && !rs.wasNull()); assertTrue(rs.getString(3).equals("%") && !rs.wasNull()); // assertTrue(!rs.next()); }
From source file:com.SE.myPlayer.MusicPlayerGUI.java
public void tableReferesh(JTable songData_Table, String tableName, String columName) { int emptyResultSet = 0; try {// w ww .j a va 2s . c o m con = db.getCon(); stmt = con.createStatement(); ResultSet rs; switch (tableName) { case "library": rs = stmt.executeQuery("select * from library order by " + columName + ""); break; case "playlist": rs = stmt.executeQuery("select * from library order by " + columName + ""); break; default: rs = stmt.executeQuery( "Select library.id_songs, library.song_location, library.song_name, library.song_album, library.song_artist, library.genre, library.year, library.time, library.comment from playlist INNER JOIN library ON library.id_songs = playlist.id_songs AND playlist.playlist_name = '" + tableName + "' order by " + columName + ""); break; } DefaultTableModel myModel = new DefaultTableModel() { @Override public boolean isCellEditable(int row, int column) { return false; } }; String[] songsColumnsName = { "Location", "Name", "Album", "Artist", "Genre", "Year", "Time", "Comment" }; myModel.setColumnIdentifiers(songsColumnsName); ResultSetMetaData rsmd = rs.getMetaData(); int colNumbers = rsmd.getColumnCount(); Object[] objects = new Object[colNumbers]; while (rs.next()) { emptyResultSet = 1; for (int i = 0; i < colNumbers - 1; i++) { objects[i] = rs.getObject(i + 2); } myModel.addRow(objects); } if (emptyResultSet == 0) { myModel.addRow(objects); } songData_Table.setModel(myModel); rs = stmt.executeQuery("select col_name from col_name where col_status = 0"); while (rs.next()) { songData_Table.removeColumn(songData_Table.getColumn(rs.getString(1))); } songData_Table.getTableHeader().removeMouseListener(ma); songData_Table.getTableHeader().addMouseListener(ma); songData_Table.setDragEnabled(true); songData_Table.setDropTarget(new DropTarget() { @Override public synchronized void drop(DropTargetDropEvent dtde) { dtde.acceptDrop(DnDConstants.ACTION_COPY_OR_MOVE); Transferable t = dtde.getTransferable(); try { if (dtde.isDataFlavorSupported(DataFlavor.javaFileListFlavor)) { Object fileList = t.getTransferData(DataFlavor.javaFileListFlavor); String files = fileList.toString(); finalString = convertFileString(files); if (dropControl == 0 && lastOpen.equals("library")) { songAddDB(finalString); } else if (dropControl == 0 && !lastOpen.equals("library")) { songAddPlaylistFromLibrary(lastOpen, finalString); getSongTable(lastOpen); } else { songAddPlaylistFromLibrary(tableName, finalString); } } else if (dtde.isDataFlavorSupported(DataFlavor.stringFlavor)) { Object fileList = t.getTransferData(DataFlavor.stringFlavor); String fileListString = fileList.toString(); fileListString = Arrays.toString(fileListString.split("\\n")); String[] splitLocations = fileListString.split(",\\s"); for (int i = 0; i < splitLocations.length; i++) { if (i == 0) { splitLocations[i] = splitLocations[i].substring(1, splitLocations[i].indexOf(".mp3") + 4); } else { splitLocations[i] = splitLocations[i].substring(0, splitLocations[i].indexOf(".mp3") + 4); } } for (int i = 0; i < splitLocations.length; i++) { splitLocations[i] = sd.getLocations(splitLocations[i]); } finalString = Arrays.asList(splitLocations); if (dropControl == 0 && lastOpen.equals("library")) { songAddDB(finalString); } else if (dropControl == 0 && !lastOpen.equals("library")) { songAddPlaylistFromLibrary(lastOpen, finalString); getSongTable(lastOpen); } else { songAddPlaylistFromLibrary(tableName, finalString); } } } catch (UnsupportedFlavorException | IOException | InvalidDataException | UnsupportedTagException ex) { System.out.println("Error in second drop flavour............" + ex); } } }); if (con != null) { stmt.close(); con.close(); } } catch (SQLException e) { System.out.println("Error in Stmt " + e); } }
From source file:org.jfree.data.jdbc.JDBCXYDataset.java
/** * ExecuteQuery will attempt execute the query passed to it against the * provided database connection. If connection is null then no action is * taken.//from w w w . ja v a 2 s . co m * * The results from the query are extracted and cached locally, thus * applying an upper limit on how many rows can be retrieved successfully. * * @param query the query to be executed. * @param con the connection the query is to be executed against. * * @throws SQLException if there is a problem executing the query. */ public void executeQuery(Connection con, String query) throws SQLException { if (con == null) { throw new SQLException("There is no database to execute the query."); } ResultSet resultSet = null; Statement statement = null; try { statement = con.createStatement(); resultSet = statement.executeQuery(query); ResultSetMetaData metaData = resultSet.getMetaData(); int numberOfColumns = metaData.getColumnCount(); int numberOfValidColumns = 0; int[] columnTypes = new int[numberOfColumns]; for (int column = 0; column < numberOfColumns; column++) { try { int type = metaData.getColumnType(column + 1); switch (type) { case Types.NUMERIC: case Types.REAL: case Types.INTEGER: case Types.DOUBLE: case Types.FLOAT: case Types.DECIMAL: case Types.BIT: case Types.DATE: case Types.TIME: case Types.TIMESTAMP: case Types.BIGINT: case Types.SMALLINT: ++numberOfValidColumns; columnTypes[column] = type; break; default: columnTypes[column] = Types.NULL; break; } } catch (SQLException e) { columnTypes[column] = Types.NULL; throw e; } } if (numberOfValidColumns <= 1) { throw new SQLException("Not enough valid columns where generated by query."); } /// First column is X data this.columnNames = new String[numberOfValidColumns - 1]; /// Get the column names and cache them. int currentColumn = 0; for (int column = 1; column < numberOfColumns; column++) { if (columnTypes[column] != Types.NULL) { this.columnNames[currentColumn] = metaData.getColumnLabel(column + 1); ++currentColumn; } } // Might need to add, to free memory from any previous result sets if (this.rows != null) { for (int column = 0; column < this.rows.size(); column++) { ArrayList row = (ArrayList) this.rows.get(column); row.clear(); } this.rows.clear(); } // Are we working with a time series. switch (columnTypes[0]) { case Types.DATE: case Types.TIME: case Types.TIMESTAMP: this.isTimeSeries = true; break; default: this.isTimeSeries = false; break; } // Get all rows. // rows = new ArrayList(); while (resultSet.next()) { ArrayList newRow = new ArrayList(); for (int column = 0; column < numberOfColumns; column++) { Object xObject = resultSet.getObject(column + 1); switch (columnTypes[column]) { case Types.NUMERIC: case Types.REAL: case Types.INTEGER: case Types.DOUBLE: case Types.FLOAT: case Types.DECIMAL: case Types.BIGINT: case Types.SMALLINT: newRow.add(xObject); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: newRow.add(new Long(((Date) xObject).getTime())); break; case Types.NULL: break; default: System.err.println("Unknown data"); columnTypes[column] = Types.NULL; break; } } this.rows.add(newRow); } /// a kludge to make everything work when no rows returned if (this.rows.size() == 0) { ArrayList newRow = new ArrayList(); for (int column = 0; column < numberOfColumns; column++) { if (columnTypes[column] != Types.NULL) { newRow.add(new Integer(0)); } } this.rows.add(newRow); } /// Determine max and min values. if (this.rows.size() < 1) { this.maxValue = 0.0; this.minValue = 0.0; } else { ArrayList row = (ArrayList) this.rows.get(0); this.maxValue = Double.NEGATIVE_INFINITY; this.minValue = Double.POSITIVE_INFINITY; for (int rowNum = 0; rowNum < this.rows.size(); ++rowNum) { row = (ArrayList) this.rows.get(rowNum); for (int column = 1; column < numberOfColumns; column++) { Object testValue = row.get(column); if (testValue != null) { double test = ((Number) testValue).doubleValue(); if (test < this.minValue) { this.minValue = test; } if (test > this.maxValue) { this.maxValue = test; } } } } } fireDatasetChanged(new DatasetChangeInfo()); //TODO: fill in real change info } finally { if (resultSet != null) { try { resultSet.close(); } catch (Exception e) { // TODO: is this a good idea? } } if (statement != null) { try { statement.close(); } catch (Exception e) { // TODO: is this a good idea? } } } }
From source file:io.druid.indexing.jdbc.JDBCIndexTask.java
@Override public TaskStatus run(final TaskToolbox toolbox) throws Exception { log.info("Starting up!"); startTime = DateTime.now();//from ww w .j av a2 s .c o m mapper = toolbox.getObjectMapper(); status = Status.STARTING; if (chatHandlerProvider.isPresent()) { log.info("Found chat handler of class[%s]", chatHandlerProvider.get().getClass().getName()); chatHandlerProvider.get().register(getId(), this, false); } else { log.warn("No chat handler detected"); } runThread = Thread.currentThread(); // Set up FireDepartmentMetrics final FireDepartment fireDepartmentForMetrics = new FireDepartment(dataSchema, new RealtimeIOConfig(null, null, null), null); fireDepartmentMetrics = fireDepartmentForMetrics.getMetrics(); toolbox.getMonitorScheduler() .addMonitor(new RealtimeMetricsMonitor(ImmutableList.of(fireDepartmentForMetrics), ImmutableMap.of(DruidMetrics.TASK_ID, new String[] { getId() }))); BasicDataSource dataSource = new BasicDataSource(); dataSource.setUsername(ioConfig.getUser()); dataSource.setPassword(ioConfig.getPassword()); dataSource.setUrl(ioConfig.getConnectURI()); dataSource.setDriverClassLoader(getClass().getClassLoader()); final String table = ioConfig.getTableName(); if (!StringUtils.isEmpty(ioConfig.getDriverClass())) { dataSource.setDriverClassName(ioConfig.getDriverClass()); } final Handle handle = new DBI(dataSource).open(); try (final Appenderator appenderator0 = newAppenderator(fireDepartmentMetrics, toolbox); final AppenderatorDriver driver = newDriver(appenderator0, toolbox, fireDepartmentMetrics)) { toolbox.getDataSegmentServerAnnouncer().announce(); appenderator = appenderator0; // Start up, set up initial offsets. final Object restoredMetadata = driver.startJob(); if (restoredMetadata == null) { nextOffsets.putAll(ioConfig.getJdbcOffsets().getOffsetMaps()); } else { final Map<String, Object> restoredMetadataMap = (Map) restoredMetadata; final JDBCOffsets restoredNextPartitions = toolbox.getObjectMapper() .convertValue(restoredMetadataMap.get(METADATA_NEXT_OFFSETS), JDBCOffsets.class); nextOffsets.putAll(restoredNextPartitions.getOffsetMaps()); // Sanity checks. if (!restoredNextPartitions.getTable().equals(ioConfig.getTableName())) { throw new ISE("WTF?! Restored table[%s] but expected table[%s]", restoredNextPartitions.getTable(), ioConfig.getTableName()); } if (!nextOffsets.equals(ioConfig.getJdbcOffsets().getOffsetMaps())) { throw new ISE("WTF?! Restored partitions[%s] but expected partitions[%s]", nextOffsets, ioConfig.getJdbcOffsets().getOffsetMaps()); } } // Set up sequenceNames. final Map<Integer, String> sequenceNames = Maps.newHashMap(); for (Integer partitionNum : nextOffsets.keySet()) { sequenceNames.put(partitionNum, String.format("%s_%s", ioConfig.getBaseSequenceName(), partitionNum)); } // Set up committer. final Supplier<Committer> committerSupplier = new Supplier<Committer>() { @Override public Committer get() { final Map<Integer, Long> snapshot = ImmutableMap.copyOf(nextOffsets); return new Committer() { @Override public Object getMetadata() { return ImmutableMap.of(METADATA_NEXT_OFFSETS, new JDBCOffsets(ioConfig.getJdbcOffsets().getTable(), snapshot)); } @Override public void run() { // Do nothing. } }; } }; // Set<Integer> assignment = assignPartitionsAndSeekToNext(handle); // boolean stillReading = !assignment.isEmpty(); status = Status.READING; try { // while (stillReading) { // if (possiblyPause(assignment)) { // The partition assignments may have changed while paused by a call to setEndOffsets() so reassign // partitions upon resuming. This is safe even if the end offsets have not been modified. // assignment = assignPartitionsAndSeekToNext(handle); // if (assignment.isEmpty()) { // log.info("All partitions have been fully read"); // publishOnStop = true; // stopRequested = true; // } // } // if (stopRequested) { // break; // } final String query = (ioConfig.getQuery() != null) ? ioConfig.getQuery() : makeQuery(ioConfig.getColumns(), ioConfig.getJdbcOffsets()); org.skife.jdbi.v2.Query<Map<String, Object>> dbiQuery = handle.createQuery(query); final ResultIterator<InputRow> rowIterator = dbiQuery.map(new ResultSetMapper<InputRow>() { List<String> queryColumns = (ioConfig.getColumns() == null) ? Lists.<String>newArrayList() : ioConfig.getColumns(); List<Boolean> columnIsNumeric = Lists.newArrayList(); @Override public InputRow map(final int index, final ResultSet r, final StatementContext ctx) throws SQLException { try { if (queryColumns.size() == 0) { ResultSetMetaData metadata = r.getMetaData(); for (int idx = 1; idx <= metadata.getColumnCount(); idx++) { queryColumns.add(metadata.getColumnName(idx)); } Preconditions.checkArgument(queryColumns.size() > 0, String.format("No column in table [%s]", table)); verifyParserSpec(parser.getParseSpec(), queryColumns); } if (columnIsNumeric.size() == 0) { ResultSetMetaData metadata = r.getMetaData(); Preconditions.checkArgument(metadata.getColumnCount() >= queryColumns.size(), String.format( "number of column names [%d] exceeds the actual number of returning column values [%d]", queryColumns.size(), metadata.getColumnCount())); columnIsNumeric.add(false); // dummy to make start index to 1 for (int idx = 1; idx <= metadata.getColumnCount(); idx++) { boolean isNumeric = false; int type = metadata.getColumnType(idx); switch (type) { case BIGINT: case DECIMAL: case DOUBLE: case FLOAT: case INTEGER: case NUMERIC: case SMALLINT: case TINYINT: isNumeric = true; break; } columnIsNumeric.add(isNumeric); } } final Map<String, Object> columnMap = Maps.newHashMap(); int columnIdx = 1; for (String column : queryColumns) { Object objToPut = null; if (table != null) { objToPut = r.getObject(column); } else { objToPut = r.getObject(columnIdx); } columnMap.put(column, objToPut == null ? columnIsNumeric.get(columnIdx) : objToPut); columnIdx++; } return parser.parse(columnMap); } catch (IllegalArgumentException e) { throw new SQLException(e); } } }).iterator(); org.skife.jdbi.v2.Query<Map<String, Object>> maxItemQuery = handle .createQuery(makeMaxQuery(ioConfig.getJdbcOffsets())); long currOffset = maxItemQuery != null ? (long) maxItemQuery.list(1).get(0).get("MAX") : 0; while (rowIterator.hasNext()) { InputRow row = rowIterator.next(); try { if (!ioConfig.getMinimumMessageTime().isPresent() || !ioConfig.getMinimumMessageTime().get().isAfter(row.getTimestamp())) { final String sequenceName = sequenceNames.get(nextOffsets.keySet().toArray()[0]); //TODO::: check data final AppenderatorDriverAddResult addResult = driver.add(row, sequenceName, committerSupplier); if (addResult.isOk()) { // If the number of rows in the segment exceeds the threshold after adding a row, // move the segment out from the active segments of AppenderatorDriver to make a new segment. if (addResult.getNumRowsInSegment() > tuningConfig.getMaxRowsPerSegment()) { driver.moveSegmentOut(sequenceName, ImmutableList.of(addResult.getSegmentIdentifier())); } } else { // Failure to allocate segment puts determinism at risk, bail out to be safe. // May want configurable behavior here at some point. // If we allow continuing, then consider blacklisting the interval for a while to avoid constant checks. throw new ISE("Could not allocate segment for row with timestamp[%s]", row.getTimestamp()); } fireDepartmentMetrics.incrementProcessed(); } else { fireDepartmentMetrics.incrementThrownAway(); } } catch (ParseException e) { if (tuningConfig.isReportParseExceptions()) { throw e; } else { log.debug(e, "Dropping unparseable row from row[%d] .", row); fireDepartmentMetrics.incrementUnparseable(); } } } nextOffsets.put((int) ioConfig.getJdbcOffsets().getOffsetMaps().keySet().toArray()[0], currOffset); // if (nextOffsets.get(record.partition()).equals(endOffsets.get(record.partition())) // && assignment.remove(record.partition())) { // log.info("Finished reading table[%s], partition[%,d].", record.topic(), record.partition()); // stillReading = ioConfig.isPauseAfterRead() || !assignment.isEmpty(); // } // } } finally { driver.persist(committerSupplier.get()); // persist pending data } synchronized (statusLock) { if (stopRequested && !publishOnStop) { throw new InterruptedException("Stopping without publishing"); } status = Status.PUBLISHING; } final TransactionalSegmentPublisher publisher = (segments, commitMetadata) -> { final JDBCOffsets finalOffsets = toolbox.getObjectMapper() .convertValue(((Map) commitMetadata).get(METADATA_NEXT_OFFSETS), JDBCOffsets.class); // Sanity check, we should only be publishing things that match our desired end state. //TODO::: Santiny Check! // if (!endOffsets.equals(finalOffsets.getOffsetMaps())) { // throw new ISE("WTF?! Driver attempted to publish invalid metadata[%s].", commitMetadata); // } final SegmentTransactionalInsertAction action; if (ioConfig.isUseTransaction()) { action = new SegmentTransactionalInsertAction(segments, new JDBCDataSourceMetadata(ioConfig.getJdbcOffsets()), new JDBCDataSourceMetadata(finalOffsets) //TODO::: Check Values ); } else { action = new SegmentTransactionalInsertAction(segments, null, null); } log.info("Publishing with isTransaction[%s].", ioConfig.isUseTransaction()); return toolbox.getTaskActionClient().submit(action).isSuccess(); }; // Supervised kafka tasks are killed by JDBCSupervisor if they are stuck during publishing segments or waiting // for hand off. See JDBCSupervisorIOConfig.completionTimeout. final SegmentsAndMetadata published = driver .publish(publisher, committerSupplier.get(), sequenceNames.values()).get(); final SegmentsAndMetadata handedOff; if (tuningConfig.getHandoffConditionTimeout() == 0) { handedOff = driver.registerHandoff(published).get(); } else { handedOff = driver.registerHandoff(published).get(tuningConfig.getHandoffConditionTimeout(), TimeUnit.MILLISECONDS); } if (handedOff == null) { throw new ISE("Transaction failure publishing segments, aborting"); } else { log.info("Published segments[%s] with metadata[%s].", Joiner.on(", ") .join(Iterables.transform(handedOff.getSegments(), new Function<DataSegment, String>() { @Override public String apply(DataSegment input) { return input.getIdentifier(); } })), handedOff.getCommitMetadata()); } } catch (InterruptedException | RejectedExecutionException e) { // handle the InterruptedException that gets wrapped in a RejectedExecutionException if (e instanceof RejectedExecutionException && (e.getCause() == null || !(e.getCause() instanceof InterruptedException))) { throw e; } // if we were interrupted because we were asked to stop, handle the exception and return success, else rethrow if (!stopRequested) { Thread.currentThread().interrupt(); throw e; } log.info("The task was asked to stop before completing"); } finally { if (chatHandlerProvider.isPresent()) { chatHandlerProvider.get().unregister(getId()); } handle.close(); } toolbox.getDataSegmentServerAnnouncer().unannounce(); //TODO::implement return success(); }
From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
public List<String> toStringList(ResultSet rs) throws Exception { List<String> list = new ArrayList<String>(); try {//from w w w. j av a2 s .c om // we will need the column names, this will save the table meta-data like column nmae. java.sql.ResultSetMetaData rsmd = rs.getMetaData(); //loop through the ResultSet while (rs.next()) { //figure out how many columns there are int numColumns = rsmd.getColumnCount(); //each row in the ResultSet will be converted to a JSON Object StringBuilder builder = new StringBuilder(); // loop through all the columns and place them into the JSON Object for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { builder.append(rs.getArray(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { builder.append(rs.getBoolean(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { builder.append(rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { builder.append(rs.getDouble(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { builder.append(rs.getFloat(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { builder.append(rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { // temp = rs.getString(column_name); //saving column data to temp variable // temp = ESAPI.encoder().canonicalize(temp); //decoding data to base state // temp = ESAPI.encoder().encodeForHTML(temp); //encoding to be browser safe // obj.put(column_name, temp); //putting data into JSON object // builder.append(rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { builder.append(rs.getDate(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIME) { builder.append(rs.getTime(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { builder.append(rs.getTimestamp(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC) { builder.append(rs.getBigDecimal(column_name)); } else { builder.append(rs.getObject(column_name)); } } //end foreach list.add(builder.toString()); } //end while } catch (Exception e) { e.printStackTrace(); } return list; //return String list }
From source file:GestoSAT.GestoSAT.java
public int iniciarSesion(String correo, String pass) { File f = new File("confGestoSAT"); if (f.exists()) { try {/*from w w w .j a va 2 s .c o m*/ Class.forName("com.mysql.jdbc.Driver"); Statement st = con.createStatement(); ResultSet res = st.executeQuery( "Select * From usuarios u INNER JOIN entidades e ON u.id_Entidad = e.id_Entidad Where u.Correo_electronico ='" + correo + "' AND u.Activo = 1"); if (res.next()) { String p = (String) res.getObject("u.Password"); MessageDigest sha256 = MessageDigest.getInstance("SHA-256"); sha256.update(pass.getBytes("UTF-8")); byte[] digest = sha256.digest(); StringBuffer sb = new StringBuffer(); for (int i = 0; i < digest.length; i++) { sb.append(String.format("%02x", digest[i])); } String password = sb.toString(); if (p.equals(password)) { if ((res.getString("u.Gerente")).equals("1")) empleado = new Gerente(res.getString("u.Nombre"), res.getString("u.Apellidos"), res.getString("u.DNI"), res.getString("u.Poblacion"), res.getString("u.Provincia"), res.getInt("u.CP"), res.getString("u.Calle"), res.getString("u.Numero"), res.getString("u.Escalera"), res.getInt("u.Piso"), res.getString("u.Puerta"), res.getInt("u.tlf_Fijo"), res.getInt("u.tlf_Movil"), res.getFloat("u.Sueldo_base"), res.getFloat("u.Precio_hora"), correo, res.getString("e.nombre"), res.getString("e.nif"), res.getString("e.provincia"), res.getString("e.poblacion"), res.getInt("e.CP"), res.getString("e.Calle"), res.getString("e.numero"), res.getString("e.correo_electronico"), res.getInt("e.tlf_fijo"), res.getInt("e.tlf_movil"), res.getInt("e.fax")); else empleado = new Empleado(res.getString("u.Nombre"), res.getString("u.Apellidos"), res.getString("u.DNI"), res.getString("u.Poblacion"), res.getString("u.Provincia"), res.getInt("u.CP"), res.getString("u.Calle"), res.getString("u.Numero"), res.getString("u.Escalera"), res.getInt("u.Piso"), res.getString("u.Puerta"), res.getInt("u.tlf_Fijo"), res.getInt("u.tlf_Movil"), res.getFloat("u.Sueldo_base"), res.getFloat("u.Precio_hora"), correo, res.getString("e.nombre"), res.getString("e.nif"), res.getString("e.provincia"), res.getString("e.poblacion"), res.getInt("e.CP"), res.getString("e.Calle"), res.getString("e.numero"), res.getString("e.correo_electronico"), res.getInt("e.tlf_fijo"), res.getInt("e.tlf_movil"), res.getInt("e.fax")); empleado.setGestoSAT(this); int devolver = res.getInt("u.id_Usuario"); res.close(); this.empleados.put(devolver, empleado); this.getEmpleados(devolver); return devolver; } else { res.close(); return 0; } } else return 0; } catch (SQLException ex) { Logger.getLogger(GestoSAT.class.getName()).log(Level.SEVERE, null, ex); return 0; } catch (ClassNotFoundException ex) { Logger.getLogger(GestoSAT.class.getName()).log(Level.SEVERE, null, ex); return 0; } catch (NoSuchAlgorithmException ex) { Logger.getLogger(GestoSAT.class.getName()).log(Level.SEVERE, null, ex); return 0; } catch (UnsupportedEncodingException ex) { Logger.getLogger(GestoSAT.class.getName()).log(Level.SEVERE, null, ex); return 0; } } else { String defaultUser = "admin@default.es"; String defaultPass = "admin"; if (defaultUser.equals(correo) && defaultPass.equals(pass)) return 1; else return 0; } }
From source file:com.krawler.esp.servlets.AdminServlet.java
public static String getAdminUserData(Connection conn, HttpServletRequest request, String companyid, String searchString, boolean fromHere) throws ServiceException { int count = 0; String data = ""; PreparedStatement pstmt = null; JSONObject res = new JSONObject(); String[] searchStrObj = new String[] { "concat(users.fname, ' ',users.lname)", "userlogin.username" }; String myLikeString = StringUtil.getMySearchString(searchString, "and", searchStrObj); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); java.util.Date d = new java.util.Date(); String COUNT_USERS = " SELECT COUNT(*) as count " + " FROM users INNER JOIN userlogin ON users.userid=userlogin.userid " + " WHERE companyid=? and userlogin.isactive = 1"; String GET_USERS = " SELECT image, users.userid, userlogin.username, fname, lname, emailid, aboutuser, address, contactno, CONCAT(fname, ' ', lname) as fullname, " + " case " + " when date(lastactivitydate) <= date('1990-01-01 00:00:00') then '' " + " else lastactivitydate end as lastactivitydate " + " FROM users INNER JOIN userlogin ON users.userid=userlogin.userid " + " WHERE companyid=? and userlogin.isactive = 1" + myLikeString + " LIMIT ? OFFSET ? "; String GET_MAX_USERS = " SELECT maxusers,costperuser " + " FROM company INNER JOIN planedition ON planedition.planid=company.planid " + " WHERE company.companyid=? "; ResultSet rs = null; try {//from www . j av a 2s.co m String loginid = ""; try { loginid = AuthHandler.getUserid(request); } catch (SessionExpiredException e) { loginid = ""; } pstmt = conn.prepareStatement(COUNT_USERS + myLikeString); pstmt.setString(1, companyid); StringUtil.insertParamSearchString(2, pstmt, searchString, searchStrObj.length); rs = pstmt.executeQuery(); if (rs.next()) { count = rs.getInt("count"); } if (count > 0) { rs = null; pstmt = null; pstmt = conn.prepareStatement(GET_USERS); pstmt.setString(1, companyid); int cnt = StringUtil.insertParamSearchString(2, pstmt, searchString, searchStrObj.length); pstmt.setInt(cnt++, Integer.parseInt(request.getParameter("limit"))); pstmt.setInt(cnt++, Integer.parseInt(request.getParameter("start"))); rs = pstmt.executeQuery(); while (rs.next()) { JSONObject temp = new JSONObject(); String img = StringUtil.getAppsImagePath(rs.getString("userid"), 35); temp.put("image", img); temp.put("userid", rs.getString("userid")); temp.put("username", rs.getString("username")); temp.put("fname", rs.getString("fname")); temp.put("lname", rs.getString("lname")); temp.put("fullname", rs.getString("fullname")); temp.put("emailid", rs.getString("emailid")); String lastLogin = rs.getObject("lastactivitydate").toString(); if (fromHere) { // lastLogin = Timezone.dateTimeRenderer(conn, Timezone.toUserTimezone(conn, sdf.format(d), loginid), lastLogin, loginid); lastLogin = Timezone.toCompanyTimezone(conn, lastLogin, companyid); } temp.put("lastlogin", lastLogin);// rs.getObject("lastactivitydate")); temp.put("aboutuser", rs.getString("aboutuser")); temp.put("address", rs.getString("address")); temp.put("contactno", rs.getString("contactno")); String userid = rs.getString("userid"); PermissionManager pm = new PermissionManager(); List<String> allPermissions = pm.getUserActivePermissionList(conn, userid); Iterator itr = allPermissions.iterator(); String permissions = ""; if (allPermissions.isEmpty()) { String un = rs.getString("fullname"); permissions = "[<i>" + MessageSourceProxy.getMessage("pm.permission.msg.notassigned", new Object[] { un }, request) + "</i>]"; } while (itr.hasNext()) { String tempStr = (String) itr.next(); permissions += MessageSourceProxy.getMessage("pm.permission." + tempStr, null, request) + "<br>"; } temp.put("permissions", permissions); res.append("data", temp); } pstmt.close(); } else { res.put("data", ""); } pstmt = conn.prepareStatement(GET_MAX_USERS); pstmt.setString(1, companyid); rs = pstmt.executeQuery(); if (rs.next()) { res.put("count", count); res.put("maxusers", rs.getInt("maxusers")); res.put("costperuser", rs.getInt("costperuser")); } data = res.toString(); } catch (SQLException e) { throw ServiceException.FAILURE("Admin.getAdminUserData", e); } catch (JSONException ex) { throw ServiceException.FAILURE("Admin.getAdminUserData", ex); } catch (ServiceException ex) { throw ServiceException.FAILURE("Admin.getAdminUserData", ex); } finally { DbPool.closeStatement(pstmt); } return data; }