Monday, August 23, 2021

Spring Boot + Database example - Data retrieval

 1.Create springboot project using https://start.spring.io/ 

2.Add dependency , Spring Web, Mysql Driver, Spring JPA

 

 

3. Generate the code and , import as existing maven project

4.Since this is simple database sample add DBconnection details to the application.properties file

spring.datasource.url=jdbc:mysql://db_ip:3306/db_name?useSSL=false&serverTimezone=UTC
spring.datasource.username=user_name
spring.datasource.password=password

5.Next we create 3 layers virtually for easy understand and development

1.Control Layer

2.Service Layer

3.Repository Layer

6.Create Entity class for relevant table to be used,name City

import java.util.Objects;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "cities_test") // Mapping table with DB
public class City {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;
    private int population;

    public City() {
    }

    public City(Long id, String name, int population) {
        this.id = id;
        this.name = name;
        this.population = population;
    }

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getPopulation() {
        return population;
    }

    public void setPopulation(int population) {
        this.population = population;
    }
}

7.Extend CrudRepository and create CityRepository interface for doing db related transactions

Make sure to add @Repository annotation to the class

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface CityRepository extends CrudRepository<City, Long>{

}
 

8.Then create and CityService interface for doing transaction with Repository

also add signature for retrieve data from db layer  

import java.util.List;

import lk.inova.example.all_demo.repository.City;

public interface CityService {
  List<City> findAllCities();
}

 

9.Then Create impl class by implementing above CityService interface method, also Autowired the CityRepository interface to service Impl class, make sure to set @Service annotation

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import lk.inova.example.all_demo.repository.City;
import lk.inova.example.all_demo.repository.CityRepository;
import lk.inova.example.all_demo.service.CityService;

@Service
public class CityServiceImpl implements CityService {
    @Autowired
    CityRepository repository;
    
    @Override
    public List<City> findAllCities() {
        return (List<City>) repository.findAll();
         
    }

}

10.Then create class MyCityConroller for any transaction about cities - Get Cities, Update, Delete..etc

makesure to add annotation @RestController,...Also Add request mapping and GetMapping to relevant method. also Autowired CityServiceImple class. 

@RestController
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import lk.inova.example.all_demo.repository.City;
import lk.inova.example.all_demo.service.impl.CityServiceImpl;

@RestController
@RequestMapping("/db")
public class MyCityController {
    @Autowired
    CityServiceImpl cityServiceImpl;
    
    @GetMapping("/cities")
    public List<City> getAllCities(){
        return cityServiceImpl.findAllCities();
    }

}


11.Now you can see the all cities 

http://localhost:8080/db/cities


note : 

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>lk.inova.example</groupId>
    <artifactId>all_demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>all_demo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>




Wednesday, May 27, 2020

MY SQL Query hints , best practices and performance

Hi,

 Below described some SQL practices which are important to us during the API development,

1.            Use ASC or DESC when you are using ‘LIMIT’ in the Query, Sometimes you may not get desired results

2.            Distinct not work only for selected column, but eliminates duplicate rows,

Ex :

nativeQuery.append("SELECT ");

nativeQuery.append("DISTINCT(payment_reference_no.id),");  

nativeQuery.append("(\\@cnt \\:\\= \\@cnt \\+ 1) AS rowNumber, ");

nativeQuery.append("payment_reference_no.created_at AS  prnCreatedAt, ");

3.            Use always parametrized query, caching will improve performance

4.            Need, full group by columns after the use aggregate functions [depends on where clause]

5.            IF foreign key is not null ,use JOIN[inner join] not the LEFT JOIN,..LEFT JOIN results sometimes may not be corrected and have performance hit.

6.            IF you are using LEFT JOIN , do not add relevant table's filter criteria to where clause it should be with JOIN condition .

7.use concat_ws function to use for separate concat strings to given for mat

Ex concat_we('-' ,'aa','bb');

aa-bb  

8.use always nested query joins