Spring JDBC Example

JDBC or Java Database Connectivity is a specification from Sun microsystems that provides a standard abstraction(that is API or Protocol) for java applications to communicate with various databases. It provides the language with java database connectivity standards. It is used to write programs required to access databases. JDBC along with the database driver is capable of accessing databases and spreadsheets. The enterprise data stored in a relational database(RDB) can be accessed with the help of JDBC APIs. In this article, we are going to discuss how to write a JDBC program using Spring Framework.

Prerequisite: JDBC Tutorial

Step by Step Implementation

Step 1: Create a simple Java project in your preferred IDE (IntelliJ IDEA or Eclipse). You may refer to these articles:

• Creating First Java Application in IntelliJ IDEA
• How to Install Eclipse IDE For Java?

Step 2: Create some tables inside your database. In this article, we have used the MySQL database. And the following data has been present inside our MySQL Database.

So here ‘studentdb’ is our schema name and ‘hostelstudentinfo’ is the table name. Similarly, you can create your own schema and table and put some data inside that table manually. You may refer to these articles:

• How to Install MySQL on Windows?
• How to Install SQL Workbench For MySQL on Windows?

Step 3: Go to the Java project and create one class named StudentDAO and inside the class, we are going to create a single method selectAllRows() to fetch all the data present inside our MySQL database. We are also going to declare our four most important attributes to connect our Java program with the MySQL server. 

• Driver
• URL
• User
• Password

Example

Java

// Java Program to fetch All Data Present Inside MySQL DB

// Importing required classes
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
    // Setter Injection
    public void setDriver(String driver)
    {
        this.driver = driver;
    }

    // Setter
    public void setUrl(String url) { this.url = url; }

    // Setter
    public void setUserName(String userName)
    {
        this.userName = userName;
    }

    // Setter
    public void setPassword(String password)
    {
        this.password = password;
    }

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

        // Load driver
        Class.forName(driver);

        // Getting a 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);
        }

        // Closing the connection
        con.close();
    }
}

Step 4: Now we have to Add the External JAR Files to an IntelliJ IDEA Project. A JAR (Java Archive) is a package file format typically used to aggregate many Java class files and associated metadata and resources (text, images, etc.) into one file to distribute application software or libraries on the Java platform. In simple words, a JAR file is a file that contains a compressed version of .class files, audio files, image files, or directories. We have to add the following external jar files to our Java project

• Spring
• MySQL Connector

You may refer to this article How to Add External JAR File to an IntelliJ IDEA Project?. You may download the jar file from the following links

• Spring: Click Here
• MySQL Connector: Click Here

Step 5: Let’s create the bean of StudentDAO class inside the beans.xml file and inject the values of the properties by setter injection. You may refer to this article Spring – Injecting Literal Values By Setter Injection. Below is the code for the beans.xml file.

XML

<?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
        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 the Main class and let’s test our application is running fine or not. Below is the code for the Main.java file.

Java

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

// Class
public class Main {
    public static void main(String[] args)
        throws SQLException, ClassNotFoundException
    {

        // Using ApplicationContext tom implement Spring IoC
        ApplicationContext context
            = new ClassPathXmlApplicationContext(
                "beans.xml");

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

        // Calling the method
        studentDAO.selectAllRows();
    }
}

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

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