Spring Boot: Setting up JDBCTemplate with MySQL Tutorial

In this tutorial you will learn how to set up JDBCTemplate using Spring Boot Starter project.

Step 1: Spring Initializr (https://start.spring.io)

• Go to https://start.spring.io i.e. the Spring Initializr which is a web-based tool that allows developers to quickly generate a new Spring Boot project structure.
• We are choosing Project as Gradle (you may also choose Gradle)
• Language: Java
• Spring Boot: 3.0.5 (choosing the current stable version)
• Project Metadata:
  • Group: org.code2care.jdbctemplateeg
  • Artifact: jdbctemplateexample
  • Name: Spring Boot JDBCTemplate Example
  • Description: Example of Spring Boot JDBC Template by Code2care
  • Package name: org.code2care.jdbctemplateeg.JDBCTemplateExample
  • Packaging: jar
  • Java: 20 (you can choose between JDK 8 - 20 which is the latest)
• Dependencies: JDBC API (SQL) - Database Connectivity API that defines how a client may connect and query a database.

Make sure to hit Generate, the project zip file will get downloaded.

Next you can unzip the project and import it into your favorite IDE - Eclipse, IntelliJ, or VSCode.

Step 2: Configuring MySQL

• Open the application.properties under src -> main -> resources and add the properties for MySQL.
  • spring.datasource.url=jdbc:mysql://localhost/jdbcTemplateDb
  • spring.datasource.username=root
  • spring.datasource.password=your-db-password
  • spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
  • spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
• Make sure you create the jdbcTemplateDb database in your MySQL server
• Add the below dependency in your pom.xml,

  <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.14</version>
  </dependency>

• If you are using Gradle, add this to your build.gradle file,

  implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.14'
  

Step 3: Create your MySQL table and add record

create table message (id int(4), message varchar(255));

insert into message values(1,"Hello World!");

Step 4: Create Mapping class

public class Message {

    private int id;
    private String message;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }
}

Step 5: Create Repository class for JDBCTemplate

@Repository
public class JDBCTemplateRepo {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    private String selectQuery = "Select * from message where id=1";

    public String getMessage() {
       List<Message> messageList =  jdbcTemplate.query(query, new BeanPropertyRowMapper<>(Message.class));
        return  messageList.get(0).getMessage(); //crude! but just for demo
    }

}

Step 6: Create a CommandLineRunner Class

@Component
public class JDBCTemplateRunner implements CommandLineRunner {

    @Autowired
    JDBCTemplateRepo jdbcTemplateRepo;

    @Override
    public void run(String... args) throws Exception {

        System.out.println(jdbcTemplateRepo.getMessage());

    }
}

We are all set! All you need to do is run the class with main method - for me its JdbctemplateExampleApplication

You should see "Hello World!" printed in the console.

Output:

...
2023-04-13T15:39:33.599+05:30  INFO 73693 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2023-04-13T15:39:33.664+05:30  INFO 73693 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@6daf2337
2023-04-13T15:39:33.664+05:30  INFO 73693 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
Hello World!
2023-04-13T15:39:33.687+05:30  INFO 73693 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2023-04-13T15:39:33.688+05:30  INFO 73693 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0