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