mysql - HQL 在子查询和子句中不起作用

Question

像这样在 mysql 中有 3 个表:

CREATE TABLE tstudent(
  id varchar(36) primary key not null,
  firstname VARCHAR(30) NOT NULL,
  lastname VARCHAR(30)  NOT NULL,
  grade int   NOT NULL,
  sex VARCHAR(1)      NOT NULL,
  birthday DATE,
  dayorboarding VARCHAR(1)    NOT NULL,
  age int   ,
  house VARCHAR(20),
  mentor VARCHAR(50)
);


CREATE TABLE ttrip(
  id varchar(36) primary key not null,
  departureday       DATE         NOT NULL,
  name varchar(100) not null unique
);

CREATE TABLE tsignup(
  id varchar(36) primary key not null,
  s_id varchar(36) not null,
  FOREIGN KEY (s_id) REFERENCES tstudent(id), 
  t_id varchar(36) not null,
  FOREIGN KEY (t_id) REFERENCES ttrip(id),
  departuretransport   VARCHAR(1)   NOT NULL,
  returntransport    VARCHAR(1)   NOT NULL,
  remark TEXT
);

我可以使用这个 sql 来查询这样的信息:

mysql> select * from tsignup where t_id in (select id from ttrip where  name like '%bar%');
... ignore data ...  
1 row in set (0.00 sec)

但是当像这样使用hql时，它不起作用:

@Override
public DataGrid datagrid(Signup signup) {
    DataGrid dg = new DataGrid();

    String hql = "from Tsignup t";  
    Map<String, Object> params = new HashMap<String, Object>(); 
    hql = addWhere(signup, hql, params);

    String totalHql = "select count(*) " + hql; 
    hql = addOrder(signup, hql);
    List<Tsignup> l = signupDao.find(hql, params, signup.getPage(), signup.getRows());
    List<Signup> nl = new ArrayList<Signup>();
    changeModel(l, nl);
    dg.setTotal(signupDao.count(totalHql, params));
    dg.setRows(nl);
    return dg;
}

private String addWhere(Signup signup, String hql, Map<String, Object> params) {

    if(signup !=null){
        hql += " where 1=1 ";
        if(signup.getName() != null  && !signup.getName().trim().equals("")){
            hql += " and t.id in (select trip.id from ttrip as trip where trip.name like :name)";
            params.put("name", "%%" + signup.getName().trim() + "%%");
        }

    }
// logger will print:
    logger.info(signup.getName());
    logger.info(hql);

    return hql;
}

系统打印如下:

[cz.service.impl.StudentServiceImpl]bar
[cz.service.impl.StudentServiceImpl]from Tsignup t where 1=1  and t.id in (select trip.id from ttrip as trip where trip.name like :name)

想知道在 Hql 中创建相同 sql 的正确方法是什么。

Answer 1

寻找解决方案:

    String hql = "from Tsignup t";      
    Map<String, Object> params = new HashMap<String, Object>(); 
    hql = addWhere(signup, hql, params);

...

        hql += " where 1=1 ";
        if(signup.getName() != null  && !signup.getName().trim().equals("")){
            hql += " and t.ttrip.id in (select id from Ttrip where name like :name)";
            params.put("name", "%%" + signup.getName().trim() + "%%");