Package ca.spatial.table

Class JDBCStore

java.lang.Object

javax.swing.table.AbstractTableModel

ca.spatial.table.AttributeStore

ca.spatial.table.JDBCStore

All Implemented Interfaces:

PopupActivationListener, Serializable, Comparable, EventListener, ListSelectionListener, TableModel

public abstract class JDBCStore
extends AttributeStore

This class provides the makeJDBCStore method for reading a database select query and accessing the results through the AttributeStore interface. Only select queries are processed, all other SQL statement types will cause an error.

The use of this class requires an appropriate JDBC driver, typically installed as a JAR file in the Patchworks/lib folder. JDBC drivers are available for many popular databases: try searching the web for the driver suitable to the database that you want to connect to.

Use the makeJDBCStore(java.lang.String, java.lang.String, java.util.Properties) method to connect to a database, execute a query and return an AttributeStore object. The query will run against the database and all selected rows will be immediately retrieved into a temporary cache. Any subsequent changes to the database will not be reflected in the cached data. The temporary caches supports editing, but any changes that you make will not be save unless you perform an explicit export.

Database connections are not cached. Each time the makeJDBCStore method is called a new connection will be opened, the query executed, and the connection closed.

Example

 store = JDBCStore.makeJDBCStore("JDBC:SQLITE:../SpatialDataPrep_pll.gpkg",
            "select * from preblocks", null);
 

Note that the sqlite driver allows a relative path reference to the location of the database file.

The third parameter to the makeJDBCStore method is a Properties map. This map can be filled with values appropriate to the connection that you are trying to establish. Common properties are login credentials such as username and password, but some database connection allow many performance tuning options. If no properties are required then the third argument may be null.

Drivers

JDBC database connections require a database driver. The driver files are not distributed with Patchworks, but can be found online. Once obtained these drivers must be loaded in to the application before they can be used. There are a number of methods that can be used to load the drivers:

1. The driver JAR file can be saved in to the lib folder in the Patchworks installation folder, usually found in the "Program Files/Spatial Planning Systems" folder. All of the jar files in the lib folder will be automaticaally loaded in to the application when it is started. This is the preferred method to locate drivers.
2. Add the name of the of the jar file to the "launcher.classpath" property. This value can be found in the patchworks.ini file, or can be set by right-clicking on the "Applications" icon in the Patchworks Application Launcher.
3. The registerJarDriver(java.lang.String) method can be used to load a driver. This method requires the full file name of the jar file. Add the call to this method to a script prior to opening a JDBCStore table. This command will load and register the driver the first time that it is called. On subsequent calls with the same driver name the driver will not be reloaded.
4. A special property with a key of "driverJar" can added to the property set that is passed to the registerJarDriver(java.lang.String) method. The system will attempt to register the jar file as a JDBC driver prior to opening the connection. Similar to the previous method the driver will not be reloaded after the first time it is registered.

See Also:

Serialized Form

Field Summary

Fields inherited from class ca.spatial.table.AttributeStore

SELECT_ADD, SELECT_FROM, SELECT_NEW

Method Summary

Modifier and Type	Method	Description
static RamAttributeStore	makeJDBCStore(String connection, String query, Properties props)	Create an AttributeStore object from an SQL query on a JDBC data source.
static boolean	registerJarDriver(String path)	Load a JDBC driver contained in the jar file.

Methods inherited from class ca.spatial.table.AttributeStore

absoluteFile, absoluteFile, absolutePath, absolutePath, addCellEditors, addMenuItems, addPopupActivationListener, addRelate, addRelate, addTableModelListener, addToGlobalStoreList, allowsEditing, booleanValue, booleanValue, buildIndex, calculate, calculateField, calculateField, canIndex, changeSelection, clearSelection, close, compareTo, decodeCharset, doubleValue, doubleValue, exportCSV, exportCSV, exportCSV, exportCSV, exportCSV, exportCSV, exportDBF, exportDBF, exportDBF, exportDBF, exportDBF, findColumn, findColumn, findSimilarColumnNames, getAttributeFormat, getCacheParent, getCachingLayer, getCachingLayer, getCanonicalName, getColumnClass, getColumnCount, getColumnName, getColumnNames, getContextPeriods, getCwd, getEditMode, getFullColumnName, getListSelectionModel, getMinMax, getMinMax, getMinMax, getMinMax, getMinMax, getOpenStore, getOpenStore, getOpenStore, getOpenStores, getPeriod, getPeriodCount, getPeriodWidth, getRowCount, getRowNodeRemap, getSelectedRowCount, getSelection, getSelection, getSelection, getShortName, getSnapshot, getSnapshot, getStoreByShortName, getStoreByTitle, getSynchronizer, getTableName, getTableSpec, getTitle, getValueAt, getValueAt, getYearForPeriod, hasIndex, identifyRecords, integerValue, integerValue, isCellEditable, isSelectedIndex, isSelectionEmpty, listify, listValue, listValue, lookupRow, makeLu, maybePopup, notifyPopupActivationListeners, openOrReuse, openOrReuse, queryTable, queryTable, quoteIfRequired, relativePath, removeFromGlobalStoreList, removePopupActivationListener, removeTableModelListener, select, select, selectAdd, selectFrom, selectNew, setCwd, setCwd, setEditMode, setListSelectionModel, setPeriod, setRowNodeRemap, setShortName, setTableSpec, setTitle, setValueAt, setValueAt, showCube, showTable, toString, uniqueValues, uniqueValues, uniqueValues, uniqueValues, uniqueValues, uniqueValues, uniqueValues, uniqueValues, valueChanged

Methods inherited from class javax.swing.table.AbstractTableModel

fireTableCellUpdated, fireTableChanged, fireTableDataChanged, fireTableRowsDeleted, fireTableRowsInserted, fireTableRowsUpdated, fireTableStructureChanged, getListeners, getTableModelListeners

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, wait, wait, wait

Method Details

makeJDBCStore

public static RamAttributeStore makeJDBCStore(String connection,
 String query,
 Properties props)
                                       throws Exception

Create an AttributeStore object from an SQL query on a JDBC data source. The query will be executed, the results cached in memory and the database connection will be closed.

Parameters:

connection - the JDBC connection string

query - the SQL select query

props - A set of properties that are applicable to this driver

Throws:

Exception

registerJarDriver

public static boolean registerJarDriver(String path)

Load a JDBC driver contained in the jar file.

The driver class will only be loaded one time, even if this command is called multiple times with the same path argument, or even with different paths but containing the same driver.

Parameters:

path - the name of the jar file containing the JDBC driver

Returns:

true if the driver is loaded, false if it was already registered.

Throws:

IllegalStateException - If the jar file cannot be found or it does not contain JDBC service provider information or if the driver cannot be loaded. The IllegalStateException will wrap the original cause.