Spring Boot JDBC + MySQL + HikariCP example

In The previous post Getting Started · Building an Application with Spring Boot  AND Building an Application with Spring Boot | PART 2 We have already learn that how to create a spring-boot application

In this post we will integrate MySql database with spring-boot application. We persist the Student data in a Table named student in MySql. And our spring-boot application will query the database to fetch the required data. Following is the project structure.

Now let us add the required dependency :

We will add spring-boot-starter-jdbc and exclude tomcat jdbc connection pool to use HikariCP. Then we will add the HikariCP. Spring boot will automatically starts using HikariCP as it will not find the tomcat jdbc connection pool. Then we will add mysql-connector-java to connect MySql database. Following is our updated pom.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
         http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.tuturself</groupId>
    <artifactId>spring-boot-jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.1.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.jayway.jsonpath</groupId>
            <artifactId>json-path</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- exclude tomcat jdbc connection pool -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.tomcat</groupId>
                    <artifactId>tomcat-jdbc</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- Now tomcat-jdbc excluded, Spring Boot will use HikariCP automatically -->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </dependency>

        <!-- For MySQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

    </dependencies>

    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>


    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>spring-releases</id>
            <url>https://repo.spring.io/libs-release</url>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>spring-releases</id>
            <url>https://repo.spring.io/libs-release</url>
        </pluginRepository>
    </pluginRepositories>
</project>

Definition of JdbcTemplate :

We will create a StudentRepository and autowire a JdbcTemplate in it.Spring Boot will register the JdbcTemplate bean automatically once it found the injected bean.

package com.tuturself.spring.boot.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.tuturself.spring.boot.model.Student;

@Repository
public class StudentRepository {

  @Autowired
  private JdbcTemplate jdbcTemplate;

  // Find all students, By Java 8 we can create a custom RowMapper 
  public List<Student> findAll() {

	List<Student> result = jdbcTemplate.query
       ("SELECT studentId, firstName,lastName,email FROM student",rs,rowNum) -> 
          new Student(rs.getInt("studentId"), 
		    rs.getString("firstName"), 
            rs.getString("lastName"),
		    rs.getString("email")
          )
       );
	return result;
  }
}

Student Model :

To model the Student representation, we create a resource representation class. Provide a plain old java object with fields, constructors, and accessors for the attributes of Student. Following is our Student representation class.

package com.tuturself.spring.boot.model;

public class Student {

	private Integer studentId;
	private String firstName;
	private String lastName;
	private String email;

	public Student(Integer studentId, String firstName, String lastName, String email) {
		super();
		this.studentId = studentId;
		this.firstName = firstName;
		this.lastName = lastName;
		this.email = email;
	}

	public Integer getStudentId() {
		return studentId;
	}

	public void setStudentId(Integer studentId) {
		this.studentId = studentId;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}
}

Database Creation :

Now we will create a table named student and insertes some data in it. Although spring-boot can automatically do this for us as a database intialization step which we will cover in some other post.

CREATE TABLE `myappdb`.`student` (
`studentId` INT NOT NULL AUTO_INCREMENT,
`firstName` VARCHAR(100) NOT NULL,
`lastName` VARCHAR(100) NULL,
`email` VARCHAR(100) NULL,
PRIMARY KEY (`studentId`));

INSERT INTO `myappdb`.`student` (`firstName`, `lastName`, `email`) 
VALUES ('Ninja', 'Panda', '[email protected]');
INSERT INTO `myappdb`.`student` (`firstName`, `lastName`, `email`) 
VALUES ('Ninja', 'AK', '[email protected]');
INSERT INTO `myappdb`.`student` (`firstName`, `lastName`, `email`)
VALUES ('Master', 'Shifu', '[email protected]');

DEFINING CONFIGURATION: 

FROM PROPERTIES TO YAML : There are dozens of ways to handle externalized configuration in an application or service. Over the years Spring has provided quite a few, and recently the @Value and @Profile annotations have started to bring some sanity to the situation by attempting to minimize the developer’s interaction with the filesystem to read what should be readily available. With the advent of Spring Boot there are a couple new interesting twists – YAML files and @ConfigurationProperties.

First let’s look at YAML. The non-Java world has been using YAML format for quite some time, while the Java world appears to have been stuck on the .properties file and format. Properties files can now be a relic of the past if you so choose, as Spring Boot gives us the option to configure an application for all profiles within one file – application.yml. With the YAML file format you define sections that represent the different profiles. For example, consider this application.yml:

spring:
  profiles.active: default
---
spring:
  profiles: default
spring.datasource:
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://localhost:3306/myappdb?autoReconnect=true
  username: root
  password: root
management:
  security:
    enabled: true
    role: ADMIN
---
spring:
  profiles: qa
spring.datasource:
  url: jdbc:mysql://qa.myapp.com:3306/myappdb?autoReconnect=true
  username: qauser
  password: qapassword

Note a few things about this format – there’s a default active profile defined in the first section, followed by the default profile itself, followed by the qa profile (separated by “—“). There is no longer the namespacing format for each property, but an indentation-based markup to delineate hierarchy (yaml is space-sensitive). Also note that that in the qa profile we don’t re-define the management configuration, it is inherited, whereas we want to override the datasource url, password and user, but not the driver.

If we did nothing more than this the application configuration would default to the default profile, which would be fine in dev mode, but when deployed to the qa environment, we would pass the -Dspring.profiles.active=qa to the command line params for that profile to take effect. You can have multiple profiles alive at the same time too, otherwise it would have been called spring.profile.active ?? So who wins if there are multiple overrides? The lower you are in the yml file, the more override power you have, so that’s one way to think about how you organize your configuration.

This is all well and good, however what happens when the default profile is NOT a great profile for some of the development or QA team, and they want their own overrides? This is where you do need to resurrect the properties file, but this time for great justice. Your application.yml should be checked in, however you don’t want each and every team member checking in their own little overrides, or the file will get unwieldy and nobody will be happy. The trick is to create an application-.properties locally and exclude it (or all properties files) from your source control (.svnignore, .gitignore). With this file in the classpath you can reference the profile in your startup just as we did for qa, EG: -Dspring.profiles.active=default,dilbert.

But this trick is not just for developers. It’s never a good idea to check in production keys/tokens/secrets to your source control for all to see (unless you have absolutely no controls in place or don’t care) so this is a great mechanism for operations and/or SCM staff to have their own properties override file that contains all the sensitive content and let’s them control who has access to it.

SpringBootApplication:

Following is our SpringBootApplication class StudentSearchApplication which is registering the DataSource.

package com.tuturself.spring.boot;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class StudentSearchApplication {
	
  @Autowired
  DataSource dataSource;

  public static void main(String[] args) throws Exception {
	SpringApplication.run(StudentSearchApplication.class, args);
  }
}

Now the API class:

We didnot made any chnage in this class except the searchStudent method name and signature to match our new Repository.

package com.tuturself.spring.boot.api;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.tuturself.spring.boot.model.Student;
import com.tuturself.spring.boot.service.StudentRepository;

@RestController
public class StudentAPI {

	@Autowired
	private StudentRepository studentRepository;

	@RequestMapping("/students")
	public List searchStudent() {
		List students = studentRepository.findAll();
		return students;
	}
}

Running the example : At this point our application should work. Since we have used the spring-boot-starter-parent POM we have a useful run goal that we can use to start the application. Type mvn spring-boot:run from the root project directory to start the application/ or you can build the application and run the main method in Eclipse: It will print like following in console.

 .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.5.1.RELEASE)

 If you open a web browser to http://localhost:8080/students you should see the following output:

[    
  {"studentId":1,"firstName":"Ninja","lastName":"Panda","email":"[email protected]"},
  {"studentId":2,"firstName":"Ninja","lastName":"AK","email":"[email protected]"},
  {"studentId":3,"firstName":"Master","lastName":"Shifu","email":"[email protected]"}
]

To download the complete code from GitHub Click Here
 

Source of DEFINING CONFIGURATION section is : https://hoserdude.com/2014/06/19/spring-boot-configurationproperties-and-profile-management-using-yaml/

spring 12

FOLLOW US ON LinkedIn



Explore Tutu'rself