List of usage examples for org.apache.commons.dbutils DbUtils closeQuietly
public static void closeQuietly(Statement stmt)
Statement, avoid closing if null and hide any SQLExceptions that occur. From source file:com.zionex.t3sinc.util.db.SincDatabaseUtility.java
public void close(ResultSet resultSet) { DbUtils.closeQuietly(resultSet); }
From source file:com.quinsoft.zeidon.dbhandler.JdbcHandler.java
@Override public void endTransaction(boolean commit) { try {/* w w w.j a va 2 s .c o m*/ // Close any statements that were cached. if (cachedStatements != null) { for (PreparedStatementCacheValue v : cachedStatements.values()) DbUtils.closeQuietly(v.ps); task.dblog().trace("Loaded %d statements from cache\nTotal cache size = %d", cachedStatementCount, cachedStatements.size()); cachedStatements.clear(); } if (closeTransaction) { if (commit) transaction.getConnection().commit(); else transaction.getConnection().rollback(); transaction.close(); task.dblog().debug("JDBC: closed transaction"); transaction = null; } } catch (Throwable e) { throw ZeidonException.prependMessage(e, "JDBC = %s", options.getOiSourceUrl()); } }
From source file:com.intelligentz.appointmentz.controllers.Data.java
public static String checkRoomId(String room_number, String hospital_id) { String check = "default"; try {//from ww w . ja va 2 s. c o m connection = DBConnection.getDBConnection().getConnection(); String SQL = "select room_number from room where hospital_id = ? and room_number = ?"; preparedStatement = connection.prepareStatement(SQL); preparedStatement.setString(1, hospital_id); preparedStatement.setString(2, room_number); resultSet = preparedStatement.executeQuery(); if (resultSet.next()) { check = "Available"; } else { check = "Unavailable"; } } catch (SQLException | IOException | PropertyVetoException e) { check = "Error"; } finally { try { DbUtils.closeQuietly(resultSet); DbUtils.closeQuietly(preparedStatement); DbUtils.close(connection); } catch (SQLException ex) { Logger.getLogger(register.class.getName()).log(Level.SEVERE, ex.toString(), ex); } } return check; }
From source file:azkaban.scheduler.JdbcScheduleLoader.java
@Override public void updateNextExecTime(Schedule s) throws ScheduleManagerException { logger.info("Update schedule " + s.getScheduleName() + " into db. "); Connection connection = getConnection(); QueryRunner runner = new QueryRunner(); try {//from ww w . ja v a 2s. c om runner.update(connection, UPDATE_NEXT_EXEC_TIME, s.getNextExecTime(), s.getScheduleId()); } catch (SQLException e) { e.printStackTrace(); logger.error(UPDATE_NEXT_EXEC_TIME + " failed.", e); throw new ScheduleManagerException("Update schedule " + s.getScheduleName() + " into db failed. ", e); } finally { DbUtils.closeQuietly(connection); } }
From source file:librec.data.DataDAO.java
/** * Read data from the database. Note that we don't take care of duplicate lines. * /*w w w . ja v a 2 s . c o m*/ * @return a sparse matrix storing all the relevant data */ public SparseMatrix[] loadData(double binThold, int maxIds) throws Exception { Logs.info("Dataset: From database"); // Table {row-id, col-id, rate} Table<Integer, Integer, Double> dataTable = HashBasedTable.create(); // Table {row-id, col-id, timestamp} Table<Integer, Integer, Long> timeTable = null; // Map {col-id, multiple row-id}: used to fast build a rating matrix Multimap<Integer, Integer> colMap = HashMultimap.create(); DatabaseManager dbm = new DatabaseManager(); Connection conn = null; PreparedStatement stmnt = null; ResultSet rs = null; minTimestamp = Long.MAX_VALUE; maxTimestamp = Long.MIN_VALUE; try { conn = dbm.getConnection(); if (maxIds > 0) { stmnt = conn.prepareStatement( "SELECT UserId, ItemId, Time, Rating FROM Rating WHERE ItemId < ? AND UserId < ?;"); stmnt.setInt(1, maxIds); stmnt.setInt(2, maxIds); } else { stmnt = conn.prepareStatement("SELECT UserId, ItemId, Time, Rating FROM Rating;"); } // Logs.info("Executing statement: {}", stmnt); rs = stmnt.executeQuery(); while (rs.next()) { int user = rs.getInt("UserId"); int item = rs.getInt("ItemId"); // convert time to milliseconds long mms = rs.getTimestamp("Time").getTime(); long timestamp = timeUnit.toMillis(mms); Double rate = rs.getDouble("Rating"); // binarize the rating for item recommendation task if (binThold >= 0) rate = rate > binThold ? 1.0 : 0.0; scaleDist.add(rate); // inner id starting from 0 int row = userIds.containsKey(user) ? userIds.get(user) : userIds.size(); userIds.put(user, row); int col = itemIds.containsKey(item) ? itemIds.get(item) : itemIds.size(); itemIds.put(item, col); dataTable.put(row, col, rate); colMap.put(col, row); // record rating's issuing time if (timeTable == null) timeTable = HashBasedTable.create(); if (minTimestamp > timestamp) minTimestamp = timestamp; if (maxTimestamp < timestamp) maxTimestamp = timestamp; timeTable.put(row, col, timestamp); // if(dataTable.size() % 100000 == 0) // Logs.info("Ratings loaded into dataTable: {}", dataTable.size()); } } catch (SQLException e) { e.printStackTrace(); } finally { DbUtils.closeQuietly(stmnt); DbUtils.closeQuietly(conn); DbUtils.closeQuietly(rs); } // Logs.info("All ratings loaded into dataTable"); numRatings = scaleDist.size(); ratingScale = new ArrayList<>(scaleDist.elementSet()); Collections.sort(ratingScale); int numRows = numUsers(), numCols = numItems(); // if min-rate = 0.0, shift upper a scale double minRate = ratingScale.get(0).doubleValue(); double epsilon = minRate == 0.0 ? ratingScale.get(1).doubleValue() - minRate : 0; if (epsilon > 0) { // shift upper a scale for (int i = 0, im = ratingScale.size(); i < im; i++) { double val = ratingScale.get(i); ratingScale.set(i, val + epsilon); } // update data table for (int row = 0; row < numRows; row++) { for (int col = 0; col < numCols; col++) { if (dataTable.contains(row, col)) dataTable.put(row, col, dataTable.get(row, col) + epsilon); } } } String dateRange = ""; dateRange = String.format(", Timestamps = {%s, %s}", Dates.toString(minTimestamp), Dates.toString(maxTimestamp)); Logs.debug("With Specs: {Users, {}} = {{}, {}, {}}, Scale = {{}}{}", (isItemAsUser ? "Users, Links" : "Items, Ratings"), numRows, numCols, numRatings, Strings.toString(ratingScale), dateRange); // build rating matrix rateMatrix = new SparseMatrix(numRows, numCols, dataTable, colMap); if (timeTable != null) timeMatrix = new SparseMatrix(numRows, numCols, timeTable, colMap); // release memory of data table dataTable = null; timeTable = null; return new SparseMatrix[] { rateMatrix, timeMatrix }; }
From source file:com.mirth.connect.server.migration.Migrate3_3_0.java
private void migrateDataPrunerConfiguration() { boolean enabled = true; String time = ""; String interval = ""; String dayOfWeek = ""; String dayOfMonth = "1"; ResultSet results = null;//from ww w. ja v a2 s .c o m PreparedStatement statement = null; Connection connection = null; try { connection = getConnection(); try { statement = connection .prepareStatement("SELECT NAME, VALUE FROM CONFIGURATION WHERE CATEGORY = 'Data Pruner'"); results = statement.executeQuery(); while (results.next()) { String name = results.getString(1); String value = results.getString(2); if (name.equals("interval")) { interval = value; } else if (name.equals("time")) { time = value; } else if (name.equals("dayOfWeek")) { dayOfWeek = value; } else if (name.equals("dayOfMonth")) { dayOfMonth = value; } } } catch (SQLException e) { logger.error("Failed to read Data Pruner configuration properties.", e); } finally { DbUtils.closeQuietly(statement); DbUtils.closeQuietly(results); } enabled = !interval.equals("disabled"); String pollingType = "INTERVAL"; String pollingHour = "12"; String pollingMinute = "0"; boolean weekly = !StringUtils.equals(interval, "monthly"); boolean[] activeDays = new boolean[] { true, true, true, true, true, true, true, true }; if (enabled && !StringUtils.equals(interval, "hourly")) { SimpleDateFormat timeDateFormat = new SimpleDateFormat("hh:mm aa"); DateFormatter timeFormatter = new DateFormatter(timeDateFormat); Date timeDate = null; try { timeDate = (Date) timeFormatter.stringToValue(time); Calendar timeCalendar = Calendar.getInstance(); timeCalendar.setTime(timeDate); pollingType = "TIME"; pollingHour = String.valueOf(timeCalendar.get(Calendar.HOUR_OF_DAY)); pollingMinute = String.valueOf(timeCalendar.get(Calendar.MINUTE)); if (StringUtils.equals(interval, "weekly")) { SimpleDateFormat dayDateFormat = new SimpleDateFormat("EEEEEEEE"); DateFormatter dayFormatter = new DateFormatter(dayDateFormat); Date dayDate = (Date) dayFormatter.stringToValue(dayOfWeek); Calendar dayCalendar = Calendar.getInstance(); dayCalendar.setTime(dayDate); activeDays = new boolean[] { false, false, false, false, false, false, false, false }; activeDays[dayCalendar.get(Calendar.DAY_OF_WEEK)] = true; } } catch (Exception e) { logger.error("Failed to get Data Pruner time properties", e); } } DonkeyElement pollingProperties = new DonkeyElement( "<com.mirth.connect.donkey.model.channel.PollConnectorProperties/>"); pollingProperties.setAttribute("version", "3.3.0"); pollingProperties.addChildElementIfNotExists("pollingType", pollingType); pollingProperties.addChildElementIfNotExists("pollOnStart", "false"); pollingProperties.addChildElementIfNotExists("pollingFrequency", "3600000"); pollingProperties.addChildElementIfNotExists("pollingHour", pollingHour); pollingProperties.addChildElementIfNotExists("pollingMinute", pollingMinute); pollingProperties.addChildElementIfNotExists("cronJobs"); DonkeyElement advancedProperties = pollingProperties .addChildElementIfNotExists("pollConnectorPropertiesAdvanced"); advancedProperties.addChildElementIfNotExists("weekly", weekly ? "true" : "false"); DonkeyElement inactiveDays = advancedProperties.addChildElementIfNotExists("inactiveDays"); if (inactiveDays != null) { for (int index = 0; index < 8; ++index) { inactiveDays.addChildElement("boolean", activeDays[index] ? "false" : "true"); } } advancedProperties.addChildElementIfNotExists("dayOfMonth", dayOfMonth); advancedProperties.addChildElementIfNotExists("allDay", "true"); advancedProperties.addChildElementIfNotExists("startingHour", "8"); advancedProperties.addChildElementIfNotExists("startingMinute", "0"); advancedProperties.addChildElementIfNotExists("endingHour", "17"); advancedProperties.addChildElementIfNotExists("endingMinute", "0"); PreparedStatement inputStatement = null; try { inputStatement = connection .prepareStatement("INSERT INTO CONFIGURATION (CATEGORY, NAME, VALUE) VALUES (?, ?, ?)"); inputStatement.setString(1, "Data Pruner"); inputStatement.setString(2, "pollingProperties"); inputStatement.setString(3, pollingProperties.toXml()); inputStatement.executeUpdate(); } catch (Exception e) { logger.error("Failed to insert Data Pruner configuration pollingProperties.", e); } finally { DbUtils.closeQuietly(inputStatement); } PreparedStatement updateStatement = null; try { updateStatement = connection.prepareStatement( "UPDATE CONFIGURATION SET NAME = ?, VALUE = ? WHERE CATEGORY = ? AND NAME = ?"); updateStatement.setString(1, "enabled"); updateStatement.setString(2, enabled ? "true" : "false"); updateStatement.setString(3, "Data Pruner"); updateStatement.setString(4, "interval"); updateStatement.executeUpdate(); } finally { DbUtils.closeQuietly(updateStatement); } PreparedStatement deleteStatement = null; try { deleteStatement = connection .prepareStatement("DELETE FROM CONFIGURATION WHERE CATEGORY = ? AND NAME IN (?, ?, ?)"); deleteStatement.setString(1, "Data Pruner"); deleteStatement.setString(2, "time"); deleteStatement.setString(3, "dayOfWeek"); deleteStatement.setString(4, "dayOfMonth"); deleteStatement.executeUpdate(); } finally { DbUtils.closeQuietly(deleteStatement); } } catch (Exception e) { logger.error("Failed to modify Data Pruner configuration properties", e); } }
From source file:hermes.store.SingleUserMessageStore.java
public int getDepth(Destination d) throws JMSException { d = createStoreDestination(d);/*w w w . j a v a2 s. c o m*/ synchronized (depths) { if (depths.containsKey(d)) { return depths.get(d); } } final Connection connection = connectionPool.get(); try { int depth = adapter.getDepth(connection, getId(), d); synchronized (depths) { depths.put(d, depth); } return depth; } catch (SQLException e) { throw new HermesException(e); } finally { DbUtils.closeQuietly(connection); } }
From source file:jp.co.golorp.emarf.sql.MetaData.java
/** * @return ?/* w w w .j av a 2 s . com*/ */ private static List<TableInfo> prepareTableInfos() { String catalog = BUNDLE.getString("catalog"); String schemaPattern = BUNDLE.getString("schemaPattern"); String tableNamePattern = BUNDLE.getString("tableNamePattern"); String typesText = BUNDLE.getString("types"); String[] types = null; if (StringUtil.isNotBlank(typesText)) { types = StringUtil.split(typesText); } // oracle if (catalog.equals("")) { catalog = null; } if (schemaPattern.equals("")) { schemaPattern = null; } if (tableNamePattern.equals("")) { tableNamePattern = null; } if (StringUtil.isBlank(types)) { types = null; } List<TableInfo> tableInfos = new ArrayList<TableInfo>(); Connection cn = Connections.get(); try { ResultSet rs = null; try { // ? rs = cn.getMetaData().getTables(catalog, schemaPattern, tableNamePattern, types); while (rs.next()) { // ?? String tableName = rs.getString("TABLE_NAME"); // String tableType = rs.getString("TABLE_TYPE"); // String remarks = rs.getString("REMARKS"); if (StringUtil.isBlank(remarks)) { if (commentSource == TableCommentSources.showTableStatus) { // MySQL???showTableStatus????? remarks = getTableCommentByShowTableStatus(cn, tableName); } else if (commentSource == TableCommentSources.userTabComments) { // Oracle???userTabComments????? remarks = getTableCommentByUserTabComments(cn, tableName); } else { // ?? remarks = getTableComment(cn, tableName); } } // view?? Map<String, ViewInfo> viewInfos = null; Set<String> primaryKeys = null; if (tableType.equals("VIEW")) { viewInfos = getViewInfos(cn, tableName); } else { primaryKeys = MetaData.getPrimaryKeys(cn, tableName); } // ????? String tableMei = null; if (StringUtil.isNotBlank(remarks)) { tableMei = remarks.split("\t")[0]; } // ? List<ColumnInfo> columnInfos = MetaData.getColumnInfos(cn, tableName); // ?? String modelName = StringUtil.toUpperCamelCase(tableName); // tableInfos.add(new TableInfo(modelName, tableName, tableType, tableMei, primaryKeys, columnInfos, viewInfos)); } } catch (SQLException e) { throw new SystemError(e); } finally { DbUtils.closeQuietly(rs); } } catch (Exception e) { throw new SystemError(e); } finally { Connections.close(); } for (String columnInfoName : NOT_EXIST_COLUMN_INFO_NAMES) { LOG.trace("Column MetaData [" + columnInfoName + "] is not exists."); } return tableInfos; }
From source file:com.mirth.connect.server.util.DatabaseConnection.java
public CachedRowSet executeUpdateAndGetGeneratedKeys(String expression, List<Object> parameters) throws SQLException { PreparedStatement statement = null; try {//from w ww . j a v a 2 s. c o m statement = connection.prepareStatement(expression, Statement.RETURN_GENERATED_KEYS); logger.debug("executing prepared statement:\n" + expression); ListIterator<Object> iterator = parameters.listIterator(); while (iterator.hasNext()) { int index = iterator.nextIndex() + 1; Object value = iterator.next(); logger.debug("adding parameter: index=" + index + ", value=" + value); statement.setObject(index, value); } statement.executeUpdate(); CachedRowSetImpl crs = new CachedRowSetImpl(); crs.populate(statement.getGeneratedKeys()); return crs; } catch (SQLException e) { throw e; } finally { DbUtils.closeQuietly(statement); } }
From source file:com.mirth.connect.connectors.jdbc.DatabaseReceiverQuery.java
@Override public Object poll() throws DatabaseReceiverException, InterruptedException { ResultSet resultSet = null;/* w w w .ja v a 2s . c o m*/ int attempts = 0; String channelId = connector.getChannelId(); String channelName = connector.getChannel().getName(); int maxRetryCount = NumberUtils .toInt(replacer.replaceValues(connectorProperties.getRetryCount(), channelId, channelName), 0); int retryInterval = NumberUtils .toInt(replacer.replaceValues(connectorProperties.getRetryInterval(), channelId, channelName), 0); boolean done = false; boolean contextFactoryChanged = false; try { contextFactoryChanged = checkContextFactory(); } catch (Exception e) { throw new DatabaseReceiverException(e); } while (!done && !connector.isTerminated()) { CachedRowSet cachedRowSet = null; try { /* * If the keepConnectionOpen option is not enabled, we open the database * connection(s) here. They will be closed in afterPoll(). Always reset the * connection if the connector's context factory has changed. */ if (contextFactoryChanged || !connectorProperties.isKeepConnectionOpen()) { initSelectConnection(); if (connectorProperties.getUpdateMode() == DatabaseReceiverProperties.UPDATE_EACH) { initUpdateConnection(); } } int objectIndex = 1; /* * Using the list of placeholder keys found in the select statement (selectParams), * get the corresponding values from JdbcUtils.getParameters() which uses a * TemplateValueReplacer to to look up values from a default context based on the * given channel id */ for (Object param : JdbcUtils.getParameters(selectParams, connector.getChannelId(), connector.getChannel().getName(), null, null, null)) { selectStatement.setObject(objectIndex++, param); } resultSet = selectStatement.executeQuery(); // if we are not caching the ResultSet, return it immediately if (connectorProperties.isCacheResults()) { // if we are caching the ResultSet, convert it into a CachedRowSet and return it cachedRowSet = new CachedRowSetImpl(); cachedRowSet.populate(resultSet); DbUtils.closeQuietly(resultSet); resultSet = cachedRowSet; } done = true; } catch (SQLException e) { DbUtils.closeQuietly(resultSet); DbUtils.closeQuietly(cachedRowSet); if (attempts++ < maxRetryCount && !connector.isTerminated()) { logger.error("An error occurred while polling for messages, retrying after " + retryInterval + " ms...", e); // Wait the specified amount of time before retrying Thread.sleep(retryInterval); if (connectorProperties.isKeepConnectionOpen() && !JdbcUtils.isValidConnection(selectConnection)) { try { initSelectConnection(); } catch (SQLException e1) { } } } else { throw new DatabaseReceiverException(e); } } } return resultSet; }