1*34dd1e25SAndrew Rist /************************************************************** 2*34dd1e25SAndrew Rist * 3*34dd1e25SAndrew Rist * Licensed to the Apache Software Foundation (ASF) under one 4*34dd1e25SAndrew Rist * or more contributor license agreements. See the NOTICE file 5*34dd1e25SAndrew Rist * distributed with this work for additional information 6*34dd1e25SAndrew Rist * regarding copyright ownership. The ASF licenses this file 7*34dd1e25SAndrew Rist * to you under the Apache License, Version 2.0 (the 8*34dd1e25SAndrew Rist * "License"); you may not use this file except in compliance 9*34dd1e25SAndrew Rist * with the License. You may obtain a copy of the License at 10*34dd1e25SAndrew Rist * 11*34dd1e25SAndrew Rist * http://www.apache.org/licenses/LICENSE-2.0 12*34dd1e25SAndrew Rist * 13*34dd1e25SAndrew Rist * Unless required by applicable law or agreed to in writing, 14*34dd1e25SAndrew Rist * software distributed under the License is distributed on an 15*34dd1e25SAndrew Rist * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16*34dd1e25SAndrew Rist * KIND, either express or implied. See the License for the 17*34dd1e25SAndrew Rist * specific language governing permissions and limitations 18*34dd1e25SAndrew Rist * under the License. 19*34dd1e25SAndrew Rist * 20*34dd1e25SAndrew Rist *************************************************************/ 21*34dd1e25SAndrew Rist 22*34dd1e25SAndrew Rist 23cdf0e10cSrcweir 24cdf0e10cSrcweir import java.io.*; 25cdf0e10cSrcweir 26cdf0e10cSrcweir import com.sun.star.comp.helper.RegistryServiceFactory; 27cdf0e10cSrcweir import com.sun.star.comp.servicemanager.ServiceManager; 28cdf0e10cSrcweir import com.sun.star.lang.XMultiComponentFactory; 29cdf0e10cSrcweir import com.sun.star.lang.XSingleServiceFactory; 30cdf0e10cSrcweir import com.sun.star.lang.XServiceInfo; 31cdf0e10cSrcweir import com.sun.star.lang.XComponent; 32cdf0e10cSrcweir import com.sun.star.bridge.XUnoUrlResolver; 33cdf0e10cSrcweir import com.sun.star.uno.UnoRuntime; 34cdf0e10cSrcweir import com.sun.star.uno.XComponentContext; 35cdf0e10cSrcweir import com.sun.star.beans.XPropertySet; 36cdf0e10cSrcweir import com.sun.star.container.XNameAccess; 37cdf0e10cSrcweir import com.sun.star.container.XNameContainer; 38cdf0e10cSrcweir import com.sun.star.sdbc.*; 39cdf0e10cSrcweir import com.sun.star.sdb.*; 40cdf0e10cSrcweir import com.sun.star.sdbcx.*; 41cdf0e10cSrcweir import com.sun.star.frame.*; 42cdf0e10cSrcweir 43cdf0e10cSrcweir public class CodeSamples 44cdf0e10cSrcweir { 45cdf0e10cSrcweir public static XComponentContext xContext; 46cdf0e10cSrcweir public static XMultiComponentFactory xMCF; 47cdf0e10cSrcweir main(String argv[])48cdf0e10cSrcweir public static void main(String argv[]) throws java.lang.Exception 49cdf0e10cSrcweir { 50cdf0e10cSrcweir try { 51cdf0e10cSrcweir // get the remote office component context 52cdf0e10cSrcweir xContext = com.sun.star.comp.helper.Bootstrap.bootstrap(); 53cdf0e10cSrcweir System.out.println("Connected to a running office ..."); 54cdf0e10cSrcweir xMCF = xContext.getServiceManager(); 55cdf0e10cSrcweir } 56cdf0e10cSrcweir catch(Exception e) { 57cdf0e10cSrcweir System.err.println("ERROR: can't get a component context from a running office ..."); 58cdf0e10cSrcweir e.printStackTrace(); 59cdf0e10cSrcweir System.exit(1); 60cdf0e10cSrcweir } 61cdf0e10cSrcweir 62cdf0e10cSrcweir try{ 63cdf0e10cSrcweir createQuerydefinition( ); 64cdf0e10cSrcweir printQueryColumnNames( ); 65cdf0e10cSrcweir 66cdf0e10cSrcweir XConnection con = openConnectionWithDriverManager(); 67cdf0e10cSrcweir if ( con != null ) { 68cdf0e10cSrcweir { 69cdf0e10cSrcweir SalesMan sm = new SalesMan( con ); 70cdf0e10cSrcweir 71cdf0e10cSrcweir try { 72cdf0e10cSrcweir sm.dropSalesManTable( ); // doesn't matter here 73cdf0e10cSrcweir } 74cdf0e10cSrcweir catch(com.sun.star.uno.Exception e) 75cdf0e10cSrcweir { 76cdf0e10cSrcweir } 77cdf0e10cSrcweir sm.createSalesManTable( ); 78cdf0e10cSrcweir sm.insertDataIntoSalesMan( ); 79cdf0e10cSrcweir sm.updateSalesMan( ); 80cdf0e10cSrcweir sm.retrieveSalesManData( ); 81cdf0e10cSrcweir } 82cdf0e10cSrcweir 83cdf0e10cSrcweir { 84cdf0e10cSrcweir Sales sm = new Sales( con ); 85cdf0e10cSrcweir 86cdf0e10cSrcweir try { 87cdf0e10cSrcweir sm.dropSalesTable( ); // doesn't matter here 88cdf0e10cSrcweir } 89cdf0e10cSrcweir catch(com.sun.star.uno.Exception e) 90cdf0e10cSrcweir { 91cdf0e10cSrcweir } 92cdf0e10cSrcweir sm.createSalesTable( ); 93cdf0e10cSrcweir sm.insertDataIntoSales( ); 94cdf0e10cSrcweir sm.updateSales( ); 95cdf0e10cSrcweir sm.retrieveSalesData( ); 96cdf0e10cSrcweir sm.displayColumnNames( ); 97cdf0e10cSrcweir } 98cdf0e10cSrcweir displayTableStructure( con ); 99cdf0e10cSrcweir } 100cdf0e10cSrcweir // printDataSources(); 101cdf0e10cSrcweir } 102cdf0e10cSrcweir catch(Exception e) 103cdf0e10cSrcweir { 104cdf0e10cSrcweir System.err.println(e); 105cdf0e10cSrcweir e.printStackTrace(); 106cdf0e10cSrcweir } 107cdf0e10cSrcweir System.exit(0); 108cdf0e10cSrcweir } 109cdf0e10cSrcweir 110cdf0e10cSrcweir // check if the connection is not null aand dispose it later on. checkConnection(XConnection con)111cdf0e10cSrcweir public static void checkConnection(XConnection con) throws com.sun.star.uno.Exception 112cdf0e10cSrcweir { 113cdf0e10cSrcweir if(con != null) 114cdf0e10cSrcweir { 115cdf0e10cSrcweir System.out.println("Connection was created!"); 116cdf0e10cSrcweir // now we dispose the connection to close it 117cdf0e10cSrcweir XComponent xComponent = (XComponent)UnoRuntime.queryInterface(XComponent.class,con); 118cdf0e10cSrcweir if(xComponent != null) 119cdf0e10cSrcweir { 120cdf0e10cSrcweir // connections must be disposed 121cdf0e10cSrcweir xComponent.dispose(); 122cdf0e10cSrcweir System.out.println("Connection disposed!"); 123cdf0e10cSrcweir } 124cdf0e10cSrcweir } 125cdf0e10cSrcweir else 126cdf0e10cSrcweir System.out.println("Connection could not be created!"); 127cdf0e10cSrcweir } 128cdf0e10cSrcweir 129cdf0e10cSrcweir // uses the driver manager to create a new connection and dispose it. openConnectionWithDriverManager()130cdf0e10cSrcweir public static XConnection openConnectionWithDriverManager() throws com.sun.star.uno.Exception 131cdf0e10cSrcweir { 132cdf0e10cSrcweir XConnection con = null; 133cdf0e10cSrcweir // create the DriverManager 134cdf0e10cSrcweir Object driverManager = 135cdf0e10cSrcweir xMCF.createInstanceWithContext("com.sun.star.sdbc.DriverManager", 136cdf0e10cSrcweir xContext); 137cdf0e10cSrcweir // query for the interface 138cdf0e10cSrcweir com.sun.star.sdbc.XDriverManager xDriverManager; 139cdf0e10cSrcweir xDriverManager = (XDriverManager)UnoRuntime.queryInterface(XDriverManager.class,driverManager); 140cdf0e10cSrcweir if(xDriverManager != null) 141cdf0e10cSrcweir { 142cdf0e10cSrcweir // first create the needed url 143cdf0e10cSrcweir String url = "jdbc:mysql://localhost:3306/TestTables"; 144cdf0e10cSrcweir // second create the necessary properties 145cdf0e10cSrcweir com.sun.star.beans.PropertyValue [] props = new com.sun.star.beans.PropertyValue[] 146cdf0e10cSrcweir { 147cdf0e10cSrcweir new com.sun.star.beans.PropertyValue("user",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE), 148cdf0e10cSrcweir new com.sun.star.beans.PropertyValue("password",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE), 149cdf0e10cSrcweir new com.sun.star.beans.PropertyValue("JavaDriverClass",0,"org.gjt.mm.mysql.Driver",com.sun.star.beans.PropertyState.DIRECT_VALUE) 150cdf0e10cSrcweir }; 151cdf0e10cSrcweir // now create a connection to mysql 152cdf0e10cSrcweir con = xDriverManager.getConnectionWithInfo(url,props); 153cdf0e10cSrcweir } 154cdf0e10cSrcweir return con; 155cdf0e10cSrcweir } 156cdf0e10cSrcweir 157cdf0e10cSrcweir // uses the driver directly to create a new connection and dispose it. openConnectionWithDriver()158cdf0e10cSrcweir public static XConnection openConnectionWithDriver() throws com.sun.star.uno.Exception 159cdf0e10cSrcweir { 160cdf0e10cSrcweir XConnection con = null; 161cdf0e10cSrcweir // create the Driver with the implementation name 162cdf0e10cSrcweir Object aDriver = 163cdf0e10cSrcweir xMCF.createInstanceWithContext("org.openoffice.comp.drivers.MySQL.Driver", 164cdf0e10cSrcweir xContext); 165cdf0e10cSrcweir // query for the interface 166cdf0e10cSrcweir com.sun.star.sdbc.XDriver xDriver; 167cdf0e10cSrcweir xDriver = (XDriver)UnoRuntime.queryInterface(XDriver.class,aDriver); 168cdf0e10cSrcweir if(xDriver != null) 169cdf0e10cSrcweir { 170cdf0e10cSrcweir // first create the needed url 171cdf0e10cSrcweir String url = "jdbc:mysql://localhost:3306/TestTables"; 172cdf0e10cSrcweir // second create the necessary properties 173cdf0e10cSrcweir com.sun.star.beans.PropertyValue [] props = new com.sun.star.beans.PropertyValue[] 174cdf0e10cSrcweir { 175cdf0e10cSrcweir new com.sun.star.beans.PropertyValue("user",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE), 176cdf0e10cSrcweir new com.sun.star.beans.PropertyValue("password",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE), 177cdf0e10cSrcweir new com.sun.star.beans.PropertyValue("JavaDriverClass",0,"org.gjt.mm.mysql.Driver",com.sun.star.beans.PropertyState.DIRECT_VALUE) 178cdf0e10cSrcweir }; 179cdf0e10cSrcweir // now create a connection to mysql 180cdf0e10cSrcweir con = xDriver.connect(url,props); 181cdf0e10cSrcweir } 182cdf0e10cSrcweir return con; 183cdf0e10cSrcweir } 184cdf0e10cSrcweir 185cdf0e10cSrcweir // print all available datasources printDataSources()186cdf0e10cSrcweir public static void printDataSources() throws com.sun.star.uno.Exception 187cdf0e10cSrcweir { 188cdf0e10cSrcweir // create a DatabaseContext and print all DataSource names 189cdf0e10cSrcweir XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface( 190cdf0e10cSrcweir XNameAccess.class, 191cdf0e10cSrcweir xMCF.createInstanceWithContext("com.sun.star.sdb.DatabaseContext", 192cdf0e10cSrcweir xContext)); 193cdf0e10cSrcweir String aNames [] = xNameAccess.getElementNames(); 194cdf0e10cSrcweir for(int i=0;i<aNames.length;++i) 195cdf0e10cSrcweir System.out.println(aNames[i]); 196cdf0e10cSrcweir } 197cdf0e10cSrcweir 198cdf0e10cSrcweir // displays the structure of the first table displayTableStructure(XConnection con)199cdf0e10cSrcweir public static void displayTableStructure(XConnection con) throws com.sun.star.uno.Exception 200cdf0e10cSrcweir { 201cdf0e10cSrcweir XDatabaseMetaData dm = con.getMetaData(); 202cdf0e10cSrcweir XResultSet rsTables = dm.getTables(null,"%","SALES",null); 203cdf0e10cSrcweir XRow rowTB = (XRow)UnoRuntime.queryInterface(XRow.class, rsTables); 204cdf0e10cSrcweir while ( rsTables.next() ) 205cdf0e10cSrcweir { 206cdf0e10cSrcweir String catalog = rowTB.getString( 1 ); 207cdf0e10cSrcweir if ( rowTB.wasNull() ) 208cdf0e10cSrcweir catalog = null; 209cdf0e10cSrcweir 210cdf0e10cSrcweir String schema = rowTB.getString( 2 ); 211cdf0e10cSrcweir if ( rowTB.wasNull() ) 212cdf0e10cSrcweir schema = null; 213cdf0e10cSrcweir 214cdf0e10cSrcweir String table = rowTB.getString( 3 ); 215cdf0e10cSrcweir String type = rowTB.getString( 4 ); 216cdf0e10cSrcweir System.out.println("Catalog: " + catalog + " Schema: " + schema + " Table: " + table + " Type: " + type); 217cdf0e10cSrcweir System.out.println("------------------ Columns ------------------"); 218cdf0e10cSrcweir XResultSet rsColumns = dm.getColumns(catalog,schema,table,"%"); 219cdf0e10cSrcweir XRow rowCL = (XRow)UnoRuntime.queryInterface(XRow.class, rsColumns); 220cdf0e10cSrcweir while ( rsColumns.next() ) 221cdf0e10cSrcweir { 222cdf0e10cSrcweir System.out.println("Column: " + rowCL.getString( 4 ) + " Type: " + rowCL.getInt( 5 ) + " TypeName: " + rowCL.getString( 6 ) ); 223cdf0e10cSrcweir } 224cdf0e10cSrcweir 225cdf0e10cSrcweir } 226cdf0e10cSrcweir } 227cdf0e10cSrcweir 228cdf0e10cSrcweir // quote the given name quoteTableName(XConnection con, String sCatalog, String sSchema, String sTable)229cdf0e10cSrcweir public static String quoteTableName(XConnection con, String sCatalog, String sSchema, String sTable) throws com.sun.star.uno.Exception 230cdf0e10cSrcweir { 231cdf0e10cSrcweir XDatabaseMetaData dbmd = con.getMetaData(); 232cdf0e10cSrcweir String sQuoteString = dbmd.getIdentifierQuoteString(); 233cdf0e10cSrcweir String sSeparator = "."; 234cdf0e10cSrcweir String sComposedName = ""; 235cdf0e10cSrcweir String sCatalogSep = dbmd.getCatalogSeparator(); 236cdf0e10cSrcweir if (0 != sCatalog.length() && dbmd.isCatalogAtStart() && 0 != sCatalogSep.length()) 237cdf0e10cSrcweir { 238cdf0e10cSrcweir sComposedName += sCatalog; 239cdf0e10cSrcweir sComposedName += dbmd.getCatalogSeparator(); 240cdf0e10cSrcweir } 241cdf0e10cSrcweir if (0 != sSchema.length()) 242cdf0e10cSrcweir { 243cdf0e10cSrcweir sComposedName += sSchema; 244cdf0e10cSrcweir sComposedName += sSeparator; 245cdf0e10cSrcweir sComposedName += sTable; 246cdf0e10cSrcweir } 247cdf0e10cSrcweir else 248cdf0e10cSrcweir { 249cdf0e10cSrcweir sComposedName += sTable; 250cdf0e10cSrcweir } 251cdf0e10cSrcweir if (0 != sCatalog.length() && !dbmd.isCatalogAtStart() && 0 != sCatalogSep.length()) 252cdf0e10cSrcweir { 253cdf0e10cSrcweir sComposedName += dbmd.getCatalogSeparator(); 254cdf0e10cSrcweir sComposedName += sCatalog; 255cdf0e10cSrcweir } 256cdf0e10cSrcweir return sComposedName; 257cdf0e10cSrcweir } 258cdf0e10cSrcweir 259cdf0e10cSrcweir // creates a new query definition createQuerydefinition()260cdf0e10cSrcweir public static void createQuerydefinition() throws com.sun.star.uno.Exception 261cdf0e10cSrcweir { 262cdf0e10cSrcweir XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface( 263cdf0e10cSrcweir XNameAccess.class, 264cdf0e10cSrcweir xMCF.createInstanceWithContext("com.sun.star.sdb.DatabaseContext", 265cdf0e10cSrcweir xContext)); 266cdf0e10cSrcweir // we use the first datasource 267cdf0e10cSrcweir XQueryDefinitionsSupplier xQuerySup = (XQueryDefinitionsSupplier) 268cdf0e10cSrcweir UnoRuntime.queryInterface(XQueryDefinitionsSupplier.class, 269cdf0e10cSrcweir xNameAccess.getByName( "Bibliography" )); 270cdf0e10cSrcweir XNameAccess xQDefs = xQuerySup.getQueryDefinitions(); 271cdf0e10cSrcweir // create new query definition 272cdf0e10cSrcweir XSingleServiceFactory xSingleFac = (XSingleServiceFactory) 273cdf0e10cSrcweir UnoRuntime.queryInterface(XSingleServiceFactory.class, xQDefs); 274cdf0e10cSrcweir 275cdf0e10cSrcweir XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface( 276cdf0e10cSrcweir XPropertySet.class,xSingleFac.createInstance()); 277cdf0e10cSrcweir xProp.setPropertyValue("Command","SELECT * FROM biblio"); 278cdf0e10cSrcweir xProp.setPropertyValue("EscapeProcessing",new Boolean(true)); 279cdf0e10cSrcweir 280cdf0e10cSrcweir XNameContainer xCont = (XNameContainer) UnoRuntime.queryInterface(XNameContainer.class, xQDefs); 281cdf0e10cSrcweir try 282cdf0e10cSrcweir { 283cdf0e10cSrcweir if ( xCont.hasByName("Query1") ) 284cdf0e10cSrcweir xCont.removeByName("Query1"); 285cdf0e10cSrcweir } 286cdf0e10cSrcweir catch(com.sun.star.uno.Exception e) 287cdf0e10cSrcweir {} 288cdf0e10cSrcweir xCont.insertByName("Query1",xProp); 289cdf0e10cSrcweir XDocumentDataSource xDs = (XDocumentDataSource)UnoRuntime.queryInterface(XDocumentDataSource.class, xQuerySup); 290cdf0e10cSrcweir 291cdf0e10cSrcweir XStorable xStore = (XStorable)UnoRuntime.queryInterface(XStorable.class,xDs.getDatabaseDocument()); 292cdf0e10cSrcweir xStore.store(); 293cdf0e10cSrcweir } 294cdf0e10cSrcweir 295cdf0e10cSrcweir // prints all column names from Query1 printQueryColumnNames()296cdf0e10cSrcweir public static void printQueryColumnNames() throws com.sun.star.uno.Exception 297cdf0e10cSrcweir { 298cdf0e10cSrcweir XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface( 299cdf0e10cSrcweir XNameAccess.class, 300cdf0e10cSrcweir xMCF.createInstanceWithContext("com.sun.star.sdb.DatabaseContext", 301cdf0e10cSrcweir xContext)); 302cdf0e10cSrcweir // we use the first datasource 303cdf0e10cSrcweir XDataSource xDS = (XDataSource)UnoRuntime.queryInterface( 304cdf0e10cSrcweir XDataSource.class, xNameAccess.getByName( "Bibliography" )); 305cdf0e10cSrcweir XConnection con = xDS.getConnection("",""); 306cdf0e10cSrcweir XQueriesSupplier xQuerySup = (XQueriesSupplier) 307cdf0e10cSrcweir UnoRuntime.queryInterface(XQueriesSupplier.class, con); 308cdf0e10cSrcweir 309cdf0e10cSrcweir XNameAccess xQDefs = xQuerySup.getQueries(); 310cdf0e10cSrcweir 311cdf0e10cSrcweir XColumnsSupplier xColsSup = (XColumnsSupplier) UnoRuntime.queryInterface( 312cdf0e10cSrcweir XColumnsSupplier.class,xQDefs.getByName("Query1")); 313cdf0e10cSrcweir XNameAccess xCols = xColsSup.getColumns(); 314cdf0e10cSrcweir String aNames [] = xCols.getElementNames(); 315cdf0e10cSrcweir for(int i=0;i<aNames.length;++i) 316cdf0e10cSrcweir System.out.println(aNames[i]); 317cdf0e10cSrcweir } 318cdf0e10cSrcweir } 319cdf0e10cSrcweir 320