Hibernate: @JoinTable with intermediary table

There are a lot of articles about Hibernate associations in the internet and particularly on my blog. But JPA provides numerous combinations of associations, so it’s not strange that we still see new posts about this or that kind of association. Today I want to examine situation with the @JoinTable annotation and intermediary table. For the tutorial I’ll use MySQL, Hibernate as implementation of JPA and Maven.

Today’s example will be based on a simple situation – user role service. That means that I’m going to work with two entities: User, Role. Every user can has just one role. So in the database I need to create one table for the users, one table for the roles and the third one for the mapping of the roles to the users. The last table, as you understand, plays role of intermediary table.

JoinTable-Intermediary-table

Here is a code for the tables creation:

CREATE TABLE `roles` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `role` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `login` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `user_roles` (
  `user_id` int(6) NOT NULL,
  `role_id` int(6) NOT NULL,
  KEY `user` (`user_id`),
  KEY `role` (`role_id`),
  CONSTRAINT `user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

After all tables were created I need to insert some roles into roles table:

INSERT INTO roles 
(role) 
VALUES 
('admin'),
('moderator');

The admin record will be with the id = 1, and the moderator record will be with the id = 2.
Now let’s move to a java code of the classes which will represent the tables:

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.OneToOne;
import javax.persistence.Table;

@Entity
@Table(name="users")
public class User {

	@Id
	@GeneratedValue
	private Integer id;
	
	private String login;
	
	private String password;
	
	@OneToOne(cascade=CascadeType.ALL)
	@JoinTable(name="user_roles",
	joinColumns={@JoinColumn(name="user_id", referencedColumnName="id")},
	inverseJoinColumns={@JoinColumn(name="role_id", referencedColumnName="id")})
	private Role role;
	
	public User(String login, String password) {
		this.login = login;
		this.password = password;
	}

	public Integer getId() {
		return id;
	}

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

	public String getLogin() {
		return login;
	}

	public void setLogin(String login) {
		this.login = login;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public Role getRole() {
		return role;
	}

	public void setRole(Role role) {
		this.role = role;
	}
	
}

In this class you noticed that I applied a lot of annotations to the Role property. The @OneToOne annotation was used since users table is in “one to one” relationship with the user_roles table. The @JoinTable annotation indicates that we will interact with the intermediary table (user_roles) and further you can see settings of the relationship including mapping of columns.

The joinColumns attribute is responsible for the columns mapping of the owning side. The name attribute contains the column name of the intermediary table, the referencedColumnName attribute contains the primary key column name (in our case the primary key column of the user table is id) of the owning side.

The inverseJoinColumns attribute is responsible for the columns mapping of the inverse side. Here is everything is similar as in the explanation above, just with the one distinction – you need to apply this to the roles and user roles tables.
More information about associations you can read in the one of my previous posts.

import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name="roles")
public class Role {

	@Id
	@GeneratedValue
	private Integer id;
	
	private String role;
	
	@OneToMany(cascade=CascadeType.ALL)
	@JoinTable(name="user_roles",
	joinColumns={@JoinColumn(name="role_id", referencedColumnName="id")},
	inverseJoinColumns={@JoinColumn(name="user_id", referencedColumnName="id")})
	private List userList;
	
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getRole() {
		return role;
	}
	public void setRole(String role) {
		this.role = role;
	}
	public List getUserList() {
		return userList;
	}
	public void setUserList(List userList) {
		this.userList = userList;
	}
	
}

In the Role entity, almost all annotations are the same as in the previous one. Pay your attention to @OneToMany annotation. Hence, appears property of collection type (private List userList).

Let’s see how this code can work:

public class DemoFirst {

	public static void main(String[] args) {

		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();
		session.beginTransaction();
		
		Role role = (Role) session.get(Role.class, 2);

		User user = new User("Fruzenshtein", "qwerty");
		user.setRole(role);
		
		session.save(user);
		
		session.getTransaction().commit();
		
		session.close();

	}
}

The result of the code execution is:

Hibernate: select role0_.id as id2_0_, role0_.role as role2_0_ from roles role0_ where role0_.id=?
Hibernate: insert into users (login, password) values (?, ?)
Hibernate: insert into user_roles (role_id, user_id) values (?, ?)

Summary

The @JoinTable annotation is a powerful feature of the Hibernate, it can be applicable to many situations to make your life easier. It can be used with two tables and with three, the last case I have demonstrated in this tutorial. You can find the sources of the application on my GitHub account.

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS
  • abdul

    This post was very helpful to me. It saved my day. Thanks and continue good work.

  • Paul

    Amazing! Thanks for your effort!

  • WS

    Hi, I implemented your solution in my project and it’s creating the joined table but the behavior is strange.

    I have a `Document` and `Keyword` object. `Document` has a primary key `docid` and `Keyword` has a primary key `text`. My joined table will use both of these as primary keys.

    Here’s an example, I have a `Document` with `docid = “abcd”` and it has attached two keywords `weather` and `storm`. I also have another `Document` with `docid = “efgh”` and it has the same two keywords. Note, the `text` column is the keyword.

    When I save the `abcd` document, the document table has abcd in the row. The keywords table has both weather and storm rows. And the joined table document_keyword has two rows. (abcd, weather) and (abcd, storm).

    But, when I add `efgh`, it deletes both records in the document_keyword table and adds itself. So now the docuemnt_keyword table contains only (efgh, weather) and (efgh, storm).

    Have you tried adding two Roles that have the same two Users in your example? Possibly my problem is not having an auto-generated column. Thanks.

    • http://fruzenshtein.com/ Alex Zvolinskiy

      Hi WS,

      Firstly “My joined table will use both of these as primary keys.”. You can have just one PK in a table.

      I didn’t try to “adding two Roles that have the same two Users in your example?” because I have one-to-one relation which implies that one record from the table A can has just one recored from table B by definition.

      In your example you need to define which type of relations will suit for you.

    • RAHUL

      @Ws it may be because of two unidirectional mapping rather than one bidirectional.use mappedby attribute in keyword class instead of @jointable

  • Mayur Agrawal

    i feel that this example is very bad for usage of JPA relationships. you should use

    class User{

    @ManyToOne
    @JoinTable(name =”user_role”, joincolumn(name=user_id),inversejoincolumn(name=role_id))
    Role role;
    }

    class Role{
    @OneToMany(mappedBy=”role”)
    Set users;

    }

    • http://fruzenshtein.com/ Alex Zvolinskiy

      Mayur, you right, you just feel. This example is for demonstration of intermediate table with the help of Hibernate. Sure I can use direct relation from one tabel to another and even more, I have a lot of tutorials, which describe this approach

    • Vanadise

      Why do you feel that Fruzenshtein’s example is very bad and yours is good?

      • MyNewScreenName

        For starters, User to Role is OneToOne, but Role to User is OneToMany…

        • Vanadise

          While that’s a good point, that’s obviously just a casual mistake that will be caught by any decent IDE. I was more curious if there was a reason why using the “mappedBy” attribute on the @OneToMany annotation was better than adding another @JoinTable annotation.

  • xiaoyu

    hi, i recieved this error
    java.lang.IllegalStateException: No WebApplicationContext found: no ContextLoaderListener registered?

    what can i do? thankyou

  • yogesh

    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘groupId’ in ‘field list’
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:408)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    com.mysql.jdbc.Util.getInstance(Util.java:381)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
    com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
    com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
    com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
    com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
    org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:233)
    org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
    org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:96)
    org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:58)
    org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3032)
    org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3558)
    org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:98)
    org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:490)
    org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:195)
    org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:179)
    org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:214)
    org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:324)
    org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:288)
    org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:194)
    org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:125)
    org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:209)
    org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:55)
    org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:194)
    org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:49)
    org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
    org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:715)
    org.hibernate.internal.SessionImpl.save(SessionImpl.java:707)
    org.hibernate.internal.SessionImpl.save(SessionImpl.java:702)
    com.leadwinner.infra.user.dao.UserDAOImpl.addUser(UserDAOImpl.java:37)
    com.leadwinner.infra.user.service.UserManagerImpl.addUser(UserManagerImpl.java:25)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:483)
    org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
    org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    com.sun.proxy.$Proxy1130.addUser(Unknown Source)
    com.leadwinner.infra.user.UserController.userInsertForm(UserController.java:170)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:483)
    org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

    • http://www.leadwinner.com yogesh

      i followed same code as, but by chaging the field names as per my requirements,
      but why im getting this problem,
      waiting for ans,
      reply will be appreciated

    • http://www.leadwinner.com yogesh

      com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘userentity1_.groupId’ in ‘field list’