com.bixuebihui.db.ActiveRecord Maven / Gradle / Ivy
package com.bixuebihui.db;
import com.bixuebihui.DbException;
import com.bixuebihui.jdbc.BaseDao;
import com.bixuebihui.query.Paging;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;
import org.supercsv.cellprocessor.CellProcessorAdaptor;
import org.supercsv.cellprocessor.Optional;
import org.supercsv.cellprocessor.StrReplace;
import org.supercsv.cellprocessor.ift.CellProcessor;
import org.supercsv.cellprocessor.ift.StringCellProcessor;
import org.supercsv.io.CsvBeanWriter;
import org.supercsv.io.ICsvBeanWriter;
import org.supercsv.prefs.CsvPreference;
import org.supercsv.util.CsvContext;
import java.io.IOException;
import java.io.StringWriter;
import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import static java.util.Collections.emptySet;
/**
* Created with IntelliJ IDEA.
* User: Administrator
* Date: 13-4-18
* Time: 下午5:57
* To change this template use File | Settings | File Templates.
*
* @author xingwx
* @version $Id: $Id
*/
public interface ActiveRecord
extends Record,
Customizer,
StatisticRecord {
/**
* Constant ORDER_ASC=0
*/
int ORDER_ASC = 0;
/**
* Constant ORDER_DESC=1
*/
int ORDER_DESC = 1;
/**
* the fields that query will return
*
* @return a set of DB fields
*/
Set getResultFieldsSet();
/**
* in
* condition
*
* @param field a {@link java.lang.String} object.
* @param values an array of {@link java.lang.Object} objects.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord in(String field, Object[] values);
ActiveRecord in(String field, List values);
ActiveRecord in(String field, SqlString sqlString);
/**
* not in
* condition
*
* @param field a {@link java.lang.String} object.
* @param values an array of {@link java.lang.Object} objects.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord notIn(String field, Object[] values);
ActiveRecord notIn(String field, List values);
ActiveRecord notIn(String field, SqlString sqlString);
/**
* Call this method will reject empty string as where condition value
*
* @return this
*/
ActiveRecord emptyStringAsNullCondition();
/**
* By defautl, the rs.getInt(fieldName) return primitive type, so well turn null to zero,
* if this is not desired, then call keepNullForNumberField to return null using the trick below:
* ResultSet rs = new ResultSet();
*
* resultVO.setTaskId(rs.getLong("task_id"));
* if(rs.wasNull()){
* resultVO.setTaskId(null);
* }
*
* or
*
* ResultSet rs = statement.executeQuery("select Id from table");
* Integer id = 0;
* if (rs.getObject("Id") != null) {
* id = rs.getInt("Id");
* } else {
* id = null;
* }
* JDBC ResultSet 的getInt, getLong, getDouble, getFloat等方法，不论数据库中是否存储为null，都返回0，或者0.0。
* 注意：这个wasNull判断一定要紧跟在你想要判断非空的那个字段取动作之后，否则它判断的就不是你所期望的那个column值了。
*
* @return
*/
ActiveRecord keepNullForNumberField();
/**
* in.
*
* @param field a {@link java.lang.String} object.
* @param values a {@link java.lang.Object} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord in(String field, Object values);
/**
* for union primary key search
* @param fields primary key fields
* @param values primary key values
* @return this
*/
ActiveRecord in(Pair fields, List> values);
/**
* like.
*
* @param field a {@link java.lang.String} object.
* @param value a {@link java.lang.String} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord like(String field, String value);
/**
* startWith.
*
* @param field a {@link java.lang.String} object.
* @param value a {@link java.lang.String} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord startWith(String field, String value);
/**
* eq.
*
* @param field a {@link java.lang.String} object.
* @param value a {@link java.lang.Object} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord eq(String field, Object value);
/**
* between.
*
* @param field a {@link java.lang.String} object.
* @param leftValue a {@link java.lang.Object} object.
* @param rightValue a {@link java.lang.Object} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord between(String field, Object leftValue, Object rightValue);
/**
* notBetween.
*
* @param field a {@link java.lang.String} object.
* @param leftValue a {@link java.lang.Object} object.
* @param rightValue a {@link java.lang.Object} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord notBetween(String field, Object leftValue, Object rightValue);
/**
* ne.
*
* @param field a {@link java.lang.String} object.
* @param value a {@link java.lang.Object} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord ne(String field, Object value);
/**
* eq.
*
* @param fields an array of {@link java.lang.String} objects.
* @param value an array of {@link java.lang.Object} objects.
* @return a {@link ActiveRecord} object.
* @throws SQLException if any.
*/
ActiveRecord eq(String[] fields, Object[] value) throws SQLException;
/**
* greaterThan.
*
* @param field a {@link java.lang.String} object.
* @param value a {@link java.lang.Object} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord greaterThan(String field, Object value);
/**
* greater than, same as greaterThan
* @param field
* @param value
* @return
*/
ActiveRecord gt(String field, Object value);
/**
* smallerThan.
*
* @param field a {@link java.lang.String} object.
* @param value a {@link java.lang.Object} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord smallerThan(String field, Object value);
/**
* use for native function use such as sql("json_contains(field, ?)", value)
* or sql("json_contains_any(field, ?)", value),
* which value is a json array such as [1,2,3], or ["x","y","z"] etc.
* @param field may be a function expression
* @param value value as function parameter
* @return this
*/
ActiveRecord sql(String field, Object... value);
/**
* less than, same as smallerThan
* @param field
* @param value
* @return
*/
ActiveRecord lt(String field, Object value);
ActiveRecord isNull(String field);
ActiveRecord isNotNull(String field);
/**
* asc.
*
* @param field a {@link java.lang.String} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord asc(String field);
ActiveRecord and(SqlHelper andStack);
ActiveRecord not(SqlHelper notStack);
/**
* Sort by given order
* e.g. select FirstName from DemoTable where Id IN(4,5,6) order by field(Id,4,5,6);
* +----+-----------+
* | Id | FirstName |
* +----+-----------+
* | 1 | Chris |
* | 2 | Robert |
* | 3 | Mike |
* | 4 | Sam |
* | 5 | Carol |
* | 6 | David |
* +----+-----------+
* result
* +-----------+
* | FirstName |
* +-----------+
* | Sam |
* | Carol |
* | David |
* +-----------+
*
* @param field field name
* @param in fields value in IN clause
* @return this
*/
ActiveRecord orderByIn(String field, Object[] in);
/**
* desc.
*
* @param field a {@link java.lang.String} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord desc(String field);
/**
* limit.
*
* @param begin a int.
* @param num a int.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord limit(int begin, int num);
/**
* last.
*
* @return a {@link Record} object.
*/
Record last();
/**
* @param field db field
* @param value value to compare
* @return this
*/
ActiveRecord greaterOrEqualThan(String field, Object value);
/**
* greater than or equal, same as greaterOrEqualThan
* @param field
* @param value
* @return
*/
ActiveRecord ge(String field, Object value);
/**
* @param field db field
* @param value value to compare
* @return this
*/
ActiveRecord smallerOrEqualThan(String field, Object value);
/**
* less than or equal, same as smallerOrEqualThan
* @param field
* @param value
* @return
*/
ActiveRecord le(String field, Object value);
/**
* @param field db field
* @param value value to compare
*/
ActiveRecord wildcard(String field, String[] value);
/**
* getCondStack.
*
* @return a {@link SqlHelper} object.
*/
SqlHelper getCondStack();
/**
* or.
*
* @param andStack a {@link SqlHelper} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord or(SqlHelper andStack);
default ActiveRecord or(ActiveRecord orStack){
return or(orStack.getCondStack());
}
default ActiveRecord and(ActiveRecord andStack){
return and(andStack.getCondStack());
}
default ActiveRecord not(ActiveRecord notStack){
return not(notStack.getCondStack());
}
/**
* alias.
*
* @param shortName a {@link java.lang.String} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord alias(String shortName);
/**
* Override table name for partition or other porpoise, otherwise you can omit to call this
*
* @param partitionTableName
* @return
*/
ActiveRecord tableName(String partitionTableName);
/**
* return ResultSet fields
* @param fields table or view fields
* @return this
*/
ActiveRecord fields(String... fields);
/**
* join.
*
* @param joinClause a {@link java.lang.String} object.
* @return a {@link ActiveRecord} object.
*/
ActiveRecord join(String joinClause);
/**
* sql group by clause
*
* @param fields
* @return
*/
ActiveRecord groupBy(String... fields);
ActiveRecord having(HavingConditon fieldProcessor);
static Set toFieldsSet(String commaSeparatedFields) {
if (StringUtils.isBlank(commaSeparatedFields) || commaSeparatedFields.contains("*")) {
return emptySet();
}
return Stream.of(commaSeparatedFields.split(",")).map(x -> BaseDao.getFieldAlias(x.toLowerCase(Locale.ROOT))).collect(Collectors.toSet());
}
/**
* use 'search bean' value to query result:
* when search bean's property is primitive, this is equals `eq`,
* when property is String, this is like 'like',
* when property is Date/Time, this is between operation
*
* @param searchBean
* @param fields valid table/view fields
* @param
* @return
*/
default ActiveRecord query(Paging searchBean, Set fields) {
return searchBean.toActiveRecordParams(this, fields);
}
ActiveRecord query(Paging searchBean);
default String findAllToCsv(String titles) throws DbException {
return findAllToCsv(titles, null);
}
/**
* export to CSV format with Excel preferences
* @param titles i18n titles
* @param onlyFields field names of POJO type T
* @return csv string
* @throws DbException if any.
*/
default String findAllToCsv(String titles, String onlyFields) throws DbException {
return findAllToCsv(titles, onlyFields, false, false);
}
default String findAllToCsv(String titles, String onlyFields, boolean withBom, boolean forceStringForDateTime) throws DbException {
List list = findAll();
return findAllToCsv(list, titles, onlyFields, withBom, forceStringForDateTime, null);
}
default String findAllToCsv(String titles, String onlyFields, Map customFormatters) throws DbException {
List list = findAll();
return findAllToCsv(list, titles, onlyFields, true, true, customFormatters);
}
ActiveRecord coreSqlInterceptor(Function coreSqlInterceptor);
class MyCustomCellProcessor extends CellProcessorAdaptor {
// 匹配日期时间格式，如 2020-01-01 12:00:00.000 或 2020-01-01 12:00:00
// 或长数字 20200101120000001
static String regex = "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}(\\.\\d+)?$|^\\d{11,20}$";
static String replacement = "=\"$0\"";
public MyCustomCellProcessor() {
super(new Optional(new StrReplace(regex, replacement)));
}
public MyCustomCellProcessor(StringCellProcessor next) {
super(new Optional(new StrReplace(regex, replacement, next)));
}
@Override
public T execute(Object value, CsvContext context) {
return next.execute(value, context);
}
}
static String findAllToCsv(List list, String titles, String onlyFields) throws DbException {
return findAllToCsv(list, titles, onlyFields, false, false, null);
}
static String findAllToCsv(List list, String titles, String onlyFields, Map customFormatters) throws DbException {
return findAllToCsv(list, titles, onlyFields, false, false, customFormatters);
}
static String findAllToCsv(List list, String titles, String onlyFields,
boolean withBom, boolean forceStringForDateTime,
Map customFormatters) throws DbException {
if(list.isEmpty()) return "";
String[] fields = onlyFields!=null ? onlyFields.split(",")
: Arrays.stream(list.get(0).getClass().getDeclaredFields())
.map(Field::getName).filter(name -> !name.contains("$")).toArray(String[]::new);
StringWriter sb = new StringWriter(10_000);
// add bom header to StringBuilder
if(withBom) {
sb.write("\uFEFF");
}
ICsvBeanWriter beanWriter = new CsvBeanWriter(sb,
CsvPreference.EXCEL_PREFERENCE);
String[] header = titles.split(",");
try {
beanWriter.writeHeader(header);
if(forceStringForDateTime) {
CellProcessor[] processors = new CellProcessor[fields.length];
for (int i = 0; i < fields.length; i++) {
if(customFormatters!=null && customFormatters.containsKey(fields[i])) {
processors[i] = customFormatters.get(fields[i]);
}else {
processors[i] = new MyCustomCellProcessor();
}
}
for (T pojo : list) {
beanWriter.write(pojo, fields, processors);
}
}else{
CellProcessor[] processors = new CellProcessor[fields.length];
for (int i = 0; i < fields.length; i++) {
if(customFormatters!=null && customFormatters.containsKey(fields[i])) {
processors[i] = customFormatters.get(fields[i]);
}
}
for (T pojo : list) {
beanWriter.write(pojo, fields, processors);
}
}
beanWriter.close();
} catch (IOException e) {
throw new DbException(e);
}
return sb.toString();
}
}