Hibernate

Connecting To Remote MySQL From Hibernate Using Tunnelling Over SSH

  • Posted on: 12 December 2014
  • By: Zhijun Chen

For certain reasons sometimes you may want to connect to remote MySQL server over SSH tunnel when you maintain web applications, e.g. debug a production issue which is hard to replicate locally. Most IDEs like Eclipse offer the debug tools which you can put break points and step through. You can find out the problem once connecting to remote database and debug with caution using local code copy.

The following steps illustrate how to achieve this for Hibernate based web applications (Presume you have the user set up for SSH and database connection).

  • Open MySQL tunnel over SSH

 You will only need the following command to open a tunnel:

ssh -L 3333:db.remotehost.com:3306 username@remotehost.com

This will open port 3333 on local machine to connect to MySQL port 3306 on remote server, enter your password on remote host once prompted.

  • Update Hibernate configuration file

Once we've got the tunnel, the next step would be updating Hibernate configuration file hibernate.cfg.xml to connect to the port 3333 as follows:

<hibernate-configuration>
  <session-factory>
    ...
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3333/remotedb</property>
    <property name="hibernate.connection.username">remote_user</property>
    <property name="hibernate.connection.password">remote_password</property>
    ...
  </session-factory>
</hibernate-configuration>

The remotedb is the database to connect on remote server, remote_user/remote_password is the username and password for that database on remote MySQL database.

Now you can open your web application and it should use the remote database you specified.

Hibernate Many To Many Annotation Mapping

  • Posted on: 29 March 2014
  • By: Zhijun Chen

In this blog I will use a simple 'Student' and 'Exam' example to demonstrate how to implement Hibernate Many to Many relationship mapping using annotations. 

* Database structure and schema

The database relationship is shown in the following diagram.

The corresponding mysql schema is shown as follows:

 CREATE TABLE `student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(50) NULL DEFAULT NULL,
    `last_name` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)

CREATE TABLE `exam` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `start_time` TIMESTAMP NOT NULL,
    PRIMARY KEY (`id`)
)

CREATE TABLE `student_exam` (
    `student_id` INT(11) NOT NULL,
    `exam_id` INT(11) NOT NULL,
    PRIMARY KEY (`student_id`, `exam_id`),
    KEY student_exam_student_key (student_id),
    KEY student_exam_exam_key (exam_id),
    CONSTRAINT student_exam_student_key FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT student_exam_exam_key FOREIGN KEY (`exam_id`) REFERENCES `exam` (`exam_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
* Hibernate Model Classes

Two entity classes, Student and Exam, will be created to map database tables with annotations.

Student.java

package uk.co.zhijun;

import java.util.HashSet;
import java.util.Set;
 
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;
 
@Entity
@Table(name="student")
public class Student {
    
    private Long id;
    private String firstName;
    private String lastname;
    private Set<Exam> exams = new HashSet<Exam>();
     
    public Student() {
    }
    
    public void setId(Long id) {
          this.id = id;
    }

    @Id
    @Column(name="id")
    @GeneratedValue
    public Long getId() {
          return id;
    }

    public void setFirstName(String firstName) {
          this.firstName = firstName;
    }

    @Column(name="first_name")
    public String getFirstName() {
          return firstName; 
    }

   public void setLastName(String lastName) {
          this.lastName = lastName;
    }
    
    @Column(name="last_name")
    public String getLastName() {
          return lastName; 
    }

    public void setExams(Set<Exam> exams) {
          this.exams = exams;
    }

    @ManyToMany(cascade = {CascadeType.ALL})
    @JoinTable(name="student_exam", 
                joinColumns={@JoinColumn(name="student_id")}, 
                inverseJoinColumns={@JoinColumn(name="exam_id")})
    public Set<Exam> getExams() {
          return exams;
    }
}

Exam.java

package uk.co.zhijun;

import java.util.Date;
import java.util.HashSet;
import java.util.Set;
 
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.Table;
 
@Entity
@Table(name="exam")
public class Exam {
 
    private Long id;
    private String name;
    private Date startTime;
    private Set<Student> students = new HashSet<Student>();
     
    public Exam() {
    }

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

    @Id
    @Column(name="id")
    @GeneratedValue
    public Long getId() {
          return id;
    }

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

    @Column(name="name")
    public String getName() {
          return name; 
    }

    public void setStartTime(Date startTime) {
          this.startTime = startTime;
    }

    @Column(name="start_time")
    public Date getStartTime() {
          return startTime;
    }

    public void setStudents(Set<Student> students) {
          this.students = students;
    }
    
    @ManyToMany(mappedBy="exams")
    public Set<Student> getStudents() {
          return students;
    }
}

@ManyToMany - is used to create many-to-many relationship between Student and Exam entites. The association can be unidirectional or bidirectional (in this case). The Student entity is the owning side.

@JoinTable - is used to define the join table for many-to-many relationship.

@JoinColumn - is used to define the join columns.

* Hibernate configuration file

 We need to put these two classes in hibernate.cfg.xml file.

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">root</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="show_sql">false</property>
        <mapping class="uk.co.zhijun.Student" />
        <mapping class="uk.co.zhijun.Exam" />
    </session-factory>
</hibernate-configuration>