org.ralasafe.db.sql.Query.java Source code

Java tutorial

Introduction

Here is the source code for org.ralasafe.db.sql.Query.java

Source

/**
 * Copyright (c) 2004-2011 Wang Jinbao(Julian Wong), http://www.ralasafe.com
 * Licensed under the MIT license: http://www.opensource.org/licenses/mit-license.php
 */
package org.ralasafe.db.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.ralasafe.SystemConstant;
import org.ralasafe.db.ColumnAdapter;
import org.ralasafe.db.DBLevelException;
import org.ralasafe.db.DBPower;
import org.ralasafe.entitle.QueryResult;
import org.ralasafe.entitle.QueryTestResult;
import org.ralasafe.user.User;
import org.ralasafe.util.DBUtil;

public class Query implements Operand, LeftOfIn, RightOfIn, SQLElement {
    private static Log log = LogFactory.getLog(Query.class);

    public static final String SQL_TYPE = "sql";
    public static final String STORED_PROCEDURE_TYPE = "storedProcedure";
    private String name;
    /**
     * datasource name
     */
    private String ds;
    private boolean isRawSQL;
    private String type = SQL_TYPE;
    private Select select = new Select();
    private From from = new From();
    private Where where = new Where();
    private GroupBy groupBy = new GroupBy();
    private OrderBy orderBy = new OrderBy();
    private RawSQL rawSQL = new RawSQL();
    private StoredProcedure storedProcedure = new StoredProcedure();
    private String SQL;

    /**
     * The values would be set to PreparedStatement
     */
    private ArrayList values = new ArrayList();

    public boolean isRawSQL() {
        return isRawSQL;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public StoredProcedure getStoredProcedure() {
        return storedProcedure;
    }

    public void setStoredProcedure(StoredProcedure storedProcedure) {
        this.storedProcedure = storedProcedure;
    }

    public void setIsRawSQL(boolean isRawSQL) {
        this.isRawSQL = isRawSQL;
    }

    public RawSQL getRawSQL() {
        return rawSQL;
    }

    public void setRawSQL(RawSQL rawSQL) {
        this.rawSQL = rawSQL;
    }

    public String reloadSQL() {
        this.SQL = null;
        return toSQL();
    }

    public String toSQL() {
        if (this.SQL == null) {
            StringBuffer buf = new StringBuffer();
            if (isRawSQL) {
                buf.append(rawSQL.getContent());
                buf.append(where.toSQL());
            } else {
                buf.append(select.toSQL()).append(from.toSQL());
                buf.append(where.toSQL());
                buf.append(groupBy.toSQL());
                buf.append(orderBy.toSQL());
            }
            this.SQL = format(buf.toString());
        }

        if (log.isDebugEnabled()) {
            log.debug("\n" + this.SQL);
        }
        return this.SQL;
    }

    /**
     * Add line breaker at propriety positions, to format for web view
     * @param script
     * @return
     */
    private String format(String script) {
        int maxInLine = 80;
        int lengthOfLine = 0;
        int indexOfTheLatestSpaceOrComma = 0;
        char[] dst = new char[script.length()];
        script.getChars(0, script.length(), dst, 0);
        StringBuffer buf = new StringBuffer();
        char replacedChar = ' ';

        for (int i = 0; i < dst.length; i++, lengthOfLine++) {
            char curChar = dst[i];
            buf.append(curChar);

            if (curChar == '\n') {
                // reset counter
                lengthOfLine = 0;
                continue;
            }

            if (curChar == ' ' || curChar == '\t' || curChar == ',') {
                replacedChar = curChar;
                indexOfTheLatestSpaceOrComma = buf.length() - 1;
            }

            if (lengthOfLine == maxInLine) {
                // break line
                if (replacedChar == ',') {
                    buf.replace(indexOfTheLatestSpaceOrComma, indexOfTheLatestSpaceOrComma + 1, ",\n ");
                } else {
                    buf.replace(indexOfTheLatestSpaceOrComma, indexOfTheLatestSpaceOrComma + 1, "\n ");
                }
                lengthOfLine = (buf.length() - indexOfTheLatestSpaceOrComma + 1);
            }
        }
        return buf.toString();
    }

    public void setValues(ArrayList values) {
        this.values = values;
    }

    public ArrayList getValues() {
        return values;
    }

    public Select getSelect() {
        return select;
    }

    public void setSelect(Select select) {
        this.select = select;
    }

    public From getFrom() {
        return from;
    }

    public void setFrom(From from) {
        this.from = from;
    }

    public Where getWhere() {
        return where;
    }

    public void setWhere(Where where) {
        this.where = where;
    }

    public GroupBy getGroupBy() {
        return groupBy;
    }

    public void setGroupBy(GroupBy groupBy) {
        this.groupBy = groupBy;
    }

    public OrderBy getOrderBy() {
        return orderBy;
    }

    public void setOrderBy(OrderBy orderBy) {
        this.orderBy = orderBy;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    /**
     * 
     * Execute this query. It' often be called by UserCategory and BusinessDate script.
     * 
     * @param returnCollection     return a collection?
     * @return   if returnCollection, return the collection< first column's value of query records>
     *           else, return the first record's first column value
     */
    public Object executeQueryRef(User user, Map context, boolean returnCollection) throws DBLevelException {
        Connection conn = null;
        try {
            conn = DBPower.getConnection(ds);
            return executeQueryRef(conn, user, context, returnCollection);
        } finally {
            DBUtil.close(conn);
        }
    }

    private Object executeQueryRef(Connection conn, User user, Map context, boolean returnCollection) {
        QueryResult queryResult = execute(conn, user, context);
        Collection dataColl = queryResult.getData();
        int size = dataColl.size();

        if (returnCollection) {
            ArrayList list = new ArrayList(size);

            for (Iterator iter = dataColl.iterator(); iter.hasNext();) {
                Object record = iter.next();

                list.add(extractValue(record));
            }

            return list;
        } else {
            Object record = null;

            // return default value if no record be queried
            if (size == 0) {
                record = this.select.getObjectNewer().newObject();
            } else {
                record = dataColl.iterator().next();
            }

            return extractValue(record);
        }
    }

    private Object extractValue(Object record) {
        ArrayList columns = this.select.getColumns();
        Column column = (Column) columns.get(0);
        ColumnAdapter adapter = column.getAdapter();
        return adapter.extractFieldValue(record);
    }

    //   private Object getValue( ResultSet rs ) throws SQLException {
    //      ArrayList columns=this.select.getColumns();
    //      if( columns.size()>0 ) {
    //         Column column=(Column) columns.get( 0 );
    //         column.getAdapter().readResultSet( rs, 1, o )
    //      }
    //      return rs.getObject(1);
    //   }

    public QueryResult execute(User user, Map context) throws DBLevelException {
        Connection conn = null;
        try {
            conn = DBPower.getConnection(ds);
            QueryResult result = null;
            int queryLimit = SystemConstant.getQueryLimit();
            if (queryLimit > 0) {
                result = execute(conn, user, context, 0, queryLimit);
                if (result.getData().size() >= queryLimit) {
                    result.setReachQueryLimit(true);
                }
            } else {
                result = execute(conn, user, context);
            }
            result.setTotalCount(executeCount(conn, user, context));
            return result;
        } finally {
            DBUtil.close(conn);
        }
    }

    /**
     * Return query result pagination.
     * 
     * @param first         first index, count from 0,1,2,...
     * @param max           max records
     */
    public QueryResult execute(User user, Map context, int first, int max) {
        first = (first < 0 ? 0 : first);
        max = (max < 0 ? 0 : max);
        Connection conn = null;
        try {
            conn = DBPower.getConnection(ds);
            int queryLimit = SystemConstant.getQueryLimit();
            if (queryLimit > 0 && queryLimit < max) {
                max = queryLimit;
            }
            QueryResult result = execute(conn, user, context, first, max);
            if (result.getData().size() >= queryLimit) {
                result.setReachQueryLimit(true);
            }
            result.setTotalCount(executeCount(conn, user, context));
            return result;
        } finally {
            DBUtil.close(conn);
        }
    }

    private QueryResult execute(Connection conn, User user, Map context) {
        if (context == null) {
            context = new HashMap();
        }

        List data = new LinkedList();

        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            if (type.equals(SQL_TYPE)) {
                pstmt = conn.prepareStatement(toSQL());
            } else if (type.equals(STORED_PROCEDURE_TYPE)) {
                pstmt = conn.prepareCall(storedProcedure.getContent());
            }
            rs = getResultSet(user, context, pstmt);

            // mapping
            Select selectObj = null;
            if (type.equals(SQL_TYPE)) {
                if (isRawSQL) {
                    selectObj = rawSQL.getSelect();
                } else {
                    selectObj = select;
                }
            } else if (type.equals(STORED_PROCEDURE_TYPE)) {
                selectObj = storedProcedure.getSelect();
            }

            while (rs.next()) {
                Object obj = selectObj.getObjectNewer().newObject();
                ArrayList columns = selectObj.getColumns();
                int columnCount = columns.size();
                for (int i = 0; i < columnCount; i++) {
                    Column column = (Column) columns.get(i);
                    column.getAdapter().readResultSet(rs, column.getAlias(), obj);
                }
                data.add(obj);
            }
        } catch (SQLException e) {
            throw new DBLevelException(e);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(pstmt);
        }

        return getQueryResult(data);
    }

    private QueryResult getQueryResult(Collection data) {
        QueryResult result = new QueryResult();
        result.setData(data);

        if (type.equals(SQL_TYPE)) {

            if (isRawSQL) {
                result.setFields(rawSQL.getSelect().getFields());
                result.setReadOnlyFields(rawSQL.getSelect().getReadOnlyFields());
            } else {
                result.setFields(select.getFields());
                result.setReadOnlyFields(select.getReadOnlyFields());
            }

        } else if (type.equals(STORED_PROCEDURE_TYPE)) {
            result.setFields(storedProcedure.getSelect().getFields());
            result.setReadOnlyFields(storedProcedure.getSelect().getReadOnlyFields());
        }

        return result;
    }

    private QueryResult execute(Connection conn, User user, Map context, int first, int max) {
        if (context == null) {
            context = new HashMap();
        }

        List data = new LinkedList();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            boolean supportsLimit = false;
            if (type.equals(SQL_TYPE)) {
                supportsLimit = DBUtil.supportsLimit(conn);
                String sql = toSQL();

                if (supportsLimit) {
                    sql = DBUtil.getLimitString(conn, sql, first, max);
                }

                pstmt = conn.prepareStatement(sql);
            } else if (type.equals(STORED_PROCEDURE_TYPE)) {
                supportsLimit = false;
                pstmt = conn.prepareCall(storedProcedure.getContent());
            }

            rs = getResultSet(user, context, pstmt);

            // mapping
            Select selectObj = null;
            if (type.equals(SQL_TYPE)) {
                if (isRawSQL) {
                    selectObj = rawSQL.getSelect();
                } else {
                    selectObj = select;
                }
            } else if (type.equals(STORED_PROCEDURE_TYPE)) {
                selectObj = storedProcedure.getSelect();
            }

            if (supportsLimit) {
                while (rs.next()) {
                    Object obj = selectObj.getObjectNewer().newObject();
                    ArrayList columns = selectObj.getColumns();
                    int columnCount = columns.size();
                    for (int i = 0; i < columnCount; i++) {
                        Column column = (Column) columns.get(i);
                        column.getAdapter().readResultSet(rs, column.getAlias(), obj);
                    }
                    data.add(obj);
                }
            } else {
                // skip to index(first)
                for (int n = 0; n < first && rs.next(); n++)
                    ;

                // read max records
                for (int n = 0; n < max && rs.next(); n++) {
                    Object obj = selectObj.getObjectNewer().newObject();
                    ArrayList columns = selectObj.getColumns();
                    int columnCount = columns.size();
                    for (int i = 0; i < columnCount; i++) {
                        Column column = (Column) columns.get(i);
                        column.getAdapter().readResultSet(rs, column.getAlias(), obj);
                    }
                    data.add(obj);
                }
            }
        } catch (SQLException e) {
            throw new DBLevelException(e);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(pstmt);
        }

        return getQueryResult(data);
    }

    public String getDs() {
        return ds;
    }

    public void setDs(String ds) {
        this.ds = ds;
    }

    public int executeCount(User user, Map context) {
        Connection conn = null;
        try {
            conn = DBPower.getConnection(ds);
            return executeCount(conn, user, context);
        } finally {
            DBUtil.close(conn);
        }
    }

    private int executeCount(Connection conn, User user, Map context) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int count = 0;
        try {
            if (type.equals(SQL_TYPE)) {
                pstmt = conn.prepareStatement(toCountSQL());
                rs = getResultSet(user, context, pstmt);
                rs.next();
                count = rs.getInt(1);
            } else if (type.equals(STORED_PROCEDURE_TYPE)) {
                pstmt = conn.prepareCall(storedProcedure.getContent());
                rs = getResultSet(user, context, pstmt);
                while (rs.next()) {
                    count++;
                }
            }
            return count;
        } catch (SQLException e) {
            throw new DBLevelException(e);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(pstmt);
        }
    }

    /**
     * SQL: SELECT COUNT(*)...
     * @return
     */
    private String toCountSQL() {
        StringBuffer buf = new StringBuffer();
        if (isRawSQL) {
            buf.append("SELECT COUNT(*) FROM ( ");
            buf.append(rawSQL.getContent());
            buf.append(where.toSQL());
            buf.append(") ralasafe_");
        } else {
            buf.append("SELECT COUNT(*) FROM ( ");
            buf.append(select.toSQL()).append(from.toSQL());
            buf.append(where.toSQL());
            buf.append(groupBy.toSQL());
            buf.append(") ralasafe_");
            //buf.append(orderBy.toSQL());

        }

        String countSql = buf.toString();
        if (log.isDebugEnabled()) {
            log.debug("\n" + countSql);
        }

        return countSql;
    }

    private ResultSet getResultSet(User user, Map context, PreparedStatement pstmt) throws SQLException {
        int valueCount = values.size();
        for (int i = 0; i < valueCount; i++) {
            Value value = (Value) values.get(i);
            Object setValue = value.getValue(user, context);
            if (value.isBehindLike()) {
                if (setValue instanceof java.util.Date) {
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                    setValue = format.format((java.util.Date) setValue);
                }
                pstmt.setObject(i + 1, "%" + setValue + "%");
            } else {
                if (setValue instanceof java.util.Date) {
                    java.util.Date utilDate = (java.util.Date) setValue;
                    java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
                    pstmt.setDate(i + 1, sqlDate);
                } else {
                    pstmt.setObject(i + 1, setValue);
                }
            }
        }
        // long start=System.currentTimeMillis();
        ResultSet executeQuery = pstmt.executeQuery();
        // long end=System.currentTimeMillis();
        // System.out.println( "Oracle Query Cost Time(ms):" + (end-start) );

        return executeQuery;
    }

    public QueryTestResult test(User user, Map context, int first, int max) {
        QueryTestResult result = new QueryTestResult();
        try {
            QueryResult queryResult = execute(user, context, first, max);
            List data = new ArrayList(queryResult.getData());
            int count = queryResult.getTotalCount();
            result.setSQL(toSQL());
            result.setProperties(getProperties());
            result.setData(getData(data));
            result.setTotalRecords(count);
        } catch (Exception e) {
            result.setFailed(true);
            result.setErrorMessage(e.getMessage());
            result.setSQL(toSQL());
        }

        return result;
    }

    private String[][] getData(List result) {
        ArrayList columns = null;
        if (type.equals(SQL_TYPE)) {
            if (isRawSQL) {
                columns = rawSQL.getSelect().getColumns();
            } else {
                columns = select.getColumns();
            }
        } else if (type.equals(STORED_PROCEDURE_TYPE)) {
            columns = storedProcedure.getSelect().getColumns();
        }

        String[][] data = new String[result.size()][columns.size()];
        for (int i = 0; i < data.length; i++) {
            Object mappingObj = result.get(i);
            for (int j = 0; j < data[i].length; j++) {
                Column column = (Column) columns.get(j);
                Object extractFieldValue = column.getAdapter().extractFieldValue(mappingObj);
                if (extractFieldValue == null) {
                    data[i][j] = null;
                } else {
                    data[i][j] = extractFieldValue.toString();
                }
            }
        }
        return data;
    }

    private String[] getProperties() {
        ArrayList columns = null;
        if (type.equals(SQL_TYPE)) {
            if (isRawSQL) {
                columns = rawSQL.getSelect().getColumns();
            } else {
                columns = select.getColumns();
            }
        } else if (type.equals(STORED_PROCEDURE_TYPE)) {
            columns = storedProcedure.getSelect().getColumns();
        }

        String[] properties = new String[columns.size()];
        for (int i = 0; i < properties.length; i++) {
            Column column = (Column) columns.get(i);
            properties[i] = column.getProperty();
        }
        return properties;
    }

    public Query lightCopy() {
        Query q = new Query();
        q.setDs(ds);
        q.setFrom(from);
        q.setGroupBy(groupBy);
        q.setIsRawSQL(isRawSQL);
        q.setName(name);
        q.setOrderBy(orderBy);
        q.setRawSQL(rawSQL);
        q.setSelect(select);
        q.setStoredProcedure(storedProcedure);
        q.setType(type);

        ArrayList newValues = new ArrayList(values.size());
        newValues.addAll(values);
        q.setValues(newValues);

        Where w = where.lightCopy();
        q.setWhere(w);

        return q;
    }
}