drop table employee;
drop table department;
drop table company;

create table company (company_id numeric not null, name varchar2(32));
create table department (department_id numeric not null, company_id numeric not null, name varchar2(32));
create table employee (employee_id numeric not null, department_id numeric not null, name varchar2(32));

insert into company values (1, 'Company 1');
insert into company values (2, 'Company 2');
insert into company values (3, 'Company 3');

insert into department values (1, 1, 'Engineering');
insert into department values (2, 1, 'Sales');
insert into department values (3, 2, 'Marketing');
insert into department values (4, 2, 'Technical Support');
insert into department values (5, 3, 'Accounting');
insert into department values (6, 3, 'Human Resources');

insert into employee values (1, 1, 'Joe');
insert into employee values (2, 1, 'Bob');
insert into employee values (3, 2, 'Billy');
insert into employee values (4, 2, 'Mike');
insert into employee values (5, 2, 'Sam');
insert into employee values (6, 3, 'Pete');

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

  <class entity-name="Company" table="company">
    <id name="company_id" column="company_id" type="integer" />
    <property name="CompanyId" column="company_id" type="integer" update="false" insert="false"/>
    <property name="CompanyName" column="name" type="string" update="false" insert="false"/>
    <set name="employees">
      <key column="employee_id"/>
      <one-to-many entity-name="Employee"/>
      <loader query-ref="loadEmployees"/>
    </set>
  </class>

  <class entity-name="Employee" table="employee">
    <id name="employee_id" column="employee_id" type="integer" />
    <property name="EmployeeId" column="employee_id" type="integer" update="false" insert="false"/>
    <property name="EmployeeName" column="name" type="string" update="false" insert="false"/>
  </class>

  <sql-query name="loadEmployees">
    <load-collection alias="e" role="Company.employees"/>
    select {e.*} from department d, employee e where d.company_id = ? and e.department_id = d.department_id
  </sql-query>

</hibernate-mapping>

public class Test
{
  public static void main(String[] args)
  {
    Configuration configuration = new Configuration();
    configuration.configure();
    SessionFactory factory = configuration.buildSessionFactory();
    Session session = factory.openSession();
    System.out.println("DEBUG> company1=" + session.get("Company", 1));
    System.out.println("DEBUG> company2=" + session.get("Company", 2));
    System.out.println("DEBUG> company3=" + session.get("Company", 3));
    System.out.println("DEBUG> employee1=" + session.get("Employee", 1));
  }
}

log4j:WARN No appenders could be found for logger (org.hibernate.cfg.Environment).
log4j:WARN Please initialize the log4j system properly.
Hibernate: select company0_.company_id as company1_0_0_, company0_.name as name0_0_ from company company0_ where company0_.company_id=?
Hibernate: select e.employee_id as employee1_0__, e.employee_id as employee1_1_0_, e.name as name1_0_ from department d, employee e where d.company_id = ? and e.department_id = d.department_id
DEBUG> company1={employees=[{$type$=Employee, EmployeeName=Joe, EmployeeId=1, employee_id=1}], $type$=Company, CompanyId=1, company_id=1, CompanyName=Company 1}
Hibernate: select company0_.company_id as company1_0_0_, company0_.name as name0_0_ from company company0_ where company0_.company_id=?
DEBUG> company2={employees=[{$type$=Employee, EmployeeName=Bob, EmployeeId=2, employee_id=2}], $type$=Company, CompanyId=2, company_id=2, CompanyName=Company 2}
Hibernate: select company0_.company_id as company1_0_0_, company0_.name as name0_0_ from company company0_ where company0_.company_id=?
DEBUG> company3={employees=[{$type$=Employee, EmployeeName=Billy, EmployeeId=3, employee_id=3}], $type$=Company, CompanyId=3, company_id=3, CompanyName=Company 3}
DEBUG> employee1={$type$=Employee, EmployeeName=Joe, EmployeeId=1, employee_id=1}

Process finished with exit code 0

    Query query = session.getNamedQuery("loadEmployees");
    query.setInteger("companyId", 1);
    for (Object employee : query.list()) {
      System.out.println("DEBUG> employee: " + employee);
    }

DEBUG> employee: {$type$=Employee, EmployeeName=Joe, EmployeeId=1, id=1}
DEBUG> employee: {$type$=Employee, EmployeeName=Bob, EmployeeId=2, id=2}
DEBUG> employee: {$type$=Employee, EmployeeName=Billy, EmployeeId=3, id=3}
DEBUG> employee: {$type$=Employee, EmployeeName=Mike, EmployeeId=4, id=4}
DEBUG> employee: {$type$=Employee, EmployeeName=Sam, EmployeeId=5, id=5}