sql.java

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

            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
    }

    /**
     * Executes the given SQL statement. See {@link #executeInsert(GString)}
     * for more details. 
     * @param sql The SQL statement to execute.
     * @return A list of the auto-generated column values for each
     * inserted row.
     */
    public List executeInsert(String sql) throws SQLException {
        Connection connection = createConnection();
        Statement statement = null;
        try {
            log.fine(sql);
            statement = connection.createStatement();
            configure(statement);
            boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);

            // Prepare a list to contain the auto-generated column
            // values, and then fetch them from the statement.
            List autoKeys = new ArrayList();
        	ResultSet keys = statement.getGeneratedKeys();
        	int count = keys.getMetaData().getColumnCount();

        	// Copy the column values into a list of a list.
        	while (keys.next()) {
        		List rowKeys = new ArrayList(count);
        		for (int i = 1; i <= count; i++) {
        			rowKeys.add(keys.getObject(i));
        		}

        		autoKeys.add(rowKeys);
        	}

        	// Store the update count so that it can be retrieved by
        	// clients, and then return the list of auto-generated
        	// values.
        	this.updateCount = statement.getUpdateCount();
        	return autoKeys;
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
    }

    /**
     * Executes the given piece of SQL with parameters
     */
    public boolean execute(String sql, List params) throws SQLException {
        Connection connection = createConnection();
        PreparedStatement statement = null;
        try {
            log.fine(sql);
            statement = connection.prepareStatement(sql);
            setParameters(params, statement);
            configure(statement);
            boolean isResultSet = statement.execute();
            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 with parameters
     * 
     * @return the number of rows updated
     */
    public int executeUpdate(String sql, List params) throws SQLException {
        Connection connection = createConnection();
        PreparedStatement statement = null;
        try {
            log.fine(sql);
            statement = connection.prepareStatement(sql);
            setParameters(params, statement);
            configure(statement);
            this.updateCount = statement.executeUpdate();
            return this.updateCount;
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
    }

    /**
     * Executes the given SQL statement with a particular list of
     * parameter values. See {@link #executeInsert(GString)} for
     * more details. 
     * @param sql The SQL statement to execute.
     * @param params The parameter values that will be substituted
     * into the SQL statement's parameter slots.
     * @return A list of the auto-generated column values for each
     * inserted row.
     */
    public List executeInsert(String sql, List params) throws SQLException {
        // Now send the SQL to the database.
        Connection connection = createConnection();
        PreparedStatement statement = null;
        try {
            log.fine(sql);

            // Prepare a statement for the SQL and then execute it.
            statement = connection.prepareStatement(sql);
            setParameters(params, statement);
            configure(statement);
            boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);

            // Prepare a list to contain the auto-generated column
            // values, and then fetch them from the statement.
            List autoKeys = new ArrayList();
        	ResultSet keys = statement.getGeneratedKeys();
        	int count = keys.getMetaData().getColumnCount();

        	// Copy the column values into a list of a list.
        	while (keys.next()) {
        		List rowKeys = new ArrayList(count);
        		for (int i = 1; i <= count; i++) {
        			rowKeys.add(keys.getObject(i));
        		}

        		autoKeys.add(rowKeys);
        	}

        	// Store the update count so that it can be retrieved by
        	// clients, and then return the list of auto-generated
        	// values.
        	this.updateCount = statement.getUpdateCount();
        	return autoKeys;
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
    }

    /**
     * Executes the given SQL with embedded expressions inside
     */
    public boolean execute(GString gstring) throws SQLException {
        List params = getParameters(gstring);
        String sql = asSql(gstring, params);
        return execute(sql, params);
    }

    /**
     * Executes the given SQL update with embedded expressions inside
     * 
     * @return the number of rows updated
     */
    public int executeUpdate(GString gstring) throws SQLException {
        List params = getParameters(gstring);
        String sql = asSql(gstring, params);
        return executeUpdate(sql, params);
    }

    /**
     * <p>Executes the given SQL with embedded expressions inside, and
     * returns the values of any auto-generated colums, such as an
     * autoincrement ID field. These values can be accessed using
     * array notation. For example, to return the second auto-generated
     * column value of the third row, use <code>keys[3][1]</code>. The
     * method is designed to be used with SQL INSERT statements, but is
     * not limited to them.</p>
     * <p>The standard use for this method is when a table has an
     * autoincrement ID column and you want to know what the ID is for
     * a newly inserted row. In this example, we insert a single row
     * into a table in which the first column contains the autoincrement
     * ID:</p>
     * <pre>
     *     def sql = Sql.newInstance("jdbc:mysql://localhost:3306/groovy",
     *                               "user", 
     *                               "password",
     *                               "com.mysql.jdbc.Driver")
     *
     *     def keys = sql.insert("insert into test_table (INT_DATA, STRING_DATA) "
     *                           + "VALUES (1, 'Key Largo')")
     *
     *     def id = keys[0][0]
     *
     *     // 'id' now contains the value of the new row's ID column.
     *     // It can be used to update an object representation's
     *     // id attribute for example.
     *     ...
     * </pre>
     * @return A list of column values representing each row's
     * auto-generated keys.
     */
    public List executeInsert(GString gstring) throws SQLException {
        List params = getParameters(gstring);
        String sql = asSql(gstring, params);
        return executeInsert(sql, params);
    }

    /**
     * Performs a stored procedure call
     */
    public int call(String sql) throws Exception {
        return call(sql, Collections.EMPTY_LIST);
    }

    /**
     * Performs a stored procedure call with the given parameters
     */
    public int call(String sql, List params) throws Exception {
        Connection connection = createConnection();
        CallableStatement statement = connection.prepareCall(sql);
        try {
            log.fine(sql);
            setParameters(params, statement);
            configure(statement);
            return statement.executeUpdate();
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
    }

    /**
     * Performs a stored procedure call with the given parameters.  The closure
     * is called once with all the out parameters.
     */
    public void call(String sql, List params, Closure closure) throws Exception {
        Connection connection = createConnection();
        CallableStatement statement = connection.prepareCall(sql);
        try {
            log.fine(sql);
            setParameters(params, statement);
            statement.execute();
            List results = new ArrayList();
            int indx = 0;
            int inouts = 0;
            for (Iterator iter = params.iterator(); iter.hasNext();) {
                Object value = iter.next();
                if(value instanceof OutParameter){
                    if(value instanceof ResultSetOutParameter){
                        results.add(new CallResultSet(statement,indx));
                    }else{
                        Object o = statement.getObject(indx+1);
                        if(o instanceof ResultSet){
                            results.add(new GroovyResultSet((ResultSet)o));
                        }else{
                            results.add(o);
                        }
                    }
                    inouts++;
                }
                indx++;
            }
            closure.call(results.toArray(new Object[inouts]));
        } catch (SQLException e) {
            log.log(Level.WARNING, "Failed to execute: " + sql, e);
            throw e;
        } finally {
            closeResources(connection, statement);
        }
    }
    
    /**
     * Performs a stored procedure call with the given parameters
     */
    public int call(GString gstring) throws Exception {
        List params = getParameters(gstring);
        String sql = asSql(gstring, params);
        return call(sql, params);
    }


    /**
     * Performs a stored procedure call with the given parameters,
     * calling the closure once with all result objects.
     */
    public void call(GString gstring, Closure closure) throws Exception {
        List params = getParameters(gstring);
        String sql = asSql(gstring,params);
        call(sql, params,closure);
    }
    
    /**
     * If this SQL object was created with a Connection then this method closes
     * the connection. If this SQL object was created from a DataSource then
     * this method does nothing.
     * 
     * @throws SQLException
     */
    public void close() throws SQLException {
        if (useConnection != null) {
            useConnection.close();
        }
    }