how can i join two different table

[Seetharaman Venkatasamy]

this is my first hibernate program. and i read some of the tutorial. but still i struggled

i have table A which has two column such as id and name here id is an primary key (and)

table B which has two column such as id and email here id is an primary key

now i need to fetch A.name and B.email from tables A and B based on id . i tried but failed to obtain the data .

please anyone can tell me the way to achieve it ?

[Paul Sturrock]

So is there is not relationship between these tables? Or is it one to one?

Can you post what you tried?

[Seetharaman Venkatasamy]

well,relation is one-to-one.

Here is my UserName.hbm.xml[For database table A]

    

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="com.UserName" table="A">
<id column="ID" name="id" type="java.lang.Long">
        <generator class="increment">
            <!-- 
            <param name="table">idgen</param>
            <param name="column">NEXT</param>
             -->
    	</generator>  
</id>
<property column="NAME" name="name" type="java.lang.String"/>

<!-- MAPPING -->
 
</class>
</hibernate-mapping>

and UserName.java[javabean]

   

public class UserName implements Serializable {
	
	private long id;
	private String name;	

	/** default constructor */
    public UserName() {
    }
   
    /** full constructor */
    public UserName(String name) {
        this.name = name;        
    }

    public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public long getId() {
		return id;
	}

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

}

and UserMail.hbm.xml[For database table B]

    
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="com.UserMail" table="B">
<id column="ID" name="id" type="java.lang.Long">
        <generator class="increment">
            <!-- 
            <param name="table">idgen</param>
            <param name="column">NEXT</param>
             -->
    	</generator>  
</id>
<property column="EMAIL" name="email" type="java.lang.String"/>

</class>
</hibernate-mapping>

and UserMail.java[java bean]

    
public class UserMail {

	private long id;
	private String email;
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	
}

and finally hibernate.cfg.xml

    

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">
<hibernate-configuration>
    <!-- a SessionFactory instance listed as /jndi/name -->
    <!--
hibernate.connection.datasource datasource JNDI name
hibernate.jndi.url URL of the JNDI provider (optional)
hibernate.jndi.class class of the JNDI InitialContextFactory (optional)
hibernate.jndi.xxxx pass the property xxxx to the JNDI InitialContextFactory (optional)
hibernate.connection.username database user (optional)
hibernate.connection.password database user password (optional)
    name="java:comp/env/hibernate/SessionFactory"
    -->
    <session-factory >
        
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost/test</property>
        <property name="connection.username">root</property>
        <property name="connection.password">root123</property>
        <!-- <property name="show_sql">true</property> -->       
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
        
        <!--
        <mapping resource="com/aof/component/domain/security/SecurityGroup.hbm.xml"/>
        <mapping resource="com/aof/component/domain/party/Party.hbm.xml"/>
        -->
        <mapping resource="com/UserName.hbm.xml"/>
        <mapping resource="com/UserMail.hbm.xml"/>
    </session-factory>
</hibernate-configuration> 

and i create a java class to test the application

Execute.java [assume it HibernateUtils class is fine]

    

public class Execute {
	
	//to be execute : from UserName,UserMail where UserName.id = UserMail.id
	
	public static void main(String[] args) {
		Session session = null;
		try
		{
			session = HibernateUtils.currentSession();
			Query q = session.createQuery("from UserName");
			List<UserName> aList = q.list();
			for(UserName userName : aList)
			{
				System.out.println(userName.getName());//it is fine working
			}
		}
		catch(Throwable th)
		{
			th.printStackTrace();
		}
		finally
		{
			if(null != session)
			{
				HibernateUtils.closeSession();
			}
		}
	}

}

Now it is working fine for retrieve the values from table A only.. now please see my question in my first post[how to inner join and get the datas]

Sorry for the long post

[Paul Sturrock]

OK, so you need to define this association in your mapping file and you do this with the one-to-one mapping. Have a read of that link, it includes a few examples.

(One question - why are you using Hibernate 2? Its a bit old. )

[Seetharaman Venkatasamy]

Thanks a lot .

Paul Sturrock wrote:(One question - why are you using Hibernate 2? Its a bit old. )

Yes . really i need to switch to Hibernate 3

[Seetharaman Venkatasamy]

in Hibernate Query , i need to fetch the records from two table. example,

select a.name,b.email from A as a,B as b where a.id=b.id

how to execute this query and how to get the values from the tables .

    
session = HibernateUtils.currentSession();
			Query q = session.createQuery("select n.name,e.email from UserName as n ,UserMail as e where n.id = e.id");
			List<UserName> aList = q.list();
			for(UserName userName : aList)
			{
				//System.out.println(userName.getName());
				UserMail userMail = userName.getUserMail();
				System.out.println(userName.getName()+ "   " +userMail.getEmail());
			}

Problem here UserName and ,UserMail are created and i am only casting in to the UserName . so i am getting classcastException

any help ?

[Rahul Babbar]

your "alist" will be a List of Object[]
So, it should be List<Object[]>
and when you iterate over this list, in each iteration, you get an Object[], cast the first element of the array to type of "a.name" and second element to "b.email" and create the required objects.

[Paul Sturrock]

Its worth having a read through the documentation. It includes a tutorial too, so you'll probably save yourself a good deal of hassle.

[Seetharaman Venkatasamy]

Thanks Paul and Rahul