Use MySQL database in a Spring Boot web application through Hibernate

UPDATE: take a look to this newer post for how to use MySQL in Spring Boot: Using MySQL In Spring Boot Via Spring Data JPA.

In the following is described how you can connect and use a MySQL database in your Spring Boot web application using Hibernate.

Spring Boot version

The code in this post is tested with Spring Boot 1.2.3.

Dependencies

Add the following dependencies in your pom.xml file:

<parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>1.2.3.RELEASE</version>
  <relativePath />
</parent>

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
  </dependency>
</dependencies>

You can see an example of the whole pom.xml here.

The application.properties file

Add these configurations inside the application.properties file:

 src/main/resources/application.properties

# Database
db.driver: com.mysql.jdbc.Driver
db.url: jdbc:mysql://localhost:8889/netgloo_blog
db.username: root
db.password: root

# Hibernate
hibernate.dialect: org.hibernate.dialect.MySQL5Dialect
hibernate.show_sql: true
hibernate.hbm2ddl.auto: create
entitymanager.packagesToScan: netgloo

The Database configurations are for the MySQL database connection and you should set them with your own values (database url, username and password).

The Hibernate configurations are for the Hibernate library and you can browse its official reference here for more details.

Java configuration class

Create a java class DatabaseConfig used for the database connection configurations:

 src/main/java/netgloo/configs/DatabaseConfig.java

package netgloo.configs;

// Imports ...

@Configuration
@EnableTransactionManagement
public class DatabaseConfig {

  @Value("${db.driver}")
  private String DB_DRIVER;
  
  @Value("${db.password}")
  private String DB_PASSWORD;
  
  @Value("${db.url}")
  private String DB_URL;
  
  @Value("${db.username}")
  private String DB_USERNAME;

  @Value("${hibernate.dialect}")
  private String HIBERNATE_DIALECT;
  
  @Value("${hibernate.show_sql}")
  private String HIBERNATE_SHOW_SQL;
  
  @Value("${hibernate.hbm2ddl.auto}"
  private String HIBERNATE_HBM2DDL_AUTO;

  @Value("${entitymanager.packagesToScan}")
  private String ENTITYMANAGER_PACKAGES_TO_SCAN;
  
  @Bean
  public DataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(DB_DRIVER);
    dataSource.setUrl(DB_URL);
    dataSource.setUsername(DB_USERNAME);
    dataSource.setPassword(DB_PASSWORD);
    return dataSource;
  }

  @Bean
  public LocalSessionFactoryBean sessionFactory() {
    LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();
    sessionFactoryBean.setDataSource(dataSource());
    sessionFactoryBean.setPackagesToScan(ENTITYMANAGER_PACKAGES_TO_SCAN);
    Properties hibernateProperties = new Properties();
    hibernateProperties.put("hibernate.dialect", HIBERNATE_DIALECT);
    hibernateProperties.put("hibernate.show_sql", HIBERNATE_SHOW_SQL);
    hibernateProperties.put("hibernate.hbm2ddl.auto", HIBERNATE_HBM2DDL_AUTO);
    sessionFactoryBean.setHibernateProperties(hibernateProperties);
    
    return sessionFactoryBean;
  }

  @Bean
  public HibernateTransactionManager transactionManager() {
    HibernateTransactionManager transactionManager = 
        new HibernateTransactionManager();
    transactionManager.setSessionFactory(sessionFactory().getObject());
    return transactionManager;
  }

}

This class will read your configurations from the application.properties file previously written. So if you have to change your database connection parameters (or some hibernate settings) you can change them in the property file and not in the java class.

You can see the whole java file content here.

The Data Access Object

In order access the database you should add an entity object and its DAO (Data Access Object) in the project.

In this example we create an entity User and its DAO. Create two java classes:

src/main/java/netgloo/models/User.java
src/main/java/netgloo/models/UserDao.java

with the following content for the class User:

package netgloo.models;

// Imports ...

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

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long id;
  
  @NotNull
  @Size(min = 3, max = 80)
  private String email;
  
  @NotNull
  @Size(min = 2, max = 80)
  private String name;

  public User() { }

  public User(long id) { 
    this.id = id;
  }

  public User(String email, String name) {
    this.email = email;
    this.name = name;
  }

  // Getter and setter methods ...
  
}

and this content for the class UserDao:

package netgloo.models;

// Imports ...

@Repository
@Transactional
public class UserDao {
  
  @Autowired
  private SessionFactory _sessionFactory;
  
  private Session getSession() {
    return _sessionFactory.getCurrentSession();
  }

  public void save(User user) {
    getSession().save(user);
  }
  
  public void delete(User user) {
    getSession().delete(user);
  }
  
  @SuppressWarnings("unchecked")
  public List getAll() {
    return getSession().createQuery("from User").list();
  }
  
  public User getByEmail(String email) {
    return (User) getSession().createQuery(
        "from User where email = :email")
        .setParameter("email", email)
        .uniqueResult();
  }

  public User getById(long id) {
    return (User) getSession().load(User.class, id);
  }

  public void update(User user) {
    getSession().update(user);
  }

}

Using the entity inside a controller

Now we can use the entity inside a Spring controller using the DAO class to handle database operations.

For example, you can create a UserController class:

src/main/java/netgloo/controllers/UserController.java

with the following code:

package netgloo.controllers;

// Imports ...

@Controller
@RequestMapping(value="/user")
public class UserController {

  @RequestMapping(value="/delete")
  @ResponseBody
  public String delete(long id) {
    try {
      User user = new User(id);
      _userDao.delete(user);
    }
    catch(Exception ex) {
      return ex.getMessage();
    }
    return "User succesfully deleted!";
  }
  
  @RequestMapping(value="/get-by-email")
  @ResponseBody
  public String getByEmail(String email) {
    String userId;
    try {
      User user = _userDao.getByEmail(email);
      userId = String.valueOf(user.getId());
    }
    catch(Exception ex) {
      return "User not found";
    }
    return "The user id is: " + userId;
  }

  @RequestMapping(value="/save")
  @ResponseBody
  public String create(String email, String name) {
    try {
      User user = new User(email, name);
      _userDao.save(user);
    }
    catch(Exception ex) {
      return ex.getMessage();
    }
    return "User succesfully saved!";
  }

  // Private fields

  @Autowired
  private UserDao _userDao;
  
}

Launching your Spring Boot web application you can test the UserController class (and the database connection) using the following urls:

• /user/save?email=[email]&name=[name]: create a new user with an auto-generated id and email and name as passed values.
• /user/delete?id=[id]: delete the user with the passed id.
• /user/get-by-email?email=[email]: retrieve the id for the user with the passed email address.

Get the whole code

You can download and try yourself the whole project described in this post from our GitHub repository:

https://github.com/netgloo/spring-boot-samples/tree/master/spring-boot-mysql-hibernate

Related posts

• Spring Boot: data access with JPA, Hibernate and MySQL: use JPA for access data and Hibernate as JPA implementation in a Spring Boot web application.

References

• http://www.concretepage.com/spring-4/spring-4-hibernate-4-gradle-integration-example-using-annotation
• http://examples.javacodegeeks.com/enterprise-java/hibernate/hibernate-query-language-example/
• https://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch11.html#objectstate-querying
• https://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch03.html#configuration-optional