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.XMultiServiceFactory;
29 //	import com.sun.star.lang.XServiceInfo;
30 import com.sun.star.lang.XComponent;
31 //	import com.sun.star.bridge.XUnoUrlResolver;
32 import com.sun.star.uno.*;
33 import com.sun.star.util.Date;
34 import com.sun.star.beans.XPropertySet;
35 import com.sun.star.container.XNameAccess;
36 import com.sun.star.sdbc.*;
37 
38 public class Sales
39 {
40 	private XConnection con;
41 
Sales(XConnection connection )42 	public Sales(XConnection connection )
43 	{
44 		con = connection;
45 	}
46 	// create the table sales.
createSalesTable()47 	public void createSalesTable() throws com.sun.star.uno.Exception
48 	{
49 		String createTableSales = "CREATE TABLE SALES " +
50 				"(SALENR INTEGER NOT NULL, " +
51 				" COS_NR INTEGER, " +
52 				" SNR INTEGER, " +
53 				" NAME VARCHAR(50)," +
54 				" SALEDATE DATE," +
55 				" PRICE FLOAT(10), " +
56 				" PRIMARY KEY(SALENR)" +
57 				" )";
58 		XStatement stmt = con.createStatement();
59 		stmt.executeUpdate( createTableSales );
60 	}
61 
62 	// drop the table sales.
dropSalesTable()63 	public void dropSalesTable() throws com.sun.star.uno.Exception
64 	{
65 		String createTableSalesman = "DROP TABLE SALES ";
66 		XStatement stmt = con.createStatement();
67 		stmt.executeUpdate( createTableSalesman );
68 	}
69 
70 	// insert data into the table sales.
insertDataIntoSales()71 	public void insertDataIntoSales() throws com.sun.star.uno.Exception
72 	{
73 		XStatement stmt = con.createStatement();
74 		stmt.executeUpdate("INSERT INTO SALES " +
75 				"VALUES (1, '100', '1','Linux','2001-02-12',15)");
76 		stmt.executeUpdate("INSERT INTO SALES " +
77 				"VALUES (2, '101', '2','Beef','2001-10-18',15.78)");
78 		stmt.executeUpdate("INSERT INTO SALES " +
79 				"VALUES (3, '104', '4','orange juice','2001-08-09',1.5)");
80 	}
81 
82 	// update the table sales with a prepared statement.
updateSales()83 	public void updateSales() throws com.sun.star.uno.Exception
84 	{
85 		XStatement stmt = con.createStatement();
86 		String updateString =	"UPDATE SALES " +
87 								"SET PRICE = 30 " +
88 								"WHERE SALENR = 1";
89 		stmt.executeUpdate(updateString);
90 	}
91 
92 	// retrieve the data of the table sales.
retrieveSalesData()93 	public void retrieveSalesData() throws com.sun.star.uno.Exception
94 	{
95 		XStatement stmt = con.createStatement();
96 		String query =	"SELECT NAME, PRICE FROM SALES " +
97 						"WHERE SALENR = 1";
98 		XResultSet rs = stmt.executeQuery(query);
99 		XRow      row = (XRow)UnoRuntime.queryInterface(XRow.class, rs);
100 		while (rs.next()) {
101 				String s = row.getString(1);
102 				float  n = row.getFloat(2);
103 				System.out.println("The current price for " + s + " is: $" + n + ".");
104 		}
105 	}
106 
107 	// create a scrollable resultset.
retrieveSalesData2()108 	public void retrieveSalesData2() throws com.sun.star.uno.Exception
109 	{
110 		// example for a programmatic way to do updates. This doesn't work with adabas.
111 		XStatement stmt = con.createStatement();
112 		XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,stmt);
113 
114 		xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
115 		xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE));
116 
117 		XResultSet srs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
118 		XRow       row = (XRow)UnoRuntime.queryInterface(XRow.class,srs);
119 
120 		srs.afterLast();
121 		while (srs.previous()) {
122 			String name = row.getString(1);
123 			float price = row.getFloat(2);
124 			System.out.println(name + "     " + price);
125 		}
126 
127 		srs.last();
128 		XRowUpdate updateRow = (XRowUpdate)UnoRuntime.queryInterface(XRowUpdate.class,srs);
129 		updateRow.updateFloat(2, (float)0.69);
130 
131 		XResultSetUpdate updateRs = ( XResultSetUpdate )UnoRuntime.queryInterface(
132             XResultSetUpdate.class,srs);
133 		updateRs.updateRow(); // this call updates the data in DBMS
134 
135 		srs.last();
136 		updateRow.updateFloat(2, (float)0.99);
137 		updateRs.cancelRowUpdates();
138 		updateRow.updateFloat(2, (float)0.79);
139 		updateRs.updateRow();
140 	}
141 
142 	// inserts a row programmatically.
insertRow()143 	public void insertRow() throws com.sun.star.uno.Exception
144 	{
145 		// example for a programmatic way to do updates. This doesn't work with adabas.
146 		XStatement stmt = con.createStatement();
147 //		stmt.executeUpdate("INSERT INTO SALES " +
148 //                   "VALUES (4, 102, 5, 'FTOP Darjeeling tea', '2002-01-02',150)");
149 //
150 //		stmt = con.createStatement();
151 		XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,stmt);
152 		xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
153 		xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE));
154 		XResultSet rs = stmt.executeQuery("SELECT * FROM SALES");
155 		XRow       row = (XRow)UnoRuntime.queryInterface(XRow.class,rs);
156 
157 		// insert a new row
158 		XRowUpdate updateRow = (XRowUpdate)UnoRuntime.queryInterface(XRowUpdate.class,rs);
159 		XResultSetUpdate updateRs = ( XResultSetUpdate )UnoRuntime.	queryInterface(XResultSetUpdate.class,rs);
160 		updateRs.moveToInsertRow();
161 		updateRow.updateInt(1, 4);
162 		updateRow.updateInt(2, 102);
163 		updateRow.updateInt(3, 5);
164 		updateRow.updateString(4, "FTOP Darjeeling tea");
165 		updateRow.updateDate(5, new Date((short)1,(short)2,(short)2002));
166 		updateRow.updateFloat(6, 150);
167 		updateRs.insertRow();
168 	}
169 
170 	// deletes a row programmatically.
deleteRow()171 	public void deleteRow() throws com.sun.star.uno.Exception
172 	{
173 		// example for a programmatic way to do updates. This doesn't work with adabas.
174 		XStatement stmt = con.createStatement();
175 		XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,stmt);
176 		xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
177 		xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE));
178 		XResultSet rs = stmt.executeQuery("SELECT * FROM SALES");
179 		XRow       row = (XRow)UnoRuntime.queryInterface(XRow.class,rs);
180 
181 		XResultSetUpdate updateRs = ( XResultSetUpdate )UnoRuntime.	queryInterface(XResultSetUpdate.class,rs);
182 		// move to the inserted row
183 		rs.absolute(4);
184 		updateRs.deleteRow();
185 	}
186 
187 	// refresh a row
refreshRow()188 	public void refreshRow() throws com.sun.star.uno.Exception
189 	{
190 		// example for a programmatic way to do updates. This doesn't work with adabas.
191 		// first we need the 4 row
192 		insertRow();
193 
194 		XStatement stmt = con.createStatement();
195 		XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,stmt);
196 		xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
197 		xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.READ_ONLY));
198 		XResultSet rs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
199 		XRow       row = (XRow)UnoRuntime.queryInterface(XRow.class, rs);
200 		rs.absolute(4);
201 		float price1 = row.getFloat(2);
202 
203 		// modifiy the 4 row
204 		XRowUpdate updateRow = (XRowUpdate)UnoRuntime.queryInterface(XRowUpdate.class,rs);
205 		XResultSetUpdate updateRs = ( XResultSetUpdate )UnoRuntime.	queryInterface(XResultSetUpdate.class,rs);
206 		updateRow.updateFloat(2, 150);
207 		updateRs.updateRow();
208 		// repositioning
209 		rs.absolute(4);
210 		rs.refreshRow();
211 		float price2 = row.getFloat(2);
212 		if (price2 != price1) {
213 			System.out.println("Prices are different.");
214 		}
215 		else
216 			System.out.println("Prices are equal.");
217 		deleteRow();
218 	}
219 
220 	// displays the column names
displayColumnNames()221 	public void displayColumnNames() throws com.sun.star.uno.Exception
222 	{
223 		XStatement stmt = con.createStatement();
224 		XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,stmt);
225 		xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
226 		xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.READ_ONLY));
227 		XResultSet rs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
228 		XResultSetMetaDataSupplier xRsMetaSup = (XResultSetMetaDataSupplier)
229             UnoRuntime.queryInterface(XResultSetMetaDataSupplier.class,rs);
230 		XResultSetMetaData xRsMetaData =  xRsMetaSup.getMetaData();
231 		int nColumnCount =  xRsMetaData.getColumnCount();
232 		for(int i=1 ; i <= nColumnCount ; ++i)
233 		{
234 		   System.out.println("Name: " + xRsMetaData.getColumnName(i) + " Type: " +
235                               xRsMetaData.getColumnType(i));
236 		}
237 	}
238 }
239 
240