how can i collect values from database using OR condition?
have a table containing employee and language ...
I have a select box containing languages and if i select two languages and click on submit.
it should do collect all employees having either of the languages using OR condition..how can i do this out..
i have written some code and i will show u...
please give your suggstions
<%
//String eid=request.getParameter()
String s3[]=request.getParameterValues("language");
String check[] = request.getParameterValues("check");
int i=0;
int j=0;
String a="";
String ename="";
//String check
try
{
Class.forName("com.mysql.jdbc.Driver");
}
catch(ClassNotFoundException ce)
{
out.println(ce);
}
try
{
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test");
PreparedStatement pStmt = con.prepareStatement("select lang.ename from lang where language like ? ");
if(check == null)
{
for ( i = 0; i < s3.length; i++)
{
if(i < s3.length - 1)
{
a=a + s3 + ",";
}
else if(i < s3.length)
{
a = a + s3 ;
}
pStmt.setString(1, "%" + a + "%");
}
}
else
{
for ( i = 0; i < s3.length ; i++)
{
if(i < s3.length - 1)
{
a=a + s3 + ",";
}
else
{
a = a + s3 ;
}
pStmt.setString(1, a );
}
}
%>
<table width="15%" border="1">
<tr>
<td><div align="center">Employees</div></td>
</tr>
<%
ResultSet rs=pStmt.executeQuery();
while (rs.next ())
{
ename = rs.getString("ename");
%>
<tr>
<td><%=ename%></td>
</tr>
<%
}
%>
</table>
<%
}
catch(SQLException e)
{
System.out.println(e.getMessage());
}
%>
# 1
next time, use code tag to format your code. read about formatting in this forum here: http://forum.java.sun.com/help.jspa?sec=formattingwhats your problem?select lang.ename from lang where language like ? or language like ?
# 2
thanku very much for ur reply....
i have to collect all employees having either of the languages using OR condition..how can i do this out..
//String eid=request.getParameter()
String s3[]=request.getParameterValues("language");
String check[] = request.getParameterValues("check");
int i=0;
int j=0;
String a="";
String ename="";
//String check
try
{
Class.forName("com.mysql.jdbc.Driver");
}
catch(ClassNotFoundException ce)
{
out.println(ce);
}
try
{
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test");
PreparedStatement pStmt = con.prepareStatement("select lang.ename from lang where language like ? ");
if(check == null)
{
for ( i = 0; i < s3.length; i++)
{
if(i < s3.length - 1)
{
a=a + s3 + ",";
}
else if(i < s3.length)
{
a = a + s3 ;
}
pStmt.setString(1, "%" + a + "%");
}
}
else
{
for ( i = 0; i < s3.length ; i++)
{
if(i < s3.length - 1)
{
a=a + s3 + ",";
}
else
{
a = a + s3 ;
}
pStmt.setString(1, a );
}
}
%>
<table width="15%" border="1">
<tr>
<td><div align="center">Employees</div></td>
</tr>
<%
ResultSet rs=pStmt.executeQuery();
while (rs.next ())
{
ename = rs.getString("ename");
%>
<tr>
<td><%=ename%></td>
</tr>
<%
}
%>
</table>
<%
}
catch(SQLException e)
{
System.out.println(e.getMessage());
}
# 3
can anyone please help me, really urgent...pleaseeeeeeeeee
# 4
Sure. Start by explaining your problem better. What does it mean, in terms of your database, for an employee to "have" a language? Do you have an employee table? What's in it? Your example only has a ton of irrelevant JSP rubbish (please don't put database code in a JSP) and a query to a "lang" table.
# 5
thank u very much for ur suggestions....
i will explain u in detail...
i have a task to be completed is...
i have employee table that have employee id, name, pasword.
and have language table that have language , employee id, name.
in the HR module, the hr have to select employee based on languages.
if he gives c, c++ -) it should select employees on c or based on c++
# 6
> in the HR module, the hr have to select employee based on
> languages.
> if he gives c, c++ -) it should select employees on c or based on c++
do you mean, the languange number selected may vary so you can not work with your current query because that only capable selecting 1 languange?
# 7
thank you very much for ur suggestion..really thank u very much...there are conditions to be checked in this ie..if the HR select two sklls in the select box and hit enter, it should select employees based on either 1st skill or the 2nd skill if present
# 8
okay... i'm working from your code.
here:
PreparedStatement pStmt = con.prepareStatement("select lang.ename from lang where language like ? ");
this query actually work with only one condition.
adding comma as separator will not works.
second, if you are using 'like' keyword,
when u select languange 'C' you will
also get 'C++'.
so for your condition, '=' is enough.
here is the solution (i assume s3 is selected language):
...
// first you need to construct your sql query
String sql = "select lang.ename from lang where ";
for (int i = 0; i < s3.length; i++) {
if (i > 0) sql += " or ";
sql += "(language = ?)";
}
PreparedStatement pStmt = con.prepareStatement(sql);
// setting parameter
for (int i = 0; i < s3.length; i++) {
pStmt.setString(i+1, s3); // jdbc index always start from 1
}
...
Edit: i put single quote with string. should be double quote.
Message was edited by:
j_shadinata
# 9
thank u very much ...thank u very much ...thank u very much ...hats off to u , i will apply the code and get back to u now.....thank u very much ...
# 10
i have applied as u said ... but i am getting a error such that
unclosed charcter literal...
i have applied like this...
try
{
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test");
//pStmt = con.prepareStatement(" select lang.ename from lang where lang.language like ?" );
if(check == null)
{
String sql = "select lang.ename from lang where ";
for (int i = 0; i < s3.length; i++) {
if (i > 0) sql + = ' or ';
sql += '(language = ?)';
}
PreparedStatement pStmt = con.prepareStatement(sql);
for (int i = 0; i < s3.length; i++) {
pStmt.setString(i+1, s3); // jdbc index always start from 1
}
}
# 11
yes, thats why i edited my previous reply.
here is the problem:
if (i > 0) sql + = ' or '; // should be double-quote, not single
sql += '(language = ?)'; // and here.
should be:
if (i > 0) sql + = " or ";
sql += "(language = ?)";
# 12
thank u very much for ur patience and giving ur suggestions..again i got another error asillegal start of expressions if (i > 0) sql + = " or ";what should i do.....please help me out
# 13
if (i > 0) sql + = " or ";should be:if (i > 0) sql += " or "; // no space between + and =
# 14
now i am getting error over here that issetString(int,java.lang.String) in java.sql.PreparedStatement cannot be applied to (int,java.lang.String[])pStmt.setString(i+1, s3); // jdbc index always start from 1
# 15
pStmt.setString(i+1, s3);should be:pStmt.setString(i+1, s3[i]);
# 16
thank u lot for patience in guiding me...now i am able to get data....but not the exact data..i will send the whole process....please guide me....ur reply would be appreciated
# 17
> thank u lot for patience in guiding me...welcome> but not the exact data..what do you mean by exact data?
# 18
[nobr]i will explain u the whole procedure and the modules, so that u can give me the right solution...
HR Module
**********
i have selct box that various skills , the code is
<html>
<body bgcolor="#c8d8f8">
<script language= "JavaScript" bgcolor="#c8d8f8">
<!--
function one2two() {
m1len = m1.length ;
for ( i=0; i<m1len ; i++){
if (m1.options[i].selected == true ) {
m2len = m2.length;
m2.options[m2len]= new Option(m1.options[i].text);
}
}
for ( i = (m1len -1); i>=0; i--){
if (m1.options[i].selected == true ) {
m1.options[i] = null;
}
}
}
function two2one() {
m2len = m2.length ;
for ( i=0; i<m2len ; i++){
if (m2.options[i].selected == true ) {
m1len = m1.length;
m1.options[m1len]= new Option(m2.options[i].text);
}
}
for ( i=(m2len-1); i>=0; i--) {
if (m2.options[i].selected == true ) {
m2.options[i] = null;
}
}
}
//-->
</script>
<form method="POST" name="theForm" action="searchmain1.jsp">
<table bgcolor="#c8d8f8" border="1" cellpadding="5" cellspacing="2" align="center">
<tr><td align="center">
<select id=menu1 size=10 multiple>
<option>javascript</option>
<option>php</option>
<option>Zeo</option>
<option>asp</option>
<option>jsp</option>
<option>ajax</option>
</select><br />
<p align="center"><input type="button" onClick="one2two()" value=" >> ">
</td><td align="center">
<select NAME="language" multiple>
<option>struts</option>
<option>c</option>
<option>c++</option>
</SELECT>
<p align="center"><input type="button" onClick="two2one()" value=" << " >
<br>select only<input type="checkbox" name="check">
</td></tr></table>
<center><input type="submit" value="search"></center>
</form>
<script language= "JavaScript">
// IMPORTANT: this is the extra bit of code
// shorthand for referring to menus
// must run after document has been created
// you can also change the name of the select menus and
// you would only need to change them in one spot, here
var m1 = document.theForm.menu1;
//var m2 = document.theForm.menu2;
var m2 = document.theForm.language;
</script>
</body>
</html>
**********
the select box also have a check box....
condition 1 ( AND condition)
***********
if the hr select two skills eg: c, c++ ... then clicks the check box and submit
--> it should select only employees having both skills.
if the hr select three skills eg: c, c++, jsp ...then clicks the check box and submit
-->it should select only employees having three skills. etcc...
condition 2( OR condition)
**********
if the hr select the two or three or even one skill and hit submit without clicking the check box
-->it should select employees based on first skill if present or select employees based on second skill or etc like that
<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%--
The taglib directive below imports the JSTL library. If you uncomment it,
you must also add the JSTL library to the project. The Add Library... action
on Libraries node in Projects view can be used to add the JSTL 1.1 library.
--%>
<%--
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
--%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@page import="java.lang.*" %>
<%@page import="java.sql.*" %>
<%@page import="java.io.*" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body bgcolor="#c8d8f8">
<%
//String eid=request.getParameter()
String s3[]=request.getParameterValues("language");
String check[] = request.getParameterValues("check");
int i=0;
int j=0;
String a="";
String ename="";
PreparedStatement pStmt =null;
try
{
Class.forName("com.mysql.jdbc.Driver");
}
catch(ClassNotFoundException ce)
{
out.println(ce);
}
try
{
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test");
//pStmt = con.prepareStatement(" select lang.ename from lang where lang.language like ?" );
if(check == null)
{
String sql = "select lang.ename from lang where ";
for ( i = 0; i < s3.length; i++)
{
if (i > 0) sql += " or ";
sql += "(language = ?)";
}
pStmt = con.prepareStatement(sql);
for ( i = 0; i < s3.length; i++) {
pStmt.setString(i+1, s3[i]);// jdbc index always start from 1
}
}
//}
else
{
pStmt = con.prepareStatement(" select lang.ename from lang where lang.language like ?" );
for ( i = 0; i < s3.length ; i++)
{
if(i < s3.length - 1)
{
a=a + s3[i] + ",";
pStmt.setString(1, a + "%" );
}
else
{
a = a + s3[i] ;
pStmt.setString(1, a + "%" );
}
// pStmt.setString(1, a + "%" );
}
}
%>
<table width="15%" border="1">
<tr>
<td><div align="center">Employees</div></td>
</tr>
<%
ResultSet rs=pStmt.executeQuery();
while (rs.next ())
{
ename = rs.getString("ename");
%>
<tr>
<td><%=ename%></td>
</tr>
<%
}
%>
</table>
<%
}
catch(SQLException e)
{
System.out.println(e.getMessage());
}
%>
<a href="searchmain.jsp"><h4><u>Back</h4></a>
</body>
</html>
please help me,....really need ur help[/nobr]
# 19
well, actually i don't do jsp.
String check[] = request.getParameterValues("check");
i'm not sure but i think there is a method for get 'only one' value from request parameter. i think its :
String check = request.getParameter("check");
if user not check the check box, you will get null.
so when that check variable is null, you change conditional operator in previous query from 'or' to 'and'.
# 20
i have checked it. & its true.
so you only need to modify a little :
...
//String check[] = request.getParameterValues("check");
String check = request.getParameter("check");
String operator = (check == null) ? " or " : " and ";
...
//if (i > 0) sql += " or ";
if (i > 0) sql += operator;
...
# 21
thank u a lot for ur suggestions...
i have applied as u said, its working out...
i will give u one more information ie....
i have stored employees languages in this way ie
c,c++,java -->using ',' operator...
we need to pull the data ie present in between to skills like c++ (present between c and java)
now i am not able to get data present in between the skills.....
i really thank u for the patience that had in guiding.....
i never seen a person like u helping me in this forum...
if u need any more details, i will send u, please ask me....
reply would be appreciated...
# 22
> c,c++,java -->using ',' operator...errr...now i know why you are using like keyword.you can doing extra works to check your result set with like '%xxx%' keyword.if possible, i suggest you to change your design.
# 23
thank u for ur suggestions...should i change to older design....can u give ur suggestions
# 24
Just write a simple query likeSelect * from Student where sno <>' ' where sno is a column with primary keyappend this qry to stringBuffer & concat only column name with or
# 25
reply to J_SHADINATADear Sir/Mam......if it is possible, please work it out and give ur suggestions...it would really helpful.....reply would be appreciated ....thank u a lot
# 26
first approach, using "like" keyword.
however, you need to filter the result because it may return more than you expected. eg:
with keyword '%c%', you also get c++.
second approarch is change your table structure.
note:
your code is very hard to maintain...
you can seperate java code in a .class file. so your jsp will much cleaner.
if you can not avoid using java code (<% ... %>) inside jsp,
put them in one block.
don't let them spoil in whole jsp page.
whenever you are using a "resouce", always remeber to close them.
other application may need to access that resouce. in your case, it is database connection.
please wait, i will post an example of better jsp structure.
# 27
<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%--
The taglib directive below imports the JSTL library. If you uncomment it,
you must also add the JSTL library to the project. The Add Library... action
on Libraries node in Projects view can be used to add the JSTL 1.1 library.
--%>
<%--
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
--%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@page import="java.lang.*, java.sql.*, java.io.*, java.sql.*, java.util.*" %>
<%
// most j2ee server support jndi & pooled data source
// you should use it instead registering driver here
try {
Class.forName("com.mysql.jdbc.Driver");
} catch(ClassNotFoundException ce) {
out.println(ce);
}
String s3[]=request.getParameterValues("language");
String check = request.getParameter("check");
String operator = (check == null) ? "or " : " and ";
String sql = "select lang.ename, lang.language from lang";
if (s3.length > 0) sql += " where ";
for (int i = 0; i < s3.length; i++) {
if (i > 0) sql += operator;
sql += "(language like ?)";
}
// List<String> employeeNames = new ArrayList<String>(); // jdk1.5 or above
List employeeNames = new ArrayList(); // jdk1.4 compability
Connection con = null;
PreparedStatement pStmt = null;
ResultSet rs = null;
try {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test");
pStmt = con.prepareStatement(sql);
for (int i = 0; i < s3.length; i++) pSmt.setString(i+1, "%" + s3[i] + "%");
rs = pStmt.executeQuery();
while (rs.next()) {
// here is code for extra filtering...
// but i suggest you to change your table structure
// if (match)
employeeNames.add(rs.getString(1));
}
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (rs != null) try { rs.close(); } catch (SQLException ex) { };
if (pStmt != null) try { pStmt.close(); } catch (SQLException ex) { };
if (con != null) try { con.close(); } catch (SQLException ex) { };
}
%>
<html>
<head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></head>
<body bgcolor="#c8d8f8">
<table width="15%" border="1">
<tr>
<td><div align="center">Employees</div></td>
</tr>
<%
// tag lib may usefull here
for (Iterator i = employeeNames.iterator(); i.hasNext(); ) {
String ename = (String) i.next();
out.println("<tr>");
out.println("<td>" + ename + "</td>");
out.println("</tr>");
}
%>
</table>
<a href="searchmain.jsp"><h4><u>Back</h4></a>
</body>
</html>
# 28
Thank u a lot for ur help in guiding me
Hats off to u Shadinata
i will surely follow the approaches that you mentioned without fail...
Your suggestions would really help me to built a better Jsp ...
surely i will follow the steps that u mentioned and will get back to you without fail...
thank u a lot....
# 29
Reply to J_shadinata
thats really great, no more words to praise....
really it worked out and i completed my task and i have to show this to my boss....
In the "AND" operator condition, it proceeds well ...But when we give c,struts etc...
it also brings employees having javascript, struts.
is there any solution for this....
Once again i thank u very much for ur suggestions and various approaches ....
reply would be appreciated...please
# 30
..But when we give c,struts etc...
it also brings employees having javascript, struts.
is there any solution for this....
that's why i put commented line there:
// if (match)
you should implement your own filtering.
the problem is 'javascript' also have 'c' character (javascript).
you also will get employee with c++
in the query language, i put 'lang.language' on purpose.
(select lang.ename, lang.language from ...)
obtain that column & do your own filtering.
d i completed my task and i have to show this to my boss....
oh my... i thought you are in learning stage...
i'm affraid this is not a stable application...
i hope your boss never click the button without selecting any language ;P
# 31
Thank u for your reply....
ok, i will do the required filtering over there...
i need one more help, if it possible, please give ur suggestions...
i do have a login page in the appication...
i have done all the steps to check the users in the database..
and i have to implement J_Security_Check with login page...
can u give ur suggestions....
one more question, if i click the button without selecting any languages, its showing "NULL POINTER EXCEPTION"
how can i overcome that...
reply would be appreciated ...please
# 32
> and i have to implement J_Security_Check with login page...
sorry, i don't know what is J_Security_Check
> one more question, if i click the button without selecting any
> languages, its showing "NULL POINTER EXCEPTION"
i can not check it here. actually i don't have good knowledge about jsp.
i suspect it cause by 's3 == null'. you can do checking before using s3.
if (s3 != null) {
// do the work...
} else {
// don't do anything with s3
}
> how can i overcome that...
need lot of work... your current design is poor. you can do repairing (debuging)
over and over. even if you able to manage this problem, it
will another arise. it's a never ending nightmare for beginner developer.
in large scale project, design is more importand than coding (although
yours is not large, but usually real-world application will grow).
so, re-design now!
# 33
Reply to J_shadinata
Thank you for your reply...
ok i will change to if(s3!=null)...
once again thank you for your wonderful suggestion that made my work better....
thank you for patience in guiding me....
Anymore in the future , if you see any queries from me , please do reply....i am expecting....
Reply would be appreciated
