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