/** * Removes an record. * * @param id id * @param sql sql */ private void remove(final String id, final StringBuilder sql) { sql.append("DELETE FROM ").append(getName()).append(" WHERE ").append(JdbcRepositories.getDefaultKeyName()).append(" = '"). append(id).append("'"); }
/** * get. * * @param sql sql */ private void get(final StringBuilder sql) { sql.append("SELECT * FROM ").append(getName()).append(" WHERE ").append(JdbcRepositories.getDefaultKeyName()).append(" = ?"); }
@Override public Map<String, JSONObject> get(final Iterable<String> ids) throws RepositoryException { final Map<String, JSONObject> map = new HashMap<>(); JSONObject jsonObject; for (final String id : ids) { jsonObject = get(id); map.put(jsonObject.optString(JdbcRepositories.getDefaultKeyName()), jsonObject); } return map; }
@Override public String queryPage(final int start, final int end, final String selectSql, final String filterSql, final String orderBySql, final String tableName) { final StringBuilder sql = new StringBuilder(); /* select * from ( select ROW_NUMBER() over(order by id desc) rownum, * from .... where .... order by .... ) a where rownum>10000 and rownum<10501 */ final String over = StringUtils.isBlank(orderBySql) ? "order by " + JdbcRepositories.getDefaultKeyName() + " desc" : orderBySql; sql.append(selectSql).append(" from (select top 100 percent ROW_NUMBER() over(").append(over).append(") rownum, * from ").append( tableName); if (StringUtils.isNotBlank(filterSql)) { sql.append(" where ").append(filterSql); } sql.append(orderBySql); sql.append(" ) a where rownum > ").append(start).append(" and rownum <= ").append(end); return sql.toString(); }
@Override public String queryPage(final int start, final int end, final String selectSql, final String filterSql, final String orderBySql, final String tableName) { /* SELECT * FROM ( SELECT a.*, rownum r__ FROM ( SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%' ORDER BY OrderDate DESC, ShippingDate DESC ) a WHERE rownum < ((pageNumber * pageSize) + 1 ) ) WHERE r__ >= (((pageNumber-1) * pageSize) + 1) */ final StringBuilder sql = new StringBuilder(); final String orderBy = StringUtils.isBlank(orderBySql) ? " order by " + JdbcRepositories.getDefaultKeyName() + " desc" : orderBySql; sql.append(selectSql).append(" from (select a.*, rownum r__ from (select * from ").append(tableName); if (StringUtils.isNotBlank(filterSql)) { sql.append(" where ").append(filterSql); } sql.append(orderBy).append(" ) a where rownum < ").append(end).append(") where r__ >= ").append(start); return sql.toString(); }
@Override public long count() throws RepositoryException { final StringBuilder sql = new StringBuilder("SELECT COUNT(" + JdbcRepositories.getDefaultKeyName() + ") FROM ").append(getName()); return count(sql, new ArrayList<>()); }
@Override public long count(final Query query) throws RepositoryException { final StringBuilder countSql = new StringBuilder("SELECT COUNT(" + JdbcRepositories.getDefaultKeyName() + ") FROM ").append(getName()); final List<Object> paramList = new ArrayList<>(); final StringBuilder filterSql = new StringBuilder(); buildWhere(filterSql, paramList, query.getFilter()); if (StringUtils.isNotBlank(filterSql.toString())) { countSql.append(" WHERE ").append(filterSql); } return (int) count(countSql, paramList); }
/** * setUpdateProperties. * * @param id id * @param needUpdateJsonObject needUpdateJsonObject * @param paramList paramList * @param sql sql * @throws JSONException JSONException */ private void setUpdateProperties(final String id, final JSONObject needUpdateJsonObject, final List<Object> paramList, final StringBuilder sql) throws JSONException { final Iterator<String> keys = needUpdateJsonObject.keys(); String key; boolean isFirst = true; final StringBuilder wildcardString = new StringBuilder(); while (keys.hasNext()) { key = keys.next(); if (isFirst) { wildcardString.append(" SET ").append(key).append(" = ?"); isFirst = false; } else { wildcardString.append(", ").append(key).append(" = ?"); } paramList.add(needUpdateJsonObject.get(key)); } sql.append("UPDATE ").append(getName()).append(wildcardString).append(" WHERE ").append(JdbcRepositories.getDefaultKeyName()).append(" = ?"); paramList.add(id); }
final StringBuilder countBuilder = new StringBuilder("SELECT COUNT(" + JdbcRepositories.getDefaultKeyName() + ") FROM ").append(getName()); if (StringUtils.isNotBlank(whereBuilder.toString())) { countBuilder.append(" WHERE ").append(whereBuilder);
/** * Removes an record. * * @param id id * @param sql sql */ private void remove(final String id, final StringBuilder sql) { sql.append("DELETE FROM ").append(getName()).append(" WHERE ").append(JdbcRepositories.getDefaultKeyName()).append(" = '"). append(id).append("'"); }
/** * get. * * @param sql sql */ private void get(final StringBuilder sql) { sql.append("SELECT * FROM ").append(getName()).append(" WHERE ").append(JdbcRepositories.getDefaultKeyName()).append(" = ?"); }
@Override public Map<String, JSONObject> get(final Iterable<String> ids) throws RepositoryException { final Map<String, JSONObject> map = new HashMap<>(); JSONObject jsonObject; for (final String id : ids) { jsonObject = get(id); map.put(jsonObject.optString(JdbcRepositories.getDefaultKeyName()), jsonObject); } return map; }
@Override public long count() throws RepositoryException { final StringBuilder sql = new StringBuilder("SELECT COUNT(" + JdbcRepositories.getDefaultKeyName() + ") FROM ").append(getName()); return count(sql, new ArrayList<>()); }
@Override public long count(final Query query) throws RepositoryException { final StringBuilder countSql = new StringBuilder("SELECT COUNT(" + JdbcRepositories.getDefaultKeyName() + ") FROM ").append(getName()); final List<Object> paramList = new ArrayList<>(); final StringBuilder filterSql = new StringBuilder(); buildWhere(filterSql, paramList, query.getFilter()); if (StringUtils.isNotBlank(filterSql.toString())) { countSql.append(" WHERE ").append(filterSql); } return (int) count(countSql, paramList); }
/** * setUpdateProperties. * * @param id id * @param needUpdateJsonObject needUpdateJsonObject * @param paramList paramList * @param sql sql * @throws JSONException JSONException */ private void setUpdateProperties(final String id, final JSONObject needUpdateJsonObject, final List<Object> paramList, final StringBuilder sql) throws JSONException { final Iterator<String> keys = needUpdateJsonObject.keys(); String key; boolean isFirst = true; final StringBuilder wildcardString = new StringBuilder(); while (keys.hasNext()) { key = keys.next(); if (isFirst) { wildcardString.append(" SET ").append(key).append(" = ?"); isFirst = false; } else { wildcardString.append(", ").append(key).append(" = ?"); } paramList.add(needUpdateJsonObject.get(key)); } sql.append("UPDATE ").append(getName()).append(wildcardString).append(" WHERE ").append(JdbcRepositories.getDefaultKeyName()).append(" = ?"); paramList.add(id); }
final StringBuilder countBuilder = new StringBuilder("SELECT COUNT(" + JdbcRepositories.getDefaultKeyName() + ") FROM ").append(getName()); if (StringUtils.isNotBlank(whereBuilder.toString())) { countBuilder.append(" WHERE ").append(whereBuilder);