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