Tag Archives: SQL

how to pass LIKE parameters (values) in NamedParameterJdbcTemplate in Spring

Imagine we have a query

SELECT * FROM ARTICLE WHERE TITLE LIKE ‘%spring%’

As we are using NamedParameterJdbcTemplate, I’d like to pass title as a parameter named title holding the value.

So the query would now be SELECT * FROM ARTICLE WHERE TITLE LIKE ‘%:title%’

Solution:
We have to frame the query as like below in Spring JDBC Code

String final GET_QUERY = "SELECT * FROM ARTICLE WHERE TITLE LIKE '%'||:title||'%'";

So, in our spring code

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("title", title);

NamedParameterJdbcTemplate npjt = new NamedParameterJdbcTemplate(
getJdbcTemplate().getDataSource());
list = npjt.query(
GET_QUERY, parameters,
new RowMapper() {....});

Advertisements

how to insert double quotes in oracle sql queries?

Recently I came across a requirement to insert a word with double-quotes as values to a column from my Oracle Database table using SQL.

SQL> insert into DEFINITION_TABLE values(6,'Amount of Money Written off','The purpose of
this object is to calculate the amount of money classified as '“uncollectable”' by the bank. It should
ideally be as low as possible, because it directly impacts the financial health of the bank in a negative manner.','Amount of Money Writt
en off= Total amount of money classified as '“uncollectable”'','#','Null','Null','Actual Amount of Money Written off vs Predicted Amount of Money Written off,Amount of Money Written off by Product Type,Amount of Money Written off by Product Type on a Monthly Basis,Amount of Money Written off by Region,Amount of Money Written off by R
egion on a Monthly Basis','Quantitative, Lagging','Credit Cards','Minimization of losses',1);

insert into DEFINITION_TABLE values(6,'Amount of Money Written off','The purpose of this

ERROR at line 1:
ORA-00911: invalid character

So now how to do that????

SQL> insert into GLOSRY_KPI_DEFINITION_TABLE values(6,'Amount of Money Written off','[The purpose of
this KPI is to calculate the amount of money classified as "uncollectable" by the bank. It should ideally be as low as possible, because it directly impacts the financial health of the bank in a negative manner.]','[Amount of Money Written off= Total amount of money classified as "uncollectable"]','#','Null','Null','Actual Amount of
Money Written off vs Predicted Amount of Money Written off,Amount of Money Written off by Product Type,Amount of Money Written off by Product Type on a Monthly Basis,Amount of Money Written off by Region,Amount of Money Written off by Region on a Monthly Basis','Quantitative, Lagging','Credit Cards','Minimization of losses',1);

1 row created.

Use SQUARE BRACKETS [] to wrap the whole column value

insert into TABLE values (‘normal value’, ‘[this column value contains “double” quotes]’, ‘normal value’);

Happy Learning and Sharing 🙂