Spring – Prepared Statement JDBC Template
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.
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’.
Insert some data in the table like below.
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,
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.
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’.
- 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.
- 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.
- 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.
- 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.