Using the Database API

An example use of the Database API is shown below. Note that in most cases, direct use of the Database API is discouraged; you should use the Content API, which encapsulates use of the database.

The query and querySingle have two sets of parameters. If you are merely reading data, and will not be changing any values, you can use the forms without the table parameter. This allows you to perform queries with results pulled from multiple tables, for example:

    TableRowIterator readOnlyRows = DatabaseManager.query(context,
    "SELECT handle.handle, item.submitter_id FROM handle, item WHERE
    handle.resource_id=item.item_id");

If you do wish to update the rows, you'll need to use the forms including the table parameter, for example:

    TableRow updateable = DatabaseManager.querySingle(context,
        "item",
        "SELECT * FROM item WHERE item_id=1234");
    updateable.setColumn("submitter_id", 5678);
    DatabaseManager.update(context, updateable);

More example usage:

    // Create or obtain a context object
    Context context;

    try
    {
      // Run an arbitrary query
      // Each object returned by the iterator is a TableRow,
      // with values obtained from the results of the query
      TableRowIterator iterator = DatabaseManager.query(context,
      "community",
      "SELECT * FROM Community WHERE name LIKE 'T%'");

      // Find a single row, using an arbitrary query
      // If no rows are found, then null is returned.
      TableRow row = DatabaseManager.querySingle(context,
      "SELECT * FROM EPerson WHERE email = 'pbreton@mit.edu'");

      // Run an insert, update or delete SQL command
      // Returns the number of rows affected.
      int rowsAffected = DatabaseManager.updateQuery(context,
      "DELETE FROM EPersonGroup WHERE name LIKE 'collection_100_%'");

      // Find a row in a particular table
      // This will return the row in the eperson table with id 1, or null
      // if no such row exists
      TableRow epersonrow = DatabaseManager.find(context, "eperson", 1);

      // Create a new row, and assign a primary key
      TableRow newrow = DatabaseManager.create(context, "Collection");
      newrow.setColumn("name", "Test Collection for example code");
      newrow.setColumn("provenance_description", "Created via test program");
      // Save changes to the database
      DatabaseManager.update(context, newrow);
      // Delete the row
      DatabaseManager.delete(context, newrow);

      // Make sure all changes are committed
      context.complete();
    }
    catch (SQLException sqle)
    {
        // Handle database error
    }

Database IDs

All tables in the DSpace system have a single primary key, which is an integer. The primary key column is named for the table; for example, the EPerson table has eperson_id.

Assigning database IDs is done by invoking the SQL function getnextid with the table name as a single parameter. The database backend may implement this in any suitable way; it should be robust to access via multiple simultaneous clients and transactions.