Sunday, 17 February 2019

Spring + JdbcTemplate + Query single column value example | Spring JDBC tutorial

🚀 Boost Your Java Skills!

Join the Ram N Java family for more simplified coding tutorials.

How to Query a Single Column Value with Spring JDBC

In this guide, we'll walk through a beginner-friendly way to fetch a specific piece of information—like an employee's name—from a database using Spring JdbcTemplate. This is one of the most common tasks when working with databases in Java.

The Simple SQL Logic

To get a single value (like a name) based on an ID, we use a standard SELECT query with a placeholder (?). This keeps our application secure from SQL injection.

        SELECT name FROM employee WHERE employee_id = ?
    

Using queryForObject

Spring provides a special method called queryForObject(). For a single column value, we pass three things to it:

SQL String: Our select statement.
Arguments: The value to replace the ? (e.g., ID 1).
Return Type: In this case, String.class because the name is a piece of text.

Project Setup and Code Flow

Dependencies: Ensure your pom.xml has Spring JDBC and MySQL driver dependencies.
Configuration: Define your DataSource (database URL, username, password) in the Spring XML file.
The DAO: Your implementation class extends JdbcDaoSupport, which gives you easy access to the JdbcTemplate object.
Execution: The application context reads the XML, creates the bean, and executes the query to return "Peter" for employee ID 1.

⭐ Quick Learning Tip:

Using queryForObject for single values is much cleaner than traditional JDBC because Spring handles all the connection opening, closing, and error handling for you!

Related Tutorials to Explore

Enhance your database skills with these related videos from Ram N Java:

Click here to watch on Youtube:
https://www.youtube.com/watch?v=m24ix4gsDo8&list=UUhwKlOVR041tngjerWxVccw

Click the below Image to Enlarge:

Spring + JdbcTemplate + Query single column value example | Spring JDBC tutorial

Employee.sql

CREATE DATABASE org_db;

CREATE TABLE `employee` (

  `EMPLOYEE_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  `NAME` VARCHAR(100) NOT NULL,

  `AGE` INT(10) NOT NULL,

  `SALARY` INT(10) DEFAULT NULL,

  PRIMARY KEY (`EMPLOYEE_ID`)

) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <groupId>com.ram.core</groupId>

    <artifactId>SpringDemo</artifactId>

    <packaging>jar</packaging>

    <version>1.0-SNAPSHOT</version>

    <name>SpringDemo</name>

    <url>http://maven.apache.org</url>

    <properties>

        <spring.version>5.0.5.RELEASE</spring.version>

    </properties>

    <dependencies>

        <dependency>

            <groupId>junit</groupId>

            <artifactId>junit</artifactId>

            <version>3.8.1</version>

            <scope>test</scope>

        </dependency>

        <!-- Spring 5 dependencies -->

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-core</artifactId>

            <version>${spring.version}</version>

        </dependency>

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-context</artifactId>

            <version>${spring.version}</version>

        </dependency>

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-jdbc</artifactId>

            <version>${spring.version}</version>

        </dependency>

        <!-- MySQL database driver -->

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

            <version>8.0.11</version>

        </dependency>

    </dependencies>

</project>

Employee.java

package com.ram.employee.model;

public class Employee

{

    private long employeeId;

    private String name;

    private int age;

    private int salary;

    public long getEmployeeId()

    {

        return employeeId;

    }

    public void setEmployeeId(long employeeId)

    {

        this.employeeId = employeeId;

    }

    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 getSalary()

    {

        return salary;

    }

    public void setSalary(int salary)

    {

        this.salary = salary;

    }

    @Override

    public String toString()

    {

        return "Employee [employeeId=" + employeeId + ", name=" + name

                + ", age=" + age + ", salary=" + salary + "]";

    }

}

EmployeeDAO.java

package com.ram.employee.dao;

public interface EmployeeDAO

{

    public String findEmployeeNameById(int employeeId);

}

EmployeeDAOImpl.java

package com.ram.employee.dao.impl;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.ram.employee.dao.EmployeeDAO;

public class EmployeeDAOImpl

        extends JdbcDaoSupport implements EmployeeDAO

{

    public String findEmployeeNameById(int employeeId)

    {

        String sql = "SELECT NAME FROM EMPLOYEE WHERE EMPLOYEE_ID=?";

        String name = (String) getJdbcTemplate().queryForObject(sql,

                new Object[] { employeeId }, String.class);

        return name;

    }

}

applicationContext.xml

<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-4.3.xsd">

    <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/org_db" />

        <property name="username" value="root" />

        <property name="password" value="root" />

    </bean>

    <bean id="employeeDAO" class="com.ram.employee.dao.impl.EmployeeDAOImpl">

        <property name="dataSource" ref="dataSource" />

    </bean>

</beans>

App.java

package com.ram.core;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.ram.employee.dao.EmployeeDAO;

public class App

{

    public static void main(String[] args)

    {

        ApplicationContext context = new ClassPathXmlApplicationContext(

                "applicationContext.xml");

        EmployeeDAO employeeDAO = (EmployeeDAO) context

                .getBean("employeeDAO");

        String name = employeeDAO.findEmployeeNameById(1);

        System.out.println("name = " + name);

    }

}

Output:

Feb 06, 2019 10:31:18 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@35d176f7: startup date [Wed Feb 06 10:31:18 IST 2019]; root of context hierarchy

Feb 06, 2019 10:31:18 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions

INFO: Loading XML bean definitions from class path resource [applicationContext.xml]

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

Feb 06, 2019 10:31:19 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

INFO: Loaded JDBC driver: com.mysql.jdbc.Driver

Wed Feb 06 10:32:10 IST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

name = Peter