CODEWITHSUNDEEP
javamysqlservlets
user3054319
user3054319

Reputation: 85

stuck in creating query to fetch the result from database?

i am creating a project called discussion forum in servlets... but i stuck in the query to fetch the result from database with respect to the given topic... i make a servlet named index in which there is a topics when i clicked the topic i got some kind of error like Unknown column 'pepsi' in 'where clause' in the MessageDisplayed servlet...i think there is a problem in the query... the coding of index servlet is:

@WebServlet("/Index")
public class Index extends HttpServlet {
    private static final long serialVersionUID = 1L;
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        String url = "jdbc:mysql://localhost:3306/df";
        String driver = "com.mysql.jdbc.Driver";
        String USER = "root";
        String PASS = "root";
        try {
            Class.forName(driver);
            Connection conn = (Connection) DriverManager.getConnection(url, USER, PASS);
            out.print("<html><body>");
            out.print("<a href='PostTopic'><input type='submit' value='Post New Topic'></a><br><br>");
            String sql = "select topic_name from topic";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                String topic_name = rs.getString("topic_name");
                HttpSession session = request.getSession();
                session.setAttribute("topic_name", topic_name);

                out.print("<a href='MessageDisplayed'>");
                out.print(topic_name);out.print("</a>");
                out.print("<br>");
            }

            out.print("</body></html>");


        } catch (Exception e) {
            out.print(e.getMessage());
        }
        out.close();
    }

}

and the coding of MessageDisplayed servlet is:

public class MessageDisplayed extends HttpServlet {
    private static final long serialVersionUID = 1L;
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
         String url = "jdbc:mysql://localhost:3306/df";
         String driver = "com.mysql.jdbc.Driver";
         String USER = "root";
         String PASS = "root";
        try{
            Class.forName(driver);
            Connection conn = (Connection) DriverManager.getConnection(url, USER, PASS);
            HttpSession session = request.getSession();
            String topic = (String) session.getAttribute("topic_name");
            String sql = "select user_name,comment from comments where topic_name="+topic;
            PreparedStatement ps = conn.prepareStatement(sql);
        //  ps.setString(1, topic);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                String name = rs.getString("user_name");
                String comment = rs.getString("comment");
                out.print("<html><body>");
                out.print("Name : "+name);out.print("<br>");out.print("Message : "+comment);out.print("<br>");
                out.print("</body></html>");
            }
        }
        catch(Exception e){
            out.print(e.getMessage());
        }
        out.close();
    }

}

the database has 2 tables topic table(topic_id,topic_name) and a comments table(comment_id,comment,user_name,topic_id) the insertion is working fine... but i can't be able to fetch the results please help me out as soon as possible....

Upvotes: 0

Views: 186

Answers (1)

user2948112
user2948112

Reputation:

I think error is in this line

String sql = "select user_name,comment from comments where topic_name="+topic;

Moreover the way you are using is not the right way of using PreparedStatement.

Use this way

PreparedStatement ps = conn.prepareStatement("select user_name,comment from comments where topic_name=?");
ps.setString(1,topic);

Upvotes: 1

Related Questions