Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
org.mentabean.jdbc.QueryBuilder Maven / Gradle / Ivy
package org.mentabean.jdbc;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.mentabean.BeanConfig;
import org.mentabean.BeanException;
import org.mentabean.DBField;
import org.mentabean.sql.Condition;
import org.mentabean.sql.Function;
import org.mentabean.sql.HasParams;
import org.mentabean.sql.Sentence;
import org.mentabean.sql.param.Param;
import org.mentabean.sql.param.ParamField;
import org.mentabean.sql.param.ParamFunction;
import org.mentabean.sql.param.ParamValue;
import org.mentabean.util.PropertiesProxy;
import org.mentabean.util.SQLUtils;
/**
* Fluent QueryBuilder useful to create SQL queries
*
* @author margel
* @author erico
*
*/
public class QueryBuilder {
private StringBuilder sb = new StringBuilder();
private final AnsiSQLBeanSession session;
private List paramValues = new ArrayList();
private List> aliases = new ArrayList>();
private Map sentences = new HashMap();
private Alias aliasFrom;
private int parenthesis = 0;
private boolean clauseIf;
protected QueryBuilder(final AnsiSQLBeanSession session) {
this.session = session;
}
/**
* Builds an initial SELECT statement with given aliases
* @param as - Alias(es) with properties that will be retrieved
* @return A new Select object
*/
public Select select(Alias... as){
return new Select(as);
}
/**
* Builds an initial SELECT alias FROM alias statement.select(alias).from(alias)
* @param as - Alias with properties that will be retrieved
* @return A new From object
* @see #select(Alias...)
* @see Select#from(Alias)
*/
public From selectFrom(Alias as){
return select(as).from(as);
}
/**
* Builds an initial SELECT statement with given sentences
* @param sentences - Sentence(s) to insert in SELECT clause
* @return A new Select object
*/
public Select select(Sentence... sentences){
return new Select(sentences);
}
/**
* Creates a new QueryBuilder with the same session. It's useful to build sub-queries from the main query
* @return The new QueryBuilder instance
*/
public QueryBuilder subQuery() {
return new QueryBuilder(session);
}
/**
* Creates an alias to be used in this QueryBuilder
*
* @param clazz - Bean class that will be mapped to a BeanConfig
* @param alias - String indicating the alia's name
* @return - The alias object
*/
public Alias aliasTo(Class clazz, String alias){
return new Alias(clazz, alias);
}
/**
* Creates an alias to be used in this QueryBuilder. The alia's name is the simple name of the bean class
*
* @param clazz - Bean class that will be mapped to a BeanConfig
* @return - The alias object
*/
public Alias aliasTo(Class clazz){
return new Alias(clazz, clazz.getSimpleName().toLowerCase());
}
private void appendTable(Alias a) {
sb.append(a.config.getTableName()).append(' ').append(a.aliasStr);
}
private void append(Param param) {
if (param != null)
sb.append(param.paramInQuery());
add(param);
}
private void add(HasParams hasParams) {
Param[] params = hasParams.getParams();
if (params != null) {
for (Param param : params) {
add(param);
}
}
}
private void applyRegex() {
remove("(AND|OR|\\([\\s]*?\\)|WHERE|HAVING)[\\s]*?$");
}
private void remove(String regex) {
sb = new StringBuilder(sb.toString().replaceAll(regex, ""));
}
private void addAnd() {
if (clauseIf) {
applyRegex();
sb.append(" AND ");
}
}
private void addOr() {
if (clauseIf) {
applyRegex();
sb.append(" OR ");
}
}
private void add(Param param) {
if (param != null && param.values() != null && param.values().length > 0) {
for (Object value : param.values()) {
if (value != null) {
paramValues.add(value);
}
}
}
}
private void openPar() {
parenthesis++;
sb.append('(');
}
private void closePar() {
applyRegex();
parenthesis--;
sb.append(')');
applyRegex();
}
public class Alias {
private BeanConfig config;
private String aliasStr;
private T pxy;
private String[] returns;
private String[] returnMinus;
private Map joined = new HashMap();
private Alias(Class clazz, String aliasStr) {
this.aliasStr = aliasStr;
this.config = session.getConfigFor(clazz);
if (this.config == null)
throw new BeanException("BeanConfig not found for "+clazz);
}
/**
* Defines the properties that will return. In other words, only these properties will be populated
* @param returns
*/
public void setReturns(Object... returns){
this.returns = AnsiSQLBeanSession.getProperties(returns);
}
/**
* Defines the properties that will NOT return. In other words, these properties will be not populated
* @param returns
*/
public void setReturnMinus(Object... returns){
this.returnMinus = AnsiSQLBeanSession.getProperties(returns);
}
/**
* Returns a proxy for bean class
* @return The proxy
*/
@SuppressWarnings("unchecked")
public T pxy(){
if (pxy == null) {
this.pxy = (T) PropertiesProxy.create(config.getBeanClass());
}
return pxy;
}
/**
* Convert the given property to database column
* @param property - The bean property (can be through proxy)
* @return The database column name
*/
public String toColumn(Object property){
return session.propertyToColumn(config.getBeanClass(), property, aliasStr);
}
/**
* Populates the bean according to ResultSet
* @param rs
* @param bean
*/
public void populateBean(ResultSet rs, T bean){
session.populateBeanImpl(rs, bean, aliasStr, returns, returnMinus, false);
}
public void populateAll(ResultSet rs, T bean) {
populateBean(rs, bean);
for (Map.Entry m : joined.entrySet()) {
//only if alias is in SELECT clause
if (aliases.contains(m.getValue())) {
Object value = session.getPropertyBean(bean, m.getKey().property, m.getKey().forceInstance);
if (value != null) {
m.getValue().populateAll(rs, value);
}
}
}
}
@Override
public String toString() {
return "Alias "+aliasStr+" of "+config.getBeanClass();
}
/**
* Configures a property name to receive data from alias. When forceIntance is true
* it will force the creation of a new instance for the property, in other words,
* the value will never be null
* @param property - Bean property to populate
* @param forceInstance
* @param alias - Alias
*/
private void put(Object property, boolean forceInstance, Alias alias) {
joined.put(new Key().property(property).forceInstance(forceInstance), alias);
}
private class Key {
private String property;
private boolean forceInstance;
public Key property(Object property) {
this.property = AnsiSQLBeanSession.getProperties(new Object[] {property})[0];
return this;
}
public Key forceInstance(boolean force) {
this.forceInstance = force;
return this;
}
}
}
public class Select implements Appendable {
private Select(Alias... as) {
sb.append("SELECT ");
this.add(as);
}
private Select(Sentence... sentences) {
sb.append("SELECT ");
this.add(sentences);
}
/**
* Add the sentences in SELECT clause
* @param sentences
* @return this
*/
public Select add(Sentence... sentences) {
for (Sentence s : sentences) {
if (s.getName() == null || s.getName().isEmpty())
throw new BeanException("The sentence ("+s.build()+") in SELECT clause must have a name");
if(!sb.toString().equals("SELECT "))
sb.append(",");
sb.append(s.build()).append(' ').append(s.getName());
QueryBuilder.this.sentences.put(s.getName(), s);
QueryBuilder.this.add(s);
}
return this;
}
/**
* Add the alias columns in SELECT clause
* @param as
* @return this
*/
public Select add(Alias... as) {
for (Alias alias: as) {
if(!sb.toString().equals("SELECT "))
sb.append(",");
aliases.add(alias);
sb.append(session.buildSelectImpl(alias.config.getBeanClass(), alias.aliasStr, alias.returns, alias.returnMinus, false, true));
}
return this;
}
/**
* Creates the FROM keyword for given alias appending the table name in SQL query.
* @param alias
* @return A new From object
*/
public From from(Alias alias){
return new From(alias);
}
@Override
public Select append(Param p) {
QueryBuilder.this.append(p);
return this;
}
}
public class From extends Query implements CanOrder, CanLimit, CanGroupBy, Appendable {
private From(Alias alias) {
aliasFrom = alias;
sb.append(" FROM ").append(alias.config.getTableName()).append(" ").append(alias.aliasStr);
}
private From() {}
/**
* Creates a LEFT JOIN sentence using the given alias
* E.g.:
*
*
* builder.select(...)
* .from(a)
*
*
*
* @param a - The alias to join
* @return a new {@link On} object
*/
public On leftJoin(Alias a){
return join(a, "LEFT JOIN");
}
/**
* Creates a RIGHT JOIN sentence using the given alias
* E.g.:
*
*
* builder.select(...)
* .from(a)
*
*
*
* @param a - The alias to join
* @return a new {@link On} object
*/
public On rightJoin(Alias a){
return join(a, "RIGHT JOIN");
}
/**
* Creates a JOIN sentence using the given alias
* E.g.:
*
*
* builder.select(...)
* .from(a)
*
*
*
* @param a - The alias to join
* @return a new {@link On} object
*/
public On join(Alias a){
return join(a, "JOIN");
}
/**
* Creates a join using the given join type
* @param a - Alias to join
* @param joinType - The join type (E.g: "CROSS JOIN" or "LEFT OUTER JOIN")
* @return a new {@link On} object
*/
public On join(Alias a, String joinType) {
sb.append(" "+joinType+" ");
appendTable(a);
return new On(a, true);
}
public Where where() {
return new Where(true);
}
@Override
public Order orderBy() {
return new Order();
}
@Override
public Limit limit(Param lim) {
return new Limit(lim);
}
@Override
public Limit limit(Number lim) {
return new Limit(lim);
}
@Override
public From append(Param p) {
QueryBuilder.this.append(p);
return this;
}
@Override
public GroupBy groupBy(Alias... aliases) {
return new GroupBy(aliases);
}
@Override
public GroupBy groupByProp(Alias alias, Object... properties) {
return new GroupBy(alias, properties);
}
@Override
public GroupBy groupBy() {
return new GroupBy();
}
}
/**
* Class representing a 'pos-join' operation
* @author erico
*
*/
public class On {
private Alias aliasPK;
private On(Alias aliasPK, boolean init) {
sb.append(init ? " ON " : " AND ");
this.aliasPK = aliasPK;
}
public OnEquals on(Object property) {
sb.append(aliasPK.toColumn(property));
return new OnEquals(aliasPK);
}
public UsingPK pkOf(Alias alias) {
return new UsingPK(alias);
}
}
public class UsingPK {
private Alias aliasPK;
private UsingPK(Alias aliasPK) {
this.aliasPK = aliasPK;
}
public PopulateUsing in(Alias aliasFK) {
buildOn(aliasPK, aliasFK);
return new PopulateUsing(aliasPK, aliasFK);
}
private void buildOn(Alias aPK, Alias aFK){
Iterator pks = aPK.config.pks();
DBField df = pks.next();
sb.append(aPK.aliasStr).append(".").append(df.getDbName()).append(" = ")
.append(aFK.aliasStr).append(".").append(df.getDbName());
while (pks.hasNext()){
df = pks.next();
sb.append(" AND ").append(aPK.aliasStr).append(".").append(df.getDbName())
.append(" = ").append(aFK.aliasStr).append(".").append(df.getDbName());
}
}
}
public class PopulateUsing extends From {
private Alias aliasPK, aliasFK;
public PopulateUsing(Alias aliasPK, Alias aliasFK) {
this.aliasFK = aliasFK;
this.aliasPK = aliasPK;
}
/**
* Defines the property of foreign bean (specified on {@link UsingPK#in(Alias)} method)
* that will receive the value from alias PK (specified on {@link On#pkOf(Alias)} method) and
* force the creation of a new instance if bean property is not set.
* E.g.:
*
*
* builder.select(user, city)
* .from(user).inPropertyForcingInstance(user.pxy().getCity())
*
*
* @param propertyBean
* @return this
* @see Alias#put(Object, boolean, Alias)
*/
public From inPropertyForcingInstance(Object propertyBean) {
aliasFK.put(propertyBean, true, aliasPK);
return this;
}
/**
* Defines the property of foreign bean (specified on {@link UsingPK#in(Alias)} method)
* that will receive the value from alias PK (specified on {@link On#pkOf(Alias)} method)
* E.g.:
*
*
* builder.select(user, city)
* .from(user).inProperty(user.pxy().getCity())
*
*
* @param propertyBean
* @return this
* @see Alias#put(Object, boolean, Alias)
*/
public From inProperty(Object propertyBean) {
aliasFK.put(propertyBean, false, aliasPK);
return this;
}
/**
* Defines the property of primary bean (specified on {@link On#pkOf(Alias)} method)
* that will receive the value from foreign alias (aliasFK) (specified on {@link UsingPK#in(Alias)} method) and
* force the creation of a new instance if bean property is not set.
* Note: The pkProperty is generally used in 1x1 relationship.
* E.g.:
*
*
* builder.select(person, identity)
* .from(person).pkPropertyForcingInstance(person.pxy().getIdentity())
*
*
* @param propertyBean
* @return this
* @see Alias#put(Object, boolean, Alias)
*/
public From pkPropertyForcingInstance(Object propertyBean) {
aliasPK.put(propertyBean, true, aliasFK);
return this;
}
/**
* Defines the property of primary bean (specified on {@link On#pkOf(Alias)} method)
* that will receive the value from foreign alias (aliasFK) (specified on {@link UsingPK#in(Alias)} method)
* Note: The pkProperty is generally used in 1x1 relationship.
* E.g.:
*
*
* builder.select(person, identity)
* .from(person).pkProperty(person.pxy().getIdentity())
*
*
* @param propertyBean
* @return this
* @see Alias#put(Object, boolean, Alias)
*/
public From pkProperty(Object propertyBean) {
aliasPK.put(propertyBean, false, aliasFK);
return this;
}
}
public class OnEquals {
private Alias aliasPK;
private OnEquals(Alias aliasPK) {
sb.append('=');
this.aliasPK = aliasPK;
}
/**
* Equals the given property of alias to the property defined in on method
* E.g.:
*
*
* builder.select(a, b)
* .from(a).eq(a, a.pxy().getSomething())
*
*
* @param alias
* @param property
* @return A new Equals object
*/
public Equals eq(Alias alias, Object property) {
sb.append(alias.toColumn(property));
return new Equals(this.aliasPK, alias);
}
}
public class Equals extends From {
private Alias aliasFK, aliasPK;
private Equals(Alias aliasPK, Alias aliasFK) {
this.aliasPK = aliasPK;
this.aliasFK = aliasFK;
}
/**
* Defines the property of bean specified as alias on {@link OnEquals#eq(Alias, Object)} method
* that will receive the value from alias specified on {@link From#join(Alias)} method and
* force the creation of a new instance if bean property is not set.
* E.g.:
*
*
* builder.select(user, city)
* .from(user).inPropertyForcingInstance(user.pxy().getCity())
*
*
* @param propertyBean
* @return this
* @see Alias#put(Object, boolean, Alias)
*/
public Equals eqPropertyForcingInstance(Object propertyBean) {
aliasFK.put(propertyBean, true, aliasPK);
return this;
}
/**
* Defines the property of bean specified as alias on {@link OnEquals#eq(Alias, Object)} method
* that will receive the value from alias specified on {@link From#join(Alias)} method
* E.g.:
*
*
* builder.select(user, city)
* .from(user).inProperty(user.pxy().getCity())
*
*
* @param propertyBean
* @return this
* @see Alias#put(Object, boolean, Alias)
*/
public Equals eqProperty(Object propertyBean) {
aliasFK.put(propertyBean, false, aliasPK);
return this;
}
public OnEquals and(Object property) {
return new On(aliasPK, false).on(property);
}
}
public class Where implements Appendable, HasInitClause {
private Where (boolean init) {
if (init) {
sb.append(" WHERE ");
}
}
@Override
public InitClauseWhere clauseIf(boolean clauseIf, Param param) {
QueryBuilder.this.clauseIf = clauseIf;
return new InitClauseWhere(param);
}
@Override
public InitClauseWhere clauseIf(boolean clauseIf, Function function){
return clauseIf(clauseIf, new ParamFunction(function));
}
@Override
public InitClauseWhere clauseIf(boolean clauseIf, Alias alias, Object property) {
return clauseIf(clauseIf, new ParamField(alias, property));
}
@Override
public Where append(Param p) {
QueryBuilder.this.append(p);
return this;
}
@Override
public InitClauseWhere clause(Function function) {
return clauseIf(true, function);
}
@Override
public InitClauseWhere clause(Param param) {
return clauseIf(true, param);
}
@Override
public InitClauseWhere clause(Alias alias, Object property) {
return clauseIf(true, alias, property);
}
/**
* Insert a left parenthesis '(' in query
* @return this
*/
public Where openPar() {
QueryBuilder.this.openPar();
return this;
}
}
public class InitClauseWhere extends InitClause implements Appendable, HasEndClause {
private InitClauseWhere(Param param) {
super(param);
}
@Override
public InitClauseWhere append(Param p) {
QueryBuilder.this.append(p);
return this;
}
@Override
public EndClauseWhere condition(String condition) {
return new EndClauseWhere(condition);
}
@Override
public EndClauseWhere condition(Condition condition) {
return new EndClauseWhere(condition);
}
}
public class InitClauseHaving extends InitClause implements Appendable, HasEndClause {
private InitClauseHaving(Param param) {
super(param);
}
@Override
public InitClauseHaving append(Param p) {
QueryBuilder.this.append(p);
return this;
}
@Override
public EndClauseHaving condition(String condition) {
return new EndClauseHaving(condition);
}
@Override
public EndClauseHaving condition(Condition condition) {
return new EndClauseHaving(condition);
}
}
public class InitClause {
private InitClause (Param param) {
if (clauseIf) {
add(param);
sb.append(' ').append(param.paramInQuery());
}
}
}
public class EndClauseWhere extends EndClause implements Appendable, CanGroupBy {
private EndClauseWhere(Condition condition) {
if (clauseIf) {
add(condition);
init(condition.build());
}
}
private EndClauseWhere(String condition) {
if (clauseIf) {
init(condition);
}
}
/**
* Insert a left parenthesis '(' in query
* @return this
*/
public EndClauseWhere openPar() {
QueryBuilder.this.openPar();
return this;
}
/**
* Insert a right parenthesis ')' in query
* @return this
*/
public EndClauseWhere closePar() {
QueryBuilder.this.closePar();
return this;
}
public Where and() {
addAnd();
return new Where(false);
}
public Where or() {
addOr();
return new Where(false);
}
@Override
public EndClauseWhere append(Param p) {
QueryBuilder.this.append(p);
return this;
}
@Override
public GroupBy groupBy(Alias... aliases) {
return new GroupBy(aliases);
}
@Override
public GroupBy groupByProp(Alias alias, Object... properties) {
return new GroupBy(alias, properties);
}
@Override
public GroupBy groupBy() {
return new GroupBy();
}
}
public class EndClauseHaving extends EndClause implements Appendable {
private EndClauseHaving(Condition condition) {
if (clauseIf) {
add(condition);
init(condition.build());
}
}
private EndClauseHaving(String condition) {
if (clauseIf) {
init(condition);
}
}
/**
* Insert a left parenthesis '(' in query
* @return this
*/
public EndClauseHaving openPar() {
QueryBuilder.this.openPar();
return this;
}
/**
* Insert a right parenthesis ')' in query
* @return this
*/
public EndClauseHaving closePar() {
QueryBuilder.this.closePar();
return this;
}
public Having and() {
addAnd();
return new Having(false);
}
public Having or() {
addOr();
return new Having(false);
}
@Override
public EndClauseHaving append(Param p) {
QueryBuilder.this.append(p);
return this;
}
}
public abstract class EndClause extends Query implements CanOrder, CanLimit {
protected void init(String condition) {
sb.append(' ').append(condition);
}
@Override
public Order orderBy() {
return new Order();
}
@Override
public Limit limit(Param lim) {
return new Limit(lim);
}
@Override
public Limit limit(Number lim) {
return new Limit(lim);
}
}
public class Limit extends Query implements CanOrder, Appendable {
private Limit(Param lim) {
if (lim != null) {
applyRegex();
add(lim);
sb.append(" LIMIT ").append(lim.paramInQuery());
}
}
private Limit(Number lim) {
this(lim != null && lim.longValue() > 0 ? new ParamValue(lim) : null);
}
@Override
public Order orderBy() {
return new Order();
}
public Offset offset(Integer offset) {
return new Offset(offset);
}
public Offset offset(Param param) {
return new Offset(param);
}
@Override
public Limit append(Param p) {
QueryBuilder.this.append(p);
return this;
}
}
public class Offset extends Query implements Appendable {
public Offset(Number offset) {
this(offset != null && offset.longValue() > 0 ? new ParamValue(offset) : null);
}
public Offset(Param param) {
if (param != null) {
add(param);
sb.append(" OFFSET ").append(param.paramInQuery());
}
}
public Order orderBy() {
return new Order();
}
@Override
public Offset append(Param p) {
QueryBuilder.this.append(p);
return this;
}
}
public class Order implements Appendable {
private Order() {
applyRegex();
sb.append(" ORDER BY ");
}
public Ordering asc(Param param) {
return new Ordering().asc(param);
}
public Ordering desc(Param param) {
return new Ordering().desc(param);
}
public Ordering asc(Alias alias, Object... properties){
return new Ordering().asc(alias, properties);
}
public Ordering desc(Alias alias, Object... properties){
return new Ordering().desc(alias, properties);
}
@Override
public Order append(Param p) {
QueryBuilder.this.append(p);
return this;
}
}
public class Ordering extends Query implements CanLimit, Appendable {
private boolean alreadyOrder = false;
private void initOrder() {
if (alreadyOrder) {
sb.append(',');
}
alreadyOrder = true;
}
public Ordering asc(Param param) {
initOrder();
add(param);
sb.append(param.paramInQuery()).append(" ASC ");
return this;
}
public Ordering desc(Param param) {
initOrder();
add(param);
sb.append(param.paramInQuery()).append(" DESC ");
return this;
}
public Ordering asc(Alias alias, Object... properties){
iterateOrderBy(" ASC ", alias, properties);
return this;
}
public Ordering desc(Alias alias, Object... properties){
iterateOrderBy(" DESC ", alias, properties);
return this;
}
@Override
public Limit limit(Param lim) {
return new Limit(lim);
}
@Override
public Limit limit(Number lim) {
return new Limit(lim);
}
private void iterateOrderBy(String orderType, Alias alias, Object[] properties){
String[] props = AnsiSQLBeanSession.getProperties(properties);
initOrder();
for(String prop : props){
sb.append(alias.toColumn(prop)).append(orderType).append(",");
}
sb.setCharAt(sb.length()-1, ' ');
}
@Override
public Ordering append(Param p) {
QueryBuilder.this.append(p);
return this;
}
}
public class GroupBy extends Query implements Appendable, CanLimit, CanOrder {
private void init() {
applyRegex();
sb.append(!sb.toString().endsWith(" GROUP BY ") ? " GROUP BY " : ",");
}
private GroupBy(Alias alias, Object... properties) {
init();
add(alias, properties);
}
private GroupBy(Alias... alias) {
init();
add(alias);
}
private GroupBy() {
init();
add(aliases.toArray(new Alias[0]));
}
public GroupBy add(Alias alias, Object... properties) {
if (!sb.toString().endsWith(" GROUP BY ")) {
sb.append(',');
}
sb.append(session.buildSelectImpl(alias.config.getBeanClass(), alias.aliasStr,
AnsiSQLBeanSession.getProperties(properties), null, false, false));
return this;
}
public GroupBy add(Alias... aliases) {
for (Alias alias : aliases) {
if (!sb.toString().endsWith(" GROUP BY "))
sb.append(',');
sb.append(session.buildSelectImpl(alias.config.getBeanClass(), alias.aliasStr,
alias.returns, alias.returnMinus, false, false));
}
return this;
}
public Having having() {
return new Having(true);
}
@Override
public GroupBy append(Param p) {
QueryBuilder.this.append(p);
return this;
}
@Override
public Order orderBy() {
return new Order();
}
@Override
public Limit limit(Param lim) {
return new Limit(lim);
}
@Override
public Limit limit(Number lim) {
return new Limit(lim);
}
}
public class Having extends Query implements Appendable, HasInitClause{
private Having (boolean init) {
if (init) {
sb.append(" HAVING ");
}
}
@Override
public InitClauseHaving clauseIf(boolean clauseIf, Param param){
QueryBuilder.this.clauseIf = clauseIf;
return new InitClauseHaving(param);
}
@Override
public InitClauseHaving clauseIf(boolean clauseIf, Function function){
return clauseIf(clauseIf, new ParamFunction(function));
}
@Override
public InitClauseHaving clauseIf(boolean clauseIf, Alias alias, Object property) {
return clauseIf(clauseIf, new ParamField(alias, property));
}
@Override
public Having append(Param p) {
QueryBuilder.this.append(p);
return this;
}
@Override
public InitClauseHaving clause(Function function) {
return clauseIf(true, function);
}
@Override
public InitClauseHaving clause(Param param) {
return clauseIf(true, param);
}
@Override
public InitClauseHaving clause(Alias alias, Object property) {
return clauseIf(true, alias, property);
}
/**
* Insert a left parenthesis '(' in query
* @return this
*/
public Having openPar() {
QueryBuilder.this.openPar();
return this;
}
}
public interface HasInitClause {
/**
* Insert the param as a clause in query (same of clauseIf(true, param)).
*
* E.g.:
*
*
* ...
*
*
* @param param
*/
public T clause(Param param);
/**
* Insert the function as a clause in query (same of clauseIf(true, function)).
*
* E.g.:
*
*
* ...
*
*
* @param function
*/
public T clause(Function function);
/**
* Insert the field as a clause in query (same of clauseIf(true, alias, property)).
*
* E.g.:
*
*
* ...
*
*
* @param alias - The alias to get property
* @param property - The property
*/
public T clause(Alias alias, Object property);
/**
* Insert the param as a clause in query if and only if the flag clauseIf is true
* @param clauseIf - Flag indicating if this clause will be inserted in SQL query
* @param param
* @see #clause(Param)
*/
public T clauseIf(boolean clauseIf, Param param);
/**
* Insert the function as a clause in query if and only if the flag clauseIf is true
* @param clauseIf - Flag indicating if this clause will be inserted in SQL query
* @param function
* @see #clause(Function)
*/
public T clauseIf(boolean clauseIf, Function function);
/**
* Insert the field as a clause in query if and only if the flag clauseIf is true
* @param clauseIf - Flag indicating if this clause will be inserted in SQL query
* @param alias - The alias to get property
* @param property - The property
* @see #clause(Alias, Object)
*/
public T clauseIf(boolean clauseIf, Alias alias, Object property);
}
public interface HasEndClause {
public T condition(String condition);
public T condition(Condition condition);
}
public interface CanOrder {
public Order orderBy();
}
public interface CanLimit {
public Limit limit(Param lim);
public Limit limit(Number lim);
}
public interface CanGroupBy {
public GroupBy groupBy(Alias... aliases);
public GroupBy groupByProp(Alias alias, Object... properties);
/**
* Group by all aliases fields used in SELECT clause
* @return this
*/
public GroupBy groupBy();
}
public interface Appendable {
/**
* Appends the parameter directly in query
* @param p - Param
* @return this
*/
public T append(Param p);
}
/**
* Represents a query ready to execute
* @author erico
*
*/
public class Query {
private Query() {}
/**
* Returns a PreparedStatement setting all given parameters in order.
*
* @param params
* @return A PreparedStatement using this session connection
* @see #getSQL()
* @see SQLUtils#prepare(java.sql.Connection, String, Object...)
*/
public PreparedStatement prepare(Object... params) {
try {
PreparedStatement ppst = SQLUtils.prepare(session.getConnection(), getSQL(), params);
if (AnsiSQLBeanSession.DEBUG_NATIVE) {
System.out.println("CUSTOM QUERY (NATIVE): "+ppst);
}
return ppst;
} catch (SQLException e) {
throw new BeanException("Error preparing statement", e);
}
}
/**
* Prepares a statement with paramValues to execute the query manually.
* @return A PreparedStatement using this session connection
*/
public PreparedStatement prepare() {
return prepare(paramValues.toArray());
}
/**
* Executes the query returning a List of beans declared in FROM clause.
*
* @return A list containing all beans retrieved by ResultSet
* @see Alias#populateAll(ResultSet, Object)
*/
public List executeQuery() {
PreparedStatement ppst = null;
try {
ppst = prepare();
ResultSet rs = ppst.executeQuery();
List list = new ArrayList();
T bean;
while (rs.next()) {
bean = (T) aliasFrom.config.getBeanClass().newInstance();
aliasFrom.populateAll(rs, bean);
for (Sentence s : sentences.values()) {
session.injectValue(bean, s.getProperty(),
s.getValue(rs), s.getReturnType().getTypeClass());
}
list.add(bean);
}
return list;
} catch (Exception e) {
throw new BeanException("Unable to execute query from QueryBuilder", e);
}finally {
SQLUtils.close(ppst);
}
}
/**
* Executes the query returning a single value according returnType of sentence in query.
* @return The value returned by query
*/
public T executeSentence() {
if (sentences.values().size() != 1)
throw new BeanException("This query must have exactly one sentence to execute");
PreparedStatement ppst = null;
try {
ppst = prepare();
ResultSet rs = ppst.executeQuery();
if (rs.next()) {
if (!rs.isLast()) {
throw new BeanException("The query returns more than one result");
}
Sentence s = sentences.values().iterator().next();
return (T) s.getValue(rs);
}
throw new BeanException("Query has no results");
} catch (Exception e) {
throw new BeanException("Unable to execute sentence from QueryBuilder", e);
}finally {
SQLUtils.close(ppst);
}
}
@SuppressWarnings("unchecked")
public T getValueFromResultSet(ResultSet rs, String name) throws SQLException {
Sentence s = sentences.get(name);
if (s == null)
throw new BeanException("The sentence name '"+name+"' is not included in query");
return (T) s.getValue(rs);
}
/**
* Returns the SQL generated by QueryBuilder
* @return The String SQL
*/
public String getSQL() {
if (parenthesis != 0) {
throw new BeanException("Invalid parenthesis");
}
applyRegex();
if (AnsiSQLBeanSession.DEBUG) {
System.out.println("CUSTOM QUERY: "+sb.toString());
}
return sb.toString();
}
public List getParamValues() {
return paramValues;
}
}
}
