com.wizarius.orm.database.actions.AbstractDBSelect Maven / Gradle / Ivy
Show all versions of wizarius-orm Show documentation
package com.wizarius.orm.database.actions;
import com.wizarius.orm.database.*;
import com.wizarius.orm.database.actions.common.AbstractAction;
import com.wizarius.orm.database.actions.common.AbstractWhereAction;
import com.wizarius.orm.database.data.AggregateField;
import com.wizarius.orm.database.data.DBOrderType;
import com.wizarius.orm.database.data.JoinTypes;
import com.wizarius.orm.database.data.fieldfinder.FieldFinder;
import com.wizarius.orm.database.data.fieldfinder.FieldFinderResult;
import com.wizarius.orm.database.data.fieldfinder.FieldFinderResultList;
import com.wizarius.orm.database.exceptions.DBException;
import com.wizarius.orm.database.handlers.ReadableHandler;
import com.wizarius.orm.database.interfaces.DBEntity;
import lombok.extern.slf4j.Slf4j;
import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
/**
* @author Vladyslav Shyshkin on 21.01.17.
*
* TODO отказаться от DBEntity и возвращать generic
*/
@Slf4j
public abstract class AbstractDBSelect extends AbstractWhereAction {
private final FieldFinder fieldFinder;
private JoinTypes joinType = JoinTypes.NONE;
//limit variables
private String limitQuery;
private long limit;
private long offset;
//order by
private String orderQuery;
private String groupQuery;
//field argument
private HashMap aggregates = new HashMap<>();
/**
* Database select constructor
*
* @param fieldsMap fields map
* @param db database connection pool
*/
public AbstractDBSelect(DBParsedFieldsList fieldsMap, DBConnectionPool db) {
super(db, fieldsMap);
this.fieldFinder = new FieldFinder(fieldsMap);
}
/**
* Join table or not
*
* @param joinTables true if join
* @return currentDBFieldName instance
*/
public T joinTables(JoinTypes joinTables) {
this.joinType = joinTables;
return getInstance();
}
/**
* Add aggregate for database function
*
* @param type aggregate function
* @return currentDBFieldName instance
*/
public T addAggregate(AggregateField type) {
aggregates.put(type.getDbFieldName(), type);
return getInstance();
}
/**
* Set limit to sql query
*
* @param limit limit the display elements
* @param offset skip rows before beginning to return rows
* @return currentDBFieldName instance
*/
public T setLimit(int limit, int offset) {
this.limit = limit;
this.offset = offset;
limitQuery = buildLimitQuery(this.limit, this.offset);
return getInstance();
}
/**
* Set limit to sql query
*
* @param limit limit the display elements
* @param offset skip rows before beginning to return rows
* @return currentDBFieldName instance
*/
public T setLimit(long limit, long offset) {
this.limit = limit;
this.offset = offset;
limitQuery = buildLimitQuery(this.limit, this.offset);
return getInstance();
}
/**
* Set limit to squ query
*
* @param limit limit output
* @return currentDBFieldName instance
*/
public T setLimit(long limit) {
offset = 0;
this.limit = limit;
limitQuery = buildLimitQuery(this.limit, offset);
return getInstance();
}
/**
* Set limit to squ query
*
* @param limit limit output
* @return currentDBFieldName instance
*/
public T setLimit(int limit) {
offset = 0;
this.limit = limit;
limitQuery = buildLimitQuery(this.limit, offset);
return getInstance();
}
/**
* Order by field
*
* @param field field name
* @param type order type
* @return currentDBFieldName instance
*/
public T orderBy(String field, DBOrderType type) throws DBException {
orderQuery = buildOrderQuery(fieldFinder.findDBField(field), type);
return getInstance();
}
/**
* Order by field
*
* @param field field name
* @param type order type
* @return currentDBFieldName instance
*/
public T orderBy(String field, DBOrderType type, Class clazz) throws DBException {
orderQuery = buildOrderQuery(fieldFinder.findDBField(field, clazz), type);
return getInstance();
}
/**
* Order by field
*
* @param fields field names in database
* @return currentDBFieldName instance
*/
public T groupBy(String... fields) throws DBException {
groupQuery = buildGroupByQuery(fields);
return getInstance();
}
/**
* Build limit query
*
* @param limit limit value
* @param offset limit offset
* @return limit query
*/
protected abstract String buildLimitQuery(long limit, long offset);
/**
* Build order query
*
* @param field field name
* @param type order type
* @return order string
*/
public String buildOrderQuery(FieldFinderResult field, DBOrderType type) {
return "ORDER BY " + field.getList().getUniqueIdentification() + "_" + field.getFindField().getDbFieldName() + " " + type.toString();
}
/**
* Build group by query
*
* @param fields fields to group
* @return group by query
*/
public String buildGroupByQuery(String... fields) throws DBException {
StringBuilder sb = new StringBuilder();
sb.append("GROUP BY ");
for (String field : fields) {
FieldFinderResult dbFindField = fieldFinder.findDBField(field);
sb.append(dbFindField.getList().getUniqueIdentification()).append("_").append(field).append(",");
}
sb.setLength(sb.length() - 1);
return sb.toString();
}
/**
* Get field name
*
* @param key key name
* @return field name in select query
* @throws DBException on fiend exception
*/
public String getFieldName(String key) throws DBException {
return getFieldName(key, null);
}
/**
* Get field names
*
* @param key field key
* @return list of alias names
* @throws DBException on database exception
*/
public String[] getFieldNames(String key) throws DBException {
FieldFinderResultList allFields = fieldFinder.findAllFields(key);
String[] fields = new String[allFields.size()];
int i = 0;
for (FieldFinderResult field : allFields) {
fields[i++] = getFullSelectName(field);
}
return fields;
}
/**
* Get field names
*
* @param key field key
* @return list of alias names
* @throws DBException on database exception
*/
public String[] getFieldNames(String key, Class clazz) throws DBException {
FieldFinderResultList allFields = fieldFinder.findAllFields(key, clazz);
String[] fields = new String[allFields.size()];
int i = 0;
for (FieldFinderResult field : allFields) {
fields[i++] = getFullSelectName(field);
}
return fields;
}
/**
* Get field name
*
* @param key key name
* @param clazz java class
* @return field name in select query
* @throws DBException on unable to get field by name
*/
public String getFieldName(String key, Class clazz) throws DBException {
FieldFinderResult dbField;
if (clazz == null) {
dbField = fieldFinder.findDBField(key);
} else {
dbField = fieldFinder.findDBField(key, clazz);
}
return getFullSelectName(dbField);
}
/**
* Get count from table
*
* @return count of elements in table
* @throws DBException on unable to get count
*/
public long getCount() throws DBException {
String query = null;
try (AbstractConnection connection = pool.getConnection()) {
query = "SELECT COUNT(1) as count FROM " +
fieldsMap.getTableName() +
" as " +
fieldsMap.getUniqueIdentification() +
"_" +
fieldsMap.getTableName() +
" " +
buildQueryClauses();
PreparedStatement prepareStatement = connection.createPrepareStatement(query);
wherePrepareStatementQueryBuilder.setupWhereValues(prepareStatement);
ResultSet rs = prepareStatement.executeQuery();
if (rs.next()) {
return rs.getLong("count");
} else {
return 0;
}
} catch (SQLException e) {
throw new DBException("Unable to execute SQL: " + query + " " + e.getMessage(), e);
}
}
/**
* Execute sql script
*
* @return arraylist of DBEntity elements
* @throws DBException on unable to execute query
*/
public List execute() throws DBException {
List list = new ArrayList<>();
String query = null;
try (AbstractConnection session = pool.getConnection()) {
//copy enum to new variable
if (joinType != JoinTypes.NONE) {
query = toJoinSQLQuery();
} else {
query = toSQLQuery();
}
PreparedStatement prepareStatement = session.createPrepareStatement(query);
wherePrepareStatementQueryBuilder.setupWhereValues(prepareStatement);
ResultSet rs = prepareStatement.executeQuery();
// read data
while (rs.next()) {
DBEntity instance;
if (joinType != JoinTypes.NONE) {
instance = loadJoinValueFromResultSet(rs, fieldsMap);
} else {
instance = initializeEntity(rs, fieldsMap);
}
list.add(instance);
}
} catch (Exception e) {
throw new DBException("Unable to execute SQL: " + query + " ", e);
}
return list;
}
/**
* Get one record in database
*
* @return entity of object
* @throws DBException on unable to get one query
*/
public DBEntity getOne() throws DBException {
//set limit to one
setLimit(1);
//build query
String query = null;
try (AbstractConnection session = pool.getConnection()) {
if (joinType != JoinTypes.NONE) {
query = toJoinSQLQuery();
} else {
query = toSQLQuery();
}
PreparedStatement prepareStatement = session.createPrepareStatement(query);
wherePrepareStatementQueryBuilder.setupWhereValues(prepareStatement);
ResultSet rs = prepareStatement.executeQuery();
DBEntity instance = null;
while (rs.next()) {
if (joinType != JoinTypes.NONE) {
instance = loadJoinValueFromResultSet(rs, fieldsMap);
} else {
instance = initializeEntity(rs, fieldsMap);
}
}
return instance;
} catch (Exception e) {
throw new DBException("Unable to execute SQL: " + query + " ", e);
}
}
/**
* Generate SQL query
*
* @return sql query string
*/
private String toSQLQuery() {
StringBuilder builder = new StringBuilder();
builder.append("SELECT ");
builder.append(buildSelectValue(fieldsMap));
//добавляем фром от главной таблицы
builder.setLength(builder.length() - 3);
builder.append("\nFROM ")
.append(fieldsMap.getTableName())
.append(" as ")
.append(fieldsMap.getUniqueIdentification()).append("_").append(fieldsMap.getTableName())
.append("\n");
//build clauses
builder.append(buildQueryClauses());
return builder.toString();
}
/**
* TO join sql query
*
* @return join sql query
*/
private String toJoinSQLQuery() {
StringBuilder builder = new StringBuilder();
builder.append("SELECT ");
//добавить перечисление филдов
buildEnumeration(builder, fieldsMap);
//добавляем фром от главной таблицы
builder.setLength(builder.length() - 3);
builder.append("\nFROM ")
.append(fieldsMap.getTableName())
.append(" as ")
.append(fieldsMap.getUniqueIdentification()).append("_").append(fieldsMap.getTableName())
.append("\n");
//строим join фразу
buildJoin(builder, fieldsMap);
//build clauses
builder.append(buildQueryClauses());
return builder.toString();
}
/**
* Build join query
*
* @param sb parsed field string builder
* @return build join query
*/
private void buildEnumeration(StringBuilder sb, DBParsedFieldsList parsedFields) {
sb.append(buildSelectValue(parsedFields));
//проходимся по полям, и смотрим, если есть join, его нужно добавить в запрос
for (DBParsedField parsedField : parsedFields) {
if (parsedField.isJoinField()) {
buildEnumeration(sb, parsedField.getJoinField().getJoinFields());
}
}
}
/**
* Build join on query
*/
private void buildJoin(StringBuilder sb, DBParsedFieldsList parsedFields) {
for (DBParsedField parsedField : parsedFields) {
if (parsedField.isJoinField()) {
DBJoinField field = parsedField.getJoinField();
String joinTableName = field.getJoinFields().getUniqueIdentification() + "_" + field.getJoinFields().getTableName();
String rootTableName = parsedFields.getUniqueIdentification() + "_" + parsedField.getTableName();
sb.append(joinType.toString())
.append(" JOIN ")
.append(field.getJoinFields().getTableName())
.append(" as ")
.append(joinTableName)
.append(" on ")
.append(joinTableName)
.append(".")
.append(field.getInsideClassDBField())
.append(" = ")
.append(rootTableName)
.append(".")
.append(field.getCurrentClassDBField())
.append("\n");
buildJoin(sb, field.getJoinFields());
}
}
}
/**
* Build query clauses
*
* @return where cause with order and limit
*/
private String buildQueryClauses() {
StringBuilder builder = new StringBuilder();
//add where clause
builder.append(wherePrepareStatementQueryBuilder.buildWhereClause()).append("\n");
//add group clause
if (groupQuery != null) {
builder.append(groupQuery).append("\n");
}
//add order clause
if (orderQuery != null) {
builder.append(orderQuery).append("\n");
}
//add limit clause
if (limitQuery != null) {
builder.append(limitQuery).append("\n");
}
return builder.toString();
}
/**
* Build select field name query
*
* @param fieldsMap fields map
*/
private String buildSelectValue(DBParsedFieldsList fieldsMap) {
StringBuilder sb = new StringBuilder();
for (DBParsedField parsedField : fieldsMap) {
if (parsedField.isJoinField()) {
continue;
}
AggregateField aggregateField = aggregates.get(parsedField.getDbFieldName());
// set null if table doesn't equals
if (aggregateField != null &&
!aggregateField.getTableName().equals(fieldsMap.getTableName())) {
aggregateField = null;
}
if (aggregateField == null) {
sb.append(fieldsMap.getUniqueIdentification()).append("_").append(fieldsMap.getTableName())
.append(".")
.append(parsedField.getDbFieldName())
.append(" as ")
.append(fieldsMap.getUniqueIdentification())
.append("_")
.append(parsedField.getDbFieldName())
.append(", \n");
} else {
sb.append(aggregateField.getType().toString())
.append("(")
.append(fieldsMap.getUniqueIdentification()).append("_").append(fieldsMap.getTableName())
.append(".")
.append(parsedField.getDbFieldName())
.append(")")
.append(" as ")
.append(fieldsMap.getUniqueIdentification())
.append("_")
.append(parsedField.getDbFieldName())
.append(", \n");
}
}
return sb.toString();
}
/**
* Load entity from result set
*
* @param rs result set
* @param parsedFields parsed fields
* @throws Exception on unable to load join value from result set
*/
private DBEntity loadJoinValueFromResultSet(ResultSet rs, DBParsedFieldsList parsedFields) throws Exception {
DBEntity instance = initializeEntity(rs, parsedFields);
for (DBParsedField parsedField : parsedFields) {
if (parsedField.isJoinField()) {
parsedField.getField().setAccessible(true);
parsedField.getField().set(
instance,
loadJoinValueFromResultSet(rs, parsedField.getJoinField().getJoinFields())
);
}
}
return instance;
}
/**
* Initialize entity
*
* @param rs database result set
* @param fieldsMap fields map
* @throws Exception on unable to initialize entity
*/
private DBEntity initializeEntity(ResultSet rs, DBParsedFieldsList fieldsMap) throws Exception {
DBEntity instance = fieldsMap.getClazz().newInstance();
for (DBParsedField entry : fieldsMap) {
if (entry.isJoinField()) {
continue;
}
DBSupportedTypes fieldType = entry.getFieldType();
String dbFieldName = fieldsMap.getUniqueIdentification() + "_" + entry.getDbFieldName();
Field field = entry.getField();
field.setAccessible(true);
ReadableHandler handler = readableHandlers.get(fieldType);
if (handler == null) {
throw new DBException("Handler for type " + fieldType + " not found " + entry.toString());
}
handler.set(field, instance, rs, dbFieldName, entry);
}
return instance;
}
/**
* Get database select field name
*
* @param result field finder result
* @return alias name like A_users.id_user
* where
* A - unique identifier
* users - table name
* id_user - name of field
*/
private String getFullSelectName(FieldFinderResult result) {
return result.getList().getUniqueIdentification() + "_" + result.getList().getTableName() + "." + result.getFindField().getDbFieldName();
}
}