Java(Spring Boot) Best Database Practices

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

1. Sql Injection is Possible here
2. 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

1. No possibility of Sql Injection
2. 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