Java + Spring JDBC Template + Gradle Example

This is a very basic getting started Spring JDBC Template example, you can download the code from GitHub,

GitHub Repo Link: https://github.com/code2care/string-jdbc-template

Prerequisite

• Java 8+
• Gradle
• IDE - Eclipse/IntelliJ/VS Code
• MySQL/Oracle/Postgres DB Connector jar
• Spring Core + Context + JDBC dependencies.

Setting up Gradle Project

Create a Gradle project and add the below in the build.gradle dependencies,

dependencies {
    implementation group: 'org.springframework', name: 'spring-context', version: '5.3.22'
    implementation group: 'org.springframework', name: 'spring-core', version: '5.3.22'
    implementation group: 'org.springframework', name: 'spring-jdbc', version: '5.3.22'
    implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.30'
}

Based on what database you are using add the connector - in this example, we have used MySQL, you can get all these dependencies strings from https://mvnrepository.com

The Project Structure:

Create the Database Table with some records:

Let's create a simple table student,

CREATE TABLE `student` (
  `student_id` int NOT NULL,
  `student_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`student_id`));

insert into student values(1, 'Sam');

Create Properties file:

mysql_driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/my_uat
db_user=root
db_pwd=root123

Create Application Config class:

package config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
@ComponentScan("dao")
@PropertySource("classpath:db.properties")
public class AppConfig {

	@Autowired
	Environment environment;

	@Bean
	DataSource dataSource() {
		DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
		driverManagerDataSource.setUrl(environment.getProperty("url"));
		driverManagerDataSource.setUsername(environment.getProperty("db_user"));
		driverManagerDataSource.setPassword(environment.getProperty("db_pwd"));
		driverManagerDataSource.setDriverClassName(environment.getProperty("mysql_driver"));
		return driverManagerDataSource;
	}
}

Create the Student Pojo and Dao and Dao Impl:

package entities;

public class Student {

    private int studentId;
    private String studentName;

    public int getStudentId() {
        return studentId;
    }

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

    public String getStudentName() {
        return studentName;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }

    @Override
    public String toString() {
        return "Student{" +
                "studentId=" + studentId +
                ", studentName='" + studentName + '\'' +
                '}';
    }
}

package dao;

import entities.Student;

public interface StudentDao {
	Student getStudentById(int studentId);
}

package dao;

import javax.sql.DataSource;

import entities.Student;
import entities.StudentMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class StudentDaoImpl implements StudentDao {

	JdbcTemplate jdbcTemplate;

	@Autowired
	public StudentDaoImpl(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Override
	public Student getStudentById(int studentId) {
		return jdbcTemplate.queryForObject("select * from student where student_id = ?", new Object[] { studentId }, new StudentRowMapper());
	}
}

package entities;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class StudentRowMapper implements RowMapper<Student> {

	public Student mapRow(ResultSet resultSet, int noOfRows) throws SQLException {

		Student student = new Student();
		student.setStudentId(resultSet.getInt("student_id"));
		student.setStudentName(resultSet.getString("student_name"));
		return student;
	}
}

Testing our Results

import config.AppConfig;
import dao.StudentDao;
import entities.Student;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

public class SpringJdbcTemplateExample {
	public static void main(String[] args) {
		AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext(AppConfig.class);
		StudentDao studentDao = context.getBean(StudentDao.class);
		Student student = studentDao.getStudentById(1);
		System.out.println(student);
		context.close();
	}
}

Student{studentId=1, studentName='Sam'}