Spring boot jpa mysql example

1. Overview

In this article, We have explained Spring boot JPA MySQL example or we can say how to configure MySQL datasource with spring boot using JPA.

• Main advantage of Spring Data JPA is that it will totally remove DAO implementation, Instead of writing manually query, we can define method in a such a way that spring boot JPA automatically generate query
• In the feature, Application needs to convert another JPA implemented the database in that case just need to replace drive other things will remain same like MongoDB, Neo4j, etc which is supported by spring boot.

2. Example

Spring boot JPA MySQL example

2.1 pom.xml

• Requires MySQL connector dependency  mysql-connector-java
• Requires spring jpa dependency spring-boot-starter-data-jpa

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>spring-boot-example</groupId>
    <artifactId>spring-boot-hibernate-example</artifactId>
    <version>1.0-SNAPSHOT</version>
    <description>Spring boot jpa mysql example</description>
    <!-- Inherit defaults from Spring Boot -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.4.RELEASE</version>
    </parent>
    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    <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>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
    </dependencies>
    <!-- Package as an executable jar -->
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

2.2 application.properties

• spring.jpa.hibernate.naming.physical-strategy indicates naming strategy of the database column. There are two possible strategies available:

org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy

@Column name will be converted to camel notation with _ in database

org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl :

@Column name will remain same which is specified in java class

• spring.jpa.hibernate.ddl-auto indicate automatically update database schema while changes in Java Class
• spring.jpa.show-sql indicates show SQL in the console. false recommended for production.
• spring.datasource.* properties are related to database connections like username, password, URL, and driver.

spring.datasource.url=jdbc:mysql://localhost/demo_database
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
# Naming strategy
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
# Allows Hibernate to generate SQL optimized for a particular DBMS
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

2.3 SpringBootConfig

package com.javadeveloperzone;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
/**
 * Created by Java Developer Zone on 19-07-2017.
 */
@SpringBootApplication
@ComponentScan
// Using a root package also allows the @ComponentScan annotation to be used without needing to specify a basePackage attribute
public class SpringBootConfig {
    public static void main(String[] args) throws Exception {
        SpringApplication.run(SpringBootConfig.class, args);            // it wil start application
    }
}

2.4 EmployeeController

package com.javadeveloperzone.controller;
import com.javadeveloperzone.dao.EmployeeDAO;
import com.javadeveloperzone.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
 * Created by Java Developer Zone on 19-07-2017.
 */
@RestController
public class EmployeeController {
    @Autowired
    private EmployeeDAO employeeDAO;
    @RequestMapping("/employeeList")
    public java.util.List<Employee> emploeeList() {
        return employeeDAO.findAll();
    }
    @RequestMapping(value = "/startWithName/{name}")
    public java.util.List<Employee> findByName(@PathVariable("name")String name){
        return employeeDAO.findEmployeeByEmployeeNameStartingWith(name);
    }
    @RequestMapping(value = "/findByRole/{role}")
    public java.util.List<Employee> findByRole(@PathVariable("role")String role){
        return employeeDAO.findEmployeeByEmployeeRole(role);
    }
}

2.5 EmployeeDAO

Here, We haven’t written the query, Spring JPA will automatically generate a query based on the method name. The custom query can be written using @Query annotation.

package com.javadeveloperzone.dao;
import com.javadeveloperzone.model.Employee;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.repository.CrudRepository;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.EntityManagerFactory;
import java.io.Serializable;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
 * Created by Java Developer Zone on 03-08-2017.
 */
@Component
@Transactional
public interface EmployeeDAO extends CrudRepository<Employee,Integer> {
    List<Employee> findEmployeeByEmployeeNameStartingWith(String name); // fetch list of Employee which start with
    List<Employee> findEmployeeByEmployeeRole(String role);         // fetch Employee by role
    List<Employee> findAll();                           // fetch all Employee
    
    @Query("SELECT em FROM Employee em WHERE em.employeeId = :employeeId")     // custom query
    List<Employee> getEmployeeById(@Param("employeeId") long employeeId);
}

2.6 Employee

We have defined Employee class as Entry which will be automatically created a database in MySQL while application will be load:

package com.javadeveloperzone.model;
import javax.persistence.*;
/**
 * Created by Java Developer Zone on 03-08-2017.
 */
@Entity
@Table(name = "employee")
public class Employee {
    @Id                                                     // primary key
    @GeneratedValue(strategy = GenerationType.IDENTITY)     // auto increment
    @Column(name = "employeeId")
    private int employeeId;
    @Column(name = "employeeName")
    private String employeeName;
    @Column(name = "employeeRole")
    private String employeeRole;
    public int getEmployeeId() {
        return employeeId;
    }
    public void setEmployeeId(int employeeId) {
        this.employeeId = employeeId;
    }
    public String getEmployeeRole() {
        return employeeRole;
    }
    public void setEmployeeRole(String employeeRole) {
        this.employeeRole = employeeRole;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
}

2.7 Build & Start Application:

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.5.4.RELEASE)
2018-02-03 09:01:10.274  INFO 127292 --- [           main] com.javadeveloperzone.SpringBootConfig   : Starting SpringBootConfig on Mahesh with PID 127292 (F:\extrawork\spring-boot\spring-boot-hibernat-jpa-example\target\classes started by Lenovo in F:\extrawork\spring-boot\spring-boot-hibernat-jpa-example)
2018-02-03 09:01:10.292  INFO 127292 --- [           main] com.javadeveloperzone.SpringBootConfig   : No active profile set, falling back to default profiles: default
2018-02-03 09:01:10.430  INFO 127292 --- [           main] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@55740540: startup date [Sat Feb 03 09:01:10 IST 2018]; root of context hierarchy
2018-02-03 09:01:14.257  INFO 127292 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat initialized with port(s): 8080 (http)
2018-02-03 09:01:15.568  INFO 127292 --- [           main] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
2018-02-03 09:01:15.568  INFO 127292 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
  name: default
  ...]
2018-02-03 09:01:15.662  INFO 127292 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.0.12.Final}
2018-02-03 09:01:15.662  INFO 127292 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2018-02-03 09:01:15.662  INFO 127292 --- [           main] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
2018-02-03 09:01:15.753  INFO 127292 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
2018-02-03 09:01:16.017  INFO 127292 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
2018-02-03 09:01:16.078  INFO 127292 --- [           main] o.h.e.j.e.i.LobCreatorBuilderImpl        : HHH000423: Disabling contextual LOB creation as JDBC driver reported JDBC version [3] less than 4
2018-02-03 09:01:16.625  INFO 127292 --- [           main] org.hibernate.tool.hbm2ddl.SchemaUpdate  : HHH000228: Running hbm2ddl schema update
2018-02-03 09:01:16.640  INFO 127292 --- [           main] rmationExtractorJdbcDatabaseMetaDataImpl : HHH000262: Table not found: employee
2018-02-03 09:01:16.640  INFO 127292 --- [           main] rmationExtractorJdbcDatabaseMetaDataImpl : HHH000262: Table not found: employee
2018-02-03 09:01:17.322  INFO 127292 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2018-02-03 09:01:18.362  INFO 127292 --- [           main] s.w.s.m.m.a.RequestMappingHandlerAdapter : Looking for @ControllerAdvice: org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@55740540: startup date [Sat Feb 03 09:01:10 IST 2018]; root of context hierarchy
2018-02-03 09:01:19.154  INFO 127292 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
2018-02-03 09:01:19.169  INFO 127292 --- [           main] com.javadeveloperzone.SpringBootConfig   : Started SpringBootConfig in 9.432 seconds (JVM running for 10.293)

2.8 Output

It will automatically create Employee Table in database:

Spring boot jpa mysql example – Employee Table

Let’s insert some data in the table:

insert into `employee`(`employeeId`,`employeeName`,`employeeRole`) values (1,'Harry','ROLE_ADMIN');
insert into `employee`(`employeeId`,`employeeName`,`employeeRole`) values (2,'Bonny','ROLE_ADMIN');

Let fetch data:

localhost:8080/employeeList

[
    {
        "employeeId": 1,
        "employeeName": "Harry",
        "employeeRole": "ROLE_ADMIN"
    },
    {
        "employeeId": 2,
        "employeeName": "Bonny",
        "employeeRole": "ROLE_ADMIN"
    }
]

localhost:8080/startWithName/Ha

[
    {
        "employeeId": 1,
        "employeeName": "Harry",
        "employeeRole": "ROLE_ADMIN"
    }
]

localhost:8080/findByRole/ROLE_ADMIN

[
    {
        "employeeId": 1,
        "employeeName": "Harry",
        "employeeRole": "ROLE_ADMIN"
    },
    {
        "employeeId": 2,
        "employeeName": "Bonny",
        "employeeRole": "ROLE_ADMIN"
    }
]

3. Conclusion

In above example, We learned that how we can configure spring boot Data JPA with MySQL. Spring boot makes hibernate connection very easy using JPA.

4. References

Spring boot JPA NoSQL database

Spring boot JPA Documentation