The Jdbc Gps Device provides support for database indexing through the use of JDBC. The Jdbc device maps a Jdbc ResultSet to a set of Compass Resources (sharing the same resource mapping). Each Resource maps one to one with a ResultSet row. The Jdbc device can hold multiple ResultSet to Resource mappings. The Jdbc Gps device class is ResultSetJdbcGpsDevice. The core configuration is the mapping definitions of a Jdbc ResultSet and a Compass Resource.
The Jdbc Gps device does not use OSEM, since no POJOs are defined that map the ResultSet to objects. For applications that use ORM tools, Compass::Gps provides several devices that integrate with popular ORM tools such as Hibernate, JDO, and OJB. For more information about Compass Resource, Resource Property and resource mapping, please read the Search Engine and Resource Mapping sections.
The Jdbc Gps device also provides support for ActiveMirrorGpsDevice, meaning that data changes done to the database can be automatically detected by the defined mappings and device.
For the rest of the chapter, we will use the following database tables:
CREATE TABLE parent (
id INTEGER NOT NULL IDENTITY PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30),
version BIGINT NOT NULL
);
CREATE TABLE child (
id INTEGER NOT NULL IDENTITY PRIMARY KEY,
parent_id INTEGER NOT NULL,
first_name VARCHAR(30),
last_name VARCHAR(30),
version BIGINT NOT NULL
);
alter table child add constraint
fk_child_parent foreign key (parent_id) references parent(id);
The PARENT.ID is the primary key of the PARENT table, and the CHILD.ID is the primary key of the CHILD table. There is a one to many relationship between PARENT and child using the CHILD.PARENT_ID column. The VERSION columns will be explained later, as they are used for the data changes mirroring option.
To enable the Jdbc device to index a database, a set of mappings must be defined between the database and the compass index. The main mapping definition maps a generic Jdbc ResultSet to a set of Compass Resources that are defined by a specific Resource Mapping definitions. The mapping can be configured either at database ResultSet or Table levels. ResultSetToResourceMapping maps generic select SQL (returning a ResultSet) and TableToResourceMapping (extends the ResultSetToResourceMapping) simply maps database tables.
The following code sample shows how to configure a single ResultSet that combines both the PARENT and CHILD tables into a single resource mapping with an alias called "result-set".
ResultSetToResourceMapping mapping = new ResultSetToResourceMapping();
mapping.setAlias("result-set");
mapping.setSelectQuery("select "
+ "p.id as parent_id, p.first_name as parent_first_name, p.last_name as parent_last_name, "
+ "c.id as child_id, c.first_name as child_first_name, c.last_name child_last_name "
+ "from parent p left join child c on p.id = c.parent_id");
// maps from a parent_id column to a resource property named parent-id
mapping.addIdMapping(new IdColumnToPropertyMapping("parent_id", "parent-id"));
// maps from a child_id column to a resource property named child-id
mapping.addIdMapping(new IdColumnToPropertyMapping("child_id", "child-id"));
mapping.addDataMapping(new DataColumnToPropertyMapping("parent_first_name", "parent-first-name"));
mapping.addDataMapping(new DataColumnToPropertyMapping("parent_first_name", "first-name"));
mapping.addDataMapping(new DataColumnToPropertyMapping("child_first_name", "child-first-name"));
mapping.addDataMapping(new DataColumnToPropertyMapping("child_first_name", "first-name"));
Here, we defined a mapping from a ResultSet that combines both the PARENT table and the CHILD table into a single set of Resources. Note also in the above example how "parent_first_name" is mapped to multiple alias names, allowing searches to be performed on either the specific attribute type or the more general "first_name".
The required settings for the ResultSetToResourceMapping are the alias name of the Resource that will be created, the select query that generates the ResultSet, and the ids columns mapping (at least one must be defined) that maps to the columns the uniquely identifies the rows in the ResultSet.
ColumnToPropertyMapping is a general mapping from a database column to a Compass Resource Property. The mapping can map from a column name or a column index (the order that it appears in the select query) to a Property name. It can also have definitions of the Property characteristics (Property.Index, Property.Store and Property.TermVector). Both IdColumnToPropertyMapping and DataColumnToPropertyMapping are of ColumnToPropertyMapping type.
In the above sample, the two columns that identifies a row for the given select query, are the parent_id and the child_id. They are mapped to the parent-id and child-id property names respectively.
Mapping data columns using the DataColumnToPropertyMapping provides mapping from "data" columns into searchable meta-data (Resource Property). As mentioned, you can control the property name and it's characteristics. Mapping data columns is optional, though mapping none makes little sense. ResultSetToResourceMapping has the option to index all the unmapped columns of the ResultSet by setting the indexUnMappedColumns property to true. The meta-datas that will be created will have the property name set to the column name.
TableToResourceMapping is a simpler mapping that extends the ResultSetToResourceMapping, and maps a database table to a resource mapping. The following code sample shows how to configure the table mapping.
TableToResourceMapping parentMapping = new TableToResourceMapping("PARENT", "parent");
parentMapping.addDataMapping(new DataColumnToPropertyMapping("first_name", "first-name"));
TableToResourceMapping childMapping = new TableToResourceMapping("CHILD", "child");
childMapping.addDataMapping(new DataColumnToPropertyMapping("first_name", "first-name"));
The above code defined the table mappings. One mapping for the PARENT table to the "parent" alias, and one for the CHILD table to the "child" alias. The mappings definitions are much simpler than the ResultSetToResourceMapping, with only the table name and the alias required. Since the mapping works against a database table, the id columns can be auto generated (based on the table primary keys, and the property names are the same as the column names), and the select query (based on the table name). Note that the mapping will auto generate only settings that have not been set. If for example the select query is set, it will not be generated.
The ResultSetJdbcGpsDevice supports mirroring data changes to the database. In order to enable it, the ResultSet that will be mapped must have at least one version column. The version column must be incremented whenever a change occurs to the corresponding row in the database (Note that some databases have the feature built in, like ORACLE).
The following code sample shows how to configure a mirroring enabled ResultSet mapping:
ResultSetToResourceMapping mapping = new ResultSetToResourceMapping();
mapping.setAlias("result-set");
mapping.setSelectQuery("select "
+ "p.id as parent_id, p.first_name as parent_first_name, p.last_name as parent_last_name, p.version as parent_version, "
+ "COALESCE(c.id, 0) as child_id, c.first_name as child_first_name, c.last_name child_last_name, COALESCE(c.version, 0) as child_version "
+ "from parent p left join child c on p.id = c.parent_id");
mapping.setVersionQuery("select p.id as parent_id, COALESCE(c.id, 0) as child_id, "
+ "p.version as parent_version, COALESCE(c.version, 0) as child_version "
+ "from parent p left join child c on p.id = c.parent_id");
mapping.addIdMapping(new IdColumnToPropertyMapping("parent_id", "parent-id", "p.id"));
mapping.addIdMapping(new IdColumnToPropertyMapping("child_id", "child-id", "COALESCE(c.id, 0)"));
mapping.addDataMapping(new DataColumnToPropertyMapping("parent_first_name", "parent-first-name"));
mapping.addDataMapping(new DataColumnToPropertyMapping("child_first_name", "child-first-name"));
mapping.addVersionMapping(new VersionColumnMapping("parent_version"));
mapping.addVersionMapping(new VersionColumnMapping("child_version"));
There are three additions to the previously configured result set mapping. The first is the version query that will be executed in order to identify changes made to the result set (rows created, updated, or deleted). The version query should return the ResultSet id and version columns. The second change is the id columns names in the select query, since a dynamic where clause is added to the select query for mirroring purposes. The last one is the actual version column mapping (no version column mapping automatically disabled the mirroring feature).
The following code sample shows how to configure a mirroring enabled Table mapping:
TableToResourceMapping parentMapping = new TableToResourceMapping("parent", "parent");
parentMapping.addVersionMapping(new VersionColumnMapping("version"));
parentMapping.setIndexUnMappedColumns(true);
TableToResourceMapping childMapping = new TableToResourceMapping("child", "child");
childMapping.addVersionMapping(new VersionColumnMapping("version"));
childMapping.setIndexUnMappedColumns(true);
Again, the table mapping is much simpler than the result set mapping. The only thing that needs to be added is the version column mapping. The version query is automatically generated.
The mirroring operation works with snapshots. Snapshots are taken when the index() or the performMirroring() are called and represents the latest ResultSet state.
Compass::Gps comes with two snapshot mechanisms. The first is JdbcSnapshotPersister: RAMJdbcSnapshotPersister which holds the Jdbc snapshot in memory and is not persistable between application lifecycle. The second is FSJdbcSnapshotPersister, which save the snapshot in the file system (using the given file path). A code sample:
gpsDevice = new ResultSetJdbcGpsDevice();
gpsDevice.setSnapshotPersister(new FSJdbcSnapshotPersister("target/testindex/snapshot"));
After defining the result set mapping, the resource mapping must be defined as well. Luckily, there is no need to create the mapping file (cpm.xml file), since it can be generated automatically using Compass::Core MappingResolver feature. The Jdbc device provides the ResultSetResourceMappingResolver which automatically generates the resource mapping for a given ResultSetToResourceMapping. Additional settings for the resource mapping can be set as well, such as the sub-index, all meta data, etc.
CompassConfiguration conf = new CompassConfiguration()
.setSetting(CompassEnvironment.CONNECTION, "target/testindex");
DataSource dataSource = // get/create a Jdbc Data Source
ResultSetToResourceMapping mapping = // create the result set mapping
conf.addMappingResover(new ResultSetResourceMappingResolver(mapping, dataSource));
After explaining two of the most important aspects of the Jdbc mappings, here is a complete example of configuring a ResultSetJdbcGpsDevice.
ResultSetToResourceMapping mapping1 = // create the result set mapping or table mapping
ResultSetToResourceMapping mapping2 = // create the result set mapping or table mapping
DataSource dataSource = // create a jdbc dataSource or look it up from JNDI
CompassConfiguration conf = new CompassConfiguration().setSetting(CompassEnvironment.CONNECTION,
"target/testindex");
conf.addMappingResover(new ResultSetResourceMappingResolver(mapping1, dataSource));
// build the mirror compass instance
compass = conf.buildCompass();
gpsDevice = new ResultSetJdbcGpsDevice();
gpsDevice.setDataSource(dataSource);
gpsDevice.setName("jdbcDevice");
gpsDevice.setMirrorDataChanges(false);
gpsDevice.addMapping(mapping1);
gpsDevice.addMapping(mapping2);
gps = new SingleCompassGps(compass);
gps.addGpsDevice(gpsDevice);
gps.start();
GPS devices are Inversion Of Control / Dependency Injection enabled, meaning that it can be configured with an IOC container. For an example of configuring the ResultSetJdbcGpsDevice, please see Spring Jdbc Gps Device section.