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