@Override public int size(final DatabaseTable table) throws DatabaseException { preOperationCheck(); final StringBuilder sb = new StringBuilder(); sb.append("SELECT COUNT(" + KEY_COLUMN + ") FROM ").append(table.toString()); PreparedStatement statement = null; ResultSet resultSet = null; try { statement = connection.prepareStatement(sb.toString()); resultSet = statement.executeQuery(); if (resultSet.next()) { return resultSet.getInt(1); } } catch (SQLException e) { final ErrorInformation errorInformation = new ErrorInformation( PwmError.ERROR_DB_UNAVAILABLE, "size operation failed: " + e.getMessage()); lastError = errorInformation; throw new DatabaseException(errorInformation); } finally { close(statement); close(resultSet); } updateStats(true, false); return 0; }
private static void checkIfTableExists(final Connection connection, final DatabaseTable table) throws SQLException { final StringBuilder sb = new StringBuilder(); sb.append("SELECT * FROM ").append(table.toString()).append(" WHERE " + KEY_COLUMN + " = '0'"); Statement statement = null; ResultSet resultSet = null; try { statement = connection.createStatement(); resultSet = statement.executeQuery(sb.toString()); } finally { close(statement); close(resultSet); } }
private ResultSet init() throws DatabaseException { final StringBuilder sb = new StringBuilder(); sb.append("SELECT " + KEY_COLUMN + " FROM ").append(table.toString()); try { final PreparedStatement statement = connection.prepareStatement(sb.toString()); return statement.executeQuery(); } catch (SQLException e) { final ErrorInformation errorInformation = new ErrorInformation( PwmError.ERROR_DB_UNAVAILABLE, "get iterator failed: " + e.getMessage()); lastError = errorInformation; throw new DatabaseException(errorInformation); } }
private boolean isValid(final Connection connection) { if (connection == null) { return false; } if (status != PwmService.STATUS.OPEN) { return false; } try { final Method getFreeSpaceMethod = File.class.getMethod("isValid"); final Object rawResult = getFreeSpaceMethod.invoke(connection, 10); return (Boolean) rawResult; } catch (NoSuchMethodException e) { /* no error, pre java 1.6 doesn't have this method */ } catch (Exception e) { LOGGER.debug( "error checking for isValid for " + connection.toString() + ",: " + e.getMessage()); } final StringBuilder sb = new StringBuilder(); sb.append("SELECT * FROM ") .append(DatabaseTable.PWM_META.toString()) .append(" WHERE " + KEY_COLUMN + " = ?"); PreparedStatement statement = null; ResultSet resultSet = null; try { statement = connection.prepareStatement(sb.toString()); statement.setString(1, KEY_ENGINE_START_PREFIX + instanceID); statement.setMaxRows(1); resultSet = statement.executeQuery(); if (resultSet.next()) { resultSet.getString(VALUE_COLUMN); } } catch (SQLException e) { final ErrorInformation errorInformation = new ErrorInformation( PwmError.ERROR_DB_UNAVAILABLE, "isValid operation failed: " + e.getMessage()); lastError = errorInformation; LOGGER.error(errorInformation.toDebugStr()); return false; } finally { close(statement); close(resultSet); } return true; }
@Override public String get(final DatabaseTable table, final String key) throws DatabaseException { if (traceLogging) { LOGGER.trace("attempting get operation for table=" + table + ", key=" + key); } preOperationCheck(); final StringBuilder sb = new StringBuilder(); sb.append("SELECT * FROM ").append(table.toString()).append(" WHERE " + KEY_COLUMN + " = ?"); PreparedStatement statement = null; ResultSet resultSet = null; String returnValue = null; try { statement = connection.prepareStatement(sb.toString()); statement.setString(1, key); statement.setMaxRows(1); resultSet = statement.executeQuery(); if (resultSet.next()) { returnValue = resultSet.getString(VALUE_COLUMN); } } catch (SQLException e) { final ErrorInformation errorInformation = new ErrorInformation( PwmError.ERROR_DB_UNAVAILABLE, "get operation failed: " + e.getMessage()); lastError = errorInformation; throw new DatabaseException(errorInformation); } finally { close(statement); close(resultSet); } if (traceLogging) { final LinkedHashMap<String, Object> debugOutput = new LinkedHashMap<>(); debugOutput.put("table", table); debugOutput.put("key", key); debugOutput.put("result", returnValue); LOGGER.trace( "get operation result: " + JsonUtil.serializeMap(debugOutput, JsonUtil.Flag.PrettyPrint)); } updateStats(true, false); return returnValue; }
@Override public boolean remove(final DatabaseTable table, final String key) throws DatabaseException { if (traceLogging) { LOGGER.trace("attempting remove operation for table=" + table + ", key=" + key); } boolean result = contains(table, key); if (result) { final StringBuilder sqlText = new StringBuilder(); sqlText.append("DELETE FROM ").append(table.toString()).append(" WHERE " + KEY_COLUMN + "=?"); PreparedStatement statement = null; try { statement = connection.prepareStatement(sqlText.toString()); statement.setString(1, key); statement.executeUpdate(); LOGGER.trace("remove operation succeeded for table=" + table + ", key=" + key); } catch (SQLException e) { final ErrorInformation errorInformation = new ErrorInformation( PwmError.ERROR_DB_UNAVAILABLE, "remove operation failed: " + e.getMessage()); lastError = errorInformation; throw new DatabaseException(errorInformation); } finally { close(statement); } } if (traceLogging) { final Map<String, Object> debugOutput = new LinkedHashMap<>(); debugOutput.put("table", table); debugOutput.put("key", key); debugOutput.put("result", result); LOGGER.trace( "remove operation result: " + JsonUtil.serializeMap(debugOutput, JsonUtil.Flag.PrettyPrint)); } updateStats(true, false); return result; }
private static void initTable( final Connection connection, final DatabaseTable table, final DBConfiguration dbConfiguration) throws DatabaseException { try { checkIfTableExists(connection, table); LOGGER.trace("table " + table + " appears to exist"); } catch (SQLException e) { // assume error was due to table missing; { final StringBuilder sqlString = new StringBuilder(); sqlString.append("CREATE table ").append(table.toString()).append(" (").append("\n"); sqlString .append(" " + KEY_COLUMN + " ") .append(dbConfiguration.getColumnTypeKey()) .append("(") .append(KEY_COLUMN_LENGTH) .append(") NOT NULL PRIMARY KEY,") .append("\n"); sqlString .append(" " + VALUE_COLUMN + " ") .append(dbConfiguration.getColumnTypeValue()) .append(" "); sqlString.append("\n"); sqlString.append(")").append("\n"); LOGGER.trace( "attempting to execute the following sql statement:\n " + sqlString.toString()); Statement statement = null; try { statement = connection.createStatement(); statement.execute(sqlString.toString()); LOGGER.debug("created table " + table.toString()); } catch (SQLException ex) { LOGGER.error("error creating new table " + table.toString() + ": " + ex.getMessage()); } finally { close(statement); } } { final String indexName = table.toString() + "_IDX"; final StringBuilder sqlString = new StringBuilder(); sqlString.append("CREATE index ").append(indexName); sqlString.append(" ON ").append(table.toString()); sqlString.append(" (").append(KEY_COLUMN).append(")"); Statement statement = null; LOGGER.trace( "attempting to execute the following sql statement:\n " + sqlString.toString()); try { statement = connection.createStatement(); statement.execute(sqlString.toString()); LOGGER.debug("created index " + indexName); } catch (SQLException ex) { LOGGER.error("error creating new index " + indexName + ": " + ex.getMessage()); } finally { close(statement); } } } }