public class SQLCommitter extends AbstractMappedCommitter
Commit documents to a SQL database.
By default, this Committer will throw an exception when trying to insert values into non-existing database table or fields. It is recommended your make sure your database table exists and the document fields being sent to the committer match your database fields.
Alternatively, you can provide the necessary SQLs to create a new
table as well as new fields as needed using
setCreateTableSQL(String) and setCreateFieldSQL(String)
respectively. Make sure to use the following placeholder variables
as needed in the provided SQL(s) to have them automatically replaced by
this Committer.
setTableName(String).
AbstractMappedCommitter.setTargetReferenceField(String).
AbstractMappedCommitter.setTargetContentField(String).
For databases requiring authentication, the password can
optionally be encrypted using EncryptionUtil
(or command-line "encrypt.bat" or "encrypt.sh").
In order for the password to be decrypted properly, you need
to specify the encryption key used to encrypt it. The key can be stored
in a few supported locations and a combination of
passwordKey
and passwordKeySource must be specified to properly
locate the key. The supported sources are:
passwordKeySource |
passwordKey |
|---|---|
key |
The actual encryption key. |
file |
Path to a file containing the encryption key. |
environment |
Name of an environment variable containing the key. |
property |
Name of a JVM system property containing the key. |
<committer class="com.norconex.committer.sql.SQLCommitter">
<!-- Mandatory settings -->
<driverClass>
(Class name of the JDBC driver to use.)
</driverClass>
<connectionUrl>
(JDBC connection URL.)
</connectionUrl>
<tableName>
(The target database table name where documents will be committed.)
</tableName>
<!-- Other settings -->
<driverPath>
(Path to JDBC driver. Not required if already in classpath.)
</driverPath>
<properties>
<property key="(property name)">(Property value.)</property>
<!-- You can have multiple property. -->
</properties>
<createTableSQL>
<!--
The CREATE statement used to create a table if it does not
already exist. If you need fields of specific types,
specify them here. The following variables are expected
and will be replaced with the configuration options of the same name:
${tableName}, ${targetReferenceField} and ${targetContentField}.
Example:
-->
CREATE TABLE ${tableName} (
${targetReferenceField} VARCHAR(32672) NOT NULL,
${targetContentField} CLOB,
PRIMARY KEY ( ${targetReferenceField} ),
title VARCHAR(256)
)
</createTableSQL>
<createFieldSQL>
<!--
The ALTER statement used to create missing table fields.
The ${tableName} variable and will be replaced with
the configuration option of the same name. The ${fieldName}
variable will be replaced by newly encountered field names.
Example:
-->
ALTER TABLE ${tableName} ADD ${fieldName} VARCHAR(32672)
</createFieldSQL>
<multiValuesJoiner>
(One or more characters to join multi-value fields.
Default is "|".)
</multiValuesJoiner>
<fixFieldNames>
(Attempts to prevent insertion errors by converting characters that
are not underscores or alphanumeric to underscores.
Will also remove all non alphabetic characters that begins
a field name.)
</fixFieldNames>
<fixFieldValues>
(Attempts to prevent insertion errors by truncating values
that are larger than their defined maximum field length.)
</fixFieldValues>
<!-- Use the following if authentication is required. -->
<username>(Optional user name)</username>
<password>(Optional user password)</password>
<!-- Use the following if password is encrypted. -->
<passwordKey>(the encryption key or a reference to it)</passwordKey>
<passwordKeySource>[key|file|environment|property]</passwordKeySource>
<sourceReferenceField keep="[false|true]">
(Optional name of field that contains the document reference, when
the default document reference is not used.
Once re-mapped, this metadata source field is
deleted, unless "keep" is set to true.)
</sourceReferenceField>
<targetReferenceField>
(Name of the database target field where the store a document unique
identifier (sourceReferenceField). If not specified,
default is "id". Typically is a tableName primary key.)
</targetReferenceField>
<sourceContentField keep="[false|true]">
(If you wish to use a metadata field to act as the document
"content", you can specify that field here. Default
does not take a metadata field but rather the document content.
Once re-mapped, the metadata source field is deleted,
unless "keep" is set to true.)
</sourceContentField>
<targetContentField>
(Target repository field name for a document content/body.
Default is "content". Since document content can sometimes be
quite large, a CLOB field is usually best advised.)
</targetContentField>
<commitBatchSize>
(Max number of documents to send to the database at once.)
</commitBatchSize>
<queueDir>(optional path where to queue files)</queueDir>
<queueSize>(max queue size before committing)</queueSize>
<maxRetries>(max retries upon commit failures)</maxRetries>
<maxRetryWait>(max delay in milliseconds between retries)</maxRetryWait>
</committer>
XML configuration entries expecting millisecond durations
can be provided in human-readable format (English only), as per
DurationParser (e.g., "5 minutes and 30 seconds" or "5m30s").
The following example uses an H2 database and creates the table and fields as they are encountered, storing all new fields as VARCHAR, making sure those new fields are no longer than 5000 characters.
<committer class="com.norconex.committer.sql.SQLCommitter">
<driverPath>/path/to/driver/h2.jar</driverPath>
<driverClass>org.h2.Driver</driverClass>
<connectionUrl>jdbc:h2:file:///path/to/db/h2</connectionUrl>
<tableName>test_table</tableName>
<createTableSQL>
CREATE TABLE ${tableName} (
${targetReferenceField} VARCHAR(32672) NOT NULL,
${targetContentField} CLOB,
PRIMARY KEY ( ${targetReferenceField} )
)
</createTableSQL>
<createFieldSQL>
ALTER TABLE ${tableName} ADD ${fieldName} VARCHAR(5000)
</createFieldSQL>
<fixFieldValues>true</fixFieldValues>
</committer>
| Modifier and Type | Field and Description |
|---|---|
static String |
DEFAULT_MULTI_VALUES_JOINER
Default multi-value join string
|
static String |
DEFAULT_SQL_CONTENT_FIELD
Default SQL content field
|
static String |
DEFAULT_SQL_ID_FIELD
Default SQL primary key field
|
DEFAULT_COMMIT_BATCH_SIZEDEFAULT_QUEUE_DIR, filesCommittingDEFAULT_QUEUE_SIZE, queueSize| Constructor and Description |
|---|
SQLCommitter()
Constructor.
|
getSourceContentField, getSourceReferenceField, getTargetContentField, getTargetReferenceField, isKeepSourceContentField, isKeepSourceReferenceField, loadFromXML, prepareCommitAddition, saveToXML, setKeepSourceContentField, setKeepSourceReferenceField, setSourceContentField, setSourceReferenceField, setTargetContentField, setTargetReferenceFieldcommitAddition, commitComplete, commitDeletion, getCommitBatchSize, getMaxRetries, getMaxRetryWait, setCommitBatchSize, setMaxRetries, setMaxRetryWaitgetInitialQueueDocCount, getQueueDir, prepareCommitDeletion, queueAddition, queueRemoval, setQueueDiradd, getQueueSize, remove, setQueueSizepublic static final String DEFAULT_SQL_ID_FIELD
public static final String DEFAULT_SQL_CONTENT_FIELD
public static final String DEFAULT_MULTI_VALUES_JOINER
public String getDriverPath()
public void setDriverPath(String driverPath)
public String getDriverClass()
public void setDriverClass(String driverClass)
public String getConnectionUrl()
public void setConnectionUrl(String connectionUrl)
public String getUsername()
public void setUsername(String username)
public String getPassword()
public void setPassword(String password)
public EncryptionKey getPasswordKey()
public void setPasswordKey(EncryptionKey passwordKey)
public Properties getProperties()
public String getTableName()
public void setTableName(String tableName)
public String getCreateTableSQL()
public void setCreateTableSQL(String createTableSQL)
public String getCreateFieldSQL()
public void setCreateFieldSQL(String createFieldSQL)
public String getMultiValuesJoiner()
public void setMultiValuesJoiner(String multiValuesJoiner)
public boolean isFixFieldNames()
public void setFixFieldNames(boolean fixFieldNames)
public boolean isFixFieldValues()
public void setFixFieldValues(boolean fixFieldValues)
public void commit()
commit in interface ICommittercommit in class AbstractFileQueueCommitterpublic void close()
protected void commitBatch(List<ICommitOperation> batch)
commitBatch in class AbstractBatchCommitterprotected void saveToXML(XMLStreamWriter writer) throws XMLStreamException
saveToXML in class AbstractMappedCommitterXMLStreamExceptionprotected void loadFromXml(XMLConfiguration xml)
loadFromXml in class AbstractMappedCommitterpublic boolean equals(Object other)
equals in class AbstractMappedCommitterpublic int hashCode()
hashCode in class AbstractMappedCommitterpublic String toString()
toString in class AbstractMappedCommitterCopyright © 2017–2020 Norconex Inc.. All rights reserved.