java - 为什么无法通过关系字段找到所有项目？

Question

我从 sql 语句开始。当我测试它时，我有那个 sql（如下所示），它在 SQL Management Studio 中运行良好，我得到了预期的结果。

select distinct p.id, p.name, m.name
from test001.npd.projects p,
     test001.npd.customer,
     test001.npd.manager w
where lower(p.name) like '%%' and lower(p.customer) like '%%' and lower(m.name) like '%k%'

但后来我将该语句移动到休眠createQuery的休眠方法，如下所示：

public List<Projects> findAlike(String projectName, String customerName, String manager) {
    Query q = getCurrentSession().createQuery("select p from projects p, managers m, wine_styles w where 
    lower(p.name) like :name and lower(p.customer) like :customer and lower(m.name) like :manager");
    q.setParameter("name", "%" + projectName.toLowerCase() + "%");
    q.setParameter("customer", "%" + customerName.toLowerCase() + "%");
    q.setParameter("manager", "%" + manager.toLowerCase() + "%");

    List<Projects> lo = (List<Projects>) q.getResultStream().distinct().collect(Collectors.toList());

    return (List<Projects>) lo;
}

我遇到的问题是，当我通过name并且customer一切正常时，我可以通过它进行搜索，但manager每次字段都会返回表中的所有行。

我为每个KeyListener#keyReleased调用调用该函数，如下所示：

@Override
public void keyReleased(KeyEvent e) {
 NewProdForm.getData().setProjects(NewProdForm.getData().getProjectService().findAlike(projectInput.getText(), customerInput.getText(), projectManagerInput.getText()));
 NewProdForm.getAppFrame().getProjectListPanel().getTm().fireTableDataChanged();
}

而且我tabelModel每次都提神醒脑。我的班级实现KeyListener并在 cosntructor 中为每个人添加关键侦听器TextField

public class Search extends JPanel implements KeyListener {
    private JTextField projectInput;
    private JTextField customerInput;
    private JTextField projectManagerInput;
    private JTextField wineStyleInput;
    private JTextField proposedLaunchDateInput;
    private JTextField statusInput;
    public Search() {
        setLayout(null);
        setBackground(Color.white);
        projectInput = new JTextField();
        customerInput = new JTextField();
        projectManagerInput = new JTextField();
        wineStyleInput = new JTextField();
        proposedLaunchDateInput = new JTextField();
        statusInput = new JTextField();

        add(projectInput);
        add(customerInput);
        add(projectManagerInput);
        add(wineStyleInput);
        add(proposedLaunchDateInput);
        add(statusInput);

        projectInput.addKeyListener(this);
        customerInput.addKeyListener(this);
        projectManagerInput.addKeyListener(this);
    }
}

不知道该怎么办。我搜索谷歌，但我发现的所有例子都表明我做的一切都是正确的，但我确信我不是因为我没有我的结果。

编辑 所以在一些评论建议后我创建了procedure这样

IF (OBJECT_ID('npd.searching') IS NOT NULL)
  DROP PROCEDURE npd.searching

USE test001;
GO
CREATE PROCEDURE npd.searching
    @project nvarchar(50),
    @customer nvarchar(50),
    @manager nvarchar(50),
    @wine nvarchar(50)
AS
    SET NOCOUNT ON;
    select p.id, p.name, m.name from test001.npd.projects p
        inner join test001.npd.managers m on p.manager_id = m.id
        inner join test001.npd.wine_styles w on p.wine_style_id = w.id
    where 
        lower(p.name) like '%' + @project + '%' and 
        lower(p.customer) like '%' + @customer + '%' and 
        lower(m.name) like '%' + @manager + '%' and 
        lower(w.name) like '%' + @wine + '%';
GO

我像这样在 SQL Management Studio 中对其进行了测试

EXEC npd.searching '', '', 'k', '';

一切都如我所愿。所以我也想把它移到休眠状态所以我做了这样的事情

public List<Projects> findAlike(String projectName, String customerName, String manager, String wineStyle) {
        Query q = getCurrentSession().createSQLQuery("EXEC npd.searching :name, :customer, :manager, :wine").addEntity(Projects.class);
        q.setParameter("name", "'" + projectName.toLowerCase() + "'");
        q.setParameter("customer", "'" + customerName.toLowerCase() + "'");
        q.setParameter("manager", "'" + manager.toLowerCase() + "'");
        q.setParameter("wine", "'" + wineStyle.toLowerCase() + "'");
        List<Projects> lo = (List<Projects>) q.getResultStream().distinct().collect(Collectors.toList());

        return (List<Projects>) lo;
    }

但是当我调用该方法时，我什么也没得到，我的意思是我arraylist从查询中分配数据的位置是空的。那有什么问题？

编辑[已解决]

好的，经过2小时的搜索终于找到了解决方案。我使用的上述问题的解决方案是@NamedNativeQueries. 所以我加了

@NamedNativeQueries({
    @NamedNativeQuery(
            name = "searchProjects",
            query = "EXEC npd.searching :project, :customer, :manager, :wine",
            resultClass = Projects.class
    )
})

我改变了findAlike使用方法，@NamedNativeQuery如下所示：

@SuppressWarnings("unchecked")
public List<Projects> findAlike(String projectName, String customerName, String manager, String wineStyle) {
    Query query = getCurrentSession().getNamedQuery("searchProjects"); // THIS LINE
    query.setParameter("project", projectName.toLowerCase());
    query.setParameter("customer", customerName.toLowerCase());
    query.setParameter("manager", manager.toLowerCase());
    query.setParameter("wine", wineStyle.toLowerCase());
    List<Projects> foundProjects = (List<Projects>) query.getResultStream().distinct().collect(Collectors.toList());

    return foundProjects;
}

标签： javasqlsql-serverhibernate