• Home
• com.manydesigns
• portofino-database
• 5.3.0
• source code
• QueryUtils.java

×

Project download

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.

com.manydesigns.portofino.persistence.QueryUtils Maven / Gradle / Ivy

/*
 * Copyright (C) 2005-2020 ManyDesigns srl.  All rights reserved.
 * http://www.manydesigns.com/
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 3 of
 * the License, or (at your option) any later version.
 *
 * This software 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
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
 */

package com.manydesigns.portofino.persistence;

import com.manydesigns.elements.fields.search.Criterion;
import com.manydesigns.elements.fields.search.TextMatchMode;
import com.manydesigns.elements.reflection.ClassAccessor;
import com.manydesigns.elements.reflection.PropertyAccessor;
import com.manydesigns.elements.text.OgnlHqlFormat;
import com.manydesigns.elements.text.OgnlSqlFormat;
import com.manydesigns.elements.text.QueryStringWithParameters;
import com.manydesigns.portofino.model.Model;
import com.manydesigns.portofino.model.database.*;
import com.manydesigns.portofino.reflection.TableAccessor;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.JdbcNamedParameter;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.select.*;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.hibernate.query.Query;
import org.jetbrains.annotations.NotNull;
import org.jetbrains.annotations.Nullable;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.persistence.metamodel.EntityType;
import javax.persistence.metamodel.ManagedType;
import java.io.Serializable;
import java.io.StringReader;
import java.sql.*;
import java.text.MessageFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Utility class for running queries (SQL and HQL) against the database. Provides methods for many common cases,
 * but you can always use the Hibernate {@link Session} directly.
 *
 * @author Paolo Predonzani     - [email protected]
 * @author Angelo Lupo          - [email protected]
 * @author Giampiero Granatella - [email protected]
 * @author Alessio Stalla       - [email protected]
 */
public class QueryUtils {
    public static final String copyright =
            "Copyright (C) 2005-2020 ManyDesigns srl";

    protected static final String WHERE_STRING = " WHERE ";
    protected static final Pattern FROM_PATTERN =
            Pattern.compile("(SELECT\\s+.*\\s+)?FROM\\s+([a-z_$\\u0080-\\ufffe]{1}[a-z_$0-9\\u0080-\\ufffe]*).*",
                            Pattern.CASE_INSENSITIVE | Pattern.DOTALL); //. (dot) matches newlines

    protected static final Logger logger = LoggerFactory.getLogger(QueryUtils.class);

    /**
     * Runs a SQL query against a session. The query is processed with an {@link OgnlSqlFormat}, so it can
     * access values from the OGNL context.
     * @param session the session
     * @param sql the query string
     * @return the results of the query as an Object[] (an array cell per column)
     */
    public static List runSql(Session session, String sql) {
        OgnlHqlFormat hqlFormat = OgnlHqlFormat.create(sql);
        String formatString = hqlFormat.getFormatString();
        Object[] parameters = hqlFormat.evaluateOgnlExpressions(null);
        return runSql(session, formatString, parameters);
    }

    /**
     * Runs a SQL query against a session. The query can contain placeholders for the parameters, as supported
     * by {@link PreparedStatement}.
     * @param session the session
     * @param queryString the query
     * @param parameters parameters to substitute in the query
     * @return the results of the query as an Object[] (an array cell per column)
     */
    public static List runSql(Session session, final String queryString, final Object[] parameters) {
        final List result = new ArrayList();

        try {
            session.doWork(new Work() {
                public void execute(Connection connection) throws SQLException {
                    try (PreparedStatement stmt = connection.prepareStatement(queryString)) {
                        for (int i = 0; i < parameters.length; i++) {
                            stmt.setObject(i + 1, parameters[i]);
                        }
                        try(ResultSet rs = stmt.executeQuery()) {
                            ResultSetMetaData md = rs.getMetaData();
                            int cc = md.getColumnCount();
                            while (rs.next()) {
                                Object[] current = new Object[cc];
                                for (int i = 0; i < cc; i++) {
                                    current[i] = rs.getObject(i + 1);
                                }
                                result.add(current);
                            }
                        }
                    }
                }
            });
        } catch (HibernateException e) {
            session.getTransaction().rollback();
            session.beginTransaction();
            throw e;
        }

        return result;
    }

    /**
     * Runs a query, expressed as {@link TableCriteria}, against the database.
     * @param session the session
     * @param criteria the search criteria
     * @param firstResult index of the first result to return
     * @param maxResults maximum number of results to return
     * @return at most maxResults results from the query
     */
    public static List