need HQL statement for connecting two databases at the earliest.

Thanks in advance

Do you mean two tables? or do you really mean two databases. If you mean two databases, you can't join them with hibernate. You can have two session factories (1 for each database) and start two sessions and programatically move data between the two sessions.

If you mean two tables, then you would use the join keyword. PLease provide more clarity.

Hi
I have attached the code which i have written it is given and error at the lines marked in bold. Please tell me what changes i have to make to join two table employee and department and display them....



import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.apache.log4j.*;
import org.hibernate.Query;

import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.HashSet;
import java.util.Set;

public class select
{
public static void main(String[] args)
{
BasicConfigurator.configure();
Logger logger = Logger.getLogger(select.class);
Session session = null;
Transaction tx = null;
SessionFactory sessionFactory = null;
try
{
Configuration configuration = new Configuration();
configuration.configure();
sessionFactory = configuration.buildSessionFactory();
session = sessionFactory.openSession();

String query = "from Employee as emp join Employee1 as dept";/* Is this statement correct? here employee is a java file which has getter and setter methods for a table employee and employee1 is a java file which has getter and setter methods for a table department*/

Query qry = session.createQuery(query);

Iterator prjItr = qry.iterate();
while(prjItr.hasNext()){
Employee emp=(Employee) prjItr.next();
emp.show();
System.out.println("====================================================");
System.out.println("===================================================="); //System.out.println(o);
}
}

Please post mapping files as well in code block elements so that it is in readable format. DB schema is also helpful to put some example mapping files, if someone wants to give you example mapping files.

Change your mapping name from Employee1( to Department ) as it maps to department table. This will help is removing confusion for yourself.

No, your statement is not correct. You should not have to explicity state the join in this case. Assuming that Department to Employees is a one-to-many, ideally, you would have something like this

public class Department {
private long id;
private Set employees;

public long getId() {
return id;
}

public void setId(long id) {
this.id = id;
}

public Set getEmployees() {
return employees;
}

public void setEmployees(Set employees) {
this.employees = employees;
}
}

public class Employee {
private Department department;

public Department getDepartment() {
return department;
}

public void setDepartment(Department department) {
this.department = department;
}
}

Here are the mapping files (assuming you are using sequences for id's)

Department.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
<class name="Department" table="DEPARTMENTS">
<id name="id" type="long" column="DEPARTMENT_ID">
<generator class="sequence">
<param name="sequence">DEPARTMENT_SEQUENCE</param>
</generator>
</id>

<set name="employees"
table="EMPLOYEES"
cascade="save-update"
lazy="true"
<key column="DEPARTMENT_ID" not-null="true"/>
<one-to-many class="Employee"/>
</set>
</class>
</hibernate-mapping>

Employee.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
<class name="Employee" table="EMPLOYEES">
<id name="id" type="long" column="EMPLOYEE_ID">
<generator class="sequence">
<param name="sequence">EMPLOYEE_SEQUENCE</param>
</generator>
</id>

<many-to-one name="department" column="DEPARTMENT_ID" not-null="true"/>
</class>
</hibernate-mapping>

This should give you a good starting place. To get the employees for a department, you can either

1) load the department by id
-- session.get(Department.class, id)
-- and access the employees in the session or turn off lazy loading (lazy="false") in the Department.hbm.xml

2) load the employees by department id (but previous might work better)
-- read the reference guide to explain more details about this since i use
-- criteria for all my queries and I am a little rusty on HQL

hi canning
is it possible to log on to messenger so that v can communicate better
Gautham