@Override public String add(final JSONObject jsonObject) throws RepositoryException { final JdbcTransaction currentTransaction = TX.get(); if (null == currentTransaction) { throw new RepositoryException("Invoking add() outside a transaction"); } final Connection connection = getConnection(); final List<Object> paramList = new ArrayList<>(); final StringBuilder sql = new StringBuilder(); String ret; try { if (Latkes.RuntimeDatabase.ORACLE == Latkes.getRuntimeDatabase()) { toOracleClobEmpty(jsonObject); } ret = buildAddSql(jsonObject, paramList, sql); JdbcUtil.executeSql(sql.toString(), paramList, connection, debug); JdbcUtil.fromOracleClobEmpty(jsonObject); } catch (final Exception e) { LOGGER.log(Level.ERROR, "Add failed", e); throw new RepositoryException(e); } return ret; }
/** * queryJsonObject. * * @param sql sql * @param paramList paramList * @param connection connection * @param tableName tableName * @param isDebug the specified debug flag * @return JSONObject only one record. * @throws SQLException SQLException * @throws JSONException JSONException * @throws RepositoryException repositoryException */ public static JSONObject queryJsonObject(final String sql, final List<Object> paramList, final Connection connection, final String tableName, final boolean isDebug) throws SQLException, JSONException, RepositoryException { return queryJson(sql, paramList, connection, true, tableName, isDebug); }
@Override public List<JSONObject> getRandomly(final int fetchSize) throws RepositoryException { final List<JSONObject> jsonObjects = new ArrayList<>(); final StringBuilder sql = new StringBuilder(); JSONArray jsonArray; final Connection connection = getConnection(); getRandomly(fetchSize, sql); try { jsonArray = JdbcUtil.queryJsonArray(sql.toString(), new ArrayList<>(), connection, getName(), debug); for (int i = 0; i < jsonArray.length(); i++) { jsonObjects.add(jsonArray.getJSONObject(i)); } } catch (final Exception e) { LOGGER.log(Level.ERROR, "Get randomly failed", e); throw new RepositoryException(e); } return jsonObjects; }
@Override public boolean clearTable(final String tableName, final boolean ifdrop) throws SQLException { final Connection connection = Connections.getConnection(); try { final StringBuilder clearTableSql = new StringBuilder(); clearTableSql(clearTableSql, tableName, ifdrop); return JdbcUtil.executeSql(clearTableSql.toString(), connection, false); } catch (final SQLException e) { throw e; } finally { connection.close(); } }
@Override public JSONObject get(final String id) throws RepositoryException { JSONObject ret; final StringBuilder sql = new StringBuilder(); final Connection connection = getConnection(); try { get(sql); final ArrayList<Object> paramList = new ArrayList<>(); paramList.add(id); ret = JdbcUtil.queryJsonObject(sql.toString(), paramList, connection, getName(), debug); } catch (final Exception e) { LOGGER.log(Level.ERROR, "Get failed", e); throw new RepositoryException(e); } return ret; }
/** * @param sql sql * @param paramList paramList * @param connection connection * @param ifOnlyOne ifOnlyOne to determine return object or array. * @param tableName tableName * @param isDebug the specified debug flag * @return JSONObject * @throws SQLException SQLException * @throws JSONException JSONException * @throws RepositoryException respsitoryException */ private static JSONObject queryJson(final String sql, final List<Object> paramList, final Connection connection, final boolean ifOnlyOne, final String tableName, final boolean isDebug) throws SQLException, JSONException, RepositoryException { if (isDebug || LOGGER.isTraceEnabled()) { LOGGER.log(Level.INFO, "Executing SQL [" + sql + "]"); } final PreparedStatement preparedStatement = connection.prepareStatement(sql); for (int i = 1; i <= paramList.size(); i++) { preparedStatement.setObject(i, paramList.get(i - 1)); } final ResultSet resultSet = preparedStatement.executeQuery(); final JSONObject jsonObject = resultSetToJsonObject(resultSet, ifOnlyOne, tableName); resultSet.close(); preparedStatement.close(); return jsonObject; }
fromOracleClobEmpty(jsonObject);
@Override public void remove(final Query query) throws RepositoryException { final StringBuilder deleteSQL = new StringBuilder("DELETE FROM ").append(getName()); final List<Object> paramList = new ArrayList<>(); final StringBuilder filterSql = new StringBuilder(); buildWhere(filterSql, paramList, query.getFilter()); if (StringUtils.isNotBlank(filterSql.toString())) { deleteSQL.append(" WHERE ").append(filterSql); } final Connection connection = getConnection(); try { JdbcUtil.executeSql(deleteSQL.toString(), paramList, connection, debug); } catch (final SQLException e) { LOGGER.log(Level.ERROR, "Remove failed", e); throw new RepositoryException(e); } }
/** * Count. * * @param sql sql * @param paramList paramList * @return count * @throws RepositoryException RepositoryException */ private long count(final StringBuilder sql, final List<Object> paramList) throws RepositoryException { final Connection connection = getConnection(); JSONObject jsonObject; long count; try { jsonObject = JdbcUtil.queryJsonObject(sql.toString(), paramList, connection, getName(), debug); count = jsonObject.getLong(jsonObject.keys().next()); } catch (final Exception e) { LOGGER.log(Level.ERROR, "Count failed", e); throw new RepositoryException(e); } return count; }
/** * @param sql sql * @param paramList paramList * @param connection connection * @param ifOnlyOne ifOnlyOne to determine return object or array. * @param tableName tableName * @param isDebug the specified debug flag * @return JSONObject * @throws SQLException SQLException * @throws JSONException JSONException * @throws RepositoryException respsitoryException */ private static JSONObject queryJson(final String sql, final List<Object> paramList, final Connection connection, final boolean ifOnlyOne, final String tableName, final boolean isDebug) throws SQLException, JSONException, RepositoryException { if (isDebug || LOGGER.isTraceEnabled()) { LOGGER.log(Level.INFO, "Executing SQL [" + sql + "]"); } final PreparedStatement preparedStatement = connection.prepareStatement(sql); for (int i = 1; i <= paramList.size(); i++) { preparedStatement.setObject(i, paramList.get(i - 1)); } final ResultSet resultSet = preparedStatement.executeQuery(); final JSONObject jsonObject = resultSetToJsonObject(resultSet, ifOnlyOne, tableName); resultSet.close(); preparedStatement.close(); return jsonObject; }
fromOracleClobEmpty(jsonObject);
@Override public void update(final String id, final JSONObject jsonObject) throws RepositoryException { if (StringUtils.isBlank(id)) { return; } final JdbcTransaction currentTransaction = TX.get(); if (null == currentTransaction) { throw new RepositoryException("Invoking update() outside a transaction"); } final JSONObject oldJsonObject = get(id); final Connection connection = getConnection(); final List<Object> paramList = new ArrayList<>(); final StringBuilder sqlBuilder = new StringBuilder(); try { if (Latkes.RuntimeDatabase.ORACLE == Latkes.getRuntimeDatabase()) { toOracleClobEmpty(jsonObject); } update(id, oldJsonObject, jsonObject, paramList, sqlBuilder); JdbcUtil.fromOracleClobEmpty(jsonObject); final String sql = sqlBuilder.toString(); if (StringUtils.isBlank(sql)) { return; } JdbcUtil.executeSql(sql, paramList, connection, debug); } catch (final Exception e) { LOGGER.log(Level.ERROR, "Update failed", e); throw new RepositoryException(e); } }
@Override public boolean createTable(final RepositoryDefinition repositoryDefinition) throws SQLException { final Connection connection = Connections.getConnection(); try { final StringBuilder createTableSql = new StringBuilder(); createTableHead(createTableSql, repositoryDefinition); createTableBody(createTableSql, repositoryDefinition); createTableEnd(createTableSql, repositoryDefinition); return JdbcUtil.executeSql(createTableSql.toString(), connection, false); } catch (final SQLException e) { throw e; } finally { connection.close(); } }
@Override public List<JSONObject> select(final String statement, final Object... params) throws RepositoryException { JSONArray jsonResults; final Connection connection = getConnection(); try { if (null == params || 0 == params.length) { jsonResults = JdbcUtil.queryJsonArray(statement, Collections.emptyList(), connection, getName(), debug); } else { jsonResults = JdbcUtil.queryJsonArray(statement, Arrays.asList(params), connection, getName(), debug); } return CollectionUtils.jsonArrayToList(jsonResults); } catch (final Exception e) { LOGGER.log(Level.ERROR, "Select failed", e); throw new RepositoryException(e); } }
@Override public JSONObject get(final String id) throws RepositoryException { JSONObject ret; final StringBuilder sql = new StringBuilder(); final Connection connection = getConnection(); try { get(sql); final ArrayList<Object> paramList = new ArrayList<>(); paramList.add(id); ret = JdbcUtil.queryJsonObject(sql.toString(), paramList, connection, getName(), debug); } catch (final Exception e) { LOGGER.log(Level.ERROR, "Get failed", e); throw new RepositoryException(e); } return ret; }
/** * queryJsonArray. * * @param sql sql * @param paramList paramList * @param connection connection * @param tableName tableName * @param isDebug the specified debug flag * @return JSONArray * @throws SQLException SQLException * @throws JSONException JSONException * @throws RepositoryException repositoryException */ public static JSONArray queryJsonArray(final String sql, final List<Object> paramList, final Connection connection, final String tableName, final boolean isDebug) throws SQLException, JSONException, RepositoryException { final JSONObject jsonObject = queryJson(sql, paramList, connection, false, tableName, isDebug); return jsonObject.getJSONArray(Keys.RESULTS); }
@Override public String add(final JSONObject jsonObject) throws RepositoryException { final JdbcTransaction currentTransaction = TX.get(); if (null == currentTransaction) { throw new RepositoryException("Invoking add() outside a transaction"); } final Connection connection = getConnection(); final List<Object> paramList = new ArrayList<>(); final StringBuilder sql = new StringBuilder(); String ret; try { if (Latkes.RuntimeDatabase.ORACLE == Latkes.getRuntimeDatabase()) { toOracleClobEmpty(jsonObject); } ret = buildAddSql(jsonObject, paramList, sql); JdbcUtil.executeSql(sql.toString(), paramList, connection, debug); JdbcUtil.fromOracleClobEmpty(jsonObject); } catch (final Exception e) { LOGGER.log(Level.ERROR, "Add failed", e); throw new RepositoryException(e); } return ret; }
@Override public void remove(final String id) throws RepositoryException { if (StringUtils.isBlank(id)) { return; } final JdbcTransaction currentTransaction = TX.get(); if (null == currentTransaction) { throw new RepositoryException("Invoking remove() outside a transaction"); } final StringBuilder sql = new StringBuilder(); final Connection connection = getConnection(); try { remove(id, sql); JdbcUtil.executeSql(sql.toString(), connection, debug); } catch (final Exception e) { LOGGER.log(Level.ERROR, "Remove failed", e); throw new RepositoryException(e); } }
final JSONArray jsonResults = JdbcUtil.queryJsonArray(sql.toString(), paramList, connection, getName(), query.isDebug());
/** * Count. * * @param sql sql * @param paramList paramList * @return count * @throws RepositoryException RepositoryException */ private long count(final StringBuilder sql, final List<Object> paramList) throws RepositoryException { final Connection connection = getConnection(); JSONObject jsonObject; long count; try { jsonObject = JdbcUtil.queryJsonObject(sql.toString(), paramList, connection, getName(), debug); count = jsonObject.getLong(jsonObject.keys().next()); } catch (final Exception e) { LOGGER.log(Level.ERROR, "Count failed", e); throw new RepositoryException(e); } return count; }