Spring Boot: data access with JPA, Hibernate and MySQL

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 showed how to integrate JPA in Spring Boot, using Hibernate as JPA implementation and MySQL as database.

Configurations

First of all you should create a Java configuration file DatabaseConfig.java containing all the spring configurations about the database access.

src/main/java/netgloo/configs/DatabaseConfig.java
@Configuration
@EnableTransactionManagement
public class DatabaseConfig {

  /**
   * DataSource definition for database connection. Settings are read from
   * the application.properties file (using the env object).
   */
  @Bean
  public DataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(env.getProperty("db.driver"));
    dataSource.setUrl(env.getProperty("db.url"));
    dataSource.setUsername(env.getProperty("db.username"));
    dataSource.setPassword(env.getProperty("db.password"));
    return dataSource;
  }

  /**
   * Declare the JPA entity manager factory.
   */
  @Bean
  public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    LocalContainerEntityManagerFactoryBean entityManagerFactory =
        new LocalContainerEntityManagerFactoryBean();
    
    entityManagerFactory.setDataSource(dataSource);
    
    // Classpath scanning of @Component, @Service, etc annotated class
    entityManagerFactory.setPackagesToScan(
        env.getProperty("entitymanager.packagesToScan"));
    
    // Vendor adapter
    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    entityManagerFactory.setJpaVendorAdapter(vendorAdapter);
    
    // Hibernate properties
    Properties additionalProperties = new Properties();
    additionalProperties.put(
        "hibernate.dialect", 
        env.getProperty("hibernate.dialect"));
    additionalProperties.put(
        "hibernate.show_sql", 
        env.getProperty("hibernate.show_sql"));
    additionalProperties.put(
        "hibernate.hbm2ddl.auto", 
        env.getProperty("hibernate.hbm2ddl.auto"));
    entityManagerFactory.setJpaProperties(additionalProperties);
    
    return entityManagerFactory;
  }

  /**
   * Declare the transaction manager.
   */
  @Bean
  public JpaTransactionManager transactionManager() {
    JpaTransactionManager transactionManager = 
        new JpaTransactionManager();
    transactionManager.setEntityManagerFactory(
        entityManagerFactory.getObject());
    return transactionManager;
  }
  
  /**
   * PersistenceExceptionTranslationPostProcessor is a bean post processor
   * which adds an advisor to any bean annotated with Repository so that any
   * platform-specific exceptions are caught and then rethrown as one
   * Spring's unchecked data access exceptions (i.e. a subclass of 
   * DataAccessException).
   */
  @Bean
  public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
    return new PersistenceExceptionTranslationPostProcessor();
  }

  // Private fields
  
  @Autowired
  private Environment env;

  @Autowired
  private DataSource dataSource;

  @Autowired
  private LocalContainerEntityManagerFactoryBean entityManagerFactory;

}

Some configurations in the Java config file are read from the application.properties file using the Environment object. In this file are stored informations about the database connection and some hibernate configurations:

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: update
entitymanager.packagesToScan: netgloo

Make sure to set in this file your own configurations for the MySQL connection.

The Entity object

Now you can create an entity object representing a table in your db.

For example, the following entity is an user composed by three fields (id, email and name):

src/main/java/netgloo/models/User.java
@Entity
@Table(name = "users")
public class User {

  // The entity fields (private)  
  
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long id;
  
  @NotNull
  private String email;
  
  @NotNull
  private String name;


  // Public methods
  
  public User() { }

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

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

  // Getters and setters methods
  // ...
  
}

The Data Access Object

For each entity object you should create a DAO (Data Access Object) implementing at least the CRUD operations (create, read, update and delete) for your entity.

This is the Dao class for the entity User:

src/main/java/netgloo/models/UserDao.java
/**
 * This class is used to access data for the User entity.
 * Repository annotation allows the component scanning support to find and 
 * configure the DAO wihtout any XML configuration and also provide the Spring 
 * exceptiom translation.
 * Since we've setup setPackagesToScan and transaction manager on
 * DatabaseConfig, any bean method annotated with Transactional will cause
 * Spring to magically call begin() and commit() at the start/end of the
 * method. If exception occurs it will also call rollback().
 */
@Repository
@Transactional
public class UserDao {
  
  /**
   * Save the user in the database.
   */
  public void create(User user) {
    entityManager.persist(user);
    return;
  }
  
  /**
   * Delete the user from the database.
   */
  public void delete(User user) {
    if (entityManager.contains(user))
      entityManager.remove(user);
    else
      entityManager.remove(entityManager.merge(user));
    return;
  }
  
  /**
   * Return all the users stored in the database.
   */
  @SuppressWarnings("unchecked")
  public List getAll() {
    return entityManager.createQuery("from User").getResultList();
  }
  
  /**
   * Return the user having the passed email.
   */
  public User getByEmail(String email) {
    return (User) entityManager.createQuery(
        "from User where email = :email")
        .setParameter("email", email)
        .getSingleResult();
  }

  /**
   * Return the user having the passed id.
   */
  public User getById(long id) {
    return entityManager.find(User.class, id);
  }

  /**
   * Update the passed user in the database.
   */
  public void update(User user) {
    entityManager.merge(user);
    return;
  }

  // Private fields
  
  // An EntityManager will be automatically injected from entityManagerFactory
  // setup on DatabaseConfig class.
  @PersistenceContext
  private EntityManager entityManager;
  
}

A simple controller for testing

In the following controller you can see how to use the DAO object to access the database:

src/main/java/netgloo/controllers/UserController.java
@Controller
public class UserController {

  /**
   * Create a new user with an auto-generated id and email and name as passed 
   * values.
   */
  @RequestMapping(value="/create")
  @ResponseBody
  public String create(String email, String name) {
    try {
      User user = new User(email, name);
      userDao.create(user);
    }
    catch (Exception ex) {
      return "Error creating the user: " + ex.toString();
    }
    return "User succesfully created!";
  }
  
  /**
   * Delete the user with the passed id.
   */
  @RequestMapping(value="/delete")
  @ResponseBody
  public String delete(long id) {
    try {
      User user = new User(id);
      userDao.delete(user);
    }
    catch (Exception ex) {
      return "Error deleting the user: " + ex.toString();
    }
    return "User succesfully deleted!";
  }
  
  /**
   * Retrieve the id for the user with the passed email address.
   */
  @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: " + ex.toString();
    }
    return "The user id is: " + userId;
  }
  
  /**
   * Update the email and the name for the user indentified by the passed id.
   */
  @RequestMapping(value="/update")
  @ResponseBody
  public String updateName(long id, String email, String name) {
    try {
      User user = userDao.getById(id);
      user.setEmail(email);
      user.setName(name);
      userDao.update(user);
    }
    catch (Exception ex) {
      return "Error updating the user: " + ex.toString();
    }
    return "User succesfully updated!";
  } 


  // Private fields
  
  // Wire the UserDao used inside this controller.
  @Autowired
  private UserDao userDao;
  
}

Launching the Spring Boot application you can use the previous controller through the following urls:

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

Get the whole code

Try yourself getting the whole code described above from our github repository:

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

Related posts

References

Categories

Category BootstrapCategory CoffeescriptCategory DrupalCategory GravCategory HTMLCategory JavascriptCategory JoomlaCategory jQueryCategory LaravelCategory MagentoCategory PHPCategory SharePointCategory SpringCategory ThymeleafCategory WordPressCategory Workflow

Comments

Developed and designed by Netgloo
© 2016 Netgloo