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 and Description
protected static org.apache.logging.log4j.Logger	log

Constructor Summary

Constructors

Constructor and Description
JdbcUtil()

Method Summary

Modifier and Type	Method and Description
static void	applyParameter(PreparedStatement statement, ParameterValue pv, int parameterIndex, boolean parameterTypeMatchRequired)
static void	applyParameters(IDbmsSupport dbmsSupport, PreparedStatement statement, ParameterList parameters, Message message, PipeLineSession session)
static void	applyParameters(IDbmsSupport dbmsSupport, PreparedStatement statement, ParameterValueList parameters)
static void	close(Connection connection)
static String	executeBlobQuery(IDbmsSupport dbmsSupport, Connection connection, String query)
static int	executeIntQuery(Connection connection, String query)
static int	executeIntQuery(Connection connection, String query, int param)
static int	executeIntQuery(Connection connection, String query, int param1, int param2)
static int	executeIntQuery(Connection connection, String query, int param1, int param2, String param3)
static int	executeIntQuery(Connection connection, String query, int param1, int param2, String param3, String param4)
static int	executeIntQuery(Connection connection, String query, int param1, String param2)
static int	executeIntQuery(Connection connection, String query, int param1, String param2, String param3)
static int	executeIntQuery(Connection connection, String query, String param)
static int	executeIntQuery(Connection connection, String query, String param1, String param2) executes query that returns an integer.
static List<List<Object>>	executeObjectListListQuery(Connection connection, String query, int columnsCount)
static Object	executeQuery(IDbmsSupport dbmsSupport, Connection connection, String query, ParameterValueList parameterValues)
static void	executeStatement(Connection connection, String query)
static void	executeStatement(Connection connection, String query, int param)
static void	executeStatement(Connection connection, String query, int param1, int param2, int param3, String param4, String param5)
static void	executeStatement(Connection connection, String query, int param1, int param2, String param3, String param4)
static void	executeStatement(Connection connection, String query, int param1, String param2)
static void	executeStatement(Connection connection, String query, int param1, String param2, String param3)
static void	executeStatement(Connection connection, String query, String param)
static void	executeStatement(Connection connection, String query, String param1, String param2)
static void	executeStatement(IDbmsSupport dbmsSupport, Connection connection, String query, ParameterValueList parameterValues)
static String	executeStringQuery(Connection connection, String query) executes query that returns a string.
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(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 String	getBlobAsString(InputStream blobIntputStream, String column, String charset, 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(IDbmsSupport dbmsSupport, ResultSet rs, int column, String charset, boolean blobIsCompressed)
static Reader	getBlobReader(IDbmsSupport dbmsSupport, ResultSet rs, String column, String charset, boolean blobIsCompressed)
static Reader	getBlobReader(InputStream blobIntputStream, String charset)
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 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)
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(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	streamBlob(InputStream blobIntputStream, String charset, 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, SaxElementBuilder parent)
static void	warningsToXml(SQLWarning warnings, XmlBuilder parent) Deprecated.
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

Throws:

SQLException

JdbcException

getBlobInputStream

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

Throws:

SQLException

JdbcException

getBlobReader

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

Throws:

IOException

JdbcException

SQLException

getBlobReader

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

Throws:

IOException

JdbcException

SQLException

getBlobReader

public static Reader getBlobReader(InputStream blobIntputStream,
                                   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

Throws:

JdbcException

SQLException

IOException

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

Throws:

JdbcException

SQLException

IOException

streamBlob

public static void streamBlob(InputStream blobIntputStream,
                              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 JdbcException,
                              SQLException,
                              IOException

Throws:

JdbcException

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

Throws:

IOException

JdbcException

SQLException

getBlobAsString

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

Throws:

IOException

JdbcException

SQLException

getBlobAsString

public static String getBlobAsString(InputStream blobIntputStream,
                                     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 JdbcException,
                                               SQLException

Throws:

JdbcException

SQLException

getBlobOutputStream

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

Throws:

JdbcException

SQLException

getBlobWriter

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

Throws:

IOException

JdbcException

SQLException

putStringAsBlob

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

Throws:

IOException

JdbcException

SQLException

putByteArrayAsBlob

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

Throws:

IOException

JdbcException

SQLException

getClobAsString

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

Throws:

IOException

JdbcException

SQLException

getClobAsString

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

Throws:

IOException

JdbcException

SQLException

putStringAsClob

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

Throws:

IOException

JdbcException

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)

executeStringQuery

public static String executeStringQuery(Connection connection,
                                        String query)
                                 throws JdbcException

executes query that returns a string. Returns null if no results are found.

Throws:

JdbcException

executeBlobQuery

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

Throws:

JdbcException

executeIntQuery

public static int executeIntQuery(Connection connection,
                                  String query)
                           throws JdbcException

Throws:

JdbcException

executeIntQuery

public static int executeIntQuery(Connection connection,
                                  String query,
                                  String param)
                           throws JdbcException

Throws:

JdbcException

executeIntQuery

public static int executeIntQuery(Connection connection,
                                  String query,
                                  String param1,
                                  String param2)
                           throws JdbcException

executes query that returns an integer. Returns -1 if no results are found.

Throws:

JdbcException

executeIntQuery

public static int executeIntQuery(Connection connection,
                                  String query,
                                  int param)
                           throws JdbcException

Throws:

JdbcException

executeIntQuery

public static int executeIntQuery(Connection connection,
                                  String query,
                                  int param1,
                                  String param2)
                           throws JdbcException

Throws:

JdbcException

executeIntQuery

public static int executeIntQuery(Connection connection,
                                  String query,
                                  int param1,
                                  String param2,
                                  String param3)
                           throws JdbcException

Throws:

JdbcException

executeIntQuery

public static int executeIntQuery(Connection connection,
                                  String query,
                                  int param1,
                                  int param2)
                           throws JdbcException

Throws:

JdbcException

executeIntQuery

public static int executeIntQuery(Connection connection,
                                  String query,
                                  int param1,
                                  int param2,
                                  String param3)
                           throws JdbcException

Throws:

JdbcException

executeIntQuery

public static int executeIntQuery(Connection connection,
                                  String query,
                                  int param1,
                                  int param2,
                                  String param3,
                                  String param4)
                           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)
                             throws JdbcException

Throws:

JdbcException

executeStatement

public static void executeStatement(Connection connection,
                                    String query,
                                    String param)
                             throws JdbcException

Throws:

JdbcException

executeStatement

public static void executeStatement(Connection connection,
                                    String query,
                                    String param1,
                                    String param2)
                             throws JdbcException

Throws:

JdbcException

executeStatement

public static void executeStatement(Connection connection,
                                    String query,
                                    int param)
                             throws JdbcException

Throws:

JdbcException

executeStatement

public static void executeStatement(Connection connection,
                                    String query,
                                    int param1,
                                    String param2)
                             throws JdbcException

Throws:

JdbcException

executeStatement

public static void executeStatement(Connection connection,
                                    String query,
                                    int param1,
                                    String param2,
                                    String param3)
                             throws JdbcException

Throws:

JdbcException

executeStatement

public static void executeStatement(Connection connection,
                                    String query,
                                    int param1,
                                    int param2,
                                    String param3,
                                    String param4)
                             throws JdbcException

Throws:

JdbcException

executeStatement

public static void executeStatement(Connection connection,
                                    String query,
                                    int param1,
                                    int param2,
                                    int param3,
                                    String param4,
                                    String param5)
                             throws JdbcException

Throws:

JdbcException

selectAllFromTable

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

Throws:

SQLException

selectAllFromTable

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

Throws:

SQLException

executeObjectListListQuery

public static List<List<Object>> executeObjectListListQuery(Connection connection,
                                                            String query,
                                                            int columnsCount)
                                                     throws JdbcException

Throws:

JdbcException

executeStatement

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

Throws:

JdbcException

executeQuery

public static Object executeQuery(IDbmsSupport dbmsSupport,
                                  Connection connection,
                                  String query,
                                  ParameterValueList parameterValues)
                           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)
                            throws SQLException,
                                   JdbcException

Throws:

SQLException

JdbcException

applyParameter

public static void applyParameter(PreparedStatement statement,
                                  ParameterValue pv,
                                  int parameterIndex,
                                  boolean parameterTypeMatchRequired)
                           throws SQLException,
                                  JdbcException

Throws:

SQLException

JdbcException

setParameter

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

Throws:

SQLException

isNumeric

public static boolean isNumeric(int sqlTYpe)