im.dadoo.spring.jdbc.support.Criteria Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of spring-jdbc-support Show documentation
Show all versions of spring-jdbc-support Show documentation
support for generating "where" and "set" clause based on spring-jdbc
The newest version!
package im.dadoo.spring.jdbc.support;
import im.dadoo.spring.jdbc.support.condition.Order;
import im.dadoo.spring.jdbc.support.util.Pair;
import im.dadoo.spring.jdbc.support.condition.Condition;
import im.dadoo.spring.jdbc.support.util.Util;
import java.util.ArrayList;
import java.util.List;
/**
* Criteria is used to generate "where" clause, "set" clause and "order" clause dynamically
*
* @author codekitten
* @since 0.1
*/
public final class Criteria {
private Criteria() {}
/**
* This function is to generate "SET" string for the "UPDATE" sentence.
* When you want to update the fields such as "name" and "state", you can follow the code as below.
*
* List<String> fields = new ArrayList<>();
* fields.add("name");
* fields.add("state");
* String clause = Criteria.set(fields);
* //clause will be "SET name = :name, state = :state"
*
* Then you can use spring-jdbc to update the datebase.
* @param fields the fields expected to be updated
* @return SET clause
* @since 0.1
*/
public static String set(final List fields) {
return set(fields, null);
}
/**
* This function is to generate "SET" string for the "UPDATE" sentence.But the value could be customized.
* When you want to update the fields such as "name" and "state", you can follow the code as below.
*
* List<String> fields = new ArrayList<>();
* fields.add("name");
* fields.add("state");
*
* List<String> values1 = new ArrayList<>();
* values1.add("test_name");
* values1.add("test_state");
* String clause1 = Criteria.set(fields,values1);
* //clause1 will be "SET name = :test_name, state = :test_state"
*
* List<String> values2 = new ArrayList<>();
* values2.add(null);
* values2.add("test_state");
* String clause2 = Criteria.set(fields,values2);
* //clause2 will be "SET name = :name, state = :test_state"
*
* Then you can use spring-jdbc to update the datebase.
*
* Caution:If the values is not null, the length of fields and values must be the same!
*
* @param fields the fields expected to be updated
* @param values the placeholder of values
* @return SET clause
* @since 0.3
*/
public static String set(List fields, List values) {
String result = "";
if (fields != null && !fields.isEmpty()) {
if (!Util.checkFields(fields)) {
throw new IllegalArgumentException("some field in fields is null or empty");
}
if (values == null) {
values = new ArrayList<>();
for (String field : fields) {
values.add(field);
}
}
if (fields.size() != values.size()) {
throw new IllegalArgumentException("The length of fields and values should be the same");
}
List kvs = new ArrayList<>(fields.size());
for (int i = 0; i < fields.size(); i++) {
String field = fields.get(i);
String value = values.get(i);
if (value == null || value.isEmpty()) {
value = field;
}
kvs.add(String.format("%s = %s", field, Util.placeholder(value)));
}
result = String.format("SET %s", Util.join(kvs));
}
return result;
}
/**
* This function is to generate "WHERE" clause for all the sql sentence.
* When you want to select or update or delete the records with some "WHERE" conditions,
* you can follow the code as below.
*
* List<Condition> conds = new ArrayList<>();
* conds.add(Conditions.eq(name));
* conds.add(Conditions.gt(date));
* String clause = Criteria.where(conds);
* //clause will be "WHERE name = :name and date > :date"
*
* Then you can use spring-jdbc to handle the datebase.
* @param conditions conditions for where clause
* @return WHERE clause
*/
public static String where(final List conditions) {
return where(conditions, null);
}
/**
* This function is to generate "WHERE" clause for all the sql sentence.
* When you want to select or update or delete the records with some "WHERE" conditions,
* you can follow the code as below.
*
* List<Condition> conds = new ArrayList<>();
* conds.add(Conditions.eq(name));
* conds.add(Conditions.gt(date));
* List<String> queries = new ArrayList<>();
* queries.add("OR state=:state");
* String clause = Criteria.where(conds, queries);
* //clause will be "WHERE name = :name AND date > :date OR state=:state"
*
* Then you can use spring-jdbc to handle the datebase.
* @param conditions conditions for where clause
* @param queries plain where clause
* @return WHERE clause
*/
public static String where(final List conditions, List queries) {
String result = "";
List list = new ArrayList<>();
if (conditions != null && !conditions.isEmpty()) {
if (!Util.checkConditions(conditions)) {
throw new IllegalArgumentException("conditions illegal");
}
for (Condition condition : conditions) {
list.add(makeConditionSql(condition));
}
}
if (queries != null && !queries.isEmpty()) {
for (String query : queries) {
if (query != null && !query.isEmpty()) {
list.add(query);
}
}
}
if (!list.isEmpty()) {
result = String.format("WHERE %s", Util.join(list, " AND "));
}
return result;
}
/**
* This function is to generate "ORDER BY" clause for select sql sentence.
* you can follow the code as below to use this function.
*
* List<Pair<String,Order>> orders = new ArrayList<>();
* fields.add(Pair.of("name", Order.ASC));
* fields.add(Pair.of("date", Order.DESC));
* String clause = Criteria.orderBy(orders);
* //clause will be "ORDER BY name ASC,date DESC"
*
* Then you can use spring-jdbc to handle the datebase.
*
* @param orders fields for ordering
* @return generated parted sql with order
* @since 0.3
*/
public static String orderBy(List> orders) {
String result = "";
if (orders != null && !orders.isEmpty()) {
List kvs = new ArrayList<>();
for (Pair order : orders) {
if (order.getV1() != null && !order.getV1().isEmpty()) {
kvs.add(String.format("%s %s", order.getV1(), order.getV2().getName()));
}
}
result = String.format("ORDER BY %s", Util.join(kvs));
}
return result;
}
private static String makeConditionSql(final Condition condition) {
StringBuilder sb = new StringBuilder();
if (condition != null && condition.getField() != null && condition.getOp() != null) {
switch (condition.getOp()) {
case EQ:
case NE:
case NOT_EQ:
case GT:
case GE:
case LT:
case LE:
case LIKE:
case NOT_LIKE:
if (condition.getValue() != null) {
sb.append(String.format("%s %s %s", condition.getField(),
condition.getOp().getName(),
condition.getValue()));
}
break;
case BETWEEN:
case NOT_BETWEEN:
if (condition.getValue() != null) {
@SuppressWarnings("unchecked")
Pair pair = (Pair) condition.getValue();
sb.append(String.format("%s %s %s AND %s",
condition.getField(), condition.getOp().getName(), pair.getV1(), pair.getV2()));
}
break;
case IS_NULL:
case IS_NOT_NULL:
sb.append(String.format("%s %s", condition.getField(), condition.getOp().getName()));
break;
case IN:
case NOT_IN:
if (condition.getValue() != null) {
sb.append(String.format("%s %s (%s)",
condition.getField(), condition.getOp().getName(), condition.getValue()));
}
break;
}
}
return sb.toString();
}
}