WEB开发中的多关键词模糊查询

gaotm

WEB开发中的多关键词模糊查询；

     我们知道，要实现表记录的增、删、改、查功能，在学习ORM框架之前，都使用原生的JDBC技术实现，特别是针对select的操作；在WEB开发中，通常有针对商品的模糊性查询操作，现在以学生所做的图书购买为例，说明一下多关键词模糊查询应用；

    表结构为：Id，书名(name),作者（author）；价格，数量等；以书名和作者为关键词进行模糊搜索；

    先说一个关键词的情况；

    String sql = “select * from book_tab where name like %?% or author like %?%”;

    在表单中输入搜索关键字；

import java.io.IOException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

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 com.javakc.gaoming.jdbc.utils.Jdbc;

@WebServlet("/aa.do")

public class AaServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;

    private static final Connection conn;

    static {

       conn = Jdbc.getConnection("aaa", "aaa");

    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

       this.doPost(request, response);

    }

    @Override

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String keyword = request.getParameter("keyword");

        PreparedStatement stmt = null;

       ResultSet result = null;

       String sql = "select * from book_tab where name like %?% or author like %?%";

       try {

           stmt = conn.prepareStatement(sql);

           stmt.setObject(1, keyword);

           stmt.setObject(2, keyword);

           result = stmt.executeQuery();

           System.out.println(result);

       } catch (SQLException e) {

           e.printStackTrace();

       } finally {

           Jdbc.close(result, stmt);

       }

    }

    @Override

    public void destroy() {

       Jdbc.close(conn);

    }

}

    SQL语句为；

运行时出现；

    所以sql代码需要更改如下；

    可以正确得到查询结果；

    有多个关键词的情况，如；

    此时在Servlet中；

package com.javakc.book.one; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; 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 com.javakc.gaoming.jdbc.utils.Jdbc; @WebServlet("/aa.do") public class AaServlet extends HttpServlet {     private static final long serialVersionUID = 1L;     private static final Connection conn;     static {        conn = Jdbc.getConnection("aaa", "aaa");     }     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {        this.doPost(request, response);     }     @Override     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {        String str = request.getParameter("keywords");        String[] keywords = str.trim().split("\\s+");        /*         * 此时的要点是查询语句的构造；         */        final StringBuffer sb = new StringBuffer("select * from book_tab where ");        for (int i = 0; i < keywords.length; ++i) {            if (i > 0) {               sb.append(" or ");            }            sb.append("name like ? or author like ? ");        }        System.out.println(sb);        PreparedStatement stmt = null;        ResultSet result = null;        String sql = sb.toString().trim();        try {            stmt = conn.prepareStatement(sql);            for (int i = 0; i < keywords.length; ++i) {               String keyword = keywords[i];               int start = i * 2 + 1;               int end = start + 1;               stmt.setObject(start, "%" + keyword + "%");               stmt.setObject(end, "%" + keyword + "%");            }            result = stmt.executeQuery();            while (result.next()) {               System.out.println(result.getString("name") + "," + result.getString("author"));            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            Jdbc.close(result, stmt);        }     }     @Override     public void destroy() {        Jdbc.close(conn);     } }

    图书表book_tab；

    输入查询条件；

    将获取到的行记录转换为类实例，或显示，或跳转，或分页显示，进行后续的操作....