JDBC and Oracle DB : Escape ' (single quote)

Question

Hello this looks like simple but I am having issues here.

Firstly I am using Statement#executeBatch for executing many UPDATE statements. Each update statements have String value to be updated. These String have " ' " single quote. I have tried adding one more single quote in front of it as per Oracle doc as well adding '\\\' in front of single quote. With the first one, my query gets stuck and does not come out even after 10 minutes. With second one I get 'batching: ORA-00927: missing equal sign' error.

What is the correct approach? Note:- I cannot use PreparedStatement to make use of JDBC parameters.

Please help.

Answer 1

You may use the q-quoted string eg q'['''''''']'

This give a following example

Statement stmt = con.createStatement();

stmt.addBatch("update tst set txt = q'['''''''']' where id = 1");
stmt.addBatch("update tst set txt = q'['''''''']' where id = 2");
stmt.addBatch("update tst set txt = q'['''''''']' where id = 3");
stmt.addBatch("update tst set txt = q'['''''''']' where id = 4");
stmt.addBatch("update tst set txt = q'['''''''']' where id = 5"); 
// submit a batch of update commands for execution
int[] updateCounts = stmt.executeBatch();

But the correct way is to use the prepared statement

PreparedStatement stmt = con.prepareStatement("update tst set txt = ? where id = ?");
5.times { i ->
  stmt.setString(1, "''''''''");
  stmt.setInt(2, i+1);
  stmt.addBatch();
}

// submit a batch of update commands for execution
int[] updateCounts = stmt.executeBatch();