I have a question. How to search a specific user in a form? like i have a servlet which contains a text box and a search button. when a user writes the firstname or lastname and click on the button my browser should display all of the information.
SELECT FirstName, LastName, UserName, Password
FROM USERS.posts
WHERE FirstName ..... this is where i am stuck.
Any help will be appreciated.
Thanks
> I have a question. How to search a specific user in a
> form? like i have a servlet which contains a text box
> and a search button. when a user writes the firstname
> or lastname and click on the button my browser should
> display all of the information.
>
> SELECT FirstName, LastName, UserName, Password
> FROM USERS.posts
> WHERE FirstName ..... this is where i am stuck.
>
> Any help will be appreciated.
> Thanks
Although you probably wont you should really put some effort in an learn SQL instead of just learning it on the fly as you need it. The basics are very simple and the way you write your statements and the values you search on and join on can be extremely important.
Even after a couple of weeks of intense learning you will see a big improvement, SQL is very powerful and pretty straightforward and knowing how to query large tables can be the difference between getting results back or not getting them back at all because of a time out.
PreparedStatement implementations.
option 1 using =
String nameToSearchFor = // from somewhere
String sql = "SELECT FirstName, LastName, UserName, Password FROM USERS.posts WHERE FirstName = ? OR LastName = ?";
//conn is your database connection
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,nameToSearchFor);// note parameter indexes start with 1
ps.setString(2,nameToSearchFor);
ResultSet rs = ps.executeQuery();
option 2 using LIKE
String nameToSearchFor = // from somewhere
String sql = "SELECT FirstName, LastName, UserName, Password FROM USERS.posts WHERE FirstName LIKE ? OR LastName LIKE ?";
//conn is your database connection
PreparedStatement ps = conn.prepareStatement(sql);
String nameWithWildcards = "%"+nameToSearchFor+"%";
ps.setString(1,nameWithWildcards);
ps.setString(2,nameWithWildcards);
ResultSet rs = ps.executeQuery();
It is a VERY good idea to get into the habit of using PreparedStatements. There are several advantages to doing so:
1) Your code will be more portable and less of it because you don't have to deal with awkward formats e.g. dealing with Dates
2) Your code will be safer. Using PreparedStatements prevents SQL injection attacks. Even at the best of times one will have problems with things like ' in text. PreparedStatements fix this problem thus averting the security danger that can result.
3) Proper use of PreparedStatements can in some cases lead to performance improvements
4) [url=http://forum.java.sun.com/profile.jspa?userID=675384]This guy[/url] will be your friend.
For more information consult the following references
• [url=http://en.wikipedia.org/wiki/SQL_Injection]Wikipedia entry on SQL Injection[/url]
• [url=http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html]Article on MySQL.com on why to use PreparedStatements[/url]
• [url=http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html]Sun Tutorial on Using PreparedStatements[/url]
[nobr]public class SearchServlet extends javax.servlet.http.HttpServlet implements javax.servlet.Servlet {
private String keyword = "";
public SearchServlet() {
super();
}
public void init() {
try {
Class.forName("org.apache.derby.jdbc.ClientDriver");
System.out.println("Driver loaded");
}
catch (ClassNotFoundException e) {
System.out.println(e.toString());
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
method stub
sendSearchForm(response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
keyword = request.getParameter("keyword");
sendSearchForm(response);
sendSearchResult(response);
}
void sendSearchResult(HttpServletResponse response)
throws IOException {
PrintWriter out = response.getWriter();
try {
Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/sample");
System.out.println("got connection");
Statement s = con.createStatement();
out.println("<TABLE>");
out.println("<TR>");
out.println("<TH>First Name</TH>");
out.println("<TH>Last Name</TH>");
out.println("<TH>User Name</TH>");
out.println("<TH>Password</TH>");
out.println("</TR>");
String sql = "SELECT FirstName, LastName, UserName, Password" +
" FROM USERS.posts" +
" WHERE FirstName LIKE '%" +keyword + "%'" +
" OR LastName LIKE '%" +keyword + "%'";
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
out.println("<TR>");
out.println("<TD>" + (rs.getString(1)) + "</TD>");
out.println("<TD>" + (rs.getString(2)) + "</TD>");
out.println("<TD>" + (rs.getString(3)) + "</TD>");
out.println("<TD>" + (rs.getString(4)) + "</TD>");
out.println("</TR>");
}
s.close();
con.close();
}
catch (SQLException e) {
}
catch (Exception e) {
}
out.println("</TABLE>");
}
private void sendSearchForm(HttpServletResponse response)
throws IOException {
PrintWriter out = response.getWriter();
out.println("<BR><H2>Search Form</H2>");
out.println("<BR>Please enter the first name, last name or part of any.");
out.println("<BR>");
out.println("<BR><FORM METHOD=POST>");
out.print("Name: <INPUT TYPE=TEXT Name=keyword");
out.println(">");
out.println("<INPUT TYPE=SUBMIT>");
out.println("</FORM>");
out.println("<BR>");
out.println("<BR>");
}
Not the best way but it can be done like the way i said it also. But cotton your's a good solution. actually i am new to sql and servlets and j2ee and ejb. So still learning. But please adopt cotton post.
Thanks
Message was edited by:
fastmike[/nobr]
Below is a sort of merged copy of fastmike's sendSearchResult
method using PreparedStatements instead. I also moved a few bits
and closed resources in a finally block. Added some comments as
well.
void sendSearchResult(HttpServletResponse response)
throws IOException {
PrintWriter out = response.getWriter();
/* I moved this block because I personally it's better structured
to have only the code in the try block that can actually throw
the exception we are catching
this is just my person opinion but I think it makes the code
easier to follow
*/
out.println("<TABLE>");
out.println("<TR>");
out.println("<TH>First Name</TH>");
out.println("<TH>Last Name</TH>");
out.println("<TH>User Name</TH>");
out.println("<TH>Password</TH>");
out.println("</TR>");
// these variables were moved here so that we can close them in our finally block
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// normally in a servlet we would get a db connection from a data source
con = DriverManager.getConnection("jdbc:derby://localhost:1527/sample");
System.out.println("got connection");
String sql = "SELECT FirstName, LastName, UserName, Password" +
" FROM USERS.posts" +
" WHERE FirstName LIKE ? OR LastName LIKE ? "+
" ORDER BY LastName ASC, FirstName ASC";
ps = con.prepareStatement(sql);
/* this hackery is the only time one needs to do formatting on a
on a value used for a PreparedStatement. Because we want to look
for wild card matches.
*/
String wildCardKeyword = "%"+this.keyword+"%";
ps.setString(1,wildCardKeyword);
ps.setString(2,wildCardKeyword);
rs = ps.executeQuery();
while (rs.next()) {
out.println("<TR>");
/*
Note : It is good practice to fetch columns in the same order as they
appeared in the SELECT. Some drivers and databases do not support
out-of-order fetches.
*/
out.println("<TD>" + (rs.getString(1)) + "</TD>");
out.println("<TD>" + (rs.getString(2)) + "</TD>");
out.println("<TD>" + (rs.getString(3)) + "</TD>");
out.println("<TD>" + (rs.getString(4)) + "</TD>");
out.println("</TR>");
}
}
catch (SQLException e) {
// something should happen here so that you know there was an error
}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException closeRSEx){
// close result set failed... should be logged or something
}
}
if(ps!=null){
try{
ps.close();
}catch(SQLException closePSEx){
// close statement failed... should be logged or something
}
}
if(con!=null){
try{
con.close();
}catch(SQLException closeConEx){
// close connection failed... should be logged or something
}
}
}
out.println("</TABLE>");
}
Also I should point out there is another different discussion to be had
here regarding Servlets and JSP. I really know very little about Servlets
so all I will say is that in general JSP's should be used to generate
the HTML and not the Servlet. The servlet does all the back end dirty
work.
See http://en.wikipedia.org/wiki/Struts
