com.wizarius.orm.database.actions.WizDBSelect Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of wizarius-orm Show documentation
Show all versions of wizarius-orm Show documentation
Java orm for Postgres or Mysql with migration system and connection pool
The newest version!
package com.wizarius.orm.database.actions;
import com.wizarius.orm.database.DBException;
import com.wizarius.orm.database.connection.DBConnection;
import com.wizarius.orm.database.connection.DBConnectionPool;
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.FieldFinderResult;
import com.wizarius.orm.database.entityreader.DBJoinField;
import com.wizarius.orm.database.entityreader.DBParsedField;
import com.wizarius.orm.database.entityreader.DBParsedFieldsList;
import com.wizarius.orm.database.entityreader.DBSupportedTypes;
import com.wizarius.orm.database.handlers.ReadableHandler;
import lombok.extern.slf4j.Slf4j;
import java.io.IOException;
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;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
/**
* @author Vladyslav Shyshkin on 21.01.17.
*/
@Slf4j
@SuppressWarnings("unused")
public class WizDBSelect extends WizAbstractWhereAction> implements IDBSelect {
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 final Map aggregates = new HashMap<>();
public WizDBSelect(DBConnectionPool pool, DBParsedFieldsList fields) {
super(pool, fields);
}
/**
* Join table or not
*
* @param joinTables true if join
* @return current instance
*/
public WizDBSelect joinTables(JoinTypes joinTables) {
this.joinType = joinTables;
return this;
}
/**
* Add aggregate for database function
*
* @param type aggregate function
* @return current instance
*/
public WizDBSelect addAggregate(AggregateField type) {
aggregates.put(type.getDbFieldName(), type);
return this;
}
/**
* Set limit to sql query
*
* @param limit limit the display elements
* @param offset skip rows before beginning to return rows
* @return current instance
*/
public WizDBSelect setLimit(int limit, int offset) {
this.limit = limit;
this.offset = offset;
limitQuery = buildLimitQuery(this.limit, this.offset);
return this;
}
/**
* Set limit to sql query
*
* @param limit limit the display elements
* @param offset skip rows before beginning to return rows
* @return current instance
*/
public WizDBSelect setLimit(long limit, long offset) {
this.limit = limit;
this.offset = offset;
limitQuery = buildLimitQuery(this.limit, this.offset);
return this;
}
/**
* Set limit to squ query
*
* @param limit limit output
* @return current instance
*/
public WizDBSelect setLimit(long limit) {
offset = 0;
this.limit = limit;
limitQuery = buildLimitQuery(this.limit, offset);
return this;
}
/**
* Set limit to squ query
*
* @param limit limit output
* @return current instance
*/
public WizDBSelect setLimit(int limit) {
offset = 0;
this.limit = limit;
limitQuery = buildLimitQuery(this.limit, offset);
return this;
}
/**
* Order by field
*
* @param field field name
* @param type order type
* @return current instance
*/
public WizDBSelect orderBy(String field, DBOrderType type) throws DBException {
orderQuery = buildOrderQuery(fieldFinder.findDBField(field), type);
return this;
}
/**
* Order by field
*
* @param field field name
* @param type order type
* @return current instance
*/
public WizDBSelect orderBy(String field, DBOrderType type, Class clazz) throws DBException {
orderQuery = buildOrderQuery(fieldFinder.findDBField(field, clazz), type);
return this;
}
/**
* Order by field
*
* @param fields field names in database
* @return current instance
*/
public WizDBSelect groupBy(String... fields) throws DBException {
groupQuery = buildGroupByQuery(fields);
return this;
}
/**
* Build limit query
*
* @param limit limit value
* @param offset limit offset
* @return limit query
*/
protected String buildLimitQuery(long limit, long offset) {
return dialect.buildLimitQuery(limit, 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 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 {
PreparedStatement preparedStatement = null;
try (DBConnection connection = pool.getConnection()) {
String tableName = getTableName();
String query = "SELECT COUNT(1) as count FROM " +
tableName +
" as " +
fields.getUniqueIdentification() +
"_" +
tableName +
" " +
buildQueryClauses();
preparedStatement = connection.createPrepareStatement(query);
whereQueryBuilder.setupWhereValues(new AtomicInteger(1), preparedStatement);
ResultSet rs = preparedStatement.executeQuery();
if (rs.next()) {
return rs.getLong("count");
} else {
return 0;
}
} catch (SQLException e) {
throw new DBException("Unable to sql query = " + preparedStatement, e);
}
}
/**
* Execute select query
*
* @return list of entities
* @throws DBException on unable to execute select query
*/
public List execute() throws DBException {
try (DBConnection connection = pool.getConnection()) {
return execute(connection);
}
}
/**
* Execute custom select query and convert to entities list
*
* @param query query
* @return entities list
* @throws DBException on unable to execute custom query
*/
@Override
public List execute(String query) throws DBException {
try (DBConnection connection = pool.getConnection()) {
return execute(query, connection);
}
}
/**
* Execute custom select query and convert to entities list
* If connection is presented, it is assumed that the user himself wants to manage the connection
* The connection will not be automatically closed after the request
*
* @param query query
* @param connection connection
* @return entities list
* @throws DBException on unable to execute custom query
*/
@Override
public List execute(String query, DBConnection connection) throws DBException {
List result = new ArrayList<>();
try {
ResultSet rs = connection.executeSqlQuery(query);
while (rs.next()) {
Entity instance = initializeEntity(rs, fields, false);
result.add(instance);
}
return result;
} catch (SQLException e) {
throw new DBException("Unable to sql query = " + query, e);
} catch (IllegalAccessException e) {
throw new DBException("Unable to access to field", e);
} catch (InstantiationException e) {
throw new DBException("Unable to create new instance of " + fields.getClazz().getSimpleName(), e);
} catch (IOException e) {
throw new DBException("Unable to read data from result set", e);
}
}
/**
* Execute select query
* If connection is presented, it is assumed that the user himself wants to manage the connection
* The connection will not be automatically closed after the request
*
* @param connection connection to database
* @return list of entities
* @throws DBException on unable to execute select query
*/
@Override
public List execute(DBConnection connection) throws DBException {
List result = new ArrayList<>();
PreparedStatement preparedStatement = null;
try {
preparedStatement = toPreparedSQLQuery(connection);
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()) {
Entity instance;
if (joinType != JoinTypes.NONE) {
instance = initializeEntityWithJoin(rs, fields);
} else {
instance = initializeEntity(rs, fields, true);
}
result.add(instance);
}
return result;
} catch (SQLException e) {
throw new DBException("Unable to sql query = " + preparedStatement, e);
} catch (IllegalAccessException e) {
throw new DBException("Unable to access to field", e);
} catch (InstantiationException e) {
throw new DBException("Unable to create new instance of " + fields.getClazz().getSimpleName(), e);
} catch (IOException e) {
throw new DBException("Unable to read data from result set", e);
}
}
/**
* Execute select query to get one item
*
* @return entity
* @throws DBException on unable to execute select query
*/
@Override
public Entity getOne() throws DBException {
try (DBConnection connection = pool.getConnection()) {
return getOne(connection);
}
}
/**
* Execute select query
* If connection is presented, it is assumed that the user himself wants to manage the connection
* The connection will not be automatically closed after the request
*
* @param connection connection to database
* @return entity
* @throws DBException on unable to execute select query
*/
@Override
public Entity getOne(DBConnection connection) throws DBException {
//set limit to one
setLimit(1);
List result = execute(connection);
if (!result.isEmpty()) {
return result.get(0);
}
return null;
}
/**
* Returns sql query with all parameters and where conditions
*
* @return query for execution
* @throws DBException unable to build query
* @throws DBException on unable to execute select query
*/
@Override
public String toSQLQuery() throws DBException {
try (DBConnection connection = pool.getConnection()) {
return toPreparedSQLQuery(connection).toString();
}
}
/**
* Returned prepared sql query
*
* @param connection connection
* @return prepared statement
* @throws DBException on unable to build query
*/
private PreparedStatement toPreparedSQLQuery(DBConnection connection) throws DBException {
StringBuilder builder = new StringBuilder();
String tableName = getTableName();
if (joinType == JoinTypes.NONE) {
builder.append("SELECT ");
builder.append(buildSelectValue(fields));
//добавляем from от главной таблицы
builder.setLength(builder.length() - 3);
builder.append("\nFROM ")
.append(tableName)
.append(" as ")
.append(fields.getUniqueIdentification()).append("_").append(tableName)
.append("\n");
//build clauses
builder.append(buildQueryClauses());
} else {
if (customTableName != null) {
throw new DBException("Unable to use custom table name for join query");
}
builder.append("SELECT ");
//добавить перечисление филдов
buildJoinEnumeration(builder, fields);
//добавляем фром от главной таблицы
builder.setLength(builder.length() - 3);
builder.append("\nFROM ")
.append(tableName)
.append(" as ")
.append(fields.getUniqueIdentification()).append("_").append(tableName)
.append("\n");
//строим join фразу
buildJoin(builder, fields);
//build clauses
builder.append(buildQueryClauses());
}
try {
PreparedStatement prepareStatement = connection.createPrepareStatement(builder.toString());
whereQueryBuilder.setupWhereValues(new AtomicInteger(1), prepareStatement);
return prepareStatement;
} catch (SQLException e) {
throw new DBException("Unable to build sql query " + e.getMessage(), e);
}
}
/**
* Build join query
* Add to select query string link: SELECT tablename.fieldName, tablename.fieldName, ***
*
* @param sb parsed field string builder
*/
private void buildJoinEnumeration(StringBuilder sb, DBParsedFieldsList parsedFields) {
sb.append(buildSelectValue(parsedFields));
//проходимся по полям, и смотрим, если есть join, его нужно добавить в запрос
for (DBParsedField parsedField : parsedFields) {
if (parsedField.isJoinField()) {
buildJoinEnumeration(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 tableName = field.getJoinFields().getTableName();
String joinTableName = field.getJoinFields().getUniqueIdentification() + "_" + tableName;
String rootTableName = parsedFields.getUniqueIdentification() + "_" + parsedField.getTableName();
sb.append(joinType.toString())
.append(" JOIN ")
.append(tableName)
.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(whereQueryBuilder.getWhereQuery()).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 fields fields map
*/
private String buildSelectValue(DBParsedFieldsList fields) {
StringBuilder sb = new StringBuilder();
for (DBParsedField parsedField : fields) {
if (parsedField.isJoinField()) {
continue;
}
String tableName = fields.getTableName();
if (joinType == JoinTypes.NONE) {
tableName = getTableName();
}
AggregateField aggregateField = aggregates.get(parsedField.getDbFieldName());
// set null if table doesn't equals
// todo проверить в момент добавления агрегации
if (aggregateField != null && !aggregateField.getTableName().equals(tableName)) {
aggregateField = null;
}
if (aggregateField == null) {
sb.append(fields.getUniqueIdentification()).append("_").append(tableName)
.append(".")
.append(parsedField.getDbFieldName())
.append(" as ")
.append(fields.getUniqueIdentification())
.append("_")
.append(parsedField.getDbFieldName())
.append(", \n");
} else {
sb.append(aggregateField.getType().toString())
.append("(")
.append(fields.getUniqueIdentification()).append("_").append(tableName)
.append(".")
.append(parsedField.getDbFieldName())
.append(")")
.append(" as ")
.append(fields.getUniqueIdentification())
.append("_")
.append(parsedField.getDbFieldName())
.append(", \n");
}
}
return sb.toString();
}
/**
* Load entity from result set
*
* @param rs result set
* @param parsedFields parsed fields
* @throws DBException on invalid data format
* @throws SQLException on unable to read from result set
* @throws InstantiationException on unable to create instance
* @throws IllegalAccessException on unable to set data to field
*/
private Entity initializeEntityWithJoin(ResultSet rs, DBParsedFieldsList parsedFields) throws DBException, SQLException, IllegalAccessException, InstantiationException, IOException {
Entity instance = initializeEntity(rs, parsedFields, true);
for (DBParsedField parsedField : parsedFields) {
if (parsedField.isJoinField()) {
parsedField.getField().setAccessible(true);
parsedField.getField().set(
instance,
initializeEntityWithJoin(rs, parsedField.getJoinField().getJoinFields())
);
}
}
return instance;
}
/**
* Initialize entity
*
* @param rs database result set
* @param fields fields map
* @throws DBException on invalid data format
* @throws SQLException on unable to read from result set
* @throws InstantiationException on unable to create instance
* @throws IllegalAccessException on unable to set data to field
*/
@SuppressWarnings("unchecked")
private T initializeEntity(ResultSet rs, DBParsedFieldsList fields, boolean withUniqueIdentifier) throws DBException, SQLException, IllegalAccessException, InstantiationException, IOException {
Object instance = fields.getClazz().newInstance();
for (DBParsedField entry : fields) {
if (entry.isJoinField()) {
continue;
}
DBSupportedTypes fieldType = entry.getFieldType();
String dbFieldName = withUniqueIdentifier ? (fields.getUniqueIdentification() + "_" + entry.getDbFieldName()) : 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);
}
handler.set(field, instance, rs, dbFieldName, entry);
}
return (T) 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() + "_" + getTableName() + "." + result.getFindField().getDbFieldName();
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy