odk/examples/DevelopersGuide/Database/sdbcx.java

   1 /*************************************************************************
   2  *
   3  *  $RCSfile: sdbcx.java,v $
   4  *
   5  *  $Revision: 1.4 $
   6  *
   7  *  last change: $Author: rt $ $Date: 2004-09-09 09:56:23 $
   8  *
   9  *  The Contents of this file are made available subject to the terms of
  10  *  the BSD license.
  11  *
  12  *  Copyright (c) 2003 by Sun Microsystems, Inc.
  13  *  All rights reserved.
  14  *
  15  *  Redistribution and use in source and binary forms, with or without
  16  *  modification, are permitted provided that the following conditions
  17  *  are met:
  18  *  1. Redistributions of source code must retain the above copyright
  19  *     notice, this list of conditions and the following disclaimer.
  20  *  2. Redistributions in binary form must reproduce the above copyright
  21  *     notice, this list of conditions and the following disclaimer in the
  22  *     documentation and/or other materials provided with the distribution.
  23  *  3. Neither the name of Sun Microsystems, Inc. nor the names of its
  24  *     contributors may be used to endorse or promote products derived
  25  *     from this software without specific prior written permission.
  26  *
  27  *  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
  28  *  "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
  29  *  LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
  30  *  FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
  31  *  COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
  32  *  INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
  33  *  BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
  34  *  OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  35  *  ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
  36  *  TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
  37  *  USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  38  *
  39  *************************************************************************/
  40
  41 import java.io.*;
  42
  43 import com.sun.star.lang.XComponent;
  44 import com.sun.star.uno.*;
  45 import com.sun.star.bridge.XUnoUrlResolver;
  46 import com.sun.star.beans.XPropertySet;
  47 import com.sun.star.container.XNameAccess;
  48 import com.sun.star.container.XIndexAccess;
  49 import com.sun.star.sdbc.*;
  50 import com.sun.star.sdbcx.*;
  51 import com.sun.star.lang.XMultiServiceFactory;
  52
  53 public class sdbcx
  54 {
  55     private XMultiServiceFactory xORB;
  56     private static XConnection con;
  57     private XTablesSupplier xTabSup;
  58
  59         public static XMultiServiceFactory rSmgr;
  60     public static void main(String argv[]) throws java.lang.Exception
  61     {
  62         try{
  63             rSmgr = connect("socket,host=localhost,port=8100");
  64                         sdbcx test = new sdbcx(rSmgr);
  65                         test.createConnection();
  66                         test.displayTableProperties();
  67                         // now we dispose the connection to close it
  68                         XComponent xComponent = (XComponent)UnoRuntime.queryInterface(XComponent.class,con);
  69                         if(xComponent != null)
  70                         {
  71                                 xComponent.dispose();
  72                                 System.out.println("Connection disposed!");
  73                         }
  74                 }
  75         catch(com.sun.star.uno.Exception e)
  76         {
  77             System.out.println(e);
  78             e.printStackTrace();
  79         }
  80         System.exit(0);
  81         }
  82         public static XMultiServiceFactory connect( String connectStr )
  83         throws com.sun.star.uno.Exception,
  84         com.sun.star.uno.RuntimeException, java.lang.Exception
  85     {
  86         // initial serviceManager
  87         XMultiServiceFactory xLocalServiceManager =
  88             com.sun.star.comp.helper.Bootstrap.createSimpleServiceManager();
  89
  90         // create a connector, so that it can contact the office
  91         Object  xUrlResolver  = xLocalServiceManager.createInstance( "com.sun.star.bridge.UnoUrlResolver" );
  92         XUnoUrlResolver urlResolver = (XUnoUrlResolver)UnoRuntime.queryInterface(
  93             XUnoUrlResolver.class, xUrlResolver );
  94
  95         Object rInitialObject = urlResolver.resolve( "uno:" + connectStr + ";urp;StarOffice.NamingService" );
  96
  97         XNamingService rName = (XNamingService)UnoRuntime.queryInterface(
  98             XNamingService.class, rInitialObject );
  99
 100         XMultiServiceFactory xMSF = null;
 101         if( rName != null ) {
 102             System.err.println( "got the remote naming service !" );
 103             Object rXsmgr = rName.getRegisteredObject("StarOffice.ServiceManager" );
 104
 105             xMSF = (XMultiServiceFactory)
 106                 UnoRuntime.queryInterface( XMultiServiceFactory.class, rXsmgr );
 107         }
 108
 109         return ( xMSF );
 110     }
 111
 112
 113     public sdbcx(XMultiServiceFactory rSmgr )
 114     {
 115         xORB = rSmgr;
 116     }
 117
 118     public void createConnection() throws com.sun.star.uno.Exception
 119     {
 120         // create the Driver with the implementation name
 121         Object aDriver = xORB.createInstance("com.sun.star.comp.sdbcx.adabas.ODriver");
 122         // query for the interface
 123         com.sun.star.sdbc.XDriver xDriver;
 124         xDriver = (XDriver)UnoRuntime.queryInterface(XDriver.class,aDriver);
 125         if(xDriver != null)
 126         {
 127             // first create the needed url
 128             String adabasURL = "sdbc:adabas::MYDB0";
 129             // second create the necessary properties
 130             com.sun.star.beans.PropertyValue [] adabasProps = new com.sun.star.beans.PropertyValue[]
 131             {
 132                 new com.sun.star.beans.PropertyValue("user",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE),
 133                 new com.sun.star.beans.PropertyValue("password",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE)
 134             };
 135             //
 136
 137             // now create a connection to adabas
 138             con = xDriver.connect(adabasURL,adabasProps);
 139             if(con != null)
 140             {
 141                 System.out.println("Connection could be created!");
 142                 // we the XDatabaseDefinitionSupplier interface from the driver to get the XTablesSupplier
 143                 XDataDefinitionSupplier xDDSup = (XDataDefinitionSupplier)UnoRuntime.queryInterface(
 144                         XDataDefinitionSupplier.class,xDriver);
 145                 if(xDDSup != null)
 146                 {
 147                     xTabSup = xDDSup.getDataDefinitionByConnection(con);
 148                     if(xTabSup != null)
 149                     {
 150                         XNameAccess xTables = xTabSup.getTables();
 151                         // now print all table names
 152                         System.out.println("Tables available:");
 153                         String [] aTableNames = xTables.getElementNames();
 154                         for ( int i =0; i<= aTableNames.length-1; i++)
 155                             System.out.println(aTableNames[i]);
 156                     }
 157                 }
 158                 else
 159                     System.out.println("The driver is not a SDBCX capable!");
 160             }
 161             else
 162                 System.out.println("Connection could not be created!");
 163         }
 164     }
 165
 166     public void displayTableProperties() throws com.sun.star.uno.Exception
 167     {
 168         XNameAccess xTables = xTabSup.getTables();
 169         String [] aTableNames = xTables.getElementNames();
 170         if(0 != aTableNames.length)
 171         {
 172             Object table = xTables.getByName(aTableNames[0]);
 173             XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,table);
 174             System.out.println("Name:          " + xProp.getPropertyValue("Name"));
 175             System.out.println("CatalogName:   " + xProp.getPropertyValue("CatalogName"));
 176             System.out.println("SchemaName:    " + xProp.getPropertyValue("SchemaName"));
 177             System.out.println("Description:   " + xProp.getPropertyValue("Description"));
 178             // the following property is optional so we first must check if it exists
 179             if(xProp.getPropertySetInfo().hasPropertyByName("Type"))
 180                 System.out.println("Type:          " + xProp.getPropertyValue("Type"));
 181         }
 182     }
 183
 184     //###########################################################
 185     // 15. example
 186     // print all columns of a XColumnsSupplier
 187     //###########################################################
 188     public static void printColumns(XColumnsSupplier xColumnsSup) throws com.sun.star.uno.Exception,SQLException
 189     {
 190         System.out.println("Example printColumns");
 191         // the table must be at least support a XColumnsSupplier interface
 192         System.out.println("--- Columns ---");
 193         XNameAccess xColumns = xColumnsSup.getColumns();
 194         String [] aColumnNames = xColumns.getElementNames();
 195         for ( int i =0; i<= aColumnNames.length-1; i++)
 196             System.out.println("    " + aColumnNames[i]);
 197     }
 198     //###########################################################
 199     // 16. example
 200     // print all keys inclusive the columns of a key
 201     //###########################################################
 202     public static void printKeys(XColumnsSupplier xColumnsSup) throws com.sun.star.uno.Exception,SQLException
 203     {
 204         System.out.println("Example printKeys");
 205         XKeysSupplier xKeysSup = (XKeysSupplier)UnoRuntime.queryInterface(XKeysSupplier.class,xColumnsSup);
 206         if(xKeysSup != null)
 207         {
 208             System.out.println("--- Keys ---");
 209             XIndexAccess xKeys = xKeysSup.getKeys();
 210             for ( int i =0; i < xKeys.getCount(); i++)
 211             {
 212                 Object key = xKeys.getByIndex(i);
 213                 XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,key);
 214                 System.out.println("    " + xProp.getPropertyValue("Name"));
 215                 XColumnsSupplier xKeyColumnsSup = ( XColumnsSupplier ) UnoRuntime.queryInterface(XColumnsSupplier.class,xProp);
 216                 printColumns(xKeyColumnsSup);
 217             }
 218         }
 219     }
 220     //###########################################################
 221     // 17. example
 222     // print all keys inclusive the columns of a key
 223     //###########################################################
 224     public static void printIndexes(XColumnsSupplier xColumnsSup) throws com.sun.star.uno.Exception,SQLException
 225     {
 226         System.out.println("Example printIndexes");
 227         XIndexesSupplier xIndexesSup = (XIndexesSupplier)UnoRuntime.queryInterface(XIndexesSupplier.class,xColumnsSup);
 228         if(xIndexesSup != null)
 229         {
 230             System.out.println("--- Indexes ---");
 231             XNameAccess xIndexs = xIndexesSup.getIndexes();
 232             String [] aIndexNames = xIndexs.getElementNames();
 233             for ( int i =0; i<= aIndexNames.length-1; i++)
 234             {
 235                 System.out.println("    " + aIndexNames[i]);
 236                 Object index = xIndexs.getByName(aIndexNames[i]);
 237                 XColumnsSupplier xIndexColumnsSup = (XColumnsSupplier)UnoRuntime.queryInterface(XColumnsSupplier.class,index);
 238                 printColumns(xIndexColumnsSup);
 239             }
 240         }
 241     }
 242
 243     //###########################################################
 244     // 18. example
 245     // column properties
 246     //###########################################################
 247     public static void printColumnProperties(Object column) throws com.sun.star.uno.Exception,SQLException
 248     {
 249         System.out.println("Example printColumnProperties");
 250         XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,column);
 251         System.out.println("Name:            " + xProp.getPropertyValue("Name"));
 252         System.out.println("Type:            " + xProp.getPropertyValue("Type"));
 253         System.out.println("TypeName:        " + xProp.getPropertyValue("TypeName"));
 254         System.out.println("Precision:       " + xProp.getPropertyValue("Precision"));
 255         System.out.println("Scale:           " + xProp.getPropertyValue("Scale"));
 256         System.out.println("IsNullable:      " + xProp.getPropertyValue("IsNullable"));
 257         System.out.println("IsAutoIncrement: " + xProp.getPropertyValue("IsAutoIncrement"));
 258         System.out.println("IsCurrency:      " + xProp.getPropertyValue("IsCurrency"));
 259         // the following property is optional so we first must check if it exists
 260         if(xProp.getPropertySetInfo().hasPropertyByName("IsRowVersion"))
 261             System.out.println("IsRowVersion:    " + xProp.getPropertyValue("IsRowVersion"));
 262         if(xProp.getPropertySetInfo().hasPropertyByName("Description"))
 263             System.out.println("Description:     " + xProp.getPropertyValue("Description"));
 264         if(xProp.getPropertySetInfo().hasPropertyByName("DefaultValue"))
 265             System.out.println("DefaultValue:    " + xProp.getPropertyValue("DefaultValue"));
 266     }
 267
 268     //###########################################################
 269     // 19. example
 270     // index properties
 271     //###########################################################
 272     public static void printIndexProperties(Object index) throws com.sun.star.uno.Exception,SQLException
 273     {
 274         System.out.println("Example printIndexProperties");
 275         XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,index);
 276         System.out.println("Name:              " + xProp.getPropertyValue("Name"));
 277         System.out.println("Catalog:           " + xProp.getPropertyValue("Catalog"));
 278         System.out.println("IsUnique:          " + xProp.getPropertyValue("IsUnique"));
 279         System.out.println("IsPrimaryKeyIndex: " + xProp.getPropertyValue("IsPrimaryKeyIndex"));
 280         System.out.println("IsClustered:       " + xProp.getPropertyValue("IsClustered"));
 281     }
 282
 283     //###########################################################
 284     // 20. example
 285     // key properties
 286     //###########################################################
 287     public static void printKeyProperties(Object key) throws com.sun.star.uno.Exception,SQLException
 288     {
 289         System.out.println("Example printKeyProperties");
 290         XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,key);
 291         System.out.println("Name:            " + xProp.getPropertyValue("Name"));
 292         System.out.println("Type:            " + xProp.getPropertyValue("Type"));
 293         System.out.println("ReferencedTable: " + xProp.getPropertyValue("ReferencedTable"));
 294         System.out.println("UpdateRule:      " + xProp.getPropertyValue("UpdateRule"));
 295         System.out.println("DeleteRule:      " + xProp.getPropertyValue("DeleteRule"));
 296     }
 297
 298     //###########################################################
 299     // 21. example
 300     // print all groups and the users with their privileges who belong to this group
 301     //###########################################################
 302     public static void printGroups(XTablesSupplier xTabSup) throws com.sun.star.uno.Exception,SQLException
 303     {
 304         System.out.println("Example printGroups");
 305         XGroupsSupplier xGroupsSup = (XGroupsSupplier)UnoRuntime.queryInterface(XGroupsSupplier.class,xTabSup);
 306         if(xGroupsSup != null)
 307         {
 308             // the table must be at least support a XColumnsSupplier interface
 309             System.out.println("--- Groups ---");
 310             XNameAccess xGroups = xGroupsSup.getGroups();
 311             String [] aGroupNames = xGroups.getElementNames();
 312             for ( int i =0; i < aGroupNames.length; i++)
 313             {
 314                 System.out.println("    " + aGroupNames[i]);
 315                 XUsersSupplier xUsersSup = (XUsersSupplier)UnoRuntime.queryInterface(XUsersSupplier.class,xGroups.getByName(aGroupNames[i]));
 316                 if(xUsersSup != null)
 317                 {
 318                     XAuthorizable xAuth = (XAuthorizable)UnoRuntime.queryInterface(XAuthorizable.class,xUsersSup);
 319                     // the table must be at least support a XColumnsSupplier interface
 320                     System.out.println("\t--- Users ---");
 321                     XNameAccess xUsers = xUsersSup.getUsers();
 322                     String [] aUserNames = xUsers.getElementNames();
 323                     for ( int j =0; j < aUserNames.length; j++)
 324                     {
 325                         System.out.println("\t    " + aUserNames[j] + " Privileges: " + xAuth.getPrivileges(aUserNames[j],PrivilegeObject.TABLE));
 326                     }
 327                 }
 328             }
 329         }
 330     }
 331
 332     //###########################################################
 333     // 22. example
 334     // create the table salesmen
 335     //###########################################################
 336     public static void createTableSalesMen(XNameAccess xTables) throws com.sun.star.uno.Exception,SQLException
 337     {
 338         System.out.println("Example createTableSalesMen");
 339         XDataDescriptorFactory xTabFac = (XDataDescriptorFactory)UnoRuntime.queryInterface(XDataDescriptorFactory.class,xTables);
 340         if(xTabFac != null)
 341         {
 342             // create the new table
 343             XPropertySet xTable = xTabFac.createDataDescriptor();
 344             // set the name of the new table
 345             xTable.setPropertyValue("Name","SALESMAN");
 346             // append the columns
 347             XColumnsSupplier xColumSup = (XColumnsSupplier)UnoRuntime.queryInterface(XColumnsSupplier.class,xTable);
 348             XDataDescriptorFactory xColFac = (XDataDescriptorFactory)UnoRuntime.queryInterface(XDataDescriptorFactory.class,xColumSup.getColumns());
 349             XAppend xAppend = (XAppend)UnoRuntime.queryInterface(XAppend.class,xColFac);
 350             // we only need one descriptor
 351             XPropertySet xCol = xColFac.createDataDescriptor();
 352             // create first column and append
 353             xCol.setPropertyValue("Name","SNR");
 354             xCol.setPropertyValue("Type",new Integer(DataType.INTEGER));
 355             xCol.setPropertyValue("IsNullable",new Integer(ColumnValue.NO_NULLS));
 356             xAppend.appendByDescriptor(xCol);
 357             // 2nd only set the properties which differs
 358             xCol.setPropertyValue("Name","FIRSTNAME");
 359             xCol.setPropertyValue("Type",new Integer(DataType.VARCHAR));
 360             xCol.setPropertyValue("IsNullable",new Integer(ColumnValue.NULLABLE));
 361             xCol.setPropertyValue("Precision",new Integer(50));
 362             xAppend.appendByDescriptor(xCol);
 363             // 3nd only set the properties which differs
 364             xCol.setPropertyValue("Name","LASTNAME");
 365             xCol.setPropertyValue("Precision",new Integer(100));
 366             xAppend.appendByDescriptor(xCol);
 367             // 4nd only set the properties which differs
 368             xCol.setPropertyValue("Name","STREET");
 369             xCol.setPropertyValue("Precision",new Integer(50));
 370             xAppend.appendByDescriptor(xCol);
 371             // 5nd only set the properties which differs
 372             xCol.setPropertyValue("Name","STATE");
 373             xAppend.appendByDescriptor(xCol);
 374             // 6nd only set the properties which differs
 375             xCol.setPropertyValue("Name","ZIP");
 376             xCol.setPropertyValue("Type",new Integer(DataType.INTEGER));
 377             xCol.setPropertyValue("Precision",new Integer(10)); // default value integer
 378             xAppend.appendByDescriptor(xCol);
 379             // 7nd only set the properties which differs
 380             xCol.setPropertyValue("Name","BIRTHDATE");
 381             xCol.setPropertyValue("Type",new Integer(DataType.DATE));
 382             xCol.setPropertyValue("Precision",new Integer(10)); // default value integer
 383             xAppend.appendByDescriptor(xCol);
 384             // now we create the primary key
 385             XKeysSupplier xKeySup = (XKeysSupplier)UnoRuntime.queryInterface(XKeysSupplier.class,xTable);
 386             XDataDescriptorFactory xKeyFac = (XDataDescriptorFactory)UnoRuntime.queryInterface(XDataDescriptorFactory.class,xKeySup.getKeys());
 387             XAppend xKeyAppend = (XAppend)UnoRuntime.queryInterface(XAppend.class,xKeyFac);
 388             XPropertySet xKey = xKeyFac.createDataDescriptor();
 389             xKey.setPropertyValue("Type",new Integer(KeyType.PRIMARY));
 390             // now append the columns to key
 391             XColumnsSupplier xKeyColumSup = (XColumnsSupplier)UnoRuntime.queryInterface(XColumnsSupplier.class,xKey);
 392             XDataDescriptorFactory xKeyColFac = (XDataDescriptorFactory)UnoRuntime.queryInterface(XDataDescriptorFactory.class,xKeyColumSup.getColumns());
 393             XAppend xKeyColAppend = (XAppend)UnoRuntime.queryInterface(XAppend.class,xKeyColFac);
 394             // we only need one descriptor
 395             XPropertySet xKeyCol = xKeyColFac.createDataDescriptor();
 396             xKeyCol.setPropertyValue("Name","SNR");
 397             // append the key column
 398             xKeyColAppend.appendByDescriptor(xKeyCol);
 399             // apend the key
 400             xKeyAppend.appendByDescriptor(xKey);
 401             // the last step is to append the new table to the tables collection
 402              XAppend xTableAppend = (XAppend)UnoRuntime.queryInterface(XAppend.class,xTabFac);
 403              xTableAppend.appendByDescriptor(xTable);
 404         }
 405     }
 406
 407     //###########################################################
 408     // 23. example
 409     // create a user
 410     //###########################################################
 411     public static void createUser(XNameAccess xUsers) throws com.sun.star.uno.Exception,SQLException
 412     {
 413         System.out.println("Example createUser");
 414         XDataDescriptorFactory xUserFac = (XDataDescriptorFactory)UnoRuntime.queryInterface(XDataDescriptorFactory.class,xUsers);
 415         if(xUserFac != null)
 416         {
 417             // create the new table
 418             XPropertySet xUser = xUserFac.createDataDescriptor();
 419             // set the name of the new table
 420             xUser.setPropertyValue("Name","BOSS");
 421             xUser.setPropertyValue("Password","BOSSWIFENAME");
 422             XAppend xAppend = (XAppend)UnoRuntime.queryInterface(XAppend.class,xUserFac);
 423             xAppend.appendByDescriptor(xUser);
 424         }
 425     }
 426 }