How to pass a string value of java to sql query

Question

I am trying to create this sql query in Java Netbeans:

SELECT * FROM DBNAME WHERE NAME LIKE '%SOMETHING%'

I am using Prepared Statement like this:

PreparedStatement ps=con.prepareStatement("SELECT * FROM demo WHERE NAME LIKE ? ");
ps.setString(1, "%"+something+"%");
...

The problem is that "setString" doesnt work at all. I discovered that the value of "'" is actually "\'" so the query becomes

SELECT * FROM DBNAME WHERE NAME LIKE \'%SOMETHING%\'

Possible duplicate of [Using "like" wildcard in prepared statement](http://stackoverflow.com/questions/8247970/using-like-wildcard-in-prepared-statement) — Gurwinder Singh, Apr 06 '17 at 04:02
Possibly your Solution is here: [passing java string variable in mysql query](http://stackoverflow.com/questions/24644882/passing-java-string-variable-in-mysql-query) — Ninja, Apr 06 '17 at 06:19

A_Wen · Answer 1 · 2017-04-06T06:06:02.593

try this:

String sql = "select * from tablename like ?";
String sqlParasValue = "%" + "2015-03-23" + "%";
pStmt =conn.prepareStatement(sql);
pStmt.setString(1, sqlParasValue);//Automatically add single quotes
pStmt.execute();

I try this on my code and it is work. I uesd Spring boot + mybatis + postgresql

SQL Table & some data:

name   h    w    f
xi  182  53  li
1xi 181  52  [NULL]
li  180  51  xh
zs  165  50  li
xh  190  52  [NULL]

sql query:

select name from stu where name like '%x%'

sql query result:

name    h   w   f
xi  182 53  li
1xi 181 52  [NULL]
xh  190 52  [NULL]

java code

@Select("select name  from stu where name like #{name}")
List<String> test(@Param("name") String name);
List<String> s = serviceImpl.test("%x%");
for(String ss : s){
    System.out.println(ss);
}

console :

2017-04-06 14:02:47.607  INFO 7932 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
2017-04-06 14:02:47.619  INFO 7932 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2017-04-06 14:02:47.759  INFO 7932 --- [           main] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Driver does not support get/set network timeout for connections. (这个 org.postgresql.jdbc.PgConnection.getNetworkTimeout() 方法尚未被实作。)
2017-04-06 14:02:47.777  INFO 7932 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
xi
1xi
xh
2017-04-06 14:02:47.848  INFO 7932 --- [           main] com.ium.um.Application                   : Started Application in 5.911 seconds (JVM running for 6.416)

I ve tried it. It doesnt work.When i use integers it works fine but when i use strings it doesnt. Is there anyway to avoid the backslash of the apostrophe value? I want this one " ' " to be the same and not " \' " — Christos.S, Apr 06 '17 at 04:29

How to pass a string value of java to sql query

1 Answers1