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