Package org.frankframework.jdbc

Class JdbcQuerySenderBase<H>

java.lang.Object
org.frankframework.jndi.JndiBase
org.frankframework.jdbc.JdbcFacade
org.frankframework.jdbc.JdbcSenderBase<H>
org.frankframework.jdbc.JdbcQuerySenderBase<H>
All Implemented Interfaces:
AdapterAware, HasPhysicalDestination, IBlockEnabledSender<H>, IConfigurable, IConfigurationAware, INamedObject, IScopeProvider, ISender, ISenderWithParameters, IWithParameters, IXAEnabled, HasStatistics, org.springframework.beans.factory.Aware, org.springframework.context.ApplicationContextAware
Direct Known Subclasses:
DirectQuerySender, FixedQuerySender
public abstract class JdbcQuerySenderBase<H> extends JdbcSenderBase<H>
This executes the query that is obtained from the (here still abstract) method getStatement. Descendent classes can override getStatement to provide meaningful statements. If used with parameters, the values of the parameters will be applied to the statement. Each occurrence of a questionmark ('?') will be replaced by a parameter value. Parameters are applied in order: The n-th questionmark is replaced by the value of the n-th parameter.

Note on using packages

The package processor makes some assumptions about the data types:
  • elements that start with a single quote are assumed to be Strings
  • elements that contain a dash ('-') are assumed to be dates (yyyy-MM-dd) or timestamps (yyyy-MM-dd HH:mm:ss)
  • elements containing a dot ('.') are assumed to be floats
  • all other elements are assumed to be integers

Queries that return no data (queryType 'other') return a message indicating the number of rows processed
Since:
4.1
Author:
Gerrit van Brakel
Parameters
all parameters present are applied to the statement to be executed

  • Field Details

  • Constructor Details

    • JdbcQuerySenderBase

      public JdbcQuerySenderBase()

  • Method Details

    • configure

      public void configure() throws ConfigurationException
      Description copied from interface: ISender
      configure() is called once at startup of the framework in the configure method of the owner of this sender. Purpose of this method is to check whether the static configuration of the sender is correct. As much as possible class-instantiating should take place in the configure() or open() method, to improve performance.
      Specified by:
      configure in interface IConfigurable
      Specified by:
      configure in interface ISender
      Overrides:
      configure in class JdbcSenderBase<H>
      Throws:
      ConfigurationException

    • getQuery

      protected abstract String getQuery(Message message) throws SenderException
      Obtain a query to be executed. Method-stub to be overridden in descender-classes.
      Throws:
      SenderException

    • open

      public void open() throws SenderException
      Description copied from interface: ISender
      This method will be called to start the sender. After this method is called the sendMessage method may be called. Purpose of this method is to reduce creating connections to databases etc. in the sendMessage() method.
      Specified by:
      open in interface ISender
      Overrides:
      open in class JdbcSenderBase<H>
      Throws:
      SenderException

    • convertQuery

      @Nonnull protected String convertQuery(@Nonnull String query) throws SQLException, DbmsException
      Throws:
      SQLException
      DbmsException

    • getStatement

      protected final PreparedStatement getStatement(@Nonnull Connection con, @Nonnull String query, @Nullable JdbcQuerySenderBase.QueryType queryType) throws JdbcException, SQLException
      Throws:
      JdbcException
      SQLException

    • prepareQuery

      protected PreparedStatement prepareQuery(@Nonnull Connection con, @Nonnull String query, @Nullable JdbcQuerySenderBase.QueryType queryType) throws SQLException, JdbcException
      Throws:
      SQLException
      JdbcException

    • prepareQueryWithResultSet

      protected PreparedStatement prepareQueryWithResultSet(Connection con, String query, int resultSetConcurrency) throws SQLException
      Throws:
      SQLException

    • prepareQueryWithColumnsReturned

      protected PreparedStatement prepareQueryWithColumnsReturned(Connection con, String query, String[] columnsReturned) throws SQLException
      Throws:
      SQLException

    • getCallWithRowIdReturned

      protected CallableStatement getCallWithRowIdReturned(Connection con, String query) throws SQLException
      Throws:
      SQLException

    • getReturnedColumns

      protected ResultSet getReturnedColumns(PreparedStatement st) throws SQLException
      Throws:
      SQLException

    • getQueryExecutionContext

      public QueryExecutionContext getQueryExecutionContext(Connection connection, Message message) throws SenderException, SQLException, JdbcException
      Throws:
      SenderException
      SQLException
      JdbcException

    • getConnectionForSendMessage

      protected Connection getConnectionForSendMessage() throws JdbcException, TimeoutException
      Throws:
      JdbcException
      TimeoutException

    • closeConnectionForSendMessage

      protected void closeConnectionForSendMessage(Connection connection, PipeLineSession session)

    • closeStatementSet

      protected void closeStatementSet(QueryExecutionContext queryExecutionContext)

    • executeStatementSet

      protected PipeRunResult executeStatementSet(@Nonnull QueryExecutionContext queryExecutionContext, @Nonnull Message message, @Nonnull PipeLineSession session, @Nullable IForwardTarget next) throws SenderException, TimeoutException
      Throws:
      SenderException
      TimeoutException

    • adjustQueryAndParameterListForNamedParameters

      protected String adjustQueryAndParameterListForNamedParameters(ParameterList parameterList, String query)

    • getResult

      protected Message getResult(ResultSet resultset) throws JdbcException, SQLException, IOException, javax.jms.JMSException
      Throws:
      JdbcException
      SQLException
      IOException
      javax.jms.JMSException

    • getResult

      protected Message getResult(ResultSet resultset, Object blobSessionVar, Object clobSessionVar) throws JdbcException, SQLException, IOException
      Throws:
      JdbcException
      SQLException
      IOException

    • getResult

      protected PipeRunResult getResult(ResultSet resultset, Object blobSessionVar, Object clobSessionVar, javax.servlet.http.HttpServletResponse response, String contentType, String contentDisposition, PipeLineSession session, IForwardTarget next) throws JdbcException, SQLException, IOException
      Throws:
      JdbcException
      SQLException
      IOException

    • buildDb2XMLWriter

      protected DB2XMLWriter buildDb2XMLWriter()

    • executeUpdateBlobQuery

      protected Message executeUpdateBlobQuery(PreparedStatement statement, Message contents) throws SenderException
      Throws:
      SenderException

    • executeUpdateClobQuery

      protected Message executeUpdateClobQuery(PreparedStatement statement, Message contents) throws SenderException
      Throws:
      SenderException

    • executeSelectQuery

      protected PipeRunResult executeSelectQuery(PreparedStatement statement, Object blobSessionVar, Object clobSessionVar, PipeLineSession session, IForwardTarget next) throws SenderException
      Throws:
      SenderException

    • executeSelectQuery

      protected PipeRunResult executeSelectQuery(PreparedStatement statement, Object blobSessionVar, Object clobSessionVar, javax.servlet.http.HttpServletResponse response, String contentType, String contentDisposition, PipeLineSession session, IForwardTarget next) throws SenderException
      Throws:
      SenderException

    • executePackageQuery

      protected Message executePackageQuery(QueryExecutionContext queryExecutionContext) throws SenderException, JdbcException, IOException, javax.jms.JMSException
      Throws:
      SenderException
      JdbcException
      IOException
      javax.jms.JMSException

    • executeOtherQuery

      protected Message executeOtherQuery(QueryExecutionContext queryExecutionContext, Message message, PipeLineSession session) throws SenderException
      Throws:
      SenderException

    • executeOtherQuery

      protected Message executeOtherQuery(Connection connection, PreparedStatement statement, String query, String resultQuery, PreparedStatement resStmt, Message message, PipeLineSession session, ParameterList parameterList) throws SenderException
      Throws:
      SenderException

    • getUpdateStatementResult

      protected Message getUpdateStatementResult(PreparedStatement statement, String resultQuery, PreparedStatement resStmt, int numRowsAffected) throws SQLException, JdbcException, IOException, javax.jms.JMSException
      Throws:
      SQLException
      JdbcException
      IOException
      javax.jms.JMSException

    • fillParamArray

      protected String fillParamArray(Object[] paramArray, String message) throws SenderException
      Throws:
      SenderException

    • setPackageContent

      public void setPackageContent(String packageContent)
      Controls wheter the returned package content is db2 format or xml format. Possible values:
      • select:
        • xml content s expected
      • anything else:
        • db2 content is expected

    • setQueryType

      public void setQueryType(JdbcQuerySenderBase.QueryType queryType)
      Type of query to be executed
      Default value
      OTHER

    • setScalar

      public void setScalar(boolean b)
      When true, the value of the first column of the first row (or the startrow) is returned as the only result, as a simple non-xml value
      Default value
      false

    • setScalarExtended

      public void setScalarExtended(boolean b)
      When true and scalar is also true, but returns no value, one of the following is returned:
      • '[absent]' no row is found
      • '[null]' a row is found, but the value is a SQL-NULL
      • '[empty]' a row is found, but the value is a empty string
      Default value
      false

    • setMaxRows

      public void setMaxRows(int i)
      The maximum number of rows to be returned from the output of select queries, -1 means unlimited rows
      Default value
      -1

    • setStartRow

      public void setStartRow(int i)
      The number of the first row to be returned from the output of select queries. Rows before this are skipped from the output.
      Default value
      1

    • setNullValue

      public void setNullValue(String string)
      Value used in result as contents of fields that contain no value (sql-null)
      Default value
      empty string

    • setResultQuery

      public void setResultQuery(String string)
      Query that can be used to obtain result of side-effect of update-query, like generated value of sequence. Example: SELECT mysequence.currval FROM dual

    • setColumnsReturned

      public void setColumnsReturned(String string)
      Comma separated list of columns whose values are to be returned. Works only if the driver implements jdbc 3.0 getGeneratedKeys(). Note: not all drivers support multiple values and returned field names may vary between drivers. Works for H2 and Oracle. Could work for MS_SQL with a single identity column, with name GENERATED_KEYS, if a identity has been generated. Not supported for other DBMSes.

    • setUseNamedParams

      public void setUseNamedParams(Boolean b)
      Named parameters will be auto-detected by default. Every string in the query which equals "?{"paramname"}" will be replaced by the value of the corresponding parameter. The parameters don't need to be in the correct order and unused parameters are skipped.
      Default value
      null

    • setIncludeFieldDefinition

      public void setIncludeFieldDefinition(boolean b)
      when true, the result contains besides the returned rows also includes a header with information about the fetched fields
      Default value
      application default (true)

    • setTrimSpaces

      public void setTrimSpaces(boolean b)
      Remove trailing blanks from all result values.
      Default value
      true

    • setRowIdSessionKey

      public void setRowIdSessionKey(String string)
      If specified, the rowid of the processed row is put in the pipelinesession under the specified key (only applicable for querytype=other). Note: If multiple rows are processed a SqlException is thrown.

    • setStreamResultToServlet

      @Deprecated public void setStreamResultToServlet(boolean b)
      Deprecated.
      If set, the result is streamed to the HttpServletResponse object of the RestServiceDispatcher (instead of passed as bytes or as a String)
      Default value
      false

    • setSqlDialect

      public void setSqlDialect(String string)
      If set, the SQL dialect in which the queries are written and should be translated from to the actual SQL dialect

    • setLockRows

      public void setLockRows(boolean b)
      When set true, exclusive row-level locks are obtained on all the rows identified by the select statement (e.g. by appending ' FOR UPDATE NOWAIT SKIP LOCKED' to the end of the query)
      Default value
      false

    • setLockWait

      public void setLockWait(int i)
      when set and >=0, ' FOR UPDATE WAIT #' is used instead of ' FOR UPDATE NOWAIT SKIP LOCKED'
      Default value
      -1

    • setSynchronous

      public void setSynchronous(boolean synchronous)
      When true, the result of sendMessage is the reply of the request.
      Default value
      true

    • setBlobColumn

      public void setBlobColumn(int i)
      Only for querytype 'updateBlob': column that contains the BLOB to be updated
      Default value
      1

    • setBlobSessionKey

      public void setBlobSessionKey(String string)
      For querytype 'updateBlob': key of session variable that contains the data (String or InputStream) to be loaded to the BLOB. When empty, the input of the pipe, which then must be a String, is used. For querytype 'select': key of session variable that contains the OutputStream, Writer or Filename to write the BLOB to

    • setBlobsCompressed

      public void setBlobsCompressed(boolean b)
      controls whether blobdata is stored compressed in the database
      Default value
      true

    • setBlobBase64Direction

      public void setBlobBase64Direction(Base64Pipe.Direction value)
      controls whether the streamed blobdata will need to be base64 encode or decode or not.

    • setBlobCharset

      public void setBlobCharset(String string)
      Charset that is used to read and write BLOBs. This assumes the blob contains character data. If blobCharset and blobSmartGet are not set, BLOBs are returned as bytes. Before version 7.6, blobs were base64 encoded after being read to accommodate for the fact that senders need to return a String. This is no longer the case

    • setBlobSmartGet

      public void setBlobSmartGet(boolean b)
      Controls automatically whether blobdata is stored compressed and/or serialized in the database
      Default value
      false

    • setClobColumn

      public void setClobColumn(int i)
      Only for querytype 'updateClob': column that contains the CLOB to be updated
      Default value
      1

    • setClobSessionKey

      public void setClobSessionKey(String string)
      For querytype 'updateClob': key of session variable that contains the CLOB (String or InputStream) to be loaded to the CLOB. When empty, the input of the pipe, which then must be a String, is used. For querytype 'select': key of session variable that contains the OutputStream, Writer or Filename to write the CLOB to

    • setCloseInputstreamOnExit

      @Deprecated public void setCloseInputstreamOnExit(boolean b)
      Deprecated.
      When set to false, the Inputstream is not closed after it has been used to update a BLOB or CLOB
      Default value
      true

    • setCloseOutputstreamOnExit

      public void setCloseOutputstreamOnExit(boolean b)
      When set to false, the Outputstream is not closed after BLOB or CLOB has been written to it
      Default value
      true

    • setStreamCharset

      public void setStreamCharset(String string)
      Charset used when reading a stream (that is e.g. going to be written to a BLOB or CLOB). When empty, the stream is copied directly to the BLOB, without conversion

    • setAvoidLocking

      public void setAvoidLocking(boolean avoidLocking)
      If true, then select queries are executed in a way that avoids taking locks, e.g. with isolation mode 'read committed' instead of 'repeatable read'.
      Default value
      false

    • setPrettyPrint

      public void setPrettyPrint(boolean prettyPrint)
      If true and scalar=false, multiline indented XML is produced
      Default value
      false

    • setOutputFormat

      public void setOutputFormat(DocumentFormat outputFormat)
      The type of output. If not set then defaults to old-style XML. If set to XML, new-style XML is used. EXPERIMENTAL: datatypes like numbers are not yet rendered correctly
      Default value
      false

    • getBatchSize

      public int getBatchSize()

    • getQueryType

      public JdbcQuerySenderBase.QueryType getQueryType()

    • getMaxRows

      public int getMaxRows()

    • getStartRow

      public int getStartRow()

    • isScalar

      public boolean isScalar()

    • isScalarExtended

      public boolean isScalarExtended()

    • isSynchronous

      public boolean isSynchronous()
      Description copied from interface: ISender
      When true, the result of sendMessage is the reply of the request.

    • getBlobColumn

      public int getBlobColumn()

    • getClobColumn

      public int getClobColumn()

    • getBlobSessionKey

      public String getBlobSessionKey()

    • getClobSessionKey

      public String getClobSessionKey()

    • getNullValue

      public String getNullValue()

    • getColumnsReturned

      public String getColumnsReturned()

    • getResultQuery

      public String getResultQuery()

    • isTrimSpaces

      public boolean isTrimSpaces()

    • getBlobCharset

      public String getBlobCharset()

    • isCloseInputstreamOnExit

      public boolean isCloseInputstreamOnExit()

    • isCloseOutputstreamOnExit

      public boolean isCloseOutputstreamOnExit()

    • getBlobBase64Direction

      public Base64Pipe.Direction getBlobBase64Direction()

    • getStreamCharset

      public String getStreamCharset()

    • isBlobsCompressed

      public boolean isBlobsCompressed()

    • isBlobSmartGet

      public boolean isBlobSmartGet()

    • getUseNamedParams

      public Boolean getUseNamedParams()

    • isIncludeFieldDefinition

      public boolean isIncludeFieldDefinition()

    • getRowIdSessionKey

      public String getRowIdSessionKey()

    • getPackageContent

      public String getPackageContent()

    • getColumnsReturnedList

      public String[] getColumnsReturnedList()

    • isStreamResultToServlet

      public boolean isStreamResultToServlet()

    • getSqlDialect

      public String getSqlDialect()

    • isLockRows

      public boolean isLockRows()

    • getLockWait

      public int getLockWait()

    • isAvoidLocking

      public boolean isAvoidLocking()

    • getOutputFormat

      public DocumentFormat getOutputFormat()

    • isPrettyPrint

      public boolean isPrettyPrint()