Skip to content

Call an Oracle Function from Spring

June 29th, 2017 - SoftwareTutorial

The easiest way to call a function from Spring is using SELECT. For example, having a function HAS_PANEL_FLAG_OK with two parameters and returning an integer, the function call looks like:

SELECT  panel_id,
  HAS_PANEL_FLAG_OK('17584', panel_id) AS flag
  FROM panels
  WHERE panel_id LIKE '%30'
  ;

This will return all flags for the panels matching the condition.

Java Call

The query string can be something like:

static final String STATEMENT = "select  panel_id, " +
  "HAS_PANEL_FLAG_OK('17584', panel_id) AS flag " +
  "FROM panels" +
  "WHERE panel_id LIKE ?"

and the actual query will be:

jdbcOperations.queryForInt(SELECT_PANEL_IS_LOCKED, new Object[] {
    "%30"
});

The problem I’ve encountered is the ability to specify an argument which is not part of the query. Something like this:

static final String STATEMENT = "select  panel_id, " +
  "HAS_PANEL_FLAG_OK(?, panel_id) AS flag " + // the first function parameter is parametereised
  "FROM panels" +
  "WHERE panel_id LIKE ?"

jdbcOperations.queryForInt(SELECT_PANEL_IS_LOCKED, new Object[] {
    "17584",
    "%30"
});

I’ll investigate this later…

HTH,

Share on
Reddit
Linked in
Whatsapp

A little experiment: