Spring JdbcTemplate

Introduction

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.

Advantage of Spring JdbcTemplate

Spring JdbcTemplate eliminates all the problems of JDBC API. It provides you methods to write the queries directly, so it saves a lot of work and time.
1. Opens connection.
2. Prepares and executes statements.
3. Set up the loop to iterate through the results.
4. Process any exception.
4. Handle transactions.
5. Close the connection, statement, and resultset.

Spring Jdbc Approaches

Spring framework provides following approaches for JDBC database access:

JdbcTemplate
NamedParameterJdbcTemplate
SimpleJdbcTemplate
SimpleJdbcInsert and SimpleJdbcCall

JdbcTemplate class

For Insertion/deletion/updation we can use the method like update/update args

i.e

public int update(String query);
public int update(String query,Object... args);

Ex : For Insert/delete/Update

String sampleInsertQuery = "insert into signorsymptom(disease,diseaseType,AccountNumber) values(?,?,?)"
jdbcTemplate.update(sampleInsertQuery,disease,disType,AccountNumber);

Ex : For Batch Insertion

String insertSignOrSymptomQuery = "insert into signorsymptom(disease,diseaseType,AccountNumber) values(?,?,?)";

jdbcTemplate.batchUpdate(insertSignOrSymptomQuery, new BatchPreparedStatementSetter() {

                        @Override
                        public void setValues(PreparedStatement pstmt, int i) throws SQLException {
                            pstmt.setString(1, disease);
                            pstmt.setString(2, disType);
                            pstmt.setString(3, AccountNumber);
                        }

                        @Override
                        public int getBatchSize() {
                            return 0;
                        }
                    });

Ex : For Retrieving the data

If we want to Retrieve the data from select query which is only one distinct element like we are using queryForObject i.e

String fractureQuery1 = "select distinct BodySystem from MST_StandardBodyParts2 where operationName=?";
                bodySystem = jdbcTemplate.queryForObject(fractureQuery1, String.class, opName);

here we can retrieve the objects like i.e String,Integer,Boolean etc...

Ex : For Retrieving the Result set data

To Fetch the records from the database we are using two techniques
i.e
ResultSetExtractor
RowMapper interfaces

But if we facilitate mapping between columns and fields of pojo class we are using advanced technique using BeanPropertyRowMapper There are several built-in classes that directly map the result set to a list of objects,
for example, BeanPropertyRowMapper. i.e

String hematomaQuery = "select distinct Code,bodypart,BodyPartLoc from MST_StandardBodyParts2 where Opnname=?";
    List<PcsBodyPart> pcsBodyPartList = jdbcTemplate.query(hematomaQuery,
            new BeanPropertyRowMapper<PcsBodyPart>(PcsBodyPart.class), "Hematoma");

            for (PcsBodyPart pcsBodyPart : pcsBodyPartList) {
                        code = pcsBodyPart.getCode();
                        bpart = pcsBodyPart.getStandardBodyPart();
                        bpartLoc = pcsBodyPart.getBodyPartLoc();
            }                       

Ex : For Executing normal StoredProcedure

For executing normal stored Procedure we are following technique.

    String standardPcs3SP = "execute NLP_InsertStandardPcs ?,?";            
    jdbcTemplate.update(standardPcs3SP, accountNumber, documentId);

Ex : For Executing StoredProcedure with OUT Parameter

If we are executing the simple StoredProcedure with OUT Parameter. We are using SimpleJdbcCall

Ex:

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
                    .withProcedureName("LabProcedurename");
            Map<String, Object> inParamMap = new HashMap<String, Object>();
            inParamMap.put("PathFileName", reportPath.toAbsolutePath().toString());
            inParamMap.put("ReportDate", reportDate);
            SqlParameterSource source = new MapSqlParameterSource(inParamMap);
            Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(source);
            insertStatus = (int) simpleJdbcCallResult.get("OutParameterName");