Spring Boot JPA MySQL Sample App – Code Example

Creating a Spring Boot app with MySQL data source is something every Java developer come across when thinking of creating a quick POC or app. This post is aimed to provide quick code samples which can be used to quickly create a MySQL-based Spring boot app.

In this post, you will learn about code examples in relation to getting setup with Spring Boot app which interacts with MySQL database. The following are some of the topics covered:

• Define properties in application.properties file
• Create/Configure Database-related Beans
• Create Entity Classes (Domain)
• Create Database Implementation Classes (DAOs)
• Define Service Classes for interacting with Data Access Objects
• Invoke service class from SpringBoot main program
• Include appropriate libraries in POM.xml

Define properties in application.properties file

Provide mysql database details in the application.properties file.

app.datasource.driverClassName=com.mysql.jdbc.Driver
app.datasource.url=jdbc:mysql://mysqlEndpoint:3306/mysqlDBName
app.datasource.username=mysqlDBUsername
app.datasource.password=mysqlDBPassword
#
# Show or not log for each sql query
spring.jpa.show-sql = true
#
# Naming strategy
#
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
#
# Allows Hibernate to generate SQL optimized for a particular DBMS
#
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

Create/Configure Database-related Beans

Load database related beans. Pay attention to some of the following details:

• @Value annotation used to assign default values which are read from application.properties file.
• @EntityScan annotation used for configuring the base packages used by auto-configuration when scanning for entity/domain classes
• @EnableTransactionManagement annotation to enable annotation-driven transaction management capability
• DataSource bean to configure the data source
• SessionFactory bean created using LocalSessionFactoryBuilder which is a spring-provided extension of the standard Hibernate Configuration class.
• TransactionManager bean of type, HibernateTransactionManager which is passed a reference to SessionFactory. HibernateTransactionManager requires the reference to SessionFactory as like DataSourceTransactionManager which needs a reference to DataSource. Read greater details on this page
• DataSourceInitializer bean which is used to set up a database during initialization and clean up a database during destruction.

@Configuration
@EntityScan("com.vflux.rbot.services.account.domain")
@EnableTransactionManagement
@PropertySource("classpath:application.properties")
public class MySQLAppConfig {

    @Value("${app.datasource.driverClassName}") String driverClassName;
    @Value("${app.datasource.url}") String url;
    @Value("${app.datasource.username}") String username;
    @Value("${app.datasource.password}") String password;

    @Bean(name = "dataSource")
    public DataSource getDataSource() {
        DataSource dataSource = DataSourceBuilder
                .create()
                .username(username)
                .password(password)
                .url(url)
                .driverClassName(driverClassName)
                .build();
        return dataSource;
    }

    @Bean(name = "sessionFactory")
    public SessionFactory getSessionFactory(DataSource dataSource) {
        LocalSessionFactoryBuilder sessionBuilder = new LocalSessionFactoryBuilder(dataSource);
        sessionBuilder.scanPackages("com.vflux.rbot.services.account.domain");
        return sessionBuilder.buildSessionFactory();
    }

    @Bean(name = "transactionManager")
    public HibernateTransactionManager getTransactionManager(
            SessionFactory sessionFactory) {
        HibernateTransactionManager transactionManager = new HibernateTransactionManager(
                sessionFactory);
        return transactionManager;
    }   

    @Bean
    public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
        final DataSourceInitializer initializer = new DataSourceInitializer();
        initializer.setDataSource(dataSource);
        return initializer;
    }    
}

Create Entity Classes (Domain)

Create entity classes representing the tables. The following represents an example of two entity classes, one representing an account table and other representing a user table which has a foreign key relationship to account table.

Pay attention to some of the following:

• Usage of NamedQuery for defining queries. Note that name must be unique across different entities.
• Usage of entity name such as Account instead of table name account in the NamedQuery definitions. If not done, it will throw exception related to mapping not found.
• Column name is mapped using @Column(name=columnName) annotation.
• The variable name is of camelcase format. If the column name is created_by, member variable name is createdBy. If the column name is created_on, member variable name is createdOn

@Entity
@Table(name="account")
@NamedQueries({
    @NamedQuery(
        name = "findAccountById",
        query = "from Account a where a.id = :id"
        ),
})
public class Account {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id; 
    @Column(name = "code") private String code;
    @Column(name = "created_by") private int createdBy;
    @Column(name = "created_on") private Timestamp createdOn;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    public int getCreatedBy() {
        return createdBy;
    }
    public void setCreatedBy(int createdBy) {
        this.createdBy = createdBy;
    }
    public Timestamp getCreatedOn() {
        return createdOn;
    }
    public void setCreatedOn(Timestamp createdOn) {
        this.createdOn = createdOn;
    }
}

Pay attention to some of the following:

• The foreign key relationship is represented using @JoinColumn annotation
• Usage of member variable name (account) such as u.account.id when creating NamedQuery definitions for related entities.

@Entity
@Table(name="user")
@NamedQueries({
    @NamedQuery(
        name = "findUserById",
        query = "from User u where u.id = :id"
        ),
    @NamedQuery(
        name = "findUsersByAccountId",
        query = "from User u where u.account.id = :id"
        ),
})
public class User {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id; 
    @Column(name = "email_address") private String emailAddress;
    @Column(name = "password") private String password;
    @Column(name = "name") private String name;
    @Column(name = "created_by") private int createdBy;
    @Column(name = "created_on") private Timestamp createdOn;

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name = "account_id") private Account account;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public Account getAccount() {
        return account;
    }
    public void setAccount(Account account) {
        this.account = account;
    }
    public String getEmailAddress() {
        return emailAddress;
    }
    public void setEmailAddress(String emailAddress) {
        this.emailAddress = emailAddress;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getCreatedBy() {
        return createdBy;
    }
    public void setCreatedBy(int createdBy) {
        this.createdBy = createdBy;
    }
    public Timestamp getCreatedOn() {
        return createdOn;
    }
    public void setCreatedOn(Timestamp createdOn) {
        this.createdOn = createdOn;
    }    
}

Create Database Implementation Classes (DAOs)

Pay attention to DAO interface for defining one or more APIs.

public interface AccountDAO {
    Account findById(int id);
}

Pay attention to some of the following:

• @Repository annotation used for representing the repository class used for storing, retrieving and search from a collection of objects.
• SessionFactory bean is injected in the constructor. It is used for executing queries later in the query methods.

@Repository
public class AccountDAOImpl implements AccountDAO {

    final static Logger logger = LoggerFactory.getLogger(AccountDAOImpl.class);

    private SessionFactory sessionFactory;

    @Autowired 
    public AccountDAOImpl(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    @Override
    public Account findById(int id) {
        Session session = this.sessionFactory.getCurrentSession();
        TypedQuery<Account> query = session.getNamedQuery("findAccountById");  
        query.setParameter("id", id);
        Account account = query.getSingleResult();
        return account;
    }
}

Define Service Classes for interacting with Data Access Objects

Pay attention to account service interface which defines findById API.

public interface AccountService {
    Account findById(int id);
}

Pay attention to some of the following:

• @Service annotation is used to indicate a service implementation class or a business service facade. This annotation serves as a specialization of @Component, allowing for implementation classes to be autodetected through classpath scanning.
• @Transactional annotation to declare transaction configuration

@Service
@Transactional
public class AccountServiceImpl implements AccountService {

    private AccountDAO accountDAO;

    @Autowired
    public AccountServiceImpl(AccountDAO accountDAO) {
        this.accountDAO = accountDAO;
    }


    @Override
    public Account findById(int id) {
        return this.accountDAO.findById(id);
    }

}

Invoke service class from SpringBoot main program

Pay attention to some of the following:

• AccountService is auto-wired
• CommandLineRunner is implemented for running the code

@SpringBootApplication
public class RecruiterbotApplication implements CommandLineRunner {

    @Autowired AccountService accountService;

    public static void main(String[] args) {
        SpringApplication app = new SpringApplication(RecruiterbotApplication.class);
        app.run(args);
    }

    @Override
    public void run(String... arg0) throws IOException, URISyntaxException {
        Account account = this.accountService.findById(1);
        System.out.println("Account Details: " + account.getCode() + ", " + account.getCompany());
    }
}

Include appropriate libraries in POM.xml

Include the following in POM.xml for working with MySQL related code.

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

Summary

In this post, you learned about creating a spring boot app with mysql as the data source. Did you find this article useful? Do you have any questions or suggestions about this article? Leave a comment and ask your questions and I shall do my best to address your queries.