Spring JDBC Example

Spring JDBC provides a simplified approach to interact with relational databases by reducing boilerplate JDBC code. It uses JdbcTemplate to handle common tasks like connection management, query execution, and exception handling efficiently.

Eliminates the need for manual resource handling like Connection, Statement, and ResultSet closing.
Provides JdbcTemplate to execute SQL queries and updates easily.
Converts SQL exceptions into a consistent, unchecked exception hierarchy for better handling

Prerequisite:

Basic understanding of JDBC
Installed MySQL database
Java IDE (IntelliJ IDEA or Eclipse)
Spring and MySQL Connector JAR files

Step-by-Step Implementation

Step 1: Create Java Project

Create a simple Java project in IntelliJ IDEA or Eclipse.
Set up the project structure (src folder, packages, etc.).
Ensure Java is properly configured in the IDE.

Step 2: Setup Database

Create a MySQL database (e.g., studentdb).
Create a table (e.g., hostelstudentinfo).
Insert sample records into the table for testing.
Verify data using MySQL Workbench or CLI.

Step 3: Create DAO Class

Create StudentDAO class.
Define DB properties: driver , url , username , password
Use setter injection for these properties.
Create method selectAllRows() to fetch data using JDBC.

Java

import java.sql.*;

// Main class
public class StudentDAO {

    // Class data members
    private String driver;
    private String url;
    private String userName;
    private String password;

    // Setter methods for dependency injection
    public void setDriver(String driver) {
        this.driver = driver;
    }
    public void setUrl(String url) {
        this.url = url;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public void setPassword(String password) {
        this.password = password;
    }

    // Method to fetch all student records
    public void selectAllRows() throws ClassNotFoundException, SQLException {
        System.out.println("Retrieving all student data..");

        // Load driver
        Class.forName(driver);

        // Establish connection
        Connection con = DriverManager.getConnection(url, userName, password);

        // Execute query
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM studentdb.hostelstudentinfo");

        while (rs.next()) {
            int studentId = rs.getInt(1);
            String studentName = rs.getString(2);
            double hostelFees = rs.getDouble(3);
            String foodType = rs.getString(4);

            System.out.println(studentId + " " + studentName + " " + hostelFees + " " + foodType);
        }

        // Close connection
        con.close();
    }
}

Step 4: Add Dependencies (JARs)

Add Spring JDBC JAR.
Add MySQL Connector JAR.
Ensure libraries are added to build path correctly.
Verify no missing dependency errors.

Step 5: Configure Spring Bean (beans.xml)

Define StudentDAO bean.
Inject database values using setter injection.
Configure: Driver class , Database URL , Username & password
Ensure correct XML schema setup.

Spring Configuration (beans.xml):

XML

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans/"
       xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans/
        https://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="studentDAO" class="StudentDAO">
        <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/studentdb"/>
        <property name="userName" value="root"/>
        <property name="password" value="your_password"/>
    </bean>

</beans>

Step 6: Create Main Class

Load Spring Application Context using ClassPathXmlApplicationContext.
Retrieve StudentDAO bean using getBean().
Call selectAllRows() method.
Run the application to fetch data.

Java

// Importing required classes
import java.sql.SQLException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

// Main class
public class Main {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        
        // Initialize Spring Application Context
        ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");

        // Retrieve bean
        StudentDAO studentDAO = context.getBean("studentDAO", StudentDAO.class);

        // Call method to fetch student records
        studentDAO.selectAllRows();
    }
}

Output: After running the program, the following output will be displayed:

Retrieving all student data..
1 Asish 300.5 Veg
2 Vicky 245.89 Non Veg
3 Anshul 123.67 Veg

You can see we have successfully fetched the data from the MySQL Database.