generating dynamic/runtime query
 

I want to generate a code that will automatically take the poll-id..of which the user wishes to view the result of that poll. there's the code:using mysql & java & another thing i wish to use this on a jsp page:

 package votepiepack;

   

  import java.sql.Connection;

  import java.sql.DriverManager;

  import java.sql.*;

  import org.jfree.chart.*;

  import org.jfree.data.jdbc.*;

  import org.jfree.data.general.*;

   

   

  public class votepie {

   

              /**

               * @param args

               */

              

              private PieDataset readData() {

                          JDBCPieDataset data = null;

                          String url = "jdbc:mysql://localhost/vote";

                          Connection con;

                          try {

                          Class.forName("com.mysql.jdbc.Driver");

                          }

                          catch (ClassNotFoundException e) {

                          System.err.print("ClassNotFoundException: ");

                          System.err.println(e.getMessage());

                          }

                          try {

                          con = DriverManager.getConnection(url, "vote", "vote001");

                          data = new JDBCPieDataset(con);

                          String sql = "SELECT option_text, counter FROM VOTE_VOTES WHERE poll-id=1;";

                          data.executeQuery(sql);

                          con.close();

                          }

                          catch (SQLException e) {

                          System.err.print("SQLException: ");

                          System.err.println(e.getMessage());

                          }

                          catch (Exception e) {

                          System.err.print("Exception: ");

                          System.err.println(e.getMessage());

                          }

                          return data;

                          }

              public static void main(String[] args) {

                          // TODO Auto-generated method stub

                          

                          votepie pd = new votepie();

                          pd.readData();

                          

                          //creating the chart

                          JFreeChart chart = ChartFactory.createPieChart(

                                                  "Sample Pie Chart",

                                                  pd.readData(),

                                                  true, // legend?

                                                  true, // tooltips?

                                                  false // URLs?

                                                  );

                                                  // create and display a frame...

                                                  ChartFrame frame = new ChartFrame("First", chart);

                                                  frame.pack();

                                                  frame.setVisible(true);

   

              }

   

  }

as you can see I can view only 1 result at a time.

String sql = "SELECT option_text, counter FROM VOTE_VOTES WHERE poll-id=1;";

but how is it possible to capture the poll-id on which the user wishes to click & show the result of that poll....is it by using "placeholders" if yes how? or any other, please suggest ...thanks

ps: donno where to post so posting both on mysql & java forums.

Re: generating dynamic/runtime query
 

hope i've made myself clear..please help guys

Re: generating dynamic/runtime query
 

This is really a question that belongs in the JSP forum. You will need to read the pollId they clicked from the GET or POST request, place that variable in your query, and display the results. It is a very standard thing for web programming.

Re: generating dynamic/runtime query
 

Well there are a number of ways to capture the id's. But what is the best way for you is to be decided by you. Here are the ways:

1) URL rewriting. (Exposed to visible eyes. Not a safe bet)
2) Cookies. (Cookies needs to be enabled on the Client Browser).
3) HTTPSession Object. (You can pass the id through the HTTPSession Object: Very Safe)

Please refer some text on how to use them.

Now, as far as you are concerned with sending the id, you should create a public method which will take id as a parameter and you can pass the id from it.
Here's how it would look like:

public class VotePie {

public static PieDataSet readData(int id){
JDBCPieDataset data = null;
String url = "jdbc:mysql://localhost/vote";
Connection con;
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url, "vote", "vote001");
data = new JDBCPieDataset(con);
String sql = "SELECT option_text, counter FROM VOTE_VOTES WHERE poll-id="+id;//you will send the parameter value at runtime.
data.executeQuery(sql);
con.close();
} catch (SQLException e) {
System.err.print("SQLException: ");
System.err.println(e.getMessage());
} catch (Exception e) {
System.err.print("Exception: ");
System.err.println(e.getMessage());
}
return data;
}

}

Your jsp page from where you need to take the parameters would look something like this. I'll call it inputParam.jsp

<%@page import="VotePie"%>
<!--%Other libraries required by you%-->
<html>
<a href="/jsp/collectVote&1">Choice1</a>
<a href="/jsp/collectVote&2">Choice2</a>
<a href="/jsp/collectVote&3">Choice3</a>
</html>

It will direct you to a new jsp page or Servlet (whatever you prefer. Mostly Servlet is preferred) but restricting to jsp for now.

in the collectVote.jsp you are required to get the input parameter and pass it to the readData method of ....

Here's the code:

String param = (String)request.getParameter();
int id = Integer.parseInt(param);


JDBCPieDataset data = VotePie.readData(id);
request.setParameter("DATA", data);
response.setUrl("theUrlToDisplayPie");

You'll automatically be directed to the new web page.
In this page write the same code you were writing in the main method. Just note that no method name is required.