odk/examples/DevelopersGuide/Database/CodeSamples.java

   1 /*************************************************************************
   2  *
   3  *  $RCSfile: CodeSamples.java,v $
   4  *
   5  *  $Revision: 1.7 $
   6  *
   7  *  last change: $Author: ihi $ $Date: 2006-08-01 16:32:39 $
   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.comp.helper.RegistryServiceFactory;
  44 import com.sun.star.comp.servicemanager.ServiceManager;
  45 import com.sun.star.lang.XMultiComponentFactory;
  46 import com.sun.star.lang.XSingleServiceFactory;
  47 import com.sun.star.lang.XServiceInfo;
  48 import com.sun.star.lang.XComponent;
  49 import com.sun.star.bridge.XUnoUrlResolver;
  50 import com.sun.star.uno.UnoRuntime;
  51 import com.sun.star.uno.XComponentContext;
  52 import com.sun.star.beans.XPropertySet;
  53 import com.sun.star.container.XNameAccess;
  54 import com.sun.star.container.XNameContainer;
  55 import com.sun.star.sdbc.*;
  56 import com.sun.star.sdb.*;
  57 import com.sun.star.sdbcx.*;
  58 import com.sun.star.frame.*;
  59
  60 public class CodeSamples
  61 {
  62     public static XComponentContext xContext;
  63     public static XMultiComponentFactory xMCF;
  64
  65     public static void main(String argv[]) throws java.lang.Exception
  66     {
  67         try {
  68             // get the remote office component context
  69             xContext = com.sun.star.comp.helper.Bootstrap.bootstrap();
  70             System.out.println("Connected to a running office ...");
  71             xMCF = xContext.getServiceManager();
  72         }
  73         catch(Exception e) {
  74             System.err.println("ERROR: can't get a component context from a running office ...");
  75             e.printStackTrace();
  76             System.exit(1);
  77         }
  78
  79         try{
  80             createQuerydefinition( );
  81             printQueryColumnNames( );
  82
  83             XConnection con = openConnectionWithDriverManager();
  84             if ( con != null ) {
  85                 {
  86                     SalesMan sm = new SalesMan( con );
  87
  88                     try {
  89                         sm.dropSalesManTable( ); // doesn't matter here
  90                     }
  91                     catch(com.sun.star.uno.Exception e)
  92                     {
  93                     }
  94                     sm.createSalesManTable( );
  95                     sm.insertDataIntoSalesMan( );
  96                     sm.updateSalesMan( );
  97                     sm.retrieveSalesManData( );
  98                 }
  99
 100                 {
 101                     Sales sm = new Sales( con );
 102
 103                     try {
 104                         sm.dropSalesTable( ); // doesn't matter here
 105                     }
 106                     catch(com.sun.star.uno.Exception e)
 107                     {
 108                     }
 109                     sm.createSalesTable( );
 110                     sm.insertDataIntoSales( );
 111                     sm.updateSales( );
 112                     sm.retrieveSalesData( );
 113                     sm.displayColumnNames( );
 114                 }
 115                 displayTableStructure( con );
 116             }
 117             //  printDataSources();
 118         }
 119         catch(Exception e)
 120         {
 121             System.err.println(e);
 122             e.printStackTrace();
 123         }
 124         System.exit(0);
 125     }
 126
 127     // check if the connection is not null aand dispose it later on.
 128     public static void checkConnection(XConnection con) throws com.sun.star.uno.Exception
 129     {
 130         if(con != null)
 131         {
 132             System.out.println("Connection was created!");
 133             // now we dispose the connection to close it
 134             XComponent xComponent = (XComponent)UnoRuntime.queryInterface(XComponent.class,con);
 135             if(xComponent != null)
 136             {
 137                 // connections must be disposed
 138                 xComponent.dispose();
 139                 System.out.println("Connection disposed!");
 140             }
 141         }
 142         else
 143             System.out.println("Connection could not be created!");
 144     }
 145
 146     // uses the driver manager to create a new connection and dispose it.
 147     public static XConnection openConnectionWithDriverManager() throws com.sun.star.uno.Exception
 148     {
 149         XConnection con = null;
 150         // create the DriverManager
 151         Object driverManager =
 152             xMCF.createInstanceWithContext("com.sun.star.sdbc.DriverManager",
 153                                            xContext);
 154         // query for the interface
 155         com.sun.star.sdbc.XDriverManager xDriverManager;
 156         xDriverManager = (XDriverManager)UnoRuntime.queryInterface(XDriverManager.class,driverManager);
 157         if(xDriverManager != null)
 158         {
 159             // first create the needed url
 160             String adabasURL = "sdbc:adabas::MYDB0";
 161             // second create the necessary properties
 162             com.sun.star.beans.PropertyValue [] adabasProps = new com.sun.star.beans.PropertyValue[]
 163             {
 164                 new com.sun.star.beans.PropertyValue("user",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE),
 165                 new com.sun.star.beans.PropertyValue("password",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE)
 166             };
 167             // now create a connection to adabas
 168             con = xDriverManager.getConnectionWithInfo(adabasURL,adabasProps);
 169         }
 170         return con;
 171     }
 172
 173     // uses the driver manager to create a new connection and dispose it.
 174     public static XConnection openToJDBC() throws com.sun.star.uno.Exception
 175     {
 176         XConnection con = null;
 177         // create the DriverManager
 178         Object driverManager =
 179             xMCF.createInstanceWithContext("com.sun.star.sdbc.DriverManager",
 180                                            xContext);
 181         // query for the interface
 182         com.sun.star.sdbc.XDriverManager xDriverManager;
 183         xDriverManager = (XDriverManager)UnoRuntime.queryInterface(XDriverManager.class,driverManager);
 184         if(xDriverManager != null)
 185         {
 186             // first create the needed url
 187             String url = "jdbc:mysql://localhost:3306/TestTables";
 188             // second create the necessary properties
 189             com.sun.star.beans.PropertyValue [] props = new com.sun.star.beans.PropertyValue[]
 190             {
 191                 new com.sun.star.beans.PropertyValue("user",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE),
 192                 new com.sun.star.beans.PropertyValue("password",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE),
 193                 new com.sun.star.beans.PropertyValue("JavaDriverClass",0,"org.gjt.mm.mysql.Driver",com.sun.star.beans.PropertyState.DIRECT_VALUE)
 194             };
 195             // now create a connection to adabas
 196             con = xDriverManager.getConnectionWithInfo(url,props);
 197         }
 198         return con;
 199     }
 200
 201     // uses the driver directly to create a new connection and dispose it.
 202     public static XConnection openConnectionWithDriver() throws com.sun.star.uno.Exception
 203     {
 204         XConnection con = null;
 205         // create the Driver with the implementation name
 206         Object aDriver =
 207             xMCF.createInstanceWithContext("com.sun.star.comp.sdbcx.adabas.ODriver",
 208                                            xContext);
 209         // query for the interface
 210         com.sun.star.sdbc.XDriver xDriver;
 211         xDriver = (XDriver)UnoRuntime.queryInterface(XDriver.class,aDriver);
 212         if(xDriver != null)
 213         {
 214             // first create the needed url
 215             String adabasURL = "sdbc:adabas::MYDB0";
 216             // second create the necessary properties
 217             com.sun.star.beans.PropertyValue [] adabasProps = new com.sun.star.beans.PropertyValue[]
 218             {
 219                 new com.sun.star.beans.PropertyValue("user",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE),
 220                 new com.sun.star.beans.PropertyValue("password",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE)
 221             };
 222             // now create a connection to adabas
 223             con = xDriver.connect(adabasURL,adabasProps);
 224         }
 225         return con;
 226     }
 227
 228     // print all available datasources
 229     public static void printDataSources() throws com.sun.star.uno.Exception
 230     {
 231         // create a DatabaseContext and print all DataSource names
 232         XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface(
 233             XNameAccess.class,
 234             xMCF.createInstanceWithContext("com.sun.star.sdb.DatabaseContext",
 235                                            xContext));
 236         String aNames [] = xNameAccess.getElementNames();
 237         for(int i=0;i<aNames.length;++i)
 238             System.out.println(aNames[i]);
 239     }
 240
 241     // displays the structure of the first table
 242     public static void displayTableStructure(XConnection con) throws com.sun.star.uno.Exception
 243     {
 244         XDatabaseMetaData dm = con.getMetaData();
 245         XResultSet rsTables = dm.getTables(null,"%","SALES",null);
 246         XRow       rowTB = (XRow)UnoRuntime.queryInterface(XRow.class, rsTables);
 247         while ( rsTables.next() )
 248         {
 249             String catalog = rowTB.getString( 1 );
 250             if ( rowTB.wasNull() )
 251                 catalog = null;
 252
 253             String schema = rowTB.getString( 2 );
 254             if ( rowTB.wasNull() )
 255                 schema = null;
 256
 257             String table = rowTB.getString( 3 );
 258             String type = rowTB.getString( 4 );
 259             System.out.println("Catalog: " + catalog + " Schema: " + schema + " Table: " + table + " Type: " + type);
 260             System.out.println("------------------ Columns ------------------");
 261             XResultSet rsColumns = dm.getColumns(catalog,schema,table,"%");
 262             XRow       rowCL = (XRow)UnoRuntime.queryInterface(XRow.class, rsColumns);
 263             while ( rsColumns.next() )
 264             {
 265                 System.out.println("Column: " + rowCL.getString( 4 ) + " Type: " + rowCL.getInt( 5 ) + " TypeName: " + rowCL.getString( 6 ) );
 266             }
 267
 268         }
 269     }
 270
 271     // quote the given name
 272     public static String quoteTableName(XConnection con, String sCatalog, String sSchema, String sTable) throws com.sun.star.uno.Exception
 273     {
 274         XDatabaseMetaData dbmd = con.getMetaData();
 275         String sQuoteString = dbmd.getIdentifierQuoteString();
 276         String sSeparator = ".";
 277         String sComposedName = "";
 278         String sCatalogSep = dbmd.getCatalogSeparator();
 279         if (0 != sCatalog.length() && dbmd.isCatalogAtStart() && 0 != sCatalogSep.length())
 280         {
 281             sComposedName += sCatalog;
 282             sComposedName += dbmd.getCatalogSeparator();
 283         }
 284         if (0 != sSchema.length())
 285         {
 286             sComposedName += sSchema;
 287             sComposedName += sSeparator;
 288             sComposedName += sTable;
 289         }
 290         else
 291                 {
 292             sComposedName += sTable;
 293         }
 294         if (0 != sCatalog.length() && !dbmd.isCatalogAtStart() && 0 != sCatalogSep.length())
 295         {
 296             sComposedName += dbmd.getCatalogSeparator();
 297             sComposedName += sCatalog;
 298         }
 299         return sComposedName;
 300     }
 301
 302     // creates a new query definition
 303     public static void createQuerydefinition() throws com.sun.star.uno.Exception
 304     {
 305         XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface(
 306             XNameAccess.class,
 307             xMCF.createInstanceWithContext("com.sun.star.sdb.DatabaseContext",
 308                                            xContext));
 309         // we use the first datasource
 310         XQueryDefinitionsSupplier xQuerySup = (XQueryDefinitionsSupplier)
 311                                             UnoRuntime.queryInterface(XQueryDefinitionsSupplier.class,
 312                                             xNameAccess.getByName( "Bibliography" ));
 313         XNameAccess xQDefs = xQuerySup.getQueryDefinitions();
 314         // create new query definition
 315         XSingleServiceFactory xSingleFac =      (XSingleServiceFactory)
 316                                             UnoRuntime.queryInterface(XSingleServiceFactory.class, xQDefs);
 317
 318         XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(
 319             XPropertySet.class,xSingleFac.createInstance());
 320         xProp.setPropertyValue("Command","SELECT * FROM biblio");
 321         xProp.setPropertyValue("EscapeProcessing",new Boolean(true));
 322
 323         XNameContainer xCont = (XNameContainer) UnoRuntime.queryInterface(XNameContainer.class, xQDefs);
 324                 try
 325                 {
 326                     if ( xCont.hasByName("Query1") )
 327                         xCont.removeByName("Query1");
 328                 }
 329                 catch(com.sun.star.uno.Exception e)
 330                 {}
 331         xCont.insertByName("Query1",xProp);
 332         XDocumentDataSource xDs = (XDocumentDataSource)UnoRuntime.queryInterface(XDocumentDataSource.class, xQuerySup);
 333
 334         XStorable xStore = (XStorable)UnoRuntime.queryInterface(XStorable.class,xDs.getDatabaseDocument());
 335         xStore.store();
 336     }
 337
 338     // prints all column names from Query1
 339     public static void printQueryColumnNames() throws com.sun.star.uno.Exception
 340     {
 341         XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface(
 342             XNameAccess.class,
 343             xMCF.createInstanceWithContext("com.sun.star.sdb.DatabaseContext",
 344                                            xContext));
 345         // we use the first datasource
 346         XDataSource xDS = (XDataSource)UnoRuntime.queryInterface(
 347             XDataSource.class, xNameAccess.getByName( "Bibliography" ));
 348         XConnection con = xDS.getConnection("","");
 349         XQueriesSupplier xQuerySup = (XQueriesSupplier)
 350                                             UnoRuntime.queryInterface(XQueriesSupplier.class, con);
 351
 352         XNameAccess xQDefs = xQuerySup.getQueries();
 353
 354         XColumnsSupplier xColsSup = (XColumnsSupplier) UnoRuntime.queryInterface(
 355             XColumnsSupplier.class,xQDefs.getByName("Query1"));
 356         XNameAccess xCols = xColsSup.getColumns();
 357         String aNames [] = xCols.getElementNames();
 358         for(int i=0;i<aNames.length;++i)
 359             System.out.println(aNames[i]);
 360     }
 361 }
 362