sql.java

来自「Groovy动态语言 运行在JVM中的动态语言 可以方便的处理业务逻辑变化大的业」· Java 代码 · 共 1,263 行 · 第 1/4 页

    public static ExpandedVariable expand(final Object object){
        return new ExpandedVariable(){
            public Object getObject() {
                return object;
            }};
    }
    
    /**
     * Constructs an SQL instance using the given DataSource. Each operation
     * will use a Connection from the DataSource pool and close it when the
     * operation is completed putting it back into the pool.
     *
     * @param dataSource
     */
    public Sql(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * Construts an SQL instance using the given Connection. It is the callers
     * responsibility to close the Connection after the Sql instance has been
     * used. You can do this on the connection object directly or by calling the
     * {@link java.sql.Connection#close()}  method.
     *
     * @param connection
     */
    public Sql(Connection connection) {
        if (connection == null) {
            throw new NullPointerException("Must specify a non-null Connection");
        }
        this.useConnection = connection;
    }

    public Sql(Sql parent) {
        this.dataSource = parent.dataSource;
        this.useConnection = parent.useConnection;
    }

    public DataSet dataSet(String table) {
        return new DataSet(this, table);
    }

    public DataSet dataSet(Class type) {
        return new DataSet(this, type);
    }

    /**
     * Performs the given SQL query calling the closure with the result set
     */
    public void query(String sql, Closure closure) throws SQLException {
        Connection connection = createConnection();
        Statement statement = connection.createStatement();
        configure(statement);
        ResultSet results = null;
        try {
            log.fine(sql);
            results = statement.executeQuery(sql);
            closure.call(results);
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement, results);
        }
    }

    /**
     * Performs the given SQL query with parameters calling the closure with the
     * result set
     */
    public void query(String sql, List params, Closure closure) throws SQLException {
        Connection connection = createConnection();
        PreparedStatement statement = null;
        ResultSet results = null;
        try {
            log.fine(sql);
            statement = connection.prepareStatement(sql);
            setParameters(params, statement);
            configure(statement);
            results = statement.executeQuery();
            closure.call(results);
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement, results);
        }
    }

    /**
     * Performs the given SQL query calling the closure with the result set
     */
    public void query(GString gstring, Closure closure) throws SQLException {
        List params = getParameters(gstring);
        String sql = asSql(gstring, params);
        query(sql, params, closure);
    }

    /**
     * @deprecated please use eachRow instead
     */
    public void queryEach(String sql, Closure closure) throws SQLException {
        warnDeprecated();
        eachRow(sql, closure);
    }

    /**
     * Performs the given SQL query calling the closure with each row of the
     * result set
     */
    public void eachRow(String sql, Closure closure) throws SQLException {
        Connection connection = createConnection();
        Statement statement = connection.createStatement();
        configure(statement);
        ResultSet results = null;
        try {
            log.fine(sql);
            results = statement.executeQuery(sql);

            GroovyResultSet groovyRS = new GroovyResultSet(results);
            while (groovyRS.next()) {
                closure.call(groovyRS);
            }
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement, results);
        }
    }

    /**
     * @deprecated please use eachRow instead
     */
    public void queryEach(String sql, List params, Closure closure) throws SQLException {
        warnDeprecated();
        eachRow(sql, params, closure);
    }

    /**
     * Performs the given SQL query calling the closure with the result set
     */
    public void eachRow(String sql, List params, Closure closure) throws SQLException {
        Connection connection = createConnection();
        PreparedStatement statement = null;
        ResultSet results = null;
        try {
            log.fine(sql);
            statement = connection.prepareStatement(sql);
            setParameters(params, statement);
            configure(statement);
            results = statement.executeQuery();

            GroovyResultSet groovyRS = new GroovyResultSet(results);
            while (groovyRS.next()) {
                closure.call(groovyRS);
            }
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement, results);
        }
    }

    /**
     * Performs the given SQL query calling the closure with the result set
     */
    public void eachRow(GString gstring, Closure closure) throws SQLException {
        List params = getParameters(gstring);
        String sql = asSql(gstring, params);
        eachRow(sql, params, closure);
    }

    /**
     * @deprecated please use eachRow instead
     */
    public void queryEach(GString gstring, Closure closure) throws SQLException {
        warnDeprecated();
        eachRow(gstring, closure);
    }

    /**
     * Performs the given SQL query and return the rows of the result set
     */
     public List rows(String sql) throws SQLException {
        List results = new ArrayList();
        Connection connection = createConnection();
        Statement statement = connection.createStatement();
        configure(statement);
        ResultSet rs = null;
        try {
            log.fine(sql);
            rs = statement.executeQuery(sql);
            while (rs.next()) {
                ResultSetMetaData metadata = rs.getMetaData();
                LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true);
                for(int i=1 ; i<=metadata.getColumnCount() ; i++) {
                      lhm.put(metadata.getColumnName(i),rs.getObject(i));
                }
                GroovyRowResult row = new GroovyRowResult(lhm);
                results.add(row);
            }
            return(results);
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement, rs);
        }
    }

    /**
     * Performs the given SQL query and return the first row of the result set
     */
    public Object firstRow(String sql) throws SQLException {
        List rows = rows(sql);
        if (rows.isEmpty()) return null;
        return(rows.get(0));
    }

    /**
     * Performs the given SQL query with the list of params and return
     * the rows of the result set
     */
    public List rows(String sql, List params) throws SQLException {
        List results = new ArrayList();
        Connection connection = createConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            log.fine(sql);
            statement = connection.prepareStatement(sql);
            setParameters(params, statement);
            configure(statement);
            rs = statement.executeQuery();
            while (rs.next()) {
                ResultSetMetaData metadata = rs.getMetaData();
                LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true);
                for(int i=1 ; i<=metadata.getColumnCount() ; i++) {
                    lhm.put(metadata.getColumnName(i),rs.getObject(i));
                }
                GroovyRowResult row = new GroovyRowResult(lhm);
                results.add(row);
            }
            return(results);
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement, rs);
        }
    }

     /**
      * Performs the given SQL query with the list of params and return
      * the first row of the result set
      */
    public Object firstRow(String sql, List params) throws SQLException {
         List rows = rows(sql, params);
         if (rows.isEmpty()) return null;
         return rows.get(0);
     }

    /**
     * Executes the given piece of SQL
     */
    public boolean execute(String sql) throws SQLException {
        Connection connection = createConnection();
        Statement statement = null;
        try {
            log.fine(sql);
            statement = connection.createStatement();
            configure(statement);
            boolean isResultSet = statement.execute(sql);
            this.updateCount = statement.getUpdateCount();
            return isResultSet;
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
    }

    /**
     * Executes the given SQL update
     * 
     * @return the number of rows updated
     */
    public int executeUpdate(String sql) throws SQLException {
        Connection connection = createConnection();
        Statement statement = null;
        try {
            log.fine(sql);
            statement = connection.createStatement();
            configure(statement);
            this.updateCount = statement.executeUpdate(sql);
            return this.updateCount;
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);