mirror of
https://github.com/berkeleydb/je.git
synced 2024-11-15 01:46:24 +00:00
377 lines
14 KiB
HTML
377 lines
14 KiB
HTML
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
|
||
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
<html xmlns="http://www.w3.org/1999/xhtml">
|
||
<head>
|
||
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
|
||
<title>Database Joins</title>
|
||
<link rel="stylesheet" href="gettingStarted.css" type="text/css" />
|
||
<meta name="generator" content="DocBook XSL Stylesheets V1.73.2" />
|
||
<link rel="start" href="index.html" title="Getting Started with Berkeley DB Java Edition" />
|
||
<link rel="up" href="indexes.html" title="Chapter 10. Secondary Databases" />
|
||
<link rel="prev" href="secondaryCursor.html" title="Using Secondary Cursors" />
|
||
<link rel="next" href="indexusage.html" title="Secondary Database Example" />
|
||
</head>
|
||
<body>
|
||
<div xmlns="" class="navheader">
|
||
<div class="libver">
|
||
<p>Library Version 12.2.7.5</p>
|
||
</div>
|
||
<table width="100%" summary="Navigation header">
|
||
<tr>
|
||
<th colspan="3" align="center">Database Joins</th>
|
||
</tr>
|
||
<tr>
|
||
<td width="20%" align="left"><a accesskey="p" href="secondaryCursor.html">Prev</a> </td>
|
||
<th width="60%" align="center">Chapter 10. Secondary Databases</th>
|
||
<td width="20%" align="right"> <a accesskey="n" href="indexusage.html">Next</a></td>
|
||
</tr>
|
||
</table>
|
||
<hr />
|
||
</div>
|
||
<div class="sect1" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h2 class="title" style="clear: both"><a id="joins"></a>Database Joins</h2>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<div class="toc">
|
||
<dl>
|
||
<dt>
|
||
<span class="sect2">
|
||
<a href="joins.html#joinUsage">Using Join Cursors</a>
|
||
</span>
|
||
</dt>
|
||
<dt>
|
||
<span class="sect2">
|
||
<a href="joins.html#joinconfig">JoinCursor Properties</a>
|
||
</span>
|
||
</dt>
|
||
</dl>
|
||
</div>
|
||
<p>
|
||
If you have two or more secondary databases associated with a primary
|
||
database, then you can retrieve primary records based on the intersection of
|
||
multiple secondary entries. You do this using a
|
||
<span><code class="classname">JoinCursor</code>.</span>
|
||
|
||
</p>
|
||
<p>
|
||
Throughout this document we have presented a
|
||
<span>class</span>
|
||
|
||
that stores
|
||
<span>inventory</span>
|
||
information on grocery
|
||
<span>items.</span>
|
||
|
||
That
|
||
<span>class</span>
|
||
|
||
is fairly simple with a limited
|
||
number of data members, few of which would be interesting from a query
|
||
perspective. But suppose, instead, that we were storing
|
||
information on something with many more characteristics that can be queried, such
|
||
as an automobile. In that case, you may be storing information such as
|
||
color, number of doors, fuel mileage, automobile type, number of
|
||
passengers, make, model, and year, to name just a few.
|
||
</p>
|
||
<p>
|
||
In this case, you would still likely be using some unique value to key your
|
||
primary entries (in the United States, the automobile's VIN would be
|
||
ideal for this purpose). You would then create a
|
||
<span>class</span>
|
||
|
||
that identifies
|
||
all the characteristics of the automobiles in your inventory.
|
||
|
||
<span>
|
||
You would
|
||
also have to create some mechanism by which you would move instances of
|
||
this class in and out of Java <code class="literal">byte</code> arrays. We
|
||
described the concepts and mechanisms by which you can perform these
|
||
activities in <a class="xref" href="DBEntry.html" title="Chapter 8. Database Records">Database Records</a>.
|
||
</span>
|
||
</p>
|
||
<p>
|
||
To query this data, you might then create multiple secondary databases,
|
||
one for each of the characteristics that you want to query. For
|
||
example, you might create a secondary for color, another for number of
|
||
doors, another for number of passengers, and so forth. Of course, you
|
||
will need a unique
|
||
<span>key creator</span>
|
||
|
||
for each such secondary database. You do
|
||
all of this using the concepts and techniques described throughout this
|
||
chapter.
|
||
</p>
|
||
<p>
|
||
Once you have created this primary database and all interesting
|
||
secondaries, what you have is the ability to retrieve automobile records
|
||
based on a single characteristic. You can, for example, find all the
|
||
automobiles that are red. Or you can find all the automobiles that have
|
||
four doors. Or all the automobiles that are minivans.
|
||
</p>
|
||
<p>
|
||
The next most natural step, then, is to form compound queries, or joins.
|
||
For example, you might want to find all the automobiles that are red,
|
||
and that were built by Toyota, and that are minivans. You can do this
|
||
using a
|
||
<span><code class="classname">JoinCursor</code> class instance.</span>
|
||
|
||
</p>
|
||
<div class="sect2" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h3 class="title"><a id="joinUsage"></a>Using Join Cursors</h3>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<p>
|
||
To use a join cursor:
|
||
</p>
|
||
<div class="itemizedlist">
|
||
<ul type="disc">
|
||
<li>
|
||
<p>
|
||
Open two or more
|
||
<span>secondary cursors. These </span>
|
||
cursors
|
||
<span>must be obtained from</span>
|
||
|
||
secondary databases that are associated with
|
||
the same primary database.
|
||
</p>
|
||
</li>
|
||
<li>
|
||
<p>
|
||
Position each such cursor to the secondary key
|
||
value in which you are interested. For example, to build on
|
||
the previous description, the cursor for the color
|
||
database is positioned to the <code class="literal">red</code> records
|
||
while the cursor for the model database is positioned to the
|
||
<code class="literal">minivan</code> records, and the cursor for the
|
||
make database is positioned to <code class="literal">Toyota</code>.
|
||
</p>
|
||
</li>
|
||
<li>
|
||
<p>
|
||
|
||
Create an array of <span>secondary</span> cursors, and
|
||
place in it each of the cursors that are participating in your join query.
|
||
|
||
|
||
</p>
|
||
</li>
|
||
<li>
|
||
<p>
|
||
|
||
Obtain a join cursor. You do this using the
|
||
<code class="methodname">Database.join()</code>
|
||
|
||
|
||
method. You must pass this method the array of secondary cursors that you
|
||
opened and positioned in the previous steps.
|
||
</p>
|
||
</li>
|
||
<li>
|
||
<p>
|
||
Iterate over the set of matching records
|
||
<span>using <code class="methodname">JoinCursor.getNext()</code></span>
|
||
until
|
||
<span><code class="classname">OperationStatus</code> is not <code class="literal">SUCCESS</code>.</span>
|
||
|
||
</p>
|
||
</li>
|
||
<li>
|
||
<p>
|
||
Close your <span>join</span> cursor.
|
||
</p>
|
||
</li>
|
||
<li>
|
||
<p>
|
||
If you are done with them, close all your <span>secondary</span> cursors.
|
||
</p>
|
||
</li>
|
||
</ul>
|
||
</div>
|
||
<p>
|
||
For example:
|
||
</p>
|
||
<a id="je_index9"></a>
|
||
<pre class="programlisting">package je.gettingStarted;
|
||
|
||
import com.sleepycat.je.Database;
|
||
import com.sleepycat.je.DatabaseEntry;
|
||
import com.sleepycat.je.DatabaseException;
|
||
import com.sleepycat.je.JoinCursor;
|
||
import com.sleepycat.je.LockMode;
|
||
import com.sleepycat.je.OperationStatus;
|
||
import com.sleepycat.je.SecondaryCursor;
|
||
import com.sleepycat.je.SecondaryDatabase;
|
||
|
||
...
|
||
|
||
// Database and secondary database opens omitted for brevity.
|
||
// Assume a primary database handle:
|
||
// automotiveDB
|
||
// Assume 3 secondary database handles:
|
||
// automotiveColorDB -- index based on automobile color
|
||
// automotiveTypeDB -- index based on automobile type
|
||
// automotiveMakeDB -- index based on the manufacturer
|
||
|
||
// Query strings:
|
||
String theColor = "red";
|
||
String theType = "minivan";
|
||
String theMake = "Toyota";
|
||
|
||
// Secondary cursors used for the query:
|
||
SecondaryCursor colorSecCursor = null;
|
||
SecondaryCursor typeSecCursor = null;
|
||
SecondaryCursor makeSecCursor = null;
|
||
|
||
// The join cursor
|
||
JoinCursor joinCursor = null;
|
||
|
||
// These are needed for our queries
|
||
DatabaseEntry foundKey = new DatabaseEntry();
|
||
DatabaseEntry foundData = new DatabaseEntry();
|
||
|
||
// All cursor operations are enclosed in a try block to ensure that they
|
||
// get closed in the event of an exception.
|
||
|
||
try {
|
||
// Database entries used for the query:
|
||
DatabaseEntry color = new DatabaseEntry(theColor.getBytes("UTF-8"));
|
||
DatabaseEntry type = new DatabaseEntry(theType.getBytes("UTF-8"));
|
||
DatabaseEntry make = new DatabaseEntry(theMake.getBytes("UTF-8"));
|
||
|
||
colorSecCursor = automotiveColorDB.openSecondaryCursor(null, null);
|
||
typeSecCursor = automotiveTypeDB.openSecondaryCursor(null, null);
|
||
makeSecCursor = automotiveMakeDB.openSecondaryCursor(null, null);
|
||
|
||
// Position all our secondary cursors to our query values.
|
||
OperationStatus colorRet =
|
||
colorSecCursor.getSearchKey(color, foundData, LockMode.DEFAULT);
|
||
OperationStatus typeRet =
|
||
typeSecCursor.getSearchKey(type, foundData, LockMode.DEFAULT);
|
||
OperationStatus makeRet =
|
||
makeSecCursor.getSearchKey(make, foundData, LockMode.DEFAULT);
|
||
|
||
// If all our searches returned successfully, we can proceed
|
||
if (colorRet == OperationStatus.SUCCESS &&
|
||
typeRet == OperationStatus.SUCCESS &&
|
||
makeRet == OperationStatus.SUCCESS) {
|
||
|
||
// Get a secondary cursor array and populate it with our
|
||
// positioned cursors
|
||
SecondaryCursor[] cursorArray = {colorSecCursor,
|
||
typeSecCursor,
|
||
makeSecCursor};
|
||
|
||
// Create the join cursor
|
||
joinCursor = automotiveDB.join(cursorArray, null);
|
||
|
||
// Now iterate over the results, handling each in turn
|
||
while (joinCursor.getNext(foundKey, foundData, LockMode.DEFAULT) ==
|
||
OperationStatus.SUCCESS) {
|
||
|
||
// Do something with the key and data retrieved in
|
||
// foundKey and foundData
|
||
}
|
||
}
|
||
} catch (DatabaseException dbe) {
|
||
// Error reporting goes here
|
||
} catch (Exception e) {
|
||
// Error reporting goes here
|
||
} finally {
|
||
try {
|
||
// Make sure to close out all our cursors
|
||
if (colorSecCursor != null) {
|
||
colorSecCursor.close();
|
||
}
|
||
if (typeSecCursor != null) {
|
||
typeSecCursor.close();
|
||
}
|
||
if (makeSecCursor != null) {
|
||
makeSecCursor.close();
|
||
}
|
||
if (joinCursor != null) {
|
||
joinCursor.close();
|
||
}
|
||
} catch (DatabaseException dbe) {
|
||
// Error reporting goes here
|
||
}
|
||
} </pre>
|
||
</div>
|
||
<div class="sect2" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h3 class="title"><a id="joinconfig"></a>JoinCursor Properties</h3>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<p>
|
||
You can set <code class="classname">JoinCursor</code> properties using the
|
||
<code class="classname">JoinConfig</code> class. Currently there is just one property that you can
|
||
set:
|
||
</p>
|
||
<div class="itemizedlist">
|
||
<ul type="disc">
|
||
<li>
|
||
<p>
|
||
<code class="methodname">JoinConfig.setNoSort()</code>
|
||
</p>
|
||
<p>
|
||
Specifies whether automatic sorting of input cursors is disabled. The cursors are sorted from the
|
||
one that refers to the least number of data items to the one that refers to the most.
|
||
</p>
|
||
<p>
|
||
If the data is structured so that cursors with many data items also share many common elements,
|
||
higher performance will result from listing those cursors before cursors with fewer data
|
||
items. Turning off sorting permits applications to specify cursors in the proper order given this
|
||
scenario.
|
||
</p>
|
||
<p>
|
||
The default value is <code class="literal">false</code> (automatic cursor sorting is performed).
|
||
</p>
|
||
<p>
|
||
For example:
|
||
</p>
|
||
<a id="je_index10"></a>
|
||
<pre class="programlisting">// All database and environments omitted
|
||
JoinConfig config = new JoinConfig();
|
||
config.setNoSort(true);
|
||
JoinCursor joinCursor = myDb.join(cursorArray, config); </pre>
|
||
</li>
|
||
</ul>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<div class="navfooter">
|
||
<hr />
|
||
<table width="100%" summary="Navigation footer">
|
||
<tr>
|
||
<td width="40%" align="left"><a accesskey="p" href="secondaryCursor.html">Prev</a> </td>
|
||
<td width="20%" align="center">
|
||
<a accesskey="u" href="indexes.html">Up</a>
|
||
</td>
|
||
<td width="40%" align="right"> <a accesskey="n" href="indexusage.html">Next</a></td>
|
||
</tr>
|
||
<tr>
|
||
<td width="40%" align="left" valign="top">
|
||
<span>Using Secondary Cursors</span>
|
||
|
||
</td>
|
||
<td width="20%" align="center">
|
||
<a accesskey="h" href="index.html">Home</a>
|
||
</td>
|
||
<td width="40%" align="right" valign="top"> Secondary Database Example</td>
|
||
</tr>
|
||
</table>
|
||
</div>
|
||
</body>
|
||
</html>
|