All Downloads are FREE. Search and download functionalities are using the official Maven repository.

edu.uvm.ccts.common.sql.SqlStatement Maven / Gradle / Ivy

Go to download

A library of useful generic objects and tools consolidated here to simplify all UVM CCTS projects

There is a newer version: 1.1.5
Show newest version
/*
 * Copyright 2015 The University of Vermont and State
 * Agricultural College.  All rights reserved.
 *
 * Written by Matthew B. Storer 
 *
 * This file is part of CCTS Common.
 *
 * CCTS Common 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.
 *
 * CCTS Common 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 CCTS Common.  If not, see .
 */

package edu.uvm.ccts.common.sql;

import com.foundationdb.sql.StandardException;
import com.foundationdb.sql.parser.*;
import edu.uvm.ccts.common.sql.exceptions.TableNotFoundException;
import edu.uvm.ccts.common.sql.model.Field;
import edu.uvm.ccts.common.sql.model.Table;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * Created by mstorer on 11/1/13.
 */
public class SqlStatement {
    private static final Log log = LogFactory.getLog(SqlStatement.class);

    private static final List whitespaceChars = Arrays.asList(' ', '\n', '\r', '\t');

    private String sql;
    private StatementNode stmtNode;
    private List selectedFields = null;
    private List tables = null;
    private List whereClauseValues = null;

    public SqlStatement(String sql) throws StandardException {
        this.sql = sql;
        refresh();
    }

    public void treePrint() {
        stmtNode.treePrint();
    }

    @Override
    public String toString() {
        return getSql();
    }

    public String getSql() {
        return sql;
    }

    public List getWhereClauseValues() {
        return whereClauseValues;
    }

    public List getSelectedFields() {
        if (selectedFields == null) {
            selectedFields = new ArrayList();

            ResultSetNode rsNode = ((CursorNode) stmtNode).getResultSetNode();
            ResultColumnList selectCols = rsNode.getResultColumns();

            for (String colName : selectCols.getColumnNames()) {
                Field sf;
                if (colName == null) {
                    // query selects a non-table field (e.g. null, 3+5, etc.) without an alias

                    String tn = getTables().get(0).getAliasOrName();
                    sf = new Field(tn, Field.NULL, Field.NULL);

                } else {
                    ResultColumn col = selectCols.getResultColumn(colName);
                    ValueNode expr = col.getExpression();

                    if (expr.getColumnName() == null) {
                        // query selects a non-table field (e.g. null, 3+5, etc.) with an alias

                        String tn = getTables().get(0).getAliasOrName();
                        sf = new Field(tn, Field.NULL, col.getName());

                    } else {
                        sf = new Field(expr.getTableName(), expr.getColumnName(), col.getName());
                    }
                }

                selectedFields.add(sf);
            }
        }

        return selectedFields;
    }

    public List
 getTables() {
        if (tables == null) {
            tables = new ArrayList
();

            SelectNode selNode = (SelectNode) ((CursorNode) stmtNode).getResultSetNode();
            FromList fromList = selNode.getFromList();

            for (int i = 0; i < fromList.size(); i ++) {
                populateTableList(fromList.get(i), tables);
            }
        }

        return tables;
    }

    public String getActualNameForTable(String tableName) throws TableNotFoundException {
        for (Table t : getTables()) {
            if (t.hasNameOrAlias(tableName)) {
                return t.getName();
            }
        }
        throw new TableNotFoundException("unknown table '" + tableName + "'");
    }

    public String getAliasOrNameForTable(String tableName) throws TableNotFoundException {
        for (Table t : getTables()) {
            if (t.hasNameOrAlias(tableName)) {
                return t.getAliasOrName();
            }
        }
        throw new TableNotFoundException("unknown table '" + tableName + "'");
    }

    public void addSelectField(Field field) throws StandardException {
        SelectNode selNode = (SelectNode) ((CursorNode) stmtNode).getResultSetNode();
        FromList fromList = selNode.getFromList();

        int offset = fromList.getBeginOffset() - 6;

        char c = sql.charAt(offset);
        while (whitespaceChars.contains(c)) c = sql.charAt(--offset);

        offset += 1;

        StringBuilder sb = new StringBuilder();
        sb.append(sql.substring(0, offset));
        sb.append(", ").append(field.asSelectField());
        sb.append(sql.substring(offset));

        sql = sb.toString();

        refresh();
    }

    public void addWhereClause(String whereClauseFragment, List values) throws StandardException {
        if (whereClauseFragment.trim().isEmpty()) return;

        assert StringUtils.countMatches(whereClauseFragment, "?") == values.size() : "SQL value placeholder count mismatch";

        SelectNode selNode = (SelectNode) ((CursorNode) stmtNode).getResultSetNode();
        ValueNode whereClause = selNode.getWhereClause();

        int offset = whereClause != null ?
                whereClause.getEndOffset() + 1 :
                selNode.getFromList().getEndOffset() + 1;

        StringBuilder sb = new StringBuilder();
        sb.append(sql.substring(0, offset));
        if (whereClause == null) sb.append(" where ");
        else                     sb.append(" and ");
        sb.append(whereClauseFragment);
        sb.append(sql.substring(offset));

        sql = sb.toString();

        if (whereClauseValues == null) whereClauseValues = new ArrayList();
        for (Object value : values) {
            if (value instanceof Date)  whereClauseValues.add(new java.sql.Timestamp(((Date) value).getTime()));
            else                        whereClauseValues.add(value);
        }
//        whereClauseValues.addAll(values);

        refresh();
    }


///////////////////////////////////////////////////////////////////////////////////
// Private methods
//

    protected void refresh() throws StandardException {
        SQLParser parser = new SQLParser();
        stmtNode = parser.parseStatement(sql);
        selectedFields = null;
        tables = null;
    }

    private void populateTableList(ResultSetNode node, List
 list) {
        if (node instanceof JoinNode) {
            JoinNode jn = (JoinNode) node;

            populateTableList(jn.getLogicalLeftResultSet(), list);
            populateTableList(jn.getLogicalRightResultSet(), list);

        } else if (node instanceof FromBaseTable) {
            FromBaseTable fbt = (FromBaseTable) node;

            Table t = new Table(fbt.getOrigTableName().getTableName(), fbt.getCorrelationName());
            list.add(t);
        }
    }
}



            

            


© 2015 - 2024 Weber Informatics LLC | Privacy Policy