我试图使用setString(index, parameter)
方法的准备语句,以创建一个ResultSet
,但它似乎没有正确插入。我知道查询是正确的,因为我在后来的else
中使用了相同的查询(减去对setString
的需要)。下面是我目前的代码:
**据我所知,ps.setString(1, "'%" + committeeCode + "%'");
应该取代查询中的?
,但我的输出却说不是这样。如有任何帮助,不胜感激。
public String getUpcomingEvents(String committeeCode) throws SQLException{
Context ctx = null;
DataSource ds = null;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
StringBuilder htmlBuilder = new StringBuilder();
String html = "";
try {
ctx = new InitialContext();
ds = (DataSource) ctx.lookup("java:ConnectDaily");
conn = ds.getConnection();
if(committeeCode != null){
//get all events
String queryStatement = "SELECT " +
.......
"WHERE c.calendar_id = ci.calendar_id AND c.short_name LIKE ? " +
"AND ci.style_id = 0 " +
"AND ci.starting_date > to_char(sysdate-1, 'J') " +
"AND ci.item_type_id = cit.item_type_id " +
"ORDER BY to_date(to_char(ci.starting_date), 'J')";
ps = conn.prepareStatement(queryStatement);
ps.setString(1, "'%" + committeeCode + "%'");
System.out.println(queryStatement);
rs = ps.executeQuery();
if (rs != null){
while(rs.next()){
String com = rs.getString("name");
String comID = rs.getString("short_name");
String startTime = rs.getString("starting_time");
String endTime = rs.getString("ending_time");
String name = rs.getString("contact_name");
String desc = rs.getString("description");
String info = rs.getString("contact_info");
String date = rs.getString("directory");
htmlBuilder.append("<li><a href='?com="+committeeCode+"&directory=2014-09-10'>"+com+" - "+ date +" - "+startTime+" - "+endTime+"</a> <!-- Link/title/date/start-end time --><br>");
htmlBuilder.append("<strong>Location: </strong>"+comID+"<br>");
htmlBuilder.append("<strong>Dial-In:</strong>"+com+"<br>");
htmlBuilder.append("<strong>Part. Code:</strong>"+info+"<br>");
htmlBuilder.append("<a href='http://nyiso.webex.com'>Take me to WebEx</a>");
htmlBuilder.append("</li>");
}
}
html = htmlBuilder.toString();
。..
}catch (NamingException e) {
e.printStackTrace();
//log error and send error email
} catch (SQLException e) {
e.printStackTrace();
//log error and send error email
}finally{
//close all resources here
ps.close();
rs.close();
conn.close();
}
return html;
}
}
输出14:18:22,979 INFO [STDOUT] SELECT to_char(to_date(to_char(ci.starting_date), 'J'),'mm/dd/yyyy') as start_date, to_char(to_date(to_char(ci.ending_date), 'J'),'mm/dd/yyyy') as end_date, to_char(to_date(to_char(ci.starting_date), 'J'),'yyyy-mm-dd') as directory, ci.starting_time, ci.ending_time, ci.description, cit.description as location, c.name, c.short_name, ci.add_info_url, ci.contact_name, ci.contact_info FROM calitem ci, calendar c, calitemtypes cit WHERE c.calendar_id = ci.calendar_id AND c.short_name LIKE ? AND ci.style_id = 0 AND ci.starting_date > to_char(sysdate-1, 'J') AND ci.item_type_id = cit.item_type_id ORDER BY to_date(to_char(ci.starting_date), 'J')
setString
:
ps.setString(1, "%" + committeeCode + "%");
该方法将指定的String
绑定到第一个参数。它不会改变保存在queryStatement
中的原始查询String
。
占位符仍然是SQL文本的一部分。
在执行语句时传递bind值;实际的SQL文本没有被修改。(这是预处理语句的一大优点:可以重用完全相同的SQL文本,并且避免了硬解析的开销。
还要注意,在值中包含了单引号,这有点奇怪。
如果要在SQL文本中替换绑定占位符,假设committeeCode包含foo
,则等效的SQL文本将是:
AND c.short_name LIKE '''%foo%'''
将只匹配以单引号开始和结束的c.short_name
值,并且包含字符串foo
。
(这看起来更像Oracle SQL语法而不是MySQL)
正如我们所知,在setString中我们只能传递字符串值,所以即使我们这样写代码:
String param="'%"+committeeCode+"%'";
如果你输出param的值,它会抛出错误,因此你不能在预处理语句中使用它。
你需要稍微修改一下
String param="%"+committeeCode+"%";(Simpler one, other way can be used)
ps.setString(1,param);