Advanced Java

Anytime, anywhere

-Professor Shweta Waghmare

Module 5 : JDBC Data Access with
Spring
Topics in Module 5

❏ Managing JDBC Connection

❏ Configuring Data Source to obtain JDBC Connection
❏ Data Access operations with JdbcTemplate and Spring (Important)
⇒ https://docs.spring.io/spring-framework/docs/3.0.x/spring-framework-reference/html/jdbc.html
❏ RDBMS operation classes (important)
❏ Modelling JDBC Operations as Java Objects (may be ask)
❏ Self learning topics: JDBC Architecture and basic JDBC Program using DML
operation
JDBC Overview(Just for reference)
JDBC stands for Java Database Connectivity. JDBC is a Java API to connect and execute the query
with the database. It is a part of JavaSE (Java Standard Edition). JDBC API uses JDBC drivers to
connect with the database. There are four types of JDBC drivers:

● JDBC-ODBC Bridge Driver,

● Native Driver,
● Network Protocol Driver, and
● Thin Driver
Architecture
Java Database Connectivity with 5 Steps
There are 5 steps to connect any java application with the database using JDBC. These steps are
as follows:

● Register the Driver class

● Create connection
● Create statement
● Execute queries(create,insert,update,alter,drop,create procedure,join,select)
● Close connection
● Example of traditional JDBC

Refer : https://www.javatpoint.com/steps-to-connect-to-the-database-in-java
Spring - JDBC Framework Overview

While working with the database using plain old JDBC, it becomes
cumbersome to write unnecessary code to handle exceptions, opening and
closing database connections, etc.
However, Spring JDBC Framework takes care of all the low-level details
starting from opening the connection, prepare and execute the SQL
statement, process exceptions, handle transactions and finally close the
connection.
Spring - JDBC Framework Overview

● So what you have to do is just define the connection parameters and

specify the SQL statement to be executed and do the required work for
each iteration while fetching data from the database.
● Spring JDBC provides several approaches and correspondingly different
classes to interface with the database.
● The classic and the most popular approach which makes use of
JdbcTemplate class of the framework.
● This is the central framework class that manages all the database
communication and exception handling.
Why ?
The problems of JDBC API are as follows:
● We need to write a lot of code before and after executing the query, such as
creating connection, statement, closing resultset, connection etc.
● We need to perform exception handling code on the database logic.
● We need to handle transaction.
● Repetition of all these codes from one to another database logic is a time
consuming task.
Data access with JDBC
The value-add provided by the Spring Framework JDBC abstraction is perhaps best
shown by the sequence of actions outlined in the given table.

The table shows what actions Spring will take care of and which actions are the
responsibility of you, the application developer.

The Spring Framework takes care of all the low-level details that can make JDBC such
a tedious API to develop with.
 Action Spring You

Define connection parameters. X

Open the connection. X

Specify the SQL statement. X

Declare parameters and provide parameter values X

Prepare and execute the statement. X

Set up the loop to iterate through the results (if any). X

Do the work for each iteration. X

Process any exception. X

Handle transactions. X

Close the connection, statement and resultset. X

The package hierarchy(just ref)
● The Spring Framework's JDBC abstraction framework consists of four different packages,
namely core, dataSource, object, and support.

● The org.springframework.jdbc.core package contains the JdbcTemplate class and its

various callback interfaces, plus a variety of related classes.

● The org.springframework.jdbc.datasource package contains a utility class for easy

DataSource access, and various simple DataSource implementations that can be used for
testing and running unmodified JDBC code outside of a J2EE container.

The utility class provides static methods to obtain connections from JNDI and to close
connections if necessary. It has support for thread-bound connections, e.g. for use with
DataSourceTransactionManager.
The package hierarchy
● Next, the org.springframework.jdbc.object package contains classes that represent RDBMS
queries, updates, and stored procedures as thread safe, reusable objects. This approach is
modeled by JDO, although of course objects returned by queries are “disconnected” from the
database.

● Finally the org.springframework.jdbc.support package is where you find the SQLException

translation functionality and some utility classes.

● Exceptions thrown during JDBC processing are translated to exceptions defined in the
org.springframework.dao package. This means that code using the Spring JDBC abstraction
layer does not need to implement JDBC or RDBMS-specific error handling.
Choosing an approach for JDBC database access
There are a number of options for selecting an approach to form the basis for your JDBC
database access.

There are three flavors of the JdbcTemplate, a new "SimpleJdbc" approach taking
advantage of database metadata, and there is also the "RDBMS Object" style for a more
object oriented approach similar in style to the JDO Query design.

Keep in mind that even if you start using one of these approaches, you can still mix and
match if there is a feature in a different approach that you would like to take advantage
of.

All approaches requires a JDBC 2.0 compliant driver and some advanced features require
a JDBC 3.0 driver.
JDBC database access approach

● JdbcTemplate (Java Class)

● NamedParameterJdbcTemplate
● SimpleJdbcTemplate
● SimpleJdbcInsert and SimpleJdbcCall
● RDBMS Objects including MappingSqlQuery
● SqlUpdate and StoredProcedure
JdbcTemplate

● It is the classic Spring JDBC approach and the most popular.

● This "lowest level" approach and all others use a JdbcTemplate under the covers, and all
are updated with Java 5 support such as generics.

NamedParameterJdbcTemplate

● It wraps a JdbcTemplate to provide named parameters instead of the traditional JDBC "?"
placeholders.
● This approach provides better documentation and ease of use when you have multiple
parameters for an SQL statement.

SimpleJdbcTemplate

● It combines the most frequently used operations of JdbcTemplate and

NamedParameterJdbcTemplate.
SimpleJdbcInsert and SimpleJdbcCall

● It optimize database metadata to limit the amount of necessary configuration. This

approach simplifies coding so that you only need to provide the name of the table or
procedure and provide a map of parameters matching the column names.
● This only works if the database provides adequate metadata. If the database doesn't
provide this metadata, you will have to provide explicit configuration of the
parameters.

RDBMS Objects including MappingSqlQuery, SqlUpdate and StoredProcedure

● It requires you to create reusable and thread-safe objects during initialization of your
data access layer.
● This approach is modeled after JDO Query wherein you define your query string,
declare parameters, and compile the query. Once you do that, execute methods can be
called multiple times with various parameter values passed in.
Just for reference
What does thread-safe mean in Java?
When multiple threads are working on the same data, and the value of our data is
changing, that scenario is not thread-safe and we will get inconsistent results. When a
thread is already working on an object and preventing another thread on working on
the same object, this process is called Thread-Safety
Spring JdbcTemplate
Spring JdbcTemplate is a powerful mechanism to connect to the database and execute SQL
queries. It internally uses JDBC api, but eliminates a lot of problems of JDBC API.

Problems of JDBC API

The problems of JDBC API are as follows:

● We need to write a lot of code before and after executing the query, such as creating
connection, statement, closing resultset, connection etc.
● We need to perform exception handling code on the database logic.
● We need to handle transaction.
● Repetition of all these codes from one to another database logic is a time consuming task.
Advantage of Spring JdbcTemplate

Spring JdbcTemplate eliminates all the mentioned problems of JDBC API.

It provides you methods to write the queries directly, so it saves a lot of work and time.

● Solution of JDBC problem solved by Spring JDBC

● Spring JDBC provides class JdbcTemplate which has all important methods to
perform operation with database
JdbcTemplate class
It is the central class in the Spring JDBC support classes.

It takes care of creation and release of resources such as creating and closing of
connection object etc. So it will not lead to any problem if you forget to close the
connection.

It handles the exception and provides the informative exception messages by the help of
exception classes defined in the org.springframework.dao package.

We can perform all the database operations by the help of JdbcTemplate class such as
insertion, updation, deletion and retrieval of the data from the database.
JdbcTemplate class

● Code using this class need only implement callback interfaces, giving them a clearly
defined contract.
● The PreparedStatementCreator callback interface creates a prepared statement given a
Connection, providing SQL and any necessary parameters.
● The ResultSetExtractor interface extracts values from a ResultSet.
● Also PreparedStatementSetter and RowMapper for two popular alternative callback
interfaces.
● Can be used within a service implementation via direct instantiation with a DataSource
reference, or get prepared in an application context and given to services as bean
reference.
JdbcTemplate class

● Note: The DataSource should always be configured as a bean in the application

context, in the first case given to the service directly, in the second case to the
prepared template.
● Because this class is parameterizable by the callback interfaces and the
SQLExceptionTranslator interface, there should be no need to subclass it.
● All SQL operations performed by this class are logged at debug level, using
“org.springframework.jdbc.core.JdbcTemplate" as log category.

● NOTE: An instance of this class is thread-safe once configured.

❏ Managing JDBC Connection
DriverManagerDataSource (java class that implements DataSource)
The DriverManagerDataSource is used to contain the information about the database such as
driver class name, connection URL, username and password.

There are a property named datasource in the JdbcTemplate class of

DriverManagerDataSource type.

So, we need to provide the reference of DriverManagerDataSource object in the JdbcTemplate

class for the datasource property.
<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/mydb" />
<property name="username" value="root" />
<property name="password" value="root" />
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">

<property name="dataSource" ref="ds"></property>
</bean>
Basic Example
Example of Spring JdbcTemplate

We are assuming that you have created the following table inside the Oracle10g database.

create table employee(

id number(10),
name varchar2(20),
salary number(10)
);
Employee.java

This class contains 3 properties with constructors and setter and getters.

package com.javatpoint;
public class Employee {
private int id;
private String name;
private float salary;
public void getId(){}
Public void setId(){}
//no-arg and parameterized constructors
//getters and setters
}
EmployeeDao.java :It contains one property jdbcTemplate and three methods saveEmployee(),
updateEmployee and deleteEmployee().
import org.springframework.jdbc.core.JdbcTemplate;
public class EmployeeDao {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate; }

public int saveEmployee(Employee e){

String query="insert into employee values(
'"+e.getId()+"' ,'"+e.getName()+"' ,'"+e.getSalary()+"')";
return jdbcTemplate.update(query); }

public int updateEmployee(Employee e){

String query="update employee set
name='"+e.getName()+"',salary='"+e.getSalary()+"' where id='"+e.getId()+"' ";
return jdbcTemplate.update(query); }

public int deleteEmployee(Employee e){

String query="delete from employee where id='"+e.getId()+"' ";
return jdbcTemplate.update(query); } }
Configuration : applicationContext.xml(user defined name)

<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />
<property name="username" value="system" />
<property name="password" value="oracle" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="ds"></property>
</bean>
<bean id="edao" class="com.javatpoint.EmployeeDao">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
Test.java : This class gets the bean from the applicationContext.xml file and calls the methods.
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
//Main Method
ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");
EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");
int status=dao.saveEmployee(new Employee(102,"Amit",35000)); //single row
System.out.println(“Row affected”+ status); // 1

int status=dao.updateEmployee(new Employee(102,"Sonoo",15000)); //single data row

System.out.println(“Row affected”+ status); // 1

Employee e=new Employee();

e.setId(103);
int status= dao.deleteEmployee(e); // delete (how many)query through deleteEmployee(e)
System.out.println(“Row affected”+ status); // 1

} }
Update method v2
 PreparedStatementCallback Interface
● We can execute parameterized query using Spring JdbcTemplate by the help of execute()
method of JdbcTemplate class.
● To use parameterized query, we pass the instance of PreparedStatementCallback in the execute
method.
Syntax of execute method to use parameterized query
public T execute(String sql,PreparedStatementCallback<T>);
It processes the input parameters and output results. In such case, you don't need to care about
single and double quotes.
Method of PreparedStatementCallback interface
It has only one method doInPreparedStatement. Syntax of the method is given below:
public T doInPreparedStatement(PreparedStatement ps)throws SQLException,
DataAccessException //PreparedStatement ps ⇒ tradition jdbc
Basic Example
Example of Spring JdbcTemplate

We are assuming that you have created the following table inside the Oracle10g database.

create table employee(

id number(10),
name varchar2(100),
salary number(10)
);
Employee.java

This class contains 3 properties with constructors and setter and getters.

package com.javatpoint;
public class Employee {
private int id;
private String name;
private float salary;
private int age; //updating code
//no-arg and parameterized constructors
//getters and setters
}
EmployeeDao.java

public class EmployeeDao {

private JdbcTemplate jdbcTemplate;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

this.jdbcTemplate = jdbcTemplate;

}
EmployeeDao.java
Boolean saveEmployeeByPreparedStatement(final Employee e){ //start
String query="insert into employee values(?,?,?)";
return jdbcTemplate.execute(query, new PreparedStatementCallback<Boolean>(){
@Override
public Boolean doInPreparedStatement(PreparedStatement ps)
throws SQLException, DataAccessException {
// changes format : id,salary,age,course
ps.setInt(1,e.getId());
ps.setString(2,e.getName());
ps.setInt(3,e.getSalary());
return ps.execute(); } });
} //end
Test.java : This class gets the bean from the applicationContext.xml file and calls the
saveEmployeeByPreparedStatement() method.

package com.javatpoint;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class Test {
public static void main(String[] args) {
ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");
EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");
dao.saveEmployeeByPreparedStatement(new Employee(108,"Amit",35000));
}
}
ResultSetExtractor Interface
We can easily fetch the records from the database using query() method of JdbcTemplate class
where we need to pass the instance of ResultSetExtractor.
Syntax of query method using ResultSetExtractor
public T query(String sql,ResultSetExtractor<T> rse)

ResultSetExtractor interface can be used to fetch records from the database. It accepts a
ResultSet and returns the list.

Method of ResultSetExtractor interface

It defines only one method extractData that accepts ResultSet instance as a parameter. Syntax of
the method is given below:
public T extractData(ResultSet rs)throws SQLException,DataAccessException
Just for
reference
(prerequisite)
Example of ResultSetExtractor Interface to show all the records of the table
create table employee(
id number(10),
name varchar2(100),
salary number(10)
);

Employee.java
package com.javatpoint;
public class Employee {
private int id;
private String name;
private float salary;
//no-arg and parameterized constructors
//getters and setters

public String toString(){

return id+" "+name+" "+salary;
} }
EmployeeDao.java

package com.javatpoint;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor; ///1 import pkg

public class EmployeeDao {

//pass object of Jdbctemplate
private JdbcTemplate template;

public void setTemplate(JdbcTemplate template) {

this.template = template;
}
public List<Employee> getAllEmployees(){
return template.query("select * from employee", new //template.query() start
ResultSetExtractor<List<Employee>>(){
@Override
public List<Employee> extractData(ResultSet rs) throws SQLException,
DataAccessException {
List<Employee> list=new ArrayList<Employee>();
while(rs.next()){
Employee e=new Employee();
e.setId(rs.getInt(1)); //getting and setting to variable
e.setName(rs.getString(2)); //getting and setting to variable
e.setSalary(rs.getInt(3)); //getting and setting to variable
list.add(e);
}
return list;
}
}); //template.query() end
} }
Test.Java
package com.javatpoint;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class Test {
public static void main(String[] args) {
ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");
EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");
List<Employee> list=dao.getAllEmployees();
for(Employee e:list)
System.out.println(e);
}
}
RowMapper Interface
Implementing RowMapper
● Spring provides a RowMapper interface for mapping a single row of a ResultSet to
an object.
● It can be used for both single and multiple row queries. It is parameterized as of
Spring 3.0.
public interface RowMapper<T> {
T mapRow(ResultSet rs, int rowNum)
throws SQLException;
}

An interface used by JdbcTemplate for mapping rows of a ResultSet on a per-row basis.

Implementations of this interface perform the actual work of mapping each row to a result
object, but don’t need to worry about exception handling. SQLExceptions will be caught and
handled by the calling JdbcTemplate.
RowMapper Interface

RowMapper objects are typically stateless and thus reusable; they are an ideal
choice for implementing row-mapping logic in a single place.
Alternatively, consider subclassing MappingSqlQuery from the jdbc.object package:
Instead of working with separate JdbcTemplate and RowMapper objects, you can
build executable query objects (containing row-mapping logic) in that style.

Say for example, when we are selecting records from an employee table, we will
iterate over the result set to get the individual values which won’t be ideal for
situations, especially in Java where we want to map records from a database to
individual Java objects. Also the question of re-usability comes into the picture as the
above code doesn’t represent for getting itself re-used.

Spring RowMapper interfaces come into the rescue for such situations.
RowMapper Interface
Like ResultSetExtractor, we can use RowMapper interface to fetch the records from the
database using query() method of JdbcTemplate class. In the execute of we need to pass the
instance of RowMapper now.

Syntax of query method using RowMapper (Querying for Multiple Rows)

public T query(String sql,RowMapper<T> rm)

RowMapper interface allows to map a row of the relations with the instance of user-defined
class. It iterates the ResultSet internally and adds it into the collection. So we don't need to
write a lot of code to fetch the records as ResultSetExtractor.
RowMapper

Advantage of RowMapper over ResultSetExtractor

RowMapper saves a lot of code because it internally adds the data of ResultSet into the
collection.
RowMapper Interface continue…

Method of RowMapper interface

It defines only one method mapRow that accepts ResultSet instance and int as the
parameter list.

public T mapRow(ResultSet rs, int rowNumber)throws SQLException

Example 1 of RowMapper Interface to show all the records of the table

We are assuming that you have created the following table inside the database.

create table employee(

id number(10),
name varchar2(100),
salary number(10)
);
Employee.java

This class contains 3 properties with constructors and setter and getters and one extra
method toString().

package com.javatpoint;
public class Employee {
private int id;
private String name;
private float salary;
//no-arg and parameterized constructors
//getters and setters
public String toString(){
return id+" "+name+" "+salary;
}
}
Example of RowMapper Interface to show all the records of the table
EmployeeDao.java

public List<Employee> getAllEmployeesRowMapper(){

return template.query("select * from employee",new RowMapper<Employee>(){
@Override
public Employee mapRow(ResultSet rs, int rownumber) throws SQLException {
Employee e=new Employee();
e.setId(rs.getInt(1));
e.setName(rs.getString(2));
e.setSalary(rs.getInt(3));
return e;
}
});
}
Test.java

package com.javatpoint;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class Test {
public static void main(String[] args) {
ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");
EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");
List<Employee> list=dao.getAllEmployeesRowMapper();
for(Employee e:list)
System.out.println(e);
}
}
Output : ….//Display data from database
Querying for Single Row:

public class EmployeeMapper implements RowMapper {

public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
Employee employee = new Employee();
employee.setEmpid(rs.getInt("empid"));
employee.setName(rs.getString("name"));
employee.setAge(rs.getInt("age"));
employee.setSalary(rs.getLong("salary"));
return employee;
}
Querying for Single Row:

Pass it to queryForObject() method, the returned result will call your custom mapRow()
method to match the value into the property.

public Employee getEmployee(Integer empid) {

String SQL = "SELECT * FROM Employee WHERE empid = ?";

Employee employee = (Employee) jdbcTemplateObject.queryForObject(SQL, new

Object[]{empid}, new EmployeeMapper());

return employee;

}
ResultSetExtractor vs Rowmapper
● A RowMapper is usually a simpler choice for ResultSet processing, mapping
one result object per row instead of one result object for the entire ResultSet.
● ResultSetExtractor is suppose to extract the whole ResultSet (possibly multiple
rows), while RowMapper is feeded with row at a time
RowMapper
● It is used to fetch records from the database using the query() method of JdbcTemplate class
by passing the instance of row mapper.
● It allows us to map a row of the relations with the instance of a user-defined class.
● Syntax of query method: public T query(String sql, RowMapper<T> rm)
● It defines only one method mapRow that accepts ResultSet instance and int as the parameter
list
● Its objects are typically stateless and thus reusable; they are an ideal choice for implementing
row-mapping logic in a single place.
● It is usually a simpler choice for ResultSet processing,
● It saves a lot of code because it internally adds the data of ResultSet into the collection.
● In the row-mapper interface, SQLExceptions will be caught and handled by the calling
JdbcTemplate
● All Known Implementing Classes of RowMapper are following:
BeanPropertyRowMapper, ColumnMapRowMapper, DataClassRowMapper,
MappingSqlQueryWithParameters.RowMapperImpl, SingleColumnRowMapper and
UpdatableSqlQuery.RowMapperImpl
ResultSetExtractor
● It is used to fetch records from the database using the query() method of JdbcTemplate class
by passing the instance of ResultSetExtractor.
● It accepts a ResultSet and returns the list.
● Syntax of query method-: public T query(String sql,ResultSetExtractor<T> rse)
● It defines only one method extractData that accepts ResultSet instance as a parameter
● Its object is typically stateless and thus reusable, as long as it doesn’t access stateful resources
or keep the resulting state within the object.
● Implementations of Result Extractor interface perform the work of extracting results from a
ResultSet.
● In the result Extractor interface, we don’t need to worry about exception handling because
becauseSQLExceptions will be caught and handled by calling JdbcTemplate.
● It is used to extract the whole ResultSet (possibly multiple rows).
● All Known Implementing Classes of ResultSetExtractor Interface are following:
● AbstractLobStreamingResultSetExtractor, RowMapperResultSetExtractor and
SqlRowSetResultSetExtractor
RDBMS Operation classes
● JdbcTemplate is ideal for simple queries and updates, and when you need to build
SQL strings dynamically, but sometimes you might want a higher level of abstraction,
and a more object-oriented approach to database access.

● This is provided by the org.springframework.jdbc.object package. It contains the

SqlQuery, SqlMappingQuery, SqlUpdate, and StoredProcedure classes that are
intended to be the central classes used by most Spring JDBC applications.

● These classes are used together with a DataSource and the SqlParameter class.
Each of the RDBMS Operation classes is based on the RDBMSOperation class and they
all use a JdbcTemplate internally for database access.

● As a user of these classes you will have to provide either an existing JdbcTemplate or
you can provide a DataSource and the framework code will create a JdbcTemplate when
it needs one.
RDBMS Operation classes
● Spring's RDBMS Operation classes are parameterized operations that are thread
safe once they are prepared and compiled. You can safely create a single
instance for each operation that you define.
● The preparation consists of providing a datasource and defining all the parameters
that are needed for the operation. This means that we have to be a little bit careful
when we create these operations.
● The recommended method is to define the parameters and compile them in
the constructor. That way there will not be any risk for thread conflicts.
*JDBC Operation as Java Object
The org.springframework.jdbc.object package contains classes that let you access the
database in a more object-oriented manner.

As an example, you can run queries and get the results back as a list that contains
business objects with the relational column data mapped to the properties of the
business object.

You can also run stored procedures and run update, delete, and insert statements.
SqlQuery : https://www.tutorialspoint.com/springjdbc/springjdbc_sqlquery.htm

SqlQuery is a reusable, thread-safe class that encapsulates an SQL query.

Subclasses must implement the new RowMapper(..) method to provide a RowMapper

instance that can create one object per row obtained from iterating over the ResultSet
that is created during the execution of the query.

The SqlQuery class is rarely used directly, because the MappingSqlQuery subclass provides
a much more convenient implementation for mapping rows to Java classes.

Other implementations that extend SqlQuery are MappingSqlQueryWithParameters and

UpdatableSqlQuery.

Ref :
https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/obj
ect/SqlQuery.html
Class Declaration
Following is the declaration for org.springframework.jdbc.object.SqlQuery class −
public abstract class SqlQuery<T> extends SqlOperation

Practically :
● Step 1 − Create a JdbcTemplate object using a configured datasource.
● Step 2 − Create a StudentMapper object implementing RowMapper interface.
● Step 3 − Use JdbcTemplate object methods to make database operations while
using SqlQuery object.

Used for how to read a Query using SqlQuery Object. We'll map read records from
Student Table to Student object using StudentMapper object.
_______________________________________________________________

Where,
● SQL − Read query to read all student records.
● jdbcTemplateObject − StudentJDBCTemplate object to read student records from the database.
● StudentMapper − StudentMapper object to map the student records to student objects.
● SqlQuery − SqlQuery object to query student records and map them to student objects.
 SqlUpdate : https://www.tutorialspoint.com/springjdbc/springjdbc_sqlupdate.htm

The SqlUpdate class encapsulates an SQL update.

As with a query, an update object is reusable, and, as with all RdbmsOperation classes, an update
can have parameters and is defined in SQL.

This class provides a number of update(..) methods analogous to the execute(..) methods of query
objects.

The SQLUpdate class is concrete. It can be subclassed — for example, to add a custom update
method. However, you do not have to subclass the SqlUpdate class, since it can easily be
parameterized by setting SQL and declaring parameters.
StoredProcedure
The StoredProcedure class is an abstract superclass for object abstractions of RDBMS
stored procedures.

The inherited sql property is the name of the stored procedure in the RDBMS.

To define a parameter for the StoredProcedure class, you can use an SqlParameter or one
of its subclasses. You must specify the parameter name and SQL type in the constructor.
StoredProcedure : https://www.tutorialspoint.com/springjdbc/springjdbc_storedprocedure.htm
For in parameters, in addition to the name and the SQL type, you can specify a scale for
numeric data or a type name for custom database types.

For out parameters, you can provide a RowMapper to handle mapping of rows returned
from a REF cursor. Another option is to specify an SqlReturnType that lets you define
customized handling of the return values.
Where,
● StoredProcedure − StoredProcedure object to represent a stored procedure.
● StudentProcedure − StudentProcedure object extends StoredProcedure to declare input,
output variable, and map result to Student object.
● student − Student object.
Following example will demonstrate how to call a stored procedure using Spring
StoredProcedure. We'll read one of the available records in Student Table by calling a stored
procedure. We'll pass an id and receive a student record.
MappingSqlQuery
MappingSqlQuery is a reusable query in which concrete subclasses must implement the
abstract mapRow(..) method to convert each row of the supplied ResultSet into an object of
the type specified.

A class which needs to use MappingSqlQuery has to override mapRow() method. It converts
each row to an object of given bean.

The subclass of MappingSqlQuery defines a constructor which passes query, data source
and where clause parameter to MappingSqlQuery.

Now while using the MappingSqlQuery, we need to call findObject() of MappingSqlQuery

and need to pass parameter of where clause of the sql query.
MappingSqlQuery is a reusable query in which concrete subclasses must implement the abstract
mapRow(..) method to convert each row of the supplied ResultSet into an object of the type specified.

The following example shows a custom query that maps the data from the t_actor relation to an instanc
of the Actor class:
The class extends MappingSqlQuery parameterized with the Actor type. The constructor for
this customer query takes the DataSource as the only parameter. In this constructor you call
the constructor on the superclass with the DataSource and the SQL that should be executed
to retrieve the rows for this query.

This SQL will be used to create a PreparedStatement so it may contain place holders for any
parameters to be passed in during execution.You must declare each parameter using the
declareParameter method passing in an SqlParameter. The SqlParameter takes a name and
the JDBC type as defined in java.sql.Types.

After you define all parameters, you call the compile() method so the statement can be
prepared and later executed. This class is thread-safe after it is compiled, so as long as these
instances are created when the DAO is initialized they can be kept as instance variables and
be reused.
The method in this example retrieves the customer with the id that is passed in as the only
parameter. Since we only want one object returned we simply call the convenience method
findObject with the id as parameter. If we had instead a query that returned a list of objects and
took additional parameters then we would use one of the execute methods that takes an array of
parameter values passed in as varargs.
RDBMS Operation classes
Ref : doc.spring(to know more)

1. https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/spr
ingframework/jdbc/object/RdbmsOperation.html

StoredProcedure Example :
https://javarevisited.blogspot.com/2013/04/spring-framework-tutorial-call-stor
ed-procedures-from-java.html#axzz7NLAowjTt