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>

四.运行结果

点击查询

输入“刘”，即可得到查询结果

这就是一整个增删改查的过程，你只需要建一个表，后期可以进行美化、判断，完善功能！！！！

巴特西

javaweb之模糊查询

最新文章

热门文章