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.

 Spring Initializr configuration for Spring JDBCTemplate Example

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
-

Facing issues? Have Questions? Post them here! I am happy to answer!




Author: Rakesh
Author Info:

Rakesh is a seasoned developer with over 10 years of experience in web and app development, and a deep knowledge of operating systems. Author of insightful How-To articles for Code2care.

Follow him on: X














Copyright © Code2care 2023 | Privacy Policy | About Us | Contact Us | Sitemap