通过表单提交查询条件,文本框内容什么都不填写的时候就查询所有的,然后文本框输入什么条件就查询相应的内容!我就是不会控制那个根据不同的提交条件变换SQl语句,然后查询以表格显示!!望指点~~~(javabean+jsp+mysql)用到的代码如下——
message.java
package superet;import java.sql.*;
import java.util.*;public class message { private String goodsID,supplierID,gname,barCode,metricUnit,kind,birthPlace;//表的相应信息
private double unitCost;
private double checkMoneyB,checkMoneyS;//单价大于、单价小于查看(获取从表单提交的信息)
private String sql;//储存sql语句的
private boolean flag;
public message() {
}
public ArrayList<message> checkMessage()//返回查询结果的方法
{
message cm=null;
ArrayList<message> list=new ArrayList<message>();
try {
//这里控制相应的sql语句
login check=new login();//此类里面有连接数据库和查询方法
System.out.println(sql);
ResultSet rs=check.executeQuery(sql);
while (rs.next())
{
cm=new message();
cm.setGoodsID(rs.getString(1));
cm.setSupplierID(rs.getString(2));
cm.setGname(rs.getString(3));
cm.setBarCode(rs.getString(4));
cm.setMetricUnit(rs.getString(5));
cm.setKind(rs.getString(6));
cm.setBirthPlace(rs.getString(7));
cm.setUnitCost(rs.getDouble(8));
list.add(cm);
}
rs.close();
}catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public String getBarCode() {
return barCode;
} public void setBarCode(String barCode) {
this.barCode = barCode;
} public String getBirthPlace() {
return birthPlace;
} public void setBirthPlace(String birthPlace) {
this.birthPlace = birthPlace;
}
public String getGname() {
return gname;
}
public void setGname(String gname) {
this.gname = gname;
}
public String getGoodsID() {
return goodsID;
} public void setGoodsID(String goodsID) {
this.goodsID = goodsID;
} public String getKind() {
return kind;
} public void setKind(String kind) {
this.kind = kind;
} public String getMetricUnit() {
return metricUnit;
} public void setMetricUnit(String metricUnit) {
this.metricUnit = metricUnit;
} public String getSupplierID() {
return supplierID;
} public void setSupplierID(String supplierID) {
this.supplierID = supplierID;
} public double getUnitCost() {
return unitCost;
} public void setUnitCost(double unitCost) {
this.unitCost = unitCost;
} public double getCheckMoneyB() {
return checkMoneyB;
} public void setCheckMoneyB(double checkMoneyB) {
this.checkMoneyB = checkMoneyB;
} public double getCheckMoneyS() {
return checkMoneyS;
} public void setCheckMoneyS(double checkMoneyS) {
this.checkMoneyS = checkMoneyS;
} public void setFlag(boolean flag) {
this.flag = flag;
}
这个是提交的表单样式message.jsp——<%@ page language="java" contentType="text/html; charset=GBK"
pageEncoding="GBK"%>
<%request.setCharacterEncoding("gb2312"); %>
<!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=ISO-8859-1">
<title>message.jsp</title>
<%--信息管理模块主页面 --%></head>
<body>
<table width="634" height="441" border="0" align="center" background="bg02.jpg">
<tr>
<td width="364" height="118">
<%--根据不同条件查询商品信息 --%>
<div class="check" style=" height:100pt;">
<p><font size="+3" ><b>商品信息查询</b></font></p>
<p>
<form action="checkgoods.jsp" method="post" name="f1">
<b>查询所有商品信息:</b><input type="hidden" name="all" value="all"/><br>
<b>按商品名称查询:</b><input type="text" name="gname" /><br>
<b>按商品类别查询:</b><select name="kind">
<option value="饮品" selected="selected">饮品</option>
<option value="日用品">日用品</option>
<option value="食品">食品</option>
</select><br>
<b>按商品的单价查询:</b>
<p><b>大于<input type="text" name="checkMoneyB" />元</b><br>
<b>小于<input type="text" name="checkMoneyS" />元</b><br>
<input type="submit" name="submit" value="查询"/>
<input type="reset" name="reset" value="修改"/>
</form>
</p>
</div>
</td>
<td width="260">
<div class="add" style="height:30pt; width:100pt; margin:220pt 30pt 50pt 40pt;" >
<%--点击进入添加商品页面--%>
<a href="goods.jsp"><font size="+2" style=" color:#006600; font-weight:bolder;">添加商品</font></a><br>
<a href="superet.jsp"><font size="+2" style=" color:#006600; font-weight:bolder;">返回主页面</font></a>
</div>
</td>
</tr>
</table>
</body>
</html>
这个是表单信息提交处理页面——
checkgoods.jsp
<%@ page language="java" contentType="text/html; charset=gb2312"
pageEncoding="gb2312"%>
<%request.setCharacterEncoding("gb2312"); %>
<%@page import="java.util.ArrayList"%>
<%@page import="superet.message"%>
<!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=ISO-8859-1">
<title>check.jsp</title>
<jsp:useBean id="check" scope="request" class="superet.message">
<jsp:setProperty name="check" property="*"/>
</jsp:useBean>
</head>
<body>
<%--处理商品查询条件的页面 --%>
<% try
{ ArrayList list=check.checkMessage();
int length=list.size();
if(length==0)
{
%>
<script type="text/javascript">
alter("查无此商品信息!");
</script>
<% }
else
{%>
<table width="634" border="0">
<tr>
<th>商品编号</th>
<th>供货商号;</th>
<th>名称</th>
<th>条形码</th>
<th>计量单位</th>
<th>类别</th>
<th>产地</th>
<th>单价</th>
</tr>
<%
for(int i=0;i<length;i++)
{
message m=(message)list.get(i);
%>
<tr>
<td><%=m.getGoodsID() %></td>
<td><%=m.getSupplierID() %></td>
<td><%=m.getGname()%></td>
<td><%=m.getBarCode() %></td>
<td><%=m.getMetricUnit() %></td>
<td><%=m.getKind() %></td>
<td><%=m.getBirthPlace() %></td>
<td><%=m.getUnitCost() %></td>
<td>
<a href='delgoods.jsp'?goodsID=<%=m.getGoodsID() %>>删除</a>
<a href='modify.jsp'?goodsID=<%=m.getGoodsID() %>>修改</a> </td>
<%System.out.print(m.getGoodsID()); %>
</tr>
<%
}
%>
</table>
<% }
}catch(NullPointerException e)
{
e.printStackTrace();
}
%>
<a href="message.jsp">返回信息管理主页面</a>
<a href="stock.jsp">查看商品库存</a>
</body>
</html>
message.java
package superet;import java.sql.*;
import java.util.*;public class message { private String goodsID,supplierID,gname,barCode,metricUnit,kind,birthPlace;//表的相应信息
private double unitCost;
private double checkMoneyB,checkMoneyS;//单价大于、单价小于查看(获取从表单提交的信息)
private String sql;//储存sql语句的
private boolean flag;
public message() {
}
public ArrayList<message> checkMessage()//返回查询结果的方法
{
message cm=null;
ArrayList<message> list=new ArrayList<message>();
try {
//这里控制相应的sql语句
login check=new login();//此类里面有连接数据库和查询方法
System.out.println(sql);
ResultSet rs=check.executeQuery(sql);
while (rs.next())
{
cm=new message();
cm.setGoodsID(rs.getString(1));
cm.setSupplierID(rs.getString(2));
cm.setGname(rs.getString(3));
cm.setBarCode(rs.getString(4));
cm.setMetricUnit(rs.getString(5));
cm.setKind(rs.getString(6));
cm.setBirthPlace(rs.getString(7));
cm.setUnitCost(rs.getDouble(8));
list.add(cm);
}
rs.close();
}catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public String getBarCode() {
return barCode;
} public void setBarCode(String barCode) {
this.barCode = barCode;
} public String getBirthPlace() {
return birthPlace;
} public void setBirthPlace(String birthPlace) {
this.birthPlace = birthPlace;
}
public String getGname() {
return gname;
}
public void setGname(String gname) {
this.gname = gname;
}
public String getGoodsID() {
return goodsID;
} public void setGoodsID(String goodsID) {
this.goodsID = goodsID;
} public String getKind() {
return kind;
} public void setKind(String kind) {
this.kind = kind;
} public String getMetricUnit() {
return metricUnit;
} public void setMetricUnit(String metricUnit) {
this.metricUnit = metricUnit;
} public String getSupplierID() {
return supplierID;
} public void setSupplierID(String supplierID) {
this.supplierID = supplierID;
} public double getUnitCost() {
return unitCost;
} public void setUnitCost(double unitCost) {
this.unitCost = unitCost;
} public double getCheckMoneyB() {
return checkMoneyB;
} public void setCheckMoneyB(double checkMoneyB) {
this.checkMoneyB = checkMoneyB;
} public double getCheckMoneyS() {
return checkMoneyS;
} public void setCheckMoneyS(double checkMoneyS) {
this.checkMoneyS = checkMoneyS;
} public void setFlag(boolean flag) {
this.flag = flag;
}
这个是提交的表单样式message.jsp——<%@ page language="java" contentType="text/html; charset=GBK"
pageEncoding="GBK"%>
<%request.setCharacterEncoding("gb2312"); %>
<!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=ISO-8859-1">
<title>message.jsp</title>
<%--信息管理模块主页面 --%></head>
<body>
<table width="634" height="441" border="0" align="center" background="bg02.jpg">
<tr>
<td width="364" height="118">
<%--根据不同条件查询商品信息 --%>
<div class="check" style=" height:100pt;">
<p><font size="+3" ><b>商品信息查询</b></font></p>
<p>
<form action="checkgoods.jsp" method="post" name="f1">
<b>查询所有商品信息:</b><input type="hidden" name="all" value="all"/><br>
<b>按商品名称查询:</b><input type="text" name="gname" /><br>
<b>按商品类别查询:</b><select name="kind">
<option value="饮品" selected="selected">饮品</option>
<option value="日用品">日用品</option>
<option value="食品">食品</option>
</select><br>
<b>按商品的单价查询:</b>
<p><b>大于<input type="text" name="checkMoneyB" />元</b><br>
<b>小于<input type="text" name="checkMoneyS" />元</b><br>
<input type="submit" name="submit" value="查询"/>
<input type="reset" name="reset" value="修改"/>
</form>
</p>
</div>
</td>
<td width="260">
<div class="add" style="height:30pt; width:100pt; margin:220pt 30pt 50pt 40pt;" >
<%--点击进入添加商品页面--%>
<a href="goods.jsp"><font size="+2" style=" color:#006600; font-weight:bolder;">添加商品</font></a><br>
<a href="superet.jsp"><font size="+2" style=" color:#006600; font-weight:bolder;">返回主页面</font></a>
</div>
</td>
</tr>
</table>
</body>
</html>
这个是表单信息提交处理页面——
checkgoods.jsp
<%@ page language="java" contentType="text/html; charset=gb2312"
pageEncoding="gb2312"%>
<%request.setCharacterEncoding("gb2312"); %>
<%@page import="java.util.ArrayList"%>
<%@page import="superet.message"%>
<!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=ISO-8859-1">
<title>check.jsp</title>
<jsp:useBean id="check" scope="request" class="superet.message">
<jsp:setProperty name="check" property="*"/>
</jsp:useBean>
</head>
<body>
<%--处理商品查询条件的页面 --%>
<% try
{ ArrayList list=check.checkMessage();
int length=list.size();
if(length==0)
{
%>
<script type="text/javascript">
alter("查无此商品信息!");
</script>
<% }
else
{%>
<table width="634" border="0">
<tr>
<th>商品编号</th>
<th>供货商号;</th>
<th>名称</th>
<th>条形码</th>
<th>计量单位</th>
<th>类别</th>
<th>产地</th>
<th>单价</th>
</tr>
<%
for(int i=0;i<length;i++)
{
message m=(message)list.get(i);
%>
<tr>
<td><%=m.getGoodsID() %></td>
<td><%=m.getSupplierID() %></td>
<td><%=m.getGname()%></td>
<td><%=m.getBarCode() %></td>
<td><%=m.getMetricUnit() %></td>
<td><%=m.getKind() %></td>
<td><%=m.getBirthPlace() %></td>
<td><%=m.getUnitCost() %></td>
<td>
<a href='delgoods.jsp'?goodsID=<%=m.getGoodsID() %>>删除</a>
<a href='modify.jsp'?goodsID=<%=m.getGoodsID() %>>修改</a> </td>
<%System.out.print(m.getGoodsID()); %>
</tr>
<%
}
%>
</table>
<% }
}catch(NullPointerException e)
{
e.printStackTrace();
}
%>
<a href="message.jsp">返回信息管理主页面</a>
<a href="stock.jsp">查看商品库存</a>
</body>
</html>
解决方案 »
- spring PropertyFilter 什么用
- 大家做表单提交时用什么js组件来验证输入的符不符合要求?
- 在JSP里调用另外一个页面传入一个参数,怎么取得返回值
- JSP标签刷新问题
- struts 如何在struts-config.xml里为某一个action配置默认的forward
- Value can not be converted to requested type错误!请教如何解决?
- 请问:在在jsp中如何连接Access数据库?
- 那位高手能告诉我“上传文件按时间生成文件名去掉时间分隔符的全代码”
- 2002年即将过去,请大家抽点时间把可以结但还未结的2002年即将过去,请大家抽点时间把可以结但还未结的贴子都结了,不要把今年的事留到明
- 为什么没法连上mysql?
- 求一个jsp实现bt和迅雷下载的源码
- 高手帮忙看看。
if(gname!=null&&!"".equal(gname)){
sql = sql +" and gname="+gname;
}
....其他的条件类似上面的判断
这样就能实现都不输查全部,输入哪个条件就按照哪一个查询
String kind= request.getParameter("kind");
String checkMoneyB = request.getParameter("checkMoneyB");
String checkMoneyS = request.getParameter("checkMoneyS");StringBuilder sb = new StringBuilder();
sb.append("select * from 商品表 where 1=1");
if (gname.trim().length == 0) {
sb.append("and gname like '%" + gname + "%'");
}
if (kind.trim().length == 0) {
sb.append("and kindlike '%" + kind+ "%'");
}
if (checkMoneyB.trim().length == 0) {
sb.append("and checkMoneyB > " + checkMoneyB);
}
if (checkMoneyS.trim().length == 0) {
sb.append("and checkMoneyS < " + checkMoneyS);
}
我试试了,可是打印查询语句,出现的效果不是追加,而是查不到。就是用and链接的条件都有,就查不到了
if(gname!=null&&!"".equal(gname)){
sql = sql +" and gname="+gname;
}
就像上面如果没有1=1就会变成 sql = "select * from and gname="+gname
嗯嗯,可是打印出来的语句是——比如我要查全部信息,就什么都不填写对吧?可是打印出来的语句是
select * from Goods where 1=1 and gName like '%null%' and kind like '%null%' and metricUnit >%0.0% and metricUnit <%0.0%肯定查不到吖
这个方法带一个message 对象public ArrayList<message> checkMessage(Message message)
if(message!=null){
if(message.get***){
sql+=" and ***="+message.get***();
}
}
主要就是把你值都传过去之后进行拼串,拼成相应的sql查询条件就行了, 你试着先拼出来一个,拼出之后再考虑优化的问题!!
我试了,可是不管传过来的是什么值,拼串涉及的每个列值,都存在。就是我提交的是所有的都不填的话,提交的应该是空值吧?我已经设置了空值时候它不拼接,可是它还是会拼接所有的。比如先查最简单是就是什么条件都不输入。提交的都为空值,那应该都不拼接对吧?就是select * from Goods where 1=1 。可是它实际出来的是——select * from Goods where 1=1 and gName like '%null%' and kind like '%null%' and metricUnit >%0.0% and metricUnit <%0.0% 一直查不出来吖~~谢谢你,帮我解决一下吧~~
用IF判断来修改SQL语句再执行
我晕拉,你不知道用空判断一下,如果是空你后面的就不会加上去了
if(gname!=null&amp;amp;amp;&amp;amp;amp;!"".equal(gname)){
如果已经这样判断了,你的值是空你还能进这个if吗?进不了那你的sql语句怎么会加进去
sql += "and kind like '%"+kind+"%'";
构成一条完整的sql查询语句。判空的如6楼所写的。只是最后调用一下
sb.toString();
就行了
如这样配置
<select id="qlCount" parameterClass="com.zwdt.core.PageModel" resultClass="java.lang.Integer">
select count(*) from ql_inf_inter
<dynamic prepend="where">
<isNotEmpty prepend="AND" property="levelLB">
QL_KIND like '%'||#levelLB#||'%'
</isNotEmpty>
<isNotEmpty prepend="AND" property="ywml">
TRANSACT_DEPNAME like '%'||#ywml#||'%'
</isNotEmpty>
<isNotEmpty prepend="AND" property="emailID">
QL_REG_ID like '%'||#emailID#||'%'
</isNotEmpty>
<isNotEmpty prepend="AND" property="content">
QL_NAME like '%'||#content#||'%'
</isNotEmpty>
<isNotEmpty prepend="AND" property="region">
EXT_DQBM like '%'||#region#||'%'
</isNotEmpty>
</dynamic>
</select>
%号是用在模糊查询中的你这个goodsID应该要在引号内,要不然找不到变量
<a href='delgoods.jsp'?goodsID=<%=m.getGoodsID() %>>删除</a>
<a href='modify.jsp'?goodsID=<%=m.getGoodsID() %>>修改</a>
* 根据根据userPid、交易状态、交易时间、交易流水号进行查询多个条件查询根据页面的下拉列表获取的值。可以不选查询也可以都选查询随便选择。
*/
public List searchSucc(String userPid, int tradeStatus, String tradeTime,
String tradeNumber) {
Criteria c = sf.getCurrentSession().createCriteria(Shopnote.class);// 初始化Criteria
if(userPid!=null&&
!userPid.equals("")){
c.add(Restrictions.like("userPid",userPid,MatchMode.ANYWHERE));//根据userPid查询
}
if(tradeStatus>-1){
c.add(Restrictions.sqlRestriction("tradeStatus ="+tradeStatus));//根据交易状态查询
}
if(tradeTime!=null&&
!tradeTime.equals("")){
c.add(Restrictions.like("tradeTime",tradeTime,MatchMode.ANYWHERE));//根据交易时间查询
}
if(tradeNumber!=null&&
!tradeNumber.equals("")){
c.add(Restrictions.like("tradeNumber",tradeNumber,MatchMode.ANYWHERE));//根据交易流水号查询
}
return c.list();
}