Lets make a project to fetch data from a table.

I have MySQL installed in my machine, below is the details.

Now, as we all know that to connect to DB in java we need a JDBC Driver, i have downloaded MySql JDBC Driver jar "mysql-connector-j-8.0.31.jar" and added to build path.

Once we have the JDBC driver lets dive into the code.

To connect to DB we need 3 things 1)an url to locate our DB, 2) DB Username, 3) DB Password. Once we have this details we can get an DB Connection and once we get an connection we can execute our queries in statements. so this code will look like below.

String url = "jdbc:mysql://localhost:3306/demo";
String username = "root";
String password = "root";

// Get connection from DataSource
Connection connection = DriverManager.getConnection(url, username, password);

// Create statement
Statement statement = connection.createStatement(); 

// Execute query
resultSet = statement.executeQuery("SELECT name, aadhar_number FROM aadhar");

// Process the result set
while (resultSet.next()) {
   String name = resultSet.getString("name");
   String aadharNumber = resultSet.getString("aadhar_number");
   System.out.println(name + ": " + aadharNumber);
}

But we are spring core subject expert, we will create these Connection and Statement Object using spring. As above are Factory methods, our config file will look like below.

<?xml version="1.0" encoding="UTF-8"?>
<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">

    <!-- Define JDBC Connection bean -->
    <bean id="JDBCConnection" class="java.sql.DriverManager" factory-method="getConnection">
        <constructor-arg value="jdbc:mysql://localhost:3306/demo"/>
        <constructor-arg value="root"/>
        <constructor-arg value="root"/>
    </bean>

    <!-- Define JDBC Statement bean -->
    <bean id="JDBCStatement" factory-bean="JDBCConnection" factory-method="createStatement"/>
        <!-- more bean definitions go here -->
</beans>

Now lets look below how we can fetch records using above configuration.

package com.hk.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class BeanManagementTest {

    // spring 5.0.x doc
    // https://docs.spring.io/spring-framework/docs/5.0.x/spring-framework-reference/

    // download jars here https://repo1.maven.org/maven2/org/springframework/
    public static void main(String[] args) {
        System.out.println("stat of main(-) method");

        ApplicationContext applicationContext = new ClassPathXmlApplicationContext(
                "com/hk/cfgs/applicationContext.xml");

        fetchAndDisplayRecords(applicationContext);

        ((AbstractApplicationContext) applicationContext).close();

        System.out.println("end of main(-) method");
    }

    private static void fetchAndDisplayRecords(ApplicationContext applicationContext) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = (Connection) applicationContext.getBean("JDBCConnection"); 
            statement = (Statement) applicationContext.getBean("JDBCStatement"); 
            // Execute query
            resultSet = statement.executeQuery("SELECT name, aadhar_number FROM aadhar");

            // Process the result set
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                String aadharNumber = resultSet.getString("aadhar_number");
                System.out.println(name + ": " + aadharNumber);
            }            
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            // Close resources in finally block to ensure proper cleanup
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

folder structure:

The above code works fine i even got the output.

But when i try to fetch records twice i got below error

stat of main(-) method
Mar 16, 2024 1:06:47 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@c4437c4: startup date [Sat Mar 16 01:06:47 IST 2024]; root of context hierarchy
Mar 16, 2024 1:06:47 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [com/hk/cfgs/applicationContext.xml]
Alice Smith: 987654321098
Bob Johnson: 456789012345
Emily Brown: 789012345678
java.sql.SQLException: No operations allowed after statement closed.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:82)
    at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200)
    at com.hk.test.BeanManagementTest.fetchAndDisplayRecords(BeanManagementTest.java:44)
    at com.hk.test.BeanManagementTest.main(BeanManagementTest.java:28)
Caused by: com.mysql.cj.exceptions.StatementIsClosedException: No operations allowed after statement closed.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
    at com.mysql.cj.jdbc.StatementImpl.checkClosed(StatementImpl.java:336)
    at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1105)
    ... 2 more
Mar 16, 2024 1:06:48 AM org.springframework.context.support.AbstractApplicationContext doClose
INFO: Closing org.springframework.context.support.ClassPathXmlApplicationContext@c4437c4: startup date [Sat Mar 16 01:06:47 IST 2024]; root of context hierarchy
end of main(-) method

Let me explain why we got this error, every bean has scope and if we are not mentioning any scope it will create bean object as singleton and no matter how much time we call "getBean(-)" method it will always return already available object.

when we fetched the records for the first time, after we got the result we closed the connection and Statement. And when we tried to fetch for the 2nd time, as Connection and Statement are singleton spring bean it returned the previous Connection and Statement object and as this connection and Statement are already closed we got this error.

But we can not keep these resources open, it will cause memory leak. The other option is to change the scope of these beans like below.

above change worked and i got output. But wait a minute, that means if i have a big java project and multiple DB Calls then there will be lots of unnecessary connection object which again is a problem.

Now spring comes and laughs and says "Yeh hai aam Zindagi" and comes up with spring-jdbc jar and says "yeh hai mentos zindagi".

lets use Spring-JDBC Jar and create a project

now we can create a spring data source object which will manage the connection and statements we don't have to worry about it we can close it when ever we want.

<?xml version="1.0" encoding="UTF-8"?>
<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">

     <!-- Define DataSource -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/demo"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>
    <!-- more bean definitions go here -->
</beans>

package com.hk.test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class BeanManagementTest {

    // spring 5.0.x doc
    // https://docs.spring.io/spring-framework/docs/5.0.x/spring-framework-reference/

    // download jars here https://repo1.maven.org/maven2/org/springframework/
    public static void main(String[] args) {
        System.out.println("stat of main(-) method");

        ApplicationContext applicationContext = new ClassPathXmlApplicationContext(
                "com/hk/cfgs/applicationContext.xml");

        DataSource dataSource = applicationContext.getBean("dataSource", DataSource.class);

        fetchAndDisplayRecords(dataSource);
        fetchAndDisplayRecords(dataSource);

        ((AbstractApplicationContext) applicationContext).close();

        System.out.println("end of main(-) method");
    }

    private static void fetchAndDisplayRecords(DataSource dataSource) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
             // Get connection from DataSource
            connection = dataSource.getConnection();

            // Create statement
            statement = connection.createStatement();

            // Execute query
            resultSet = statement.executeQuery("SELECT name, aadhar_number FROM aadhar");

            // Process the result set
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                String aadharNumber = resultSet.getString("aadhar_number");
                System.out.println(name + ": " + aadharNumber);
            }            
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            // Close resources in finally block to ensure proper cleanup
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Now with above changes even if I fetch records multiple time i get no errors.

Hoping you understood why we need spring jdbc, this is one of the problem which spring-jdbc solves. there are many so keep learning.