Groovy example source code file (DataSet.java)

This example Groovy source code file (DataSet.java) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" ^TM.

Java - Groovy tags/keywords

closure, dataset, dataset, groovyruntimeexception, jdbc, list, override, override, sql, sqlexception, sqlexception, sqlorderbyvisitor, sqlwherevisitor, string, string, stringbuffer, util

The Groovy DataSet.java source code

/*
 * Copyright 2003-2010 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package groovy.sql;

import groovy.lang.Closure;
import groovy.lang.GroovyRuntimeException;
import org.codehaus.groovy.ast.ClassNode;
import org.codehaus.groovy.ast.MethodNode;
import org.codehaus.groovy.ast.CodeVisitorSupport;
import org.codehaus.groovy.ast.stmt.Statement;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * An enhancement of Groovy's Sql class providing support for accessing
 * and querying databases using POGO fields and operators rather than
 * JDBC-level API calls and RDBMS column names. So, instead of a query like:
 * <pre>
 * def db = // an instance of groovy.sql.Sql
 * def sql = '''select * from Person
 *     where (purchaseCount > ? and birthMonth = ?)
 *     and (lastName < ? or lastName > ?)
 *     and age < ? and age > ? and firstName != ?
 *     order by firstName DESC, age'''
 * def params = [10, "January", "Zulu", "Alpha", 99, 5, "Bert"]
 * def sortedPeopleOfInterest = db.rows(sql, params)
 * </pre>
 * You can write code like this:
 * <pre>
 * def person = new DataSet(db, 'Person') // or db.dataSet('Person'), or db.dataSet(Person)
 * def janFrequentBuyers = person.findAll { it.purchaseCount > 10 && it.lastName == "January" }
 * def sortedPeopleOfInterest = janFrequentBuyers.
 *     findAll{ it.lastName < 'Zulu' || it.lastName > 'Alpha' }.
 *     findAll{ it.age < 99 }.
 *     findAll{ it.age > 5 }.
 *     sort{ it.firstName }.reverse().
 *     findAll{ it.firstName != 'Bert' }.
 *     sort{ it.age }
 * </pre>
 * Currently, the Groovy source code for any accessed POGO must be on the
 * classpath at runtime.
 *
 * @author Chris Stevenson
 * @author Paul King
 * @author <a href="mailto:james@coredevelopers.net">James Strachan
 * @version $Revision: 19571 $
 */
public class DataSet extends Sql {

    private Closure where;
    private Closure sort;
    private boolean reversed = false;
    private DataSet parent;
    private String table;
    private SqlWhereVisitor visitor;
    private SqlOrderByVisitor sortVisitor;
    private String sql;
    private List params;
    private Sql delegate;

    public DataSet(Sql sql, Class type) {
        super(sql);
        delegate = sql;
        String table = type.getName();
        int idx = table.lastIndexOf('.');
        if (idx > 0) {
            table = table.substring(idx + 1);
        }
        this.table = table.toLowerCase();
    }

    public DataSet(Sql sql, String table) {
        super(sql);
        delegate = sql;
        this.table = table;
    }

    private DataSet(DataSet parent, Closure where) {
        super(parent);
        this.delegate = parent.delegate;
        this.table = parent.table;
        this.parent = parent;
        this.where = where;
    }

    private DataSet(DataSet parent, Closure where, Closure sort) {
        super(parent);
        this.delegate = parent.delegate;
        this.table = parent.table;
        this.parent = parent;
        this.where = where;
        this.sort = sort;
    }

    private DataSet(DataSet parent) {
        super(parent);
        this.delegate = parent.delegate;
        this.table = parent.table;
        this.parent = parent;
        this.reversed = true;
    }

    @Override
    protected Connection createConnection() throws SQLException {
        return delegate.createConnection();
    }

    @Override
    protected void closeResources(Connection connection, java.sql.Statement statement, ResultSet results) {
        delegate.closeResources(connection, statement, results);
    }

    @Override
    protected void closeResources(Connection connection, java.sql.Statement statement) {
        delegate.closeResources(connection, statement);
    }

    @Override
    public void cacheConnection(Closure closure) throws SQLException {
        delegate.cacheConnection(closure);
    }

    @Override
    public void withTransaction(Closure closure) throws SQLException {
        delegate.withTransaction(closure);
    }

    @Override
    public void commit() throws SQLException {
        delegate.commit();
    }

    @Override
    public void rollback() throws SQLException {
        delegate.rollback();
    }

    public void add(Map<String, Object> map) throws SQLException {
        StringBuffer buffer = new StringBuffer("insert into ");
        buffer.append(table);
        buffer.append(" (");
        StringBuffer paramBuffer = new StringBuffer();
        boolean first = true;
        for (String column : map.keySet()) {
            if (first) {
                first = false;
                paramBuffer.append("?");
            } else {
                buffer.append(", ");
                paramBuffer.append(", ?");
            }
            buffer.append(column);
        }
        buffer.append(") values (");
        buffer.append(paramBuffer.toString());
        buffer.append(")");

        int answer = executeUpdate(buffer.toString(), new ArrayList<Object>(map.values()));
        if (answer != 1) {
            LOG.warning("Should have updated 1 row not " + answer + " when trying to add: " + map);
        }
    }

    public DataSet findAll(Closure where) {
        return new DataSet(this, where);
    }

    public DataSet sort(Closure sort) {
        return new DataSet(this, null, sort);
    }

    public DataSet reverse() {
        if (sort == null) {
            throw new GroovyRuntimeException("reverse() only allowed immediately after a sort()");
        }
        return new DataSet(this);
    }

    public void each(Closure closure) throws SQLException {
        eachRow(getSql(), getParameters(), closure);
    }

    private String getSqlWhere() {
        String whereClaus = "";
        String parentClaus = "";
        if (parent != null) {
            parentClaus = parent.getSqlWhere();
        }
        if (where != null) {
            whereClaus += getSqlWhereVisitor().getWhere();
        }
        if (parentClaus.length() == 0) return whereClaus;
        if (whereClaus.length() == 0) return parentClaus;
        return parentClaus + " and " + whereClaus;
    }

    private String getSqlOrderBy() {
        String sortByClaus = "";
        String parentClaus = "";
        if (parent != null) {
            parentClaus = parent.getSqlOrderBy();
        }
        if (reversed) {
            if (parentClaus.length() > 0) parentClaus += " DESC";
        }
        if (sort != null) {
            sortByClaus += getSqlOrderByVisitor().getOrderBy();
        }
        if (parentClaus.length() == 0) return sortByClaus;
        if (sortByClaus.length() == 0) return parentClaus;
        return parentClaus + ", " + sortByClaus;
    }

    public String getSql() {
        if (sql == null) {
            sql = "select * from " + table;
            String whereClaus = getSqlWhere();
            if (whereClaus.length() > 0) {
                sql += " where " + whereClaus;
            }
            String orerByClaus = getSqlOrderBy();
            if (orerByClaus.length() > 0) {
                sql += " order by " + orerByClaus;
            }
        }
        return sql;
    }

    public List getParameters() {
        if (params == null) {
            params = new ArrayList();
            if (parent != null) {
                params.addAll(parent.getParameters());
            }
            params.addAll(getSqlWhereVisitor().getParameters());
        }
        return params;
    }

    protected SqlWhereVisitor getSqlWhereVisitor() {
        if (visitor == null) {
            visitor = new SqlWhereVisitor();
            visit(where, visitor);
        }
        return visitor;
    }

    protected SqlOrderByVisitor getSqlOrderByVisitor() {
        if (sortVisitor == null) {
            sortVisitor = new SqlOrderByVisitor();
            visit(sort, sortVisitor);
        }
        return sortVisitor;
    }

    private void visit(Closure closure, CodeVisitorSupport visitor) {
        if (closure != null) {
            ClassNode classNode = closure.getMetaClass().getClassNode();
            if (classNode == null) {
                throw new GroovyRuntimeException(
                        "Could not find the ClassNode for MetaClass: " + closure.getMetaClass());
            }
            List methods = classNode.getDeclaredMethods("doCall");
            if (!methods.isEmpty()) {
                MethodNode method = (MethodNode) methods.get(0);
                if (method != null) {
                    Statement statement = method.getCode();
                    if (statement != null) {
                        statement.visit(visitor);
                    }
                }
            }
        }
    }

    /*
    * create a subset of the original dataset
    */
    public DataSet createView(Closure criteria) {
        return new DataSet(this, criteria);
    }

    /**
     * Returns a List of all of the rows from the table a DataSet
     * represents
     *
     * @return Returns a list of GroovyRowResult objects from the dataset
     * @throws SQLException if a database error occurs
     */
    public List rows() throws SQLException {
        return rows(getSql(), getParameters());
    }

    /**
     * Returns the first row from a DataSet's underlying table
     *
     * @return Returns the first GroovyRowResult object from the dataset
     * @throws SQLException if a database error occurs
     */
    public Object firstRow() throws SQLException {
        List rows = rows();
        if (rows.isEmpty()) return null;
        return (rows.get(0));
    }
}