Package nl.nn.adapterframework.util

Class JdbcUtil

java.lang.Object

nl.nn.adapterframework.util.JdbcUtil

public class JdbcUtil
extends Object

Database-oriented utility functions.

Since:

4.1

Author:

Gerrit van Brakel

Field Summary

Fields

Modifier and Type	Field	Description
protected static org.apache.logging.log4j.Logger	log

Constructor Summary

Constructors

Constructor	Description
JdbcUtil()

Method Summary

Modifier and Type	Method	Description
static void	applyParameters(IDbmsSupport dbmsSupport, PreparedStatement statement, ParameterList parameters, Message message, PipeLineSession session)
static void	applyParameters(IDbmsSupport dbmsSupport, PreparedStatement statement, ParameterValueList parameters, PipeLineSession session)
static void	close(Connection connection)
static String	executeBlobQuery(IDbmsSupport dbmsSupport, Connection connection, String query)
static Object	executeQuery(IDbmsSupport dbmsSupport, Connection connection, String query, ParameterValueList parameterValues, PipeLineSession session)
static void	executeStatement(Connection connection, String query, Object... params)	Executes the given SQL statement.
static void	executeStatement(IDbmsSupport dbmsSupport, Connection connection, String query, ParameterValueList parameterValues, PipeLineSession session)
static void	fullClose(Connection connection, ResultSet rs)
static void	fullClose(Connection connection, Statement statement)	Note: Depending on the connect pool used (for example with Tomcat 7) the connection retrieved from the statement will be the direct connection instead of the proxied connection.
static String	getBlobAsString(InputStream blobInputStream, String column, String charset, boolean blobSmartGet, boolean encodeBlobBase64)
static String	getBlobAsString(IDbmsSupport dbmsSupport, ResultSet rs, int column, String charset, boolean blobIsCompressed, boolean blobSmartGet, boolean encodeBlobBase64)
static String	getBlobAsString(IDbmsSupport dbmsSupport, ResultSet rs, String column, String charset, boolean blobIsCompressed, boolean blobSmartGet, boolean encodeBlobBase64)
static InputStream	getBlobInputStream(IDbmsSupport dbmsSupport, ResultSet rs, int column, boolean blobIsCompressed)
static InputStream	getBlobInputStream(IDbmsSupport dbmsSupport, ResultSet rs, String column, boolean blobIsCompressed)
static OutputStream	getBlobOutputStream(IDbmsSupport dbmsSupport, Object blobUpdateHandle, PreparedStatement stmt, int columnIndex, boolean compressBlob)
static OutputStream	getBlobOutputStream(IDbmsSupport dbmsSupport, Object blobUpdateHandle, ResultSet rs, int columnIndex, boolean compressBlob)
static Reader	getBlobReader(InputStream blobInputStream, String charset)
static Reader	getBlobReader(IDbmsSupport dbmsSupport, ResultSet rs, int column, String charset, boolean blobIsCompressed)
static Reader	getBlobReader(IDbmsSupport dbmsSupport, ResultSet rs, String column, String charset, boolean blobIsCompressed)
static Writer	getBlobWriter(IDbmsSupport dbmsSupport, Object blobUpdateHandle, ResultSet rs, int columnIndex, String charset, boolean compressBlob)
static String	getClobAsString(IDbmsSupport dbmsSupport, ResultSet rs, int columnIndex, boolean xmlEncode)
static String	getClobAsString(IDbmsSupport dbmsSupport, ResultSet rs, String columnName, boolean xmlEncode)
static String	getValue(IDbmsSupport dbmsSupport, ResultSet rs, int colNum, ResultSetMetaData rsmeta, String blobCharset, boolean decompressBlobs, String nullValue, boolean trimSpaces, boolean getBlobSmart, boolean encodeBlobBase64)
static Writer	getWriter(Object target)	Deprecated.
static boolean	isNumeric(int sqlTYpe)
static boolean	isQueryResultEmpty(Connection connection, String query)
static SQLType	mapParameterTypeToSqlType(Parameter.ParameterType parameterType)
static void	putByteArrayAsBlob(IDbmsSupport dbmsSupport, ResultSet rs, int columnIndex, byte[] content, boolean compressBlob)
static void	putStringAsBlob(IDbmsSupport dbmsSupport, ResultSet rs, int columnIndex, String content, String charset, boolean compressBlob)
static void	putStringAsClob(IDbmsSupport dbmsSupport, ResultSet rs, int columnIndex, String content)
static String	selectAllFromTable(IDbmsSupport dbmsSupport, Connection conn, String tableName, String orderBy)
static void	setParameter(PreparedStatement statement, int parameterIndex, String value, boolean parameterTypeMatchRequired)
static void	streamBlob(InputStream blobInputStream, String charset, Base64Pipe.Direction blobBase64Direction, Object target, boolean close)
static void	streamBlob(IDbmsSupport dbmsSupport, ResultSet rs, int columnIndex, String charset, boolean blobIsCompressed, Base64Pipe.Direction blobBase64Direction, Object target, boolean close)
static void	streamBlob(IDbmsSupport dbmsSupport, ResultSet rs, String columnName, String charset, boolean blobIsCompressed, Base64Pipe.Direction blobBase64Direction, Object target, boolean close)
static void	streamClob(IDbmsSupport dbmsSupport, ResultSet rs, int column, Object target, boolean close)
static void	warningsToDocument(SQLWarning warnings, ObjectBuilder parent)
static String	warningsToString(SQLWarning warnings)	Deprecated.
static void	warningsToXml(SQLWarning warnings, XmlBuilder parent)	Deprecated.
static void	warningsToXml(SQLWarning warnings, SaxElementBuilder parent)
static XmlBuilder	warningsToXmlBuilder(SQLWarning warnings)	Deprecated.

Methods inherited from class java.lang.Object

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

Field Detail

log

protected static org.apache.logging.log4j.Logger log

Constructor Detail

JdbcUtil

public JdbcUtil()

Method Detail

warningsToString

@Deprecated
public static String warningsToString(SQLWarning warnings)

Deprecated.

warningsToXml

@Deprecated
public static void warningsToXml(SQLWarning warnings,
                                 XmlBuilder parent)

Deprecated.

warningsToXmlBuilder

@Deprecated
public static XmlBuilder warningsToXmlBuilder(SQLWarning warnings)

Deprecated.

warningsToXml

public static void warningsToXml(SQLWarning warnings,
                                 SaxElementBuilder parent)
                          throws SAXException

Throws:

SAXException

warningsToDocument

public static void warningsToDocument(SQLWarning warnings,
                                      ObjectBuilder parent)
                               throws SAXException

Throws:

SAXException

getValue

public static String getValue(IDbmsSupport dbmsSupport,
                              ResultSet rs,
                              int colNum,
                              ResultSetMetaData rsmeta,
                              String blobCharset,
                              boolean decompressBlobs,
                              String nullValue,
                              boolean trimSpaces,
                              boolean getBlobSmart,
                              boolean encodeBlobBase64)
                       throws IOException,
                              SQLException

Throws:

IOException

SQLException

getBlobInputStream

public static InputStream getBlobInputStream(IDbmsSupport dbmsSupport,
                                             ResultSet rs,
                                             int column,
                                             boolean blobIsCompressed)
                                      throws SQLException,
                                             JdbcException,
                                             DbmsException

Throws:

SQLException

JdbcException

DbmsException

getBlobInputStream

public static InputStream getBlobInputStream(IDbmsSupport dbmsSupport,
                                             ResultSet rs,
                                             String column,
                                             boolean blobIsCompressed)
                                      throws SQLException,
                                             JdbcException,
                                             DbmsException

Throws:

SQLException

JdbcException

DbmsException

getBlobReader

public static Reader getBlobReader(IDbmsSupport dbmsSupport,
                                   ResultSet rs,
                                   int column,
                                   String charset,
                                   boolean blobIsCompressed)
                            throws IOException,
                                   JdbcException,
                                   SQLException,
                                   DbmsException

Throws:

IOException

JdbcException

SQLException

DbmsException

getBlobReader

public static Reader getBlobReader(IDbmsSupport dbmsSupport,
                                   ResultSet rs,
                                   String column,
                                   String charset,
                                   boolean blobIsCompressed)
                            throws IOException,
                                   JdbcException,
                                   SQLException,
                                   DbmsException

Throws:

IOException

JdbcException

SQLException

DbmsException

getBlobReader

public static Reader getBlobReader(InputStream blobInputStream,
                                   String charset)
                            throws IOException

Throws:

IOException

streamBlob

public static void streamBlob(IDbmsSupport dbmsSupport,
                              ResultSet rs,
                              int columnIndex,
                              String charset,
                              boolean blobIsCompressed,
                              Base64Pipe.Direction blobBase64Direction,
                              Object target,
                              boolean close)
                       throws JdbcException,
                              SQLException,
                              IOException,
                              DbmsException

Throws:

JdbcException

SQLException

IOException

DbmsException

streamBlob

public static void streamBlob(IDbmsSupport dbmsSupport,
                              ResultSet rs,
                              String columnName,
                              String charset,
                              boolean blobIsCompressed,
                              Base64Pipe.Direction blobBase64Direction,
                              Object target,
                              boolean close)
                       throws JdbcException,
                              SQLException,
                              IOException,
                              DbmsException

Throws:

JdbcException

SQLException

IOException

DbmsException

streamBlob

public static void streamBlob(InputStream blobInputStream,
                              String charset,
                              Base64Pipe.Direction blobBase64Direction,
                              Object target,
                              boolean close)
                       throws JdbcException,
                              IOException

Throws:

JdbcException

IOException

getWriter

@Deprecated
public static Writer getWriter(Object target)
                        throws IOException

Deprecated.

Throws:

IOException

streamClob

public static void streamClob(IDbmsSupport dbmsSupport,
                              ResultSet rs,
                              int column,
                              Object target,
                              boolean close)
                       throws DbmsException,
                              SQLException,
                              IOException

Throws:

DbmsException

SQLException

IOException

getBlobAsString

public static String getBlobAsString(IDbmsSupport dbmsSupport,
                                     ResultSet rs,
                                     int column,
                                     String charset,
                                     boolean blobIsCompressed,
                                     boolean blobSmartGet,
                                     boolean encodeBlobBase64)
                              throws IOException,
                                     JdbcException,
                                     SQLException,
                                     DbmsException

Throws:

IOException

JdbcException

SQLException

DbmsException

getBlobAsString

public static String getBlobAsString(IDbmsSupport dbmsSupport,
                                     ResultSet rs,
                                     String column,
                                     String charset,
                                     boolean blobIsCompressed,
                                     boolean blobSmartGet,
                                     boolean encodeBlobBase64)
                              throws IOException,
                                     JdbcException,
                                     SQLException,
                                     DbmsException

Throws:

IOException

JdbcException

SQLException

DbmsException

getBlobAsString

public static String getBlobAsString(InputStream blobInputStream,
                                     String column,
                                     String charset,
                                     boolean blobSmartGet,
                                     boolean encodeBlobBase64)
                              throws IOException,
                                     JdbcException

Throws:

IOException

JdbcException

getBlobOutputStream

public static OutputStream getBlobOutputStream(IDbmsSupport dbmsSupport,
                                               Object blobUpdateHandle,
                                               ResultSet rs,
                                               int columnIndex,
                                               boolean compressBlob)
                                        throws SQLException,
                                               DbmsException

Throws:

SQLException

DbmsException

getBlobOutputStream

public static OutputStream getBlobOutputStream(IDbmsSupport dbmsSupport,
                                               Object blobUpdateHandle,
                                               PreparedStatement stmt,
                                               int columnIndex,
                                               boolean compressBlob)
                                        throws DbmsException,
                                               SQLException

Throws:

DbmsException

SQLException

getBlobWriter

public static Writer getBlobWriter(IDbmsSupport dbmsSupport,
                                   Object blobUpdateHandle,
                                   ResultSet rs,
                                   int columnIndex,
                                   String charset,
                                   boolean compressBlob)
                            throws IOException,
                                   DbmsException,
                                   SQLException

Throws:

IOException

DbmsException

SQLException

putStringAsBlob

public static void putStringAsBlob(IDbmsSupport dbmsSupport,
                                   ResultSet rs,
                                   int columnIndex,
                                   String content,
                                   String charset,
                                   boolean compressBlob)
                            throws IOException,
                                   DbmsException,
                                   SQLException

Throws:

IOException

DbmsException

SQLException

putByteArrayAsBlob

public static void putByteArrayAsBlob(IDbmsSupport dbmsSupport,
                                      ResultSet rs,
                                      int columnIndex,
                                      byte[] content,
                                      boolean compressBlob)
                               throws IOException,
                                      DbmsException,
                                      SQLException

Throws:

IOException

DbmsException

SQLException

getClobAsString

public static String getClobAsString(IDbmsSupport dbmsSupport,
                                     ResultSet rs,
                                     int columnIndex,
                                     boolean xmlEncode)
                              throws IOException,
                                     JdbcException,
                                     SQLException,
                                     DbmsException

Throws:

IOException

JdbcException

SQLException

DbmsException

getClobAsString

public static String getClobAsString(IDbmsSupport dbmsSupport,
                                     ResultSet rs,
                                     String columnName,
                                     boolean xmlEncode)
                              throws IOException,
                                     DbmsException,
                                     SQLException

Throws:

IOException

DbmsException

SQLException

putStringAsClob

public static void putStringAsClob(IDbmsSupport dbmsSupport,
                                   ResultSet rs,
                                   int columnIndex,
                                   String content)
                            throws IOException,
                                   DbmsException,
                                   SQLException

Throws:

IOException

DbmsException

SQLException

fullClose

public static void fullClose(Connection connection,
                             ResultSet rs)

fullClose

public static void fullClose(Connection connection,
                             Statement statement)

Note: Depending on the connect pool used (for example with Tomcat 7) the connection retrieved from the statement will be the direct connection instead of the proxied connection. After a close on this (unproxied) connection the transaction manager isn't able to do a commit anymore. Hence, this method doesn't get it from the statement but has an extra connection parameter.

Parameters:

connection - the proxied/original connection the statement was created with

statement - the statement to close

close

public static void close(Connection connection)

executeBlobQuery

public static String executeBlobQuery(IDbmsSupport dbmsSupport,
                                      Connection connection,
                                      String query)
                               throws JdbcException

Throws:

JdbcException

isQueryResultEmpty

public static boolean isQueryResultEmpty(Connection connection,
                                         String query)
                                  throws JdbcException

Throws:

JdbcException

executeStatement

public static void executeStatement(Connection connection,
                                    String query,
                                    Object... params)
                             throws JdbcException

Executes the given SQL statement. The statement can be any SQL statement that does not return a result set. Each object in the array is mapped to its most appropriate JDBC type, however not all types are supported. Column types are not considered, only the class of each parameter.

Supported Java types and JDBC Type mapping:

Integer	Types.INTEGER
Long	Types.BIGINT
Float	Types.NUMERIC
Double	Types.NUMERIC
Timestamp	Types.TIMESTAMP
Time	Types.TIME
Date	Types.DATE
String	Types.VARCHAR

Parameters:

connection - The JDBC Connection on which to execute the statement.

query - The SQL statement, as a string.

params - The statement parameters, see above.

Throws:

JdbcException - if there is an error in statement execution or parameter mapping.

selectAllFromTable

public static String selectAllFromTable(IDbmsSupport dbmsSupport,
                                        Connection conn,
                                        String tableName,
                                        String orderBy)
                                 throws SQLException

Throws:

SQLException

executeStatement

public static void executeStatement(IDbmsSupport dbmsSupport,
                                    Connection connection,
                                    String query,
                                    ParameterValueList parameterValues,
                                    PipeLineSession session)
                             throws JdbcException

Throws:

JdbcException

executeQuery

public static Object executeQuery(IDbmsSupport dbmsSupport,
                                  Connection connection,
                                  String query,
                                  ParameterValueList parameterValues,
                                  PipeLineSession session)
                           throws JdbcException

Throws:

JdbcException

applyParameters

public static void applyParameters(IDbmsSupport dbmsSupport,
                                   PreparedStatement statement,
                                   ParameterList parameters,
                                   Message message,
                                   PipeLineSession session)
                            throws SQLException,
                                   JdbcException,
                                   ParameterException

Throws:

SQLException

JdbcException

ParameterException

applyParameters

public static void applyParameters(IDbmsSupport dbmsSupport,
                                   PreparedStatement statement,
                                   ParameterValueList parameters,
                                   PipeLineSession session)
                            throws SQLException,
                                   JdbcException

Throws:

SQLException

JdbcException

mapParameterTypeToSqlType

public static SQLType mapParameterTypeToSqlType(Parameter.ParameterType parameterType)

setParameter

public static void setParameter(PreparedStatement statement,
                                int parameterIndex,
                                String value,
                                boolean parameterTypeMatchRequired)
                         throws SQLException

Throws:

SQLException

isNumeric

public static boolean isNumeric(int sqlTYpe)