[SQL] prepared statement에서 LIKE 사용방법

---

0. 환경1

macbook-pro 13” 2012 mid / 10GB
parallels9 ubuntu 12.04 LTS

---

1. 문제

preparedStatement를 사용시 SQL QUERY에 LIKE 문법 있을 때 처리방법

코드

StringBuffer query = new StringBuffer();
query.append("SELECT * FROM article WHERE name LIKE '%?%'");

// logger
logger.debug("name: " + name);

psmt = conn.prepareStatement(query.toString());
psmt.setString(1, name);  // 이 부분에서 에러 발생
rs = psmt.executeQuery();

에러 메시지

12:00:25.498 [DEBUG] [http-bio-8080-exec-3] [next.wildgoose.dao.ReporterCardDAO] - name: 하
12:00:25.514 [DEBUG] [http-bio-8080-exec-3] [next.wildgoose.dao.ReporterCardDAO] - Parameter index out of range (1 > number of parameters, which is 0).
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086) ~[mysql-connector-java-5.1.29.jar:na]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) ~[mysql-connector-java-5.1.29.jar:na]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) ~[mysql-connector-java-5.1.29.jar:na]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920) ~[mysql-connector-java-5.1.29.jar:na]
    at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3796) ~[mysql-connector-java-5.1.29.jar:na]
    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3778) ~[mysql-connector-java-5.1.29.jar:na]
    at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4599) ~[mysql-connector-java-5.1.29.jar:na]
    at next.wildgoose.dao.ReporterCardDAO.findReportersByName(ReporterCardDAO.java:101) ~[ReporterCardDAO.class:na]

---

2. 해결방법

Mysql

StringBuffer query = new StringBuffer();
query.append("SELECT * FROM article WHERE name LIKE ?");

psmt.setString(1, "%"+name+"%"); 

Oracle

StringBuffer query = new StringBuffer();
query.append("SELECT * FROM article WHERE name LIKE '%' || ? || '%'");

psmt.setString(1, name); 

Note
Oracle의 방법을 Mysql에 적용할 경우,
첫 번째 요청에 대해서는 결과를 응답하지만
name을 바꾼 후 요청은 update시키지 못한다는 문제가 발생했다.
그 이유에 대해서는 아직 찾아보지 못했다.

---

* 참고자료

stackoverflow - 2857164
stackoverflow - 8247970

---

Written with Dec7.