displaying data based on criteria from 2 tables having same type of data

Dear Experts,

First, thanks for replying my question regarding my servlet.

My mind was very confused then. I realise that I can't use a servlet to process search criteria without having a form.

I have recently read up about DAO pattern and I think it might be able to solve my problem. Correct me if I'm wrong. Some other forums told me to use Hibernate for my problem so I'm quite lost.

Hence, I hope the experts here can give me some sample code how to write my code.

Basically, I have 2 forms already. One for tutors and the other for parents.

So, now I need a servlet (or I do not need one? ...please advise) to process a search.

The search will have following features:-

1. it enables me to enter the ID of the parents.
2. based on the ID of parents I will know what is the type of tutors they are looking for - e.g. budget, type of tutors.
3. thereafter, there will be a button to allow me to display the tutors that match the parent's requirements.

OK. I look forward to your guidance. Many thanks.

View Answers

June 8, 2010 at 5:18 PM

Hi Friend,

You can use jsp or servlet or both. We have used jsp.

Try the following code:

1)enter.jsp:

<%@page import="java.sql.*"%>
<script>
function info(sub,qua){
window.open("info.jsp?sub="+sub+"&&qua="+qua,'mywindow','width=300, height=200,toolbar=no,resizable=yes,menubar=yes');

}
</script>
<form>
Enter ID:<input type="text" name="id"><input type="submit" value="Submit" style="background-color:#49743D;font-weight:bold;color:#ffffff;">
</form>
<%String id=request.getParameter("id");
if(id!=null){
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql";, "root", "root");
Statement st=connection.createStatement();
ResultSet rs=st.executeQuery("SELECT * FROM parents where id='"+id+"'");
%>
<table border="1">
<tr><th>Subject</th><th>Qualification</th><th>Get Information</th></tr>
<%
while(rs.next()){
String sub=rs.getString("subjects");
String qua=rs.getString("qualification");

%>
<tr><td><%=rs.getString("subjects")%></td><td><%=rs.getString("qualification")%></td><td><input type=button value="Get" style="background-color:#49743D;font-weight:bold;color:#ffffff;" onclick='info("<%=sub%>","<%=qua%>")'></td></tr>
<%
}
%>
</table>
<%
}
%>

2)info.jsp:

<%@page import="java.sql.*"%>
<%
String sub=request.getParameter("sub");
String qua=request.getParameter("qua");
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql";, "root", "root");
Statement st=connection.createStatement();
ResultSet rs=st.executeQuery("SELECT * FROM memberparticulars WHERE subjects='"+sub+"' and degree='"+qua+"'");
%>
<center>
<table border="1">
<tr><th>Name</th><th>Contact</th><th>Subjects</th></tr>
<%
while(rs.next()){
%>
<tr><td><%=rs.getString("name")%></td><td><%=rs.getString("contact")%></td><td><%=rs.getString("subjects")%></td></tr>
<%
}
%>
</table>
</center>

We have created following database tables:

1)'parents'
CREATE TABLE `parents` (
`id` bigint(255) NOT NULL auto_increment,
`subjects` varchar(255) default NULL,
`qualification` varchar(255) default NULL,
PRIMARY KEY (`id`)
)
2'memberparticulars'
CREATE TABLE `memberparticulars` (
`id` bigint(255) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`contact` varchar(255) default NULL,
`subjects` varchar(255) default NULL,
`degree` varchar(255) default NULL,
PRIMARY KEY (`id`)
)

Hope that it will be helpful for you.
Thanks