1<?xml version="1.0" encoding="UTF-8" standalone="no"?> 2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 3<html xmlns="http://www.w3.org/1999/xhtml"> 4 <head> 5 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> 6 <title>Database Joins</title> 7 <link rel="stylesheet" href="gettingStarted.css" type="text/css" /> 8 <meta name="generator" content="DocBook XSL Stylesheets V1.73.2" /> 9 <link rel="start" href="index.html" title="Getting Started with Berkeley DB" /> 10 <link rel="up" href="indexes.html" title="Chapter��10.��Secondary Databases" /> 11 <link rel="prev" href="secondaryCursor.html" title="Using Secondary Cursors" /> 12 <link rel="next" href="javaindexusage.html" title="Secondary Database Example" /> 13 </head> 14 <body> 15 <div class="navheader"> 16 <table width="100%" summary="Navigation header"> 17 <tr> 18 <th colspan="3" align="center">Database Joins</th> 19 </tr> 20 <tr> 21 <td width="20%" align="left"><a accesskey="p" href="secondaryCursor.html">Prev</a>��</td> 22 <th width="60%" align="center">Chapter��10.��Secondary Databases</th> 23 <td width="20%" align="right">��<a accesskey="n" href="javaindexusage.html">Next</a></td> 24 </tr> 25 </table> 26 <hr /> 27 </div> 28 <div class="sect1" lang="en" xml:lang="en"> 29 <div class="titlepage"> 30 <div> 31 <div> 32 <h2 class="title" style="clear: both"><a id="joins"></a>Database Joins</h2> 33 </div> 34 </div> 35 </div> 36 <div class="toc"> 37 <dl> 38 <dt> 39 <span class="sect2"> 40 <a href="joins.html#joinUsage">Using Join Cursors</a> 41 </span> 42 </dt> 43 <dt> 44 <span class="sect2"> 45 <a href="joins.html#joinconfig">JoinCursor Properties</a> 46 </span> 47 </dt> 48 </dl> 49 </div> 50 <p> 51 If you have two or more secondary databases associated with a primary 52 database, then you can retrieve primary records based on the intersection of 53 multiple secondary entries. You do this using a 54 <span><code class="classname">JoinCursor</code>.</span> 55 56 </p> 57 <p> 58 Throughout this document we have presented a 59 <span>class</span> 60 61 that stores 62 <span>inventory</span> 63 information on grocery 64 65 66 That 67 <span>class</span> 68 69 is fairly simple with a limited 70 number of data members, few of which would be interesting from a query 71 perspective. But suppose, instead, that we were storing 72 information on something with many more characteristics that can be queried, such 73 as an automobile. In that case, you may be storing information such as 74 color, number of doors, fuel mileage, automobile type, number of 75 passengers, make, model, and year, to name just a few. 76 </p> 77 <p> 78 In this case, you would still likely be using some unique value to key your 79 primary entries (in the United States, the automobile's VIN would be 80 ideal for this purpose). You would then create a 81 <span>class</span> 82 83 that identifies 84 all the characteristics of the automobiles in your inventory. 85 86 <span> 87 You would 88 also have to create some mechanism by which you would move instances of 89 this class in and out of Java <code class="literal">byte</code> arrays. We 90 described the concepts and mechanisms by which you can perform these 91 activities in <a class="xref" href="DBEntry.html" title="Chapter��8.��Database Records">Database Records</a>. 92 </span> 93 </p> 94 <p> 95 To query this data, you might then create multiple secondary databases, 96 one for each of the characteristics that you want to query. For 97 example, you might create a secondary for color, another for number of 98 doors, another for number of passengers, and so forth. Of course, you 99 will need a unique 100 <span>key creator</span> 101 102 for each such secondary database. You do 103 all of this using the concepts and techniques described throughout this 104 chapter. 105 </p> 106 <p> 107 Once you have created this primary database and all interesting 108 secondaries, what you have is the ability to retrieve automobile records 109 based on a single characteristic. You can, for example, find all the 110 automobiles that are red. Or you can find all the automobiles that have 111 four doors. Or all the automobiles that are minivans. 112 </p> 113 <p> 114 The next most natural step, then, is to form compound queries, or joins. 115 For example, you might want to find all the automobiles that are red, 116 and that were built by Toyota, and that are minivans. You can do this 117 using a 118 <span><code class="classname">JoinCursor</code> class instance.</span> 119 120 </p> 121 <div class="sect2" lang="en" xml:lang="en"> 122 <div class="titlepage"> 123 <div> 124 <div> 125 <h3 class="title"><a id="joinUsage"></a>Using Join Cursors</h3> 126 </div> 127 </div> 128 </div> 129 <p> 130 To use a join cursor: 131 </p> 132 <div class="itemizedlist"> 133 <ul type="disc"> 134 <li> 135 <p> 136 Open two or more 137 <span>secondary cursors. These </span> 138 cursors 139 140 <span>for</span> 141 secondary databases that are associated with 142 the same primary database. 143 </p> 144 </li> 145 <li> 146 <p> 147 Position each such cursor to the secondary key 148 value in which you are interested. For example, to build on 149 the previous description, the cursor for the color 150 database is positioned to the <code class="literal">red</code> records 151 while the cursor for the model database is positioned to the 152 <code class="literal">minivan</code> records, and the cursor for the 153 make database is positioned to <code class="literal">Toyota</code>. 154 </p> 155 </li> 156 <li> 157 <p> 158 159 Create an array of <span>secondary</span> cursors, and 160 place in it each of the cursors that are participating in your join query. 161 162 163 </p> 164 </li> 165 <li> 166 <p> 167 168 Obtain a join cursor. You do this using the 169 <code class="methodname">Database.join()</code> 170 171 172 method. You must pass this method the array of secondary cursors that you 173 opened and positioned in the previous steps. 174 </p> 175 </li> 176 <li> 177 <p> 178 Iterate over the set of matching records 179 <span>using <code class="methodname">JoinCursor.getNext()</code></span> 180 until 181 <span><code class="classname">OperationStatus</code> is not <code class="literal">SUCCESS</code>.</span> 182 183 </p> 184 </li> 185 <li> 186 <p> 187 Close your <span>join</span> cursor. 188 </p> 189 </li> 190 <li> 191 <p> 192 If you are done with them, close all your <span>secondary</span> cursors. 193 </p> 194 </li> 195 </ul> 196 </div> 197 <p> 198 For example: 199 </p> 200 <a id="java_index9"></a> 201 <pre class="programlisting">package db.GettingStarted; 202 203import com.sleepycat.db.Database; 204import com.sleepycat.db.DatabaseEntry; 205import com.sleepycat.db.DatabaseException; 206import com.sleepycat.db.JoinCursor; 207import com.sleepycat.db.LockMode; 208import com.sleepycat.db.OperationStatus; 209import com.sleepycat.db.SecondaryCursor; 210import com.sleepycat.db.SecondaryDatabase; 211 212... 213 214// Database and secondary database opens omitted for brevity. 215// Assume a primary database handle: 216// automotiveDB 217// Assume 3 secondary database handles: 218// automotiveColorDB -- index based on automobile color 219// automotiveTypeDB -- index based on automobile type 220// automotiveMakeDB -- index based on the manufacturer 221Database automotiveDB = null; 222SecondaryDatabase automotiveColorDB = null; 223SecondaryDatabase automotiveTypeDB = null; 224SecondaryDatabase automotiveMakeDB = null; 225 226// Query strings: 227String theColor = "red"; 228String theType = "minivan"; 229String theMake = "Toyota"; 230 231// Secondary cursors used for the query: 232SecondaryCursor colorSecCursor = null; 233SecondaryCursor typeSecCursor = null; 234SecondaryCursor makeSecCursor = null; 235 236// The join cursor 237JoinCursor joinCursor = null; 238 239// These are needed for our queries 240DatabaseEntry foundKey = new DatabaseEntry(); 241DatabaseEntry foundData = new DatabaseEntry(); 242 243// All cursor operations are enclosed in a try block to ensure that they 244// get closed in the event of an exception. 245 246try { 247 // Database entries used for the query: 248 DatabaseEntry color = new DatabaseEntry(theColor.getBytes("UTF-8")); 249 DatabaseEntry type = new DatabaseEntry(theType.getBytes("UTF-8")); 250 DatabaseEntry make = new DatabaseEntry(theMake.getBytes("UTF-8")); 251 252 colorSecCursor = automotiveColorDB.openSecondaryCursor(null, null); 253 typeSecCursor = automotiveTypeDB.openSecondaryCursor(null, null); 254 makeSecCursor = automotiveMakeDB.openSecondaryCursor(null, null); 255 256 // Position all our secondary cursors to our query values. 257 OperationStatus colorRet = 258 colorSecCursor.getSearchKey(color, foundData, LockMode.DEFAULT); 259 OperationStatus typeRet = 260 typeSecCursor.getSearchKey(type, foundData, LockMode.DEFAULT); 261 OperationStatus makeRet = 262 makeSecCursor.getSearchKey(make, foundData, LockMode.DEFAULT); 263 264 // If all our searches returned successfully, we can proceed 265 if (colorRet == OperationStatus.SUCCESS && 266 typeRet == OperationStatus.SUCCESS && 267 makeRet == OperationStatus.SUCCESS) { 268 269 // Get a secondary cursor array and populate it with our 270 // positioned cursors 271 SecondaryCursor[] cursorArray = {colorSecCursor, 272 typeSecCursor, 273 makeSecCursor}; 274 275 // Create the join cursor 276 joinCursor = automotiveDB.join(cursorArray, null); 277 278 // Now iterate over the results, handling each in turn 279 while (joinCursor.getNext(foundKey, foundData, LockMode.DEFAULT) == 280 OperationStatus.SUCCESS) { 281 282 // Do something with the key and data retrieved in 283 // foundKey and foundData 284 } 285 } 286} catch (DatabaseException dbe) { 287 // Error reporting goes here 288} catch (Exception e) { 289 // Error reporting goes here 290} finally { 291 try { 292 // Make sure to close out all our cursors 293 if (colorSecCursor != null) { 294 colorSecCursor.close(); 295 } 296 if (typeSecCursor != null) { 297 typeSecCursor.close(); 298 } 299 if (makeSecCursor != null) { 300 makeSecCursor.close(); 301 } 302 if (joinCursor != null) { 303 joinCursor.close(); 304 } 305 } catch (DatabaseException dbe) { 306 // Error reporting goes here 307 } 308} </pre> 309 </div> 310 <div class="sect2" lang="en" xml:lang="en"> 311 <div class="titlepage"> 312 <div> 313 <div> 314 <h3 class="title"><a id="joinconfig"></a>JoinCursor Properties</h3> 315 </div> 316 </div> 317 </div> 318 <p> 319 You can set <code class="classname">JoinCursor</code> properties using the 320 <code class="classname">JoinConfig</code> class. Currently there is just one property that you can 321 set: 322 </p> 323 <div class="itemizedlist"> 324 <ul type="disc"> 325 <li> 326 <p> 327 <code class="methodname">JoinConfig.setNoSort()</code> 328 </p> 329 <p> 330 Specifies whether automatic sorting of input cursors is disabled. The cursors are sorted from the 331 one that refers to the least number of data items to the one that refers to the most. 332 </p> 333 <p> 334 If the data is structured so that cursors with many data items also share many common elements, 335 higher performance will result from listing those cursors before cursors with fewer data 336 items. Turning off sorting permits applications to specify cursors in the proper order given this 337 scenario. 338 </p> 339 <p> 340 The default value is <code class="literal">false</code> (automatic cursor sorting is performed). 341 </p> 342 <p> 343 For example: 344 </p> 345 <a id="je_index10"></a> 346 <pre class="programlisting">// All database and environments omitted 347JoinConfig config = new JoinConfig(); 348config.setNoSort(true); 349JoinCursor joinCursor = myDb.join(cursorArray, config); </pre> 350 </li> 351 </ul> 352 </div> 353 </div> 354 </div> 355 <div class="navfooter"> 356 <hr /> 357 <table width="100%" summary="Navigation footer"> 358 <tr> 359 <td width="40%" align="left"><a accesskey="p" href="secondaryCursor.html">Prev</a>��</td> 360 <td width="20%" align="center"> 361 <a accesskey="u" href="indexes.html">Up</a> 362 </td> 363 <td width="40%" align="right">��<a accesskey="n" href="javaindexusage.html">Next</a></td> 364 </tr> 365 <tr> 366 <td width="40%" align="left" valign="top"> 367 <span>Using Secondary Cursors</span> 368 369 ��</td> 370 <td width="20%" align="center"> 371 <a accesskey="h" href="index.html">Home</a> 372 </td> 373 <td width="40%" align="right" valign="top">��Secondary Database Example</td> 374 </tr> 375 </table> 376 </div> 377 </body> 378</html> 379