er.extensions.jdbc
Class ERXJDBCUtilities

java.lang.Object
  er.extensions.jdbc.ERXJDBCUtilities

public class ERXJDBCUtilities
extends Object

Nested Class Summary

static class	ERXJDBCUtilities.CopyTask
static interface	ERXJDBCUtilities.IConnectionDelegate IConnectionDelegate is like a closure for connection operations.
static interface	ERXJDBCUtilities.IResultSetDelegate IResultSetDelegate is like a closure for ResultSet operations.

Field Summary

static Logger	log
static NSTimestampFormatter	TIMESTAMP_FORMATTER

Constructor Summary

ERXJDBCUtilities()

Method Summary

static void	_copyDatabaseDefinedByEOModelAndConnectionDictionaryToDatabaseWithConnectionDictionary(EOModelGroup modelGroup, NSDictionary sourceDict, NSDictionary destDict)
static void	_copyDatabaseDefinedByEOModelAndConnectionDictionaryToDatabaseWithConnectionDictionary(EOModel m, NSDictionary sourceDict, NSDictionary destDict) Copies all rows from one database to another database.
static EOAdaptorChannel	adaptorChannelWithUserAndPassword(EOModel model, String userName, String password) Returns an adaptor channel with the given username and password.
static EOAdaptorChannel	adaptorChannelWithUserAndPassword(String adaptorName, NSDictionary originalConnectionDictionary, String userName, String password) Returns an adaptor channel with the given username and password.
static void	createTablesForEntities(EOAdaptorChannel channel, NSArray<EOEntity> entities) Creates tables, primary keys, and foreign keys for the given list of entities.
static void	createTablesForModel(EOAdaptorChannel channel, EOModel model) Creates tables, primary keys, and foreign keys for the tables in the given model.
static String	databaseProductName(EOAdaptorChannel channel) Returns the name of the database product for the given channel (handy when loading database-vendor-specific sql scripts in migrations).
static String	databaseProductName(EOModel model) Returns the name of the database product for the given an eomodel (handy when loading database-vendor-specific sql scripts in migrations).
static void	dropTablesForEntities(EOAdaptorChannel channel, NSArray<EOEntity> entities, boolean ignoreFailures) Drops tables, primary keys, and foreign keys for the given list of entities.
static void	dropTablesForModel(EOAdaptorChannel channel, EOModel model, boolean ignoreFailures) Drops tables, primary keys, and foreign keys for the tables in the given model.
static void	executeQuery(EOAdaptorChannel adaptorChannel, String query, ERXJDBCUtilities.IResultSetDelegate delegate) Using the backing connection from the adaptor context, executes the given query and calls delegate.processResultSet(rs) once for the ResultSet.
static int	executeUpdate(EOAdaptorChannel channel, String sql) Shortcut to java.sql.Statement.executeUpdate(..) that operates on an EOAdaptorChannel.
static int	executeUpdate(EOAdaptorChannel channel, String sql, boolean autoCommit) Shortcut to java.sql.Statement.executeUpdate(..) that operates on an EOAdaptorChannel.
static int	executeUpdateScript(EOAdaptorChannel channel, NSArray<String> sqlStatements) Splits the given sqlscript and executes each of the statements in a single transaction
static int	executeUpdateScript(EOAdaptorChannel channel, NSArray<String> sqlStatements, boolean ignoreFailures) Splits the given sqlscript and executes each of the statements in a single transaction
static int	executeUpdateScript(EOAdaptorChannel channel, String sqlScript) Splits the given sqlscript and executes each of the statements in a single transaction
static int	executeUpdateScript(EOAdaptorChannel channel, String sqlScript, boolean ignoreFailures) Splits the given sqlscript and executes each of the statements in a single transaction
static int	executeUpdateScriptFromResourceNamed(EOAdaptorChannel channel, String resourceName, String frameworkName) Executes a SQL script that is stored as a resource.
static int	executeUpdateScriptIgnoringErrors(EOAdaptorChannel channel, String script) Deprecated. use executeUpdateScript with the boolean param
static javax.sql.rowset.CachedRowSet	fetchRowSet(EOAdaptorChannel adaptorChannel, String query) Using the backing connection from the adaptor context, executes the given query and returns a CachedRowSet of the results.
static String	jdbcTimestamp(NSTimestamp t)
static void	processConnection(EOAdaptorChannel adaptorChannel, ERXJDBCUtilities.IConnectionDelegate delegate) Using the backing connection from the adaptor context, executes the given query and calls delegate.processConnection(conn) for the Connection.
static void	processResultSetRows(EOAdaptorChannel adaptorChannel, String query, ERXJDBCUtilities.IResultSetDelegate delegate) Using the backing connection from the adaptor context, executes the given query and calls processor.process(rs) for each row of the ResultSet.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

log

public static Logger log

TIMESTAMP_FORMATTER

public static NSTimestampFormatter TIMESTAMP_FORMATTER

Constructor Detail

ERXJDBCUtilities

public ERXJDBCUtilities()

Method Detail

jdbcTimestamp

public static String jdbcTimestamp(NSTimestamp t)

_copyDatabaseDefinedByEOModelAndConnectionDictionaryToDatabaseWithConnectionDictionary

public static void _copyDatabaseDefinedByEOModelAndConnectionDictionaryToDatabaseWithConnectionDictionary(EOModel m,
                                                                                                          NSDictionary sourceDict,
                                                                                                          NSDictionary destDict)

Copies all rows from one database to another database. The tables must exist before calling this method. Example:

 NSMutableDictionary sourceDict = new NSMutableDictionary();
 sourceDict.setObjectForKey("YourPassword", "password");
 sourceDict.setObjectForKey("YourUserName", "username");
 sourceDict.setObjectForKey("jdbc:FrontBase://127.0.0.1/YourSourceDatabase", "URL");
 sourceDict.setObjectForKey("com.frontbase.jdbc.FBJDriver", "driver");
 sourceDict.setObjectForKey(Boolean.FALSE.toString(), "autoCommit");
 sourceDict.setObjectForKey(Boolean.TRUE.toString(), "readOnly");
 sourceDict.setObjectForKey(Boolean.TRUE.toString(), "quote");
 
 NSMutableDictionary destDict = sourceDict.mutableClone();
 destDict.setObjectForKey("jdbc:postgresql://localhost/YourDestinationDatabase", "URL");
 destDict.setObjectForKey("YourPassword", "password");
 destDict.setObjectForKey("YourUserName", "username");
 destDict.setObjectForKey("org.postgresql.Driver", "driver");
 destDict.setObjectForKey(Boolean.FALSE.toString(), "autoCommit");
 destDict.setObjectForKey(Boolean.FALSE.toString(), "readOnly");
 destDict.setObjectForKey(Boolean.FALSE.toString(), "quote");
 
 EOModel model = EOModelGroup.defaultGroup().modelNamed("YourModelName");
 ERXJDBCUtilities._copyDatabaseDefinedByEOModelAndConnectionDictionaryToDatabaseWithConnectionDictionary(model, sourceDict, destDict);
 

Parameters:

m - the model that defines the database to copy

sourceDict - a NSDictionary containing the following keys for the source database:

1. username, the username for the connection
2. password, the password for the connection
3. url, the JDBC URL for the connection, for FrontBase its jdbc:FrontBase://host/database , for PostgreSQL its jdbc:postgresql://host/database
4. driver, the full class name of the driver, for FrontBase its com.frontbase.jdbc.FBJDriver , for PostgreSQL its org.postgresql.Driver
5. autoCommit, a Boolean defining if autoCommit should be on or off, default is true
6. readOnly, a Boolean defining if the Connection is readOnly or not, default is false. Its a good
7. quote, a Boolean defining if the table and field names should be "quoted" idea to make the sourceDict readOnly, because one does not write.

destDict - same as sourceDict just used for the destination database.

_copyDatabaseDefinedByEOModelAndConnectionDictionaryToDatabaseWithConnectionDictionary

public static void _copyDatabaseDefinedByEOModelAndConnectionDictionaryToDatabaseWithConnectionDictionary(EOModelGroup modelGroup,
                                                                                                          NSDictionary sourceDict,
                                                                                                          NSDictionary destDict)

Parameters:

modelGroup - the model group to copy

sourceDict - the source connection dictionary

destDict - the destination connection dictionary

See Also:

ERXJDBCUtilities._copyDatabaseDefinedByEOModelAndConnectionDictionaryToDatabaseWithConnectionDictionary(EOModel, NSDictionary, NSDictionary)

adaptorChannelWithUserAndPassword

public static EOAdaptorChannel adaptorChannelWithUserAndPassword(EOModel model,
                                                                 String userName,
                                                                 String password)

Returns an adaptor channel with the given username and password.

Parameters:

model - the model to base this connection off of

userName - the new username

password - the new password

Returns:

a new adaptor channel

adaptorChannelWithUserAndPassword

public static EOAdaptorChannel adaptorChannelWithUserAndPassword(String adaptorName,
                                                                 NSDictionary originalConnectionDictionary,
                                                                 String userName,
                                                                 String password)

Returns an adaptor channel with the given username and password.

Parameters:

adaptorName - the name of the adaptor to user

originalConnectionDictionary - the original connection dictionary

userName - the new username

password - the new password

Returns:

a new adaptor channel

executeUpdate

public static int executeUpdate(EOAdaptorChannel channel,
                                String sql)
                         throws SQLException

Shortcut to java.sql.Statement.executeUpdate(..) that operates on an EOAdaptorChannel.

Parameters:

channel - the JDBCChannel to work with

sql - the sql to execute

Returns:

the number of rows updated

Throws:

SQLException - if there is a problem

executeUpdate

public static int executeUpdate(EOAdaptorChannel channel,
                                String sql,
                                boolean autoCommit)
                         throws SQLException

Shortcut to java.sql.Statement.executeUpdate(..) that operates on an EOAdaptorChannel. and optionally commits.

Parameters:

channel - the JDBCChannel to work with

sql - the sql to execute

autoCommit - if true, autocommit the connection after executing

Returns:

the number of rows updated

Throws:

SQLException - if there is a problem

executeUpdateScript

public static int executeUpdateScript(EOAdaptorChannel channel,
                                      String sqlScript)
                               throws SQLException

Splits the given sqlscript and executes each of the statements in a single transaction

Parameters:

channel - the JDBCChannel to work with

sqlScript - the sql script to execute

Returns:

the number of rows updated

Throws:

SQLException - if there is a problem

executeUpdateScript

public static int executeUpdateScript(EOAdaptorChannel channel,
                                      String sqlScript,
                                      boolean ignoreFailures)
                               throws SQLException

Splits the given sqlscript and executes each of the statements in a single transaction

Parameters:

channel - the JDBCChannel to work with

sqlScript - the sql script to execute

ignoreFailures - if true, failures in a particular statement are ignored

Returns:

the number of rows updated

Throws:

SQLException - if there is a problem

executeUpdateScript

public static int executeUpdateScript(EOAdaptorChannel channel,
                                      NSArray<String> sqlStatements)
                               throws SQLException

Splits the given sqlscript and executes each of the statements in a single transaction

Parameters:

channel - the JDBCChannel to work with

sqlStatements - the array of sql scripts to execute

Returns:

the number of rows updated

Throws:

SQLException - if there is a problem

executeUpdateScript

public static int executeUpdateScript(EOAdaptorChannel channel,
                                      NSArray<String> sqlStatements,
                                      boolean ignoreFailures)
                               throws SQLException

Splits the given sqlscript and executes each of the statements in a single transaction

Parameters:

channel - the JDBCChannel to work with

sqlStatements - the array of sql scripts to execute

ignoreFailures - if true, failures in a particular statement are ignored

Returns:

the number of rows updated

Throws:

SQLException - if there is a problem

executeUpdateScriptIgnoringErrors

public static int executeUpdateScriptIgnoringErrors(EOAdaptorChannel channel,
                                                    String script)
                                             throws SQLException

Deprecated. use executeUpdateScript with the boolean param

Runs a given sql script and executes each of the statements in a one transaction.

Parameters:

channel - the JDBCChannel to work with

script - the array of sql scripts to execute

Returns:

the number of rows updated

Throws:

SQLException - if there is a problem

executeUpdateScriptFromResourceNamed

public static int executeUpdateScriptFromResourceNamed(EOAdaptorChannel channel,
                                                       String resourceName,
                                                       String frameworkName)
                                                throws SQLException,
                                                       IOException

Executes a SQL script that is stored as a resource.

Parameters:

channel - the channel to execute the scripts within

resourceName - the name of the SQL script resource

frameworkName - the name of the framework that contains the resource

Returns:

the number of rows updated

Throws:

SQLException - if a SQL error occurs

IOException - if an error occurs reading the script

dropTablesForModel

public static void dropTablesForModel(EOAdaptorChannel channel,
                                      EOModel model,
                                      boolean ignoreFailures)
                               throws SQLException

Drops tables, primary keys, and foreign keys for the tables in the given model.

Parameters:

channel - the channel to use for execution

model - the model to drop tables for

ignoreFailures - if true, failures in a particular statement are ignored

Throws:

SQLException - if something fails

dropTablesForEntities

public static void dropTablesForEntities(EOAdaptorChannel channel,
                                         NSArray<EOEntity> entities,
                                         boolean ignoreFailures)
                                  throws SQLException

Drops tables, primary keys, and foreign keys for the given list of entities. This is useful in your Migration #0 class.

Parameters:

channel - the channel to use for execution

entities - the entities to drop tables for

ignoreFailures - if true, failures in a particular statement are ignored

Throws:

SQLException - if something fails

createTablesForModel

public static void createTablesForModel(EOAdaptorChannel channel,
                                        EOModel model)
                                 throws SQLException

Creates tables, primary keys, and foreign keys for the tables in the given model. This is useful in your Migration #0 class.

Parameters:

channel - the channel to use for execution

model - the model to create tables for

Throws:

SQLException - if something fails

createTablesForEntities

public static void createTablesForEntities(EOAdaptorChannel channel,
                                           NSArray<EOEntity> entities)
                                    throws SQLException

Creates tables, primary keys, and foreign keys for the given list of entities. This is useful in your Migration #0 class.

Parameters:

channel - the channel to use for execution

entities - the entities to create tables for

Throws:

SQLException - if something fails

databaseProductName

public static String databaseProductName(EOAdaptorChannel channel)

Returns the name of the database product for the given channel (handy when loading database-vendor-specific sql scripts in migrations).

Parameters:

channel - the channel

Returns:

the database the database product name ("FrontBase", "PostgreSQL")

databaseProductName

public static String databaseProductName(EOModel model)

Returns the name of the database product for the given an eomodel (handy when loading database-vendor-specific sql scripts in migrations).

Parameters:

model - the EOModel

Returns:

the database the database product name ("FrontBase", "PostgreSQL")

processConnection

public static void processConnection(EOAdaptorChannel adaptorChannel,
                                     ERXJDBCUtilities.IConnectionDelegate delegate)
                              throws Exception

Using the backing connection from the adaptor context, executes the given query and calls delegate.processConnection(conn) for the Connection. This handles properly closing all the underlying JDBC resources.

Parameters:

adaptorChannel - the adaptor channel

delegate - the connection delegate

Throws:

Exception - if something goes wrong

executeQuery

public static void executeQuery(EOAdaptorChannel adaptorChannel,
                                String query,
                                ERXJDBCUtilities.IResultSetDelegate delegate)
                         throws Exception

Using the backing connection from the adaptor context, executes the given query and calls delegate.processResultSet(rs) once for the ResultSet. This handles properly closing all the underlying JDBC resources.

Parameters:

adaptorChannel - the adaptor channel

query - the query to execute

delegate - the processor delegate

Throws:

Exception - if something goes wrong

processResultSetRows

public static void processResultSetRows(EOAdaptorChannel adaptorChannel,
                                        String query,
                                        ERXJDBCUtilities.IResultSetDelegate delegate)
                                 throws Exception

Using the backing connection from the adaptor context, executes the given query and calls processor.process(rs) for each row of the ResultSet. This handles properly closing all the underlying JDBC resources.

Parameters:

adaptorChannel - the adaptor channel

query - the query to execute

delegate - the processor delegate

Throws:

Exception - if something goes wrong

fetchRowSet

public static javax.sql.rowset.CachedRowSet fetchRowSet(EOAdaptorChannel adaptorChannel,
                                                        String query)
                                                 throws Exception

Using the backing connection from the adaptor context, executes the given query and returns a CachedRowSet of the results. This can be useful for more complicated migrations. This handles properly closing all the underlying JDBC resources.

Parameters:

adaptorChannel - the adaptor channel

query - the query to execute

Returns:

a CachedRowSet of the results

Throws:

Exception - if something goes wrong