List of usage examples for java.sql ResultSet first
boolean first() throws SQLException;
ResultSet object. From source file:it.anyplace.sync.repository.repo.SqlRepository.java
private void checkDb() { logger.debug("check db"); try (Connection connection = getConnection()) { try {/*from w w w .java2s . c o m*/ try (PreparedStatement statement = connection .prepareStatement("SELECT version_number FROM version")) { ResultSet resultSet = statement.executeQuery(); checkArgument(resultSet.first()); int version = resultSet.getInt(1); checkArgument(version == VERSION, "database version mismatch, expected %s, found %s", VERSION, version); logger.info("database check ok, version = {}", version); } } catch (Exception ex) { logger.warn("invalid database, resetting db", ex); initDb(); } } catch (Exception ex) { close(); throw new RuntimeException(ex); } }
From source file:com.oracle.tutorial.jdbc.CoffeesTable.java
public void modifyPricesByPercentage(String coffeeName, float priceModifier, float maximumPrice) throws SQLException { con.setAutoCommit(false);/*from www . j a v a2s. c o m*/ Statement getPrice = null; Statement updatePrice = null; ResultSet rs = null; String query = "SELECT COF_NAME, PRICE FROM COFFEES " + "WHERE COF_NAME = '" + coffeeName + "'"; try { Savepoint save1 = con.setSavepoint(); getPrice = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); updatePrice = con.createStatement(); if (!getPrice.execute(query)) { System.out.println("Could not find entry for coffee named " + coffeeName); } else { rs = getPrice.getResultSet(); rs.first(); float oldPrice = rs.getFloat("PRICE"); float newPrice = oldPrice + (oldPrice * priceModifier); System.out.println("Old price of " + coffeeName + " is " + oldPrice); System.out.println("New price of " + coffeeName + " is " + newPrice); System.out.println("Performing update..."); updatePrice.executeUpdate( "UPDATE COFFEES SET PRICE = " + newPrice + " WHERE COF_NAME = '" + coffeeName + "'"); System.out.println("\nCOFFEES table after update:"); CoffeesTable.viewTable(con); if (newPrice > maximumPrice) { System.out.println("\nThe new price, " + newPrice + ", is greater than the maximum " + "price, " + maximumPrice + ". Rolling back the transaction..."); con.rollback(save1); System.out.println("\nCOFFEES table after rollback:"); CoffeesTable.viewTable(con); } con.commit(); } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (getPrice != null) { getPrice.close(); } if (updatePrice != null) { updatePrice.close(); } con.setAutoCommit(true); } }
From source file:org.esupportail.dining.web.controllers.ViewController.java
@RequestMapping("/restaurants") public ModelAndView renderMain() throws Exception { ModelMap model = new ModelMap(); User user = this.authenticator.getUser(); /* Get Area To Display */ String[] areaToDisplay = null; try {/*from w w w. j a va 2 s . co m*/ // Check if user has a special options that overrides default config ResultSet results = this.dc.executeQuery("SELECT AREANAME FROM USERAREA WHERE USERNAME='" + StringEscapeUtils.escapeSql(user.getLogin()) + "';"); results.next(); areaToDisplay = results.getString("AREANAME").split(","); } catch (Exception e) { // If not we just fetch data from the default config ResultSet results = this.dc.executeQuery("SELECT AREANAME FROM PATHFLUX WHERE IS_DEFAULT=TRUE;"); results.next(); areaToDisplay = results.getString("AREANAME").split(","); } model.addAttribute("area", areaToDisplay); // Get favorite for the current user try { ResultSet favList = this.dc.executeQuery("SELECT RESTAURANTID FROM FAVORITERESTAURANT WHERE USERNAME='" + StringEscapeUtils.escapeSql(user.getLogin()) + "';"); List<Restaurant> favorites = new ArrayList<Restaurant>(); if (favList.next()) { for (Restaurant r : this.feed.getFeed().getRestaurants()) { do { if (r.getId() == favList.getInt("RESTAURANTID")) { favorites.add(r); } } while (favList.next()); favList.first(); } model.put("favorites", favorites); } } catch (Exception e) { // Nothing to do here. // If SQL fetch throw an exception nevermind, it means the user does not have any fav atm. } // Fetch data from the feed based on the fav and area settings try { this.dinings = this.feed.getFeed(); Map<String, List<Restaurant>> areasToDisplayList = new HashMap<String, List<Restaurant>>(); for (int i = 0; i < areaToDisplay.length; i++) { areasToDisplayList.put(areaToDisplay[i], new ArrayList<Restaurant>()); } for (Restaurant restaurant : this.dinings.getRestaurants()) { if (areasToDisplayList.containsKey(restaurant.getArea())) { // update "isClosed" with current date restaurant.setAdditionalProperties("isClosed", this.feed.isClosed(restaurant)); areasToDisplayList.get(restaurant.getArea()).add(restaurant); } } model.put("restaurantLists", areasToDisplayList); } catch (NullPointerException e) { /* */ } catch (Exception e) { return new ModelAndView("error", new ModelMap("err", e.getMessage())); } return new ModelAndView("view", model); }
From source file:ua.aits.Carpath.model.ArchiveArticleModel.java
public String insertArticle(String titleEN, String titleUA, String titleHU, String titleSK, String titleRO, String textEN, String textUA, String textHU, String textSK, String textRO, String category, String author, String date, String dir, String country, String region, String district, String town, String x, String y) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException { if ("".equals(x)) { x = "0";// ww w . j a v a 2 s . co m } if ("".equals(y)) { y = "0"; } DB.runQuery( "INSERT INTO `archive_articles`(`article_title_EN`, `article_title_UA`, `article_title_HU`, `article_title_SK`, `article_title_RO`, `article_text_EN`, `article_text_UA`, `article_text_HU`, `article_text_SK`, `article_text_RO`, " + "`article_category`, `article_author`, `article_editor`, `article_add_date`, `article_edit_date`, `article_is_edit`, `article_is_delete`, `article_is_publish`, `article_dir`, `article_country`, `article_region`, `article_district`, `article_town`, `article_x`, `article_y`) VALUES (" + "'" + StringEscapeUtils.escapeSql(titleEN) + "','" + StringEscapeUtils.escapeSql(titleUA) + "','" + StringEscapeUtils.escapeSql(titleHU) + "','" + StringEscapeUtils.escapeSql(titleSK) + "','" + StringEscapeUtils.escapeSql(titleRO) + "','" + StringEscapeUtils.escapeSql(textEN) + "','" + StringEscapeUtils.escapeSql(textUA) + "','" + StringEscapeUtils.escapeSql(textHU) + "','" + StringEscapeUtils.escapeSql(textSK) + "','" + StringEscapeUtils.escapeSql(textRO) + "','" + category + "','" + author + "','" + author + "','" + date + "','" + date + "',1,0,0,'" + dir + "','" + StringEscapeUtils.escapeSql(country) + "','" + StringEscapeUtils.escapeSql(region) + "','" + StringEscapeUtils.escapeSql(district) + "','" + StringEscapeUtils.escapeSql(town) + "', " + x + ", " + y + ");"); String id = ""; ResultSet result = DB .getResultSet("SELECT article_id FROM archive_articles WHERE archive_articles.article_title_EN = '" + StringEscapeUtils.escapeSql(titleEN) + "' AND archive_articles.article_text_EN = '" + StringEscapeUtils.escapeSql(textEN) + "' AND archive_articles.article_add_date = '" + date + "';"); result.first(); id = result.getString("article_id"); DB.closeCon(); return id; }
From source file:BQJDBC.QueryResultTest.QueryResultTest.java
@Test public void QueryResultTest05() { final String sql = "SELECT word FROM publicdata:samples.shakespeare WHERE word='huzzah' ;"; final String description = "The word \"huzzah\" NOTE: It doesn't appear in any any book, so it returns with a null #WHERE"; this.logger.info("Test number: 05"); this.logger.info("Running query:" + sql); java.sql.ResultSet Result = null; try {// w w w .j a v a 2s. c o m Result = QueryResultTest.con.createStatement().executeQuery(sql); this.logger.debug(Result.getMetaData().getColumnCount()); } catch (SQLException e) { this.logger.error("SQLexception" + e.toString()); Assert.fail("SQLException" + e.toString()); } Assert.assertNotNull(Result); this.logger.debug(description); try { if (Result.getType() != ResultSet.TYPE_FORWARD_ONLY) Assert.assertFalse(Result.first()); } catch (SQLException e) { this.logger.error("SQLexception" + e.toString()); Assert.fail(e.toString()); } }
From source file:org.wso2.carbon.dashboard.portal.core.datasource.DataBaseHandler.java
/** * To get the gadget usage info/* w w w .j a v a2s. co m*/ * * @param tenantID ID of the tenant which the dashboard created in * @param dashboardID ID of the dashboard * @param gadgetId ID of the gadget */ public String getGadgetUsageInfo(int tenantID, String dashboardID, String gadgetId) throws DashboardPortalException { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; String gadgetUsageInfo = null; try { connection = dataBaseInitializer.getDBConnection(); preparedStatement = connection.prepareStatement(DataSourceConstants.SQL_SELECT_USAGE_OPERATION); preparedStatement.setInt(1, tenantID); preparedStatement.setString(2, dashboardID); preparedStatement.setString(3, gadgetId); resultSet = preparedStatement.executeQuery(); if (!connection.getAutoCommit()) { connection.commit(); } if (resultSet.first()) { gadgetUsageInfo = resultSet.getString(1); } return gadgetUsageInfo; } catch (SQLException e) { log.error("Cannot insert the gadget usage info ", e); } finally { closeDatabaseResources(connection, preparedStatement, resultSet); } return null; }
From source file:org.geowebcache.storage.jdbc.metastore.JDBCMBWrapper.java
protected boolean getTile(TileObject stObj) throws SQLException { String query;/*from www .jav a 2 s. c o m*/ if (stObj.getParametersId() == -1L) { query = "SELECT TILE_ID,BLOB_SIZE,CREATED,LOCK,NOW() FROM TILES WHERE " + " LAYER_ID = ? AND X = ? AND Y = ? AND Z = ? AND GRIDSET_ID = ? " + " AND FORMAT_ID = ? AND PARAMETERS_ID IS NULL LIMIT 1 "; } else { query = "SELECT TILE_ID,BLOB_SIZE,CREATED,LOCK,NOW() FROM TILES WHERE " + " LAYER_ID = ? AND X = ? AND Y = ? AND Z = ? AND GRIDSET_ID = ? " + " AND FORMAT_ID = ? AND PARAMETERS_ID = ? LIMIT 1 "; } long[] xyz = stObj.getXYZ(); final Connection conn = getConnection(); PreparedStatement prep = null; try { prep = conn.prepareStatement(query); prep.setLong(1, stObj.getLayerId()); prep.setLong(2, xyz[0]); prep.setLong(3, xyz[1]); prep.setLong(4, xyz[2]); prep.setLong(5, stObj.getGridSetIdId()); prep.setLong(6, stObj.getFormatId()); if (stObj.getParametersId() != -1L) { prep.setLong(7, stObj.getParametersId()); } ResultSet rs = prep.executeQuery(); try { if (rs.first()) { Timestamp lock = rs.getTimestamp(4); // This tile is locked if (lock != null) { Timestamp now = rs.getTimestamp(5); long diff = now.getTime() - lock.getTime(); // System.out.println(now.getTime() + " " + System.currentTimeMillis()); if (diff > lockTimeout) { log.warn("Database lock exceeded (" + diff + "ms , " + lock.toString() + ") for " + stObj.toString() + ", clearing tile."); deleteTile(conn, stObj); stObj.setStatus(StorageObject.Status.EXPIRED_LOCK); } else { stObj.setStatus(StorageObject.Status.LOCK); } // This puts the request back in the queue return false; } stObj.setId(rs.getLong(1)); stObj.setBlobSize(rs.getInt(2)); stObj.setCreated(rs.getLong(3)); stObj.setStatus(StorageObject.Status.HIT); return true; } else { stObj.setStatus(StorageObject.Status.MISS); return false; } } finally { close(rs); } } finally { close(prep); close(conn); } }
From source file:org.wso2.carbon.dashboard.portal.core.datasource.DataBaseHandler.java
/** * To check whether a dashboard contains a gadget that is already deleted * * @param tenantId Id of the tenant which the dashboard belongs to * @param dashboardId Id of the dashboard * @return true if the dashboard contains gadget that is already deleted, otherwise false * @throws DashboardPortalException//from www .j a va 2 s.c om */ public boolean isDashboardDefective(int tenantId, String dashboardId) throws DashboardPortalException { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; String deletedGadgetState = "DELETED"; try { connection = dataBaseInitializer.getDBConnection(); preparedStatement = connection.prepareStatement(DataSourceConstants.SQL_CHECK_DEFECTIVE_DASHBOARD); preparedStatement.setInt(1, tenantId); preparedStatement.setString(2, dashboardId); preparedStatement.setString(3, deletedGadgetState); resultSet = preparedStatement.executeQuery(); if (!connection.getAutoCommit()) { connection.commit(); } if (resultSet.first()) { return true; } } catch (SQLException e) { log.error("Cannot check defective dashboard ", e); } finally { closeDatabaseResources(connection, preparedStatement, null); } return false; }
From source file:org.wso2.carbon.ml.dataset.internal.DatabaseHandler.java
/** * Retrieves the path of the data-set having the given ID, from the * database./*from w ww .j ava 2s . co m*/ * * @param datasetID Unique Identifier of the data-set * @return Absolute path of a given data-set * @throws DatabaseHandlerException */ protected String getDatasetUrl(String datasetID) throws DatabaseHandlerException { Connection connection = null; ResultSet result = null; PreparedStatement getStatement = null; try { connection = dataSource.getConnection(); connection.setAutoCommit(true); getStatement = connection.prepareStatement(SQLQueries.GET_DATASET_LOCATION); getStatement.setString(1, datasetID); result = getStatement.executeQuery(); if (result.first()) { return result.getNString(1); } else { logger.error("Invalid dataset ID: " + datasetID); throw new DatabaseHandlerException("Invalid dataset ID: " + datasetID); } } catch (SQLException e) { throw new DatabaseHandlerException("An error occured while reading the Dataset " + datasetID + " from the database: " + e.getMessage(), e); } finally { // Close the database resources. MLDatabaseUtils.closeDatabaseResources(connection, getStatement, result); } }
From source file:org.wso2.carbon.ml.dataset.internal.DatabaseHandler.java
/** * Retrieve the SamplePoints object for a given data-set. * * @param datasetID Unique Identifier of the data-set * @return SamplePoints object of the data-set * @throws DatabaseHandlerException *//*w w w . j a v a 2 s .com*/ private SamplePoints getDatasetSample(String datasetID) throws DatabaseHandlerException { Connection connection = null; PreparedStatement updateStatement = null; ResultSet result = null; SamplePoints samplePoints = null; try { connection = dataSource.getConnection(); connection.setAutoCommit(true); updateStatement = connection.prepareStatement(SQLQueries.GET_SAMPLE_POINTS); updateStatement.setString(1, datasetID); result = updateStatement.executeQuery(); if (result.first()) { samplePoints = (SamplePoints) result.getObject(1); } return samplePoints; } catch (SQLException e) { // Roll-back the changes. MLDatabaseUtils.rollBack(connection); throw new DatabaseHandlerException("An error occured while retrieving the sample of " + "dataset " + datasetID + ": " + e.getMessage(), e); } finally { // Close the database resources. MLDatabaseUtils.closeDatabaseResources(connection, updateStatement, result); } }