java - 如何将java的字符串值传递给sql查询

Question

我正在尝试在 Java Netbeans 中创建此 sql 查询:

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

我正在使用这样的准备语句:

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

问题是“setString”根本不起作用。我发现“'”的值实际上是“\'”所以查询变成了

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

Answer 1

试试这个:

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();

我在我的代码上尝试了这个，它成功了。我用的是Spring boot + mybatis + postgresql

SQL 表和一些数据:

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查询:

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

sql查询结果:

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

java代码

@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);
}

控制台:

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)