Spring JdbcTemplate Examples

Introduction

Here in this section we are discussing total all possible JdbcTemplate Examples.

Query + No Arguments + Rows Effected

Here in this exanple Simple Update query with no input and display the result of number of rows effected.

        String query= "update I18NUser set language='English'";
        int n = jdbcTemplate.update(query);

Query + Arguments + Rows Effected

2. Directly given the Inputs

    String query= "insert into I18NUser(id,username,password,language) values(?,?,?,?)";
        int n = jdbcTemplate.update(query,4,"Rajesh",1212,"Hindi");

2. by using Object array

        String query= "insert into I18NUser(id,username,password,language) values(?,?,?,?)";
        Object[] objectParam =  new Object[] {5,"Srinvas",4566,"French"};
        int n = jdbcTemplate.update(query,objectParam);

3. by using SimpleJdbcInsert Class

        SimpleJdbcInsert insertop = new SimpleJdbcInsert(jdbcTemplate);
        insertop.withTableName("I18NUser");

        Map<String, Object> parameters = new HashMap<String, Object>(4);
        parameters.put("id", 6);
        parameters.put("username", "Deepu");
        parameters.put("password", "456465");
        parameters.put("Language", "English");
        int n=insertop.execute(parameters);

4. For Batch Insertion

This example i.e with BatchPreparedStatementSetter/PreparedStatementSetter can be used for when dynamically set the input arguments.

String insertSignOrSymptomQuery = "insert into I18NUser(id,username,password,language) values(?,?,?,?)";

jdbcTemplate.batchUpdate(insertSignOrSymptomQuery, new BatchPreparedStatementSetter() {

    @Override
    public void setValues(PreparedStatement pstmt, int i) throws SQLException {
                            pstmt.setString(1, 7);
                            pstmt.setString(2, "Devi");
                            pstmt.setString(3, "2123");
                            pstmt.setString(4, "English");
        }

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

Query + Arguments + Single Record Entry

Here for getting resultSet data of Select queries we are following many techniques.

String query = "select Language from I18nUser where username=?";
   String language = jdbcTemplate.queryForObject(query, String.class, "hari");

Query + Arguments + Multiple Record

Here for getting resultSet data of Select queries we are following many techniques.

Introduction to get the ResultSet Data

Spring Framework also provides classes to extract the data from the result set. There are several built-in classes that directly map the result set to a list of objects, for example, BeanPropertyRowMapper. However, sometimes we need to implement custom logic that extracts the data. In order to do so, we need to implement custom mapper or extractor. i.e.
RowMapper and ResultSetExtractor.

Getting ResultSet Data by using ResultSetExtractor basic one.

Now let’s say that we want to import the result of the query into a single Map object where the key is the country and the value is a list of employees for the given country. We can’t do that directly with the RowMapper because there we have access only to a single row from the result set. To achieve this we must write a ResultSetExtractor.

public class StudentExtractor implements ResultSetExtractor<List<Student>>{

public List<Student> extractData(ResultSet rs) {
    try {
        List<Student> studentList = new ArrayList<Student>();
        while(rs.next()) {
            Student student = new Student();
            student.setId(rs.getInt(1));
            student.setUserName(rs.getString(2));
            student.setPassword(rs.getString(3));
            student.setLanguage(rs.getString(4));
            studentList.add(student);           
        }
        return studentList;
    }catch(Exception e) {
        e.printStackTrace();
        return null;
    }
}
}

String Query = "select id,username,password,language FROM i18nUser";
List<Student> stList = jdbcTemplate.query(query,new StudentExtractor()

The ResultSet Extractor used for whole ResultSet.
So if we need to map several rows returned from the query to a single object we should use extractor

Getting ResultSet Data by using RowMapper Interface.

It is better to implement RowMapper than ResultSetExtractor because a single row returned from the table has to be mapped to a single java object

public class StudentMapper implements RowMapper<Student>{

    @Override
    public Student mapRow(ResultSet rs, int arg1) throws SQLException {
        Student student=new Student();
        student.setId(rs.getInt(1));
        student.setUserName(rs.getString(2));
        student.setPassowrd(rs.getString(3));
        student.setLanguage(rs.getString(4));
        return student;
    }
}

To wrap it up, the difference between RowMapper and ResultSetExtrator is that with the mapper we have access to a returned row while with the extractor we can use the whole result set. So if we need to map several rows returned from the query to a single object we should use extractor, but in the other cases, a mapper should be sufficient.

Query + Single Argument + Single Record

Getting ResultSet Data by using BeanPropertyRowMapper basic one.

There are several built-in classes that directly map the result set to a list of objects, for example, BeanPropertyRowMapper. By using BeanPropertyRowMapper we can map the table column names/alias names with pojo class property names .

It maps each row of the resultset with a new instance of target class. The target class should have a no-argument default constructor. It maps the column names from resultset to properties of target class using public setter methods.
If column name doesn't match with property name, we need to provide a column alias matching the property name.

String query = "select distinct username,language from I18nUser where  username=?";
    List<Student> list = jdbcTemplate.query(query,
            new BeanPropertyRowMapper<Student>(Student.class), "Hari");

            for (Student student : pcsBodyPartList) {
                    username=student.getUserName();
                    language =student.getLanguage();
            }                       

If column name doesn't match with property name, we need to provide a column alias matching the property name.

For Ex : Here propertyname is username , but column name is name. then by using alias name we can map those one.

String query = "select name as userName,language from I18nUser where  username=?";

Stored Procedure + Arguments + Effected Rows

For executing normal stored Procedure we are following technique.

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

Stored Procedure + OUT Parameters

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

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");