Changing embedded database in Spring Boot from H2 to MySQL

Question

Is there a way to change database in application that is almost done? I am encoutering many problems with H2 that does not occur in MySQL. For example ALTER TABLE yourtable AUTO_INCREMENT = 1; does not work and instead I had to use restart at which does not work as good as MySQL version. Also now I am having problems with datediff. So is it possible to change database in ongoing application?

Answer 1

Yes you can. Include dependencies for MySql in your pom file:

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

Create your repository interface for mysql that extends JpaRepository:

public interface SqlDAO  extends JpaRepository<YourPOJO,Long>{
   // you can use JpaRepository methods out of the box or write custom ones
}

Add properties for your sql, you can use .properties or .yml files. I use yaml:

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/coolDB
    username: root
    password: 123456
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true

Don't forget to run MySql database itself and you good to go. Your Service now should be using your repository interface to communicate with Sql.

Here is a link for Jpa documentation and how to create your custom methods: https://docs.spring.io/spring-data/jpa/docs/1.4.1.RELEASE/reference/html/jpa.repositories.html

Edit: you have to create database in mysql console manually, Spring won't do it for you. You can include .sql file into your resource directory to create dummy data or set sql settings further on, Spring will run that for you.