情况是这样的,一个模糊查询界面将YHDLM、FLBH、LXRMC这3个值传到search.java
search.java主要代码:
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException { response.setContentType("text/html;charset=gb2312");
PrintWriter out = response.getWriter();
request.setCharacterEncoding("gb2312");
String LXRMC = request.getParameter("LXRMC").trim();
int FLBH = Integer.parseInt(request.getParameter("FLBH"));
request.setAttribute("LXRMC", request.getParameter("LXRMC"));
HttpSession session = request.getSession();
String YHDLM = ((beans.UserInf)session.getAttribute("admin")).getYHDLM();
ArrayList list = new DBConn().search(FLBH,LXRMC,YHDLM);
String sPage=request.getParameter("page");
int recordCount=list.size();
int pageCount=1;
int page=strToInt(sPage,1);
int pageSize=5;
int paend=page*pageSize;
List list1=new ArrayList();
for(int i=paend-5;i<paend;i++)
{
try{
beans.UserInf bean=(beans.UserInf)(list.get(i));//
list1.add(bean);
} catch (Exception e) {
}
}
request.setAttribute("recordCount", recordCount);
request.setAttribute("pageCount",(recordCount-1)/pageSize+1);
request.setAttribute("page", page);
request.setAttribute("rs", list1);
request.getRequestDispatcher("searchs.jsp").forward(request, response);//
out.flush();
out.close();
}
这上面应该没问题,然后调用类DBConn中的search方法:
public ArrayList search(int FLBH,String LXRMC,String YHDLM )
{
ArrayList<UserInf> a = new ArrayList<UserInf>();
try {
String sql="select * from TXLB,TXLFLB where TXLB.YHDLM='"+YHDLM+"'and TXLFLB.FLBH=TXLB.FLBH";
if(LXRMC!="")
{ sql+= "and TXLB.LXRMC like '%"+LXRMC+"%'";
if(FLBH!=1)
{sql+="and TXLB.FLBH='"+FLBH+"'";}
}
else
{ if(FLBH!=1)
{ sql+= "and TXLB.FLBH='"+FLBH+"'";}
}
stmt=conn.prepareStatement(sql);
rs = stmt.executeQuery();
while(rs.next())
{
UserInf u = new UserInf();
u.setID(rs.getInt("ID"));
u.setYHDLM(rs.getString("YHDLM"));
u.setLXRMC(rs.getString("LXRMC"));
u.setFLBH(rs.getInt("FLBH"));
u.setFLMC(rs.getString("FLMC"));
u.setDZ(rs.getString("DZ"));
u.setLXRSJ(rs.getString("LXRSJ"));
u.setLXRDW(rs.getString("LXRDW"));
u.setLXRBM(rs.getString("LXRBM"));
u.setEMAIL(rs.getString("EMAIL"));
a.add(u);
}
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return a;
}
这个是模糊查询,两个限制条件都为空时,查询全部记录,这个可以实现,但是一旦有一个限制条件后,或者2个限制条件,然后就报错了,
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TXLB.LXRMC like '%n%'' at line 1再贴另外一个search方法,这个可以实现整个模糊查询的功能
public ArrayList search(int FLBH,String LXRMC,String YHDLM )
{
ArrayList<UserInf> a = new ArrayList<UserInf>();
try {
String sql="select * from TXLB where YHDLM='"+YHDLM+"'";
if(LXRMC!="")
{ sql+= "and LXRMC like '%"+LXRMC+"%'";
if(FLBH!=1)
{sql+="and FLBH='"+FLBH+"'";}
}
else
{ if(FLBH!=1)
{ sql+= "and FLBH='"+FLBH+"'";}
}
stmt=conn.prepareStatement(sql);
rs = stmt.executeQuery();
while(rs.next())
{
UserInf u = new UserInf();
u.setID(rs.getInt("ID"));
u.setYHDLM(rs.getString("YHDLM"));
u.setLXRMC(rs.getString("LXRMC"));
u.setFLBH(rs.getInt("FLBH"));
//u.setFLMC(rs.getString("FLMC"));
u.setDZ(rs.getString("DZ"));
u.setLXRSJ(rs.getString("LXRSJ"));
u.setLXRDW(rs.getString("LXRDW"));
u.setLXRBM(rs.getString("LXRBM"));
u.setEMAIL(rs.getString("EMAIL"));
a.add(u);
}
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return a;
}
然后我就搞不懂了,关键现在是2个表txlb和txlflb,上面报错那个是想希望2个表连接查询,来得到flmc
而下面成功的那个就只有查询表txlb,然后没办法得到flmc。
希望各位大大们 能帮我解决下这个问题
然后这个是查询的jsp页面,显示内容的部分
<c:forEach items="${requestScope.rs}" var="s" varStatus="status" >
<tr>
<td> <input type="checkbox" name="checkBoxName" value="${s.ID}"></td>
<td>${s.YHDLM } </td>
<td>${s.LXRMC } </td>
<td>${s.FLMC} </td>
<td>${s.DZ } </td>
<td>${s.LXRSJ } </td>
<td>${s.LXRDW } </td>
<td>${s.LXRBM } </td>
<td>${s.EMAIL } </td>
<td><a href="edits.do?ID=${s.ID}">修改</a></td>
search.java主要代码:
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException { response.setContentType("text/html;charset=gb2312");
PrintWriter out = response.getWriter();
request.setCharacterEncoding("gb2312");
String LXRMC = request.getParameter("LXRMC").trim();
int FLBH = Integer.parseInt(request.getParameter("FLBH"));
request.setAttribute("LXRMC", request.getParameter("LXRMC"));
HttpSession session = request.getSession();
String YHDLM = ((beans.UserInf)session.getAttribute("admin")).getYHDLM();
ArrayList list = new DBConn().search(FLBH,LXRMC,YHDLM);
String sPage=request.getParameter("page");
int recordCount=list.size();
int pageCount=1;
int page=strToInt(sPage,1);
int pageSize=5;
int paend=page*pageSize;
List list1=new ArrayList();
for(int i=paend-5;i<paend;i++)
{
try{
beans.UserInf bean=(beans.UserInf)(list.get(i));//
list1.add(bean);
} catch (Exception e) {
}
}
request.setAttribute("recordCount", recordCount);
request.setAttribute("pageCount",(recordCount-1)/pageSize+1);
request.setAttribute("page", page);
request.setAttribute("rs", list1);
request.getRequestDispatcher("searchs.jsp").forward(request, response);//
out.flush();
out.close();
}
这上面应该没问题,然后调用类DBConn中的search方法:
public ArrayList search(int FLBH,String LXRMC,String YHDLM )
{
ArrayList<UserInf> a = new ArrayList<UserInf>();
try {
String sql="select * from TXLB,TXLFLB where TXLB.YHDLM='"+YHDLM+"'and TXLFLB.FLBH=TXLB.FLBH";
if(LXRMC!="")
{ sql+= "and TXLB.LXRMC like '%"+LXRMC+"%'";
if(FLBH!=1)
{sql+="and TXLB.FLBH='"+FLBH+"'";}
}
else
{ if(FLBH!=1)
{ sql+= "and TXLB.FLBH='"+FLBH+"'";}
}
stmt=conn.prepareStatement(sql);
rs = stmt.executeQuery();
while(rs.next())
{
UserInf u = new UserInf();
u.setID(rs.getInt("ID"));
u.setYHDLM(rs.getString("YHDLM"));
u.setLXRMC(rs.getString("LXRMC"));
u.setFLBH(rs.getInt("FLBH"));
u.setFLMC(rs.getString("FLMC"));
u.setDZ(rs.getString("DZ"));
u.setLXRSJ(rs.getString("LXRSJ"));
u.setLXRDW(rs.getString("LXRDW"));
u.setLXRBM(rs.getString("LXRBM"));
u.setEMAIL(rs.getString("EMAIL"));
a.add(u);
}
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return a;
}
这个是模糊查询,两个限制条件都为空时,查询全部记录,这个可以实现,但是一旦有一个限制条件后,或者2个限制条件,然后就报错了,
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TXLB.LXRMC like '%n%'' at line 1再贴另外一个search方法,这个可以实现整个模糊查询的功能
public ArrayList search(int FLBH,String LXRMC,String YHDLM )
{
ArrayList<UserInf> a = new ArrayList<UserInf>();
try {
String sql="select * from TXLB where YHDLM='"+YHDLM+"'";
if(LXRMC!="")
{ sql+= "and LXRMC like '%"+LXRMC+"%'";
if(FLBH!=1)
{sql+="and FLBH='"+FLBH+"'";}
}
else
{ if(FLBH!=1)
{ sql+= "and FLBH='"+FLBH+"'";}
}
stmt=conn.prepareStatement(sql);
rs = stmt.executeQuery();
while(rs.next())
{
UserInf u = new UserInf();
u.setID(rs.getInt("ID"));
u.setYHDLM(rs.getString("YHDLM"));
u.setLXRMC(rs.getString("LXRMC"));
u.setFLBH(rs.getInt("FLBH"));
//u.setFLMC(rs.getString("FLMC"));
u.setDZ(rs.getString("DZ"));
u.setLXRSJ(rs.getString("LXRSJ"));
u.setLXRDW(rs.getString("LXRDW"));
u.setLXRBM(rs.getString("LXRBM"));
u.setEMAIL(rs.getString("EMAIL"));
a.add(u);
}
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return a;
}
然后我就搞不懂了,关键现在是2个表txlb和txlflb,上面报错那个是想希望2个表连接查询,来得到flmc
而下面成功的那个就只有查询表txlb,然后没办法得到flmc。
希望各位大大们 能帮我解决下这个问题
然后这个是查询的jsp页面,显示内容的部分
<c:forEach items="${requestScope.rs}" var="s" varStatus="status" >
<tr>
<td> <input type="checkbox" name="checkBoxName" value="${s.ID}"></td>
<td>${s.YHDLM } </td>
<td>${s.LXRMC } </td>
<td>${s.FLMC} </td>
<td>${s.DZ } </td>
<td>${s.LXRSJ } </td>
<td>${s.LXRDW } </td>
<td>${s.LXRBM } </td>
<td>${s.EMAIL } </td>
<td><a href="edits.do?ID=${s.ID}">修改</a></td>
{ sql+= "and TXLB.LXRMC like '%"+LXRMC+"%'";
if(FLBH!=1){
sql+="and TXLB.FLBH='"+FLBH+"'";//TXLB.FLBH是int型sql+="and TXLB.FLBH="+FLBH;
}
}
else
{ if(FLBH!=1)
{ sql+= "and TXLB.FLBH='"+FLBH+"'";}//TXLB.FLBH是int型sql+="and TXLB.FLBH="+FLBH;
}
System.out.println("sql="+sql);
应该是组装sql的时候出错了,把sql打印出来,看看是不是有错误,可以放到工具中运行下看看
问题找到了的确是sql句出问题了,但不是int型的问题,感谢你提醒我用
System.out.println("sql="+sql);打印sql语句,我才发现
这几个sql+= "and TXLB.LXRMC like '%"+LXRMC+"%'"; and前都少个空格
sql+= " and TXLB.LXRMC like '%"+LXRMC+"%'";