Spring JDBC

Spring JDBC

 

Data base Access

Data base access possible in multiple ways, we can connect through plain JDBC, IBATIS, Hibernate, spring ORM, JPA, and Spring JDBC.

but if we use any Frameworks, it reduces the program development complexity for developer.

 

Problem with plain JDBC

Create a connection

Create a statement

Start the transaction

Execute the statement

Supports only Positional Parameters(?)

Iterate the result set, and get the values

Commit the transaction

Close the statements

Close the connections

 

Lot of boiler plate code problem with this plain JDBC and also, we should perform exception handling explicitly.

 

Spring frameworks eliminates all the above problems. And it provides methods to write the query directly.

 

 

Spring JDBC

Spring is loosely coupled framework it is developed in modular fashion.

Spring JDBC is one of the modules in spring framework.

Spring JDBC is built on top of JDBC API.

 

Spring JDBC provides multiple Templates to interact with Databases.

1) JdbcTemplate

2) NamedParameterJdbcTemplate

3) SimpleJdbcInsert

4) SimpleJdbcCall

 

based on requirement we can use any above templates.

 

 

1) JdbcTemplate

It is the central class in the spring JDBC, remaining approaches i.e., NamedParameterJdbc templates, and SimpleJdbc internally uses this JdbcTemplate only.

We can perform all CRUD operations i.e., insertion, updating, deletion and retrieving data with the help of JdbcTemplate.

JdbcTemplate provides methods, for reducing boiler plate code problems.

JdbcTemplate methods are execute(), update(), query(), queryForXX() methods and etc..

  

JdbcTemplate methods are we can divide into two parts 

a) Direct methods

b) Callback Interface

  

a) Direct methods

Direct methods are used to perform crud operations, but we are getting fixed custom results.

i.e., records as list, Map, etc.!

  

DDL operations (drop, rename, create, truncate, alter)

Execute()

 

DML operations (insert, update, delete)

Update()

 

DQL operations (select)

query()

queryForList()

queryForInt()

queryForLong()

queryForObject()

 

  

b) Callback Interface

Callback Interface is used to get the records in custom format.

i.e., like BO, list Bo, etc.!

 

Spring Jdbc provides multiple callback interfaces are

RowMapper(I)

ResultSetExtractor(I)

RowCallbackHandler(I)

PreparedStatementCallback(I)

PreparedStatementCreater(I)

and etc.!

 

Note: JdbcTemplate doesn’t support named params, it supports only positional param(?)

 

2) NamedParameterJdbcTemplate

Spring provides another way to insert the data by named parameter.

The limitation with the positional param(?) is providing index and setting values to those parameters accord to an index, it is bit complex especially if the query having multiple records.

 

Examples:

Query with positional param

Select empNumber,employeeName from employee where empNumber>=? And empNumber<=?

  

Query with named param

Select empNumber,employeeName from employee where empNumber>=:min And empNumber<=:max

 

Note: JdbcTemplate doesn’t support named params, it supports only positional param(?)

Note: NamedParameterJdbcTemplate doesn’t support positional params(?), it supports only named parameters.

 

3) SimpleJdbcInsert

JdbcTemplate, NamedParameterJdbcTemplate are thread-safe i.e., they are single thread objects, so they allow single thread at a time.

A SimpleJdbcInsert is a multi-threaded, reusable object providing easy insert capabilities for a table. 

It provides meta-data processing to simplify the code needed to construct a basic insert statement.

While working with SimpleJdbcInsert we don’t write SQL query separately, just provide DS, DB table name, Map of column names, values then insert SQL query will generate dynamically.

SimpleJdbcInsert template = new SimpleJdbcInsert(dataSource()).withTableName("table_name");

4) SimpleJdbcCall

A SimpleJdbcCall is a multi-threaded, reusable object representing a call to a stored procedure or a stored function. It provides meta-data processing to simplify the code needed to access basic stored procedures/functions.

All you need to provide is the name of the procedure/function and a Map containing the parameters when you execute the call.

The names of the supplied parameters will be matched up with in and out parameters declared when the stored procedure was created.

SimpleJdbcCall procedureName = simpleJdbcTemplate.withProcedureName("Procedure_Name");

SqlParameterSource inputParam = new MapSqlParameterSource().addValue("INPUT_VALUE", inputValue); 

Map<String, Object> execute = procedureName.execute(inputParam );

TableName name= new TableName ();

name.setPersonName((String)execute.get("CUSTOMER_NAME"));