Show List
Spring Boot JDBC API
Spring Boot JDBC API is a feature of the Spring Boot framework that provides support for connecting to relational databases using the Java Database Connectivity (JDBC) API. JDBC is a standard API for accessing relational databases in Java, and provides a set of APIs for performing common database operations, such as executing SQL statements and retrieving results.
Spring Boot JDBC API makes it easy to connect to a relational database and perform common database operations in your application. It provides a simple and consistent API for accessing databases, abstracting away the details of the underlying JDBC driver and connection pool.
In the example below, we are going to connect to database using JDBC template from the Spring Boot application. The JdbcTemplate class is a convenient utility class provided by the Spring JDBC API that makes it easy to perform common database operations.
1. Go to Spring initializr website and create a Java Maven Project with dependencies: JDBC API, MySQL and Web. Download the project zip and extract to a folder.
2. Import the project into IDE (I am using IntelliJ Idea). Pom.xml:
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
2. Create Student class in the com.example.demo.model package.
package com.example.demo.model;
public class Student {
private String student_id;
private String name;
private String grade;
public Student(){
}
public Student( String student_id, String name, String grade) {
this.student_id = student_id;
this.name = name;
this.grade = grade;
}
public String getStudent_id() {
return student_id;
}
public void setStudent_id(String student_id) {
this.student_id = student_id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGrade() {
return grade;
}
public void setGrade(String grade) {
this.grade = grade;
}
@Override
public String toString() {
return "Student{" +
"student_id=" + student_id +
", name='" + name + '\'' +
", grade='" + grade + '\'' +
'}';
}
}
3. Create StudentDao class in the com.example.demo.dao package.
package com.example.demo.dao;
import com.example.demo.model.Student;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public class StudentDao {
@Autowired
private JdbcTemplate jdbcTemplate;
private static final Logger LOGGER = LoggerFactory.getLogger(StudentDao.class);
public Student findStudentById(String studentId){
Student st = null;
String sql = "Select * from student where student_id = ?";
try{
st = jdbcTemplate.queryForObject(sql,Student.class);
}catch (Exception ex){
LOGGER.info("Student for given Id is not found");
}
return st;
}
public List<Student> getAllStudents(){
String sql = "Select * from student ";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Student>(Student.class));
}
public int insertStudent(Student st){
String sql = "insert into student values (?,?,?)";
return jdbcTemplate.update(sql, st.getStudent_id(), st.getName(), st.getGrade());
}
}
4. Create StudentService class in the com.example.demo.service package. In this class there are two methods. One to add three students and other to get all the students.
package com.example.demo.service;
import com.example.demo.dao.StudentDao;
import com.example.demo.model.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class StudentService {
@Autowired
StudentDao dao;
public void getAllStudents(){
List<Student> list = dao.getAllStudents();
list.stream().forEach( (st)-> System.out.println(st));
}
public void AddStudents(){
dao.insertStudent(new Student("1","Ana", "One"));
dao.insertStudent(new Student("2","Bob", "Two"));
dao.insertStudent(new Student("3","Claire", "One"));
}
}
5. In the local mysql database, create student table with below columns.
6. Add below line in the application.properties file to connect to local MYSQL database.
spring.datasource.url=jdbc:mysql://localhost:3306/demo
spring.datasource.username=admin
spring.datasource.password=admin
7. Update the main class to implement CommandLineRunner to call service methods.
package com.example.demo;
import com.example.demo.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class DemoApplication implements CommandLineRunner {
@Autowired
StudentService service;
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
@Override
public void run(String... args) throws Exception {
service.AddStudents();
service.getAllStudents();
}
}
6. Run the Spring Boot application. Log from the getAllStudents method can be seen in the application.
"C:\Program Files\Java\jdk-11.0.15\bin\java.exe" "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2021.2\lib\idea_rt.jar=61662:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2021.2\bin" -Dfile.encoding=UTF-8 -classpath C:\Users\mail2\Downloads\demo\target\classes;D:\.m2\repository\org\springframework\boot\spring-boot-starter-jdbc\2.7.3\spring-boot-starter-jdbc-2.7.3.jar;D:\.m2\repository\org\springframework\boot\spring-boot-starter\2.7.3\spring-boot-starter-2.7.3.jar;D:\.m2\repository\org\springframework\boot\spring-boot\2.7.3\spring-boot-2.7.3.jar;D:\.m2\repository\org\springframework\boot\spring-boot-autoconfigure\2.7.3\spring-boot-autoconfigure-2.7.3.jar;D:\.m2\repository\org\springframework\boot\spring-boot-starter-logging\2.7.3\spring-boot-starter-logging-2.7.3.jar;D:\.m2\repository\ch\qos\logback\logback-classic\1.2.11\logback-classic-1.2.11.jar;D:\.m2\repository\ch\qos\logback\logback-core\1.2.11\logback-core-1.2.11.jar;D:\.m2\repository\org\apache\logging\log4j\log4j-to-slf4j\2.17.2\log4j-to-slf4j-2.17.2.jar;D:\.m2\repository\org\apache\logging\log4j\log4j-api\2.17.2\log4j-api-2.17.2.jar;D:\.m2\repository\org\slf4j\jul-to-slf4j\1.7.36\jul-to-slf4j-1.7.36.jar;D:\.m2\repository\jakarta\annotation\jakarta.annotation-api\1.3.5\jakarta.annotation-api-1.3.5.jar;D:\.m2\repository\org\yaml\snakeyaml\1.30\snakeyaml-1.30.jar;D:\.m2\repository\com\zaxxer\HikariCP\4.0.3\HikariCP-4.0.3.jar;D:\.m2\repository\org\slf4j\slf4j-api\1.7.36\slf4j-api-1.7.36.jar;D:\.m2\repository\org\springframework\spring-jdbc\5.3.22\spring-jdbc-5.3.22.jar;D:\.m2\repository\org\springframework\spring-beans\5.3.22\spring-beans-5.3.22.jar;D:\.m2\repository\org\springframework\spring-tx\5.3.22\spring-tx-5.3.22.jar;D:\.m2\repository\org\springframework\boot\spring-boot-starter-web\2.7.3\spring-boot-starter-web-2.7.3.jar;D:\.m2\repository\org\springframework\boot\spring-boot-starter-json\2.7.3\spring-boot-starter-json-2.7.3.jar;D:\.m2\repository\com\fasterxml\jackson\core\jackson-databind\2.13.3\jackson-databind-2.13.3.jar;D:\.m2\repository\com\fasterxml\jackson\core\jackson-annotations\2.13.3\jackson-annotations-2.13.3.jar;D:\.m2\repository\com\fasterxml\jackson\core\jackson-core\2.13.3\jackson-core-2.13.3.jar;D:\.m2\repository\com\fasterxml\jackson\datatype\jackson-datatype-jdk8\2.13.3\jackson-datatype-jdk8-2.13.3.jar;D:\.m2\repository\com\fasterxml\jackson\datatype\jackson-datatype-jsr310\2.13.3\jackson-datatype-jsr310-2.13.3.jar;D:\.m2\repository\com\fasterxml\jackson\module\jackson-module-parameter-names\2.13.3\jackson-module-parameter-names-2.13.3.jar;D:\.m2\repository\org\springframework\boot\spring-boot-starter-tomcat\2.7.3\spring-boot-starter-tomcat-2.7.3.jar;D:\.m2\repository\org\apache\tomcat\embed\tomcat-embed-core\9.0.65\tomcat-embed-core-9.0.65.jar;D:\.m2\repository\org\apache\tomcat\embed\tomcat-embed-el\9.0.65\tomcat-embed-el-9.0.65.jar;D:\.m2\repository\org\apache\tomcat\embed\tomcat-embed-websocket\9.0.65\tomcat-embed-websocket-9.0.65.jar;D:\.m2\repository\org\springframework\spring-web\5.3.22\spring-web-5.3.22.jar;D:\.m2\repository\org\springframework\spring-webmvc\5.3.22\spring-webmvc-5.3.22.jar;D:\.m2\repository\org\springframework\spring-aop\5.3.22\spring-aop-5.3.22.jar;D:\.m2\repository\org\springframework\spring-context\5.3.22\spring-context-5.3.22.jar;D:\.m2\repository\org\springframework\spring-expression\5.3.22\spring-expression-5.3.22.jar;D:\.m2\repository\mysql\mysql-connector-java\8.0.30\mysql-connector-java-8.0.30.jar;D:\.m2\repository\org\springframework\spring-core\5.3.22\spring-core-5.3.22.jar;D:\.m2\repository\org\springframework\spring-jcl\5.3.22\spring-jcl-5.3.22.jar com.example.demo.DemoApplication
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.7.3)
2022-09-11 21:57:11.527 INFO 5704 --- [ main] com.example.demo.DemoApplication : Starting DemoApplication using Java 11.0.15 on sm15 with PID 5704 (C:\Users\mail2\Downloads\demo\target\classes started by mail2 in C:\Users\mail2\Downloads\demo)
2022-09-11 21:57:11.527 INFO 5704 --- [ main] com.example.demo.DemoApplication : No active profile set, falling back to 1 default profile: "default"
2022-09-11 21:57:13.201 INFO 5704 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http)
2022-09-11 21:57:13.217 INFO 5704 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2022-09-11 21:57:13.217 INFO 5704 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.65]
2022-09-11 21:57:13.357 INFO 5704 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2022-09-11 21:57:13.357 INFO 5704 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1752 ms
2022-09-11 21:57:13.513 INFO 5704 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2022-09-11 21:57:13.914 INFO 5704 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2022-09-11 21:57:14.446 INFO 5704 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2022-09-11 21:57:14.461 INFO 5704 --- [ main] com.example.demo.DemoApplication : Started DemoApplication in 3.521 seconds (JVM running for 4.072)
Student{student_id=1, name='Ana', grade='One'}
Student{student_id=2, name='Bob', grade='Two'}
Student{student_id=3, name='Claire', grade='One'}
The inserted records can also be viewed on the database:
Source Code:
https://github.com/NumeroUnoDeveloper/Spring-Boot-JDBC
Leave a Comment