ACS Java Database API Reference

• Java Database API Requirements
• Java Database API Guide

In ACS Java, rather than writing directly to the JDBC API for database access, you have the option of using the ACS Java database API, a high-level language for performing common interactions with the database. Implicit connection management, rapid development, and encapsulation of best practices for JDBC are all goals of the ACS Java database API.

This document enumerates the features of the API and how its macro language interacts with the Java code you will write. Please refer to the ACS Java Database API Guide for introductory design and usage information.

Table of contents

Definitions
db_string
db_list
db_1row
db_multirow
db_foreach
db_dml
db_exec_plsql
db_call_plsql
db_sql
db_nextval
db_transaction
db_abort_transaction
db_xml

Definitions: things every macro has in common

1. Each macro invocation begins with the command name and is followed by one or more parameters. The parameters may vary in type or number; see each command for a description of its parameters.
2. A macro may appear by itself in the code, in which case it expands into one or more valid java statements. This is called statement context. A macro may also be embedded within a java statement that you write by surrounding it with square brackets. This is called expression context.
3. The first parameter required by most macros is the statement name. This statement name serves two purposes. It serves as a unique identifier for every SQL statement in a given file, allowing us to catalog an application's use of SQL and potentially translate non-portable SQL statements when running on a database other than Oracle. Also, depending on the macro, it may be used as the name of an implicit return value: that is, a side effect of the macro may be to declare a variable with that name.
4. The second parameter required by most macros is a SQL statement surrounded by curly braces. The statement may be formatted in any way you like, containing newlines or indentation. The statement does not need to end with a semicolon (unless you're using db_exec_plsql).
5. Within any SQL statement, you can make use of automatic support for bind variables. If a variable name is prefixed with a colon character, its current value from the surrounding environment is interpolated into the SQL statement before it is executed. For example, if a SQL statement contains the clause where user_id = :user_id, the PreparedStatement.setObject method is used to bind the value of user_id before executing the SQL statement.
6. Any macro that contains a SQL statement may throw an exception of type java.sql.SQLException. This exception may originate from the JDBC driver, or from the runtime support code for the macro commands. In the latter case, we use a subclass of SQLException, com.arsdigita.db.DbException.

db_string

Use this macro to retrieve a single value from the database into a java String object. If the sql_statement does not return any information, the default_string will be returned if it is present; otherwise, a DbException will be thrown.

db_string today {
    select sysdate from dual
}
System.out.println("Oracle says the date is: " + today);    

String group_name;
group_name = [db_string group_name {
    select group_name from groups
     where group_id = :group_id
} -default "Unknown group id!"];

db_list

If you're querying a single column from a database table, and more than one row might be returned, use this macro to create an object of type List in which each element contains the value from a row in the query results.

db_list all_users {
    select user_id from users
}

db_1row

Use this macro to perform a select statement that retrieves values from a single row of a database table. Each of the columns in the query will be declared as a local variable, and given the value that results from the query. The variables created will be of type Object, unless their type is declared within the SQL statement. This can be done by appending a colon and a Java class name to the column name. For example:

db_1row user_info {
    select user_id, priv_email from users
     where user_id = :user_id
}

db_1row user_info {
    select user_id:BigDecimal, priv_email from users
     where user_id = :user_id
}

If the query returns no data, a DbException will be thrown, unless the if_no_rows clause is used. In this case, the final parameter is a code block that will be executed in this case. Here's an example from portrait/index.ajp:

db_1row user_info {
    select first_names, last_name 
      from persons 
     where person_id=:user_id
} if_no_rows {
    throw new WarningException("Account Unavailable",
    "We can't find you (user #" + user_id + ") in the users table.  " +
    "Probably your account was deleted for some reason.");
}

Another side effect of db_1row is that the ResultSet containing the query results is created as a local variable. The name used for the ResultSet object is the macro's statement name (its first parameter). So, in the above example, a variable of type ResultSet is created and named user_info. Its next method is automatically called, so that the values from its first row can be examined. This object is what would be passed to a template page if the ad_page_contract macro specified a template property of user_info:onerow. (see ad_page_contract documentation).

db_multirow

Use this macro to perform a query and obtain a ResultSet object. The specified statement_name is used as the name of the ResultSet. Alternatively, by using the macro in expression context, you can declare the variable yourself. Here's an example from the news package:

db_multirow item {
    select news_item_id, title
    from news_items_obj
    where context_id = :package_id
    and sysdate >= release_date
    and (expiration_date is null or expiration_date > sysdate)
}

db_foreach

This macro is similar to db_multirow in that it allows a query to be performed, but it should be used when you want to do something immediately with the query results. The third parameter is a block of java code (which must be surrounded by curly braces) that will be executed once for each row returned in the query results. Within that code block, the column variables are automatically declared in a manner identical to that of db_1row. Also like db_1row, if you use the optional -column_array switch, you'll have access instead to an object of type Map with the given name, containing all of the values for the current row. Here's an example from the workflow package, which generates an HTML option list from values in the database:

StringBuffer party_widget = new StringBuffer("<select name=\"party_id\">\n");

db_foreach unassigned_parties {
    select p.party_id,
           acs_object.name(p.party_id) as name,
           p.email
    from   parties p
    where  not exists (select 1 from wf_task_assignments ta where ta.task_id = :task_id and ta.party_id = p.party_id)
} {
    party_widget.append("<option value=\"");
    party_widget.append(party_id);
    party_widget.append("\">");
    party_widget.append(name);
    if (!Utilities.emptyString(email)) {
	party_widget.append("(<a href=\"mailto:");
	party_widget.append(email);
	party_widget.append("\">");
	party_widget.append(email);
	party_widget.append("</a>)");
    }
    party_widget.append("</option>\n");
}
party_widget.append("</select>");

ArrayList panelList = new ArrayList();

db_foreach getPanelRows {
    select tp.header, 
           tp.template_url,
           '#dddddd' as bgcolor
    from   wf_context_task_panels tp, 
           wf_cases c,
           wf_tasks t
    where  t.task_id = :task_id
    and    c.case_id = t.case_id
    and    tp.context_key = c.context_key
    and    tp.workflow_key = c.workflow_key
    and    tp.transition_key = t.transition_key
    order by sort_key
} -column_array row {
    panelList.add(row);
} if_no_rows {
    panelList.add(defaultInfoPanel);
}

db_dml

Use this macro when you need to perform any SQL statement that is not a query. Inserts, updates, creates, and deletes are all possibilities. Here's an example:

db_dml update_parties {
    update parties
       set email = :email,
           url = :url
     where party_id = :user_id
}

db_dml update_photo {
    begin
        update cr_revisions
           set content = empty_blob()
         where revision_id = :revision_id
        returning content into :1;
    end;
} -blob_file temp_file_info

The -blob_string switch is also used for taking the contents of a java.lang.String object and using that instead of a file to insert/update a BLOB in the database. Its usage is identical to -blob_file, except the referenced object must be a java.lang.String.

db_exec_plsql

Use this macro when you need to have Oracle execute an arbitrary block of PL/SQL code, or call a PL/SQL function that returns a value. As explained above for -blob_file, your PL/SQL code must always be surrounded with begin and end, and PL/SQL statements must be finished off with semicolons. Return values are indicated with the special bind variable ":1", which should be set to the function's return value as in the following example.

Object item_id =
    [db_exec_plsql create_item {
        begin
            :1 := content_item.new(
                     name => :name,
                     creation_ip => :creation_ip
                  );
        end;
    }];

Here's a simpler example that illustrates how to call a function if you don't need its return value:

db_exec_plsql delete_group {
    begin 
        acs_group.delete(:group_id);
    end;
}

db_call_plsql

Use this macro to call stored PL/SQL functions in Oracle, which return an output value. Typical usage is

String value = [db_call_plsql get_date { function(param_1, param_2, ... ) }];

db_exec_plsql

String value = [db_exec_plsql get_date { 
  begin
    ? := function(param_1, param_2, ... );
  end;
}];

db_call_plsql

BEGIN ... END;

db_sql

In addition to automatic interpolation of bind variables, any SQL statement passed to the Database API may contain interpolated SQL fragments. These are variables prefixed with $, and refer to variables of type com.arsdigita.db.SqlFragment, which can be easily created by using the db_sql macro. With this mechanism, it's easy to programmatically build up a SQL statement from smaller parts. Here's a small, contrived example: for real world examples showing the full power of this technique, see the acs-admin user search page, or the acs-workflow task-list page.

SqlFragment where_clause = (user_id != null ?
    [db_sql restrict_user { user_id = :user_id }] :
    [db_sql no_restriction { 1 = 1 }]
);

db_multirow one_or_all_users {
    select user_id from users
     where $where_clause
}

db_nextval

This macro is a simple shortcut for a common task: obtaining the next value from an Oracle sequence object. Its only parameter is the name of the sequence object, and it is translated into a function call that returns the next sequence value. This means you should only use this macro in expression context, as follows:

BigDecimal user_id = [db_nextval acs_object_id_seq];

db_transaction

The JDBC Connection objects that are created and managed by the ACS Java Database API are, by default, in "auto-commit" mode. Use the db_transaction macro to temporarily turn off "auto-commit" mode so that a series of SQL statements may be issued transactionally. If any errors are thrown within the db_transaction code block, the connection's rollback method will be used to return the database to the state it was in when the transaction was begun. If, on the other hand, all the operations in the transaction succeed, the commit method will be used to make the alterations permanent. Here's an example from the workflow package, where a task object can only be deleted after the references to it have been deleted from all of its child tables.

db_transaction {
    db_dml arcs_delete {
	delete from wf_arcs
	where  workflow_key = :workflow_key
	and    transition_key = :transition_key
    }

    ... (other db_dml deletions) ...

    db_dml transition_delete {
	delete from wf_transitions
	where  workflow_key = :workflow_key
	and    transition_key = :transition_key
    }
}

db_abort_transaction

Use this macro, which takes no parameters, within a db_transaction code block if a condition arises that makes you want to abort the transaction. If the macro is executed, an exception is thrown just as if a Database API statement had generated an error, and the transaction will be automatically rolled back.

db_xml

Revision History