java - HTTP 状态 500 - 第 1 行第 129 列附近出现意外标记 : ,

Question

错误如下:

HTTP Status 500 - Request processing failed; nested exception is org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: , near line 1, column 129 [Select partnumber, partdescription, invoicenumber, quantity from invoicedetails i, parts p WHERE i.invoicenumber in :invoiceList, i.partnumber = p.partNo AND quantity < 0 ]

我尝试运行查询并将记录保存在 xlx 文件中。但是当我执行时它返回错误意外标记。我是否以正确的方式编写查询？

查询在 SQL 工作台上运行良好

你们能帮我解决这个问题吗？非常感谢帮助。谢谢

实体类

public class SpecialOrder {

    public String partdescription;

    public Integer invoicenumber;

    public String partnumber;

    public Integer quantity;

    public Integer year;

    public SpecialOrder() {
        super();
    }

    public SpecialOrder(Integer invoicenumber, String partnumber, Integer quantity, Integer year,
            String partdescription) {
        super();
        this.invoicenumber = invoicenumber;
        this.partnumber = partnumber;
        this.quantity = quantity;
        this.year = year;
        this.partdescription = partdescription;
    }

    public String getPartdescription() {

        return partdescription;
    }

    public Integer getInvoicenumber() {

        return invoicenumber;
    }

    public String getPartnumber() {

        return partnumber;
    }

    public Integer getQuantity() {

        return quantity;
    }

    public Integer getYear() {

        return year;
    }

    public void setPartdescription(String partdescription) {

        this.partdescription = partdescription;
    }

    public void setInvoicenumber(Integer invoicenumber) {

        this.invoicenumber = invoicenumber;
    }

    public void setPartnumber(String partnumber) {

        this.partnumber = partnumber;
    }

    public void setQuantity(Integer quantity) {

        this.quantity = quantity;
    }

    public void setYear(Integer year) {

        this.year = year;
    }

Controller 类

@RequestMapping("specialorder")
    public ModelAndView SpecialOrder(@RequestParam("invoiceno") String invoiceno, Model map, HttpSession session,
            ModelAndView mav) throws ConnectException {

        AppUser user = (AppUser) session.getAttribute("user");
        if (user == null) {
            throw new OrderNotFoundException();
        } else {

            List<Integer> invoiceNoList = new ArrayList();
            if (invoiceno.contains(",")) {
                String[] array = invoiceno.split(",");
                for (String s : array) {
                    invoiceNoList.add(Integer.parseInt(s));
                }
            } else {
                invoiceNoList.add(Integer.parseInt(invoiceno));
            }
            List<SpecialOrder> invoiceList = ordersService.getSpecialOrder(invoiceNoList);
            System.out.println("invoice list  = " + invoiceList);

            mav.clear();
            mav.setView(new SpecialOrderExcelView());
            // mav.setViewName("specialorderpage");
            mav.addObject("user", user);
            mav.addObject("branch", branch);
            mav.addObject("appcss", appcss);
            mav.addObject("sysdate", InsightUtils.getNewUSDate());
            mav.addObject("invoiceList", invoiceList);
        }
        return mav;

    }

服务等级

@Transactional
    public List<SpecialOrder> getSpecialOrder(List<Integer> invoiceList) throws ConnectException {

        List<SpecialOrder> getspecialorder = new ArrayList<>();

        Session session = sessionFactory.getCurrentSession();
        String hSql = "Select partnumber, partdescription, invoicenumber, quantity " + "from invoicedetails i, parts p "
                + "WHERE i.invoicenumber in :invoiceList, i.partnumber = p.partNo AND quantity < 0 ";

        Query query = session.createQuery(hSql);

        query.setParameterList("invoiceList", invoiceList);

        getspecialorder = query.list();

        session.flush();
        session.clear();

        return getspecialorder;
    }

Answer 1

我检查了查询 https://www.eversql.com/sql-syntax-check-validator/它不喜欢用逗号代替 AND(实际上它位于位置 129，如您收到的错误消息中所示)，并期望 in 后面跟着括号中的内容(它也不喜欢冒号，但那是因为它是 SQL 而不是 HQL 所以不理解该参数)。它确实接受以下内容(没有冒号，我在之后恢复了冒号)。

Select
partnumber,
  partdescription,
  invoicenumber,
  quantity
from
  invoicedetails i,
  parts p
WHERE
  i.invoicenumber in (:invoiceList)
  AND i.partnumber = p.partNo
  AND quantity < 0

我相信这应该适合你。