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 main(String argv[])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. checkConnection(XConnection con)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. openConnectionWithDriverManager()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. openConnectionWithDriver()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 printDataSources()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 displayTableStructure(XConnection con)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 quoteTableName(XConnection con, String sCatalog, String sSchema, String sTable)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 createQuerydefinition()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 printQueryColumnNames()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: */ 323