Example: Querying the Bibliography Database

From Apache OpenOffice Wiki
Jump to: navigation, search

The following example queries the bibliography database that is delivered with the OpenOffice.org distribution. The basic steps are:

  1. Create a com.sun.star.sdb.RowSet.
  2. Configure com.sun.star.sdb.RowSet to select from the table "biblio" in the data source "Bibliography".
  3. Execute it.
  4. Iterate over its rows.
  5. Insert a new row.

If the database requires login, set additional properties for user and password, or connect using interactive login. There are other options as well. For details, refer to the section The RowSet Service.

  protected void openQuery() throws com.sun.star.uno.Exception, java.lang.Exception {
      xRemoteServiceManager = this.getRemoteServiceManager(
      // first we create our RowSet object and get its XRowSet interface
      Object rowSet = xRemoteServiceManager.createInstanceWithContext(
          "com.sun.star.sdb.RowSet", xRemoteContext);
      com.sun.star.sdbc.XRowSet xRowSet = (com.sun.star.sdbc.XRowSet) 
          UnoRuntime.queryInterface(com.sun.star.sdbc.XRowSet.class, rowSet);
      // set the properties needed to connect to a database
      XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class, xRowSet);
      // the DataSourceName can be a data source registered with [PRODUCTNAME], among other possibilities
      xProp.setPropertyValue("DataSourceName", "Bibliography");
      // the CommandType must be TABLE, QUERY or COMMAND - here we use COMMAND
      xProp.setPropertyValue("CommandType", new Integer(com.sun.star.sdb.CommandType.COMMAND));
      // the Command could be a table or query name or a SQL command, depending on the CommandType
      xProp.setPropertyValue("Command", "SELECT IDENTIFIER, AUTHOR FROM biblio");
      // if your database requires logon, you can use the properties User and Password
      // xProp.setPropertyValue("User", "JohnDoe");
      // xProp.setPropertyValue("Password", "mysecret");
      // prepare the XRow and XColumnLocate interface for column access
      // XRow gets column values
      com.sun.star.sdbc.XRow xRow = (com.sun.star.sdbc.XRow)UnoRuntime.queryInterface(
          com.sun.star.sdbc.XRow.class, xRowSet);
      // XColumnLocate finds columns by name
      com.sun.star.sdbc.XColumnLocate xLoc = (com.sun.star.sdbc.XColumnLocate)UnoRuntime.queryInterface(
          com.sun.star.sdbc.XColumnLocate.class, xRowSet);
      // print output header
      // output result rows
      while ( xRowSet.next() ) {
          String ident = xRow.getString(xLoc.findColumn("IDENTIFIER"));
          String author = xRow.getString(xLoc.findColumn("AUTHOR"));
          System.out.println(ident + "\t\t" + author); 
      // insert a new row
      // XResultSetUpdate for insertRow handling
      com.sun.star.sdbc.XResultSetUpdate xResultSetUpdate = (com.sun.star.sdbc.XResultSetUpdate)
              com.sun.star.sdbc.XResultSetUpdate.class, xRowSet);
      // XRowUpdate for row updates
      com.sun.star.sdbc.XRowUpdate xRowUpdate = (com.sun.star.sdbc.XRowUpdate)
              com.sun.star.sdbc.XRowUpdate.class, xRowSet);
      // move to insertRow buffer
      // edit insertRow buffer
      xRowUpdate.updateString(xLoc.findColumn("IDENTIFIER"), "GOF95");
      xRowUpdate.updateString(xLoc.findColumn("AUTHOR"), "Gamma, Helm, Johnson, Vlissides"); 
      // write buffer to database
      // throw away the row set
      com.sun.star.lang.XComponent xComp = (com.sun.star.lang.XComponent)UnoRuntime.queryInterface(
          com.sun.star.lang.XComponent.class, xRowSet);
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages