Implementation of Advanced search

[Vinutha]

Hi,
Cud someone help me with the implementation of Advanced Search feature.The feature enables a user to search within the website I am building.I am using NetBeans 5.0 IDE and PointBase database.I am coding in JSP.

[graviton]

what sould your advanced search look like? some examples of your search queries?
usually you will have to split the given string and use all single words to make a sql query, like:
car and big
-> search therms "car" and "big"
select * from documents where text like '%car%' and text like '%big%'
car or big
->select * from documents where text like '%car%' OR text like '%big%'
avoiding sql hacks:
if someone enters "car%'; drop tables;--"
this will lead to the sql
select * from documents where text like '%car%'; drop tables;--%'
so you will have to replace any dangerous characters in the searchterm. i suggest to only allow letters and numbers.

[SupriyaR]

Thanx for responding graviton.The advanced search text box accepts a part number or product name , compares it with a table, in the data base,which contains the part number or part name and the associated page to open.

[graviton]

so what's the help you need? it looks like you now what your've to do.

[Vinutha]

HI graviton,I am new to JSP.So,though I know the logic I donot know the exact syntax to code.Can U provide me with a sample code.

[Joe Beam]

Or you could simply make that database user only have SELECT rights.

[major]

here some jsp code to connect to a mysql database, you will need to change the connection string to suit the database your using. find some connection code for the database your using.

The code will search a table where the field 'Text' is like the strSearch string.

<%@ page import="java.sql.*" %>
<%
String strSearch = request.getParameter( "Search" ); // <-- html input field name
Connection connection2 = null;
Statement statement2 = null;
ResultSet rs2 = null;
String print_item[];
int k=0;
%>



<%
try
{

Class.forName("com.mysql.jdbc.Driver").newInstance();
connection2 = DriverManager.getConnection("jdbc:mysql:///DBName", "username", "password");
statement2 = connection.createStatement();
rs2 = statement2.executeQuery("SELECT * FROM Table_name WHERE Text like ='%" + strSearch + "%'");



			while (rs2.next()){
			out.write (rs2.getString(1));
			}

}

catch (Exception e){}
%>

[Vinutha]

thanks major for the code snippet related to advanced search.we are using netbeans 5.0 ide which has derby database builtin.we have a problem establishing the connection to the database through jsp code.what are the steps to connect to derby or incase we use sql server 2000 do we have to have drivers or connectors as such.

[graviton]

as the example shows you need two things:
1. Class.forName("com.mysql.jdbc.Driver").newInstance();
a jdbc driver to connect to the specific database. in case there is no specific jdbc driver, you can use a jdbc to odbc driver. the example uses a jdbc driver for mysql.

2. you need to use a connection url for your jdbc driver, which will differ from the connection string for the jdbc driver for mysql as denoted in the example:
connection2 = DriverManager.getConnection("jdbc:mysql:///DBName", "username", "password");

[graviton]

by the way, think about using the lucene api from apache.org. this supplies a great api for fulltext indexing content and complex queries.

[sammy_me]

hiii i am new to lucene..... some how i hav manages to index and search my database but couldn't delete the index..........can anyone help me with it........
i am posting the funtion

public void delete(long userid) throws IOException
{
String uid = Long.toString(userid);
try
{
final IndexReader reader = IndexReader.open(dir);
final Term term = new Term(add.ID, uid);
reader.delete(term);
reader.close();


}
catch (IOException e)
{
System.out.println("Error while removing student data from index" +e);
e.printStackTrace();
}
}