how to search particular data in database using AND operator
Dear every one,
how to search particular data in database using AND operator...
i have used LIKE operator to search and am able to retrieve datas....
but the problem is how to search the datas using AND operator...
eg: if i am searching for employees based on skills..iejava and jsp
how to get only those employees...
the code that i used for LIKE operator is..
pr=con.prepareStatement("select eid from lang where language like ? ");
pr.setString(1,"%" + s2 +"%");
# 1
You appear not to know the basics of SQL language, and I highly suggest you to either read a book, or take a tutorial in the basics of SQL query language.For instance: http://www.sql-tutorial.net/Next time, try google, it'll save you quite some time.
# 2
Thank u for ur reply...i know the syntax of sql statemenmts....how to search data from database during run time ie in web application ...
# 3
dear every one how to use wild cards in search engine using jspplease help me......
# 4
You wrote:
> i know the syntax of sql statemenmts....
Well then, why did you ask the question if you already knew how to write the SQL query?
SQL queries aren't written in any special way just because you are querying the database from JSP, ASP or PHP.
(apart from a small deviation when using PreparedStatement)
# 5
thank u for ur reply..
i am able to get data from database using LIKE operator...
i am able to find employees in application by giving the skills like c, java etc
but the problem is how to find only employees in application by giving c AND c++ .
thanks in advance
Message was edited by:
senthil_yoga
# 6
Well, I'm lost for words. In one sentence you claim to know SQL query language, in the next you ask how to apply the AND directive?
Just to be nice (in fact, I should've earned a couple of Dukes for this ;) I'll show you a working SQL query from one of my pages, this should give you a hint.
SELECT p.starttime, p.endtime, p.title, p.id
FROM programme p, channel c
WHERE p.channelid = c.channelid
AND p.starttime > ?
AND p.starttime < ?
AND p.channelid = ?
# 7
how can we apply over here...pr=con.prepareStatement("select eid from lang where language like ? "); pr.setString(1, "%" + s2 + "%");ResultSet rs=pr.executeQuery();
# 8
You can't. You are simply not supplying enough parameters.
What is it that you want to do?
If you want to use an AND directive, you need something to compare it with.
Your query gets translated to this:
SELECT eid FROM lang WHERE language LIKE %FooBar%
(if s2="FooBar").
# 9
[nobr]sorry....
i will explain you clearly...ie
i have an web appliction in which the HR can get employees based on skills..
i will show u code ie
this is view page
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
</head>
<body >
<form method="post" action="search1.jsp"
><center><h4><u> Employee Assessment Application</u></h4></center>
<br>
<br>
<br>
<br>
<table width="291" border="1" align="center">
<tr>
<td colspan="2"><div align="center">Search</div></td>
</tr>
<tr>
<td width="137"><div align="right">Skills</div></td>
<td width="138"><input type="text" name="language"></td>
</tr>
<tr>
<td colspan="2">
<div align="center">
<input type="submit" name="Submit" value=" Search">
<input type="reset" name="Submit2" value=" Reset">
</div></td>
</tr>
</table>
<a href="hrloginform.jsp"><h4>Back<h4></a>
<a href="main1.jsp"><h4>Home Page</h4></a>
</body>
</html>
this is controller page
<%@page import="java.sql.*" %>
<%@page import="java.io.*" %>
<%
String s2=request.getParameter("language");
%>
<%
int i=0;
Connection con=null;
PreparedStatement pr=null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:langskill");
pr=con.prepareStatement("select eid from lang where language like ? ");
pr.setString(1, "%" + s2 + "%");
ResultSet rs=pr.executeQuery();
out.println("Employee id's under this skill are: ");
while (rs.next ())
{
String eid = rs.getString("eid");
out.println(eid);
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
pr.close();
con.close();
}
%>
thanx in advance...
i am able to get all employees , when i give skills like c, c++..
but how to get the only employees by giving c AND c++[/nobr]
# 10
Why dont u add all the parameters to ur query string itself like the following.pr=con.prepareStatement("SELECT EID LANG WHERE A LIKE '"+"%"+user+"%'");Then give the following statementResultSet rs=pr.executeQuery();
# 11
@dev_phan:I believe that is an incorrect usage of PreparedStatement.It may very well work, but that isn't it's intended usage.
# 12
This is more of "pseudo"-code than real code, but you should get it.
sqlStmt = "SELECT eid " +
"FROM lang " +
"WHERE language LIKE ? " +
"AND language LIKE ?";
PS = dbConn.prepareStatement(sqlStmt);
PS.setString(1, "%" + String1toCompare + "%");
PS.setString(2, "%" + String2toCompare + "%");
RS = PS.executeQuery();
I'm not exactly sure about the % in the setString, I rarely use LIKE, and in combination with PreparedStatement, well, I don't know what the result will be, so you'll have to do some trial-n-error.
# 13
In order to write a query, we need to understand the data model.
From what you have shown, you have a table "lang" which has got an eid (employee id?) and a language (being a programming language) which appears to be a character field.
So I presume if a candidate knows two 'languages' then they would get two entries in this table. They can type whatever you like into that field?
What happens if someone writes J2SE or JDK or other variations on the same theme - or misspells something in this field?
I can see an issue if you tried searching for %c% as this would return all languages which have got the letter 'c' in it somewhere.
c
c#
c++
cobol (its got a c in it)
basic ...
Given the current structure, what I think you want is the intersection of 2 queries.
The query would look something like this:
select eid from lang where language like '%c++%'
intersect
select eid from lang where language like '%java%'
However I think the structure is potentially flawed.
A better approach in my opinion would be to have a table of "Skills/Languages" and then a many to many join table between Employee and Skill to indicate which employee has which skill.
# 14
thank u very much for ur help...
i have applied as u said, now i am able to get only employees that have both skills...but not based on single skill
how we can adopt both steps...
i will show u code that i have applied
pr=con.prepareStatement("SELECT eid " +
"FROM lang " +
"WHERE language LIKE ? " +
"AND language LIKE ?");
//pr.setString(1, "%" + s2 + "%");
pr.setString(1,s2);
pr.setString(2, s2 );
# 15
Reply to evnafetswhat you said is exactly correct....the problem is to intersect.....i will apply and get back to u....i really thank u and ked
# 16
once again i thank u very much......
still now i able to get employee ids if it satisfy both the language...
but now not able to select employees based on LIKE operator
i will show u the code , please give ur suggestions.....
also i inform u that , i am updating skills for employees like this into database ie c,c++....
<%
int i=0;
Connection con=null;[code]
PreparedStatement pr=null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:langskill");
// pr=con.prepareStatement("select eid from lang where language like ? ");
// pr=con.prepareStatement("SELECT EID FROM LANG WHERE A LIKE '"+"%"+user+"%'");
pr=con.prepareStatement("SELECT eid " +
"FROM lang " +
"WHERE language LIKE ? " +
"AND language LIKE ?");
// pr=con.prepareStatement("select eid from lang where language like "%c++%"intersect select eid from lang where language like "%java%);
//pr.setString(1, "%" + s2 + "%");
pr.setString(1,s2);
pr.setString(2, s2 );
//pr.setString(3, s2 );
ResultSet rs=pr.executeQuery();
out.println("Employee id's under this skill are: ");
while (rs.next ())
{
String eid = rs.getString("eid");
out.println(eid);
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
pr.close();
con.close();
}
%>
the other code for inserting skills into database is
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:langskill");
PreparedStatement pStmt = con.prepareStatement("update lang set language=? where eid=" + s1+"");
for ( i = 0; i < s3.length; i++)
{if(i < s3.length - 1)
{
a = a + s3 + ",";
}
else
{
a= a + s3;
}
pStmt.setString(1,a);
j=pStmt.executeUpdate();
}
session.setAttribute("a",a);
}
catch(Exception e)
{
System.out.println(e);
}
%>
[/code]
this will provide much more explanation....
thanks a lot in advance
# 17
sorry...the clear view is this
once again i thank u very much......
still now i able to get employee ids if it satisfy both the language...
but now not able to select employees based on LIKE operator
i will show u the code , please give ur suggestions.....
also i inform u that , i am updating skills for employees like this into database ie c,c++....
<%
int i=0;
Connection con=null;
PreparedStatement pr=null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:langskill");
// pr=con.prepareStatement("select eid from lang where language like ? ");
// pr=con.prepareStatement("SELECT EID FROM LANG WHERE A LIKE '"+"%"+user+"%'");
pr=con.prepareStatement("SELECT eid " +
"FROM lang " +
"WHERE language LIKE ? " +
"AND language LIKE ?");
// pr=con.prepareStatement("select eid from lang where language like "%c++%"intersect select eid from lang where language like "%java%);
//pr.setString(1, "%" + s2 + "%");
pr.setString(1,s2);
pr.setString(2, s2 );
//pr.setString(3, s2 );
ResultSet rs=pr.executeQuery();
out.println("Employee id's under this skill are: ");
while (rs.next ())
{
String eid = rs.getString("eid");
out.println(eid);
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
pr.close();
con.close();
}
%>
the other code for inserting skills into database is
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:langskill");
PreparedStatement pStmt = con.prepareStatement("update lang set language=? where eid=" + s1+"");
for ( i = 0; i < s3.length; i++)
{ if(i < s3.length - 1)
{
a = a + s3 + ",";
}
else
{
a= a + s3;
}
pStmt.setString(1,a);
j=pStmt.executeUpdate();
}
session.setAttribute("a",a);
}
catch(Exception e)
{
System.out.println(e);
}
%>
this will provide much more explanation....
thanks a lot in advance
# 18
What do you mean by "now not able to select employees based on LIKE?"
Can you detail your database design?
What tables do you have?
How are you storing the values?
From the new code you have shown it seems that language is a comma separated value string of all languages an employee knows - not what I was talking about before with multiple rows for each language (foreign key)
I think you need to review your data model.
# 19
thank u very much for your reply....
i have one table emp containing employee name , employee id...the employee id is primary key
the other table skill containing employee id and language..
i have one select box containing skills , where i can select multiple skills(c,c++,java,jsp..etc) and update into particular employee id..
i am inserting languages into database seperated wiith ", "
i have to select employee depending upon languages ie
the two ways->
1->
employee should be selected using LIKE operator eg : if i give c, c++...it should select all employees having skills like c,c++
2->
employee should be selected only by depending upon two skills eg: such as c AND c++
******************
please help me, really urgent
# 20
can any one please help me
# 21
Hi Senthil,
why do u want to have all the skill sets in one record itself?is there a particular reason for that?why dont u have them in separate records?
just for example 101 eid has 4 skills like java,c,c++.
then ur lang table will have its entries as
101 java
101 c
101 c++
then ur serach criteria boils down to course wide level like as follows
select distinct eid from lang where language in ('java','c') etc
ur string java and c will be dyanamic parameters.
Pls let me know if this is helpful or not.
# 22
thank u very much for ur reply...
yes i am having having table that looks like eidskills
10java, c
27 c,c++
but the problem is , in the web application, skills will be given randomly,
we have to search employees based on skills
please give ur suggestion, how can i implement
# 23
Hi,
Why dont u change the table structure of lang as follows
eid foreign key from employees table and the language column will only be having one skill instead of all skills at one place.
when the web application selects the courses randomly, capture all the entered random entries and execute the following query which returns the eid's which qualify the serach criteria.
select distinct eid from lang where language in ("+"'"+skill1+"',"+"'"+skill2+"')";
