Java使用oracle函数pivot进行查询的准备语句

Question

在使用预处理语句时，如何为以下查询传递参数来代替abcd ?

select * from table (PIVOT ( ' select A, B, SUM(C) from TABLE_XYZ where A =' 'abcd' ' GROUP BY A, B ORDER BY A ASC ' )) order by A;

Answer 1

您可以使用String.format(<format of your string with format specifiers like %s or %d and etc >,< parameters >)。在您查询的情况下;可以这样写:

String query = String.format('select * from table (PIVOT ('select A, B, SUM(C) from TABLE_XYZ where A =' %s ' GROUP BY A, B ORDER BY A ASC')) order by A;',  'abcd');

因此，您的查询将像这样(abcd不带引号):

select * from table ('select A, B, SUM(C) from TABLE_XYZ where A =' abcd ' GROUP BY A, B ORDER BY A ASC')) ORDER BY A;

如果想要加引号，写如下代码:

String query = String.format('select * from table (PIVOT ('select A, B, SUM(C) from TABLE_XYZ where A =' %s ' GROUP BY A, B ORDER BY A ASC')) order by A;',  ''abcd'');