DaniWeb Forum Index > Web Development > JSP

generating dynamic/runtime query

Please support our JSP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Nov 2006

Posts: 38

Reputation: apontutul is an unknown quantity at this point

Solved Threads: 1

apontutul

Offline

Light Poster

generating dynamic/runtime query

Aug 30th, 2007

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:

 Help with Code Tags 
 JSP Syntax (Toggle Plain Text) 
 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);
 
              }
 
  }
 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.

Help with Code Tags

JSP Syntax (Toggle Plain Text)

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.

Last edited by apontutul; Aug 30th, 2007 at 3:00 am.

•

Join Date: Nov 2006

Posts: 38

Reputation: apontutul is an unknown quantity at this point

Solved Threads: 1

apontutul

Offline

Light Poster

Re: generating dynamic/runtime query

Aug 31st, 2007

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

•

Join Date: May 2007

Posts: 4,424

Reputation: Ezzaral has much to be proud of

Solved Threads: 507

Ezzaral

Offline

Industrious Poster

Re: generating dynamic/runtime query

Aug 31st, 2007

•

Originally Posted by apontutul

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

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.

•

Join Date: Aug 2007

Posts: 78

Reputation: lookof2day is an unknown quantity at this point

Solved Threads: 10

lookof2day lookof2day is offline

Offline

Junior Poster in Training

Re: generating dynamic/runtime query

Aug 31st, 2007

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"%>

<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.