Spring Boot - MySQL

Posted on In

Spring Boot Integration with MySQL

Maven Dependencies

1
2
3
4
5
6
7
8
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jpa</artifactId>
</dependency>

Run MySQL using Docker Compose

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
version: '3.1'
services:
  mysql:
    image: mysql:latest
    container_name: mysql
    restart: always
    command: --lower_case_table_names=1 
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: test
      MYSQL_USER: test
      MYSQL_PASSWORD: test
    ports:
      - "127.0.0.1:3306:3306"
  adminer:
    image: adminer
    restart: always
    ports:
      - 8000:8080

use docker-compose up -d to start the MySQL and Adminer

Configuration for a MySQL dataSource

application.properties

1
2
3
4
5
6
7
8
9
10
11
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=test
spring.datasource.password=test
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

# ddl-auto values (create, update, create-drop, validate)
spring.jpa.hibernate.ddl-auto=create-drop

ddl-auto is used to automatically create the schema (DDL) based on the Entity classes. The possible values are:

  • update: update the schema, preserving previous data
  • create: This option will drop the existing schema and create a new one every time the application starts. It does not drop the schema when the application stops.
  • create-drop: This option will drop the existing schema and create a new one every time the application starts, just like create. However, it will also drop the schema when the application stops.
  • validate: validate the schema, make no changes to the database, if schema is not valid, SchemaManagementException will be thrown at runtime and the application will not start
  • none: do nothing with the schema, make no changes to the database

for production, it is recommended to use validate or none

Jpa Entity and Repository

Entity

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import lombok.Data;

@Entity
@Data
@NoArgsConstructor
@Accessors(chain = true)
public class User {
    @Id
    private long id;
    private String name;
    private String email;
    private String password;
}

Repository

1
2
3
@Repository
public interface UserRepo extends JpaRepository<User, Long> {
}

To test

1
2
3
4
5
6
7
8
9
10
@Bean
public CommandLineRunner run(UserRepo userRepo){
	return new CommandLineRunner(){
		@Override
		public void run(String... args) throws Exception {
			userRepo.save(new User().setName("John Doe").setEmail("abc@def.com").setPassword("1234"));
			userRepo.findAll().forEach(System.out::println);
		}
	};
}

Output in theconsole

1
User(id=0, name=John Doe, email=abc@def.com, password=1234)