java hql 多表查询语句,Android开发 hibernate 使用HQL语句进行多表联查

释放双眼，带上耳机，听听看~！

先看数据库：

[order_list_sub_sendnum_part]表：

idolsspart_num

11001

22002

33003

44004

55005

[order_list_sub_sendnum]表：

idols_idol_idsend_numhandel_dateoli_id

1111312332017-04-011

2221313132017-04-062

333131532017-04-043

444225        2017-04-034

555131352017-04-025

[order_list]表：

idorder_idmateriel_idmateriel_nameneedarrive

11789钢铁100

NULL

22798水泥99

NULL

33456树脂55

NULL

44465卷纸36

NULL

55156塑料165

NULL

当用户输入零件编号(part_num)查出全部符合要求的数据，SQL语句为：

select a.*,b.*,c.*

from order_list_sub_sendnum_part a,order_list_sub_sendnum b,order_list c

where

a.olss = b.id  and b.oli_id = c.id and a.part_num=’001′;

结果为：

idolsspart_numidols_idol_idsend_numhandel_dateoli_ididorder_idmateriel_idmateriel_nameneedarrive

1100111

1131233

2017-04-011

11789钢铁100

NULL

1100111

1131233

2017-04-011

11789钢铁100

NULL

1100111

1131233

2017-04-011

11789钢铁100

NULL

1100111

1131233

2017-04-011

11789钢铁100

NULL

1100111

1131233

2017-04-011

11789钢铁100

NULL

在java中，使用hibernate 框架的时候应该这么写：

package com.ifreshwing.test;

import java.util.ArrayList;

import java.util.List;

import org.hibernate.Criteria;

import org.hibernate.HibernateException;

import org.hibernate.Query;

import org.hibernate.Session;

import org.hibernate.Transaction;

import org.hibernate.criterion.Restrictions;

import com.google.gson.Gson;

import com.ifreshwing.hibernate.HibernateSessionFactory;

import com.ifreshwing.hibernate.HibernateUtil;

import com.ifreshwing.hibernate.OrderListSubSendnumPart;

import com.ifreshwing.hibernate.SSub;

import com.ifreshwing.hibernate.UserIn;

import com.ifreshwing.hibernate.UserInfoId;

public class Test {

public static void main(String[] args) {

List> list1 = new ArrayList<>();

list1 = findUserByName("001");

System.out.println(new Gson().toJson(list1));

}

public static List> findUserByName(String name) {

Session session = null;

try {

session = HibernateSessionFactory.getSession();

String hql = "from OrderListSubSendnumPart a,OrderListSubSendnum b,OrderList c "

+ "where a.olss = b.id and b.oliId = c.id and a.partNum=?";

Query query = session.createQuery(hql);

query.setString(0, name);

List> list = query.list();

return list;

} catch (HibernateException e) {

e.printStackTrace();

return null;

} finally {

if (session != null) {

session.close();

}

}

}

}

结果为：

[[

{“id”:1,”olss”:1,”partNum”:”001″},

{“id”:1,”olsId”:1,”olId”:1,”sendNum”:131233,”handelDate”:”2017-04-01″,”oliId”:1},

{“id”:1,”orderId”:1,”materielId”:”789″,”materielName”:”钢铁”,”need”:100.0}

]]