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