edu.emory.library.tast.db.TastDbQuery.java Source code

Java tutorial

Introduction

Here is the source code for edu.emory.library.tast.db.TastDbQuery.java

Source

/*
Copyright 2010 Emory University
       
   This file is part of Trans-Atlantic Slave Voyages.
       
   Trans-Atlantic Slave Voyages is free software: you can redistribute it and/or modify
   it under the terms of the GNU General Public License as published by
   the Free Software Foundation, either version 3 of the License, or
   (at your option) any later version.
       
   Trans-Atlantic Slave Voyages is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.
       
   You should have received a copy of the GNU General Public License
   along with Trans-Atlantic Slave Voyages.  If not, see <http://www.gnu.org/licenses/>. 
*/
package edu.emory.library.tast.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.Transaction;

import edu.emory.library.tast.dm.attributes.Attribute;

/**
 * Class that represents query built for hibernate.
 * An example usage/results:
 * 
 * Example 1:
 * -->Conditions
 * Conditions c = new Conditions();
 * QueryValue qValue = new QueryValue("Configuration", c);
 * -->QueryValue
 * from Configuration
 * -->HQL
 * select configurat0_.id as id5_, configurat0_.map_entries as map2_5_ from configurations configurat0_
 * 
 * Example 2:
 * -->Conditions
 * Conditions cMain = new Conditions(Conditions.JOIN_AND);
 * cMain.addCondition("vi.voyageId", new Long(104), Conditions.OP_SMALLER_OR_EQUAL);
 * cMain.addCondition(VoyageIndex.getRecent());
 * cMain.addCondition("vi.remoteVoyageId", new DirectValue("v.id"), Conditions.OP_EQUALS);
 * QueryValue qValue = new QueryValue("VoyageIndex as vi, Voyage v", cMain);
 * qValue.addPopulatedAttribute("sum(v.voyage)", false);
 * qValue.setGroupBy(new String[] {"v.datedep"});
 * -->QueryValue
 * select sum(v.voyage) 
 * from VoyageIndex as vi, Voyage v 
 * where vi.voyageId <=  :vivoyageId_494255733104 and
 *           vi.remoteVoyageId = v.id and
 *           (latest =  :latest_11098809531)
 * group by v.datedep
 * -->HQL
 * select sum(voyage1_.voyage) as col_0_0_
 * from voyages_index voyageinde0_, voyages voyage1_
 * where voyageinde0_.vid<=? and voyageinde0_.r_voyage_iid=voyage1_.iid and latest=?
 * group by voyage1_.datedep 
 * 
 * @author Pawel Jurczyk
 *
 */
public class TastDbQuery {

    public static final int LIMIT_NO_LIMIT = -1;

    public static final int FIRST_NO_FIRST = -1;

    public static final int ORDER_DEFAULT = 0;

    public static final int ORDER_ASC = 1;

    public static final int ORDER_DESC = -1;

    /**
     * Objects type that will be quered.
     */
    private String[] objects;

    /**
     * Aliases of objects.
     */
    private Map bindings = new HashMap();

    /**
     * Conditions for query.
     */
    private TastDbConditions conditions;

    /**
     * Group by expression.
     */
    private Attribute[] groupBy = null;

    /**
     * Order by expression.
     */
    private Attribute[] orderBy = null;

    /**
     * Deired order.
     */
    private int order;

    /**
     * Max number of results.
     */
    private int limit;

    /**
     * First result row.
     */
    private int firstResult;

    /**
     * Information abour query cacheability.
     */
    private boolean cacheable = false;

    /**
     * SELECT distinct
     */
    private boolean distinct = false;

    /**
     * Array of Populated attributes.
     */
    private ArrayList populateValues = null;

    /**
     * Constructor. Will use empty conditions.
     * @param objType object type
     */
    public TastDbQuery(String objType) {
        this(objType, new TastDbConditions(TastDbConditions.AND));
    }

    /**
     * Constructor. Will use empty conditions.
     * @param objType object type
     */
    public TastDbQuery(String[] objTypes, String[] aliases) {
        this(objTypes, aliases, new TastDbConditions(TastDbConditions.AND));
    }

    /**
     * Constructor.
     * @param objType
     * @param cond
     */
    public TastDbQuery(String objType, TastDbConditions cond) {
        this(new String[] { objType }, new String[] {}, cond, LIMIT_NO_LIMIT);
    }

    /**
     * Constructor.
     * @param objType
     * @param cond
     */
    public TastDbQuery(String[] objTypes, String[] aliases, TastDbConditions cond) {
        this(objTypes, aliases, cond, LIMIT_NO_LIMIT);
    }

    /**
     * Constructor.
     * @param objType
     * @param cond
     * @param limit
     */
    public TastDbQuery(String[] objTypes, String[] aliases, TastDbConditions cond, int limit) {
        if (aliases.length == 0) {
            aliases = new String[objTypes.length];
            for (int i = 0; i < aliases.length; i++) {
                String alias = null;
                if (objTypes[i].indexOf(".") == -1) {
                    alias = objTypes[i];
                } else {
                    alias = objTypes[i].substring(1 + objTypes[i].lastIndexOf("."));
                }
                aliases[i] = alias.toLowerCase() + "_" + i;
            }
        }
        this.objects = objTypes;
        this.conditions = cond;
        this.limit = limit;
        this.firstResult = FIRST_NO_FIRST;
        for (int i = 0; i < aliases.length; i++) {
            this.bindings.put(objTypes[i], aliases[i]);
        }
    }

    /**
     * Adds new populated attribute.
     * @param p_attrName attribute name
     * @param dictionary true  if attribute is dictionary
     */
    public void addPopulatedAttribute(Attribute p_attr) {
        if (this.populateValues == null) {
            this.populateValues = new ArrayList();
        }
        this.populateValues.add(p_attr);
    }

    /**
     * Creates HQL query.
     * @return ConditionResponse object with HQL parametrized query and map of parameters.
     */
    public ConditionResponse toStringWithParams() {
        StringBuffer buf = new StringBuffer();
        StringBuffer fetchClause = new StringBuffer();

        //Prepare select part and left outer join part (for dictionaries)
        if (this.populateValues != null) {
            buf.append("select ");
            if (distinct)
                buf.append("distinct ");
            boolean first = true;
            Iterator iter = this.populateValues.iterator();
            while (iter.hasNext()) {
                Attribute attr = (Attribute) iter.next();
                if (!first) {
                    buf.append(",");
                }
                first = false;
                buf.append(attr.getHQLSelectPath(bindings)).append(" ");
                if (attr.isOuterjoinable()) {
                    fetchClause.append(" left outer join ").append(attr.getHQLOuterJoinPath(bindings));
                }
            }
        }
        if (orderBy != null) {
            for (int i = 0; i < orderBy.length; i++) {
                Attribute attr = orderBy[i];
                if (attr.isOuterjoinable()) {
                    fetchClause.append(" left outer join ").append(attr.getHQLOuterJoinPath(bindings));
                }
            }
        }

        HashMap allProperties = new HashMap();

        buf.append("from ");
        for (int i = 0; i < this.objects.length; i++) {
            //Set from clause - adds object type and left outer join part prepared above
            if (i > 0) {
                buf.append(", ");
            }
            buf.append(this.objects[i]).append(" ");
            if (this.bindings.get(this.objects[i]) != null) {
                buf.append("as ").append(this.bindings.get(objects[i])).append(" ");
            }
        }
        buf.append(fetchClause);

        //Create where clause
        ConditionResponse response = this.conditions.createWhereForHQL(this.bindings);
        if (!response.conditionString.toString().trim().equals("")) {
            buf.append(" where ").append(response.conditionString);
        }
        allProperties.putAll(response.parameters);

        //Set group by clause
        if (groupBy != null) {
            StringBuffer groupBuf = new StringBuffer();
            for (int i = 0; i < this.groupBy.length; i++) {
                groupBuf.append(groupBy[i].getHQLSelectPath(bindings));
                if (i < this.groupBy.length - 1) {
                    groupBuf.append(", ");
                }
            }
            if (!groupBuf.toString().trim().equals("")) {
                buf.append(" group by ").append(groupBuf);
            }
        }

        //Set order by clause
        if (orderBy != null) {
            StringBuffer orderBuf = new StringBuffer();
            if (order != ORDER_DEFAULT) {
                for (int i = 0; i < this.orderBy.length; i++) {
                    orderBuf.append(this.orderBy[i].getHQLSelectPath(bindings));
                    if (order == ORDER_ASC) {
                        orderBuf.append(" asc");
                    } else {
                        orderBuf.append(" desc");
                    }
                    if (i < this.orderBy.length - 1) {
                        orderBuf.append(", ");
                    }
                }
            }
            if (!orderBuf.toString().trim().equals("")) {
                buf.append(" order by ").append(orderBuf);
            }
        }

        //Prepare return value
        ConditionResponse res = new ConditionResponse();
        res.conditionString = buf;
        res.parameters = allProperties;
        return res;
    }

    /**
     * Creates SQL query for Hibernate. This is not a general purpose functionality.
     * It has been created only for. .. 
     * @return ConditionResponse object with HQL parametrised query and map of parameters.
     */
    public ConditionResponse toSQLStringWithParams() {

        if (objects == null) {
            throw new RuntimeException("objects is null");
        }

        if (objects.length != 1) {
            throw new RuntimeException("more than one objects not supported");
        }

        // master table and master alias
        String masterTable = HibernateConn.getTableName("edu.emory.library.tast.dm." + objects[0]);

        // indexes of tables to avoid duplicates
        Map tablesIndexes = new HashMap();

        // the list of already existing joins (to avoid unnecessary number of them)
        Map existingJoins = new HashMap();

        // start the FROM part
        StringBuffer sqlFrom = new StringBuffer();
        sqlFrom.append(masterTable).append("\n");

        // start the SELECT part
        int selectItemsCount = 0;
        StringBuffer sqlSelect = new StringBuffer();

        // generate the SELECT part
        if (this.populateValues != null) {
            int colIdx = 0;
            for (Iterator iterator = populateValues.iterator(); iterator.hasNext();) {
                Attribute attr = (Attribute) iterator.next();
                if (selectItemsCount > 0)
                    sqlSelect.append(",\n    ");
                sqlSelect.append(attr.getSQLReference(masterTable, tablesIndexes, existingJoins, sqlFrom));
                sqlSelect.append(" AS col_").append(colIdx++);
                selectItemsCount++;
            }
        }

        // start the WHERE part
        StringBuffer sqlWhere = new StringBuffer();
        HashMap parameters = null;
        if (!conditions.isEmpty()) {
            ConditionResponse whereRes = conditions.createWhereForSQL(masterTable, tablesIndexes, existingJoins,
                    sqlFrom);
            sqlWhere = whereRes.conditionString;
            parameters = whereRes.parameters;
        }

        // group by
        StringBuffer sqlGroupBy = new StringBuffer();
        if (groupBy != null && groupBy.length != 0) {
            for (int i = 0; i < groupBy.length; i++) {
                if (i > 0)
                    sqlGroupBy.append(",\n    ");
                sqlGroupBy.append(groupBy[i].getSQLReference(masterTable, tablesIndexes, existingJoins, sqlFrom));
            }
        }

        // generate the ORDER BY
        StringBuffer sqlOrderBy = new StringBuffer();
        if (orderBy != null && orderBy.length != 0 && order != ORDER_DEFAULT) {
            for (int i = 0; i < orderBy.length; i++) {
                if (i > 0)
                    sqlOrderBy.append(",\n    ");
                sqlOrderBy.append(orderBy[i].getSQLReference(masterTable, tablesIndexes, existingJoins, sqlFrom));
                sqlOrderBy.append(" ").append(order == ORDER_ASC ? "ASC" : "DESC");
            }
        }

        // the entire query
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n    ").append(sqlSelect).append("\n");
        sql.append("FROM\n    ").append(sqlFrom);
        if (sqlWhere.length() > 0)
            sql.append("WHERE ").append(sqlWhere).append("\n");
        if (sqlGroupBy.length() > 0)
            sql.append("GROUP BY ").append(sqlGroupBy).append("\n");
        if (sqlOrderBy.length() > 0)
            sql.append("ORDER BY ").append(sqlOrderBy).append("\n");
        if (this.limit != LIMIT_NO_LIMIT)
            sql.append("LIMIT ").append(this.limit).append("\n");
        if (this.limit != FIRST_NO_FIRST)
            sql.append("OFFSET ").append(this.firstResult);

        //Prepare return value
        ConditionResponse res = new ConditionResponse();
        res.conditionString = sql;
        res.parameters = parameters;
        return res;

    }

    public Query getQuery(Session session) {
        return getQuery(session, false);
    }

    public Query getQuery(Session session, boolean useSQL) {

        ConditionResponse response;
        Query query;

        if (!useSQL) {
            response = toStringWithParams();
            query = session.createQuery(response.conditionString.toString());
        } else {
            response = toSQLStringWithParams();
            query = session.createSQLQuery(response.conditionString.toString());
        }

        Iterator iter = response.parameters.keySet().iterator();
        while (iter.hasNext()) {
            String param = iter.next().toString();
            query.setParameter(param, response.parameters.get(param));
        }

        if (!useSQL) {

            if (this.limit != LIMIT_NO_LIMIT) {
                query.setMaxResults(this.limit);
            }

            if (this.firstResult != FIRST_NO_FIRST) {
                query.setFirstResult(this.firstResult);
            }

        }

        query.setCacheable(this.isCacheable());

        return query;

    }

    public Object[] executeQuery() {
        return executeQuery(false);
    }

    public Object[] executeQuery(boolean usqSQL) {
        Session session = HibernateConn.getSession();
        Transaction transaction = session.beginTransaction();
        Object[] ret = executeQuery(session, usqSQL);
        transaction.commit();
        session.close();
        return ret;
    }

    public Object[] executeQuery(Session session) {
        return executeQuery(session, false);
    }

    public Object[] executeQuery(Session session, boolean useSQL) {
        List list = executeQueryList(session, useSQL);
        if (list.size() != 0) {
            return list.toArray();
        } else {
            return new Object[] {};
        }
    }

    public List executeQueryList() {
        return executeQueryList(false);
    }

    public List executeQueryList(boolean usqSQL) {
        Session session = HibernateConn.getSession();
        Transaction transaction = session.beginTransaction();
        List list = executeQueryList(session, usqSQL);
        transaction.commit();
        session.close();
        return list;
    }

    public List executeQueryList(Session session) {
        return this.getQuery(session).list();
    }

    public List executeQueryList(Session session, boolean useSQL) {
        return this.getQuery(session, useSQL).list();
    }

    public ScrollableResults executeScrollableQuery(Session session) {
        return this.executeScrollableQuery(session, false);
    }

    public ScrollableResults executeScrollableQuery(Session session, boolean useSQL) {
        return this.getQuery(session, useSQL).scroll();
    }

    public boolean isCacheable() {
        return cacheable;
    }

    public void setCacheable(boolean cacheable) {
        this.cacheable = cacheable;
    }

    public boolean isDistinct() {
        return distinct;
    }

    public void setDistinct(boolean distinct) {
        this.distinct = distinct;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }

    public void setFirstResult(int first) {
        this.firstResult = first;
    }

    public void setOrder(int order) {
        this.order = order;
    }

    public void setGroupBy(Attribute[] groupBy) {
        this.groupBy = groupBy;
    }

    public void setOrderBy(Attribute[] orderBy) {
        this.orderBy = orderBy;
    }

    public void setConditions(TastDbConditions cond) {
        this.conditions = cond;
    }

    public Attribute[] getPopulatedAttributes() {
        return (Attribute[]) this.populateValues.toArray(new Attribute[] {});
    }

}