Java(Spring Boot) Best Database Practices
Tags: springboot, java, sql, database
Published: 2020-02-08 14:44:30

###**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*
```java
 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*
```java
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*
```java
  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*
```java
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*
```java
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:
```java
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