Connect to PostgreSQL using Spring Boot JDBC Client and Hikari

The Java Database Connectivity (JDBC) API provides data access from the Java programming language. Using JDBC, any database can be accessed easily and it is most direct way of interacting with database. ORM can also be used to access database but it might be bulk and we don't know what ORM internally does and how it manages database connections. JDBC is simple and direct way of dealing with databases. 

To make developer life easier, earlier version of Spring introduced JdbcTemplate. It is a wrapper over JDBC APIs. JdbcTemplate helps to execute query and returns the result set as objects.  Spring 6.1 introduced JdbcClient which is a fluent style wrapper over JdbcTemplate.

In this article, we will explore how to connect to PostgreSQL from Spring Boot application using JdbcClient and Hikari connection pool library.

Create Spring Boot Application

In Spring Boot starter, select Spring Web and JDBC and create application. Once  the application is created, add the below dependency.

<dependency>
	<groupId>com.zaxxer</groupId>
	<artifactId>HikariCP</artifactId>
</dependency>

<dependency>
	<groupId>org.postgresql</groupId>
	 <artifactId>postgresql</artifactId>
</dependency>

Create DataSource

In Spring, there are many ways to create data source. Let's create data source programatically using annotations.

@Configuration
public class DbConfig {

    @Bean
    DataSource getDataSource() {
		
		PGSimpleDataSource dataSource = new PGSimpleDataSource();
		dataSource.setServerNames(new String[] {"localhost"});
		dataSource.setPortNumbers(new int[] {5432});
		dataSource.setUser("YOUR-DBUSER");
		dataSource.setPassword("YOUR_DBPASSWORD");
		dataSource.setDatabaseName("test");
		
		HikariDataSource hikariDataSource = new HikariDataSource();
		hikariDataSource.setDataSource(dataSource);
		
		return hikariDataSource;
	}
}

@Configuration annotation is used to declare the class as configuration

Use @Bean annotation to the function which returns DataSource. Spring will automatically create DataSource object.

Create PGSimpleDataSource datasource by setting database credentials.

Create HikariDataSource and set PGSimpleDataSource to it. Hikari will manage connection pool.

Create Repository

Create CourseRepository class using @Repository annotation to interact with database using JdbcClient. Datasource object will be created by Spring and injected.

@Repository
public class CourseRepository {
	
	@Autowired
	DataSource datasource;
	
	JdbcClient jdbcClient;
	
	@PostConstruct
	public void init() {
		jdbcClient = JdbcClient.create(datasource);		
	}

}

In the CourseRepository class add below functions to create and fetch data from database.

Create Course

Insert course in to database using SQL insert statement. The parameters are passed based on the index position. Use KeyHolder object to store the Id returned from the INSERT statement.

public Course createCourse(Course course) {
	
	KeyHolder keyHolder = new GeneratedKeyHolder();
	
	jdbcClient.sql("INSERT INTO course(name, description, duration_in_days, created_on) \
                                           values(?, ?, ?, ?) returning id")
			  .param(1, course.getName())
			  .param(2, course.getDescription())
			  .param(3, course.getDuration_in_days())
			  .param(4, Timestamp.from(Instant.now()))
			 .update(keyHolder);
	
	return getCourse(keyHolder.getKey().intValue());
}

Get Courses

Have a function which returns list of all courses and also given a course id, return a particular course.

public Course getCourse(int id) {
	
	Course course = jdbcClient.sql("select * from course where id = ?")
								  .param(1, id)
								  .query(Course.class)
								  .single();
																					
	return course;		
}

public List<Course> getAllCourses() {
	
	List<Course> userList = jdbcClient.sql("select * from course")
								  .query(Course.class)
								  .list();

	return userList;
	
}

Creae Controller

In order to interact with the application, we need Controller class. Lets build a one. Expose an API to create and retrieve Courses.

@RestController
public class CourseController {

	@Autowired 
	CourseRepository studentRepo;
	
	@GetMapping ("/api/courses/{id}")
	public ResponseEntity<Course> getCourse(@PathVariable("id") int id) {
		return ResponseEntity.ok(studentRepo.getCourse(id));
	}
	
	@GetMapping ("/api/courses")
	public ResponseEntity<List<Course>> getAllCourses() {
		return ResponseEntity.ok(studentRepo.getAllCourses());
	}
		
	@PostMapping("/api/courses")
	public ResponseEntity<Course> createCouse(@RequestBody Course course) {
		System.out.println("create course");
		Course newCourse = studentRepo.createCourse(course);
		return ResponseEntity.ok(newCourse);
	}
	
}

Using Curl or any Rest Client, Hit the below API to create and retrieve Courses.

API: http://localhost:8080/api/courses

Method: POST

Request: 
{
  "name": "Learn Java in 30 days",
  "description" : "Learn Java in 30 days",
  "duration_in_days" : 30
}

# Get all courses
API: http://localhost:8080/api/courses
Method: GET

# Get a course based on Id
API: http://localhost:8080/api/courses/8
Method: GET

Conclusion

JdbcClient provides fluent style API to interact with database using JDBC. Hikari is one of the best and fast connection pool library. Using JdbcClient and Hikari, we can interact with database in most efficient way.