org.ametys.core.script

Class SQLScriptHelper

java.lang.Object

org.ametys.core.script.SQLScriptHelper

public final class SQLScriptHelper
extends Object

Example of simple use: SQLScriptHelper.createTableIfNotExists(dataSourceId, "QRTZ_JOB_DETAILS", "plugin:core://scripts/%s/quartz.sql", _sourceResolver); Will test if table QRTZ_JOB_DETAILS exits in database from datasource dataSourceId. If not, the script plugin:core://scripts/%s/quartz.sql will be resolved and executed (where %s is replaced by the database type 'mysql', 'derby'...) Tools to run SQL scripts.

Default separator for isolating statements is the semi colon character: ;.
It can be changed by using a comment like the following snippet for using the string ---:
-- _separator_=---
begin
execute immediate 'DROP TABLE MYTABLE';
Exception when others then null;
end;
---
-- _separator_=;
CREATE TABLE MYTABLE;
...
Note that the command must be placed at the end of the comment.

The runner can be configured to ignore SQLExceptions. This can be useful to execute DROP statements when it's unknown if the tables exist:
--_ignore_exceptions_=on
DROP TABLE MYTABLE;
--_ignore_exceptions_=off

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
protected static class	SQLScriptHelper.ScriptContext Script execution context.

Field Summary

Fields

Modifier and Type	Field and Description
protected static Logger	__LOGGER Logger available to subclasses.
static String	CHANGE_SEPARATOR_COMMAND Command to change the separator.
static String	DEFAULT_SEPARATOR Default separator used for isolating statements.
static String	IGNORE_EXCEPTIONS_COMMAND Command to ignore sql exceptions.

Constructor Summary

Constructors

Modifier	Constructor and Description
private	SQLScriptHelper()

Method Summary

Modifier and Type	Method and Description
private static void	_execute(Connection connection, String command, int lineNumber, SQLScriptHelper.ScriptContext scriptContext)
private static void	_processCommand(Connection connection, StringBuilder command, int lineNumber, SQLScriptHelper.ScriptContext scriptContext)
static boolean	createTableIfNotExists(Connection connection, String tableNameToCheck, String location, SourceResolver sourceResolver) This method will test if a table exists, and if not will execute a script to create it
static boolean	createTableIfNotExists(Connection connection, String tableNameToCheck, String location, SourceResolver sourceResolver, Map<String,String> replace) This method will test if a table exists, and if not will execute a script to create it
static boolean	createTableIfNotExists(String datasourceId, String tableNameToCheck, String location, SourceResolver sourceResolver) This method will test if a table exists, and if not will execute a script to create it
static boolean	createTableIfNotExists(String datasourceId, String tableNameToCheck, String location, SourceResolver sourceResolver, Map<String,String> replace) This method will test if a table exists, and if not will execute a script to create it
protected static boolean	processScriptLine(String line, StringBuilder commandBuffer, SQLScriptHelper.ScriptContext scriptContext) Process a script line.
static void	runScript(Connection connection, InputStream is) Run a SQL script using the connection passed in.
static void	runScript(Connection connection, String script) Run a SQL script using the connection passed in.
static boolean	tableExists(Connection connection, String tableName) Checks whether the given table exists in the database.

Methods inherited from class java.lang.Object

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

Field Detail

DEFAULT_SEPARATOR

public static final String DEFAULT_SEPARATOR

Default separator used for isolating statements.

See Also:

Constant Field Values

IGNORE_EXCEPTIONS_COMMAND

public static final String IGNORE_EXCEPTIONS_COMMAND

Command to ignore sql exceptions.

See Also:

Constant Field Values

CHANGE_SEPARATOR_COMMAND

public static final String CHANGE_SEPARATOR_COMMAND

Command to change the separator.

See Also:

Constant Field Values

__LOGGER

protected static final Logger __LOGGER

Logger available to subclasses.

Constructor Detail

SQLScriptHelper

private SQLScriptHelper()

Method Detail

createTableIfNotExists

public static boolean createTableIfNotExists(String datasourceId,
                                             String tableNameToCheck,
                                             String location,
                                             SourceResolver sourceResolver)
                                      throws SQLException,
                                             IOException

This method will test if a table exists, and if not will execute a script to create it

Parameters:

datasourceId - The data source id to open a connection to the database

tableNameToCheck - The name of the table that will be checked

location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.

sourceResolver - The source resolver

Returns:

true if the table was created, false otherwise

Throws:

SQLException - If an error occurred while executing SQL script, or while testing table existence

IOException - If an error occurred while getting the script file, or if the url is malformed

createTableIfNotExists

public static boolean createTableIfNotExists(String datasourceId,
                                             String tableNameToCheck,
                                             String location,
                                             SourceResolver sourceResolver,
                                             Map<String,String> replace)
                                      throws SQLException,
                                             IOException

This method will test if a table exists, and if not will execute a script to create it

Parameters:

datasourceId - The data source id to open a connection to the database

tableNameToCheck - The name of the table that will be checked

location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.

sourceResolver - The source resolver

replace - The map of string to replace. Key is the regexp to seek, value is the replacing string.

Returns:

true if the table was created, false otherwise

Throws:

SQLException - If an error occurred while executing SQL script, or while testing table existence

IOException - If an error occurred while getting the script file, or if the url is malformed

createTableIfNotExists

public static boolean createTableIfNotExists(Connection connection,
                                             String tableNameToCheck,
                                             String location,
                                             SourceResolver sourceResolver)
                                      throws SQLException,
                                             IOException

This method will test if a table exists, and if not will execute a script to create it

Parameters:

connection - The database connection to use

tableNameToCheck - The name of the table that will be checked

location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.

sourceResolver - The source resolver

Returns:

true if the table was created, false otherwise

Throws:

SQLException - If an error occurred while executing SQL script, or while testing table existence

IOException - If an error occurred while getting the script file, or if the url is malformed

createTableIfNotExists

public static boolean createTableIfNotExists(Connection connection,
                                             String tableNameToCheck,
                                             String location,
                                             SourceResolver sourceResolver,
                                             Map<String,String> replace)
                                      throws SQLException,
                                             IOException

This method will test if a table exists, and if not will execute a script to create it

Parameters:

connection - The database connection to use

tableNameToCheck - The name of the table that will be checked

location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.

sourceResolver - The source resolver

replace - The map of string to replace. Key is the regexp to seek, value is the replacing string.

Returns:

true if the table was created, false otherwise

Throws:

SQLException - If an error occurred while executing SQL script, or while testing table existence

IOException - If an error occurred while getting the script file, or if the url is malformed

tableExists

public static boolean tableExists(Connection connection,
                                  String tableName)
                           throws SQLException

Checks whether the given table exists in the database.

Parameters:

connection - The database connection

tableName - the name of the table

Returns:

true is the table exists

Throws:

SQLException - In an SQL exception occurs

runScript

public static void runScript(Connection connection,
                             String script)
                      throws IOException,
                             SQLException

Run a SQL script using the connection passed in.

Parameters:

connection - the connection to use for the script

script - the script data.

Throws:

IOException - if an error occurs while reading the script.

SQLException - if an error occurs while executing the script.

runScript

public static void runScript(Connection connection,
                             InputStream is)
                      throws IOException,
                             SQLException

Run a SQL script using the connection passed in.

Parameters:

connection - the connection to use for the script

is - the input stream containing the script data.

Throws:

IOException - if an error occurs while reading the script.

SQLException - if an error occurs while executing the script.

processScriptLine

protected static boolean processScriptLine(String line,
                                           StringBuilder commandBuffer,
                                           SQLScriptHelper.ScriptContext scriptContext)

Process a script line.

Parameters:

line - the line to process.

commandBuffer - the command buffer.

scriptContext - the script execution context.

Returns:

true to immediately process the command (a separator was found), false to process it later.

_processCommand

private static void _processCommand(Connection connection,
                                    StringBuilder command,
                                    int lineNumber,
                                    SQLScriptHelper.ScriptContext scriptContext)
                             throws SQLException

Throws:

SQLException

_execute

private static void _execute(Connection connection,
                             String command,
                             int lineNumber,
                             SQLScriptHelper.ScriptContext scriptContext)
                      throws SQLException

Throws:

SQLException