com.sage.orion.sdata.servlet
Class JDBCHelper

java.lang.Object
  com.sage.orion.sdata.servlet.JDBCHelper

public final class JDBCHelper
extends java.lang.Object

JDBC-specific constants and helpers

Nested Class Summary

static class

JDBCHelper.RequestPayloadProcessedInfo A read-only "struct-like" class that holds the processed information from the payload part of an SData insert (POST) or update (PUT) request.

Field Summary

static java.lang.String	AME_ABBREVIATION
static java.lang.String	AME_DISABLED the disabled attribute
static java.lang.String	INCOMPLETE_KEY_FOR_UPDATE
static java.lang.String	METADATA_EXTENSIONS the namespace for the Accpac Metadata Extensions
static java.lang.String	SCHEMA_NAMESPACE the namespace used for the accpac schemas

Method Summary

static org.w3c.dom.Element	addTypeToSchema(org.w3c.dom.Element schema, java.lang.String kindName) Add the resource kind type to the schema and return it ready to be populated.
static void	appendSchema(org.w3c.dom.Element schema, JDBCResourceKind resourceKind) Appends the necessary meta data extracted from the underlying JDBC table to the schema element
static boolean	checkRecord(java.sql.Connection connection, java.lang.String tableName, java.lang.String[] columnName, java.lang.String[] columnValue) Given a table name , column and its value return true if exist
static boolean	containsMaliciousCharacter(java.lang.String sqlToken) checks whether the column name and table name strings contains Malicious characters
static java.sql.PreparedStatement	createPreparedMetaDataCollectorStatement(java.sql.Connection connection, java.lang.String jdbcTableName) Creates the prepared statement to be used to get the metadata for the specified table.
static int	executeUpdateQuery(java.sql.Connection connection, java.lang.String query) Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
static java.lang.String	getColumnEnclosingFrontChar(JDBCDatabaseType databaseType) Get ColumnEnclosingFrontChar
static java.lang.String	getColumnEnclosingRearChar(JDBCDatabaseType databaseType) Get ColumnEnclosingRearChar
static org.w3c.dom.Node	getEntryRecordNode(SDataRequest request) Gets a copy of the XML node representing the record contained in the request entry's payload.
static java.sql.ResultSet	getResulSetFromQuery(java.sql.Statement statement, java.lang.String query) get the resultset object from the given Statement and query string.
static int	getRowCount(java.sql.ResultSet rs) Determines the number of rows in a ResultSet.
static boolean	isAccpac60DB(java.sql.Connection connection, java.lang.String table) Validates if the DB is Accpac 6.0 or 6.1 compatible.
static boolean	isTableExist(java.sql.Connection connection, java.lang.String tableName) Check for the given table is exist or not.
static int	populateJDBCTableFromXML(java.sql.Connection connection, org.w3c.dom.Document document, boolean createTableIfNotExists) Populate JDBC table from the parsed XML document using JDBC Connection.
static int	populateJDBCTableFromXML(java.sql.Connection connection, java.io.InputStream xmlStream, boolean createTableIfNotExists) parses the InputStream and populate JDBC table using the parsed ResourceKind definition
static int	populateJDBCTableFromXML(java.sql.Connection connection, java.lang.String fileName, boolean createTableIfNotExists) parses the XML file and populate JDBC table using the parsed XML file
static JDBCHelper.RequestPayloadProcessedInfo	processPayLoadColumns(org.w3c.dom.Node recordNode, java.lang.String[] keyColumnNames, java.sql.ResultSetMetaData jdbcTableMetaData, java.lang.String jdbcTableName, JDBCResourceKind resourceKind) Processes the request payload of an insert (POST) or update (PUT) to gather the columns (properties) that were passed in via that payload and to generate some column-related parts of insert/uppdate SQL expressions.

Methods inherited from class java.lang.Object

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

Field Detail

AME_ABBREVIATION

public static final java.lang.String AME_ABBREVIATION

See Also:

Constant Field Values

AME_DISABLED

public static final java.lang.String AME_DISABLED

the disabled attribute

See Also:

Constant Field Values

METADATA_EXTENSIONS

public static final java.lang.String METADATA_EXTENSIONS

the namespace for the Accpac Metadata Extensions

See Also:

Constant Field Values

SCHEMA_NAMESPACE

public static final java.lang.String SCHEMA_NAMESPACE

the namespace used for the accpac schemas

See Also:

Constant Field Values

INCOMPLETE_KEY_FOR_UPDATE

public static final java.lang.String INCOMPLETE_KEY_FOR_UPDATE

See Also:

Constant Field Values

Method Detail

populateJDBCTableFromXML

public static int populateJDBCTableFromXML(java.sql.Connection connection,
                                           java.lang.String fileName,
                                           boolean createTableIfNotExists)

parses the XML file and populate JDBC table using the parsed XML file

Parameters:

connection - Connection to connect database tables

fileName - the file name to be parsed.

createTableIfNotExists - flag used to restrict the creation of table in database
true - Create the table if not exists in database
and have the permission to drop the table if any error occurred while processing the further information(say adding foreign keys,index and while insert or update records)
false - Not creating the table even though it is not exists in the database

Returns:

the number of rows(records) inserted or updated

populateJDBCTableFromXML

public static int populateJDBCTableFromXML(java.sql.Connection connection,
                                           java.io.InputStream xmlStream,
                                           boolean createTableIfNotExists)

parses the InputStream and populate JDBC table using the parsed ResourceKind definition

Parameters:

connection - Connection to connect database tables

xmlStream - the InputStream to be parsed.

createTableIfNotExists - flag used to restrict the creation of table in database
true - Create the table if not exists in database
and have the permission to drop the table if any error occurred while processing the further information(say adding foreign keys,index and while insert or update records)
false - Not creating the table even though it is not exists in the database

Returns:

the number of rows(records) inserted or updated

populateJDBCTableFromXML

public static int populateJDBCTableFromXML(java.sql.Connection connection,
                                           org.w3c.dom.Document document,
                                           boolean createTableIfNotExists)

Populate JDBC table from the parsed XML document using JDBC Connection.

Parameters:

connection - Connection to connect database tables

document - the parsed XML document.

createTableIfNotExists - flag used to restrict the creation of table in database
true - Create the table if not exists in database
and have the permission to drop the table if any error occurred while processing the further information(say adding foreign keys,index and while insert or update records)
false - Not creating the table even though it is not exists in the database

Returns:

Returns the number of rows(records) inserted or updated

checkRecord

public static boolean checkRecord(java.sql.Connection connection,
                                  java.lang.String tableName,
                                  java.lang.String[] columnName,
                                  java.lang.String[] columnValue)

Given a table name , column and its value return true if exist

Parameters:

connection - Connection to connect database tables

tableName - String having table name.

columnName - Column name to be checked.

columnValue - Column value to be checked.

Returns:

return true - if the given record is exist.
false - if the given record is not exist.

isAccpac60DB

public static boolean isAccpac60DB(java.sql.Connection connection,
                                   java.lang.String table)

Validates if the DB is Accpac 6.0 or 6.1 compatible. Checks existence of specific column in the table by sending a query to DB, for example: "SELECT companyID FROM PreferenceStore" If there is no such column in DB the SQLException should arise and we intercept this exception and set isAccpac60DB flag to true.

Parameters:

connection - The JDBC connection which provides a JDBC connectivity

table - table name

Returns:

boolean flag false - if DB is Accpac 6.1 compatible
true - if DB is Accpac 6.0 compatible

executeUpdateQuery

public static int executeUpdateQuery(java.sql.Connection connection,
                                     java.lang.String query)

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

Parameters:

connection - Connection to connect database tables

query - query string want to execute

Returns:

status of the execution. For DML returns number of rows affected.
For DDL returns nothing (0)

isTableExist

public static boolean isTableExist(java.sql.Connection connection,
                                   java.lang.String tableName)

Check for the given table is exist or not.

Parameters:

connection - Connection to connect database tables

tableName - String having table name.

Returns:

return true (if Table exists)
false - if Table is not exist

getResulSetFromQuery

public static java.sql.ResultSet getResulSetFromQuery(java.sql.Statement statement,
                                                      java.lang.String query)
                                               throws java.sql.SQLException

get the resultset object from the given Statement and query string.

Parameters:

statement - statement

query - query string

Returns:

ResultSet result set

Throws:

java.sql.SQLException - SQLException

containsMaliciousCharacter

public static boolean containsMaliciousCharacter(java.lang.String sqlToken)

checks whether the column name and table name strings contains Malicious characters

Parameters:

sqlToken - SQL token string

Returns:

false - if it doesn't contain any Malicious characters

addTypeToSchema

public static org.w3c.dom.Element addTypeToSchema(org.w3c.dom.Element schema,
                                                  java.lang.String kindName)

Add the resource kind type to the schema and return it ready to be populated.

Parameters:

schema - the schema element that holds everything

kindName - the base name of the resource kind

Returns:

the Element that is the All of the resource kind type, ready to be populated

appendSchema

public static void appendSchema(org.w3c.dom.Element schema,
                                JDBCResourceKind resourceKind)

Appends the necessary meta data extracted from the underlying JDBC table to the schema element

Parameters:

schema - the schema element to be appended

resourceKind - the meta data provider

getEntryRecordNode

public static org.w3c.dom.Node getEntryRecordNode(SDataRequest request)

Gets a copy of the XML node representing the record contained in the request entry's payload. This record node is the child node of the <sdata:payload> tag of the request's entry payload. This record node contains nodes that contain the column data for that record (although the data for some columns may not be present).

NOTE: This method never returns null - it throws an exception if it encounters a problem.

Parameters:

request - the SDataRequest which gives the payload content.

Returns:

A copy of the XML node representing the record contained in the request entry's payload.

createPreparedMetaDataCollectorStatement

public static java.sql.PreparedStatement createPreparedMetaDataCollectorStatement(java.sql.Connection connection,
                                                                                  java.lang.String jdbcTableName)
                                                                           throws java.sql.SQLException

Creates the prepared statement to be used to get the metadata for the specified table.

NOTE: The caller (or a caller of the caller) is responsible for controlling the lifetime of the returned prepared statement object (such as closing it).

Parameters:

connection - the JDBC connection object which provides a pathway to access the database

jdbcTableName - the name of the JDBC table whose metadata is going to be retrieved.

Returns:

The prepared statement to be used to get the metadata for the specified table.

Throws:

java.sql.SQLException - SQLException

processPayLoadColumns

public static JDBCHelper.RequestPayloadProcessedInfo processPayLoadColumns(org.w3c.dom.Node recordNode,
                                                                           java.lang.String[] keyColumnNames,
                                                                           java.sql.ResultSetMetaData jdbcTableMetaData,
                                                                           java.lang.String jdbcTableName,
                                                                           JDBCResourceKind resourceKind)
                                                                    throws org.w3c.dom.DOMException,
                                                                           java.sql.SQLException

Processes the request payload of an insert (POST) or update (PUT) to gather the columns (properties) that were passed in via that payload and to generate some column-related parts of insert/uppdate SQL expressions.

Parameters:

recordNode - the node containing record information for the request payload of an insert (POST) or update (PUT).

keyColumnNames - the array of column names for the columns belonging to the key (presumably a unique key) of the table.

jdbcTableMetaData - The JDBC table's meta data (which includes the type information for its columns).

jdbcTableName - The JDBC table name

resourceKind - The JDBC resource kind

Returns:

The information object representing the processed request payload of an insert (POST) or update (PUT).

Throws:

org.w3c.dom.DOMException - SQLException

java.sql.SQLException - SQLException

getColumnEnclosingFrontChar

public static java.lang.String getColumnEnclosingFrontChar(JDBCDatabaseType databaseType)

Get ColumnEnclosingFrontChar

Parameters:

databaseType - JDBC database type

Returns:

ColumnEnclosingFrontChar string

getColumnEnclosingRearChar

public static java.lang.String getColumnEnclosingRearChar(JDBCDatabaseType databaseType)

Get ColumnEnclosingRearChar

Parameters:

databaseType - JDBC database type

Returns:

ColumnEnclosingRearChar string

getRowCount

public static int getRowCount(java.sql.ResultSet rs)
                       throws java.sql.SQLException

Determines the number of rows in a ResultSet. Upon exit, if the cursor was not currently on a row, it is just before the first row in the result set (a call to ResultSet.next() will go to the first row).

Parameters:

rs - The ResultSet to check (must be scrollable).

Returns:

rowCount The number of rows.

Throws:

java.sql.SQLException - If the ResultSet is not scrollable.