CODEWITHSUNDEEP
javamysqlsqlprepared-statementresultset
IbrahimMitko
IbrahimMitko

Reputation: 1207

setString for prepared statement not working

I am trying to use the setString(index, parameter) method for Prepared Statements in order to create a ResultSet but it doesn't seem to be inserting properly. I know the query is correct because I use the same one (minus the need for the setString) in a later else. Here is the code I currently have:

**From what I understand, the ps.setString(1, "'%" + committeeCode + "%'"); is supposed to replace the ? in the query but my output says otherwise. Any help is appreciated.

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;

}

}

Output

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')

Upvotes: 3

Views: 13830

Answers (3)

Amardeep Kumar
Amardeep Kumar

Reputation: 31

As we know that in setString we can pass string value only, So even if we write the code like this:

String param="'%"+committeeCode+"%'";

And if you print the value of param it will throw error, Hence you cannot use it as well in prepared statement.

You need to modify modify it little bit as:

 String param="%"+committeeCode+"%";(Simpler one, other way can be used)
 ps.setString(1,param);

Upvotes: 0

spencer7593
spencer7593

Reputation: 108400

The placeholder remains as part of the SQL text.

The bind value is passed when the statement is executed; the actual SQL text is not modified. (This is one of the big advantages of prepared statements: the same exact SQL text is reused, and we avoid the overhead of a hard parse.

Also note that you are including single quotes within the value, which is a bit odd.

If the bind placeholder were to be replaced in the SQL text, assuming committeeCode contains foo, the equivalent SQL text would be:

   AND c.short_name LIKE '''%foo%'''

which will match only c.short_name values that begin and end with a single quote, and contain the string foo.

(This looks more like Oracle SQL syntax than it does MySQL.)

Upvotes: 1

M A
M A

Reputation: 72854

There is no need for the quotes in setString:

ps.setString(1, "%" + committeeCode + "%");

This method will bind the specified String to the first parameter. It will not change the original query String saved in queryStatement.

Upvotes: 6

Related Questions