Implementing JDBC with Spring Framework

Creating a database table for student records:

CREATE DATABASE STUDENT;
USE STUDENT;

CREATE TABLE STUDENT(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL,
    AGE INT NOT NULL,
    SEX TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL
)AUTO_INCREMENT = 100000;

INSERT INTO STUDENT(NAME,AGE,SEX) VALUES("STEVEN",31,1);
INSERT INTO STUDENT(NAME,AGE) VALUES("LUCY",28);

Required dependencies for database connectivity:

Database driver packages: mysql-connector-java-5.1.39-bin.jar, ojdbc5.jar
Spring JDBC packages: spring-jdbc-4.2.1.RELEASE.jar, spring-tx-4.2.1.RELEASE.jar

Approach 1: Configuration File Method

Entity Class

package com.example.spring.data.entity;

import java.io.Serializable;

public class Student implements Serializable {
    private static final long serialVersionUID = 1L;
    
    private int id;
    private String name;
    private int age;
    private int sex;
    
    public Student() {
        // Default constructor
    }
    
    // Getters and setters
    public int getId() {
        return id;
    }
    
    public void setId(int id) {
        this.id = id;
    }
    
    public String getName() {
        return name;
    }
    
    public void setName(String name) {
        this.name = name;
    }
    
    public int getAge() {
        return age;
    }
    
    public void setAge(int age) {
        this.age = age;
    }
    
    public int getSex() {
        return sex;
    }
    
    public void setSex(int sex) {
        this.sex = sex;
    }
}

DAO Layer Interface and Implementation

DAO Interface:

package com.example.spring.data.dao;

import java.util.List;
import java.util.Map;

import com.example.spring.data.entity.Student;

public interface StudentRepository {
    
    boolean addStudent(Student student);
    
    boolean deleteStudent(Integer studentId);
    
    boolean updateStudent(Student student);
    
    List> getStudentMapList();
    
    List<Student> getAllStudents();
}

DAO Implementation:

package com.example.spring.data.dao.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.example.spring.data.dao.StudentRepository;
import com.example.spring.data.entity.Student;

public class StudentRepositoryImpl implements StudentRepository {
    
    private JdbcTemplate jdbcTemplate;
    
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public boolean addStudent(Student student) {
        String sql = "INSERT INTO STUDENT(NAME,AGE,SEX) VALUES(?,?,?)";
        int rows = jdbcTemplate.update(sql, student.getName(), student.getAge(), student.getSex());
        return rows > 0;
    }

    @Override
    public boolean deleteStudent(Integer studentId) {
        String sql = "DELETE FROM STUDENT WHERE ID = ?";
        int rows = jdbcTemplate.update(sql, studentId);
        return rows > 0;
    }

    @Override
    public boolean updateStudent(Student student) {
        String sql = "UPDATE STUDENT SET NAME=?, AGE=?, SEX=? WHERE ID=?";
        int rows = jdbcTemplate.update(sql, student.getName(), student.getAge(), student.getSex(), student.getId());
        return rows > 0;
    }

    @Override
    public List> getStudentMapList() {
        return jdbcTemplate.queryForList("SELECT ID,NAME,AGE,SEX FROM STUDENT");
    }

    @Override
    public List<Student> getAllStudents() {
        String sql = "SELECT ID, NAME, AGE, SEX FROM STUDENT";
        // Using BeanPropertyRowMapper for automatic mapping
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Student>(Student.class));
    }
}

Service Layer Interface and Implementation

Service Interface:

package com.example.spring.data.service;

import java.util.List;
import java.util.Map;

import com.example.spring.data.entity.Student;

public interface StudentService {
    
    boolean addStudent(Student student);
    
    boolean deleteStudent(Integer studentId);
    
    boolean updateStudent(Student student);
    
    List> getStudentMapList();
    
    List<Student> getAllStudents();
}

Service Implementation:

package com.example.spring.data.service.impl;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;

import com.example.spring.data.dao.StudentRepository;
import com.example.spring.data.entity.Student;
import com.example.spring.data.service.StudentService;

public class StudentServiceImpl implements StudentService {
    
    @Autowired
    private StudentRepository studentRepository;

    @Override
    public boolean addStudent(Student student) {
        return studentRepository.addStudent(student);
    }

    @Override
    public boolean deleteStudent(Integer studentId) {
        return studentRepository.deleteStudent(studentId);
    }

    @Override
    public boolean updateStudent(Student student) {
        return studentRepository.updateStudent(student);
    }

    @Override
    public List> getStudentMapList() {
        return studentRepository.getStudentMapList();
    }

    @Override
    public List<Student> getAllStudents() {
        return studentRepository.getAllStudents();
    }
}

Spring IOC Configuration


<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd">

    
    <bean id="studentService" class="com.example.spring.data.service.impl.StudentServiceImpl">
        <property name="studentRepository" ref="studentRepository"/>
    </bean>
    
    
    <bean id="studentRepository" class="com.example.spring.data.dao.impl.StudentRepositoryImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
    
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/student"/>
        <property name="username" value="root"/>
        <property name="password" value="password"/>
    </bean>
    
</beans>

Test Class

package com.example.spring.data.test;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.example.spring.data.entity.Student;
import com.example.spring.data.service.StudentService;

public class StudentDataTest {
    private ApplicationContext context;
    
    @Before
    public void init(){
        context = new ClassPathXmlApplicationContext("applicationContext.xml");
    }
    
    @Test
    public void testAddStudent(){
        StudentService studentService = context.getBean(StudentService.class);

        Student student = new Student();
        student.setName("Michael");
        student.setAge(30);
        student.setSex(1);
        
        System.out.println(studentService.addStudent(student));
    }
    
    @Test
    public void testDeleteStudent(){
        StudentService studentService = context.getBean(StudentService.class);
        
        int studentId = 100000;
        
        System.out.println(studentService.deleteStudent(studentId));
    }
    
    @Test
    public void testUpdateStudent(){
        StudentService studentService = context.getBean(StudentService.class);
        
        Student student = new Student();
        student.setId(100001);
        student.setName("Sophia");
        student.setAge(25);
        student.setSex(0);
        
        System.out.println(studentService.updateStudent(student));
    }
    
    @Test
    public void testGetAllStudents(){
        StudentService studentService = context.getBean(StudentService.class);
        System.out.println(studentService.getAllStudents());
    }
    
    @Test
    public void testGetStudentMapList(){
        StudentService studentService = context.getBean(StudentService.class);
        System.out.println(studentService.getStudentMapList());
    }
}

Approach 2: Annotation-Based Method

DAO Implementation with Annotations:

package com.example.spring.data.dao.impl;

import java.util.List;
import java.util.Map;

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 com.example.spring.data.dao.StudentRepository;
import com.example.spring.data.entity.Student;

@Repository("studentRepository")
public class StudentRepositoryImpl implements StudentRepository {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public boolean addStudent(Student student) {
        String sql = "INSERT INTO STUDENT(NAME,AGE,SEX) VALUES(?,?,?)";
        int rows = jdbcTemplate.update(sql, student.getName(), student.getAge(), student.getSex());
        return rows > 0;
    }

    @Override
    public boolean deleteStudent(Integer studentId) {
        String sql = "DELETE FROM STUDENT WHERE ID = ?";
        int rows = jdbcTemplate.update(sql, studentId);
        return rows > 0;
    }

    @Override
    public boolean updateStudent(Student student) {
        String sql = "UPDATE STUDENT SET NAME=?, AGE=?, SEX=? WHERE ID=?";
        int rows = jdbcTemplate.update(sql, student.getName(), student.getAge(), student.getSex(), student.getId());
        return rows > 0;
    }

    @Override
    public List> getStudentMapList() {
        return jdbcTemplate.queryForList("SELECT ID,NAME,AGE,SEX FROM STUDENT");
    }

    @Override
    public List<Student> getAllStudents() {
        String sql = "SELECT ID, NAME, AGE, SEX FROM STUDENT";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Student>(Student.class));
    }
}

Service Implementation with Annotations:

package com.example.spring.data.service.impl;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.example.spring.data.dao.StudentRepository;
import com.example.spring.data.entity.Student;
import com.example.spring.data.service.StudentService;

@Service("studentService")
public class StudentServiceImpl implements StudentService {
    
    @Autowired
    private StudentRepository studentRepository;

    @Override
    public boolean addStudent(Student student) {
        return studentRepository.addStudent(student);
    }

    @Override
    public boolean deleteStudent(Integer studentId) {
        return studentRepository.deleteStudent(studentId);
    }

    @Override
    public boolean updateStudent(Student student) {
        return studentRepository.updateStudent(student);
    }

    @Override
    public List> getStudentMapList() {
        return studentRepository.getStudentMapList();
    }

    @Override
    public List<Student> getAllStudents() {
        return studentRepository.getAllStudents();
    }
}

Updated Configuration with Annotations:


<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    
    
    
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/student"/>
        <property name="username" value="root"/>
        <property name="password" value="password"/>
    </bean>
</beans>

Approach 3: Inheriting from JdbcDaoSupport

When DAO implementations extend JdbcDaoSupport, they don't need to explicitly inject JdbcTemplate as it's handled internally:

package com.example.spring.data.dao.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.example.spring.data.dao.StudentRepository;
import com.example.spring.data.entity.Student;

@Repository("studentRepository")
public class StudentRepositoryImpl extends JdbcDaoSupport implements StudentRepository {

    @Override
    public boolean addStudent(Student student) {
        String sql = "INSERT INTO STUDENT(NAME,AGE,SEX) VALUES(?,?,?)";
        int rows = getJdbcTemplate().update(sql, student.getName(), student.getAge(), student.getSex());
        return rows > 0;
    }

    @Override
    public boolean deleteStudent(Integer studentId) {
        String sql = "DELETE FROM STUDENT WHERE ID = ?";
        int rows = getJdbcTemplate().update(sql, studentId);
        return rows > 0;
    }

    @Override
    public boolean updateStudent(Student student) {
        String sql = "UPDATE STUDENT SET NAME=?, AGE=?, SEX=? WHERE ID=?";
        int rows = getJdbcTemplate().update(sql, student.getName(), student.getAge(), student.getSex(), student.getId());
        return rows > 0;
    }

    @Override
    public List> getStudentMapList() {
        return getJdbcTemplate().queryForList("SELECT ID,NAME,AGE,SEX FROM STUDENT");
    }

    @Override
    public List<Student> getAllStudents() {
        String sql = "SELECT ID, NAME, AGE, SEX FROM STUDENT";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<Student>(Student.class));
    }
}

Configuration for JdbcDaoSupport approach:


<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

    
    <bean id="studentService" class="com.example.spring.data.service.impl.StudentServiceImpl">
        <property name="studentRepository" ref="studentRepository"/>
    </bean>
    
    
    <bean id="studentRepository" class="com.example.spring.data.dao.impl.StudentRepositoryImpl">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/student"/>
        <property name="username" value="root"/>
        <property name="password" value="password"/>
    </bean>
</beans>

Approach 4: Properties File Configuration

Create a properties file in the src directory:

# jdbc.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/student
jdbc.user=root
jdbc.password=password

Update the Spring configuration to load the properties file:


<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    
    
    
    
    
    
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.user}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>
</beans>

Approach 5: C3P0 Connection Pool

First, add the C3P0 dependency:

c3p0-0.9.0.jar

Update the data source configuration:


<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="${jdbc.driver}"/>
    <property name="jdbcUrl" value="${jdbc.url}"/>
    <property name="user" value="${jdbc.user}"/>
    <property name="password" value="${jdbc.password}"/>
</bean>

Approach 6: DBCP Connection Pool

First, add the DBCP dependencies:

commons-dbcp-1.4.jar
commons-pool-1.6.jar

Update the data source configuration:


<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="${jdbc.driver}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.user}"/>
    <property name="password" value="${jdbc.password}"/>
</bean>

Tags: Spring JDBC database java Framework

Posted on Fri, 17 Jul 2026 17:24:24 +0000 by SUNNY ARSLAN

Freaks City

Implementing JDBC with Spring Framework