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

References

  • Chawqi Hajar

    I tried this code but i have an error that i can ‘t resolve : The method setPackagesToScan(String) is undefined for the type LocalSessionFactoryBean .I need your help please :/

    • Perhaps there is some conflict in your project.. Try the code from our github repo here: download it, set your own configurations in the application.properties file and launch it with $ mvn spring-boot:run from the project’s root folder… It is a very minimal/essential project using the code from this post.. Once tried you can integrate that code in your project, or vice versa ;).. Take a look also on this post if can help you.

      • Chawqi Hajar

        Thank you Andrea for your quick response , i resolved the error but i still have another one :
        main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped “{[/error],methods=[],params=[],headers=[],consumes=[],produces=[],custom=[]}” , so the tables can not be generated ,could you help me please ,i am looking for the solution but no tutoriel has resolved my problem.

        • Chawqi Hajar

          Here is what i have while launching the project

          • there are no errors in that log.. check your database: your tables should be correctly created…

          • Chawqi Hajar

            That’s the problem ,they are not created 🙁

          • 🙂 it seems like hibernate doesn’t find any entities in your project.. are you sure you have all correctly configured, e.g. marked your entities with the Entity annotation or correctly setted the packagesToScan property (that is the place where entities will be searched)?

          • Chawqi Hajar

            what do you mean by set the packagesToScan ? here is my classes and the Application.properties

          • why do you have two main packages: demo and ma.irnia?.. can’t you put all your code in ma.irnia? in the packagesToScan property (in the application.properties file) you have to set the package where you should have your entities, in your case it is ma.irnia and not demo). But if you are using the code from the other post (I see it in the UserDao class) you can delete your DatabaseConfig class and use the others configurations in the application.properties..

          • Chawqi Hajar

            Yeah you’re right thank you so much it works now 😀

          • yep.. don’t forget to put a like on our facebook page and/or follow us on twitter 😉

          • Chawqi Hajar

            for sure 😀

  • Roco Escalera

    I am trying to run this example straight from your code but I’m getting the following exception:

    org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘entityManagerFactory’ defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaAutoConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when ‘hibernate.dialect’ not set

    Any idea what might be happening?
    Thanks.

    • Hi Roco. Your error says:

      […] when ‘hibernate.dialect’ not set

      Are you sure to have correctly set the hibernate.dialect property?.. Check your application.properties file or the DatabaseConfig java class.

      You can also try to see this post, where there is described a simpler way to connect MySQL on your project.

      • Roco Escalera

        I did move to that other example you mention and everything is ok so i’m sticking to that one for now.

        Regarding the hibernate.dialect I just left it as org.hibernate.dialect.MySQL5Dialect like you had it.

        Was i supposed to create the database by my own? I assumed Hibernate would create it on the fly.

        • Yes, you’re supposed to create the database (empty). Using the property hibernate.hbm2ddl.auto: create Hibernate will create the tables for you.

        • W O

          I’m facing the same problem (Access to DialectResolutionInfo cannot be null when ‘hibernate.dialect’ not set). Did you solve it? How? Tks.

  • Chawqi Hajar

    Andrea i am sorry ,you helped me to resolve the problem a month ago .Today i wanted to do the similar thing :generate tables using spring boot but id didn’t work even if i have don’t have any error . for the folders i had put the entities in package and the application in another package

    • Chawqi Hajar

      i resolved the problem,now i understand that we can seperate the classes but the backages should derive from the main package (for ex if we have the main package is demo the other packages should be demo.newPackage)

      • Great, I’m glad to hear you resolved the problem 🙂

  • Faraz

    Hello,
    Nice work.
    I got a question. Where can I find the sql schema to create table(s) in my mysql?
    Thanks

    • Hi Faraz, if you use the option hibernate.hbm2ddl.auto: create or update the schema will be automatically created by Hibernate.

      You can get the schema with this:


      mysqldump -u root -p --no-data dbname > schema.sql

      • Faraz

        Hello Andrea, Thanks for responding so fast! I never thought I would get a reply so fast. Anyway, I didn’t get to try your solution. But this is what I did to solve the issue. Inside MySql workbench, I created a new scheme by typing: CREATE SCHEMA `netgloo_blog` ; Then, I ran your examples and they worked perfectly fine.

      • Faraz

        hibernate.hbm2ddl.auto: create is already in application.properties file. Still, the same error as before: “Unknown database ‘netgloo_blog'”
        And where should I try this command that you suggested: mysqldump -u root -p –no-data dbname > schema.sql

        • The database must exists before running the code.

          To create the database is fine to use the sql code you used: CREATE SCHEMA `netgloo_blog`.

          When the database exists, using the option hibernate.hbm2ddl.auto: create Hibernate will create all the tables when the application starts (you should disable this option when you finish the development phase in order to preserve your data inside the database).

          The command mysqldump -u root -p --no-data dbname > schema.sql is just to export the database schema if you need it. But maybe I misunderstood your first question and you don’t need this.

          • Faraz

            Okay thank. I very much appreciate your help.

  • Gabriel Catice

    Hi, nice work! But, how can I call the post method pushing a json on the body request instead in the url?
    Thanks!

Categories

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

Comments

Developed and designed by Netgloo
© 2019 Netgloo