Querying the database alphabetically from JSP/Servlets

[raghujosh]

I am developing an application wherein I have a JSP page with clickable links to all the 26 alphabets. In my database, I have data starting with all the alphabets. The way, I have(want to) structure the application is something like this. I click a alphabetical link, say A, it calls a servlet wherein there is a generic Select statement which queries the database for data that starts with that alphabet and returns a result set. I want to iterate this resultset and output the same with the following as a single line
1 CheckBox, 2. Data
If the result set contains 100 results, each line has to have a checkbox and the data individually.
I have written code for the JSP and servlet, but it is not working. I also want to know how to output checkbox along with each line of data. My code is mentioned below.

JSP code

<table cellspacing="4" border="1">

<tr>
	<td align="center"></td>
	<td><a href="ClickServlet?letter=A">A</a></td>
	
	<td align="center"></td>
	<td><a href="ClickServlet?letter=B">B</a></td>
	
	<td align="center"></td>
	<td><a href="ClickServlet?letter=C">C</a></td>
	
	<td align="center"></td>
	<td><a href="ClickServlet?letter=D">D</a></td>
	
	<td align="center"></td>
	<td><a href="ClickServlet?letter=E">E</a></td>
	
	<td align="center"></td>
	<td><a href="ClickServlet?letter=F">F</a></td>
	
	<td align="center"></td>
	<td><a href="ClickServlet?letter=G">G</a></td>

and so on till Z

Java code

public class Click {
	
	public static ResultSet ClickApp(String letter)
	{
		String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";	
	
	String url = "jdbc:sqlserver://RAGHU1:1433;databaseName=RPS;integratedSecurity=true";
	
	String username = "Raghavendra";
	String password = "";
	
	Connection conn=null;
	Statement st=null;
   	ResultSet rs=null;
	try
	{
		Class.forName(driver);
		conn = DriverManager.getConnection(url, username, password);
		st=conn.createStatement();
		String sel="SELECT ChemicalName FROM dbo.Chemicals where ChemicalName LIKE '" + letter + "'ORDER BY ChemicalName";
		rs=st.executeQuery(sel);
		
		
		
		
	}
	catch(Exception e)
	{
		e.printStackTrace();
		System.err.println(e.getMessage());
	}
	return rs;
}
		
	
	
	
}

Servlet code

public class ClickServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		String letter = request.getParameter("letter");
		ResultSet x = Click.ClickApp(letter);
		
		try {
			while(x.next()){
				String name = x.getString(1);
				System.out.println(name);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

}

[peter_budo]

1. You should look on PrepareStatement, it is better to use and less confusing when you need to submit more variables in the SQL query
2. You may want to change your query to use wildcards like where ChemicalName LIKE 'letter%' that will pick up only these that does starts with given letter. Read this for more info
3. To output data have look at this tutorial - JSP database connectivity according to Model View Controller (MVC) Model 2 that does show a basic idea of retrieving data in simple form of JSP or second with JSTL that makes it more readable

[raghujosh]

1. You should look on PrepareStatement, it is better to use and less confusing when you need to submit more variables in the SQL query
2. You may want to change your query to use wildcards like where ChemicalName LIKE 'letter%' that will pick up only these that does starts with given letter. Read this for more info
3. To output data have look at this tutorial - JSP database connectivity according to Model View Controller (MVC) Model 2 that does show a basic idea of retrieving data in simple form of JSP or second with JSTL that makes it more readable

Thanks peter. I went through the tutorial and will use it. It was of good help.
Raghu