DEV Community: sainiankitsaini

Nested Classes - CustomRowMapper !! Not a Problem Anymore !! - Part 2

sainiankitsaini — Tue, 29 Mar 2022 04:07:12 +0000

What is Spring Boot:
Spring Boot is an open source, microservice-based Java web framework.

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

Introduction
Here we are going to use custom row mapper when we want list of objects as return type.

Problem in RowMapper for nested Classes:
We can't directly assign parameters returned from DB to the class model because we never able to exact individual primitive data types variable due to nesting of classes.

Class Diagram used in this Example:

Simple Class : Not Nested Class

SportsMasterDTO.java



package workspace;

public class SportsMasterDTO {
  private int id;
  private String sportsCode;

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public String getSportsCode() {
    return sportsCode;
  }

  public void setSportsCode(String sportsCode) {
    this.sportsCode = sportsCode;
  }
}

SportsMetaDTO Class:



package workspace;

public class SportsMetaDTO {
  private int id;
  private int sportsName;
  private int sportsMasterId;
  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public int getSportsName() {
    return sportsName;
  }
  public void setSportsName(int sportsName) {
    this.sportsName = sportsName;
  }
  public int getSportsMasterId() {
    return sportsMasterId;
  }
  public void setSportsMasterId(int sportsMasterId) {
    this.sportsMasterId = sportsMasterId;
  }
  @Override
  public String toString() {
    return "SportsMetaDTO [id=" + id + ", sportsName=" + sportsName + ", sportsMasterId="
        + sportsMasterId + "]";
  }
}

ThresholdDTO class



package workspace;

public class ThresholdDTO {
  private int id;
  private int sportsMetaDataId;
  private String value;

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public int getSportsMetaDataId() {
    return sportsMetaDataId;
  }

  public void setSportsMetaDataId(int sportsMetaDataId) {
    this.sportsMetaDataId = sportsMetaDataId;
  }

  public String getValue() {
    return value;
  }

  public void setValue(String value) {
    this.value = value;
  }

  @Override
  public String toString() {
    return "ThresholdDTO [id=" + id + ", sportsMetaDataId=" + sportsMetaDataId + ", value=" + value
        + "]";
  }


}

Nested Class:

Nested Class: SportsThresholdCommonDTO



package workspace;

public class SportsThresholdCommonDTO {
  private SportsMasterDTO sportsMasterDTO;
  private SportsMetaDTO sportsMetaDTO;
  private ThresholdDTO thresholdDTO;
  public SportsMasterDTO getSportsMasterDTO() {
    return sportsMasterDTO;
  }
  public void setSportsMasterDTO(SportsMasterDTO sportsMasterDTO) {
    this.sportsMasterDTO = sportsMasterDTO;
  }
  public SportsMetaDTO getSportsMetaDTO() {
    return sportsMetaDTO;
  }
  public void setSportsMetaDTO(SportsMetaDTO sportsMetaDTO) {
    this.sportsMetaDTO = sportsMetaDTO;
  }
  public ThresholdDTO getThresholdDTO() {
    return thresholdDTO;
  }
  public void setThresholdDTO(ThresholdDTO thresholdDTO) {
    this.thresholdDTO = thresholdDTO;
  }
  @Override
  public String toString() {
    return "SportsThresholdCommonDTO [sportsMasterDTO=" + sportsMasterDTO + ", sportsMetaDTO="
        + sportsMetaDTO + ", thresholdDTO=" + thresholdDTO + "]";
  }
}

MYSQL Query Which contain joins on 3 tables with no limit



select 
  sm.id as sportsMasterId, 
  sm.sportsCode, 
  smd.id as sportsMetaId, 
  smd.sportsName, 
  smd.sportsMasterId, 
  t.id as thresholdId, 
  t.sportsMetaDataId, 
  t.value 
from 
  sportsMaster 
  inner join sportsMetaData smd on sm.id = smd.sportsMasterId 
  inner join threshold t on t.sporsMetaDataId = smd.id

How to use row mapper if mysql returns a list of objects for nested class:
Java Method



public List<AlertingMasterThresholdCommonDTO> getSportsThresholdCommonList() {

    String sql =

        "select sm.id as sportsMasterId, sm.sportsCode, smd.id as sportsMetaId, smd.sportsName, smd.sportsMasterId, t.id as thresholdId, t.sportsMetaDataId, t.value from sportsMaster inner join sportsMetaData smd on sm.id = smd.sportsMasterId inner join threshold t on t.sporsMetaDataId = smd.id";

    return sportsThresholdCommonMapperList

        .mapper(jdbcTemplate.queryForList(sql));

  }

The Wait Ends Here !!!

Mapper Class



package workspace;

import java.util.ArrayList;

import java.util.List;

import java.util.Map;

public class SportsThresholdCommonMapperList {

  public List<SportsThresholdCommonDTO> mapper(List<Map<String, Object>> rows) {

    List<SportsThresholdCommonDTO> sportsThresholdCommonDTOs = new ArrayList<>();

    for (Map rs : rows) {

      SportsThresholdCommonDTO sportsThresholdCommonDTO = new SportsThresholdCommonDTO();

      SportsMasterDTO sportsMasterDTO = new SportsMasterDTO();

      SportsMetaDTO sportsMetaDTO = new SportsMetaDTO();

      ThresholdDTO thresholdDTO = new ThresholdDTO();

      sportsMasterDTO.setId((Integer) rs.get("sportsMasterId"));

      sportsMasterDTO.setSportsCode((String) rs.get("sportsCode"));

      sportsMetaDTO.setId((Integer) rs.get("sportsMetaId"));

      sportsMetaDTO.setSportsName((String) rs.get("sportsName"));

      sportsMetaDTO.setSportsMasterId((Integer) rs.get("sportsMasterId"));

      thresholdDTO.setId((Integer) rs.get("thresholdId"));

      thresholdDTO.setSportsMetaDataId((Integer) rs.get("sportsMetaId"));

      thresholdDTO.setValue((String) rs.get("value"));

      sportsThresholdCommonDTO.setSportsMasterDTO(sportsMasterDTO);

      sportsThresholdCommonDTO.setSportsMetaDTO(sportsMetaDTO);

      sportsThresholdCommonDTO.setThresholdDTO(thresholdDTO);

      sportsThresholdCommonDTOs.add(sportsThresholdCommonDTO);

    }

    return sportsThresholdCommonDTOs;

  }

}

Alternate Way:

Make a new model which contains variable of all classes
Make Aliases to map t model properly
Result will be a unnested class with all data.

Drawback of making another model

Maintainability overheads
Making aliases and keeping different names in all models

Conclusion
We are able to get nested class object list from mysql with the help of custom row mapper.

Nested Classes - CustomRowMapper !! Not a Problem Anymore !! - Part 1

sainiankitsaini — Fri, 25 Mar 2022 17:54:21 +0000

What is Spring Boot:
Spring Boot is an open source, microservice-based Java web framework.

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

Introduction
Here we are going to use custom row mapper when we want only a single object as return type

Class Diagram used in this Example:

Simple Class : Not Nested Classes

SportsMasterDTO.java

package workspace;

public class SportsMasterDTO {
  private int id;
  private String sportsCode;

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public String getSportsCode() {
    return sportsCode;
  }

  public void setSportsCode(String sportsCode) {
    this.sportsCode = sportsCode;
  }
}

SportsMetaDTO Class:

package workspace;

public class SportsMetaDTO {
  private int id;
  private int sportsName;
  private int sportsMasterId;
  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public int getSportsName() {
    return sportsName;
  }
  public void setSportsName(int sportsName) {
    this.sportsName = sportsName;
  }
  public int getSportsMasterId() {
    return sportsMasterId;
  }
  public void setSportsMasterId(int sportsMasterId) {
    this.sportsMasterId = sportsMasterId;
  }
  @Override
  public String toString() {
    return "SportsMetaDTO [id=" + id + ", sportsName=" + sportsName + ", sportsMasterId="
        + sportsMasterId + "]";
  }
}

ThresholdDTO class

package workspace;

public class ThresholdDTO {
  private int id;
  private int sportsMetaDataId;
  private String value;

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public int getSportsMetaDataId() {
    return sportsMetaDataId;
  }

  public void setSportsMetaDataId(int sportsMetaDataId) {
    this.sportsMetaDataId = sportsMetaDataId;
  }

  public String getValue() {
    return value;
  }

  public void setValue(String value) {
    this.value = value;
  }

  @Override
  public String toString() {
    return "ThresholdDTO [id=" + id + ", sportsMetaDataId=" + sportsMetaDataId + ", value=" + value
        + "]";
  }


}

Nested Class:

Nested Class: SportsThresholdCommonDTO

package workspace;

public class SportsThresholdCommonDTO {
  private SportsMasterDTO sportsMasterDTO;
  private SportsMetaDTO sportsMetaDTO;
  private ThresholdDTO thresholdDTO;
  public SportsMasterDTO getSportsMasterDTO() {
    return sportsMasterDTO;
  }
  public void setSportsMasterDTO(SportsMasterDTO sportsMasterDTO) {
    this.sportsMasterDTO = sportsMasterDTO;
  }
  public SportsMetaDTO getSportsMetaDTO() {
    return sportsMetaDTO;
  }
  public void setSportsMetaDTO(SportsMetaDTO sportsMetaDTO) {
    this.sportsMetaDTO = sportsMetaDTO;
  }
  public ThresholdDTO getThresholdDTO() {
    return thresholdDTO;
  }
  public void setThresholdDTO(ThresholdDTO thresholdDTO) {
    this.thresholdDTO = thresholdDTO;
  }
  @Override
  public String toString() {
    return "SportsThresholdCommonDTO [sportsMasterDTO=" + sportsMasterDTO + ", sportsMetaDTO="
        + sportsMetaDTO + ", thresholdDTO=" + thresholdDTO + "]";
  }
}

How To use rowmapper if mysql returns a single object for nested class:

MYSQL Query Which contain joins on 3 tables with limit 1

select 
  sm.id as sportsMasterId, 
  sm.sportsCode, 
  smd.id as sportsMetaId, 
  smd.sportsName, 
  smd.sportsMasterId, 
  t.id as thresholdId, 
  t.sportsMetaDataId, 
  t.value 
from 
  sportsMaster 
  inner join sportsMetaData smd on sm.id = smd.sportsMasterId 
  inner join threshold t on t.sporsMetaDataId = smd.id limit 1

How to Access DataBase:

Java Method

public SportsThresholdCommonDTO getSportsThresholdCommonObject() {
    String sql =
        "select sm.id as sportsMasterId, sm.sportsCode, smd.id as sportsMetaId, smd.sportsName, smd.sportsMasterId, t.id as thresholdId, t.sportsMetaDataId, t.value from sportsMaster inner join sportsMetaData smd on sm.id = smd.sportsMasterId inner join threshold t on t.sporsMetaDataId = smd.id limit 1";
return jdbcTemplate.queryForObject(sql, SportsThresholdCommonDTO.class);
  }

The Wait Ends Here !!!

Mapper Class

package workspace;

import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.tree.RowMapper;

public class SportsThresholdCommonMapper implements RowMapper<SportsThresholdCommonDTO> {

  @Override
  public SportsThresholdCommonDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
    SportsThresholdCommonDTO sportsThresholdCommonDTO = new SportsThresholdCommonDTO();
    SportsMasterDTO sportsMasterDTO = new SportsMasterDTO();
    SportsMetaDTO sportsMetaDTO = new SportsMetaDTO();
    ThresholdDTO thresholdDTO = new ThresholdDTO();
    sportsMasterDTO.setId(rs.getInt("sportsMasterId"));
    sportsMasterDTO.setSportsCode(rs.getString("sportsCode"));
    sportsMetaDTO.setId(rs.getInt("sportsMetaId"));
    sportsMetaDTO.setSportsName(rs.getString("sportsName"));
    sportsMetaDTO.setSportsMasterId(rs.getInt("sportsMasterId"));
    thresholdDTO.setId(rs.getInt("thresholdId"));
    thresholdDTO.setSportsMetaDataId(rs.getInt("sportsMetaId"));
    thresholdDTO.setValue(rs.getString("value"));
    sportsThresholdCommonDTO.setSportsMasterDTO(sportsMasterDTO);
    sportsThresholdCommonDTO.setSportsMetaDTO(sportsMetaDTO);
    sportsThresholdCommonDTO.setThresholdDTO(thresholdDTO);
    return sportsThresholdCommonDTO;
  }
}

Alternate Way:

Make a new model which contains variable of all classes
Make Aliases to map t model properly
Result will be a unnested class with all data.

Drawback of making another model

Maintainability overheads
Making aliases and keeping different names in all models

Conclusion
We are able to get nested class object from mysql with the help of custom row mapper.

Bug that can cost you Millions of Dollars!!!

sainiankitsaini — Sat, 15 Feb 2020 07:56:41 +0000

ISO 8601

ISO 8601
There is a practical international standard for date-time handling, ISO 8601.
The ISO 8601 definition of a week is:
Weeks start on a Monday
Week # 1 has the first Thursday of the year
A year consists of either 52 or 53 weeks.
A year may have one or more days from the previous calendar year, and also from the following calendar year.

Types of Year

There are 2 types of Year in java which are as follows:

Week Based Year
Era Based Year

Week based year is in sync with WEEK_OF_YEAR cycle but era based year is the normal calendar year.

How to get Date in java

Method 1. Using Era Based Year (Using small alphabet 'y' for year)

Date date = new SimpleDateFormat("yyyy/MM/dd").parse("2019/12/31");

Method 2. Using Week based Year (Using capital alpabet 'Y' for year)

Date date = new SimpleDateFormat("YYYY/MM/dd").parse("2019/12/31");

Issue in different date getting methods

For all the weeks of a year any method of calculating date(Era/Week based) will work fine. Issue occurs only during the 1st week and last week of year.
Example: January 1, 2020 is a Wednesday. If getFirstDayOfWeek() is MONDAY and getMinimalDaysInFirstWeek() is 5 (ISO 8601 standard compatible setting), then week 1 of 2020 starts on December 30, 2019, and ends on January 5, 2020. The week year is 2020 for the last two days of calendar year 2019. If, however, getFirstDayOfWeek() is SUNDAY, then week 1 of 2020 starts on January 5, 2020, and ends on January 11, 2020; the first four days of 2020 then are part of week 53 of 2019 and their week year is 2019.

Understanding Issue through code

using Era based year('y) for getting date but Week Based Year ('Y') for formatting date

Date date = new SimpleDateFormat("yyyy/MM/dd").parse("2019/12/31");
String result = new SimpleDateFormat("YYYY/MM/dd").format(date);   

//result '2020/12/31' which is unexpected

using Era based year('y) for getting date and Era based year('y) for formatting date

Date date = new SimpleDateFormat("yyyy/MM/dd").parse("2019/12/31");
String result = new SimpleDateFormat("yyyy/MM/dd").format(date);   

//result '2019/12/31' as expected

All combinations of Week based and Era based year

package codechef;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DateMethodsExplore {

  public static void main(String[] args) throws ParseException {
    Date date1 = new SimpleDateFormat("yyyy/MM/dd").parse("2019/12/31");
    String result1 = new SimpleDateFormat("yyyy/MM/dd").format(date1);
    System.out.println("date 1 =>" + result1);
    System.out.println("**************");

    Date date2 = new SimpleDateFormat("YYYY/MM/dd").parse("2019/12/31");
    String result2 = new SimpleDateFormat("YYYY/MM/dd").format(date2);
    System.out.println("date 2 =>" + result1);
    System.out.println("**************");

    Date date3 = new SimpleDateFormat("yyyy/MM/dd").parse("2019/12/31");
    String result3 = new SimpleDateFormat("YYYY/MM/dd").format(date3);
    System.out.println("date 3 =>" + result3);
    System.out.println("**************");

    Date date4 = new SimpleDateFormat("YYYY/MM/dd").parse("2019/12/31");
    String result4 = new SimpleDateFormat("yyyy/MM/dd").format(date4);
    System.out.println("date 4 =>" + result4);
    System.out.println("**************");
  }
}
//output
date 1 =>2019/12/31
**************
date 2 =>2019/12/31
**************
date 3 =>2020/12/31
**************
date 4 =>2018/12/30
**************

Why Week Based year is Made

There are many usecases like if we want to see weekly stocks rate of a company during a financial year (For India: 1 april to next year 31 march) then week based can be used to plot a graph in order to better display the statistics.

Conclusion

It actually depends upon the usecase which year you want to use but in the formatting both methods should not be mixed.

Java(Spring Boot) Best Database Practices

sainiankitsaini — Sat, 08 Feb 2020 14:44:30 +0000

What is Spring Boot?

Spring Boot is an open source Java-based framework used to create a micro Service. It is developed by Pivotal Team and is used to build stand-alone and production ready spring applications.

What is Mysql?

MySQL is a relational database management system based on SQL – Structured Query Language. The application is used for a wide range of purposes, including data warehousing, e-commerce, and logging applications. The most common use for mySQL however, is for the purpose of a web database.

Mysql Table Structure

id	Book Name	Author Name
1	The Red and the Black	Stendhal
2	Madame Bovary	Gustave Flaubert
3	War and Peace	Tolstoy

Methods to Fetch Values from Mysql in Spring Boot

Method 1

 public int updateBookName(String bookName,int id) {
    String sql = "UPDATE book set BookName = :bookName WHERE id = :id";
    Map<String, Object> bind = new HashMap<>();
    bind.put("bookName", bookName);
    bind.put("id", id);
    SqlParameterSource paramSource = new MapSqlParameterSource(bind);
    return jdbcTemplate.update(sql, paramSource);
}

DrawBack

None but it is not the best recommended practice

Method 2

public int booksAvailability(int id ){
    int entryExists = 0;

    entryExists = jdbcTemplate.queryForObject(
        "SELECT count(*) from book where id = " + id, Integer.class);
    return entryExists;
}

DrawBack

Sql Injection is Possible here
Directly making queries by appending string make them slower

Method 3

  public boolean savebooksData(List<Object[]> inputList) {
    String sql =
        "insert into books (id, BookName, AuthorName) values (?,?,?)";
    jdbcTemplate.batchUpdate(sql, inputList);
    return true;
  }

Drawback

1.No check on what types of values can be inserted in the DB

Method 4

public int BookExists(String bookName){
    MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();

String sql =
        "SELECT count(*) from books where BookName =:bookName";
    mapSqlParameterSource.addValue("bookName", bookName,Types.String);

    entryExists = jdbcTemplate.queryForObject(sql, mapSqlParameterSource, Integer.class);
return entryExists;
}

Drawback

None

Advantage

It is the recommended practice for dealing with databases

Method 5

public int updateBook(String authorName,int id){
 String query = "UPDATE books SET AuthorName=? where id=?";
      id = jdbcTemplate.update(query, new Object[] {authorName, id});
return id;
}

DrawBack

1.No check on what types of values can be inserted in the DB

Insights in using MapSqlParameterSource

The MapSqlParameterSource is just a decorator of a LinkedHashMap, if you check the MapSqlParameterSource, you will see this:

private final Map<String, Object> values = new LinkedHashMap<String, Object>();

Why MapSqlParameterSource is Recommended

No possibility of Sql Injection
We are making an additional check on parameters while inserting as well as fetching from Mysql DB

Conclusion

Method 4 which uses MapSqlParameterSource is the most recommended practice which needs to be followed by Spring Boot Developers

For Further Reference

[1] https://stackoverflow.com/questions/42565862/mapsqlparametersource-vs-java-util-map

DEV Community: sainiankitsaini

Nested Classes - CustomRowMapper !! Not a Problem Anymore !! - Part 2

Class Diagram used in this Example:

Simple Class : Not Nested Class

Nested Class:

The Wait Ends Here !!!

Alternate Way:

Nested Classes - CustomRowMapper !! Not a Problem Anymore !! - Part 1

Class Diagram used in this Example:

Simple Class : Not Nested Classes

Nested Class:

How To use rowmapper if mysql returns a single object for nested class:

The Wait Ends Here !!!

Alternate Way:

Bug that can cost you Millions of Dollars!!!

ISO 8601

Types of Year

How to get Date in java

Method 1. Using Era Based Year (Using small alphabet 'y' for year)

Method 2. Using Week based Year (Using capital alpabet 'Y' for year)

Issue in different date getting methods

Understanding Issue through code

using Era based year('y) for getting date but Week Based Year ('Y') for formatting date

using Era based year('y) for getting date and Era based year('y) for formatting date

All combinations of Week based and Era based year

Why Week Based year is Made

Conclusion

Java(Spring Boot) Best Database Practices

What is Spring Boot?

What is Mysql?

*Mysql Table Structure *

Methods to Fetch Values from Mysql in Spring Boot

Method 1

DrawBack

Method 2

DrawBack

Method 3

Drawback

Method 4

Drawback

Advantage

Method 5

DrawBack

Insights in using MapSqlParameterSource

Why MapSqlParameterSource is Recommended

Conclusion

For Further Reference

Mysql Table Structure