How to Access Relational Data Using JDBC with Spring

How to Access Relational Data Using JDBC with Spring

Overview

The Spring framework has become an essential part of modern Java-based application development and has effectively managed to take control of every department in the Java development world. The JDBC template for Spring is used in most of the Spring-based JEE applications to communicate with the database. This article will discuss how to access a relational database using the JDBC template in Spring.

Introduction

We all know the importance of relational database in enterprise application development. Spring is one of the most widely used frameworks in Java based enterprise application development. Spring has different modules such as ORM, security, transaction, JPS, JMS, etc. As Spring provides readily available components to plug-in, the modern approach for enterprise Java application development uses the features extensively. As a result the development is much faster and more efficient. Spring provides a simple approach to handle database activities known as the Spring JDBC template.

Disadvantages of Standard JDBC API

Standard JDBC API has the following disadvantages:

  • Apart from executing the main query, you need to write a lot of code to handle the execution environment issues such as creating connection, statement, resultset, etc.
  • Need to handle exception handling code separately.
  • Need to handle transactional issues.

Advantages of the Spring JDBC Template

Spring JDBC template has the following advantages compared with the normal approach of standard JDBC.

  • Cleaning of used resources is done automatically by the Spring JDBC template. Developers do not need to bother about releasing the resources. Hence it prevents memory leaks.
  • Spring JDBC template handles the exception and errors in a more efficient way. It converts the JDBC SQLExceptions into RuntimeExceptions, so the developers can handle it in more flexible manner.
  • The Spring JDBC template also converts the vendor specific errors in a more meaningful message, thus the handling of those errors is more efficient.

Getting Started

In this document, let’s use Gradle as the tool to build our application. Let us start with a simple application used to store and retrieve employee details, along with their departments in an organization. In our example, we will have the following attributes of the employee object:

  • Employee Id
  • Employee First Name
  • Employee Middle Name
  • Employee Last Name
  • Employee Department Id

And the department object has the following attributes:

  • Department Id
  • Department Name

Now let us create the Java objects that will be used to communicate with the database. Our POJO classes for these two entities are listed below:

Listing 1: Sample showing the Employee Class

package com.home.springjdbc.objects;public class Employee {	// The employee Id	private String empId;	// The employee first Name	private String empFName;	// The employee middle name	private String empMName;	// The employee last Name	private String empLName;	// The employee department Id	private String empDeptId;	public Employee ( String empId, String empFName, String empMName,			String empLName, String empDeptId ) {		super();		this.empId = empId;		this.empFName = empFName;		this.empMName = empMName;		this.empLName = empLName;		this.empDeptId = empDeptId;	}	/**	 * @return the empId	 */	public String getEmpId () {		return empId;	}	/**	 * @param empId	 * the empId to set	 */	public void setEmpId ( String empId ) {		this.empId = empId;	}	/**	 * @return the empFName	 */	public String getEmpFName () {		return empFName;	}	/**	 * @param empFName	 * the empFName to set	 */	public void setEmpFName ( String empFName ) {		this.empFName = empFName;	}	/**	 * @return the empMName	 */	public String getEmpMName () {		return empMName;	}	/**	 * @param empMName	 * the empMName to set	 */	public void setEmpMName ( String empMName) {		this.empMName = empMName;	}	/**	 * @return the empLName	 */	public String getEmpLName () {		return empLName;	}	/**	 * @param empLName	 *  the empLName to set	 */	public void setEmpLName ( String empLName) {		this.empLName = empLName;	}	/**	 * @return the empDeptId	 */	public String getEmpDeptId () {		return empDeptId;	}	/**	 * @param empDeptId	 * the empDeptId to set	 */	public void setEmpDeptId ( String empDeptId ) {		this.empDeptId = empDeptId;	}	/*	 * ( non-Javadoc )	 * 	 * @see java.lang.Object#toString ()	 */	@Override	public String toString () {		return "Employee [ empId=" + empId + ", empFName=" + empFName				+ ", empMName=" + empMName + ", empLName=" + empLName				+ ", empDeptId=" + empDeptId + "]";	}}

Listing 2: Sample showing the Department Class

package com.home.springjdbc.objects;public class Department {	// The department Id	private String deptId;	// The department Name	private String deptName;	public Department ( String deptId, String deptName) {		super ();		this.deptId = deptId;		this.deptName = deptName;	}	/**	 * @return the deptId	 */	public String getDeptId () {		return deptId;	}	/**	 * @param deptId	 * the deptId to set	 */	public void setDeptId ( String deptId ) {		this.deptId = deptId;	}	/**	 * @return the deptName	 */	public String getDeptName () {		return deptName;	}	/**	 * @param deptName	 * the deptName to set	 */	public void setDeptName ( String deptName ) {		this.deptName = deptName;	}	/*	 * (non-Javadoc)	 * 	 * @see java.lang.Object#toString()	 */	@Override	public String toString () {		return "Department [ deptId=" + deptId + ", deptName=" + deptName + "]";	}}

Spring provides a template class known as JdbcTemplate. This class is responsible for communicating with SQL relational databases and JDBC. In the traditional approach, the bulk of JDBC code is occupied in handling connection management, resource acquisition, exception handling, and general error checking, which is irrelevant in the context of what we are trying to achieve. The JdbcTemplate takes care of all of these things and as a developer we should focus only on the business logic. A sample implementation of the template class is shown as below:

Listing 3: Sample showing the implementation of JDBC template

package com.home.springjdbc.dao;import java.sql.Driver;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import org.springframework.jdbc.datasource.SimpleDriverDataSource;import com.home.springjdbc.objects.Employee;public class EmployeeDAO {	public static void main ( String args[] ) {		// simple DS for test (not for production!)		SimpleDriverDataSource dataSource = new SimpleDriverDataSource();dataSource.setDriverClass((Class) org.h2.Driver.class);		dataSource.setUsername( "sa" );		dataSource.setUrl( "jdbc:h2:mem" );		dataSource.setPassword( "" );		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);		System.out.println( "Creating tables" );		jdbcTemplate.execute( "drop table employees if exists" );		jdbcTemplate.execute( "create table employees("+ "id varchar(25), first_name varchar(255), middle_name varchar(255), last_name varchar(255), deptId varchar(25))");String[] records = "E001 Dean Andrew Roberts D25; E002 Jeff Longman Dean D42; E003 Erin Nancy Kirkland D66;".split(";");		for ( String singleRecord : records ) {			String [] cols = singleRecord.split(" ");System.out.printf( " Adding employee record for %s %s %s %s %s
",cols[0], cols[1], cols[2], cols[3], cols[4]);jdbcTemplate.update("INSERT INTO employees(id, first_name, middle_name, last_name, deptId) values(?,?,?,?,?)",cols[0], cols[1], cols[2], cols[3], cols[4]);		}System.out.println ( " Querying for customer records where first_name = 'Erin':" );		List results = jdbcTemplate.query(				"select * from employees where first_name = ?",				new Object[] { "Erin" }, new RowMapper() {		@Override		public Employee mapRow(ResultSet rs, int rowNum)				throws SQLException {				return new Employee(rs.getString("id"),rs.getString("first_name"), rs.getString("middle_name"), rs.getString("last_name"), rs.getString("deptId"));					}				});		for (Employee employee : results) {			System.out.println(employee);		}	}}

In the example above, we set up a JDBC data source using the SimpleDriverDataSource from Spring. We then use this data source to construct the JDBCTemplate instance. Once the JDBCTemplate class is created, we can easily start calling the database. First, we create the table using the execute method of the template. Then we insert a few records using the update method of the template. Finally we use the query method to fetch the records of our choice.

In our example, we have used a simple query to fetch the record from the employee table. But as per our design we see that the employee table has a relation with the department table via the column deptId. As a result, we can also use the same code to fetch records joining these two tables. Just we need to change the query to put a join. The above code could be modified as in Listing 4.

Listing 4: Sample showing the modified query

List results = jdbcTemplate.query("select emp.first_name, emp.middle _name, emp.last _name, dept.dept_Name from employees emp, department dept where emp.first_name = ?",				new Object[] { "Erin" }, new RowMapper() {@Overridepublic EmployeeDtls mapRow(ResultSet rs, int rowNum) throws SQLException {return new Employee(				rs.getString("first_name"), rs.getString("middle_name"), rs.getString("last_name"), rs.getString("dept_Name "));			}			});for (EmployeeDtls employeeDtl : results) {			System.out.println(employeeDtl);		}

To execute this we should have another POJO class EmployeeDtls, such as:

Listing 5: Sample showing the POJO class EmployeeDtls

package com.home.springjdbc.objects;public class EmployeeDtls {	// The employee first Name	private String empFName;	// The employee middle name	private String empMName;	// The employee last Name	private String empLName;	// The department Name	private String deptName;	/**	 * @param empFName	 * @param empMName	 * @param empLName	 * @param deptName	 */	public EmployeeDtls(String empFName, String empMName, String empLName,			String deptName) {		super();		this.empFName = empFName;		this.empMName = empMName;		this.empLName = empLName;		this.deptName = deptName;	}	/*	 * (non-Javadoc)	 * 	 * @see java.lang.Object#toString()	 */	@Override	public String toString() {		return "EmployeeDtls [empFName=" + empFName + ", empMName=" + empMName				+ ", empLName=" + empLName + ", deptName=" + deptName + "]";	}	/**	 * @return the empFName	 */	public String getEmpFName() {		return empFName;	}	/**	 * @param empFName 	 * the empFName to set	 */	public void setEmpFName(String empFName) {		this.empFName = empFName;	}	/**	 * @return the empMName	 */	public String getEmpMName() {		return empMName;	}	/**	 * @param empMName	 * the empMName to set	 */	public void setEmpMName(String empMName) {		this.empMName = empMName;	}	/**	 * @return the empLName	 */	public String getEmpLName() {		return empLName;	}	/**	 * @param empLName	 * the empLName to set	 */	public void setEmpLName(String empLName) {		this.empLName = empLName;	}	/**	 * @return the deptName	 */	public String getDeptName() {		return deptName;	}	/**	 * @param deptName	 *            the deptName to set	 */	public void setDeptName(String deptName) {		this.deptName = deptName;	}}

In this article we have seen how spring JDBC template can be used in Java based standard and enterprise applications. We have also discussed the advantages of this approach and how it can make the development much faster compared to the standard JDBC based development.

?

About the Author

Kaushik Pal is a technical architect with 15 years of experience in enterprise application and product development. He has expertise in web technologies, architecture/design, Java/J2EE, Open source and big data technologies. You can find more of his work at www.techalpine.com and you can email him here.

devx-admin

devx-admin

Share the Post:
Economy Act Soars

Virginia’s Clean Economy Act Soars Ahead

Virginia has made significant strides towards achieving its short-term carbon-free objectives as outlined in the Clean Economy Act of 2020. Currently, about 44,000 megawatts (MW)

Renewable Storage Innovation

Innovative Energy Storage Solutions

The Department of Energy recently revealed a significant investment of $325 million in advanced battery technologies to store excess renewable energy produced by solar and

Development Project

Thrilling East Windsor Mixed-Use Development

Real estate developer James Cormier, in collaboration with a partnership, has purchased 137 acres of land in Connecticut for $1.15 million with the intention of

USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in

Economy Act Soars

Virginia’s Clean Economy Act Soars Ahead

Virginia has made significant strides towards achieving its short-term carbon-free objectives as outlined in the Clean Economy Act of 2020. Currently, about 44,000 megawatts (MW) of wind, solar, and energy

Renewable Storage Innovation

Innovative Energy Storage Solutions

The Department of Energy recently revealed a significant investment of $325 million in advanced battery technologies to store excess renewable energy produced by solar and wind sources. This funding will

Renesas Tech Revolution

Revolutionizing India’s Tech Sector with Renesas

Tushar Sharma, a semiconductor engineer at Renesas Electronics, met with Indian Prime Minister Narendra Modi to discuss the company’s support for India’s “Make in India” initiative. This initiative focuses on

Development Project

Thrilling East Windsor Mixed-Use Development

Real estate developer James Cormier, in collaboration with a partnership, has purchased 137 acres of land in Connecticut for $1.15 million with the intention of constructing residential and commercial buildings.

USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies in the USA. Through a

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in and explore the leaders in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India, and kickstart your journey to

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner for your online project. Your

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its

Revolutionized Low-Code

You Should Use Low-Code Platforms for Apps

As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not

Cybersecurity Strategy

Five Powerful Strategies to Bolster Your Cybersecurity

In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies

Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

Huawei Electric Dazzle

Huawei Dazzles with Electric Vehicles and Wireless Earbuds

During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

Generative AI Revolution

Is Generative AI the Next Internet?

The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

Microsoft Laptop

The New Surface Laptop Studio 2 Is Nuts

The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional