odk/examples/DevelopersGuide/Database/CodeSamples.java

   1 /* -*- Mode: Java; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*************************************************************************
   3  *
   4  *  The Contents of this file are made available subject to the terms of
   5  *  the BSD license.
   6  *
   7  *  Copyright 2000, 2010 Oracle and/or its affiliates.
   8  *  All rights reserved.
   9  *
  10  *  Redistribution and use in source and binary forms, with or without
  11  *  modification, are permitted provided that the following conditions
  12  *  are met:
  13  *  1. Redistributions of source code must retain the above copyright
  14  *     notice, this list of conditions and the following disclaimer.
  15  *  2. Redistributions in binary form must reproduce the above copyright
  16  *     notice, this list of conditions and the following disclaimer in the
  17  *     documentation and/or other materials provided with the distribution.
  18  *  3. Neither the name of Sun Microsystems, Inc. nor the names of its
  19  *     contributors may be used to endorse or promote products derived
  20  *     from this software without specific prior written permission.
  21  *
  22  *  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
  23  *  "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
  24  *  LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
  25  *  FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
  26  *  COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
  27  *  INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
  28  *  BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
  29  *  OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  30  *  ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
  31  *  TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
  32  *  USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  33  *
  34  *************************************************************************/
  35
  36 import com.sun.star.lang.XMultiComponentFactory;
  37 import com.sun.star.lang.XSingleServiceFactory;
  38 import com.sun.star.lang.XComponent;
  39 import com.sun.star.uno.UnoRuntime;
  40 import com.sun.star.uno.XComponentContext;
  41 import com.sun.star.beans.XPropertySet;
  42 import com.sun.star.container.XNameAccess;
  43 import com.sun.star.container.XNameContainer;
  44 import com.sun.star.sdbc.*;
  45 import com.sun.star.sdb.*;
  46 import com.sun.star.sdbcx.*;
  47 import com.sun.star.frame.*;
  48
  49 public class CodeSamples
  50 {
  51     public static XComponentContext xContext;
  52     public static XMultiComponentFactory xMCF;
  53
  54     public static void main(String argv[]) throws java.lang.Exception
  55     {
  56         try {
  57             // get the remote office component context
  58             xContext = com.sun.star.comp.helper.Bootstrap.bootstrap();
  59             System.out.println("Connected to a running office ...");
  60             xMCF = xContext.getServiceManager();
  61         }
  62         catch(Exception e) {
  63             System.err.println("ERROR: can't get a component context from a running office ...");
  64             e.printStackTrace();
  65             System.exit(1);
  66         }
  67
  68         try{
  69             createQuerydefinition( );
  70             printQueryColumnNames( );
  71
  72             XConnection con = openConnectionWithDriverManager();
  73             if ( con != null ) {
  74                 {
  75                     SalesMan sm = new SalesMan( con );
  76
  77                     try {
  78                         sm.dropSalesManTable( ); // doesn't matter here
  79                     }
  80                     catch(com.sun.star.uno.Exception e)
  81                     {
  82                     }
  83                     sm.createSalesManTable( );
  84                     sm.insertDataIntoSalesMan( );
  85                     sm.updateSalesMan( );
  86                     sm.retrieveSalesManData( );
  87                 }
  88
  89                 {
  90                     Sales sm = new Sales( con );
  91
  92                     try {
  93                         sm.dropSalesTable( ); // doesn't matter here
  94                     }
  95                     catch(com.sun.star.uno.Exception e)
  96                     {
  97                     }
  98                     sm.createSalesTable( );
  99                     sm.insertDataIntoSales( );
 100                     sm.updateSales( );
 101                     sm.retrieveSalesData( );
 102                     sm.displayColumnNames( );
 103                 }
 104                 displayTableStructure( con );
 105             }
 106         }
 107         catch(Exception e)
 108         {
 109             System.err.println(e);
 110             e.printStackTrace();
 111         }
 112         System.exit(0);
 113     }
 114
 115     // check if the connection is not null and dispose it later on.
 116     public static void checkConnection(XConnection con)
 117     {
 118         if(con != null)
 119         {
 120             System.out.println("Connection was created!");
 121             // now we dispose the connection to close it
 122             XComponent xComponent = UnoRuntime.queryInterface(XComponent.class,con);
 123             if(xComponent != null)
 124             {
 125                 // connections must be disposed
 126                 xComponent.dispose();
 127                 System.out.println("Connection disposed!");
 128             }
 129         }
 130         else
 131             System.out.println("Connection could not be created!");
 132     }
 133
 134     // uses the driver manager to create a new connection and dispose it.
 135     public static XConnection openConnectionWithDriverManager() throws com.sun.star.uno.Exception
 136     {
 137         XConnection con = null;
 138         // create the DriverManager
 139         Object driverManager =
 140             xMCF.createInstanceWithContext("com.sun.star.sdbc.DriverManager",
 141                                            xContext);
 142         // query for the interface
 143         com.sun.star.sdbc.XDriverManager xDriverManager;
 144         xDriverManager = UnoRuntime.queryInterface(XDriverManager.class,driverManager);
 145         if(xDriverManager != null)
 146         {
 147             // first create the needed url
 148             String url = "jdbc:mysql://localhost:3306/TestTables";
 149             // second create the necessary properties
 150             com.sun.star.beans.PropertyValue [] props = new com.sun.star.beans.PropertyValue[]
 151             {
 152                 new com.sun.star.beans.PropertyValue("user",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE),
 153                 new com.sun.star.beans.PropertyValue("password",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE),
 154                 new com.sun.star.beans.PropertyValue("JavaDriverClass",0,"org.gjt.mm.mysql.Driver",com.sun.star.beans.PropertyState.DIRECT_VALUE)
 155             };
 156             // now create a connection to mysql
 157             con = xDriverManager.getConnectionWithInfo(url,props);
 158         }
 159         return con;
 160     }
 161
 162     // uses the driver directly to create a new connection and dispose it.
 163     public static XConnection openConnectionWithDriver() throws com.sun.star.uno.Exception
 164     {
 165         XConnection con = null;
 166         // create the Driver with the implementation name
 167         Object aDriver =
 168             xMCF.createInstanceWithContext("org.openoffice.comp.drivers.MySQL.Driver",
 169                                            xContext);
 170         // query for the interface
 171         com.sun.star.sdbc.XDriver xDriver;
 172         xDriver = UnoRuntime.queryInterface(XDriver.class,aDriver);
 173         if(xDriver != null)
 174         {
 175             // first create the needed url
 176             String url = "jdbc:mysql://localhost:3306/TestTables";
 177             // second create the necessary properties
 178             com.sun.star.beans.PropertyValue [] props = new com.sun.star.beans.PropertyValue[]
 179             {
 180                 new com.sun.star.beans.PropertyValue("user",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE),
 181                 new com.sun.star.beans.PropertyValue("password",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE),
 182                                 new com.sun.star.beans.PropertyValue("JavaDriverClass",0,"org.gjt.mm.mysql.Driver",com.sun.star.beans.PropertyState.DIRECT_VALUE)
 183             };
 184             // now create a connection to mysql
 185             con = xDriver.connect(url,props);
 186         }
 187         return con;
 188     }
 189
 190     // print all available datasources
 191     public static void printDataSources() throws com.sun.star.uno.Exception
 192     {
 193         // create a DatabaseContext and print all DataSource names
 194         XNameAccess xNameAccess = UnoRuntime.queryInterface(
 195             XNameAccess.class,
 196             xMCF.createInstanceWithContext("com.sun.star.sdb.DatabaseContext",
 197                                            xContext));
 198         String aNames [] = xNameAccess.getElementNames();
 199         for(int i=0;i<aNames.length;++i)
 200             System.out.println(aNames[i]);
 201     }
 202
 203     // displays the structure of the first table
 204     public static void displayTableStructure(XConnection con) throws com.sun.star.uno.Exception
 205     {
 206         XDatabaseMetaData dm = con.getMetaData();
 207         XResultSet rsTables = dm.getTables(null,"%","SALES",null);
 208         XRow       rowTB = UnoRuntime.queryInterface(XRow.class, rsTables);
 209         while ( rsTables.next() )
 210         {
 211             String catalog = rowTB.getString( 1 );
 212             if ( rowTB.wasNull() )
 213                 catalog = null;
 214
 215             String schema = rowTB.getString( 2 );
 216             if ( rowTB.wasNull() )
 217                 schema = null;
 218
 219             String table = rowTB.getString( 3 );
 220             String type = rowTB.getString( 4 );
 221             System.out.println("Catalog: " + catalog + " Schema: " + schema + " Table: " + table + " Type: " + type);
 222             System.out.println("------------------ Columns ------------------");
 223             XResultSet rsColumns = dm.getColumns(catalog,schema,table,"%");
 224             XRow       rowCL = UnoRuntime.queryInterface(XRow.class, rsColumns);
 225             while ( rsColumns.next() )
 226             {
 227                 System.out.println("Column: " + rowCL.getString( 4 ) + " Type: " + rowCL.getInt( 5 ) + " TypeName: " + rowCL.getString( 6 ) );
 228             }
 229
 230         }
 231     }
 232
 233     // quote the given name
 234     public static String quoteTableName(XConnection con, String sCatalog, String sSchema, String sTable) throws com.sun.star.uno.Exception
 235     {
 236         XDatabaseMetaData dbmd = con.getMetaData();
 237         String sQuoteString = dbmd.getIdentifierQuoteString();
 238         String sSeparator = ".";
 239         String sComposedName = "";
 240         String sCatalogSep = dbmd.getCatalogSeparator();
 241         if (0 != sCatalog.length() && dbmd.isCatalogAtStart() && 0 != sCatalogSep.length())
 242         {
 243             sComposedName += sCatalog;
 244             sComposedName += dbmd.getCatalogSeparator();
 245         }
 246         if (0 != sSchema.length())
 247         {
 248             sComposedName += sSchema;
 249             sComposedName += sSeparator;
 250             sComposedName += sTable;
 251         }
 252         else
 253                 {
 254             sComposedName += sTable;
 255         }
 256         if (0 != sCatalog.length() && !dbmd.isCatalogAtStart() && 0 != sCatalogSep.length())
 257         {
 258             sComposedName += dbmd.getCatalogSeparator();
 259             sComposedName += sCatalog;
 260         }
 261         return sComposedName;
 262     }
 263
 264     // creates a new query definition
 265     public static void createQuerydefinition() throws com.sun.star.uno.Exception
 266     {
 267         XNameAccess xNameAccess = UnoRuntime.queryInterface(
 268             XNameAccess.class,
 269             xMCF.createInstanceWithContext("com.sun.star.sdb.DatabaseContext",
 270                                            xContext));
 271         // we use the first datasource
 272         XQueryDefinitionsSupplier xQuerySup = UnoRuntime.queryInterface(XQueryDefinitionsSupplier.class,
 273         xNameAccess.getByName( "Bibliography" ));
 274         XNameAccess xQDefs = xQuerySup.getQueryDefinitions();
 275         // create new query definition
 276         XSingleServiceFactory xSingleFac =  UnoRuntime.queryInterface(XSingleServiceFactory.class, xQDefs);
 277
 278         XPropertySet xProp = UnoRuntime.queryInterface(
 279             XPropertySet.class,xSingleFac.createInstance());
 280         xProp.setPropertyValue("Command","SELECT * FROM biblio");
 281         xProp.setPropertyValue("EscapeProcessing",Boolean.TRUE);
 282
 283         XNameContainer xCont = UnoRuntime.queryInterface(XNameContainer.class, xQDefs);
 284                 try
 285                 {
 286                     if ( xCont.hasByName("Query1") )
 287                         xCont.removeByName("Query1");
 288                 }
 289                 catch(com.sun.star.uno.Exception e)
 290                 {}
 291         xCont.insertByName("Query1",xProp);
 292         XDocumentDataSource xDs = UnoRuntime.queryInterface(XDocumentDataSource.class, xQuerySup);
 293
 294         XStorable xStore = UnoRuntime.queryInterface(XStorable.class,xDs.getDatabaseDocument());
 295         xStore.store();
 296     }
 297
 298     // prints all column names from Query1
 299     public static void printQueryColumnNames() throws com.sun.star.uno.Exception
 300     {
 301         XNameAccess xNameAccess = UnoRuntime.queryInterface(
 302             XNameAccess.class,
 303             xMCF.createInstanceWithContext("com.sun.star.sdb.DatabaseContext",
 304                                            xContext));
 305         // we use the first datasource
 306         XDataSource xDS = UnoRuntime.queryInterface(
 307             XDataSource.class, xNameAccess.getByName( "Bibliography" ));
 308         XConnection con = xDS.getConnection("","");
 309         XQueriesSupplier xQuerySup = UnoRuntime.queryInterface(XQueriesSupplier.class, con);
 310
 311         XNameAccess xQDefs = xQuerySup.getQueries();
 312
 313         XColumnsSupplier xColsSup = UnoRuntime.queryInterface(
 314             XColumnsSupplier.class,xQDefs.getByName("Query1"));
 315         XNameAccess xCols = xColsSup.getColumns();
 316         String aNames [] = xCols.getElementNames();
 317         for(int i=0;i<aNames.length;++i)
 318             System.out.println(aNames[i]);
 319     }
 320 }
 321
 322 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */