trying to get a query to use the LIKE function with a variable in JSP_问答_开发者

I have a query that is being used to pull usernames and info about the user. In Access I had the LIKE function so that the user didn't have to type in a specific name. I am now transferring it over to JSP. Here is the line in the query that I am having troubles with in JSP:

WHERE ObjectName Like '" + "%"+ VariableName + "%" +"';

The query runs fine but does not show any information even if I put in the entire name. If I change it to:

WHER开发者_如何学运维E ObjectName = '" + VariableName +"';

it works, but I would like to give the user a chance to have to ability to put in partial names in case they do not know how to spell the name or typ eit incorrectly. Any help would be apprecited.

Thanks

The line you've shown is a bit odd, but syntactically valid. So the problem lies somewhere else. What does variableName actually contain?

That said, you shouldn't be writing raw Java code in JSP files. Do that in a Java class. You can use a Servlet class to preprocess or postprocess requests. Also grab PreparedStatement to avoid SQL injections. Here's a kickoff example:

public List<User> search(String username) throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<User> users = new ArrayList<User>();

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement("SELECT id, username, age, email FROM user WHERE username LIKE ?");
        statement.setString(1, "%" + username + "%");
        resultSet = statement.executeQuery();
        while (resultSet.next()) {
            users.add(mapUser(resultSet));
        }
    } finally {
        close(connection, statement, resultSet);
    }

    return users;
}

Avoid writing SQL queries in JSP
"SELECT * FROM something WHERE ObectName LIKE '%" + VariableName + "%'" should work

this is an answer for starting users i am created a data base in a name ASHRAF, then i create a table in name CASH. the code is given below

CREATE TABLE CASH(NO INT NOT NULL PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(50) NOT NULL,ADDRESS VARCHAR(100),PET_NAME VARCHAR(50),PLACE VARCHAR(50),TYPE VARCHAR(20),TYPE_OF_PAY VARCHAR(20),AMOUNT INT(6) NOT NULL);

here the NO is auto increment ant it is primary key anyway you can search the contents from the table using jsp code that i given below

am search here using both NAME ADDRESS you can pass the parameters using a html page and a servlet

The html page(show.html) that i created is given below

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>show.html</title>
</head>
<body>
<h1><b><font color=020202>SHOW</font></b></h1><br><br>
   <form name="f6" action="getshow" method="POST" onsubmit="return check(this)">
   <table border="0">
   <tr>
  <td>Name :</td><td><input type="text" name="name"></td>
   </tr>
   <tr>
  <td>House Name :</td><td><input type="text" name="address"></td>
   </tr>
   <tr>
   <td><br><input type="SUBMIT" value="submit"></td>
   </tr>
   </table>
   </form>
</body>
</html>

The servlet is(getshow.java) given below

package Servlets;



import java.io.IOException;



import javax.servlet.RequestDispatcher;



import javax.servlet.ServletException;



import javax.servlet.http.HttpServlet;



import javax.servlet.http.HttpServletRequest;



import javax.servlet.http.HttpServletResponse;



/**



 * Servlet implementation class getdata



 */



public class getshow extends HttpServlet {


    private static final long serialVersionUID = 1L;


    /**

     * @see HttpServlet#HttpServlet()

     */

    public getshow() {

        super();

        // TODO Auto-generated constructor stub

    }


    /**

     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

     */

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
`` throws ServletException, IOException {


        // TODO Auto-generated method stub

    }


    /**`

     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse 
``response)


     */

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        // TODO Auto-generated method stub

        try{

        String url=null;

        String s1=request.getParameter("name");

        String s2=request.getParameter("address");


        request.setAttribute("name",s1);



        request.setAttribute("address",s2);


            url="show.jsp";

        RequestDispatcher view=request.getRequestDispatcher(url);

        view.forward(request, response);

    } catch (Exception e) {

        // TODO Auto-generated catch block

        e.printStackTrace();

    } 

    }


}

The jsp file is(show.jsp) given below

<%@ page language="java" contentType="text/html; charset=UTF-8"


    pageEncoding="UTF-8"%>


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">



<html>



<head>`

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">



<title>show.jsp</title>



</head>



<body>



<%String aid=(String)request.getAttribute("name"); %>



<%String sid=(String)request.getAttribute("address"); %>





  <%




        Connection con=null;

        ResultSet rs=null;

        String records=null;

        StringBuffer appender=new StringBuffer();

        java.sql.PreparedStatement st=null;

        try {

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

            con=DriverManager.getConnection("jdbc:mysql://localhost/ASHRAF?user=root&password=password");

            st=con.prepareStatement("select *from CASH where NAME like '" + aid + "%" +"' and ADDRESS like '" + sid + "%" +"'");

            rs=st.executeQuery();


            %>

    <center><TABLE cellpadding="15" border="2">

    <TR>


<TH>NO</TH>



<TH>NAME</TH>



<TH>HOUSE NAME</TH>



<TH>PET NAME</TH>



<TH>PLACE</TH>



<TH>TYPE OF OCCATION</TH>



<TH>TYPE OF PAY</TH>



<TH>AMOUNT</TH>



</TR>



 <%



while (rs.next()) {



%>



<TR>



<TD><%=rs.getString(1)%></TD>



<TD><%=rs.getString(2)%></TD>



<TD><%=rs.getString(3)%></TD>



<TD><%=rs.getString(4)%></TD>



<TD><%=rs.getString(5)%></TD>



<TD><%=rs.getString(6)%></TD>



<TD><%=rs.getString(7)%></TD>



<TD><%=rs.getString(8)%></TD>



</TR>



<% } %>



</TABLE>



</center>



</div>



<%


    } catch (Exception e) {

    // TODO Auto-generated catch block

    e.printStackTrace();


} 



finally



{


    try {

        con.close();

    } catch (SQLException e) {

        // TODO Auto-generated catch block

        e.printStackTrace();

    }


} %>



</body>



</html>

now you can search either with the name nor with the address or with both.