In Enterprise applications, data access/stored in relational databases is a common requirement. As an essential part of Java SE, Java Database Connectivity (JDBC) defines a set of standard APIs to access relational databases in a vendor-independent fashion. However, when using JDBC, the developer has to manage all database-related resources and handle database exceptions explicitly.
To make JDBC easier to use, the Spring framework provides an abstraction framework for interfacing with JDBC. JDBC template class in Spring is designed to provide template methods for different types of JDBC operations. Each template method is responsible for controlling the overall process and allows to override particular tasks of the process.
Interface PreparedStatementSetter
Spring provides PreparedStatementSetter interface in org.springframework.jdbc.core package used by the JdbcTemplate class. This is a callback interface that sets values on a PreparedStatement provided by the JdbcTemplate class, for each of a number of updates in a batch using the same SQL. It provides the SQL with placeholders, implementations are responsible for setting necessary parameters. The JdbcTemplate will create the PreparedStatement and with the callback only being responsible for setting parameter values.
@FunctionalInterface public interface PreparedStatementSetter
This interface contains one method namely,
setValues(PreparedStatement ps): It sets parameter values on the given PreparedStatement.
void setValues(PreparedStatement ps) throws SQLException
- ps – the PreparedStatement to invoke setter methods.
- It throws SQLException – if an SQLException is encountered (i.e. there is no need to catch SQLException)
Spring JDBC Application
To understand the above concept, we will create a basic Spring JDBC application to access the data from the database. We will use the PostgreSQL database and Eclipse IDE to create this project.
Steps to Create the Application
Step 1: Create a database table in PostgreSQL
Create table – ‘frameworks‘ in PostgreSQL database with columns – ‘id’, ‘name’, ‘description’.
frameworks – table
Insert some data in the table like below.
Table – Data
Step 2: Create a Spring application in Eclipse
- Create Spring project – Spring_JDBC. Add postgresql jar and spring jar files to the project.
- The jar files are available in Maven Repository.
- The final project structure will be like below,
Project Structure
Step 3: Create Bean class, DAO class, and Mapper class files under the ‘com.geeks.beans’ package
Create ‘Framework.java‘ bean class file with ‘id’, ‘name’, ‘description’ as properties and their getter/setter methods.
Framework.java file:
package com.geeks.beans;
public class Framework {
private int id;
private String name;
private String description;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
} |
Create a ‘FrameworkDao.java‘ file that imports the’ JDBC Template’ class and ‘PreparedStatementSetter’ interface used to create SQL to query the database with the values using ‘PreparedStatement’.
FrameworkDao.java file:
package com.geeks.beans;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
public class FrameworkDao {
private JdbcTemplate jdbcTemp;
public JdbcTemplate getJdbcTemp() {
return jdbcTemp;
}
public void setJdbcTemp(JdbcTemplate jdbcTemp) {
this.jdbcTemp = jdbcTemp;
}
public Framework getDetail(final String name) {
final String SQL = "select * from public.frameworks where name = ? ";
List <Framework> details = jdbcTemp.query(
SQL, new PreparedStatementSetter() {
public void setValues(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setString(1, name);
}
},
new FrameworkMapper());
return details.get(0);
}
} |
- We are using the select query to fetch the details from the database table.
- By using the JDBC Template, query the database bypassing three parameters, SQL – query, PreparedStatementSetter – to set the value and FrameworkMapper – to map the data to the respective properties.
- Create a ‘FrameworkMapper.java‘ file that implements the RowMapper interface which is used to map the data fetched from the database to the bean property values.
FrameworkMapper.java file:
package com.geeks.beans;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class FrameworkMapper implements RowMapper<Framework>{
@Override
public Framework mapRow(ResultSet rs, int map) throws SQLException {
Framework framework = new Framework();
framework.setId(rs.getInt("id"));
framework.setName(rs.getString("name"));
framework.setDescription(rs.getString("description"));
return framework;
}
} |
- We need to override the ‘mapRow()’ method of the RowMapper interface.
- Get the Framework bean object and map/set the property values with the fetched values from the database.
Step 4: Create resource file – XML configuration under ‘com.geeks.resources’ package
Create ‘applicationContext.xml‘ file to configure the datasource and bean objects.
applicationContext.xml file:
<?xml version="1.0" encoding="UTF-8"?>
xsi:schemaLocation="http://www.springframework.org/schema/beans
<bean id = "dataSource" class = "org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name = "driverClassName" value = "org.postgresql.Driver"/>
<property name = "username" value = "root"/>
<property name = "password" value = "root"/>
</bean>
<bean id="jdbcTemp" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="framework" class="com.geeks.beans.FrameworkDao">
<property name="jdbcTemp" ref="jdbcTemp"></property>
</bean>
</beans>
|
- As we are using postgresql, we need to specify the respective ‘driverClassName’, ‘url’, ‘username’ and ‘password’ of the postgresql database.
- Configure the FrameworkDao bean class file with reference to the JDBC Template object.
Step 5: Create a Test file to run the application under ‘com.geeks.test’ package
Create a ‘FrameworkTest.java‘ file that contains the main() method to run the project.
FrameworkTest.java file:
package com.geeks.test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.geeks.beans.Framework;
import com.geeks.beans.FrameworkDao;
public class FrameworkTest {
public static void main(String[] args) {
ApplicationContext con = new ClassPathXmlApplicationContext("com/geeks/resources/applicationContext.xml");
FrameworkDao dao = (FrameworkDao) con.getBean("framework");
Framework frame = dao.getDetail("spring");
System.out.println("Java Framework");
System.out.println(frame.getId()+". "+frame.getName()+": "+frame.getDescription());
}
} |
- Get the ‘ApplicationContext’ object and using that, get the bean object.
- Call the ‘getDetail()’ method in the ‘FrameworkDao.java’ class to query the database and fetch the values.
- Finally, print the fetched data in the console.
Step 6: Run the Application
- To run the Test file, right-click ‘Run As -> Java Application’.
- We will get the below output in the console.
Output