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