javaweb之模糊查询

模糊查询，主要通过sql语句来进行查询

一.dao层

加入模糊查询的方法

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//import com.mysql.cj.protocol.ResultBuilder;

import Bean.Course;
import util.DBUtil;

public class daoCourse {

    public boolean add(Course n)
    {
        boolean f=false;
        int a=0;
        String sql="insert submitcourse(classname,teachername,place) values(?,?,?)";
        try {
             Connection conn=DBUtil.getConnection();
             PreparedStatement pstmt=conn.prepareStatement(sql);
             pstmt.setString(1,n.getClassname());
             pstmt.setString(2,n.getTeachername());
             pstmt.setString(3,n.getPlace());
              a=pstmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        if(a>0) f=true;
        return f;
    }
    public boolean delete(Course n) {
    boolean f=false;
    int a=0;
    String sql="delete from submitcourse where teachername=?";
    try {
         Connection conn=DBUtil.getConnection();
         PreparedStatement pstmt=conn.prepareStatement(sql);        
         pstmt.setString(1,n.getTeachername());
          a=pstmt.executeUpdate();
          System.out.println("fuck you");
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    if(a>0) f=true;
    return f;
}
    public List<Course> list(){
        String sql="select * from submitcourse";
        List<Course> list=new ArrayList<>();
         ResultSet rs=null;
        Course ns=new Course();
        try {
             Connection conn=DBUtil.getConnection();
             PreparedStatement pstmt=conn.prepareStatement(sql);        
            // pstmt.setString(1,n.getTeachername());
             rs=pstmt.executeQuery();
             while(rs.next())
             {
                 ns=new Course(rs.getString(1),rs.getString(2),rs.getString(3));
                 list.add(ns);
             }
             //System.out.println(rs);
             System.out.println(rs);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }
    public boolean update(Course n,String old_teachername){
        boolean f=false;
        int a=0;
        String sql = "update submitcourse set classname='"+n.getClassname()+"',teachername='"+n.getTeachername()+"',place='"+n.getPlace()+"' where teachername='"+old_teachername+"'";
        try {
             Connection conn=DBUtil.getConnection();
             PreparedStatement pstmt=conn.prepareStatement(sql);             
              a=pstmt.executeUpdate();
              System.out.println(a);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        if(a>0) f=true;
        
        return f;
        
    }
    public Course queryByTeachername(String teachername) {
        String sql="select * from submitcourse where teachername=?";
        Course n=new Course();
        try {
             ResultSet rs=null;
             Connection conn=DBUtil.getConnection();
             PreparedStatement pstmt=conn.prepareStatement(sql);        
             pstmt.setString(1,teachername);
             rs=pstmt.executeQuery();
                while(rs.next()) { 
                n=new Course(rs.getString(1), rs.getString(2),rs.getString(3));
                }
              System.out.println("fuck you");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }    
        return n ;
        
    }
    public List<Course> searchByTeachername(String teachername){//模糊查询
        List<Course> ns=new ArrayList<Course>();
        String sql="select * from submitcourse where teachername like ?";
        ResultSet rs=null;

         try {
             Connection conn=DBUtil.getConnection();
            PreparedStatement pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, "%"+teachername+"%"); 
            rs=pstmt.executeQuery();
            while(rs.next())
            {
                Course n=new Course(rs.getString(1),rs.getString(2),rs.getString(3));
                ns.add(n);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }    
        return ns;
    }
}

二.servlet层

package servlet;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import Bean.Course;
import dao.daoCourse;

/**
 * Servlet implementation class course_servlet
 */
@WebServlet("/course_servlet")
public class course_servlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       daoCourse gooddao=new daoCourse();
    /**
     * @see HttpServlet#HttpServlet()
     */
    public course_servlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doPost(request, response);
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        String method=request.getParameter("method");
        String keyword=request.getParameter("keyword");//这是你要搜索的关键字
        if("add".equals(method))
        {
        String teachername=request.getParameter("teachername");
        String classname=request.getParameter("classname");
        String place=request.getParameter("place");
        Course n=new Course(classname,teachername,place);
        gooddao.add(n);
        request.setAttribute("message", "添加成功");
        request.getRequestDispatcher("NewFile2.jsp").forward(request, response);
        }
        
        else if("delete".equals(method)) {
            String teachername=request.getParameter("teachername");
            Course n=new Course();
            n.setTeachername(teachername);
            gooddao.delete(n);
            request.setAttribute("message", "删除成功");
            request.getRequestDispatcher("course_servlet?method=looklist").forward(request, response);
        }
        else if("looklist".equals(method)) {            
            List<Course> nt= gooddao.list();
            request.setAttribute("nt", nt);
            System.out.println("王建民");
            request.getRequestDispatcher("NewFile3.jsp").forward(request, response);
        }
        else if("queryByTeachername".equals(method))
        {
        String teachername=request.getParameter("teachername");
        Course n=new Course();
        System.out.println("8888");
        n.setTeachername(teachername);
        n= gooddao.queryByTeachername(teachername); 
        request.setAttribute("n", n);
        request.getRequestDispatcher("update.jsp").forward(request, response);
        }
        else if("update".equals(method))
        {
        String teachername=request.getParameter("teachername");
        String classname=request.getParameter("classname");
        String place=request.getParameter("place");
        String old_teachername=request.getParameter("old_teachername");
        Course n=new Course(classname,teachername,place);
        gooddao.update(n,old_teachername);
        request.setAttribute("message", "添加成功");
        request.getRequestDispatcher("course_servlet?method=looklist").forward(request, response);
        }
        else if("searchByTeachername".equals(method))
        {
            System.out.println(keyword);
        List<Course> cha= gooddao.searchByTeachername(keyword); //传至dao层
        request.setAttribute("n", cha);    
        request.getRequestDispatcher("chaxun.jsp").forward(request, response);
        }
    }

}

三.jsp层

我们在chaxun.jsp里面添加查询的功能

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>    <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="course_servlet?method=searchByTeachername" method="post">
按老师姓名查询<input type="text" name="keyword" id="keyword">
<input type="submit" value="搜索">
<table>
<tr>
<td>课程名称</td>
<td>教师姓名</td>
<td>教学地点</td>
<td>操作</td>
</tr>
 <c:forEach items="${n}" var="xm">
            <tr>
                <td>${xm.classname}</td>
                <td>${xm.teachername}</td>
                <td>${xm.place}</td>
               <td><a href="course_servlet?method=delete&teachername=${xm.teachername}" onClick="return confirm('确认删除吗？');">删除</a>
                   <a href="course_servlet?method=queryByTeachername&teachername=${xm.teachername}">修改</a>
            </tr>
        </c:forEach>
        
</table>
</form>
</body>
</html>

四.运行结果

点击查询