Placeholder in prepared statement

Question

I am using the below query in a prepared statement. Earlier I was using in procedure and using callable but now I am trying to use select query in jdbc prepared statement.

I know in preparestatement we write insert into abc values(?,?,?);

but here I have insert-select. same variable has been used many places. in this query I have 2 variable p_entity_type and p_update_mode

INSERT INTO dynamicEntitynotgett
    (entity_type, entity_id, entity_code, synonyms, action)
    WITH data_view AS
     ( -- ITEM table
      SELECT 'ITEM' entity_type, -- This separates inserted values
              item_id data_id,
              item_name data_name,
              item_desc data_desc,
              creation_date
        FROM itemde
      UNION ALL
      -- ORG table
      SELECT 'ORG' entity_type, -- This separates inserted values
              org_id,
              org_name,
              org_desc,
              creation_date
        FROM orgde
        UNION ALL
      -- Feature table
              SELECT 'FEATURES' entity_type, -- This separates inserted values
              FEATURE_id data_id,
              FEATURE_NAME data_name,
              FEATURE_DESC data_desc,
              CREATION_DATE
        FROM FEATURESDE
      )
    SELECT upper(t.entity_type),
           t.data_id,
           t.data_name,
           t.data_desc,
           CASE lower(p_update_mode)
             WHEN 'INCREMENTAL' THEN
               CASE
                 WHEN t.creation_date > b.last_update_date THEN
                   'update'
                 WHEN t.creation_date < b.last_update_date THEN
                   'add'
               END
             WHEN 'full' THEN
              'add' 
           END action
      FROM data_view t
           LEFT JOIN ODA_REFRESH_DETAILS b
                  ON b.entity_type = t.entity_type
                 AND lower(p_update_mode )='incremental'
     WHERE (upper(p_entity_type) = t.entity_type OR p_entity_type IS NULL)
       AND (lower(p_update_mode) = 'full'
            OR (lower(p_update_mode) = 'incremental' AND b.entity_type IS NOT NULL)
           );

I will receive p_entity_type and p_update_mode from upper stream. which solution would be better? Resultset or Preparedstatement and how can I replace those values in query or use setXXX().

score 1 · Accepted Answer · answered Aug 22 '20 at 11:00

1

I think you are looking for namedParameterStatement. This would allow you to name the parameters.

I'm not exactly sure what you are referring to in your statement, but for instance, this line:

SELECT 'ITEM' entity_type

could be replaced with:

SELECT :ITEM as entity_type

where :ITEM is passed in just like a ?, but could be used multiple times in the statement.

answered Aug 22 '20 at 11:00

Gordon Linoff

1,242,037
58
646
786

Thank you @Gordon . it is actually spring class. Do we have something like in vanilla java? I mean I am not using spring framework. – Aug 22 '20 at 11:49
1

@Shruti . . . It should be available directly -- named parameters are a very typical way of writing SQL. – Gordon Linoff Aug 22 '20 at 12:26
Hi Gordon, Could you please help me on the same, please? https://stackoverflow.com/questions/63541003/creating-json-from-oracle-database-table – Aug 22 '20 at 21:58
sir I am facing similar issues. this query works properly in procedure in pl/sql https://stackoverflow.com/questions/63553154/placeholder-in-java-jdbc-using-preparestatement?noredirect=1#comment112380978_63553154 – Aug 24 '20 at 00:41

Placeholder in prepared statement

1 Answers1